以Google App Script/Sheet為後端 Line Bot為前端寫一個簡單上下班系統利用Microsoft Power Automate串接 SharePoint資料

Published in

WU TH Max

7 min readJun 3, 2021

背景:

希望做一套系統讓作業員能夠簡易透過Line打卡上下班
過去方式還要自己填寫上下班時間、工號等等詳細資訊
新方式縮減填寫時間成本系統自動判讀時間、以及識別員工
只需透過Line填入上班或下班即完成打卡動作

上下班內容目前記錄在公司SharePoint中的一個共享xlsx中
由於是公司內部SharePoint所以會有許多權限問題
要操作SharePoint的動作必須在Power Automate中達成
避免直接操作SharePoint碰到權限問題

架構:

系統展示:

點選選單左上及右上之上下班打卡按鈕展示

點選選單左下查看打卡紀錄按鈕展示

只回傳相同USER ID的打卡紀錄
如下圖，此筆送出請求紀錄的ID為4~7列
因此只會回饋這四筆資料給這位使用者

處理LINE部分(Google Apps Script + LINE BOT)

由於後端邏輯少採取最容易部屬的Apps Scripts

首先，Google Apps Scripts有獨特關於Post, Get等等的Function寫法
doPost()就是定義Post的動作

CHANNEL_ACCESS_TOKEN產生於Line Developer>TOP>User>LineBot>Messaging API

doPost的e parameter將會是使用者傳入的訊息形式為JSON
包含userid,訊息,時間等內容

這邊的userMessage即為使用者發的訊息
用switch case來判斷如果使用者輸入上班、下班
則觸發指定行為

複寫資料到Sheet中

SpreadsheetApp.openById是由該Sheet公開分享連結後
圖中反白的地方即是ID

邏輯是一直往最後一行加上去寫
包含使用者是要上班、下班以及時間、及填寫的ID

結果預覽:

回饋資料給使用者

傳LINE把確認的資料回饋POST給使用者

處理從Sheet取資料到SharePoint部分(Google Apps Scripts + Power Automate)

從sheet中請求內容

Power Automate中得到資料這邊使用的是HTTP GET的內建方法

因此需要再寫一支簡易API
用來給Automate這邊拿資料的部分

方法蠻簡單寫個簡易版的HTTP GET在Apps Scripts中
去把指定的worksheet資料中全部加到一個json array

再透過Google的return method

return ContentService.createTextOutput(JSON.stringify(json))
    .setMimeType(ContentService.MimeType.JSON);

以TEXT型態去回傳JSON參數
要特別注意這邊的地方官方文檔中有提到

https://developers.google.com/apps-script/guides/html
也就是只要用到ContentService的回傳參數
就一定會重新導向網域
一般解決方式如同文檔中所提加個-L flag即可
甚至於POSTMAN能夠自動處理redirect
因此在POSTMAN測試不會有任何問題

但到了Power Automate

就會產生302 HTTP Status Code

<HTML>
<HEAD>
<TITLE>Moved Temporarily</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Moved Temporarily</H1>
The document has moved <A HREF="https://script.googleusercontent.com/macros/echo?user_content_key=In8OxuJLwLGpbwTRH_PKdYHmJeCo39hhExctKYdsq2cQBnh6ZCQRw7bYC9zXoZqSgyebOw3C9w1mN7kaozFX0fgHfX66m8o1m5_BxDlH2jW0nuo2oDemN9CCS2h10ox_1xSncGQajx_ryfhECjZEnM1fAPPacZ_Tv_gPlLy8mQp15J6C4TvOJnckRVJRP7158sSZINPxzoIYZpTak_o22Q&amp;lib=MZ_AKFoqL_dPfz3xILGm8pa-oLh3mq2dR">here</A>.
</BODY>
</HTML>

302 Found原意是Moved Temporarily
所以該資源原本確實存在，但已經被臨時改變了位置
換言之，就是請求的資源暫時駐留在不同的URI下
對於伺服器，通常會給瀏覽器傳送HTTP Location頭部來重新導向到新的新位置

而Power Automate本身沒辦法解決redirect的問題
所以要自行想辦法解決
最後的解法是利用Power Automate中的設定執行後續

在該HTTP請求後設定執行後續在失敗後執行下面流程
而設定一個字串變數來存HTTP Location的新URI

actions('HTTP').outputs.headers.Location

如此一來Power Automate因302而跳error時
仍能獲得正確該導向的位置
接著做第二次的redirect HTTP Request

確認得到的Response沒問題

最後根據當初App Scripts上設定的JSON格式剖析

整體流程預覽:

接著透過Power Automate的For Loop以及SharePoint, Excel Online的內建方法
把以上得到的資料寫入到指定的地方即可

Github Source: https://github.com/wutiger555/linebot-checkin-sys

以Google App Script/Sheet為後端 Line Bot為前端 寫一個簡單上下班系統 利用Microsoft Power Automate串接 SharePoint資料

背景:

架構: