Yuanchieh's Blog

Yuanchieh's Blog

生命是長期而持續的累積

01 Jun 2018

Google Sheet API 與Google OAuth2 API授權研究

最近有個需求是希望可以將各網站的退款紀錄同步到Google Sheet上,方便PM與財務部門追蹤,相比於自建網站,Google Sheet 有方便的同步編輯 / 匯出匯入 / 登入權限管理等,就不必自己重造輪子,而且也不見得造得出來(汗

整篇分成兩部分,第一是研究Google OAuth2 API授權部分,第二是Google Sheet API。

Google OAuth2 API授權

Google API授權有兩種方式

  1. OAuth 2.0:
    如果是涉及用戶隱私資料,就必須走OAuth 2.0授權方式取得用戶授權;
    例如說 Drive / Sheet / Doc / Google+ … 等等。
  2. API keys:
    如果是單純使用Google提供的對外服務,例如 Map / URL Shortener / Geocoding 等。
    API Keys使用蠻簡單的,也不是這次使用重點,就不另外贅述。

這次要使用 Sheet API 就需走 OAuth 2.0授權,Google 支援多種OAuth 2.0 授權方式

Server-Side Web App

APP主動觸發,讓用戶登入Google帳號並授權,接著透過 redirect 或其他方式取得code,最後用code去換token。
這也是對終端用戶最為常見的授權方式。

From Google From Google

Applications on limited-input devices

適用於在沒有螢幕或是輸入的裝置,例如列印機、TV等嵌入式裝置

From Google From Google

這裡同樣是 App主動觸發,但變成回傳一個URL連結,用戶必須另外找支援瀏覽器裝置輸入URL並授權;
這裡因為 App(ex. TV)跟用戶授權的裝置(ex. 手機)不同,兩者辨識用戶方式透過 device code(“code” & URL -> User),App透過polling不斷間隔請求Google才能知道該 device code 的用戶是否已經授權,後續流程就相同。

但須注意此流程授權的權限不多,需要先評估,因為我專案需要 Sheet API授權,這個授權流程就不支援,所以無法採用。

Server-to-Server

From Google From Google

在Google APIs Console中的憑證欄位,除了API金鑰 / OAuth 2.0 用戶端 ID ,第三個能夠創建的就是 服務帳戶金鑰,服務帳號像是創建一個新的用戶,只是此用戶是被用於 Server 端授權,並透過 IAM 管理權限。

透過服務帳號最大好處是應用程式是授權於服務帳號,而非個體用戶,像是遇到人員流動就不需要手動在管理授權;
而且也不需要在 Client又要跳出用戶授權頁面,非常適合用於對內的專案開發(此流程又稱為 two-legged OAuth)。

也是我們這次會嘗試的流程之一。

Long Live Token

授權後Google會回傳token,但token都是短期的只有一小時的壽命,Google並不像FB,不會核發長期無限期的Token ,反之Google是不斷的透過 refresh token機制,不斷的核發短期Token,而refresh次數是沒有限制,也不需要用戶再次授權,所以可以長期儲存起來使用。

但必須注意,如果發生以下幾點 refresh token機制會失效
1. 用戶取消對應用程式的授權
2. refresh token超過6個月沒有使用
3. 用戶更改帳號密碼且refresh token含Gmail的權限
4. 用戶擁有太多refresh token,目前是每個應用程式每用戶上限50個refresh token。
5. 用戶本身也存在著refresh token總數上限,但文件只寫到正常狀況不會發生,沒有明講多少。

實作

這一步主要是先取得 access token與 refresh token。

Node.js Quickstart | Sheets API | Google Developers

Google Sheet API有個 Node.js 快速上手說明,並用 google-api-nodejs-client,運行後會在終端機出現授權URL,點擊後就可以取得token。

但我個人不愛google-api-nodejs-client 的寫法,因為是採用層層 Callback的方式,還不如自己接REST API來得清爽。

scope部分需要取得 [https://www.googleapis.com/auth/drive](https://www.googleapis.com/auth/drive) [https://www.googleapis.com/auth/spreadsheets](https://www.googleapis.com/auth/spreadsheets)

OAuth 2.0 for Web Server Applications 取得授權方式

這個做法會透過瀏覽器取得用戶授權,並儲存 refresh_token,方便後續Server呼叫API使用。

需注意Google OAuth2登入文件並沒有明講如何取得refresh_token,所以查了一下作法

Not receiving Google OAuth refresh token

文章中的解答說要 approval_prompt=force&access_type=offline 我嘗試是不行的,後來驗證結果是必須在 redirect URL中同時加入prompt=consent&access_type=offline

這部分比較簡單常見,就不贅述。

Server-to-Server 取得授權

這部分Google文件強烈建議使用sdk而非 REST API,主要是因為Server-to-Server是透過加密JSON Web Tokens (JWTs)實作,如果出錯容易有資安風險,但範例還是採用接 REST API

  1. 首先先到 Google APIs Console 加入憑證,這次要加入的是服務帳號金鑰,記得要下載 json檔的金鑰檔案

2. 接著到 IAM管理權限,找到剛才的服務帳號 Email,類似於 ….@….iam.gserviceaccount.com,並加入「Service Management 管理員」權限,才能操作API。

3. 記得比剛才的服務帳號加入表單的共同編輯者。

左圖為IAM管理 / 右圖為表單共用設定 左圖為IAM管理 / 右圖為表單共用設定

需注意 Server-to-Server不會有 refresh_token,token過期就必須重新產生JWT並取得新的token。

文件中有提到,部分API可以用JWT而不用access token就可以呼叫,但同樣只有部分支援,所以避免踩坑還是統一用 access token。

Sheet API 使用

Introduction to the Google Sheets API | Sheets API | Google Developers

Google Sheet API有兩個部分:
1. 單純讀跟寫是在 spreadsheets.values底下
2. 其餘表單的屬性(合併欄位、更改欄位顏色等)等在 spreadsheets底下

這次只有單純的讀寫,所以就只研究上者。

操作表單API,首先需要先創建一個 Google表單,連結大概會是
https://docs.google.com/spreadsheets/d/1dMJ…../edit#gid=0

1dMJ…. 代表是表單的ID,而 gid={id}則是內頁的ID,在API用內頁名稱直接描述如 sheet1(也支援中文如 表單一);
整體上API操作可以 讀/寫/寫入文件最後(Append)/清除,每筆API都可以指定範圍如 A1:A5 ,就跟一般表單操作類似。

程式碼請參考

sj82516/google-sheet-api-test

在插入欄位的部分(API如 append / update),有個參數可以指定Google Sheet如何處理輸入的欄位(valueInputOption),可以指定為:
1. RAW,不處理完全依照輸入
2. USER_ENTERED,按照一般 Google Sheet輸入處理,如 =MAX(A1:A2) 就會自動轉成公式,字串如果符合格式會被轉製成數字等。
3. INPUT_VALUE_OPTION_UNSPECIFIED,有趣的是這個預設值不能用,API會報錯。

另一個參數是處理 Concurrency 問題,InsertDataOption可以指定當遇到插入新欄時遇上手動更新時,選擇覆蓋 OVERWRITE 或是插入在新欄位 INSERT_ROWS。

這次只有簡單用到 read / append 這兩個API,read可以指定範圍,append蠻有趣的如果有手動插入新欄,或是手動Delete整個表單,下次 append會自動正確的位置開始,這點倒不需要擔心。
其餘的寫入/更新/刪除都沒用上,不過在文件中的參考網頁有提供測試很方便。

補更:插入新行於表單最上層

透過append是將新行插在表單最下層,但很多時候我們希望後面的資料出現在最上層,查了一下SO,有個解法是先創建新行再將值更新進去

Push Notification

試想如果表單有新增資料,可以自動通知Server就能處理一些更進階的需求;Google Drive API 提供 Push Notification,而表單身為 Drive下的其中之一檔案型態,也有支援此功能,那就來研究一下吧。

Push Notifications | Drive REST API | Google Developers

參考 Files: watch 文件說明,以下為操作步驟

  1. 定義好callback url並通過網域驗證:
    因為是webhook關係,所以必須定義好 Google回呼的 callback url,這部分需要再 Google API Console > 憑證 > 網域驗證申請,網域驗證有多種方式,最簡單是Google會提供一份html並指定要放在網域的對應路徑下,用於驗證網域是屬於本人的。
  2. 呼叫API
axios.post(\`https://www.googleapis.com/drive/v2/files/${spreadsheet_id}/watch\`, {  
   type: 'web_hook',  
   id: 'channelIdAndShouldBeUnique',  
   address: \`${domain}/drive/webhook\`  
});

address 放 callback url,id則是自行定義channel,到時候回呼用於驗證,type固定為 web_hook。

Google回呼是用POST,但是內容是放在header,以 x-goog- 開頭的標頭,反而body中沒有資料。

但不得不說Google的Push Notification有點弱,因為他只會回傳哪個檔案改了、channel ID等,但不會有實質的改變內容,例如表單的哪一行修改、哪一行新增等等;
不過也是,因為這是廣泛的Drive檔案異動通知,所以不會有太細緻的內容呈現。

總結

以上是Google OAuth2 授權模式研究,以及簡單的Google Sheet API研究,評估後符合專案的需求
1. Server主動append遞增資料
2. 保留Google表單原始功能
3. 取得Google表單異動觸發

因為是對內專案,所以是採用 Server-to-Server授權方式。

可惜不能得知更進一步的異動,只能自己重新抓資料,在Server端重新比對。

另外Google Sheet還可以透過 App Script插入資料,客製化前端,之後有空再來研究。

Categories