透過 Google Apps Script 抓取 Google 試算表資料並轉換為 JSON 格式 API

之前有一篇提到透過 Google Apps Script 抓取 Google 試算表資料，這篇來進一步說明怎麼把抓出來的資料轉換為 JSON 格式，這樣便可以直接使用 Google App Script（以下簡稱 GAS） 來製作 API 而不用仰賴自己設計的資料庫了。

Step 1: 建立一支 Google App Script

Step 2: 建立一個 Google 試算表

這邊簡單的輸入一些資料，等一下要來抓取。

Step 3: 撰寫 Google App Script

建立一個 function 名為 doGet()，GAS 發布後會執行這裡面的 code

function doGet() {

}

在裡面抓取你要抓的 Google 試算表，並指定要抓的試算表是哪一個。

let spreadsheet = SpreadsheetApp.openByUrl(URL);
let sheet = spreadsheet.getSheetByName(SheetName);

其中 URL 改成建立的 google 試算表的網址，而 SheetName 則是那個網址裡哪個試算表，預設應該是會試算表1，在頁面下方的頁籤處。

接著設定你要抓的試算表的範圍，如果要全部抓的話就像下面這樣寫 getRange 這個函式丟進去的參數就可以決定要抓的範圍。

let rows = sheet.getRange(1,1,sheet.getLastRow(), sheet.getLastColumn()).getValues();

把輸出的格式轉換成方便抓取的格式，像我這邊就依序把欄位內容丟到個別的 key 裡：

let data=[];
  for(let i = 1; i < rows.length; i++) {
    let dataRow=rows[i];
    let record = {};
    record['id'] = dataRow[0];
    record['name'] = dataRow[1];
    record['email'] = dataRow[2];
    data.push(record);
  }

最後再將資料轉換成 JSON 格式輸出：

var dataExportFormat = JSON.stringify(data);
return ContentService.createTextOutput(dataExportFormat).setMimeType(ContentService.MimeType.JSON);

Step 4: 部署 GAS

要能夠讓外部可以抓取，就要部署 GAS，按一下右上角的按鈕，選擇新增部署作業，並設定網頁應用程式。

設定完後會看到像這樣的畫面，複製下面的網址，貼到瀏覽器的網址列或是像 postman 這樣就可以測試 API 了。

API 會長這樣

這樣差不多就大功告成了。

要額外提的就是如果 GSA 有變動，就需要更新部署作業，它的介面比較奇怪，你要在管理部署作業那邊選擇建立新版本，之後才會抓到新的內容，不過外部的 Google 試算表不用動，只要寫好範圍，有更新就會自動抓取內容了。