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

English version：How to use Google Sheets as a database

2021.08.25 更新：因為最近 Google 改了規則，原本的內容似乎不再適用。新增一段「V4 版取得 Google Sheets 資料的方法」，是寫這次修改後取得 Google Sheets 的新方法。

本篇會用到的資源

上一篇的「完全客製 Google 表單，美化表單樣式」是如何拿 Goolge Forms 當作網頁表單，將表單資料傳進 Google 試算表裡。

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

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

這篇會用到以下資源：

• 資料部份：Google excel、裡面的欄位用 uinames.com
• 頁面樣式：Skeleton
• 原始檔：GitHub

建立 Google Excel

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

這邊是用 uinames.com 的假資料去填寫欄位，總共抓了 4 個欄位：

• name（姓名）
• thumbnail（大頭照）
• email
• birthday（生日）

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

Demo 的資料如下：

發佈 Google Excel 到網路

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

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

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

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

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

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

如果遇到的是要取得別人 Excel 裡的資料，而對方只有設成開放檢視，而不是發佈到網路，可看這邊參考如何抓到 Excel 的資料：

用 Google Apps Script 取得 Google Excel 資料

建一個會員列表的頁面

下一步就是建一個頁面，來呈現 Google Excel 上的資料。

先建立出的 Demo 如下圖：

不知道為什麼，看上去很像是交友網站用的XD~

有了資料，有了版型，接下來就要開始從 Google Excel 接資料來套了。

從 Google Excel 接資料

其實當把 Google Excel 設定發佈到網路上後，要接到資料就很簡單了，只需要知道 AJAX 的網址就行。

AJAX 的網址如下：

https://spreadsheets.google.com/feeds/list/{excel_id}/{sheet}/public/values?alt=json

要替換的部份有 2：excel_id、sheet

sheet 就是指這個excel的第幾張表，一次只能 AJAX 一張 sheet。

excel_id，要看網址，Google Excel 的網址結構是這樣：

https://docs.google.com/spreadsheets/d/{excel_id}/edit#gid=0

因此，比方說這篇的 Demo 網址是：

https://docs.google.com/spreadsheets/d/1-vTT5LVlscvExPjqJHrhmlO2ZMM-93McoP-yXT8gyOU/edit#gid=0

那 Excel 的 id 就是：1-vTT5LVlscvExPjqJHrhmlO2ZMM-93McoP-yXT8gyOU

把 excel_id、sheet 放進上面寫的 AJAX 網址，最後得到如下：

https://spreadsheets.google.com/feeds/list/1-vTT5LVlscvExPjqJHrhmlO2ZMM-93McoP-yXT8gyOU/1/public/values?alt=json

最後只要把網址填在 AJAX 時的 URL 就行。

這篇一樣偷懶，直接用 jQuery 處理 AJAX：

console.log 的結果如下：

看到資料進來，就代表成功了，下一步就是整理資料，以及把資料寫進頁面裡。

從 console.log 裡可以看到，實際上 Google Excel 的儲存格資料，是在「data.feed.entry」這個陣列裡，也可以看到原本的欄位名稱都變成了「gsx$xxxx」這樣子的 key，這就是為什麼一開始才會說在建表時，欄位名稱都要用英文的原因。

接著用一個 for 迴圈，就可以把資料整理起來：

這邊用 console.table，可以直接把陣列用表格的形式列出，結果如下：

最後把資料用迴圈塞進頁面裡就完成了，結果如下圖：

V4 版取得 Google Sheets 資料的方法

這陣子聽到身邊的人哀嚎，因為 Google 改規則了，上述的方法似乎不再適用。

後來找到了一篇：Docs Editors Help

照著上面提供的方法，有成功取到 Goolge Sheets 的資料回來，因此新增這段來筆記並說明。

新的 V4 版的方法，因為 GET 的 URL 上需附上 key=xxxx，因此要先來取得一組 API Key。

取得 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

新的 URL 規則如下：

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

{表單id} 跟原本的一樣，網址上就可以看到。

{sheet名稱} 就是每一張試算表的名稱，預設會是叫「工作表1」，August 測試過，有支援中文。

{API金鑰} 就是上一段我們從 GCP 上取得的金鑰。

像本篇的 Demo 頁，最後組出來的 URL 就是：

https://sheets.googleapis.com/v4/spreadsheets/1-vTT5LVlscvExPjqJHrhmlO2ZMM-93McoP-yXT8gyOU/values/工作表1?alt=json&key=AIzaSyAVlwHA4EQx7AWjK1QsT87shL37vhKWrl4

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

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 上的每一列會成為一個一個的陣列。

大家如果原本有頁面是用以前的方式，換新的方式時記得原頁面塞資料的地方也要修改。

本篇的 Demo 頁面也一併做了更新：

https://letswritetw.github.io/letswrite-google-excel-db/

Summary

Article Name

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

Description

本篇大綱：本篇會用到的資源。建立 Google Excel。發佈 Google Excel 到網路。建一個會員列表的頁面。從 Google Excel 接資料。V4 版取得 Google Sheets 資料的方法。

August

Augustus

Let's Write

Let's Write

https://letswritetw.github.io/letswritetw/dist/img/logo_512.png