How to access Spreadsheet from a Google Forms subm

问题:

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.

回答1:

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:

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?.

回答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)
  }
}