Editing one Spreadsheet from another Spreadsheet in Google Apps Script

Question

Question

How do you edit two different spreadsheets from one connected script that is triggered by an onEdit command?

My Case

I am trying to create a master spreadsheet that sends and deletes information to two other spreadsheets. I have an onEdit function that once a checkbox is checked, it will send specified information to another sheet.

What I've Tried

To open the other spreadsheet I first tried the openByID function but through a little research I don't believe it is possible to edit another spreadsheet through a script that is bound to a spreadsheet. So I created a standalone script (named MasterF) that I deployed as a library to store my function so that I can call it later. I added the library to the master spreadsheet and am using it to call the function but it keeps throwing an error saying, "You do not have permission to call SpreadsheetApp.openById." All of my apps scripts have full permissions to edit, delete, etc. I've also made libraries out of the scripts bound to the other spreadsheets I want to connect and added those libraries to the MasterF library to see if that would help. I still get the same error message when the on edit function, despite trying to use a standalone script. Maybe I missed something, or it is entirely possible that I can't do what I'm trying to do.

Script

This the beginning of the custom function that I created. Essentially when an onEdit function is triggered, data from spreadsheet1 in the "submit" sheet, is transferred to spreadsheet2 in the "dashboard" sheet.

function DataSend() {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var submit = ss.getSheetByName("Submit");
  var selldata = submit.getRange('E23:I23').getValues();
  //@NotOnlyCurrentDoc
  var dash = app.openById(<sheetid>).getSheetByName("Dashboard");

Here's the onEdit Function in the script that is bound to spreadsheet1 (not directly a part of the MasterF library) that triggers the datasend function that is kept in the MasterF library.

function onEdit(e) {
  var sheetName = "Submit"
  var range = e.range;

  if (range.getSheet().getSheetName() == sheetName && range.getA1Notation() == 'K23' && range.isChecked()) {
    MasterF.DataSend();
  }
}

Although I'm not sure whether I could correctly understand about your situation, in your situation, when the installable OnEdit trigger instead of the simple trigger is used, what result will you obtain? Ref — Tanaike
You can't edit another Spreadsheet using the simple onEdit trigger, as it requires authorisation and that's a restriction on simple triggers. Change it to an installable trigger as @Tanaike has suggested and this should work with no issue. — Rafa Guillermo
Thank you @Tanaike and Rafa, my line of thinking was incorrect, you don't need a standalone script in this case. I installed an installable trigger and it still did not work. I ended up reading through every line I wrote and the issue was unrelated- I used "<" instead of ">" in an IF statement. The script works fine now and I will add an answer. — C.M.Car

C.M.Car C.M.Car · Accepted Answer · 2021-04-04T20:04:56

To edit two different spreadsheets from one connected script that is triggered by an onEdit command, you need to install a trigger. A simple trigger does not have the permissions required to edit multiple spreadsheets. Ref. In my question I was trying to use a standalone script and multiple libraries. This is unnecessary. All you need to edit multiple spreadsheets from a single script is an installable trigger.

In my case, I used the following function to create an installable trigger:

function EditTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('Edit') 
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

'Edit' is the name of the onEdit function I used to detect the changes made in the first spreadsheet. If an If statement in the onEdit function is true, then it runs a separate function that edits two different spreadsheets. After writing the EditTrigger() function, run it manually in apps script and accept permissions if necessary. Now, when the onEdit function ('Edit') is triggered, it should be able to edit multiple spreadsheets.