Receiving error message TypeError: Cannot call met

Question

I have a few different systems that use the following code to access the underlying spreadsheets.

 var mainLinks = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Main");

They have worked for 6 months, but I am now receiving the below error with a reference to the above line.

 TypeError: Cannot call method "getSheetByName" of null.

Has this method been changed? Any suggestions on how to fix this? Thanks!!

Answer 1

Nothing has changed. You don't have a currently active spreadsheet. According to the documentation for SpreadsheetApp.getActiveSpreadsheet

Returns the currently active spreadsheet, or null if there is none.

So, SpreadsheetApp.getActiveSpreadsheet() returns null, because you have no currently active spreadsheet, then you try to call a method on null and get the expected error...

Cannot call method "getSheetByName" of null.

A simple fix to avoid the error would be to check for null like this

 var mainLinks;
 var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 if (activeSpreadsheet !== null) {
     mainLinks = activeSpreadsheet.getSheetByName("Main");
 } else {
     // handle case where there is no active spreadsheet
 }

There are many reasons that you might not have an active spreadsheet and we can't tell without more information, but you could try...

sheet = SpreadsheetApp.openById(**your spreadsheet key**).getActiveSheet()

To find the spreadsheet key, open the spreadsheet and look at the browser address bar: the key is the bit between key= and the first non-alphanumeric character, usually a & or # as in #gid or &pli=1.

Answer 2

I do not know this SpreadsheetApp, but the message says that the getActiveSpreadsheet method, where the getSheetByName method is, is null.

Based on the method name, SpreadsheetApp is not enabled. It may have expired, or something like that.