Search code examples
google-apps-scriptgoogle-sheetsgoogle-forms

Need to move rows from form generated sheet


I have a survey sheet that I need my team to see once but after that I want the rows my team sets as "Closed" to an archive sheet. In the past for other sheets I have used this script but it doesn't seem to work on the form linked sheet. After researching I found that you can't CUT from a form linked sheet but you can remove rows, my theory is that the script is trying to cut and that is why is not working. What would a "Copy" and delete aproach would be?

    function onEdit(event) {
  // assumes source data in sheet named Needed
  // target sheet of move to named Acquired
  // test column with yes/no is col 4 or D
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  var sheets = ["Form Responses", "Input"];
  if(sheets.includes(s.getName()) && r.getColumn() == 1 && r.getValue() == "Closed / Move to Archive") {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Archive");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

Highly appreciate any help


Solution

  • As I had imagine the issue was the

    moveTo(target)
    

    I changed it to

    copyTo(target)
    

    and now it works properly