getting error when opening an external Google Scripts sheet from within a Main Script-CodePudding

I have a Project with the following sheets within it:

KDCLog
KDCAlerts
KDCAssets

Using Google Scripts I open the "active" Log (which is expected to be "KDCLog").

function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();
  if (activeSheet.getName() !== "KDCLog" ) return;

After this is done, I am attempting to open another (external) sheet. To do this, I followed the instructions below:

ref: https://stackoverflow.com/questions/70070131/form-data-submit-to-external-sheet-google-script
      //open external sheet
      var extSS = SpreadsheetApp.openById("Insert External Spreadsheet ID here");
      var extSH = extSS.getSheetByName("Sheet1 External");

I followed the Message here to identify the gid (it is the one with 8 up-arrow-votes) reference: Get Google Sheet by ID?

So what I finally wound up with is:

var extSS = SpreadsheetApp.openById(1591999114);
var extSH = extSS.getSheetByName("KDCAlerts");

But when executing the 2 statements above, I get the following message:

Error   Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets
    at onEdit(Code:150:32)

What am I doing wrong? Any help, hints or advice would be greatly appreciated!

TIA

@Cooper - Thanks for the response!

UPDATE 1:

I made the following change:

//https://docs.google.com/spreadsheets/d/1b5qiNH8c4wg0h1owr-P3OLRuLf-8dTBgRU9cHLBbd2A/edit#gid=1591999114

var extSS = SpreadsheetApp.openById('1b5qiNH8c4xxxxxxxx-P3OLRuLf-8dTBgRU9cHLBbd2A');
var extSH = extSS.getSheetByName("KDCAlerts");

-- AND --

I found a message that referenced this link here: https://developers.google.com/apps-script/concepts/scopes#setting_explicit_scopes

I made the change to the appscript.json file as follows:

{
  "timeZone": "America/Chicago",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
  },
  "oauthScopes": [
      "https://www.googleapis.com/auth/spreadsheets.readonly",
      "https://www.googleapis.com/auth/userinfo.email",
      "https://www.googleapis.com/auth/spreadsheets"
  ]

}

Still getting the error.

UPDATE 2:

Thanks for the response again. I made the changes below.

function creatTrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "onMyEdit").length == 0) {
    ScriptApp.newTrigger("onMyEdit").forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  }
}

function onMyEdit(e) {

  var sh = e.range.getSheet();
  if (sh.getName() == "KDCLog" ) {
    var extSS = SpreadsheetApp.openById('1b5qiNHxxxxxxxxowr-P3OLRuLf-8dTBgRU9cHLBbd2A');
    var extSH = extSS.getSheetByName("KDCAlerts");
  }
  else { return; }

.....
.....
.....
}

The message I am getting now is:

Script function not found: onEdit

What did I do wrong?

TIA

CodePudding user response：

Try something like this:

function creatTrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "onMyEdit").length == 0) {
    ScriptApp.newTrigger("onMyEdit").forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  }
}

function onMyEdit(e) {
  var sh = e.range.getSheet();
  if (sh.getName() == "KDCLog" ) {
    var extSS = SpreadsheetApp.openById("xssid");
      var extSH = extSS.getSheetByName("Sheet1 External");
  }
      
}