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

Question

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?

Answer 1

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.

Answer 2

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();