Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

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.

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

}
share|improve this answer

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 
  lock.waitLock(3600);

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

  // Release the lock
  lock.releaseLock();

  // add key to sheet
  sh.getRange(key, 3).setValue(key);
}
share|improve this answer
    
Sorry to say, but this solution does not work with my code. –  Trendmatcher Jul 10 '13 at 18:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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