Google Sheets – Autoincrement Based on Google Form Submission

formulasgoogle sheetsgoogle-apps-script

I'm trying to create an auto-generated unique ID# for each meetings being submitted via a Google Form.

The unique ID# solution must also meet the following requirements.

  • The ID# must be static (not tied to row number).
  • I need to be able to set a starting point other than "1" for the unique ID#'s.

I've tried the following solution, but it created ID#'s based on row # and if a row is removed, it duplicates a unique ID#.

Can I add an autoincrement field to a Google Spreadsheet based on a Google Form?

Best Answer

This can be done with the following script: set a trigger "on form submit" for this function.

The customizable parameters are: the column number where to insert Id, and the initial Id. The function uses Script Properties to keep track of the used Id, so they will not be repeated.

function generateId(e) {
  var columnNumber = 5;
  var initialId = 42;
  var sp = PropertiesService.getScriptProperties();
  var id = sp.getProperty("Event Id") || initialId;
  e.range.offset(0, columnNumber - 1, 1, 1).setValue(id);
  sp.setProperty("Event Id", id + 1);
}
Related Topic