jeudi 13 février 2020

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

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!

`

    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);
  }
}
`



Aucun commentaire:

Enregistrer un commentaire