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:
- Some responses added to spreadsheet. Script executed for each. Ok.
- 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.
- 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.
- 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?