TypeError: Cannot call method "getRange" of null. (line 20, file "Code")

Question

I keep getting this error when I try to run this script. It's for converting my google sheets data into JSON API.

TypeError: Cannot call method "getRange" of null. (line 20, file "Code")

function doGet(e) {

   var sheetName = "Sheet name";
   var sheetId   = "1234...";

   var book = SpreadsheetApp.openById(sheetId);
   var sheet = book.getSheetByName(sheetName);

   var json = convertSheet2JsonText(sheet);

   return ContentService
        .createTextOutput(JSON.stringify(json))
        .setMimeType(ContentService.MimeType.JSON);
}

function convertSheet2JsonText(sheet) {
  // first line(title)
   var colStartIndex = 1;
   var rowNum = 1;
   var firstRange = sheet.getRange(2, 2, 2, sheet.getLastColumn());
   var firstRowValues = firstRange.getValues();
   var titleColumns = firstRowValues[0];

  // after the second line(data)
   var lastRow = sheet.getLastRow();
   var rowValues = [];
   for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
   var colStartIndex = 1;
   var rowNum = 1;
   var range = sheet.getRange(rowIndex, colStartIndex, rowNum, 
sheet.getLastColumn());
   var values = range.getValues();
   rowValues.push(values[0]);
}

// create json
   var jsonArray = [];
   for(var i=0; i<rowValues.length; i++) {
   var line = rowValues[i];
   var json = new Object();
   for(var j=0; j<titleColumns.length; j++) {
     json[titleColumns[j]] = line[j];
   }
  jsonArray.push(json);
 }
 return jsonArray;
}

Is there anything you can see that would cause this error?

Answer 1

The name of your sheet and/or the sheetID that you have used in your code is incorrect.

---

The error message = "TypeError: Cannot call method "getRange" of null. (line 20, file "Code")"

Line 20: var firstRange = sheet.getRange(2, 2, 2, sheet.getLastColumn());

getRange is called by "sheet"; this means is that "sheet" is null.

You need to use the Script debug function (Run>Debug Function) to analyse your code. Run the debug on doGet - the debug will automatically include convertSheet2JsonText which is called by doGet.

I created some dummy data in a Sheet called "s1x", however, I deliberately assigned a 'sheetName' = "s1". Now when I run the Debug I get the same error message.

Line 20 is part of function convertSheet2JsonText, but the flaw isn't in this function; the error is in doGet.

Below is the debug screen showing the error. Note the value of "sheet" = null.

---

Below is the debug screen showing the values for doGet. Note the value of "sheet" is already null. But also note the value of sheetName = "s1". The only problem is that the actual value of the sheet is "s1x". That explains why "sheet" is null.

When I correct the value of sheetName, the function runs cleanly with no errors.