-1

I'm attempting to access data from one Google Sheets spreadsheet in order to update entries in another Google Sheets spreadsheet.

The code I've written to accomplish this so far is as follows:

var ss1 = SpreadsheetApp.openById("123456789").getSheetByName('Sheet1');

function onEdit (e) {
  var sheet = e.source.getActiveSheet();
  sheet.getRange("C4").setValue(ss1.getUrl());
}

But I'm getting the following error when I edit an entry in the target spreadsheet to trigger the script:

Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets at unknown function

How do I obtain permissions to run the openByID() method or is there a better way to access an external Google Sheets spreadsheet's data?

Rubén
  • 34,714
  • 9
  • 70
  • 166
slzkn
  • 1
  • 1
  • 2
    onEdit(e) suggests a simple trigger. You can't do anything that requires permissions with a simple trigger. You might be able to accomplish this with an installable trigger: https://developers.google.com/apps-script/guides/triggers/installable – Cooper Mar 25 '20 at 18:22
  • Creating an installable trigger did the trick - thanks! It's worth mentioning that I got it to work using the openByUrl() method instead of openByID() but I'm assuming it would work with either. – slzkn Mar 25 '20 at 18:47
  • Does this answer your question? [google script openById : You do not have permission to perform that action](https://stackoverflow.com/questions/27744929/google-script-openbyid-you-do-not-have-permission-to-perform-that-action) – Andres Duarte Mar 26 '20 at 08:46

1 Answers1

0
/*
 * The trigger function to call
 */
function updateOtherSheet(e) {
  var ss1 = SpreadsheetApp
  .openById("123456789")
  .getSheetByName('Sheet1');

  var sheet = e.source.getActiveSheet();
  sheet.getRange("C4").setValue(ss1.getUrl());

}

/*
 * Install the trigger by script `Run once`
 * or use the edit menu 'current project triggers'
 */
function createTrigger() {
  ScriptApp
  .newTrigger('updateOtherSheet')
  .forSpreadsheet(ss) // <- spreadsheet object var goes here !not string
  .onEdit()
  .create()
}
dared
  • 38
  • 2