getRange Problems, Google Apps Sheets Script

Question

I have been trying to run this code on Google scripts but running into the following error -TypeError: Cannot call 'getRange' method of null ('code' file, line 5)

The code is below. Any help is truly appreciated.

Thanks!

function myFunction() {
 var d = new Date();
 var hours = d.getHours();
 var currentTime = d.toLocaleDateString();
 var counter = SpreadsheetApp.getActiveSheet().getRange('B1').getValues();

 if (hours >= 6 && hours <= 18) {
 var response = UrlFetchApp.fetch("url");
 SpreadsheetApp.getActiveSheet().getRange('S' + counter).setValue('Visted at ' + currentTime + ' ' + hours + 'h');
 SpreadsheetApp.getActiveSheet().getRange('B1').setValue(Number(counter) + 1);
 }
}

Does this answer your question? getActiveSpreadsheet() always returns null — tehhowch
Hey @홍석현 were you able to solve your issue with my answer? Please consider upvoting/accepting it in case it was useful to you. Otherwise, kindly describe the issue you are having, so we may help you. Cheers — carlesgg97

carlesgg97 carlesgg97 · Accepted Answer · 2020-01-07T11:23:26

The script you are using is not bound to a Spreadsheet. In this case, the function getActiveSheet() will behave just as getActiveSpreadsheet():

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

Effectively returning null.

Solution

In order to be able to interact with your Spreadsheet you can:

Create a brand new script bound to the Spreadsheet you are trying to work with. To do so, from the Sheets UI (with your Spreadsheet open), go to Tools>Script editor. There, you will be able to use the functions described above.
Use any of the open() functions defined in SpreadsheetApp from a standalone script in order to interact with your Spreadsheet.

getRange Problems, Google Apps Sheets Script

1 Answers

Solution