jeudi 24 décembre 2020

Google Scripts: When checkbox true, copy row from sheet A to sheet B. When checkbox false, delete copied row from sheet B

I'm very new to Google Scripts and should really be learning the basics first, but jumped straight into trying to solve a problem for a friend. Have found this code online that is the perfect solution for what I want to achieve, however, the following snippet of code seems to be causing problems and not executing its expected function:

} else if (s.getName() == "Tab1" && r.getColumn() == 1 && r.getValue() == "") {
    // Remove the row from "Tab2" when the checkbox in "Tab1" is unchecked 
    var prop = PropertiesService.getDocumentProperties();
    var targetRow = prop.getProperty(row);
    ss.getSheetByName("Tab2").deleteRow(targetRow);
    Logger.log("Deleted the " + targetRow + "row from " + s.getName());
    prop.deleteProperty(row);

The full code can be seen below:

function onEdit(event) {
// assumes source data in sheet named "Tab1"
// target sheet of move to named "Tab2"
// getColumn with check-boxes is currently set to colu 1 or C
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
var row = r.getRow();
var numColumns = s.getLastColumn();
Logger.log(r.getValue());

if (s.getName() == "Tab1" && r.getColumn() == 1 && r.getValue() == true) {
    // Create the record in "Tab2"
    var prop = PropertiesService.getDocumentProperties();
    var targetSheet = ss.getSheetByName("Tab2");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 2);
    s.getRange(row, 2, 1, numColumns).copyTo(target, { contentsOnly: true });
    // Insert checkbox cell (already checked)
    targetSheet.getRange(target.getRow(), 1).insertCheckboxes().check();
    prop.setProperty(row, target.getRow());
} else if (s.getName() == "Tab1" && r.getColumn() == 1 && r.getValue() == "") {
    // Remove the row from "Tab2" when the checkbox in "Tab1" is unchecked 
    var prop = PropertiesService.getDocumentProperties();
    var targetRow = prop.getProperty(row);
    ss.getSheetByName("Tab2").deleteRow(targetRow);
    Logger.log("Deleted the " + targetRow + "row from " + s.getName());
    prop.deleteProperty(row);
} else if (s.getName() == "Tab2" && r.getColumn() == 1 && r.getValue() == false) {
    // Remove the row from "Tab2" when the checkbox is unchecked and unchecks in "Tab1"
    var prop = PropertiesService.getDocumentProperties();
    s.deleteRow(row);

    // Look the corresponding row in "Tab1"
    var keys = prop.getKeys();
    for (var i = 0; i < keys.length; i++) {
        var key = keys[i];
        if (prop.getProperty(key) == row) {
            ss.getSheetByName("Tab1").getRange(key, 1).setValue(false);
            prop.deleteProperty(key)
            break;
        }
    }
}

}

Everything is working exactly as I would expect it to (see use cases below), except for when the checkbox in Tab1 is unchecked on row X and the associated row in Tab2 (via PropertiesService) is not deleted... but it should be.

Use Cases:

  1. Checkbox on row X in Tab1 is true - copy row to Tab2 on next available line [WORKING in code above]
  2. Checkbox on row X in Tab1 is then made false (having the row already been copied into Tab2) - delete associated row in Tab2 [NOT WORKING in code above]
  3. Checkbox on row X in Tab2 is made false - delete row in Tab2 and make checkbox of associated row in Tab1 false [WORKING in code above]

Ideally, the code above would be even cleverer and each row would have a unique identifier (this could be added in column A for example) and work more like a relational database, so the code is not dependent on the order of rows being maintained e.g. if the user wanted to insert a new row in between existing rows in the future.

Thank you for any help in advance!




Aucun commentaire:

Enregistrer un commentaire