---
# System prepended metadata

title: 以Google App Script/Sheet為後端 利用Microsoft Power Automate串接 SharePoint資料

---

# 以Google App Script/Sheet為後端 利用Microsoft Power Automate串接 SharePoint資料

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

上下班內容目前記錄在公司SharePoint中的一個共享xlsx中
由於是公司內部SharePoint所以會有許多權限問題
要操作SharePoint的動作必須在Power Automate中達成
避免直接操作SharePoint碰到權限問題
## 系統展示:
#### 點選選單左上及右上之上下班打卡按鈕展示
![](https://i.imgur.com/2Hox9xe.jpg)

![](https://i.imgur.com/ePB5GLs.png)


---

#### 點選選單左下查看打卡紀錄按鈕展示
![](https://i.imgur.com/QFeRb6b.jpg)


只回傳相同USER ID的打卡紀錄
如下圖，此筆送出請求紀錄的ID為4~7列
因此只會回饋這四筆資料給這位使用者
![](https://i.imgur.com/cfOQ1VK.png)


## 架構:
![](https://i.imgur.com/3aLnJ0U.jpg)


### 處理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
![](https://i.imgur.com/aH5Y9Ss.png)
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("下班");
  }
}
```

![](https://i.imgur.com/v3vNVZv.png)
SpreadsheetApp.openById是由該Sheet公開分享連結後
圖中反白的地方即是ID

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

結果預覽:
![](https://i.imgur.com/84v88Dq.png)


#### 回饋資料給使用者
```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的內建方法
![](https://i.imgur.com/EZd411S.png)

因此需要再寫一支簡易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://i.imgur.com/YgUSkOm.png)
https://developers.google.com/apps-script/guides/html
也就是只要用到ContentService的回傳參數
就一定會重新導向網域
一般解決方式如同文檔中所提加個-L flag即可
甚至於POSTMAN能夠自動處理redirect
因此在POSTMAN測試不會有任何問題
![](https://i.imgur.com/IP3mgxG.png)

但到了Power Automate
![](https://i.imgur.com/MNUAL6i.png)
就會產生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&amp;lib=MZ_AKFoqL_dPfz3xILGm8pa-oLh3mq2dR">here</A>.
</BODY>
</HTML>

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

而Power Automate本身沒辦法解決redirect的問題
所以要自行想辦法解決
最後的解法是利用Power Automate中的 設定執行後續
![](https://i.imgur.com/WeR9yTz.png)
![](https://i.imgur.com/a6MvxKv.png)
在該HTTP請求後 設定執行後續 在失敗後執行下面流程
而設定一個字串變數來存HTTP Location的新URI
```javascript=
actions('HTTP').outputs.headers.Location
```
如此一來Power Automate因302而跳error時
仍能獲得正確該導向的位置
接著做第二次的redirect HTTP Request
![](https://i.imgur.com/4m8Oo9l.png)

確認得到的Response沒問題
![](https://i.imgur.com/lOJ9uA4.png)

最後根據當初App Scripts上設定的JSON格式剖析
![](https://i.imgur.com/HeLuJRN.png)

整體流程預覽:
![](https://i.imgur.com/SjhPuGT.png)

接著透過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的需求
還有許多不同功能待發現及應用