Google script doGet TypeError: Impossible to call method "getSheetByName" of null

Question

I have a spreadsheet bound google apps script and I would like to use the doGet function to control the spreadsheet/mail sending from the outside. Basically a user gets a mail onFormSubmit with two links (I'm trying with the dev version of my code):

https://script.google.com/a/macros/..mydomain.../s/...myID.../dev?line=6&answer=ok
https://script.google.com/a/macros/..mydomain.../s/...myID.../dev?line=6&answer=no

The function is the following:

function doGet(e){
  var sp = PropertiesService.getScriptProperties();
  var ssId = sp.getProperty('thisSpreadsheet');
  var sheetName = sp.getProperty('richiestaFerieSheetName');
  Logger.log(ssId + ' ' + sheetName)
  var ss = SpreadsheetApp.openById(ssId);
  Logger.log(ss + ' ' + ss.getName());
  var foglioRichiestaFerie = ss.getSheetByName(sheetName);
  foglioRichiestaFerie.setActiveRange((foglioRichiestaFerie.getRange(e.parameter.line, 1)));
  handleRequest(e.parameter.answer=='ok' ? true : false, false);
}

But I get the TypeError mentioned in the title. The log correctly shows ssId, sheetName, ss and ss.getName(), so I can't understand why the getSheetByName method is not working properly.
Any hint?
Thanks for your attention!

All I can think of, is that either the sheet name retrieved doesn't match the name of the actual sheet in the spreadsheet, or this is an error msg that doesn't match the real problem. Hard code the sheet name and comment out everything but the line that gets the ss and the line that gets the sheet by name, and see what happens. — Alan Wells

Serge insas Serge insas · Accepted Answer · 2015-05-15T18:24:42

Could you try the code like below and see what happens ?

first run init then use the url with parameters as you did.

function init(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  PropertiesService.getScriptProperties().setProperty('richiestaFerieSheetName',ss.getSheets()[0].getName());
  PropertiesService.getScriptProperties().setProperty('thisSpreadsheet',ss.getId());
}

function doGet(e){
  var sp = PropertiesService.getScriptProperties();
  var ssId = sp.getProperty('thisSpreadsheet');
  var sheetName = sp.getProperty('richiestaFerieSheetName');
  Logger.log(ssId + ' ' + sheetName)
  var ss = SpreadsheetApp.openById(ssId);
  Logger.log(ss + ' ' + ss.getName());
  var foglioRichiestaFerie = ss.getSheetByName(sheetName);
  foglioRichiestaFerie.setActiveRange((foglioRichiestaFerie.getRange(e.parameter.line, 1))).setValue('succes');
  var answer = handleRequest(e.parameter.answer=='ok' ? true : false, false);
  return ContentService.createTextOutput(answer).setMimeType(ContentService.MimeType.TEXT);
}

function handleRequest(result){
  Logger.log('result = '+result);
  return 'result is '+result;
}

Using this url (my test webapp) : https://script.google.com/a/macros/insas.be/s/AKfycbxsbr6uBB1B22uYUipmSG-blLVrJ0JB_hmFBy3kLGt7ZQlI8pw/exec?line=6&answer=ok

you should get this result in your browser :

enter image description here

and this one in the first sheet

enter image description here

Google script doGet TypeError: Impossible to call method "getSheetByName" of null

1 Answers