No permission when running a Google App Script from onEdit trigger-CodePudding

I have a simple google apps script that does something from the current Spreadsheet that I'm working on, and then transfers that data to another Spreadsheet just like below.

function unplannedSubmit()
{
  //Some pre-requisite codes here//
  //Input is transferData, output is sending those data to a certain range in ds_main

  var ds = SpreadsheetApp.openByUrl("URL will be inserted here");
  var ds_main = ds.getSheetByName("Job Management - All");

  ds_main.getRange(1, 1, 5, 10).setValues(transferData);
}

For whatever reason, when I tried to run unplannedSubmit() manually, it runs perfectly fine. I made another code section to determine whether to run these functions or not, and I put them in the onEdit() function which supposedly runs whenever there is an edit to the active Spreadsheet

function onEdit()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ss_submitList = ss.getSheetByName("Jobsheet Change List / Request");
  var ss_unplanList = ss.getSheetByName("Unplanned Job List / Application");

  //Check for unplanned import function
  if (ss_unplanList.getRange("A1").getValue() > 0) unplannedSubmit();
}

But doing this only gives me this error: Error message on trigger

I tried to change my appscript.json file to this, but still doesn't work

{
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets"
  ],
  "timeZone": "Asia/Kuala_Lumpur",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
  }
}

I also tried de-authorize and re-authorize the project to my google account, I tried changing the method from openByUrl to openById, but still can't figure out why I keep getting this error

CodePudding user response：

The onEdit() function is a simple trigger that runs in a limited context that only has access to the current spreadsheet file. In that context, other spreadsheet files are not available.

To make it work, rename onEdit() to something like onEditInstallable() and run it through an installable "on edit" trigger.

Note that the function will run every time you edit any value in the spreadsheet. You should include additional checks so that you only copy data between spreadsheet files when that data has changed.