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

如何用 Google Sheets / Excel 當作資料庫
如何用 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

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

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

  • name(姓名)
  • thumbnail(大頭照)
  • email
  • birthday(生日)

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

Demo 的資料如下:

demo 會員資料
Demo 會員資料

發佈 Google Excel 到網路

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

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

點擊 檔案 -> 發佈到網路
點擊 檔案 -> 發佈到網路

點擊後,會出現詢問框,問說發佈的範圍:

選擇發佈範圍
選擇發佈範圍

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

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

發佈成功
發佈成功

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

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

用 Google Apps Script 取得 Google Excel 資料


建一個會員列表的頁面

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

先建立出的 Demo 如下圖:

demo v1 未套資料
Demo v1 未套資料

不知道為什麼,看上去很像是交友網站用的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 的結果如下:

GET完後console.log的結果
AJAX 完後 console.log 的結果

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

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

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

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

資料整理完的結果
資料整理完的結果

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

demo v2 套入google excel來的資料
Demo v2 套入 Google Excel 來的資料

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
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 金鑰
點擊建立憑證 > API 金鑰

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

取得 API 金鑰
取得 API 金鑰

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

建議一定要設定限制,本篇的 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
如何用 Google Sheets / Excel 當作資料庫
Article Name
如何用 Google Sheets / Excel 當作資料庫
Description
本篇大綱:本篇會用到的資源。建立 Google Excel。發佈 Google Excel 到網路。建一個會員列表的頁面。從 Google Excel 接資料。V4 版取得 Google Sheets 資料的方法。
August
Let's Write
Let's Write
https://letswritetw.github.io/letswritetw/dist/img/logo_512.png
訂閱
通知
guest

12 Comments
最舊
最新
Inline Feedbacks
看所有留言
yukohn
yukohn
5 年 之前

依據範例,請問我遇到「已封鎖跨來源請求: 同源政策不允許讀取」要如何解決?

Chi
Chi
回覆給  August
4 年 之前

我在取得Google sheet網址(id)時
使用「發佈到網路」的網址(id)會顯示拒絕跨域請求的錯誤
使用「共用」的網址(id)就成功了
 
給遇到問題的人參考~~

截圖 2020-06-09 23.45.13.png
Joely
Joely
4 年 之前

請問為何我在google協作平台裡內嵌程式碼中,加入你的原始碼時可以顯示網頁,但若改用自己的google excel檔,網頁就無法顯示execl的資料?

HowardC
HowardC
3 年 之前

您好,目前看來
第一列的欄位文字會變成gsx$xxxx 的 key
接下來每一列都會變成陣列中的物件
想問能否在這些物件中再加入陣列

以您的範例舉例的話就是,如果 Julia Harris 的 email 不只一個的時候我該怎麼做

andywu
andywu
3 年 之前

您好,感謝大神的幫助,之前因為參考您的資料完成了一個網頁的製作。
但是最近GET的網址是否有變更,因為突然在GET資料後,出現404錯誤。
可以請大神協助嗎?

andywu
andywu
回覆給  August
3 年 之前

有看到這一篇,可是操作起來怪怪的,我再試試好了。感謝您的回覆。

同學生
同學生
3 年 之前

感謝大大的教學以及更新
自己對 js 不熟想詢問若想要每隔一段資料插入不同標籤中有推薦哪邊可以讓我學習
目前想到是 在 foreach 外面加上 if 判斷 d[0] 加入了幾次