Archive rows to a separate sheet - google sheets

Question

I have a script for archiving rows into one tab to another tab based on cell edit within the same spreadsheet. However the size of the sheet is getting very large resulting in slow page load. I was just wondering, as I'm not too experienced in scripts, if it were to possible to "archive" rows into a completely separate google sheet (new URL).

Note: At the moment, if you change Column 6 cell value to "Archive" it sends from one sheet ("Content Production Master") to another sheet ("Content Archive").

See code below for what I've got at the moment - any help would be very useful!

  // assumes source data in sheet named Content Production Master
  // target sheet of move to named Content Archive
  // test column with "Archive" is col6
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Content Production Master" && r.getColumn() == 6 && r.getValue() == "Archive") {
    var row = r.getRow();
    var lastRow = r.getLastRow(); //Save the last edited row index
    var numColumns = s.getLastColumn();
    var numRows = lastRow-row + 1; // Corrected this calculation
    var targetSheet = ss.getSheetByName("Content Archive");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    //s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.getRange(row, 1, numRows, numColumns).moveTo(target); // Take in account the lastRow when archiving the edited range.     
    //s.deleteRow(row);
    for (let i = row; i<=lastRow; i++) {
      s.deleteRow(row);
      console.log(`Deleted row ${row} at pass number ${i}`);
    }
  }
}

Ron M Ron M · Accepted Answer · 2021-07-22T15:27:30

Since it is not possible to move data from your source sheet to a different spreadsheet using move(target). You can manually get the data to be moved and paste that to another spreadsheet using getValues() and setValues(values).

Sample Code:

function onEdit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = ss.getActiveRange();

  if(s.getName() == "Content Production Master" && r.getColumn() == 6 && r.getValue() == "Archive") {
    //var targetSs = SpreadsheetApp.openById('1xh0huhm7ghGP7Y_5jNSFh7eaNX4iVoOL4SY95IWqpfs');
    var targetSs = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1xh0huhm7ghGP7Y_5jNSFh7eaNX4iVoOL4SY95IWqpfs/edit?resourcekey=0-j6u0gdSEBp-F9SZYlZYi1g#gid=93990300');
    var targetSheet = targetSs.getSheetByName("Content Archive"); 

    var row = r.getRow();
    var lastRow = r.getLastRow(); //Save the last edited row index
    var numColumns = s.getLastColumn();
    var numRows = lastRow-row + 1; // Corrected this calculation
    
    //Get data to be moved
    var data = s.getRange(row, 1, numRows, numColumns).getValues();

    //Paste the values to the target sheet on another spreadsheet
    targetSheet.getRange(targetSheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);

    //Delete data in the source sheet
    s.deleteRows(row, numRows);
  }
  
}

Pre-requisite(use installable onEdit trigger):

What it does?

After checking if the modified cell is in column 6 with value "Archive". Open the target spreadsheet using either openByUrl(url) or openById(id). Then select the target sheet.
Get the row values of the range to be moved using getValues()
Write the values to your target sheet using setValues(values)
Delete the archived rows in your source sheet using deleteRows(rowPosition, howMany)

Output:

Source Sheet (Before)

Source Sheet (After)

Target Sheet (After)