How to fix 'TypeError: Cannot call method “getRange” of null' in Google Apps Script

1
votes

I'm setting up a script to auto-populate a template document in Google Docs using data from Google Sheets. I keep encountering the 'TypeError: Cannot call method "getRange" of null' error. How do I resolve this error?

From what I've read, this error usually comes from not having the variable defined properly (in this case, the name of the sheet). However, I'm sure the name is the same as the file name in sheets. I've tried changing the name in both places and even making an entirely new document.

4.  var ss = SpreadsheetApp.openById("13YTb-lQzjijkfOKGsZRQf8fcpt_7sMy5_DX7bvlz5Yo");
5.
6.  var sheet = ss.getSheetByName("SAT");
7.
8.  var documentId = DriveApp.getFileById('1xrc7K9WfNiB_33y0xsB33209IZPBcpskThanC_yfzCw').makeCopy().getId();
9.  
10.  DriveApp.getFileById(documentId).setName('SAT Vocab ' + date);  
11.  
12.  var body = DocumentApp.openById(documentId).getBody();
13.
14.  var range = sheet.getRange("A2:A16");

This is the error message I receive:

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

This is the log from Logger.log(ss + ' ' + sheet);: [19-06-20 12:30:52:007 CDT] undefined undefined

1
javascriptgoogle-apps-scriptgoogle-sheetstypeerror
It's either not finding the spreadsheet from your openById() or not finding the sheet with name "SAT", use Logger.log(ss + ' ' + sheet); in your code and add the log details to the question. – ross

1 Answers

0
votes

Issue:

sheet.getRange("A2:A16")

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

The error means sheet is null and null doesn't have a getRange method. Only a real Sheet class does.

As written in the documentation, There is only one reason, where the sheet returned is null.

Returns null if there is no sheet with the given name.

There is only one answer. It's when that sheet name doesn't exist.

Possible solutions:

sheet name refers to the tab name in a spreadsheet/workbook. It does not refer to the filename or document name. By default, the first sheet/tab is named "Sheet1". Try

ss.getSheetByName("Sheet1");

If you still have trouble getting the sheet, check for spaces, non printable characters in the sheet name. Alternatively, Rename the sheet to something simple like S1.