Google sheet 資料 庫

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

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

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

建立資料

• 在試算表中建立一份新檔案
• 在第一行中輸入欄位名稱(資料庫關係，建議全英文)
• 在第二行中輸入對應資料，像是這樣

發布資料

發布資料有兩種方式 :

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() {
  var id = '試算表網址列的 ID'; 
  
  
  var spreadsheet = SpreadsheetApp.openById(id);
  var sheet = spreadsheet.getSheets()[0]; 
  var data = sheet.getDataRange().getValues();
  

  
  var dataExportFormat = JSON.stringify(data);
  
  
  
  
  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){
  
  var url = e.parameter.url; 
  
  
  var response = UrlFetchApp.fetch(decodeURIComponent(url),{ 
    headers: { "Content-type" : "application/json" }
  });
  var data = JSON.parse(response.getContentText()); 
  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 )