Transferring range of rows to another tab, then clearing tab without removing formulas

Question

In Google Sheets, I'm trying to transfer range A3:G13(if there is text; sometimes the entire range will not be used) from the Packing List tab to the Completed Transactions tab when G16 is set to "Complete" and then clear A3:G13 without removing the formulas. I have seen many scripts that transfer rows individually but I need them all to transfer onEdit of G15.

Thanks for your help!

https://docs.google.com/spreadsheets/d/1raVbOHsjwK1EHDnIO7jKo1oH4GQXsbfjBD2ormcO87I/edit?ts=5fd250e3#gid=0

Code:

function onEdit(event) { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var s = event.source.getActiveSheet(); 
  var r = event.source.getActiveRange(); 
  if(s.getName() == "Packing List" && r.getColumn() == 7 && r.getValue() == "Complete") { 
    var range = r.getRange(A3:G13); 
    var numColumns = s.getLastColumn(); 
    var targetSheet = ss.getSheetByName("Completed Transactions"); 
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
    s.getRange(row, 1, 1, numColumns).moveTo(target); 
    s.deleteRow(row);

Answer 1

You could do the following:

• Retrieve the values from the source range with getValues, filter out the rows where column A is empty with filter, and set the filtered values with setValues.
• Retrieve the formulas of the source range using getFormulas, and set them again with setFormulas after the content has been cleared.
• Clear the content of the source range with clearContent.

Code snippet:

function onEdit(e) {
  const rangeNotation = e.range.getA1Notation();
  const editedSheet = e.range.getSheet();
  if (editedSheet.getName() === "Packing List" && rangeNotation === "G15" && e.value === "Complete") {
    const sourceRange = editedSheet.getRange("A3:G13");
    const sourceFormulas = sourceRange.getFormulas();
    const sourceData = sourceRange.getValues().filter(row => row[0] != "");
    const targetSheet = e.source.getSheetByName("Completed Transactions");
    const targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, sourceData.length, sourceData[0].length);
    targetRange.setValues(sourceData);
    sourceRange.clearContent();
    sourceRange.setFormulas(sourceFormulas);
  }
}