0

We have a google form we provide to customers to fill out. Upon completion it sends an email with some details to them. The problem is that when a customer uses it, it sends the same email to the customer anywhere from 1 to 5 times. (This doesn't occur when we try to recreate the issue in the office).

We have narrowed the cause down to it being a problem with the trigger in the google sheet that is paired with the form.

For each completion of the form, the trigger goes off several times at an interval of 0~3 seconds. (image of email log https://drive.google.com/file/d/1j72nmR_uEMt3WTROSN534NulhkzJuiOv/view?usp=sharing)

We believe that this is a bug with the GAS trigger. We tried to get support from Gsuite but they dont deal with this kind of stuff apparently.

Any ideas?

Heres a copy of the code

//オリジナルデータ取得
var Original_Data = 
//オリジナル　ソート　データ取得
var Original_Data_sorted = 

var TimeStamp_CellNo = 1;
var Request_CellNo = 2;
var RequestName_CellNo = 3;
var StudentID_CellNo = 4;

var ComfirmationNumber_CellNo = 5;
var CancelName_CellNo = 6;

var AddName_CellNo = 7;
var AddDate_CellNo = 8;
var AddStartTime_CellNo = 9;
var AddDuration_CellNo = 10;

var RescheComfirmationNumber_CellNo = 11;
var RescheName_CellNo = 12;
var RescheDate_CellNo = 13;
var RescheStartTime_CellNo = 14;
var RescheDuration_CellNo = 15;
var Adress_CellNo = 16;

var AddDuration_Calc_CellNo = 17;
var AddStartTime_Plantext_CellNo = 18;
var AddEndTime_CellNo = 19;

var RescheDuration_Calc_CellNo = 20;
var RescheStartTime_Plantext_CellNo = 21;
var RescheEndTime_CellNo = 22;

function Request_Reply_Operation() {
//最新情報を取得
var Request_Input_Form = SpreadsheetApp.openById("blah blah").getSheetByName("Request Input Form");
var Request_List = SpreadsheetApp.openById("blah blah").getSheetByName("Request List");


 const NumOfLines = Request_Input_Form.getLastRow();


var loop_cnt = 0;   
var temp_date;
var temp_start;
var temp_end;
var temp_formula;
var temp_cellNo;

var to;  
var subject;  
var body;

var RequestName;
var ComfirmationNumber;
var Student_ID;

 //レッスン終了時間算出の式を挿入
 Request_Input_Form.getRange(NumOfLines, 
 AddStartTime_CellNo).setNumberFormat("h:mm");
 Request_Input_Form.getRange(NumOfLines, 
 RescheStartTime_CellNo).setNumberFormat("h:mm");

temp_formula = "=SUM(J" + NumOfLines + ")/1440";
temp_cellNo = "Q"+NumOfLines;
  Request_Input_Form.getRange(temp_cellNo).setValue(temp_formula);  
  Request_Input_Form.getRange(temp_cellNo).setNumberFormat("h:mm");

    temp_formula = "=TEXT(I" + NumOfLines + ",\"h:mm\")";
    temp_cellNo = "R"+NumOfLines;
    Request_Input_Form.getRange(temp_cellNo).setValue(temp_formula);

    temp_formula = "=TEXT(SUM(I" + NumOfLines + ",Q" + NumOfLines + "),\"h:mm\")";
    temp_cellNo = "S"+NumOfLines;
    Request_Input_Form.getRange(temp_cellNo).setValue(temp_formula);


    temp_formula = "=SUM(O" + NumOfLines + ")/1440";
    temp_cellNo = "T"+NumOfLines;
    Request_Input_Form.getRange(temp_cellNo).setValue(temp_formula);  
    Request_Input_Form.getRange(temp_cellNo).setNumberFormat("h:mm");

    temp_formula = "=TEXT(N" + NumOfLines + ",\"h:mm\")";
    temp_cellNo = "U"+NumOfLines;
    Request_Input_Form.getRange(temp_cellNo).setValue(temp_formula);

    temp_formula = "=TEXT(SUM(N" + NumOfLines + ",T" + NumOfLines + "),\"h:mm\")";
    temp_cellNo = "V"+NumOfLines;
    Request_Input_Form.getRange(temp_cellNo).setValue(temp_formula);


    //タイムスタンプ
    var TimeStamp = new Date(Request_Input_Form.getRange(NumOfLines,       TimeStamp_CellNo).getValue());

    //アドレスを取得
    var Adress = Request_Input_Form.getRange(NumOfLines,             Adress_CellNo).getValue();

    //リクエストを見る
    var Request = Request_Input_Form.getRange(NumOfLines,       Request_CellNo).getValue();

    ////データベースから予約情報を取得
    var ReservationValues = Original_Data_sorted.getRange('A1:Q').getValues();
    var ReservationSize = Original_Data_sorted.getLastRow();

    if(Request == "レッスンキャンセル"){
      ////リクエスト生成
      RequestName = Request_Input_Form.getRange(NumOfLines,             CancelName_CellNo).getValue();
ComfirmationNumber = Request_Input_Form.getRange(NumOfLines,       ComfirmationNumber_CellNo).getValue();

for(loop_cnt = 0; loop_cnt < ReservationSize; loop_cnt++){
  if(ReservationValues[loop_cnt][0] == ComfirmationNumber){

    //講師の種類
    Logger.log(ReservationValues[loop_cnt][9]);

    //レッスンの種類
    Logger.log(ReservationValues[loop_cnt][10]);
  }
}

      //Cancel
      Request_List.appendRow([
  Utilities.formatDate( TimeStamp, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm'),
  "Cancel",
  ComfirmationNumber,
  RequestName,
  "",
  "",
  ""
      ]);

      ////メール文生成
      to = Adress;
Logger.log(body);
}else if(Request == "レッスン追加"){

  ////リクエスト生成
  temp_date = new Date(Request_Input_Form.getRange(NumOfLines, 
  AddDate_CellNo).getValue());

//temp_start = new Date(Request_Input_Form.getRange(NumOfLines, 
 AddStartTime_CellNo).getValue());
temp_start = Request_Input_Form.getRange(NumOfLines, 
 AddStartTime_Plantext_CellNo).getValue()


//temp_end = new Date(Request_Input_Form.getRange(NumOfLines,             
AddEndTime_CellNo).getValue());
temp_end = Request_Input_Form.getRange(NumOfLines, 
AddEndTime_CellNo).getValue(); 

RequestName = Request_Input_Form.getRange(NumOfLines, 
AddName_CellNo).getValue();

Request_List.appendRow([
  Utilities.formatDate( TimeStamp, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm'),
  "Add",
  "",
  RequestName,
  Utilities.formatDate( temp_date, 'Asia/Tokyo', 'yyyy/MM/dd'),
  temp_start,
  temp_end
]);

 ////メール文生成
 to = Adress;

 Logger.log(body);


 }else if(Request == "レッスンキャンセルと追加"){

////リクエスト生成
temp_date = new Date(Request_Input_Form.getRange(NumOfLines, 
 RescheDate_CellNo).getValue());

//temp_start = new Date(Request_Input_Form.getRange(NumOfLines, 
 RescheStartTime_CellNo).getValue());
temp_start = Request_Input_Form.getRange(NumOfLines, 
 RescheStartTime_Plantext_CellNo).getValue()

//temp_end = new Date(Request_Input_Form.getRange(NumOfLines, 
 RescheEndTime_CellNo).getValue());
temp_end = Request_Input_Form.getRange(NumOfLines, 
 RescheEndTime_CellNo).getValue(); 

RequestName = Request_Input_Form.getRange(NumOfLines, 
 RescheName_CellNo).getValue();
ComfirmationNumber = Request_Input_Form.getRange(NumOfLines, 
 RescheComfirmationNumber_CellNo).getValue();

for(loop_cnt = 0; loop_cnt < ReservationSize; loop_cnt++){
  if(ReservationValues[loop_cnt][0] == ComfirmationNumber){

    //講師の種類
    Logger.log(ReservationValues[loop_cnt][9]);

    //レッスンの種類
    Logger.log(ReservationValues[loop_cnt][10]);
   }
   }

   //Cancel
   Request_List.appendRow([
  Utilities.formatDate( TimeStamp, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm'),
  "Cancel",
  ComfirmationNumber,
  RequestName,
  "",
  "",
  ""
  ]);

   //Add
   Request_List.appendRow([
  Utilities.formatDate( TimeStamp, 'Asia/Tokyo', 'yyyy/MM/dd HH:mm'),
  "Add",
  "",
  RequestName,
  Utilities.formatDate( temp_date, 'Asia/Tokyo', 'yyyy/MM/dd'),
  temp_start,
  temp_end
  ]);

  ////メール文生成
  to = Adress;
  Logger.log(body);


 }else{//予約状況確認


   RequestName =Request_Input_Form.getRange(NumOfLines, 
  RequestName_CellNo).getValue();
  var StudentID = Request_Input_Form.getRange(NumOfLines, 
 StudentID_CellNo).getValue();
   var String = "";

   for(loop_cnt = 0; loop_cnt < ReservationSize; loop_cnt++){
   //Logger.log(ReservationValues[loop_cnt][16]);
   //if((ReservationValues[loop_cnt][5] == RequestName)&& 
  (ReservationValues[loop_cnt][16] == StudentID)){
  if(ReservationValues[loop_cnt][16] == StudentID){
    temp_date = new Date(ReservationValues[loop_cnt][2]);
    temp_start = new Date(ReservationValues[loop_cnt][3]);
    temp_end = new Date(ReservationValues[loop_cnt][4]);

    String = String
    + "予約番号："
    + ReservationValues[loop_cnt][0]
    + "  >> "
    + Utilities.formatDate( temp_date, 'Asia/Tokyo', 'yyyy/MM/dd')
    + "  "
    + Utilities.formatDate( temp_start, 'Asia/Tokyo', 'HH:mm')
    + "～"
    + Utilities.formatDate( temp_end, 'Asia/Tokyo', 'HH:mm')
    + "\r\n";
    }
   }

   ////予約情報からメール文生成
   to = Adress;
   Logger.log(body);

 }

 var log_sheet = 


 SpreadsheetApp.openById("blah blah").getSheetByName("mail_log");
 var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
 Logger.log("Remaining email quota: " + emailQuotaRemaining);

 var temp_log = "Remaining email quota: " + emailQuotaRemaining;
 var now = new Date();
 log_sheet.appendRow([now,temp_log,to]);
 GmailApp.sendEmail(to, subject, body); //メールを送信

}
Zeabra
  • 1
  • 1
  • Can we see the mailing script that is triggered when the form is submitted? – Aung49 May 24 '19 at 03:20
  • I added the code. Any advice would be appreciated. – Zeabra May 27 '19 at 05:34
  • Thanks for the edit. Can I assume that your `Request_Reply_Operation()` is triggered by `onFormSubmit()` and not `onEdit()`? Because triggering by `onEdit()` may cause the script to be run any time a cell value changes, even if in the same session. – Aung49 May 28 '19 at 01:34
  • Yes its set to form submit. – Zeabra May 29 '19 at 04:45

1 Answers1

0

Spurious onFormSubmit Triggers

You should check out this question. My guess is that your getting spurious triggers. I get them a lot and the way that I eliminate them is discussed in here. I just made a form today that had the same problem.

Here's the script I used this morning.

function sendMeAMessage(e) {
  var quota=Number(getGlobal('msgquota'));
  if(e.values && e.values[1] && e.values[2] && e.values[3] && e.values[4] && quota>0) {
    var data=e.namedValues;
    var html=Utilities.formatString('You have received a message from: <br />Name: <strong>%s</strong><br />Email: <strong>%s</strong><br />Subject: <strong>%s</strong><br />Message:<br /><strong>%s</strong><br />Time: <strong>%s</strong><br />Quota: <strong>%s</strong>',data.Name,data.Email,data.Subject,data.Message,data.Timestamp,quota-1);
    GmailApp.sendEmail('receipient','Message from Google Form','', {htmlBody:html});
    setGlobal('msgquota',quota-1);
  }
}

You just need to have at least one required question because fortunately the spurious triggers don't seem to have any questions answered.

If this wasn't the problem I'm sorry to bother you.

Cooper
  • 59,616
  • 6
  • 23
  • 54