D4- 如何透过 Google Apps Script 来整合 Google Form / Google Sheet 并自动寄出客制的 Email？

来到了第四天，我们可以进入比较复杂一点点的操作。但一样先讲结论，如果你很急着用，可以直接使用这份 Add-On: Merge Mail，其好处是还会告诉你使用者有没有开启 Email，但免费版好像一天一个帐号只能寄给 50 人。自己写的好处是，如果你一天突然要寄很多封（20,000封 and 100人以内）且还有个五分钟，那可以持续阅读。对於想知道怎麽做的人，让我们开始吧！

先来个小测验

答案会在今天的文章中！

今天的目标

现在几乎每天都会打开 Gmail，但有时就是会需要寄一些客制化的 Email 给客户。此时就会遇到个问题。

1. 「我要怎麽设定客制化的 Email」？
2. 「我要怎麽结合客制化 Email 和 Google Form / Sheet？」？

特别提醒，这边的「客制化」Email，因为会限定是要 Google 产品，一般人会是 Gmail，或是企业与组织的 Google Workspace（简单来说你的学校、组织的基本上是用 Google Drive 传档案的话，基本上就是）。那我们就开始吧！

Q1 我要怎麽设定客制化的 Email？

Step 1 从 Google Sheet 中打开你的表单

在寄客制化 Email 时通常都要有个清单，这边就来绑定一个 Google Sheet，所以第一步是打开一份 Google Sheet。

一样提醒要按下「允许授权」。

Step 2 准备好寄送 Email 要用的 Code

在寄送 Email 上，我们透过的是 MailApp.sendEmail() 的功能。写上收信人、主旨与内文，按下执行就可以了（就是这麽简单）。

function sendEmails() {
  let emailAddress = "[email protected]";
  let subject = "Sending emails from a Spreadsheet";
  let message = "This is a test for sending email";
  MailApp.sendEmail(emailAddress, subject, message);
}

另外也有「回覆」可以使用：sendEmail(to, replyTo, subject, body)

Step 3 在 Google 表单上填写要寄的名单

首先先创造一系列要寄出的内容，这边我就大概创了一个表单。

接着我们要在 GAS 中读取这表单的内容。完整的程序码在这，待会我们会一段段分段来理解。

function sendEmails() {
   // 读取连结的 Google Sheet 中的 Tab
   let sheet = SpreadsheetApp.getActiveSheet();
   let startRow = 2;
   let numRows = 5;
   let startCol = 2;
   let numCols = 4;
   let dataRange = sheet.getRange(startRow, startCol, numRows, numCols);

   let data = dataRange.getValues();

   for (let i in data) {
     let row = data[i];
     let receiver = row[0]
     let emailAddress = row[1];
     let subject = row[2] + ' '+ receiver;
     let message = row[3];
     MailApp.sendEmail(emailAddress, subject, message);
   }
}

执行以後，你就会在「寄件备份」中看到五个已经寄出的信件。因为是假的 email address，所以你也会马上收到一份「寄件错误」的通知。

接下来我们一条条看。

Step 3-1 Spreadsheet vs Sheet

首先是第一段：「读取工作表」
let sheet = SpreadsheetApp.getActiveSheet();

回到最一开始的测验，要怎麽分试算表与工作表呢？用英文来理解会比较有帮助，Spread有「扩大、蔓延之意」，sheet 则是「一片、单张」。进而可以推测说，sheet 是指单张的「工作表」，spreadsheet 指的是全部的工作表，也就是中文的「试算表」。

Step 3-2 getRange() 的 Row（列） 和 col（行）

再来第二段：「选定要读取的资料范围」

   let startRow = 2;
   let numRows = 5;
   let startCol = 2;
   let numCols = 4;
   let dataRange = sheet.getRange(startRow, startCol, numRows, numCols);

再来是也是重要观念，到了指定的 Sheet 後，要怎麽用 getRange() 抓行（Col）与列（Row）呢？这边的关键是「 用SpreadSheet 原本的 Index 」，或是我们会称为 根据 R1C1 位置参照样式（R1C1 notation），可以理解成 Row（列） 和 col（行）都以 1 为开始，且以最左上角起始点为「R=1，C=1」。

那我们这边是直接设定好要抓的行数与列数，如果想要比较弹性地去抓，可以将 numRows 设定为 numRows = sheet.getLastRow() - startRow ，这个公式会帮我们 getLastRow() 抓出表单中最下面有数值的「那一列」。直的栏也同理可用 getLastColumn()，想看使用范例可以参考 D11。

Step 3-3 getValues() 时抓下来的是 Array of Array

再来第三段：「从选定的范围中取得资料」

   let data = dataRange.getValues();

这个也是超重要观念之一，在我们用 getValues 抓下来的数值会是 Array in Array，或是说 2 dimentional array，长起来的样子会是这样。

所以，如果我今天要抓值 Dave 的话，我需要输入的会是 data[3][0]；要抓 Amy 那列的 Hellow World 的话，就要用 data[0][3] 。因为这边不再是 Sheet 内的资料了，而是回到资料结构；因此不再遵循 R1C1，而是回到程序最原本的第一项 index 是 0。

Step 3-4 给予「客制化」的内容（ㄧ） -- 直接写在程序上

在给予客制化的内容部分有两种做法，第ㄧ种是用程序，另一是直接在表单中先搞定。以下先示范第一种。

   for (let i in data) {
     let row = data[i];
     let receiver = row[0]
     let emailAddress = row[1];
     let subject = row[2] + ' '+ receiver;
     let message = row[3];
     MailApp.sendEmail(emailAddress, subject, message);
   }

这种很单纯就是藉由 let subject = row[2] + ' '+ receiver; 在 subject 的部分加上直接加上收件者的名字。这种是在开发时写得快，但对於不熟悉的人来说，这种方式很容易会出现资料结构上的警示（像是数字没有转成字串等等），所以个人会建议先在 Google Sheet 先把内容确认。

Step 3-4 给予「客制化」的内容（二） -- 先在表单上完成

先新增一栏，放要寄出的 Subject。这时我们就可以用 concatenate 来把字串做合并，用法是像这样 CONCATENATE("欢迎", "使用", "试算表！")，调整内文也是同样的道理。

并在这时回到表单中，因为新增了一栏，所以记得调整参数也要调整（numsCol要 +1，for 回圈内要调整成如下面这样）。

   for (let i in data) {
     let row = data[i];
     let receiver = row[0]
     let emailAddress = row[1];
     let subject = row[3];
     let message = row[4];
     MailApp.sendEmail(emailAddress, subject, message);
   }

好，那基本上这样做完，就已经完成了「客制化寄信」的第一堂了，功能性基本上都有。但要怎麽样跟 Google 表单结合呢？

Q2 「我要怎麽结合客制化 Email 和 Google Form / Sheet」？

因为 Google Sheet 的结合在 Q1 已经解释了，这边就针对说明怎麽样让「Google 表单」串接客制化 Email。其实也没有什麽特殊功能，就是让 让 Google 表单连到一个 Google Sheet，再用 Google Sheet 寄出（重复 Q1）。大致的作法可以参阅这一份，当然程序码要调整一下就是。

Reference

Tutorial: Sending emails from a Spreadsheet

一样提醒，寄信有 Quota 限制——每天不超过20,000封，也不超过100人。这也是为何 D1 会提说未必适合开发大系统。好，那今天就是我们的 D4，今天完整的程序码在 Step 3。明天 D5 会继续介绍结合 Email 中寄出 HTML 的使用方式，如果不想错过可以订阅按赞小铃铛（？），也欢迎留言跟我说你还想知道什麽做法/主题。如果还有问题，透过留言之外，也可以到 Facebook Group，想开很久这次铁人赛才真的开起来哈哈哈，欢迎来当 Founding Member。我们明天见。