通过电子邮件发送表单并在电子表格中跟踪回复 [英] Send form by email and track responses in spreadsheet

问题描述

我正在使用Google Apps脚本发送电子邮件 - 我知道如何做到这一点。我想嵌入一个是或否响应链接或多项选择问题，并将收件人的回复记录在Google电子表格中。

我该怎么做？

解决方案

此工作流程涉及的组件有：

• 生成和发送带有HTML表单的电子邮件的脚本。


• 该电子邮件的HTML模板，允许我们为每个收件人自定义电子邮件。 >
  
• 一个 doPost（）函数来处理响应。脚本必须）。
  
  
  

  emailTemplate.html

  
  
  

   < div> 
  < form action =<？= scriptUrl？>方法= POST > 
  < table> 
  < tr> 
  < td> 
  < label for =commute>你上班吗？< / label> 
  < / td> 
  < td> 
  < select name =commute> 
  < option>是< / option> 
  < option> No< / option> 
  < / select> 
  < / td> 
  < / tr> 
  < tr> 
  < td> 
  < label for =车辆>如果是，你如何上班？< / label> 
  < / td> 
  < td> 
  < input type =checkboxname =vehiclevalue =Bike>我有一辆自行车< br> 
  < input type =checkboxname =vehiclevalue =Car>我有一辆车
  < / td> 
  < / tr> 
  < tr> 
  < td> 
  <！ - 隐藏字段是将信息传递给
  服务器端POST处理程序的方便方法。例如，序列号可以
  用于整理特定收件人的响应。 - > 
  < input type =hiddenname =serialvalue =<？= serialNumber？> /> 
  < / td> 
  < td> 
  < input type =submitvalue =提交/> 
  < / td> 
  < / tr> 
  < / table> 
  < / form> 
  < / div> 
    

  
  
  

  Code.gs

  
  
  

    // doPost需要电子表格ID，它没有活动电子表格的概念。 
   var _spreadsheetId ='---电子表格ID ---'; 
   
   //添加自定义菜单与选项发送调查
  函数onOpen（）{
   var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
   var entries = [{
   name：发送调查，
   functionName：sendSurvey
  }]; 
   sheet.addMenu（自定义菜单，条目）; 
  }; 
   
   / ** 
   * Build&发送调查，电子邮件中的HTML表单。 
   * / 
  函数sendSurvey（）{
   var recipient = Browser.inputBox（发送调查，输入收件人电子邮件，Browser.Buttons.OK_CANCEL）; 
   if（recipient ==='cancel'）return; 
   
   var subject ='通勤调查'; 
   
   //获取已发布的Web应用程序的URL，以包含在响应的POST中的电子邮件
   var scriptUrl = ScriptApp.getService（）。getUrl（）; 
  如果（！scriptUrl）抛出新的错误（'您必须首先部署为Web应用程序。 
   
   //构建邮件正文
   var template = HtmlService.createTemplateFromFile（'emailTemplate'）; 
   template.scriptUrl = scriptUrl; 
   template.serialNumber = getGUID（）; //生成此响应的序列号
   var html = template.evaluate（）。getContent（）; 
   
   //在调试期间，发送电子邮件至自己。删除此行以进行实际操作。 
   recipient = Session.getActiveUser（）。getEmail（）; 
   
   //发送电子邮件表单
   GmailApp.sendEmail（收件人，主题，需要HTML，{htmlBody：html}）; 
   
   Browser.msgBox（调查发送）; 
  } 
   
   / ** 
   *响应的POST处理程序; 
   * / 
  函数doPost（e）{
   Logger.log（e）; 
   var ss = SpreadsheetApp.openById（_spreadsheetId）; 
   var sheet = ss.getSheets（）[0]; //假设第一张表收集回复
   
   //用时间戳创建一行数据+发布的响应
   var row = [
   new Date（），// Timestamp 
   e.parameters.serial [0]，//序列号
   e.parameters.commute [0]，// Commuter？是/否
   e.parameters.vehicle.join（'，'）// Vehicle 
  ]; 
   
   //确保我们是在电子表格中添加行的唯一人员
   var lock = LockService.getPublicLock（）; 
   //等待最多30秒钟，其他进程完成。 
   var locked = lock.tryLock（30000）; 
   
   if（locked）{
   //保存对电子表格的响应
   var rowNum = sheet.getLastRow（）+ 1; 
   sheet.getRange（rowNum，1，row.length）.setValues（[row]）; 
   
   //释放锁，以便其他进程可以继续。 
   lock.releaseLock（）; 
   var result =Response Recorded：\\\
  + row.join（'\\\
  '）; 
  } 
   else {
   //无法锁定
   result =系统忙，请重试。 
  } 
   
   //以纯文本形式报告POST结果
  返回ContentService.createTextOutput（result）
   .setMimeType（ContentService.MimeType.TEXT）; 
  } 
   
   
   / ** 
   *返回符合rfc4122版本4的GUID / UUID字符串
   *感谢@broofa！ 
   * http://stackoverflow.com/a/2117523/1677912 
   * / 
  函数getGUID（）{
   return'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'。替换（/ [xy] / g，function（c）{
   var r = Math.random（）* 16 | 0，v = c =='x'？r：（r& 0x3 | 0x8） 
   return v.toString（16）; 
  }）; 
  } 
    

  
  
  

  部署

  
  
  

  要使用此调查系统：

  
  
  1. 在您的云端硬盘帐户中创建一个新的电子表格。在第1行中添加Timestamp，序列号，Commuter？和Vehicle的标题。
  
  
  2. 工具 - 脚本编辑器。复制 Code.gs 内容。复制电子表格的ID，并更新文件顶部的 _spreadsheetId 变量。保存。
  
  
  3. 文件 - 新的HTML文件，将文件命名为emailTemplate。复制 emailTemplate.html 内容。保存。
  
  
  4. 发布 - 部署为Web应用程序...让任何人都可以访问，包括匿名。 （在Google Apps域中，您可以将其限制在域中的用户身上。）
  
  
  5. 通过重新加载电子表格或运行 onOpen 函数。
  


  准备好了！您将在电子表格中找到自定义菜单，并显示发送调查命令。

  
  

  I'm using a Google Apps Script to send an email out - I know how to do that. I want to embed a "Yes or No" response link or multiple choice question, and have the recipients' responses recorded in a Google spreadsheet.

  How do I do that?

  解决方案

  The components involved in this workflow are:

  • A script to generate and send an email with an HTML form.
  • An HTML template for that email, which allows us to customize the email for each recipient.
  • A doPost() function to handle responses. The script must be deployed as a Web App.
  • A spreadsheet to collect responses. The script will be contained in the spreadsheet, and extends the spreadsheet UI with a menu for sending a copy of the survey. (It could be adapted for standalone use, without the UI component.)

  Here is an example of such a workflow, conducting a Commuting Survey. Recipients will receive a survey email like this:

  Recipients fill out the form right in their email client, if it supports that capability. Responses will be collected in a spreadsheet, like this:

  ^{_{Create the spreadsheet headers yourself, before running the script.}}

  The "Serial Number" column has been added to illustrate a way to correlate responses with particular respondents; note that some entries repeat. When a survey email is generated, it is given a unique serial number, which is then passed back as a hidden value with the responses. We could extend this system to recognize updates from respondents, for instance.

  Now, the code. (Which is also available as a gist.)

  emailTemplate.html

  <div>
    <form action="<?= scriptUrl ?>" method="Post">
    <table>
    <tr>
    <td>
      <label for="commute">Do you commute to work?</label>
      </td>
      <td>
      <select name="commute">
        <option>Yes</option>
        <option>No</option>
      </select>
      </td>
    </tr>
    <tr>
      <td>
      <label for="vehicle">If "Yes", how do you get to work?</label>
      </td>
      <td>
        <input type="checkbox" name="vehicle" value="Bike">I have a bike<br>
        <input type="checkbox" name="vehicle" value="Car">I have a car
      </td>
    </tr>
    <tr>
      <td>
      <!-- A Hidden field is a handy way to pass information to the
           Server-side POST handler. For example, a serial number could
           be used to collate responses from a particular recipient. -->
      <input type="hidden" name="serial" value="<?= serialNumber ?>" />
      </td>
      <td>
      <input type="submit" value="Submit" />
      </td>
    </tr>
    </table>
    </form>
  </div>
  

  Code.gs

  // doPost needs the spreadsheet ID, it has no concept of "active spreadsheet".
  var _spreadsheetId = '--- Spreadsheet ID ---';
  
  // Add custom menu with option to send survey
  function onOpen() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var entries = [{
      name : "Send Survey",
      functionName : "sendSurvey"
    }];
    sheet.addMenu("Custom Menu", entries);
  };
  
  /**
   * Build & Send Survey, an HTML form in email.
   */
  function sendSurvey() {
    var recipient = Browser.inputBox("Send Survey", "Enter Recipient Email", Browser.Buttons.OK_CANCEL);
    if (recipient === 'cancel') return;
  
    var subject = 'Commuting Survey';
  
    // Get the URL of the published Web App, to include in email for POST of response
    var scriptUrl = ScriptApp.getService().getUrl();
    if (!scriptUrl) throw new Error( 'You must Deploy as Web App first.' ); 
  
    // Build email body
    var template = HtmlService.createTemplateFromFile('emailTemplate');
    template.scriptUrl = scriptUrl;
    template.serialNumber = getGUID();  // Generate serial number for this response
    var html = template.evaluate().getContent();
  
    // During debugging, send emails to self. Remove this line for real operation.
    recipient = Session.getActiveUser().getEmail();  
  
    // Send email form
    GmailApp.sendEmail(recipient, subject, 'Requires HTML', {htmlBody:html} );
  
    Browser.msgBox("Survey Sent");
  }
  
  /**
   * POST handler for responses; 
   */
  function doPost(e) {
    Logger.log(e);
    var ss = SpreadsheetApp.openById(_spreadsheetId);
    var sheet = ss.getSheets()[0];  // Assume first sheet collects responses
  
    // Build a row of data with timestamp + posted response
    var row = [
      new Date(),                     // Timestamp
      e.parameters.serial[0],         // Serial Number
      e.parameters.commute[0],        // Commuter? Yes / No
      e.parameters.vehicle.join(',')  // Vehicle
    ];
  
    // Make sure we are the only people adding rows to the spreadsheet
    var lock = LockService.getPublicLock();
    // Wait for up to 30 seconds for other processes to finish.
    var locked = lock.tryLock(30000);
  
    if (locked) {
      // Save response to spreadsheet
      var rowNum = sheet.getLastRow()+1;
      sheet.getRange(rowNum, 1, 1, row.length).setValues([row]);
  
      // Release the lock so that other processes can continue.
      lock.releaseLock();
      var result = "Response Recorded: \n  "+row.join('\n  ');
    }
    else {
      // Failed to get lock
      result = "System busy, please try again.";
    }
  
    // Report result of POST, in plain text
    return ContentService.createTextOutput(result)
                         .setMimeType(ContentService.MimeType.TEXT);
  }
  
  
  /**
   * Returns an rfc4122 version 4 compliant GUID / UUID string
   * Thanks to @broofa!
   * http://stackoverflow.com/a/2117523/1677912
   */
  function getGUID() {
    return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
      var r = Math.random()*16|0, v = c == 'x' ? r : (r&0x3|0x8);
      return v.toString(16);
    });
  }
  

  Deployment

  To use this survey system as-is:

  1. Create a new spreadsheet in your Drive account. Add headers for "Timestamp", "Serial Number", "Commuter?", and "Vehicle" in row 1.
  2. Tools - Script Editor. Copy the Code.gs content. Copy the ID of your spreadsheet, and update the _spreadsheetId variable at the top of the file. Save.
  3. File - New HTML file, name the file emailTemplate. Copy the emailTemplate.html content. Save.
  4. Publish - Deploy as Web app... Make it accessible by anyone, including anonymous. (In a Google Apps domain, you can restrict it to users in the domain.)
  5. Authorize the script, by reloading your spreadsheet or running the onOpen function in the editor.

  Ready to go! You'll find a "Custom Menu" in your spreadsheet, with a "Send Survey" command.

  这篇关于通过电子邮件发送表单并在电子表格中跟踪回复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！