1

I have 2 google forms which collect responses and save it to appropriate google spreadsheet. For each form I've created identical scripts that generates id for new response and sends mail to respondent with this id and some of his data, and trigger to executing that script while form is subbmitting. When I tested my forms myself everything was ok. But on prod was revealed behaviour. This behavior is as follows:

  1. Some responses added to spreadsheet. Script executed for each. Ok.
  2. Next response added to spreadsheet. Script doesn't generate id for new response and doesn't send mail for respondent. There is no script execution log in 'Executions' section.
  3. Next response added to spreadsheet but script successfully executed but for the previous response. There is an execution completed log for this execution. No errors. For current (last) response nothing happens.
  4. Another scenario for the situation above: next response didn't added in spreadsheet. Script executed for current last record in spreadsheet (not last response actually).

Next errors that I met is new form responses doesn't copies to spreadsheet. I should update form connection with spreadsheet every time the script is delayed (or not executed?).

Note: when I update id field manually the next executions are successfully but not for last response.

No script runtime errors or an exceptions in logs, no errors with form connection to spreadsheet.

Here my script:

 function onFormSubmit(e) {  
  try{
    //Get spreadsheet data
    var ss = SpreadsheetApp.openById(ssId);  
    var responsesSheet = ss.getSheets()[0];
    Logger.log(responsesSheet.getName());

    var lastRow=parseInt(responsesSheet.getLastRow());  
    var uniqueId=getUniqueId(lastRow);

    //get respodent data to email
    var recipient=responsesSheet.getRange(lastRow, 2).getCell(1, 1).getValue();
    var teamName=responsesSheet.getRange(lastRow, 7).getCell(1, 1).getValue();

    Logger.log('Participant '+teamName+' was assigned with id: '+uniqueId);
    //  setting uniqueid for form response
    responsesSheet.getRange(lastRow, 3).getCell(1, 1).setValue(uniqueId);  

    Logger.log('confirmation will be send to '+recipient);  
    submitConfirmation(recipient, uniqueId, teamName);
  }
  catch(err){
    Logger.log(err);
  }
}

/*Returns unique 6 digits number composed from formatted input number and random 3 digits number*/
function getUniqueId(number){
  var formattedNumber = getFormattedNumber(number);
  var random = getRandomThreeDigitsNumber();
  var uniqueId=formattedNumber+random;

  return uniqueId;
}

//Returns formatted 3 digits number
function getFormattedNumber(number){
    if(number<10){
      return "10"+number;    
    }else if(number<100){
      return "1"+number; 
    }else{
      return number;
   }  
 }

//Returns random 3 digits number
function getRandomThreeDigitsNumber(){
  return Math.round(Math.random()*(999-100)+100);
}

function submitConfirmation(recipient, uniqueId, teamName){
  var subject = 'Subject';
  var body='';
  var htmlBody = 'htmlBody';
  var options={
    htmlBody: htmlBody,
    name: 'name',
  }

  MailApp.sendEmail(recipient, subject, body, options);
  Logger.log('confirmation was sent to '+recipient);
//  sendEmail(recipient, subject, body, options);  
}

What is the reason of this behaviour and script delay? How to ensure the stability of processing form responses? Can anyone explain what does it happen?

Karina
  • 21
  • 3
  • If you have attached two Forms to a single Spreadsheet, you should have two sheets, one for the responses of each Form. Can you confirm whether that's the case? Also, did you install two `onFormSubmit` triggers, one for each Form? If that's the case, I assume the corresponding functions don't have the same name (otherwise, this might cause problems)? Finally, why are you not using the [event object](https://developers.google.com/apps-script/guides/triggers/events#form-submit) for the `onFormSubmit`, in order to get the response data? – Iamblichus Jun 08 '20 at 13:55
  • This might be your problem: https://stackoverflow.com/questions/54834837/how-can-i-be-getting-multiple-unwanted-event-blocks-from-the-same-onformsubmit-t – Cooper Jun 08 '20 at 19:38
  • @Iamblichus, I have two forms, two spreadsheets and two single scripts projects for each form which deployed it as a web app. Yes, I installed onFormSubmit triggers for each project. At first there was one spreadsheets with two separate sheets for responses of each form, but after the first cases of that bugs I began to use different tables. I didn't see the need to use onFormSubmit event object because I thought that answers are added to the spreadsheet automatically and I can just take the necessary data from there. – Karina Jun 09 '20 at 11:53
  • So the issue is that not all form responses are getting added to the attached spreadsheet (the fact that you have two forms/spreadsheets/triggers, in this case, would be irrelevant I think, since both don't interact with each other in any sense). I would consider reporting this in [Issue Tracker](https://issuetracker.google.com/components/191640), if you can provide a way to reproduce this. – Iamblichus Jun 09 '20 at 12:11
  • About the event object, it could be useful to (1) send the email without relying on the spreadsheet, and (2) get a unique `ID` without calling your function `uniqueId`, since [FormResponse](https://developers.google.com/apps-script/reference/forms/form-response) class has an `ID` property (see [FormResponse.getId()](https://developers.google.com/apps-script/reference/forms/form-response#getId())). I think you could use this to greatly simplify your code, what do you think? – Iamblichus Jun 09 '20 at 12:12

0 Answers0