Google-sheets – way to speed up the auto-increment routine in Google Spreadsheets

google sheetsgoogle-apps-scriptgoogle-formsgoogle-sheets-performance

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?

Best Answer

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.

var FORM_ID = "YOUR FORM ID HERE";

/**
 * 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]){
        break;
      }
      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]);
      formResponse.withItemResponse(itemResponse);
    }
    formResponse.submit();
    Utilities.sleep(500);
  }

}
Related Topic