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
As I had imagine the issue was the
moveTo(target)
I changed it to
copyTo(target)
and now it works properly