CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscheckboxscriptingspreadsheet
David Smith
David Smith

Reputation: 23

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!

Upvotes: 2

Views: 486

Answers (1)

NightEye
NightEye

Reputation: 11184

I'm trying to replicate the issue and yes, I also experience the issue you are referring to. Now, after some fiddling with the code, I can't seem to modify and make it work. Even modifying the last condition and make it work as your 2nd condition.

What works with me is this:

  } else if (s.getName() == "Tab1" && r.getColumn() == 1 && r.getValue() == false) {
    sourceRow = ss.getSheetByName("Tab1").getRange(r.getRow(), 1, 1, ss.getSheetByName("Tab1").getLastColumn()).getValues();
    destData = ss.getSheetByName("Tab2").getDataRange().getValues();    
    destData.forEach(function(item, index){
      tempRow = sourceRow[0];
      tempRow[0] = true;
      if(JSON.stringify(tempRow) === JSON.stringify(item)) {
        ss.getSheetByName("Tab2").deleteRow(index + 1);
      }
    });

So I did manually access the data on both sheets. Comparing all the rows in Tab2 to the selected row in Tab1.

And since the selected row is already unchecked after catching it, I did create a temp variable to store the ticked row value. Then look it up in Tab2. Then delete the row in Tab2 if they are the same.

Also, note that the r.getValue() condition above was updated to compare to false value instead of blank.

Upvotes: 1

Related Questions