如何用 Google Sheets / Excel 當作資料庫？

參考網站

Google 試算表名稱是 Google Sheets，但為了讓大家好理解，以下也會稱 Google Excel。

這一篇說明如何把 Google Excel 當作資料庫，並實作出一個簡單的會員列表頁出來。

建立 Google Excel

在 Google Drive 上，按新增，選擇「Google 試算表」，就會進到一張新的 Google Excel。

這邊設定的假資料欄位，總共有以下 5 個：

id：流水號
name：姓名
image：圖片
email：信箱
phone：電話

要注意，欄位名稱的部份要用英文，在下面接資料那段會解釋原因。

Demo 的資料如下：

發佈 Google Excel 到網路

這步最簡單也最重要，只有選擇發佈到網路上的 Google Excel，才能 GET 到資料。

首先，點擊左上角的「檔案」>「共用」，會看到展開的選項裡有「發佈到網路」：

點擊後，會出現詢問框，問說發佈的範圍：

這篇 Demo 因為只有一張表，所以直接用「整份文件」，如果是有很多張表，但限制其中幾張是可以抓的，就選擇可以公開的表即可。

按下「發佈」後，就會看見結果的詢問框：

就可以按下叉叉關掉這框了。

接著記得將表單的「檢視」權限開放給知道連結的任何人，以免後面步驟因權限不足而被阻擋。

取得 Google API Key

1. GCP 新增新專案

沒有專案的才需要這步，進到 Google Cloud Platform 的頁面按下新增專案，取好專案名稱後即可新增。

2. 開通 Google Sheets API 功能

有了 GCP 的專案後，進到 API 程式庫：https://console.cloud.google.com/apis/library?hl=zh-TW

搜尋欄中搜尋「sheet」，會看到一項「Google Sheets API」：

點擊後按下「啟用」，專案就會開通 Google Sheets API 的功能：

啟用完成，頁面會回到 GCP 的頁面，可以看到上面一條訊息提醒要有憑證才能使用 API：

直接點擊「建立憑證」，或是打開網址：https://console.cloud.google.com/apis/credentials/wizard?hl=zh-TW

3. 建立憑證

建立憑證的第一步要先做一些選擇：

「選取 API」，選擇「Google Sheets API」。

「您需要存取什麼資料？」，這段看了說明文件也看不太懂使用的情境，選擇「應用程式資料」就可以。

「您打算將這個 API 與 Compute Engine、Kubernetes Engine、App Engine 或 Cloud Functions 搭配使用嗎？」，本篇只是為了要能夠取得 Google Sheets 中的資料，並不會用到上述的功能，選擇「不，我不會使用任何一項憑證」。

接著按「下一步」。

下一步是填寫我們建立這個帳戶的資料，填寫成我們之後回頭來看時，看得懂要做什麼的資訊就可以：

填寫完後按下「建立並繼續」。

後面二項是選填，不用設定也沒關係，按下「完成」。

4. 建立 API 金鑰

上一步完成後，頁面會回到憑證的頁面，點擊上方的「建立憑證」，選擇「API 金鑰」：

幾秒後，就會看見跳出一個小視窗，上面寫了「您的 API 金鑰」，這個金鑰也就是我們要取 Google Sheets 時後面要附上的：

視窗上面也提醒了，為了怕金鑰被外人拿到也可以用，我們必須要對這組金鑰加上限制，點擊「限制金鑰」就會進入設定的頁面。

建議一定要設定限制，本篇的 Demo 有限制只有在 Demo 頁下才有效，而且也只能用 Google Sheets API 的功能。

有了 API 金鑰，接著就是用新的 URL 去執行 GET。

Google Sheets URL

V4 版的 URL 規則如下：

1
https://sheets.googleapis.com/v4/spreadsheets/{表單id}/values/{sheet名稱}?alt=json&key={API金鑰}

{表單id}：表單的網址上就可以看到。
{sheet名稱}：就是每一張試算表的名稱，預設會是叫「工作表1」，這邊有測試過，有支援中文。
{API金鑰}：就是上一段我們從 GCP 上取得的金鑰。

我們用一個簡單的 fetch 來取：

1
2
3
4
5
fetch('https://sheets.googleapis.com/v4/spreadsheets/1-vTT5LVlscvExPjqJHrhmlO2ZMM-93McoP-yXT8gyOU/values/工作表1?alt=json&key=AIzaSyAVlwHA4EQx7AWjK1QsT87shL37vhKWrl4')
  .then(res => res.json())
  .then(res => {
    console.log(res)
  })

Console 上會看到回來的資料如下：

回來資料的格式，所有資料都收在 values 裡，Google Sheets 上的每一列會成為一個一個的陣列。