concurrency issue and loosing form data in a google apps script powered google form

Question

I've been recently having trouble with what I believe to be a concurrency issue when people are submitting the form near the same times, which is resulting in lost data for a google form. I'm already using the Lock service to prevent this issue, but I still seem to have problems. http://googleappsdeveloper.blogspot.com/2011/10/concurrency-and-google-apps-script.html

The form currently has onFormSubmit triggers: formSubmitReply and logMessage. formSubmitReply sends a confirmation to people that submitted the form and logMessage is supposed to back up the information in a separate spreadsheet in the case that rows in the regular spreadsheet get clobbered. It should be extracting the values from the formSubmit event and then appending it to the "log" sheet.

I've included all the current code for the script and replaced emails with place holders. Can I get some help identify anything buggy in the code that could be preventing the form from recording rows in the form?

 function getColIndexbyName(colName){
      var sheet=SpreadsheetApp.getActiveSheet();
      var rowWidth=sheet.getLastColumn();
      var row=sheet.getRange(1,1,1,rowWidth).getValues();//this is the first row
      for ( i in row[0]){
        var name=row[0][i];
        if(name == colName || new RegExp(colName,'i').test(name)){
          return parseInt(i)+1;
        }
      }
      return -1
    }
    function makeReceipt(e){
      /*This is for Student Volunteer auto-confirmation*/
      var ss,sheet, rowWidth, headers, rowWidth,curRow, values, greeting, robot, msg, space, newline;
      curRow=e.range.getRow();
      ss=SpreadsheetApp.getActiveSpreadsheet();
      sheet=ss.getSheetByName("RAW");
      rowWidth=sheet.getLastColumn();
      headers=sheet.getRange(1,1,1,rowWidth).getValues();
      values=sheet.getRange(curRow,1,1,rowWidth).getValues();
      greeting='Hi '+sheet.getRange(curRow,getColIndexbyName('First Name'),1,1).getValue()+"! <br><br>"+ ' '; 
      robot="<i>Below are the responses you submitted. Please let us know if any changes arise!</i> <br><br>";
      msg=greeting+robot;
      space=' ';
      newline='<br>';
      for( i in headers[0]){
        //only write non "Reminders" column values
        if(headers[0][i]!="Reminders"){
          msg+="<b>";
          msg+=headers[0][i];
          msg+="</b>";
          msg+=":";
          msg+=space;
          msg+=values[0][i];
          msg+=newline;
        }
      }
      return msg;

    }



    /**
     * Triggered on form submit
     **/
    function formSubmitReply(e) {
      var ss, row, mailIndex, userEmail, message, appreciation;
      var lock = LockService.getPublicLock();
      if(lock.tryLock(60000)){
        try{
          ss=SpreadsheetApp.getActiveSheet();
          row=e.range.getRow();
          mailIndex=getColIndexbyName('Email Address');
          userEmail=e.values[mailIndex-1];
          message=makeReceipt(e);
          MailApp.sendEmail(userEmail, 'BP Day 2012 Confirmation for'+' '+userEmail,message,{name:"Name", htmlBody:message, replyTo:"example@example.com"});  
          messageAlert100(e);
        } catch(err){
          e.values.push("did not send email"); 
          MailApp.sendEmail(""example@example.com","error in formSubmitReply"+err.message, err.message);
        }
        logToSpreadsheet(e);
      } else {
        //timeOut
        try{
          if(e && e.values){
            logToSpreadsheet(e);
            e.values.push("did not send email");  
          }
        }catch(err){
          MailApp.sendEmail("example@example.com", "error in logging script block "+err.message, err.message)
        }

      }
    }


    /**
     * Triggered on form submit
     **/
    function messageAlert100(e){
      var cheer_list, curRow, cheer_list, cheer_index, cheer, ss=SpreadsheetApp.getActiveSpreadsheet();
      if(e && e.range.activate){
        curRow=e.range.getRow();
      }
      cheer_list=["Congratulations!", "Give yourself a pat on the back!", "Yes!", "Cheers!","It's time to Celebrate!"];
      cheer_index=Math.floor(Math.random()*cheer_list.length);
      cheer=cheer_list[cheer_index];
      if(typeof(curRow) != "undefined" && curRow % 100 ==0){
        MailApp.sendEmail("example@example.com", ss.getName()+": "+cheer+" We now have "+ curRow + " Volunteers!", cheer+" We now have "+ curRow + " Volunteers!");
      }
    }

    /**
     *
     **/
    function logToSpreadsheet(e){
      var ss=SpreadsheetApp.getActiveSpreadsheet(), sh;
      if(!ss.getSheetByName("log")){
        sh=ss.insertSheet("log");
      }
      sh=ss.getSheetByName("log");
      if(e && e.values !==null){
        sh.appendRow(e.values)
      } else {
        sh.appendRow(e);
      }
      Logger.log(e);
    }

Serge insas Serge insas · Accepted Answer · 2012-09-30T08:33:21

There is a very simple approach that I use to avoid concurrency issues with forms, I had to imagine that before GAS offered the lock method. Instead of using the on form submit trigger I use a timer trigger (every few minutes or so) on a function that checks a column for a flag (MAIL SENT) ... if the flag is not present I send the email with processed data, copy to the backup sheet and set the flag. I do this on every row starting from the last one and stop when I find the flag. This way I'm sure all datarows are processed and no duplicate mail is sent. It is actually very simple to implement, your script will need only a few modifications.

Viewed from the users side the result is nearly the same as they receive a mail just a few minutes after their submission.

EDIT : of course in this setup you cannot use e parameter to get the form data but you'll have to read data on the sheet instead... but that's not really a big difference ;-)

concurrency issue and loosing form data in a google apps script powered google form

1 Answers