# 以Google App Script/Sheet為後端 利用Microsoft Power Automate串接 SharePoint資料
## 背景:
希望做一套系統 讓使用者能夠簡易透過Line打卡上下班
過去方式需要自行透過表單填寫上下班時間、工號等等詳細資訊
新方式縮減填寫時間成本 系統自動判讀時間、以及識別員工
只需透過Line填入 上班 或 下班 即完成打卡動作
上下班內容目前記錄在公司SharePoint中的一個共享xlsx中
由於是公司內部SharePoint所以會有許多權限問題
要操作SharePoint的動作必須在Power Automate中達成
避免直接操作SharePoint碰到權限問題
## 系統展示:
#### 點選選單左上及右上之上下班打卡按鈕展示
---
#### 點選選單左下查看打卡紀錄按鈕展示
只回傳相同USER ID的打卡紀錄
如下圖，此筆送出請求紀錄的ID為4~7列
因此只會回饋這四筆資料給這位使用者
## 架構:
### 處理LINE部分(Google Apps Script + LINE BOT)
由於後端邏輯少 採取最容易部屬的Apps Scripts
#### 接收使用者資訊
```javascript=
function doPost(e) {
var CHANNEL_ACCESS_TOKEN = '';
var msg = JSON.parse(e.postData.contents);
var replyToken = msg.events[0].replyToken;
var userMessage = msg.events[0].message.text;
var userId = msg.events[0].source.userId;
var groupId = msg.events[0].source.groupId;
var timeStamp = msg.events[0].timestamp;
if (typeof replyToken === 'undefined') {
return;
}
var url = 'https://api.line.me/v2/bot/message/reply';
var text = ''
switch(userMessage)
{
case '上班':
text = "User: "+userId+"\n上班時間紀錄: "+getCurrentTime()
sendToSheet(userId,getCurrentTime(),"上班")
break;
case '下班':
text = "User: "+userId+"\n下班時間紀錄: "+getCurrentTime()
sendToSheet(userId,getCurrentTime(),"下班")
break;
}
}
```
首先，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中
```javascript=
function sendToSheet(user, time, status) {
// 初始化試算表
let SpreadSheet = SpreadsheetApp.openById("");
let Sheet = SpreadSheet.getSheets()[0];
let LastRow = Sheet.getLastRow();
// 寫入試算表
Sheet.getRange(LastRow+1, 1).setValue(user);
Sheet.getRange(LastRow+1, 2).setValue(time);
if (status === '上班') {
Sheet.getRange(LastRow+1, 3).setValue("上班");
}else if (status === '下班') {
Sheet.getRange(LastRow+1, 3).setValue("下班");
}
}
```
SpreadsheetApp.openById是由該Sheet公開分享連結後
圖中反白的地方即是ID
邏輯是一直往最後一行加上去寫
包含使用者是要上班、下班 以及時間、及填寫的ID
結果預覽:
#### 回饋資料給使用者
```javascript=
UrlFetchApp.fetch(url, {
'headers': {
'Content-Type': 'application/json; charset=UTF-8',
'Authorization': 'Bearer ' + CHANNEL_ACCESS_TOKEN,
},
'method': 'post',
'payload': JSON.stringify({
'replyToken': replyToken,
'messages': [{
'type': 'text',
'text': text
}],
}),
});
```
傳LINE把確認的資料回饋POST給使用者
### 處理從Sheet取資料到SharePoint部分(Google Apps Scripts + Power Automate)
#### 從sheet中請求內容
Power Automate中得到資料 這邊使用的是HTTP GET的內建方法
因此需要再寫一支簡易API
用來給Automate這邊拿資料的部分
```javascript=
function doGet() {
// var param = e.parameter;
let SpreadSheet = SpreadsheetApp.openById("1rESZhA1WZAD5Fl4S1FXnKoQT9VjI4QO6d16NeS8iq1o");
let Sheet = SpreadSheet.getSheets()[0]; // 指定第一張試算表
var json = convertToJson(Sheet)
return ContentService.createTextOutput(JSON.stringify(json))
.setMimeType(ContentService.MimeType.JSON);
}
function convertToJson(sheet) {
// first line(title)
var colStartIndex = 1;
var rowNum = 1;
var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
var firstRowValues = firstRange.getValues();
var titleColumns = firstRowValues[0];
// after the second line(data)
var lastRow = sheet.getLastRow();
var rowValues = [];
for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
var colStartIndex = 1;
var rowNum = 1;
var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
var values = range.getValues();
rowValues.push(values[0]);
}
// create json
var jsonArray = [];
for(var i=0; i<rowValues.length; i++) {
var line = rowValues[i];
var json = new Object();
for(var j=0; j<titleColumns.length; j++) {
json[titleColumns[j]] = line[j];
}
jsonArray.push(json);
}
return jsonArray;
}
```
方法蠻簡單 寫個簡易版的HTTP GET在Apps Scripts中
去把指定的worksheet資料中全部加到一個json array
再透過Google的return method
```javascript=
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=
<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&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
```javascript=
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
## 未來展望
目前還未解決問題
1. **Google Sheet資料匯入Sharepoint裡存放OT紀錄的excel檔**
由於目前方式會將linebot的資料暫存於google sheet中
但是原資料都是在sharepoint裡的xlsx檔
造就了必須將資料遷移至sharepoint的該資料 並向下更新堆疊
目前的困難是 要將資料移入原資料集 必須得透過power automate的方式處理
而power automate可以簡單自架API做觸發 也就是當LINE這端資料更新後 可以直接呼叫此API 連動將google sheet新資料更新到excel中
但目前此方法因公司職權不足而無法使用
所以暫時的解決方式是透過power automate的time trigger
固定例如一天跑一次一隻API 將google sheet的資料load進來
但這樣也引申了更多問題 包含了同時有兩個gate(Line/表單)
且LINE的填入方式還有延遲(如一天)
以及真正串連起來後 會不會導致LINE這端與表單資料填入時有衝突
以上都待需測試及更好的解決方式來處理
2. 如能夠解決第一點問題 **有了LINE BOT 將能使這套系統擁有了"INPUT"的功能**
原先的表單方式因為只能填入單次資料 無法進行後續查詢等動作
而如果透過LINE BOT 就能達到需求功能之一例如 查詢個人上下班打卡資料
3. **LINE還有許多潛力功能 例如集點卡功能**
正好可以符合Kevin所提過的Thank you card system的需求
還有許多不同功能待發現及應用
Sign in with Wallet
Connect another wallet