English version:How to use Google Sheets as a database
本篇大綱
本篇會用到的資源
上一篇的「完全客製google表單,美化表單樣式」是如何拿goolge forms當作網頁表單,將表單資料傳進google試算表裡(以下統一稱google excel)。
這一篇則是如何把google excel當作資料庫,並實作出一個簡單的會員列表頁出來。
這篇會用到以下資源:
- 資料部份:Google excel、裡面的欄位用uinames.com
- 頁面樣式:Skeleton
- 原始檔:Github
建立Google excel sheet
在google drive上,按新增,選擇「Google 試算表」,就會進到一張新的google excel。
這邊是用uinames.com的假資料去填寫欄位,總共抓了4個欄位:
- name(姓名)
- thumbnail(大頭照)
- birthday(生日)
要注意,欄位名稱的部份要用英文,在下面接資料那段會解釋原因。
demo的資料如下:

發佈google excel到網路
這步最簡單也最重要,只有選擇發佈到網路上的google excel,才能GET到資料。
首先,點擊左上角的「檔案」,會看到展開的選項裡有「發佈到網路」:

點擊後,會出現詢問框,問說發佈的範圍:
這是廣告,點擊一下可以幫本站多個一點點的廣告收入,謝謝

這篇demo因為只有一張表,所以直接用「整份文件」,如果是有很多張表,但限制其中幾張是可以抓的,就選擇可以公開的表即可。
按下「發佈」後,就會看見結果的詢問框:

就可以按下叉叉關掉這框了。
如果遇到的是要取得別人sheet裡的資料,而對方只有設成開放檢視,而不是發佈到網路,可看這邊參考如何抓到sheet的資料:
用Google Apps Script取得Google Excel資料
建一個會員列表的頁面
下一步就是建一個頁面,來呈現excel上的資料。
先建立出的demo如下圖:

不知道為什麼,看上去很像是交友網站用的XD~
有了資料,有了版型,接下來就要開始從google excel接資料來套了。
這是廣告,點擊一下可以幫本站多個一點點的廣告收入,謝謝
從google excel接資料
其實當把google excel設定發佈到網路上後,要接到資料就很簡單了,只需要知道GET的網址就行。
GET的網址如下:
https://spreadsheets.google.com/feeds/list/{excel_id}/{sheet}/public/values?alt=json
要替換的部份有2:excel_id、sheet
sheet就是指這個excel的第幾張表,一次只能GET一張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放進上面寫的GET網址,最後得到如下:
https://spreadsheets.google.com/feeds/list/1-vTT5LVlscvExPjqJHrhmlO2ZMM-93McoP-yXT8gyOU/1/public/values?alt=json
最後只要把網址填在GET時的url就行。
這篇一樣偷懶,直接用jQuery處理GET:
console.log的結果如下:

看到資料進來,就代表成功了,下一步就是整理資料,以及把資料寫進頁面裡。
從console.log裡可以看到,實際上google excel的儲存格資料,是在「data.feed.entry」這個陣列裡,也可以看到原本的欄位名稱都變成了「gsx$xxxx」這樣子的key,這就是為什麼一開始才會說在建表時,欄位名稱都要用英文的原因。
接著用一個for迴圈,就可以把資料整理起來:
這邊用console.table,可以直接把陣列用表格的形式列出,結果如下:

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

最後附上demo的原始碼,歡迎取用:
https://github.com/letswritetw/letswrite-google-excel-db


依據範例,請問我遇到「已封鎖跨來源請求: 同源政策不允許讀取」要如何解決?
1 確認一下抓的網址有沒有抓對
2 本篇用的是jQuery,jQuery會處理跨網域的部份,可以換用jQuery試試,如果jQuery不會跳錯誤,那就換回原本POST的方式,然後看那個方式針對跨網域可以怎麼處理
我在取得Google sheet網址(id)時
使用「發佈到網路」的網址(id)會顯示拒絕跨域請求的錯誤
使用「共用」的網址(id)就成功了
給遇到問題的人參考~~
哇喔~感謝你。