Vic's Blog

遺忘x回想x自動化

0%

Google試算表作為線上資料庫 - 讀取資料

發表於 分類於
有時抓取開源資料會遇到跨網域問題，透過 Google Apps Script就能解決，而自建的試算表也能作為線上的資料庫使用。

想要弄自己的資料庫，卻又不知道該怎麼做，只知道要建立欄位，而Google的試算表就能達成這件事。只要 :

  • 建立欄位名稱
  • 輸入欄位資料
  • 發布至網路
  • 簡易的程式碼

就能撈取 JSON格式資料並使用 Get、Post等方式來存取了。

建立資料

  • 在試算表中建立一份新檔案
  • 在第一行中輸入欄位名稱(資料庫關係，建議全英文)
  • 在第二行中輸入對應資料，像是這樣
skill description
html 網頁標籤與結構化
CSS/SASS 使用 SASS格式撰寫 CSS

發布資料

發布資料有兩種方式 :

  1. 直接將試算表發佈到網路
    • 提供給所有人檢視
  2. 使用指令碼編輯器撰寫程式碼並發布
    • 知道連結的人可供檢視
    • 可以不公開至網路(像是非公開資料但需要寫入)

這兩種方式中，我們會還會用到

  • 試算表的 ID，可以在試算表的網址列中找到
    • https://docs.google.com/spreadsheets/d/試算表的ID/edit#gid=0
  • 試算表的工作表之索引值
    • 若為一般發布，索引值從 1 開始，也就是第幾張工作表
    • 若使用指令碼編輯器，程式碼中的索引值為陣列，從 0 開始

發布自建試算表

  • 在試算表的工具列中，找到 檔案，這個選項，然後選擇 發佈到網路
  • 按下發布後，對話視窗關掉即可

指令碼編輯器

  • 在試算表的工具列中，找到 工具 這個選項，然後選擇 指令碼編輯器
  • 將預設的 function內容改為以下程式碼
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// 存取試算表資料
// function名稱必須是 doGet，才能使用 get方式取得資料
function doGet() {
  var id = '試算表網址列的 ID'; 
  
  // 看起來很複雜? 其實是一連串拆開後的結果
  var spreadsheet = SpreadsheetApp.openById(id);
  var sheet = spreadsheet.getSheets()[0]; 
  var data = sheet.getDataRange().getValues();
  // SpreadsheetApp.openById(id).getSheets()[0].getDataRange().getValues()

  // 將物件轉為 JSON字串，因為瀏覽器只能使用字串傳輸
  var dataExportFormat = JSON.stringify(data);
  
  // ContentService.createTextOutput('字串') 輸入發布的網址後，伺服器會回傳字串
  // 回傳的東西可以是字串、html以及 JSON
  // setMimeType(ContentService.MimeType.JSON) 要求回傳的資料為 JSON格式
  return ContentService.createTextOutput(dataExportFormat).setMimeType(ContentService.MimeType.JSON);
}
  • 在編輯器的工作列中，找到 發布 這個選項，然後選擇 部屬為網頁應用程式
    • Project version : 每次發布時都需要修改版本編號
    • Execute the app as: 選擇誰可以執行這個 APP
    • Who has access to the app: 選擇誰可以訪問這個 APP
  • 按下更新後，取得該 APP的網址 https://script.google.com/macros/s/一連串的亂碼/exec

讀取資料

跨網域問題

為什麼需要將試算表發布為公開可檢視，是因為我們在撈取資料時，會遇到跨網域存取的問題，存放在 google的資料預設不給其他網站存取，但經過設定後，就能正常存取我們要的資料。另外，像是政府的開源資料，也常會遇到跨網域的問題，原因是同源政策，開源資料的網址在瀏覽器中可以正常讀取，但在測試用的線上程式編輯器就無法存取，因為它們無法跨網域存取資源，透過 Google Apps Script重新發布後，就能解決這個問題。

讀取自建試算表

可以到線上程式碼編輯器等網站測試，codePen或者其他都可以，利用 fetch語法或者套件 axios進行資料撈取。

  1. 讀取 發布至網路 的試算表

    • 其網址為 https://spreadsheets.google.com/feeds/list/試算表的ID/第?張工作表/public/values?alt=json
    • 若要改回非公開，再回到 發佈到網路 ，按下停止發布即可

  2. 讀取 部屬網頁應用程式

    • 不以 發布至網路 形式公開
    • 其網址為 https://script.google.com/macros/s/一連串的亂碼/exec
    • 在網址列輸入後，會轉成另外一串亂碼網址，內容是相同的

作為中繼點後讀取

  • 新建一份試算表
  • 在程式碼編輯器中，將 doGet的內容替換掉
1
2
3
4
5
6
7
8
9
10
11
12
// 作為中繼站，跨網域存取開源資料
function doGet(e){
  // 取得傳入 URL
  var url = e.parameter.url; 
  
  // 發出 GET 請求，並設定 hearder json
  var response = UrlFetchApp.fetch(decodeURIComponent(url),{ 
    headers: { "Content-type" : "application/json" }
  });
  var data = JSON.parse(response.getContentText()); // 取得 json 資料
  return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON); 
}
  • 發布後取得 https://script.google.com/macros/s/試算表的 ID/exec?
  • 在這串網址後方接上開源資料的網址，
    https://script.google.com/macros/s/試算表的 ID/exec?url=開源資料的網址

參考來源

  1. Augustus 的學習筆記 - 如何用Google Excel當作資料庫
  2. Augustus 的學習筆記 - 用Google Apps Script取得Google Excel資料
  3. Ray - 使用 Google Apps Script 解決 CORS 問題
  4. OXXO.STUDIO - 簡易後端實作 ( Google Apps Script )