Script stopping, may be due to onEdit() Spreadshee

2019-08-15 06:29发布

问题:

this script stops just at the entry of the last function : addToDocument().

Until this point all is working. I presume a problem due to onEdit() and DocumentApp call?

Note that separately, my addToDocument() works perfectly.

function onEdit() {
// simple timestamp -- when a single "t" is entered in a cell, replace it with a timestamp
// see https://productforums.google.com/d/topic/docs/rC6MpQDC7n4/discussion 
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cell = SpreadsheetApp.getActiveRange();
if (cell.getValue() == "t") {
cell.setValue(new Date()); 
}

formatDate() // Some Date formatting using        : 'SpreadsheetApp' call
mefCond()    // Some conditonnal formatting using : 'SpreadsheetApp' call
doCounts()   // Some numéricals opérations, using : 'SpreadsheetApp' call

//At this point the scripts enter in the following function,
//and stops on the first line. Nothing being executed.

addToDocument() // Time stamp on a document using : 'DocumentApp' call
}  

Any ideas ?

Thanks for reading, Eric :-)

回答1:

When OnEdit is run manually it runs with a different set of permissions but while Triggers themselves have specific restrictions as mentioned here. From that page see..

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

Please refer to this for the authorization modes and what you can do under each of them. May be line 2 below is affecting you...

The solution for you I believe is to convert your simple trigger into an installable trigger. Here are details how to install a trigger for your spreadsheet. Nothing will changes with respect to your onEdit() function, you just have to run installation code snippet once to create an installable trigger.

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

And here are the details on permissions and other details. In here it clearly mentions that you can access other services..

For example, the installable open trigger for Google Sheets activates whenever the spreadsheet is opened by any user who has edit access, just like the simple onOpen() trigger. However, the installable version can call services that require authorization. The installable version runs with the authorization of the user who created the trigger, even if another user with edit access opens the spreadsheet