Day 5— 自动化回信机(2) 读取试算表内容

昨天我们把试算表的前置作业完成，今天我们来看看将别人填表单後送到试算表中的内容怎麽被读取。

首先先来看看昨天的问题：

onEdit() 能在 Google 表单提交时，造成 Google 试算表的改变而自动执行吗？

作为一个工程师，程序作为我们实验场，当然面对未知的问题，直接测试就知道啦！

让我们直接来试一下！

首先昨天的程序码还记得吗？

function onEdit() {
  console.log('我被改变了喔! ')
}

我们这边就先不改动任何东西，直接切到「执行项目那一页」

接下来就先放着，等等执行完再回头来看。

先看一下目前的纪录，方便後面看看有没有新增

接下来我们就到表单的介面去新增一笔资料进去：

新增进去後，就到试算表看有没有成功新增：

接着我们回到 GAS 看一下执行项目有没有多出东西：

咦？没有任何改变！？

你没有看错，没有新增任何的执行纪录，这代表当资料从表单新增进试算表时， onEdit() 并不会被触发。

所以问题的答案出来罗～

其实我们也不是要表单资料新增进来时，就马上把信寄出，而是要等到把申请人的密码改为预设後，我进到试算表打勾表示完成设定，才会把信寄出。

因此重点在於：我如何知道打勾完成的那一笔资料是什麽资料！

既然要打勾完成，用一般写 V 的方法并不够帅气，这时候我发现 Google 试算表支援 Check Box 的使用：

然後插入核取方块到我们选定的那一格：

这样就完成了基本的设定。

应该也人会问，那麽後面我每一格都要自己慢慢加吗？

经过我实测，当我出现第二笔、第三笔资料时，Google 很聪明的就帮你把 Check Box 直接加到该行的最後一栏了！

真的自动出现超酷！

真的是方便很多呢！

刚刚说到我们要再打勾後才会进行动作，那麽实际上该怎麽做呢？

首先，我们先来看一下关於 onEdit() 的文件：

路径为 Guides > Triggers and events > Simple triggers

会发现他有支援 event 的参数可以呼叫，所以我们再去看一下 event object 中包含什麽东西：

路径 Guides > Triggers and events > Event objects

在这个里面，我们可以看到他有非常多的项目可以使用，而我们最主要要用的是 value 以及 range 。

range 回传的是一个 Range 物件 (注一)，代表的是该 event 在表单中位置。

所以我们就可以用这个 Range 物件来回推该数据所在的 row 以取得必要资讯。

因此我们再进入 docs 看一下 Range 会有什麽方法可以拿到该笔资料所在的储存格：

路径为 Reference > Google Workspace services > Spreadsheet > Range

这麽多方法…看起来只能用线性搜寻了！

我们就开始往下看吧：

往下一查发现，这个可以回传一个储存格的名称，就跟我们在试算表中输入方程序的命名一样！好像可以用喔~

不过我们还是再往下看一下，找找有没有更适合的：

疑？有一个方法是回传储存格，那麽是不是也代表，下面可以找到 row 的方法呢？

果然是有的！我们稍微看一下细部说明：

我不知道看完之後你有没有看懂...但是我是没懂啦…

那不如就实作一下吧！

我们切换到专案的页面，然後打下这段程序码：

function onEdit(e) {
  console.log(`取得 ${e.range.getRow()} 行，该 A1 Notation 为 ${e.range.getA1Notation()}`);
}

然後进到试算表勾起其中一个 Check Box：

然後切回执行项目来看一下结果：

所以可以看到，我们正常的取得了该行的 index！

这边可以注意到的是，row 的 index 不是从 0 开始，而是从 1 开始喔！
顺带一提， col 的 index 也是一样喔！

Ok！这样我们就能很简单的取得需要的资讯，所以把程序完整一下：

function onEdit(e) {
  const theRow = e.range.getRow();
  const theData = {};
  theData.email = e.range.getCell(theRow, 2);
  theData.schoolId = e.range.getCell(theRow, 3);
  theData.class = e.range.getCell(theRow, 4);
  theData.name = e.range.getCell(theRow, 5);
  console.log(theData);
}

然後随便触发一个 CheckBox 看一下结果：

什麽！？竟然错了！？

竟然发生错误！？到底怎麽了？

於是回头翻一翻文件後发现…原来这个 getCell(row, col) 得到的 Range 物件，必须在原本的 Range
物件范围内…也就是说，我们得重新从 Sheet (注二) 中得到取得该行 Range ，才能去选择该 cell！

因此现在重新整理一下：

function onEdit(e) {
  const theSheet = e.range.getSheet();
  const theRowIndex = e.range.getRow();
  const theColIndex = e.range.getColumn();
  const thisRange = theSheet.getRange(theRowIndex, 1, 1, theColIndex);
  const theData = {}
  theData.email = thisRange.getCell(1, 2);
  theData.shoolId = thisRange.getCell(1,3);
  theData.class = thisRange.getCell(1,4);
  theData.name = thisRange.getCell(1,5);
  console.log(theData);
}

然後看一下我们的结果：

太好了没错误，往下看一下吐出来的数据：

……是怎样，为什麽又没有数值阿?‍♂️?‍♂️?‍♂️?‍♂️?‍♂️

回头看一下 Docs…阿阿阿！原来如果单纯 getCell(row, col) 得到的还是 Range 物件，要多用一个 getValue() 才能取得储存格的资料。

所以再次修改：

function onEdit(e) {
  const theSheet = e.range.getSheet();
  const theRowIndex = e.range.getRow();
  const theColIndex = e.range.getColumn();
  const thisRange = theSheet.getRange(theRowIndex, 1, 1, theColIndex);
  const theData = {}
  theData.email = thisRange.getCell(1, 2).getValue();
  theData.shoolId = thisRange.getCell(1,3).getValue();
  theData.class = thisRange.getCell(1,4).getValue();
  theData.name = thisRange.getCell(1,5).getValue();
  console.log(theData);
}

然後回头察看结果：

终於阿！！！???

好啦~今天的教学就到这里罗！

今日作业：

请去观看 Docs，找找看如果改用 A1Notation，怎麽取得该行的 Range 吧！

明天我们要来处理自动寄信的部分了！

就让我们好好期待吧！

明天见?‍♂️?‍♀️

关於兔兔们：

• Tailwind CSS 台湾官网
• Tailwind CSS 台湾 (脸书粉丝专页)
• 兔兔教 × Tailwind CSS Taiwan (脸书社群)
• 兔兔教大本营

学生：……
我：难得没有发表意见？还在消化？
学生：不，只是这一集没什麽槽点。
我：哪会，很多好不好，例如废话很多啊，操作太细阿之类的。
学生：……老师你是被虐狂吧？

注一：
https://developers.google.com/apps-script/reference/spreadsheet/range

注二：https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer)