iT邦幫忙

2021 iThome 鐵人賽

DAY 5
0
Modern Web

來自 GAS 的香氣~跟鳳黃酥一起享受 Google Apps Script 的午茶時光系列 第 5

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 吧!

明天我們要來處理自動寄信的部分了!

就讓我們好好期待吧!

明天見?‍♂️?‍♀️


關於兔兔們:


學生:……
我:難得沒有發表意見?還在消化?
學生:不,只是這一集沒什麼槽點。
我:哪會,很多好不好,例如廢話很多啊,操作太細阿之類的。
學生:……老師你是被虐狂吧?

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

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


上一篇
Day 4— 自動化回信機(1) 前置作業
下一篇
Day 6— 自動化回信機(3) 寄出信件功能
系列文
來自 GAS 的香氣~跟鳳黃酥一起享受 Google Apps Script 的午茶時光23
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
arguskao
iT邦新手 4 級 ‧ 2022-06-16 18:30:30

TypeError: Cannot read property 'range' of undefined
老師我照著打
結果出現....這樣的錯誤

john1982 iT邦新手 5 級 ‧ 2022-10-19 10:35:30 檢舉

別懷疑,因為老師打錯,要自己修正一下。
另外不是用上面執行按鈕,而是要去編輯sheet。

我要留言

立即登入留言