I need to move rows to other tab on checkbox = TRUE, but the following code is crashing my sheet as it is terribly slow. How can I make it faster? I am a bit strangling in here.
How can I check for boxes = true across the whole document without using a for loop and without crashing the sheet?
| Check | ID | Product |
|---|---|---|
| ☑ | 1 | 3456 |
| ☑ | 2 | TOPRPR |
| [] | 3 | AZECRT |
function onEdit(event) {
// assumes source data in sheet named main
// target sheet of move to named Completed
// getColumn with check-boxes is currently set to column 4 or D
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "main" && r.getColumn() == 4 && r.getValue() == true) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Completed");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
} else if(s.getName() == "Completed" && r.getColumn() == 4 && r.getValue() == false) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("discart");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
function onEdit(e) {
//e.source.toast('entry');
//Logger.log(JSON.stringify(e));
const sh = e.range.getSheet();
if(sh.getName() == "main" && e.range.columnStart == 4 && e.value == "TRUE") {
let tsh = e.source.getSheetByName("Completed");
sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(tsh.getRange(tsh.getLastRow() + 1, 1));
sh.deleteRow(e.range.rowStart);
} else if(sh.getName() == "Completed" && e.range.columnStart == 4 && e.value == "FALSE") {
let tsh = e.source.getSheetByName("discart");
let trg = tsh.getRange(tsh.getLastRow() + 1, 1);
sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).moveTo(trg);
sh.deleteRow(e.range.rowStart);
}
}