How to access Spreadsheet from a Google Forms submit trigger function

Question

I have a function in Google Forms script to add a Unique ID to the row being added to the associated Sheet. This function is triggered on submission of the form.

Here's one version of the script I've been playing around with:-

function onFormSubmit(e) {
  // Get the active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get the active row
  var row = sheet.getActiveCell().getRowIndex();
  // Get the next ID value.  
  var id = sheet.getRange("Z4").getValue();
  // Check of ID column is empty
  if (sheet.getRange(row, 1).getValue() == "") {
    // Set new ID value
    sheet.getRange(row, 1).setValue(id);
    sheet.getRange("Z4").setValue("Z4"+1)
  }
}

On debugging I get the message:-

TypeError: Cannot call method "getActiveCell" of null. (line 5, file "CreateID")

I've tried cropping the code right down to just a simple setValue, and I get the same issue - "cannot call method...", with pretty much every line except the getActiveSheet.

The trigger of the function works ok, as I get notifications to say that the function itself had failed to execute successfully. I've looked online, tried a few things, but can't find a solution as yet.

So what I'm really after is the correct method of accessing the spreadsheet that the form is posting to. If SpreadsheetApp.getActiveSheet() isn't the right method, what is?

Totally new to this script, last programmed in PLI(!), sorry. Any pointers to existing solutions or other, would be appreciated.

The error message is very clear: `getActiveSheet()` is returning `null`. — Barmar, Jun 25 '15 at 19:45
I guess this means there's no active sheet in `SpreadsheetApp`. — Barmar, Jun 25 '15 at 19:46
open using the id, like this var spreadsheet = SpreadsheetApp.openById("abc1234567"); See https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openById(String) This may also help you, as you are using forms: http://www.labnol.org/internet/google-docs-email-form/20884/ But looks like you are leaning to program, maybe a javascrip tutorial would help you. — eddyparkinson, Jun 26 '15 at 00:29
possible duplicate of [How do I check for null values in javascript?](http://stackoverflow.com/questions/6003884/how-do-i-check-for-null-values-in-javascript) — eddyparkinson, Jun 26 '15 at 00:34
@eddyparkinson - I don't think this is a duplicate of that question. There are [plenty of other questions about spreadsheet form submission triggers](http://stackoverflow.com/search?q=%5Bgoogle-apps-script%5D+form+submission+trigger+is%3Aquestion) which contain examples - I didn't go through all of them to find this exact mistake, but it's probably there. — Mogsdad, Jun 26 '15 at 02:26
@Mogsdad good call - yes it is not the best pick as a duplicate, but bottom line is the person is getting a null pointer exception and does not understand why, or what to do about it. They are asking for help with learning how to program, and there are sites that do a much better job of teaching programming than SO. — eddyparkinson, Jun 26 '15 at 22:03

score 6 · Accepted Answer · edited May 23 '17 at 11:59

When a form is submitted, the trigger function has no active spreadsheet, nor will it have an active cell. (It's not associated with a Sheets UI, so those concepts are meaningless.)

However, the event parameter, e, will provide information about the row that has been added by the Form. See Google Sheets events:

screenshot

e.range contains a Range object covering the cells that have been filled by the form submission that triggered your function. You can backtrack from there to get the sheet.

sheet = e.range.getSheet();

You function becomes something like this:

function onFormSubmit(e) {
  // Get the active sheet
  var sheet = e.range.getSheet();
  // Get the active row
  var row = e.range.getRowIndex();
  // Get the next ID value.  
  var id = sheet.getRange("Z4").getValue();
  // Check of ID column is empty
  if (sheet.getRange(row, 1).getValue() == "") {
    // Set new ID value
    sheet.getRange(row, 1).setValue(id);
    sheet.getRange("Z4").setValue("Z4"+1)
  }
}

Now, you have other problems to deal with.

sheet.getRange(row, 1).getValue() is not necessary; that value has just been handed to you as e.values[0].
However, on a form submission, the first column contains a Timestamp, so it won't ever be empty. If your "ID" value is the first question on the form, then it's actually in column 2, or e.values[1].
The cell Z4 will likely move on you, as form submissions insert new rows into the sheet. It would be better to pull that value from a different sheet - or better yet use the Properties Service to manage it.

To make use of the event, you'll need to simulate it for testing. Read over How can I test a trigger function in GAS?.

That's great, the var sheet = e.range.getSheet(); has worked. We also needed to move the script from the Form to the Spreadsheet and trigger from there. All working now. Thanks. — Wigmore, Jun 27 '15 at 18:24

score -2 · Answer 2 · answered Jun 25 '15 at 19:48

-2

You need to check whether getActiveSheet() succeeded.

function onFormSubmit(e) {
  // Get the active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  if (!sheet) { // No active sheet, nothing to do
    return;
  }
  // Get the active row
  var row = sheet.getActiveCell().getRowIndex();
  // Get the next ID value.  
  var id = sheet.getRange("Z4").getValue();
  // Check of ID column is empty
  if (sheet.getRange(row, 1).getValue() == "") {
    // Set new ID value
    sheet.getRange(row, 1).setValue(id);
    sheet.getRange("Z4").setValue("Z4"+1)
  }
}

answered Jun 25 '15 at 19:48

Barmar

741,623
53
500
612

Thanks Barmar, that's a good idea to check the return value. What I'm really after then is the correct method of accessing the spreadsheet that the form is posting to. If SpreadsheetApp.getActiveSheet() isn't the right method, what is? – Wigmore Jun 25 '15 at 20:53
I don't know, I'm not familiar with the Google Spreadsheet API. – Barmar Jun 25 '15 at 20:54
Before worrying about whether `getActiveSheet()`, it would make more sense to check whether the event parameter `e` was passed in. – Mogsdad Jun 26 '15 at 01:59
@Mogsdad The function doesn't use `e`, so why does that matter? – Barmar Jun 26 '15 at 02:03
Because it should be using `e` to determine what row was added to the spreadsheet by the form submission. There is no "Active" sheet or cell in a spreadsheet form submission trigger. Generally speaking, checking for null is a good idea, however in this case, by following this advice, the OP will get nowhere, as sheet will always be null. This answer isn't helpful for this situation. – Mogsdad Jun 26 '15 at 02:10
Like I said, I don't really know Google Spreadsheet, this is a very generic answer. If you know more about how to use GS, post an answer that shows the proper method. – Barmar Jun 26 '15 at 02:13
Hi - Done. No worries - the Google Apps Script environment uses JavaScript, and most of that is normal... but when it comes to Google's services and automation infrastructure there are some surprises! – Mogsdad Jun 26 '15 at 02:21

How to access Spreadsheet from a Google Forms submit trigger function

2 Answers2

Linked