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.

Tanaike Tanaike · Accepted Answer · 2017-05-04T01:37:48

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.

TypeError: Cannot call method "getRange" of null

1 Answers