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?

  • It's not clear to what solution are you referring. Nowadays it has six answers. Have you found something that works for you? Commented Apr 25, 2018 at 20:16

2 Answers 2


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]);


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);
  • Sorry to say, but this solution does not work with my code. Commented Jul 10, 2013 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.