[Google Apps Script] Google Form submit 後發送電郵

Google Form 是中小企常用的工具, 雖然它能夠方便地生成表格並以Excel 格式處理收集回來的資料. 但若有比較複雜的運作 (例如自訂validation rules, user control / component 等), 則較力不從心, 而要透過Scripting 來實現. 在此會示範如何利用Google App Script 來發送電郵.

Google App Script 就像Microsoft Office 的 VBA 般, 可在其檔案中建立專案 (Project), 進行編程. 而它內建不同的API call, 並透過Trigger 觸發Script 執行. 在此示範中, 會設定當Google Form submit 後會自動發送電郵給用家, 方法如下:

  1. 開啟Script Editor.
    開啟 Google Form 對應 response 的 Sheet, 選取Tools > Script Editor.
  2. 輸入代碼.
    於Script Editor 中, 選取Code.gs, 並輸入以下代碼

    // Row no.
    var COURSE_1_ROW_NO=1;
    var COURSE_2_ROW_NO=2;
    var NAME_EN_ROW_NO=7;
    var EMAIL_ADDRESS_ROW_NO=12;
      
    // Email Content settings.
    var EMAIL_SUBJECT="Application Request";
    var EMAIL_TEMPLATE_FILE_NAME="Email_Template_v1.html";
      
    
    function OnSubmit(e) {  
    
      // Get last row data after inserted.
      SpreadsheetApp.flush();
      var sheet = SpreadsheetApp.getActiveSheet();
      var dataRange = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn());
      var data = dataRange.getValues();
      var row = data[0];
      
      // Gererate email content.
      var emailAddress = row[EMAIL_ADDRESS_ROW_NO];   
      var message = generateEmailTemplateContent(row); 
      var subject = EMAIL_SUBJECT;
       
      // Send email and log error if exception throw.
      try
      {  
        Logger.log("Email Address: "+emailAddress+"; Subject: "+subject+"; Message:"+message);
        Logger.log("Remain Email Quota"+MailApp.getRemainingDailyQuota());
        MailApp.sendEmail({
          to: emailAddress,
          subject: subject,
          htmlBody: message
        });
      }
      catch(ex)
      {
        Logger.log(ex);
      }
    }
    
    function generateEmailTemplateContent(row)
    {
      // Get HTML template content.
      var result=HtmlService.createHtmlOutputFromFile(EMAIL_TEMPLATE_FILE_NAME).getContent();
      
      // Generate course list.
      var courseHtml="";
      courseHtml+="<ul>";
      courseHtml+="<li>"+row[COURSE_1_ROW_NO]+"</li>";
      if(row[COURSE_2_ROW_NO]==null)
      {
        courseHtml+="<li>"+row[COURSE_2_ROW_NO]+"</li>";
      }
      courseHtml+="</ul>";
      
      // Replace parameter to row data.
      result=result.replace("[[NAME]]",row[NAME_EN_ROW_NO]).replace("[[COURSE]]",courseHtml);
      return result;
    }
  3. 加入HTML template.
    在Script Editor 中, 選取File > New > HTML File, 並命名為”Email_Template_v1.html”
  4. 輸入HTML Markup.
    選取 Email_Template_v1.html, 並輸入以下代碼:

    <!DOCTYPE html>
    <html>
      <head>
        <base target="_top">
      </head>
      <body>
        <p>[[NAME]],</p>
    <p>
    已經收到閣下報讀以下課程
    [[COURSE]]
    </p>
    <p>
    若有垂詢, 請賜郵
    </p>
    <hr>
    此為電腦生成之郵件, 請勿回覆.
      </body>
    </html>
    
  5. 測試代碼.
    於Function Bar 中選取OnSubmit, 並按Debug 按鈕. 
  6. 開啟Trigger 設定畫面.
    於Script Editor 中選取Edit > All your Triggers.
  7. 設定Trigger.
    在Trigger 版面, 按Add a new trigger, 於Run 中選取 onSubmit, 並於Events 中選取 from SpreadSheet 及 on form submit. 之後按 Save.
  8. 最後於form 中嘗試submit 一次, 當收到電郵時, 表示已經成功.

另外, Google App Script 可以讓user 以MailApp.SendMail() 型式發出100 封電郵, 即是數量上有限額, 使用時須要留意.

參考資料

About C.H. Ling 262 Articles
a .net / Java developer from Hong Kong and currently located in United Kingdom. Thanks for Google because it solve many technical problems so I build this blog as return. Besides coding and trying advance technology, hiking and traveling is other favorite to me, so I will write down something what I see and what I feel during it. Happy reading!!!

Be the first to comment

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.