用 Apps Script 把試算表的內容變成可以用 HTTP 請求取得的 JSON

如何用 Apps Script 把 Google 試算表（Spreadsheet）的內容，跑成可以透過 HTTP Request 取得 JSON 格式資料的服務？

關鍵字：Google Apps Script、試算表、JSON

大家有用過 Apps Script (GAS) 嗎？這是 Google 家提供的全 JavaScript 雲端平台，可以用來開發 Google Apps 的加值應用程式。雖然我常常覺得很難寫，但其實從選委會的參選登記系統到選舉公報產生器，都是我用 Apps Script 做出來的。除了做 Google Docs、Slides、Spreadsheets... 等等 apps 的外掛程式以外，也可以產生一般的網頁，並在後端整合 Google Apps 的服務。只要會寫一些簡單的 JavaScript，就可以開發 Google Apps 的外掛。

這篇文章介紹的是，如何用 GAS，將試算表（Spreadsheets/Sheets，也就是俗稱的 Google 的 Excel）的內容，轉成 JSON 的格式，並且使之能透過 HTTP Request 進行讀取。雖然市面上關於要將試算表資料進行加值轉換的需求，有很多的 solution，例如下載成 *.csv 或 *.xlsx，或者轉換成 *.json，但若遭遇需頻繁更新資料的狀況，現有的 solution 可能不是那麼合用，如果改用試算表 + HTTP Request + JSON，就可以保留試算表易於更新的優點。

如果沒有使用過 GAS，需要先到 Google Drive，點開「新增」>「連結到更多應用程式」，找到 Google Apps Script 並且連結到 Google Drive，就可以開始寫程式囉。

在這個案例中，我們會使用到 Spreadsheet App 跟 Content Service。Spreadsheet App 用於讀取試算表，Content Service 用於做 content provider 的服務。

將 GAS 連結到 Google Drive 之後，要開啟新專案。專案中容許兩種檔案格式：用來裝 script 的 *.gs 跟用來放 HTML 的 *.html。在這個案例中，我們只需要 *.gs，直接使用新專案預設的空白 gs file 即可。

處理試算表的基本流程是這樣的：

透過 Spreadsheet App，使用 Spreadsheet ID，讀取 Spreadsheet 檔案
透過 Spreadsheet，使用 Sheet name，讀取 Sheet （也就是 spreadsheet 裡的 tab）
透過 Sheet 讀取特定範圍的 cell 所組成的 range
透過 Range 讀取範圍中 cell 的 value。

所謂的 Spreadsheet ID 就在試算表的網址中，以這個網址為例：

https://docs.google.com/spreadsheets/d/1iBuXb6f-SwA_JCL3BUXaLt32uH0Q5Yw-rPPc1ifnjM0/edit

這個試算表的 ID 就是：

1iBuXb6f-SwA_JCL3BUXaLt32uH0Q5Yw-rPPc1ifnjM0

而 Sheet name 就是那個 tab 的名字。

讀取 Range 時，除了使用「指定左上起始 cell 在 sheet 中位置、讀取行列數」的方法，也可以使用常用在寫試算表公式時的 A1Notation，例如用「1:1」選取完整的第一橫列。

將上述步驟寫成程式碼，就會長這樣：

// 1. SpreadsheetApp -> Spreadsheet
var id = 'SPREADSHEET_ID';
var spreadsheet = SpreadsheetApp.openById(id);

// 2. Spreadsheet -> Sheet
var name = 'SHEET NAME';
var sheet = spreadsheet.getSheetByName(name);

// retrieve the first row as the header containing item name
// 3. + 4. Sheet -> Range -> Value
var item_range = sheet.getRange('1:1').getValues();

// retrieve the first column as the list of keys of each data row
// 3. + 4. Sheet -> Range -> Value
var key_range = sheet.getRange('A2:A').getValues(); 

var items = [];
var keys = [];
  
for (var idx in item_range[0]) {
  // neglect if the value is an empty string 
  if (item_range[0][idx] == '') {
    break;
  }
  items.push(item_range[0][idx]);
}
  
for (var idx in key_range) {
  // neglect if the value is an empty string 
  if (key_range[idx] == '') { 
    break;
  }
  keys.push(key_range[idx]);
}
  
// the number of data rows
var row_number = keys.length; 

// the number of items in data rows
var column_number = items.length; 
  

// get the data excluding the first row (as the item field names)
// 3. Sheet -> Range
var range = sheet.getRange(2,1,row_number,column_number); 

var data = [];

// 4. Range -> Value
var values = range.getValues();

// transform into JSON-like array
for (var row = 0; row < row_number; ++row) {
  var row_object = {};

  for (var col = 0; col < column_number; ++col) {
    var item = items[col];
    row_object[item] = values[row][col];
  }

  data.push(row_object);
}

最後的資料結構轉換依照需求而定，除了範例中的 array，也可以直接用 object (key-value) 的格式。完成讀取試算表、轉換資料的格式之後，接著要將這些內容變成可以用 HTTP Request 取得的 service。基本流程如下：

使用 GAS 專案保留的 doGet 為 function name，用來接收 GET
使用 Content Service 產生 text output
使用 Text Output 設定 response 的 mime type

程式碼如下：

function doGet(e) {
  /* Read the spreadsheet... */
  /* ... assign the results to a var named data */
  var output = ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);
  return output;
}

存檔後，再到「發布」>「部署為網路應用程式」，進行部署。將專案部署成網路應用程式後，就可以獲得一串超長的的網址。在送 GET 給這個網址時，專案會去 call doGet，完成讀取、轉換的動作，然後用 JSON 回應。

完整程式碼放在這個 Gist，可以直接複製到專案中的 gs file 裡。這個 folder 收錄了我用來測試的程式碼跟檔案。只要打開 terminal，跑：

curl -L https://script.google.com/macros/s/AKfycbxHnZHaXzeY4SE2-XYbTH8QDTDH2LF4MHXjMOjutU9Rb_TOtm4/exec

就可以看到 JSON 了。帶 -L 是因為 Google 有 redirect，要帶 -L 才會一路走到最後取得 JSON，用其他方式送 request 要注意這點唷。

網誌搜尋 Search

卡崔納你是否真正感到快樂

用 Apps Script 把試算表的內容變成可以用 HTTP 請求取得的 JSON

留言

張貼留言