How to send/archive a row of data from one sheet to another through trigger?

Question

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

Answer 1

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).

References

• Simple triggers: onEdit(e) (Google Apps Script guides)
• Strict equality (===) (MDN)

Answer 2

Transfer data to select sheets and delete current row

function onEdit(e) {
  const sh = e.range.getSheet();
  const cvs = ['Archive', 'Cancel', 'Canceled', 'Cancelled'];//target sheets
  const idx = cvs.indexOf(e.value);//to get sheetname
  if (sh.getName() == 'Sheet' && e.range.columnStart == 2 && ~idx && e.range.rowStart>1) {
    const vs = sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).getValues();
    const tsh = e.source.getSheetByName(cvs[idx]);
    tsh.getRange(tsh.getLastRow() + 1, 1, vs.length, vs[0].length).setValues(vs);
    sh.deleteRow(e.range.rowStart);//delete current row
  }
}

Sheet1:

Bitwise Not ~