Need to move row from one google spreadsheet to a

Question

I am trying to move a row from one spreadsheet to a completely different spreadsheet based on a cell. I have a script that I can make work if the sheets are in the same file but not two different files. The first file was created by a form responses and the other file is a spreadsheet I manually created. Here is my script:

function onEdit() {
 var sheetNameToWatch = "Form Responses 1";
 var columnNumberToWatch = 11;
 var valueToWatch = "Y";
 var sheetNameToMoveTheRowTo = "Completed Repairs";
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var ts = SpreadsheetApp.openById("1VpSqsR_zBU6FhEfVW5QJi2-oAvVhxnxSQmoINz_J5-Y");
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getActiveCell();

 if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
   var targetSheet = ts.getSheetByName(sheetNameToMoveTheRowTo);
   var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
   sheet.getRange(range.getRow(), 1, 1,sheet.getLastColumn()).copyTo(targetRange);
   sheet.deleteRow(range.getRow());
 }
}

Answer 1

You are seeing this error Execution failed: You do not have permission to perform that action., because you cannot call Built-in Google Services(openById) from onEdit() function.

Simple triggers run in a limited auth mode that doesn't allow opening other spreadsheets. You can create an installable trigger (details in that help page) to run your onEdit script with full auth.

Check this page for more details on simple triggers and the restrictions for the triggers.

Hope that helps!