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