Accessing spreadsheet in Google Script

11
votes

I am writing a google script on top of a spreadsheet. I want to deploy it as a web app. It shows some values. Unfortunately, with my current code, google reminds me:

TypeError: Cannot call method "getSheetByName" of null.

I have no idea where the mistake is.

Here's the code

function doGet(e) {
    var app = UiApp.createApplication().setTitle('Details');
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    var dataFromCell = ss.getRange("B4").getValue();

    var mypanel = app.createVerticalPanel();
    var label = app.createLabel(dataFromCell);

    app.add(mypanel);
    app.add(label);

    return app;
}
2
google-apps-script

2 Answers

32
votes

In standalone webapps you cannot use getActiveSpreadsheet because no one is using it actively... Use SpreadsheetApp.openById('ID') instead, you can get the ID in the url of your spreadsheet like in this example :

https://docs.google.com/spreadsheet/ccc?key=0AnqSFd3iikE3d-------nZIV0JQQ0c1a3dWX1dQbGc#gid=0

between key= and #, ie 0AnqSFd3iikE3d-------nZIV0JQQ0c1a3dWX1dQbGc

3
votes

Not need to use ID , just try this code ( change mygooglelocation with your Spreadsheet name and range of cells. Working very well for me with google maps...

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('mygooglelocation');
  var ss = SpreadsheetApp.getActive();
  var mylocationInfo   = ss.getRange("A2:B4").getValues();