Access to spreadsheet in deployed web app

Question

I am trying to access a Google spreadsheet via a Google script, that is published as a web app.

How I created the script:

from the spreadsheet, Tools/Script editor..., Spreadsheet project
and it asked for access to the spreadsheets in GDrive, so overall I assume it is attached to the spreadsheet.

The script:

function doGet() {
  var ss = SpreadsheetApp.getActive();
  // alternative, doesn't work either
  // var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/abcdef/edit");

  var sheets = ss.getSheets();

  var text = ... loop over sheets and do some stuff to get the data ...

  return ContentService.createTextOutput(text);
}

The error message when calling the web app:

TypeError: Cannot call method "getSheets" of null.

The function works (minus the ContentService.createTextOutput of course) when run in the editor.

Any suggestions? Thank you! :-)

Sandro

According to Google, it is "uncommon" for bound scripts to be deployed as Web Apps. It appears that being deployed in this way prevents the script from accessing getActiveSpreadsheet(), etc methods. This makes more sense if we consider getActiveSheet() -- there is no concept of "active sheet" in the context of a web app. So it seems you need to open a sheet by name (or number) and a spreadsheet by URL or Id. — user3717023

Spencer Easton Spencer Easton · Accepted Answer · 2015-03-11T13:05:07

You can't use the getActive() method unless the script is bound to a spreadsheet (ie script was created within the spreadsheet).

In your example with openByUrl() I would check your URL and the permissions to that sheet. Here is working example of what you were trying to do.

function doGet(){
  var ss= SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1XLOVvEo2nzLARwRhsiWUrFMtF0LnofC1PQoWIeLmwgQ/edit#gid=0');
  return ContentService.createTextOutput(ss.getSheets()[0].getName()).setMimeType(ContentService.MimeType.TEXT); 
}

Access to spreadsheet in deployed web app

1 Answers