Is there a way to speed up the auto-increment routine in Google Spreadsheets?

by Trendmatcher   Last Updated July 19, 2018 01:03 AM - source

The solutions that have been provided for this question does the job, but when more people submit data at the same time, things go wrong. The same number is used in multiple rows.

Is there a way to speed up the routine, or to lock the form when it is in use?

Answers 2

This little modification to the script already present, will introduce a lock mechanism, preventing duplicate entries:

function onFormSubmit(e) {
  // set sheet
  var sh = SpreadsheetApp.getActiveSheet();

  // Get a public lock on this script
  var lock = LockService.getPublicLock();

  // Wait for 1 second 

  // get active cell / key
  var key = sh.getActiveRange().getRowIndex();

  // Release the lock

  // add key to sheet
  sh.getRange(key, 3).setValue(key);
Jacob Jan Tuinstra
Jacob Jan Tuinstra
July 10, 2013 09:52 AM

Try this - it uses the event handler and submitted data to identify the correct response and row, so it isn't affected by race conditions. A test function is included.

I've also submitted this code to the original question, but felt that the issue of multiple entries at the same time merited a response. This technique could be used to similar effect for all forms dealing with high traffic and needing connected scripts.


 * This function extracts the relevant properties from the event handler,
 * then uses them to get the uniqueID and record the response
 * @param {Object} e The event parameter for form submission to a spreadsheet;
 *     e has the following properties values, range, namedValues

function onFormSubmit(e) {
  var uniqueID = getUniqueID(e.values)
  recordResponseID(e.range, uniqueID);

 * Records the unique ID for this response to the correct cell.
 * @param  {Object} eventRange Range in which the response is written
 * @param  {Integer} uniqueID   Unique id for this range
function recordResponseID(eventRange, uniqueID){
  var row =  eventRange.getRow();
  var column = eventRange.getLastColumn() + 1;
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(row, column).setValue(uniqueID);


 * Returns a unique ID for the response, by finding the actual Response that
 * has the same properties.
 * @param  {Array} eventValues Array of: Timestamp_string, form_response_value...
 * @return {Integer}             The unique id (by 1 based array position) of the Response
function getUniqueID(eventValues) {
  var isMatch = false;
  var eventItems = eventValues.slice(1);
  var eventTimestamp = new Date(eventValues.shift());

  // get the responses that match the submitted Timestamp
  var responses = FormApp.openById(FORM_ID).getResponses(eventTimestamp);

  //loop backwards through responses (latest is most likely)
  for (var i = responses.length - 1; i > -1; i--){
    var responseItems = responses[i].getItemResponses();
    //check each value matches
    for (var j = 0; j < responseItems.length; j++){
      if (responseItems[j].getResponse() !== eventItems[j]){
      isMatch = true;
    if (isMatch){
      return i+1;

function testOnSubmit(){
  var answers = [
    ["Sue", "39", "Okay I suppose"],
    ["John", "22", "Great"],
    ["Jane", "45", "yeah no"],
    ["Bob", "33", "Super"]

  var form = FormApp.openById(FORM_ID);
  var items = form.getItems();
  for (var i = 0; i < answers.length; i++) {
    var formResponse = form.createResponse();
    for (var j = 0; j < items.length; j++) {
      var item = items[j];
      var itemResponse = item.asTextItem().createResponse(answers[i][j]);

Tom Horwood
Tom Horwood
October 04, 2013 11:55 AM

Related Questions

Awesome table function

Updated December 07, 2017 08:03 AM

Google Sheets split multi-cell into new rows

Updated January 29, 2018 22:03 PM