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, Jun 07 '19 at 14:34
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: https://www.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., Jun 07 '19 at 14:41
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](https://developers.google.com/apps-script/guides/triggers/installable) for the scope of each simple/installable trigger and how to create them. — ross, Jun 07 '19 at 14:54
In fact, I've just added an answer that will set the installable trigger up for you. — ross, Jun 07 '19 at 14:57

score 0 · Accepted Answer · answered Jun 07 '19 at 14:56

0

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();
}

answered Jun 07 '19 at 14:56

ross

2,684
2
13
22

That's great! Thank you for this. Quick question. As the simple trigger onEdit(e) has the e I can use to reference the edited range in the script, how can I do the same thing with the installable trigger? i.e. what's the equivalent to e.range? – Alex S. Jun 07 '19 at 15:19
You can use it the exact same way with installable triggers. The difference is simple triggers run and require no authorization from the user. Installable triggers request authorization when you set them up, so they can access features that would be out of scope for simple triggers. – ross Jun 07 '19 at 15:21

Script won't open another spreadsheet properly

1 Answers1

Linked