SpreadsheetApp.getActive() returns null for form

Question

I have a Google Form which contains the following script. Each time it fails because SpreadsheetApp.getActive() returns null. Any ideas as to why?

function onOpen(e) {
  var courseSheet = SpreadsheetApp.getActive().getSheetByName("Courses");
}

The error I get is TypeError: Cannot call method "getSheetByName" of null. (line 6, file "")

I have found lots of examples online which shows usage in this way so I'm sure it's possible. Could it be a permissions issue with the spreadsheet that my form is linked to?

there isnt much to do from onOpen in a form. move the code to the spreadsheet. — Zig Mandel, Oct 14 '15 at 05:15
I'm trying to get to a point where I can use a column in a spreadsheet to fill a list in a form. From the spreadsheet can I push values into the form? — Naeem Sarfraz, Oct 14 '15 at 05:43

score 2 · Answer 1 · answered Oct 12 '15 at 10:20

2

I think the problem might be the use of

.getActive()

from a form. There is no active spreadsheet in use when the form is opened. Try instead:

SpreadsheetApp.openById('id')

You can get the id out of the url. looks something like this:

d/10r8Pv03S8qfMiA7dET2KNRmy0DEEExxPld42HjW-aX2c/

The numbers and letters between the slashes "/" are the id.

answered Oct 12 '15 at 10:20

Albrecht

135
1
8

Using `openById` results in a permissions error. `You do not have permission to call openById` – Naeem Sarfraz Oct 12 '15 at 10:23
Can't reproduce that error. Could be a problem with the limited auth mode of the trigger (see [link](http://stackoverflow.com/questions/31122893/google-apps-script-error-you-do-not-have-permission-to-call-openbyid?lq=1), can you try to set a trigger for the form (onopen) to force reauthorization? – Albrecht Oct 12 '15 at 10:46

rgov · Answer 2 · 2021-02-25T00:09:46.917

Developing on @Albrecht's diagnosis and solution, I made the lookup of the spreadsheet dynamic with:

let form = FormApp.getActiveForm();
let spreadsheet = SpreadsheetApp.openById(form.getDestinationId());

If you are attaching a script to the spreadsheet and want to get the form, you can use:

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let form = FormApp.openByUrl(spreadsheet.getFormUrl());

SpreadsheetApp.getActive() returns null for form

2 Answers2