How to access data on different Google Spreadsheet through Google Apps Script?

15
votes

I'm writing a Google Apps Script on my Google Site and am trying to use data that is provides on 2 different tabs in a Google Spreadsheet. From what I thought I understood from the documentation I could use all available methods in the SpreadsheetApp class on a Sites script by just using the openById() method.

Anyway here is what I tried to do

function doGet(e) {

    var doc = SpreadsheetApp.openById(SPREADSHEET_ID_GOES_HERE).getActiveSheet();
    SpreadsheetApp.setActiveSheet(doc.getSheetId()[1]);

    //....
}

I get the error

Cannot find method setActiveSheet(. (line 4)

I'm pulling my work off this link: Storing Data in Google Spreadsheets and also the Ui Service section listed under Building User Interfaces.

Anybody seeing what I'm doing wrong in these two lines?

4
google-apps-script
I'd suggest you should make use of the autocomplete feature of the script editor to avoid syntax error or using methods that don't apply... it's an easy way to start without annoying errors and/or typos ;-) - Serge insas

4 Answers

24
votes

setActiveSheet should be used only with the spreadsheet displayed by the UI, a sheet in a spreadsheet you have opened in your browser.

With SpreadsheetApp.openById you are opening a spreadsheet to access its data, but it doesn't open in your browser. It hasn't an UI.

I found this comments in https://developers.google.com/apps-script/class_spreadsheetapp?hl=es-ES#openById :

// The code below opens a spreadsheet using it's ID and gets the name for it.
// Note that the spreadsheet is NOT physically opened on the client side.
// It is opened on the server only (for modification by the script).
var ss = SpreadsheetApp.openById("abc1234567");

Some examples assume your script is running into your spreadsheet. It's not your case, because you are running a script as a service, which should have its own User Interface.

6
votes

I think @megabyte1024 addresses the syntax errors, but in answer to your comment to @YoArgentina:

Do you happen to know of a way to access data on different tabs then through a service not running inside the Spreadsheet?

Does this sort of help?

var ss = SpreadsheetApp.openById(SPREADSHEET_ID_GOES_HERE);
var sheets = ss.getSheets();
// the variable sheets is an array of Sheet objects
var sheet1A1 = sheets[0].getRange('A1').getValue();
var sheet2A1 = sheets[1].getRange('A1').getValue();
1
votes

There is at least one problem in these two lines. The 1st one is that the setActiveSheet method parameters is a Sheet class object and the getSheetId method returns an integer value. By the way this method (getSheetId) is not documented. The 2nd problem can happen if the SpreadsheetApp has no active spreadsheet. In this case there is the "Please select an active spreadsheet first." error. Use the SpreadsheetApp.setActiveSpreadsheet method to set an active spreadsheet.

1
votes

You need to access each sheet separately. 

var ss = SpreadsheetApp.openById(SPREADSHEET_ID_GOES_HERE);
var sheet = ss.getSheets()[0]; // "access data on different tabs"
ss.setActiveSheet(sheet);