TypeError: Cannot call method "getRange" of null

Question

When looking at the error logs it looks like a line is being called twice and I'm very confused:

// set globals
var ss = SpreadsheetApp.getActiveSpreadsheet();

flushSheet("Daily Overall Summary", "A1");

function flushSheet(sheetname, range) {
  var sheet = ss.getSheetByName(sheetname); Logger.log("sheet is " + sheet);
  var the_cell = sheet.getRange(range);
  var cell_content = the_cell.getValues()[0][0];
  the_cell.clearContent();
  the_cell.setValue(cell_content);

  SpreadsheetApp.flush();
}

When I run this I get:

TypeError: Cannot call method "getRange" of null. (line 8, file "forceFlush")Dismiss

Here are the resulting logs:

[17-05-04 09:12:31:301 HKT] sheet is Sheet
[17-05-04 09:12:31:706 HKT] sheet is null

I'm not sure why Logger.log() is being called twice, but I'm presuming that' a hint to my problem.

GAS is telling me that variable sheet is null but on the first time it calls getRange() on sheet it's not null as you can see by the first log entry.

Why is var sheet equal to null and not a Sheet and why does it appear that things are being called twice? What should I do? I want to get and set the value of A1 with the same value that was in A1 initially because I wnat to do this.

score 1 · Accepted Answer · answered May 04 '17 at 01:37

1

In this case, when flushSheet() is run on Script Editor, At first, flushSheet("Daily Overall Summary", "A1"); is run. And next, flushSheet(sheetname, range) is run. Because for the next, sheetname and range don't have values, it becomes sheet is null. And an error occurs.

As a test, how about following sample? Please run main().

Sample script :

// set globals
var ss = SpreadsheetApp.getActiveSpreadsheet();

// Run main() using Script Editor
function main(){
  flushSheet("Daily Overall Summary", "A1");
}

function flushSheet(sheetname, range) {
  var sheet = ss.getSheetByName(sheetname); Logger.log("sheet is " + sheet);
  var the_cell = sheet.getRange(range);
  var cell_content = the_cell.getValues()[0][0];
  the_cell.clearContent();
  the_cell.setValue(cell_content);

  SpreadsheetApp.flush();
}

If I misunderstand your question, I'm sorry.

answered May 04 '17 at 01:37

Tanaike

181,128
11
97
165

Thank you this now works. Is there a "better" way for me to create and call my function, rather than nest in a function of another name? What is the "conventional" way of defining a function and then calling it? – Doug Fir May 04 '17 at 01:41
In the case of Google, when ``functionName()`` is called, ``function functionName(){}`` is run. Only ``functionName()`` and ``functionName = function(){}`` cannot be directly run by Script Editor. But when there are both ``functionName()`` and ``function functionName(){}``, At first, ``functionName()`` is called and it runs ``function functionName(){}``. Furthermore, ``function functionName(){}`` is run. This may be related to creating custom functions for spreadsheet. I don't know about this. So generally, users make ``function functionName(){}`` and run ``functionName()``. – Tanaike May 04 '17 at 02:02
Thank you for the comment but I don't understand – Doug Fir May 04 '17 at 02:19
I'm sorry for my poor English skill. I think that for calling functions, a better way is to use function with a style of ``function functionName(){}`` at Google Apps Script. Because a mix of ``functionName()`` and `function functionName(){}`` into a script leads to errors. – Tanaike May 04 '17 at 02:30

TypeError: Cannot call method "getRange" of null

1 Answers1