如何用 Google Excel 當作資料庫

/

English version:How to use Google Sheets as a database

本篇會用到的資源

上一篇的「完全客製google表單,美化表單樣式」是如何拿goolge forms當作網頁表單,將表單資料傳進google試算表裡(以下統一稱google excel)。

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

這篇會用到以下資源:


建立Google excel sheet

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

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

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

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

demo的資料如下:

demo 會員資料
demo 會員資料

發佈google excel到網路

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

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

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

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

這是廣告,點擊一下可以幫本站多個一點點的廣告收入,謝謝

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

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

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

發佈成功
發佈成功

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

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

用Google Apps Script取得Google Excel資料


建一個會員列表的頁面

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

先建立出的demo如下圖:

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

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

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

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

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

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

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

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

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

demo v2 套入google excel來的資料
demo v2 套入google excel來的資料

最後附上demo的原始碼,歡迎取用:

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


Summary
如何用Google Excel當作資料庫
Article Name
如何用Google Excel當作資料庫
Description
本篇大綱:本篇會用到的資源:資料、樣式、原始檔。建立Google excel sheet、發佈google excel到網路、建一個會員列表的頁面、從google excel接資料。這一篇是如何把google excel當作資料庫,並實作出一個簡單的會員列表頁出來。
Augustus
Let's Write
Let's Write
https://letswrite.tw/wp-content/uploads/2020/08/logo_512.jpg

隨選筆記文

API

Instagram 基本顯示 API :抓取公開 IG 資料、相片、影片

Google Others

用 GCP 建立 Cloud Functions

Front-End

JavaScript 載入方式 defer、async

Bot Telegram

在網頁上嵌入Telegram 頻道廣播訊息

Front-End

漸層色按鈕 Hover 效果

Google Maps

Google Maps API 學習筆記 – 1:地圖、標記、客製樣式

Google Others

用 reCAPTCHA v3 來做非機器人驗證

API Front-End

用 Google Apps Script 寫一個 LINE 登入功能:上篇 – 前置作業

PWA

PWA學習筆記-6:實際使用整體流程範例

Vue

用 VuePress 製作說明文件頁面 – 4:佈景主題、外掛

以下是留言,但關於留言的部份必需先讓你們知道:

本站的文章都是 Augustus 因為覺得有趣,才會實作並整理成筆記文而後進行發表。

如果留言是希望把 Demo 改成「你想要」的樣子,或是把功能改成「符合你需求」的樣子,

Sorry~ 除非那修改是 Augustus 也有興趣的,不然不會幫你們寫程式去面對工作或是交作業。

未來這類的留言不會再主動回覆。😎

另外,公開信箱是為了讓金流驗證用,

因為之前遇過幾次回信協助解決問題後,對方卻一聲謝謝也沒有,就這樣拿去幫工作交差。

因此決定不再回覆信件,有疑問就利用留言功能囉。

訂閱
通知
guest
7 Comments
最舊
最新
Inline Feedbacks
看所有留言
yukohn
yukohn
1 年 之前

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

Chi
Chi
回覆給  Augustus
1 年 之前

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

截圖 2020-06-09 23.45.13.png
Joely
Joely
3 月 之前

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

HowardC
HowardC
1 月 之前

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

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

Let's Write

前端工程師 Augustus 的學習筆記 — solving problems, in simple ways.