CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-apps
everspader
everspader

Reputation: 1710

How to access data from an external spreadsheet with Google Apps Script

I have a spreadsheet with some data tables that I want to use in other spreadsheets script. How can I call this spreadsheet by its ID and access the data?\

From the documentation, I know that custom functions are not allowed to access other spreadsheets, a solution would be to put this function in a menu and run it, since it'd ask for the user's authorization. But this is not an option because I am using the data with the built-in method onEdit().\

Also, I tried accessing the spreadsheet via onOpen() since it's not a custom function but still no success. Any other solution?

My code:

function onOpen(){
  ss = SpreadsheetApp.getActiveSpreadsheet();
  src = SpreadsheetApp.openById("spreadsheetID");

Error message:

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

And appscript.json

{
  "timeZone": "Europe/Paris",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets"
    ]
}

Lastly, I also tried creating an installable trigger with this code:

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('myFunction')
      .forSpreadsheet(ss)
      .onOpen()
      .create();
}

But the trigger does not seem to be called upon opening the sheet.

Upvotes: 0

Views: 1855

Answers (2)

ziganotschka
ziganotschka

Reputation: 26796

As you already realized, custom functions and simple trigger cannot perform requests that require authorization

  • SpreadsheetApp.openById("spreadsheetID"); is one of those requests

  • You are on the right track with the installable triggers - they can trigger the execution of requests that require authorization

  • I think your problem is the correct implementation of installable triggers

  • function createSpreadsheetOpenTrigger creates an installable onOpen trigger that calls the function myFunction when the sheet is open

  • This means that you need to create the function myFunction first
  • Also, you need to run function createSpreadsheetOpenTrigger() once manually - to install the trigger

Sample complete code:

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('myFunction')
      .forSpreadsheet(ss)
      .onOpen()
      .create();
}


function myFunction(){
  ss = SpreadsheetApp.getActiveSpreadsheet();
  src = SpreadsheetApp.openById("spreadsheetID");
  Logger.log(src.getActiveSheet().getName());
}

Note:

Instead of implementing the function createSpreadsheetOpenTrigger(), you can install the trigger manually by going on Edit->My project's triggers and bind a trigger of the desired type to the desired function, see also Managing triggers manually

Upvotes: 1

Jason Torpy
Jason Torpy

Reputation: 144

First, in the line src = SpreadsheetApp.openById("spreadsheetID"); did you replace the id with the actual spreadsheet ID? That's the number at the end of the url. I assume you did, but it doesn't show that you did in your example.

Second, just for testing, try to do this action in a regular function (not a trigger) to get things to work. Have you done that? Then you can focus on the spreadsheet access without worrying if the trigger is causing a problem.

Upvotes: 0

Related Questions