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(大頭照)
- 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/


依據範例,請問我遇到「已封鎖跨來源請求: 同源政策不允許讀取」要如何解決?
1 確認一下抓的網址有沒有抓對
2 本篇用的是jQuery,jQuery會處理跨網域的部份,可以換用jQuery試試,如果jQuery不會跳錯誤,那就換回原本POST的方式,然後看那個方式針對跨網域可以怎麼處理
我在取得Google sheet網址(id)時
使用「發佈到網路」的網址(id)會顯示拒絕跨域請求的錯誤
使用「共用」的網址(id)就成功了
給遇到問題的人參考~~
哇喔~感謝你。
請問為何我在google協作平台裡內嵌程式碼中,加入你的原始碼時可以顯示網頁,但若改用自己的google excel檔,網頁就無法顯示execl的資料?
有發佈到網路嗎?
您好,目前看來
第一列的欄位文字會變成gsx$xxxx 的 key
接下來每一列都會變成陣列中的物件
想問能否在這些物件中再加入陣列
以您的範例舉例的話就是,如果 Julia Harris 的 email 不只一個的時候我該怎麼做
您好,感謝大神的幫助,之前因為參考您的資料完成了一個網頁的製作。
但是最近GET的網址是否有變更,因為突然在GET資料後,出現404錯誤。
可以請大神協助嗎?
他們最近改規則了,要申請一組 API Key。
還沒有時間更新文章,詳請可先看這篇:
https://support.google.com/docs/thread/121088347/retrieving-data-from-sheets-results-in-404-error-50-of-the-time?hl=en&msgid=121557699
改成這篇文章提到的新網址,有試過可以取得到資料。
有看到這一篇,可是操作起來怪怪的,我再試試好了。感謝您的回覆。
感謝大大的教學以及更新
自己對 js 不熟想詢問若想要每隔一段資料插入不同標籤中有推薦哪邊可以讓我學習
目前想到是 在 foreach 外面加上 if 判斷 d[0] 加入了幾次
查一下 JS 的 slice。
不然用 chunk,分成一組一組的,像這個:Split array into chunks