function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var r = ss.getActiveRange();
var rows = r.getRow();
var cell = s.getRange(rows, r.getColumn());
if (s.getName() == "Sheet" && r.getColumn() == 2 && cell.getValue() == "Archive" || cell.getValue() == "Cancel" || cell.getValue() == "Canceled" || cell.getValue() == "Cancelled") { // "Sheet" is original sheet, 2 is column to search for trigger
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Sheet Archive"); // "Sheet Archive" is the 2nd/target sheet
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(rows, 1, 1, numColumns).moveTo(target);
s.deleteRow(rows);
Logger.log(target);
}
I would like for a way to send a row of data from a sheet (Sheet) to another (Sheet Archive). If “Archive” is written in the 2nd column of a row, it sends the data to the archive sheet.
I want the data to stack one row after another. Say I archive row #20 in Sheet #1, I want it to send the data to row #9. If I archive row #21, 22, then 30, 31, etc. it should stack the archived rows one after the other, so in rows #9, 10, 11, 12, 13 etc. of the archive.
The code I have used to work but then I redesigned the sheets and they stopped working. When I type archive, it deletes it from Sheet #1 but then in the archive sheet it’s missing. So it’s “sending” it but it’s not showing up or pasting it properly. Is there a better way?
Thanks
Advertisement
Answer
I’ve cleaned you code a bit but seems mostly fine. Isntead of using getActiveRange and getActiveSheet, use the range provided by trigger argument. This is what it would look like:
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var r = e.range;
var s = r.getSheet();
var row = r.getRow();
var col = r.getColumn();
var cell = s.getRange(row, col);
var archiveStates = ["Archive", "Cancel", "Canceled", "Cancelled"];
if (s.getName() === "Sheet" && r.getColumn() === 2 && archiveStates.includes(cell.getValue())) {
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Sheet Archive"); // "Sheet Archive" is the 2nd/target sheet
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
I’ve also moved the way you tested for options and used strict equality (be careful with this).