getActiveSpreadsheet() always returns null

3
votes

Okay, so I was following this tutorial here about how to set up a simple Google App Script and I was stuck at around the 18:50 mark. I was getting an error message while doning the exact same thing as in the video.

I have this code in my Code.gs file:

function myFunction() {

  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();  

  activeSheet.getRange("D2").setValue(99);

}

and a spreadsheet open in another tab. But everytime I try to run "myFunction" I get the following error message:

TypeError: Cannot call method "getActiveSheet" of null. (line 5, file "Code")

So for some reason var ss = app.getActiveSpreadsheet(); apperently returns null. It works when I replace that line with var ss = app.openById('MySpreadsheetIdHere');, but hard coding the id is really not what I want, I'd like to make this script work with any arbitrary opened spreadsheet.

Running the code via Run > Test as add-on doesn't work either.

From a similar question I gathered that it seems to be hard to reproduce this problem, here's exact steps to reproduce:

  1. open up chrome in guest mode, no extensions or anything
  2. sign in to google drive with my google account
  3. create a spreadsheet and keep it open
  4. go to script.google.com and sign in with that same google account
  5. create a new project and copy/paste the code from above
  6. run "myFunction" 6a. confirm permissions when running it for the first time
  7. get the error

Anyone see what I missed?

1
google-apps-scriptgoogle-sheets
Is your script spreadsheet bound? You won't be able to get the active spreadsheet from the standalone GS file. The script can only be created from the spreadsheet itself (Tools -> Script Editor). - Anton Dementiev
Ahh yeah thanks, that worked! If I create the script from the spreadsheet, then that script has access to getActiveSpreadsheet(). But is there a way to write a reusable app script that I can apply to other spreadsheets as well? - Mark Kvetny
Yes, use a file picker or publish it as an add-on and install it to the desired sheets. - tehhowch

1 Answers

4
votes

You missed the step around 2:16 mark create a bounded script: Tools > Script editor...