Script won't open another spreadsheet properly

Question

My script quits when I use openById().

The script is supposed to detect when a user has modified two adjacent columns in a master list spreadsheet (i.e. user enters the name and date of a job on a list of jobs). It then invokes a function to create a new spreadsheet (a copy of an existing template spreadsheet). The new spreadsheet isn't being created.

I've stripped my code down to the bare bones and narrowed down the problem using the logger. I'm not quite sure what's going on here, but nothing after my variable assignment of openById() is getting logged.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = e.range;
  //var newId;
  //var newName;
  //I've removed the last two variables for debugging purposes. They'll be used to name the new spreadsheet.

  Logger.log("1");

  //Check if edit occurred in relevant range
  if((range.getColumn() !== 1) && (range.getColumn() !== 2)) return;

  Logger.log("2");

  //Check if both columns were filled after edit
  if((range.getColumn() == 1) && (range.offset(0,1).isBlank() == true)) return;
  if((range.getColumn() == 2) && (range.offset(0,-1).isBlank() == true)) return;

  Logger.log("3");

  //Temporarily removed but this is for naming the new spreadsheet
  /*if(range.getColumn() == 1) newName = range.offset(0,7).getValue();
  if(range.getColumn() == 2) newName = range.offset(0,6).getValue();*/

  //Check whether the edits occurred on the jobs list or receptions list (indicated by '2' or '3' in L1)
  if(sheet.getRange('L1').getValue() == 2) newJob();
  if(sheet.getRange('L1').getValue() == 3) newReception();

  Logger.log("11");

  //Once again, only temporarily removed.
  /*SpreadsheetApp.openById(newId).rename(newName);*/


}

function newJob() {
  Logger.log("4");

  var templateSS = SpreadsheetApp.openById("Spreadsheet ID Redacted");
  Logger.log("5");

  var newSS = templateSS.copy("Untitled Job");
  Logger.log("6");

  var originalFolder = DriveApp.getFolderById("Folder ID Redacted");
  Logger.log("7");

  var newSSFile = DriveApp.getFileById(newSS.getId());
  Logger.log("8");

  //Copy file to the correct directory and delete the instance created in root
  originalFolder.addFile(newSSFile);
  Logger.log("9");

  DriveApp.getRootFolder().removeFile(newSSFile);
  Logger.log("10");

  return(newSSFile);

} 

/*Didn't bother pasting newReception here because it's nearly identical to newJob*/

The logger should log all numbers 1-11. It's only logging 1,2,3,4. Something is stopping the script after point number 4 and before point number 5.

Please include your execution transcript and/or logs in the question so that we have some idea of where this is failing and why. — ross
So I'm an idiot. I'm new to Google Apps Script and didn't realize the execution transcript existed. It says "You do not have permission to call SpreadsheetApp.openById. Required permissions: googleapis.com/auth/spreadsheets " Any idea what causes this? After some Googling, it looks like custom functions can't open other spreadsheets. Is there any workaround to this? — Alex S.
Your issue is likely because you're trying to use a simple onEdit(e) trigger for this function. You'll need to use an installable trigger for this if you're trying to access anything outside of the spreadsheet your trigger is running for. Check the installable triggers documentation for the scope of each simple/installable trigger and how to create them. — ross
In fact, I've just added an answer that will set the installable trigger up for you. — ross

ross ross · Accepted Answer · 2019-06-07T14:56:52

You won't be able to do this with a simple trigger (it won't have the right authorization), use an installable trigger for your function onEdit for this purpose instead. This will prompt you for the authorization it needs to run properly.

The below script should set it up for you, all you need to do is run it once and you should be good to go.

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

Script won't open another spreadsheet properly

1 Answers