Google Sheet 的額外衍生應用之一，就是可以當作簡易的 API Server 來取得 Google Sheet 表單內的資料。當你取得表格內的資料後，再加以編輯一下就可以顯示到前端的畫面上了。此外，透過 Google Sheet API v3 所拿回的資料，預設會帶有 CORS Header，算是挺方便的 ( 截至 2021 年舊版的 API 尚未關閉的情況下 )。
步驟
主要分為兩個部分：「匯入 JSON 到 Google 試算表」和「從 Google 試算表 API 輸出 JSON 」
匯入 JSON 到 Google 試算表
1. 首先，打開一份空的 Google 試算表，點選「工具」中的「指令碼編輯器」
2. 預設會有一個 function ，將其清除後，貼上 import_json_appsscript.js 這段 JavaScript 的內容
3. 同時，要將左邊的檔名改為 ImportJSON.gs ，這也是你在試算表中要取用的函式名稱
4. ImportJSON(“<url>”,”<key1>”,”<key2>”)，會有三個參數。第一個 url 要直接能取到 .json 檔案，最方便的莫過於 Dropbox。但是要記得在分享的連結後面加上 “?dl=1” (“”請忽略)，這樣才能直接下載
5. key1 的部份，是 object 的 key 的關係，下面是一個例子
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
// <key1>為：/data { "data":[ { "name":"name1", "id":1 }, { "name":"name2", "id":2 } ] } |
6. key2 的部份是一些額外的設定，像是 “noInherit,noTruncate,rawHeaders” … 等
從 Google 試算表 API 輸出 JSON
1. 選擇「檔案」，然後點選「發佈到網路」
2. 你可以點選發佈的範圍，如果你沒有太多的想法，那就點選「整份文件」、「網頁」的模式發佈
3. 發佈後，還需要點選右上角的「共用」，取得「複製連結」中的隱藏 key
https://docs.google.com/spreadsheets/d/<隱藏的 key>/edit?usp=sharing
4. 接著 把「隱藏的 key」和 sheet index (從 1 開始)，帶入以下的連結
https://spreadsheets.google.com/feeds/list/{key}/{sheet index}/public/values?alt=json
5. 取得之後，你多半會需要再進行一些額外的處理，才能整理成自己預期的 api 格式。以下是個 Demo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
(async function(){ try{ const { data:rawData } = await getData() const result = rawData.feed.entry.map(d=>{ const newObject = { id:undefined, name:undefined, address:{ full:undefined, city:undefined, area:undefined, zip:undefined }, tel:undefined } const filledKey = ['id','addressarea','addresscity','addressfull','addresszip','name','tel'] filledKey.forEach(key=>{ key.includes('address') ? newObject.address.[`${key.slice(7,key.length)}`] = d[`gsx$address${key.slice(7,key.length)}`].$t : newObject[`${key}`] = d[`gsx$${key}`].$t }) return newObject }) console.log(result) }catch(err){ console.log(err) } }()) |
資料來源
1. How to import JSON data to a Google Sheet
2. 把 Google 試算表當資料庫，並取得 API 實作心得
