[Tool Note] — 透過 GAS 快速建立後端, 串接TG機器人佐 Google Excel

發表於 更新於 分類於 ， ， 文章字數： 9.5k 所需閱讀時間 ≈ 9 分鐘

Introduction & 前言

Google Apps Script

想要寫一個 Side Project 卻卡在後端建立很麻煩嗎？如果想要有一個 Side Project 需要資料庫，就算已經會 JavaScript 了，卻要在學習 Node.js、學習 ORM、學習建立資料庫、學習部署上機器…

等等，如果只是要練習 Side Project 不用這麼麻煩…趕緊來看看這篇文章吧！

這篇文章適合什麼人？

  • 想快速建立出 Side Project 當作品的人(不是很建議，但至少能證明你會看文件串接，後續繼續說明)
  • 想知道如何串接通訊軟體機器人
  • 想知道如何不需要建立 DB(資料庫) 就可以存資料及撈資料**(並非透過 LocalStorage)**
  • 沒有程式碼經驗的小白也能輕鬆做出機器人

Summary & 摘要

本篇將會用建立 Telegram(下簡稱 TG) 機器人的範例來介紹如何透過 Google Apps Script(下簡稱 GAS) 快速建立後端。

如果不知道 Telegram 詳細可以參考 專題｜為何大家開始用 Telegram？它有比 LINE 更好用嗎？ 簡單說就是像 LINE 的通訊軟體

本篇文章預設學習前的基本條件需求

  • 會基本的 JavaScript(至少能會看懂)
  • 會安裝及使用 Telegram
  • 會使用 Google Excel

這邊可以簡單分為六個小節：

  1. 簡易基礎介紹 - 我們要做出什麼 Side Project
  2. 申請機器人 - Side Project 需要使用
  3. 快速建立後端 - 不需要起一個後端服務快速建立後端
  4. 串丸子般把所有東西串起來 - 料都準備好了，一起下鍋吧
  5. 更新的一些小事 - 關於 GAS 需要注意的小事項
  6. 延伸應用 - 如果需要作品的人，可以如何延伸創作？

簡易基礎介紹

看完上面一些條件後，聰明的你應該八九成能猜到我們要做什麼。這次我們要做一個簡易的機器人，可以幫我們建立一些資料，也可以把這些資料撈取出來給使用者。

先說說什麼是通訊軟體的機器人，如果使用 LINE 來舉例(如下圖)，就像有個人再跟你互動。

LINE Bot

圖片來至 Line開發者大會TechPulse來臺，整合5套開發套件，讓使用者開發聊天機器人應用

而我們今天要使用的機器人是 TG 的機器人(不知道 TG 是什麼可以往上拉，有連結請參考)，大多的機器人概念圖都如下：

機器人概念圖

我們需要做事的地方就在右邊的後端伺服器那塊，將我們的程式碼寫好後部署上去，然後再透過通訊軟體提供的方式設定，讓通訊軟體知道如何找到我們的部署上去的程式碼，通訊軟體就會在某些時機點丟一些資訊過來，這時候在我們的程式碼裡面可以做一些判斷然後操作資料，最後回吐一些通訊軟體定義好的格式回去，這時候通訊軟體就會繼續做接下來的事情，這邊講的比較不好明白，繼續看下去就會瞭解了。

簡易來說，我們要做的事情很簡單：

  1. 使用者輸入訊息給機器人
  2. 通訊軟體接到訊息後傳送通知到我們的後端伺服器去
  3. 經過我們部署到後端伺服器上的程式碼加工處理後回吐客製化訊息給通訊軟體
  4. 通訊軟體告知使用者我們回吐的資訊

在製作機器人之前我們需要先安裝及申請幾樣東西(跟著文章一步一步走可以)：

  • Telegram 安裝及申請帳號
  • Telegram 機器人申請
  • Google Excel 申請
  • Google App Script 專案建立

申請機器人

這次需要先安裝 TG 及申請帳號密碼，這邊就不詳細贅述了，接著需要申請一組機器人；至於為何要用 TG 而不用 LINE Bot 呢？因為 TG 真的容易很多。

  1. 首先在 TG 的搜尋區輸入 @BotFather，就會看到一個藍勾勾的帳號，點選進去。

    TG的機器人之父

  2. 接著輸入 /start，輸入完會出現一些可以使用的指令，我們需要輸入 /newbot 來建立我們的第一隻機器人。

  3. 之後跟著指令輸入該輸入的資訊

    新增機器人

  4. 建立結束後會拿到一串 Token，如果沒有記下來也沒關係，之後都還可以用 @BotFather 查詢到，輸入 /mybots，在點擊你想查詢的機器人。

    我的機器人

    機器人的 Token

  5. 為了後面方便我們先將一些機器人的資訊輸入完成，點擊 Edit Bot 然後點擊要編輯的資訊，像是 Edit Name 或 Edit Description…等等

    機器人資訊

    • Name - 機器人的名稱
    • Description - 機器人的敘述，會在搜尋後要加入機器人的時候出現在畫面上 機器人填寫 Description 後顯示的地方
    • About - 會出現在個人資料裡面的介紹
    • Botpic - 機器人大頭貼
    • Commands - 在機器人一對一或者有邀請該機器人的群組裡面輸入 “/“ 後會出現的指令

另外如果有需要在群組裡面使用機器人，請記得點在機器人設定那邊點擊 BotSetting 然後把 Allow Groups 設定為 off，因為這次我們只有要自己使用，不需要打開。

允許在群組內使用

搜尋自己的機器人

TG 的 Bot 申請設定都在通訊軟體裡面的 @BotFather 完成，比起其他通訊軟體的 Bot 相對方便許多。

快速建立後端

申請好 TG Bot 之後，接下來我們要搭建我們的後端，這邊會需要一個紀錄資料的 DB 及 處理後端邏輯的地方。

  1. 首先先創建一個 Google Excel(這個就是我們的簡易資料庫了)，然後裡面打上下面的內容 Google Excel 下方的 工作表1 也請改名為 簡易記錄表 表格可以隨意命名，這邊先以圖示為例
  2. 接著創建一個 Google Apps Script 專案 新增 GAS 專案
  3. 複製下面程式碼貼上(把原本的 function myFunction() 整個替換掉)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
function doGet() {
  // 取得試算表
  const SheetId = 'excel 的 Id';
  const SheetName = '簡易記錄表';
  const SpreadSheet = SpreadsheetApp.openById(SheetId);
	// const Sheet = SpreadSheet.getSheets()[0]; // 指定第一張試算表
  const Sheet = SpreadSheet.getSheetByName(SheetName);

  // 取得資料
  const LastRow = SpreadSheet.getLastRow(); // 取得有資料的最後一欄
  const LastColumn = SpreadSheet.getLastColumn(); // 取得有資料的最後一列

  Logger.log(Sheet.getSheetValues(LastRow, LastColumn, 1, 1)); // 取得 startRow、startColumn、numRows 和 numColumns 從「左上儲存格至右下儲存格」中間區域所包含的儲存格數值
  
  return ContentService
    .createTextOutput(JSON.stringify({ result: '測試Get取得資訊成功', version: '1.0' }))
        .setMimeType(ContentService.MimeType.JSON); 
}
  1. 將 SheetId 後面的 Excel 的 Id 替換為你剛剛創建 Google Excel 的 ID，ID 哪裡拿呢？打開剛剛你創建的 Google Excel，然後看網址那邊。 https://docs.google.com/spreadsheets/d/167CxX5-SwaTDDK4u1QRaPWIIWGcpbBNKahJYYozY22A/edit?hl=zh-TW#gid=0 取得 Excel ID

    取得中間那 d/ 後面開始一直到 /edit 前面為止的範圍。

  2. 接著按下執行按鈕 執行 GAS

    第一次執行會需要權限同意，這是正常的

執行過程

這邊很重要需要先解釋一下，GAS 預設兩種接收方式，我們在 GAS 寫完的程式碼發布後，會拿到一串網址，我們可以把這個當成 API，透過 HTTP Methods 可以觸發我們寫的程式碼，而 GAS 只能接受 HTTP GET 及 POST。

而 GAS 最重要的兩點就寫在文件內，請參考 *Web Apps - Requirements for web apps*，第一點就是剛剛提到的 doGet(e) 跟 doPost(e)，第二點就是該程式碼回傳的內容必須是 **HTML service HtmlOutput Object 或 **Content service TextOutput Object。

GAS Document

要提醒很重要的一件事情就是串接任何服務之前，文件一定要好好的看清楚規則及條件還有用法。

到這邊你已經可以撈取到 Excel 的內容了，這邊一行一行來解釋一下：

  • const SheetId = 'excel 的 Id'; - 這行需要放入你的 Google Excel ID
  • const SheetName = '簡易記錄表'; - 這行需要輸入你 Google Excel 的 Sheet 名稱，名稱就是 Google Excel 下方的工作表名稱。
  • const SpreadSheet = SpreadsheetApp.openById(SheetId); - 這行是透過 Google Excel 的 ID 取得你的 Google Excel，也可以使用 openByUrl()
  • const Sheet = SpreadSheet.getSheets()[0]; - 這行註解掉的原因是取得 Excel Sheet 的方式可以使用 getSheetByName() 或 getSheets()[0] 詳細可參考 Google App Script Api Doc
  • const Sheet = SpreadSheet.getSheetByName(SheetName); - 這行上面提過是取得你的 Excel Sheet 表格
  • const LastRow = SpreadSheet.getLastRow(); - 這行是要取得有資料的最後那一欄
  • const LastRow = SpreadSheet.getLastColumn(); - 這行是要取得有資料的最後那一列
  • Logger.log(Sheet.getSheetValues(LastRow, LastColumn, 1, 1)); - 這行就是取得 startRow、startColumn、numRows 和 numColumns，最後的 1, 1 如果帶入其他數字可以取得一整個範圍的內容，例如 Sheet.getSheetValues(1, 1, 2, 2); 意思就是取得從 A1 到 B2 範圍的內容。
1
2
3
return ContentService
    .createTextOutput(JSON.stringify({ result: '測試Get取得資訊成功', version: '1.0' }))
        .setMimeType(ContentService.MimeType.JSON);
  • 最後一行則是要回傳的資訊，這邊建議回傳都使用 ContentService.createTextOutput(); 這種方式回傳

串丸子般把所有東西串起來

到這邊你已經建立好後端部分了，該準備的東西也都準備好了，我們開始來著手串接 TG Bot 吧。

先把剛剛的程式碼修改一下，左側先新增三隻檔案，然後修改一下檔案名稱如下：

目前所有檔案的快照

app.gs 內容改為：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
function doGet() {
  // 取得試算表
  const { Sheet, SpreadSheet } = connectToSheet('Excel ID');
  const SheetInfo = getSheetInfo(Sheet, SpreadSheet, {
    startColumn: 1,
    endColumn: 4,
  });

  return ContentService
    .createTextOutput(JSON.stringify(SheetInfo))
        .setMimeType(ContentService.MimeType.JSON); 
}

// 接收 TG 回應
function doPost(e) {
	// 取得試算表
  const { Sheet, SpreadSheet } = connectToSheet('Google Excel 的 ID');
  // Token
  const bot_token = "TG Bot Token";
  
  // 訊息來源
  const estringa = JSON.parse(e.postData.contents);

  if (estringa.message.chat.type !== 'private') return; 
  
  const newDate = new Date();
  const {
    id,
    username,
  } = estringa.message.chat;
  const {
    text,
  } = estringa.message;

  setSheetInfo(Sheet, SpreadSheet, [[newDate, username, id, text]])

  // 整理要回覆給通訊軟體的內容
  const options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify({
        "method": "sendMessage",
        "chat_id": estringa.message.chat.id,
        "text": estringa.message.text,
    })
  };
   
  UrlFetchApp.fetch(`https://api.telegram.org/bot${bot_token}/sendMessage`, options);
}

connectToSheet.gs 內容改為:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 連結 Excel
function connectToSheet(SheetId) {
  Logger.log('start to connectToSheet');
  const SpreadSheet = SpreadsheetApp.openById(SheetId);
  const Sheet = SpreadSheet.getSheets()[0];
  if (Sheet != null) {
    Logger.log('ConnectToSheet Success!');
    return {
      Sheet,
      SpreadSheet
    };
  }
  Logger.log('ConnectToSheet Failed!');
  throw Error;
}

getSheetInfo.gs 內容改為：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
// 取得目標 Excel 的資料
function getSheetInfo(Sheet, SpreadSheet, {
  startRow,
  startColumn,
  endRow = 1,
  endColumn = 1,
}) {
  if (!Sheet || !SpreadSheet) {
    Logger.log('Sheet Or SpreadSheet Not Found!');
    throw Error;
  }

  let _startRow = startRow;
  let _startColumn = startColumn;

  // 取得資料
  _startRow = startRow ? startRow : SpreadSheet.getLastRow(); // 取得有資料的最後一欄
  _startColumn = startColumn ? startColumn : SpreadSheet.getLastColumn(); // 取得有資料的最後一行

  return Sheet.getSheetValues(_startRow, _startColumn, endRow, endColumn); // 取得 startRow、startColumn、numRows 和 numColumns 從「左上儲存格至右下儲存格」中間區域所包含的儲存格數值
}

setSheetInfo.gs 內容改為：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
function setSheetInfo(Sheet, SpreadSheet, values) {
  if (!Sheet || !SpreadSheet) {
    Logger.log('Sheet Or SpreadSheet Not Found!');
    throw Error;
  }

  // 取得資料範圍
  const range = Sheet.getRange(SpreadSheet.getLastRow() + 1, 1, 1, 4);

  if (!range) {
    Logger.log('Range Not Found!');
    throw Error;
  }
  
  if (values[0] && Array.isArray(values[0]) && values[0].length === 4) {
    // 寫入試算表
    range.setValues(values);
  } else {
    Logger.log('Value is Not Array!');
    throw Error;
  }
}
  • setSheetInfo.gs 原本想寫成跟 getSheetInfo.gs 一樣，透過 Props 帶入參數，動態的選擇要塞入多個內容或者單個，但因為這邊寫起來會很複雜，所以先固定寫死，如果有興趣的朋友，可以自行修改內容，如果還不太明白 JavaScript 可以先略過。*

請記得先把上面該帶入的 Google Excel ID 及 TG Bot Token 填入，然後點選右上角 部署 > 新增部署作業。

新增部署作業

部署作業設定

*隨後請記得 選取類型 選擇 網頁應用程式，說明隨意輸入，執行身份選自己，誰可以存取選擇 所有人，然後點擊部署。*

GAS 部署結果

最後會拿到一串網址，請把這串網址記下來

這時候我們要開始把 TG Bog 跟 Google Script App 串接起來，請先記得幾個基本 API，最好使用 PostMan 記錄下來：

  • https://api.telegram.org/bot{{bot_token}}/setWebhook?url={{google_sheet}}
  • https://api.telegram.org/bot{{bot_token}}/deleteWebhook
  • https://api.telegram.org/bot{{bot_token}}/getMe
  • https://api.telegram.org/bot{{bot_token}}/sendMessage

*我們需要使用第一個 API 去把我們的 TG Bot 跟 Google Apps Script 串接起來，哪天如果不想要使用了，請記得使用第二個 API 去把串接起來的機器人取消掉；第三個 API 則是可以取得目前機器人的一些設定，這個在 TG BotFather 其實也可以看到；第四個 API 可以透過 HTTP Post 發送一些訊息到 TG。*

使用前還是要先解釋一下，還記得上面那張圖嗎？使用者發送訊息給 TG Bot，而 TG Bot 會告知我們，這時候要取得這些通知有兩種方法，一種是 Long Polling，另一種則是 Webhook。

前者是透過 getUpdates() 的方法去跟 TG Bot 要通知，後者則是 TG Bot 會自動透過設定好的 Webhook URL 去通知我們。

請記得第二種方法設定好之後，如果有設定成功，但是沒有 Return HTTP 200 給 TG Bot，TG 那邊預設會一直狂敲猛敲你的 Webhook URL，可能會造成流量問題，但目前我們這邊的範例不需要擔心(如果有誤還麻煩請幫忙指出，感激不盡)。

設定 Webhook

這邊開始我們要來設定 Webhook，透過 PostMan 或者打開網頁，把那行 API 輸入上去，https://api.telegram.org/bot{{bot_token}}/setWebhook?url={{google_sheet}`，請記得 **"{{}}"** 要刪掉，範例為 https://api.telegram.org/bot123456789:xxxx_xxx_xxx/setWebhook?url=https://script.google.com/macros/s/xxxxxxxx/exec` 。

設定 Webhook

設定成功會出現 "description": "Webhook was set" 。

接著到 TG Bot 輸入隨意訊息 哈摟你好嗎 ，如下圖：

機器人測試

這時候到 Google Excel 查看會看到一筆紀錄：

取得機器人回應

至此你已經會透過 GAS 串接 TG Bot 把資料從 Google Excel 塞進及取出。

更新的一些小事

管理部署

發布後如果發現有需要更新內容，點擊 部署 > 管理部屬作業 然後會彈出一個視窗，點擊右上角的編輯(一支筆)，接著點擊版本選擇 建立新版本，說明選填，然後點擊 部署。

編輯部署

這邊需要注意的是，透過 管理部屬作業 去部署的新程式碼，網頁應用程式那串網址結果都會是一樣的，不需要再去重新 setWebhook 給 TG Bot。如果需要保留舊的版本，請選擇 部署 > 新增部署作業，這樣才會 既保留舊版本，又產生新的網頁應用程式網址。

保留舊版本的好處是如果新的版本出問題了，可以快速換回舊版本；另外需要注意的是，確定沒有用的版本請記得封存，不然還是會算流量。

延伸應用

現在只要專心 Foucs 在前端即可，這幾乎是前端要做 Side Project 追求的，把重點放在前端部分。如果不需要串接 TG Bot 其實也可以寫一個簡易的訂單系統，有興趣可以參考這篇文章 **Google sheet 試算表表單串接api**，這篇文章介紹如何透過 GAS 做一個鹹酥雞訂單系統。

因為免除了從零開始建置後端的麻煩，只要是簡單的 CRUD 都可以輕鬆建立，串接其他機器人也都很輕鬆～

Conclusion & 結論

這次會碰到這個應用是因為剛好工作上團隊都會點便當，但因為每次都需要透過打開網頁去 Google Excel 看需要付多少錢有點麻煩，想了一下平常團隊最常使用的軟體是 TG，所以就想想有沒有辦法可以透過串接機器人讓他告訴我現在要付多少錢，因為懶惰又學到了一些有趣的應用，果然這才是驅使工程師成長的推力。

這個示範機器人會留著給大家試用，如果有興趣的話。但因為個資問題，會把 使用者名稱 及 Chat ID 改為 xxx

TG Bot 機器人 - 加入好友連結

Google Excel 表單 - 表單連結

參考網站