How can I copy a range to a different sheet based on checkbox value, then delete on the new sheet, while keeping both check boxes up to date

1
votes

I need some help on this code. I have my source sheet "Agency List". When the check box in column J is True the data in columns K-Last Column is copied to sheet "Itinerary" starting at the first empty row and column K. This formula is working, however I need to add some functionality and can't find an answer. I need to:

  • I want to be able to check the box, have the row copy to "Itinerary". Then on "Itinerary" have a checked box carry over with the data. When I uncheck that box on "Itinerary" I want to data to clear, and the corresponding check box on "Agency List" to be unchecked as well.

  • I also need to be able to toggle both check boxes with my mouse. And it seems that if the cell contains a formula you can not toggle. Is there a way around this?

  • Currently I'm copying the data to the last row in "Itinerary". But if there is a formula or column anywhere else on the sheet in that row it treats the row as not empty. In particular I can't have a checkbox in column 'Itinerary'!J:J down the sheet because then the row isn't blank and the data goes to the very bottom. I tried using a code that would look at the last row of Column O, but then they entire formula stopped working: var target = targetSheet.getRange(targetSheet.getRange(O:O).getLastRow() + 1,11);

  • I thought about using VLOOKUP or Match or some other formula to scan a column in "itinerary" for a match and if there isn't a match then the checkbox goes to FALSE. But again, then I can't toggle the checkbox.

Is there a way to keep both check boxes up to date and copy/clear the data on "Itinerary" based on that checkbox? Thanks!

Here is a link to a sample sheet: Sample Sheet

function onEdit(event) {
    // assumes source data in sheet named "Agency List"
    // target sheet of move to named "Itinerary"
    // getColumn with check-boxes is currently set to colu 3 or C
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = event.source.getActiveSheet();
    var r = event.source.getActiveRange();
    if (s.getName() == "Agency List" && r.getColumn() == 3 && r.getValue() == true) {
        var row = r.getRow();
        var numColumns = s.getLastColumn();
        var targetSheet = ss.getSheetByName("Itinerary");
        var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 11);
        s.getRange(row, 4, 1, numColumns).copyTo(target, { contentsOnly: true });
    } else if (s.getName() == "Itinerary" && r.getColumn() == 9 && r.getValue() == false) {
        var row = r.getRow();
        var numColumns = s.getLastColumn();
        s.deleteRow(row);
    }
}
1
google-apps-scriptcheckboxgoogle-sheets
Could you maybe share a copy of your sheet to visualize what you are trying to do? Do you want to delete both rows when the checkbox is clicked? Maybe add a description in the sample sheet to see what you want. Also you may want to use appendRow. - Raserhin
@Raserhin I just updated with a sample sheet and I put a description in there as well. Thank you in advance for any ideas! - Sio
So you want to delete the "Itinerary" when either of the checkbox is unchecked, and update the "Agency List" to "FALSE" when you delete the row in "Itinerary". Is there any unique field to identify both? Could you maybe add an ID column to each record? - Raserhin
@Raserhin I thought about using an ID, but then the checkbox would be tied to a formula and as far as I know can't be toggled, unless there is a way around this? If if that is possible, do you have a suggestion on the script needed for this? thanks! - Sio

1 Answers

0
votes

Ok @Sio I think I made a fix that could work for you.

Basically the problem that you have is keeping track of what row in Itinerary corresponds to the row in Agency List.

To have that kind of persistence in Apps Script without actually storing in the sheet I found this handy Service called PropertiesService. In there I stored the rows in pairs to later retrieve.

Code

function onEdit(event) {
    // assumes source data in sheet named "Agency List"
    // target sheet of move to named "Itinerary"
    // getColumn with check-boxes is currently set to colu 3 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() == "Agency List" && r.getColumn() == 3 && r.getValue() == true) {
        // Create the record in "Itnerary"
        var prop = PropertiesService.getDocumentProperties();
        var targetSheet = ss.getSheetByName("Itinerary");
        var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 11);
        s.getRange(row, 4, 1, numColumns).copyTo(target, { contentsOnly: true });
        // Insert checkbox cell (already checked)
        targetSheet.getRange(target.getRow(), 10).insertCheckboxes().check();
        prop.setProperty(row, target.getRow());
    } else if (s.getName() == "Agency List" && r.getColumn() == 3 && r.getValue() == "") {
        // Remove the row from "Itinerary" when the checkbox in "Agency List" is unchecked 
        var prop = PropertiesService.getDocumentProperties();
        var targetRow = prop.getProperty(row);
        ss.getSheetByName("Itinerary").deleteRow(targetRow);
        Logger.log("Deleted the " + targetRow + "row from " + s.getName());
        prop.deleteProperty(row);
    } else if (s.getName() == "Itinerary" && r.getColumn() == 10 && r.getValue() == false) {
        // Remove the row from "Itinerary"when the checkbox is unchecked and unchecks in "Agency List"
        var prop = PropertiesService.getDocumentProperties();
        s.deleteRow(row);

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

Explanation

So you actually have three cases you need to process here.

  1. You check the row in Agency List to create a new record in Itinerary.
if (s.getName() == "Agency List" && r.getColumn() == 3 && r.getValue() == true) {
        // Create the record in "Itnerary"
        var prop = PropertiesService.getDocumentProperties();
        var targetSheet = ss.getSheetByName("Itinerary");
        var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 11);
        s.getRange(row, 4, 1, numColumns).copyTo(target, { contentsOnly: true });
        // Insert checkbox cell (already checked)
        targetSheet.getRange(target.getRow(), 10).insertCheckboxes().check();
        prop.setProperty(row, target.getRow());
}

This is the one you already had, just adding the insertCheckboxes at 10th column (J). Also storing the pair of rows to later retrieve it. Look that I have store it in a document level.

  1. You uncheck the row at Agency List to delete the row at Itinerary 
} else if (s.getName() == "Agency List" && r.getColumn() == 3 && r.getValue() == "") {
        // Remove the row from "Itinerary" when the checkbox in "Agency List" is unchecked 
        var prop = PropertiesService.getDocumentProperties();
        var targetRow = prop.getProperty(row);
        ss.getSheetByName("Itinerary").deleteRow(targetRow);
        Logger.log("Deleted the " + targetRow + "row from " + s.getName());
        prop.deleteProperty(row);
}

Getting the row we need to delete through the document properties. Then use the deleteRow in Itinerary, and get rid of the record in our properties with deleteProperty.

  1. You uncheck the row in Itinerary to delete that row and to uncheck the row in Agency List . 
} else if (s.getName() == "Itinerary" && r.getColumn() == 10 && r.getValue() == false) {
        // Remove the row from "Itinerary"when the checkbox is unchecked and unchecks in "Agency List"
        var prop = PropertiesService.getDocumentProperties();
        s.deleteRow(row);

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

In here deleting the row is pretty straightforward because you retrieve that from the event object. Although in this case we will need to iterate to every pair of values from the properties because we stored the key as the row of Agency List

Reference