How to move a row from one google spreadsheet to another based on a value in a cell?

Question

My script is supposed to move a row from one google spreadsheet to another based on a value in a cell. It fails to do so, and I think it's because of the onEdit() function but can't figure it out. I found a solution for different sheets in the same spreadsheet but not in different spreadsheets. My code is below.

function onEdit(e) {

  var sss = SpreadsheetApp.getActiveSpreadsheet();
  var ss = e.source.getActiveSheet();
  var r = e.source.getActiveRange();
  if(s.getName() == "source_sheet" && r.getColumn() == 7 && r.getValue() == "Pavlovo"){

  //Get full range of data
  var SRange = ss.getRange(1, 3, 1, 5);
  //get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();
  //get the data values in range
  var SData = SRange.getValues();

  // tss = target spreadsheet 
  var tss =  SpreadsheetApp.openById('1_t7BDCWgHJDip_cndzVashxLDQ_pVS6obi3I9TB_EJI'); 

  var ts = tss.getSheetByName('Vitosha'); // ts = target sheet
  //set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);
  }

}

What do you mean by "Move"? you want to delete from current sheet and paste it in new sheet? — iJay
Exactly. To remove it from the mother spreadsheet and add it to the new spreadsheet! — StMit
What is wrong in your attempt? If you want to remove the row from the sheet you may use: sheet.deleteRow(1) — Riyafa Abdul Hameed
deleting is not the problem. What Marshmallow is saying below is the problem — StMit

Unknown Unknown · Accepted Answer · 2016-04-13T00:21:54

You are using a simple trigger onEdit. Such triggers run without authorization and therefore are subject to several restrictions, such as:

They can modify the file they are bound to, but cannot access other files because that would require authorization.

This is why SpreadsheetApp.openById fails to execute in your code.

Solution: rename the function to something other than onEdit, and add an installable trigger running on edits. An installable trigger runs as you (i.e., the user who created it), and therefore can access any files that you can access.

How to move a row from one google spreadsheet to another based on a value in a cell?

1 Answers