0

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?

Improve this question
7
  • 3
    The sheet with the name you specify doesn't exist in the workbook you are using. Commented Sep 16, 2018 at 19:20
  • I use my sheet's name. I just used a demo name (Sheet name) here. Commented Sep 17, 2018 at 4:48
  • When you say "run this script", do you mean run from the Script Editor, or run the WebApp? Commented Sep 17, 2018 at 5:00
  • run from the Script Editor. Commented Sep 17, 2018 at 5:03
  • I use my sheet's name. I just used a demo name (Sheet name) here. Regardless, The sheet name you entered in your actual script is wrong. Try changing your sheet name to something simpler like s1 Commented Sep 17, 2018 at 8:04

1 Answer 1

Reset to default
2

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. enter image description here

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. enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

var sheetName = "BDCSylhet"; var sheetId = "1_yHKdQeaq6EJsnjnK-Os77mukmA2qOJw6qqRSKVbTD4"; These are my sheetName and sheetId value. Correct value. Still facing the same problem.
problem solved. I needed to add the values to the convert function too.
Oops. My bad. But I'm glad you've got a result from it. Well done.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.