用 Google Apps Script 搭配 Google 試算表開一個 API

前言

有時候我們會臨時需要開一個 API 來用，但是卻又不想自己開一個後端，例如像是 Koa、Express 或是 Laravel 等等框架，因為實在太麻煩，因此這時候就可以使用 Google Apps Script 服務來達到這個需求，其中也可以用 Google Apps Script 串接 Google 試算表，將 Google 試算表當作資料庫來使用。

Google Apps Script

首先 Google Apps Script 是什麼呢？Google Apps Script 是 Google 所開發的腳本平台，非常地輕量，起源主要是協助 Google 試算表輔助開發平台，因此 Google Apps Script 非常適合用來串接 Google 試算表，而 Google Apps Script 也非常類似小型的後端伺服器，因此也可以做一些處理。

當然在前面的封面圖中，如果你有認真看你會發現 Google Apps Script 做的事情非常多，例如我先前有寫過一篇文章是「使用 Google Apps Script 解決 CORS 問題」，但這一篇我們就專注於串接 Google 試算表就好。

除此之外 Google Apps Script 的學習成本也相當低，尤其是對於一個 JavaScript 工程師來講會更親民，因為主要語法都是採用網頁程式語言，也就是 CSS、HTML 與 JavaScript 撰寫

那麼接下來就讓我們試著去撰寫 Google Apps Script 吧。

建立試算表

一開始我想先請你試著建立一個 Google 試算表，打開 Google 雲端硬碟，右鍵選擇「Google」 試算表

建立成功後標題你可以在右上角改一下，讓自己好辨識一下

而內容你可以參考我的內容或者你自己打也可以

最後這邊紀錄一下試算表的 Url，等一下你會用到，例如：https://docs.google.com/spreadsheets/d/1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0/edit#gid=0 (該試算表連結只是示範而已)。

建立 Google Apps Script

首先回到 Google 雲端硬碟中建立一個 Google Apps Script，它的位置通常在「更多」裡面

名稱你也可以點一下上方重新命名一下，不然到時候你會找不到 QQ

接下來在下方畫面上你可以看到一個 myFunction 的初始地方，該處就是我們準備撰寫成程式碼的地方

Google Apps Script 起手式

那麼我們該如何起手呢？其實就如同前面所言，Google Apps Script 採用的是網頁程式語言所撰寫

你可以在裡面像寫 JavaScript 一樣宣告變數、console.log 輸出都可以

1
2

const myName = 'Ray';
console.log(myName);

因此完全不用太擔心我們還要額外學什麼，接下來就直接進入正題，也就是搭配 Google 試算表開一個 API。

連接特定 Google 試算表

那麼在 Google Apps Script 中有提供一個方法可以讓你開啟特定 Google 試算表，也就是 SpreadsheetApp.openById 語法，你可以透過這個連結看到官方所提供的範例

1
2

var ss = SpreadsheetApp.openById("abc1234567");
Logger.log(ss.getName());

(附註：Google Apps Script 中大多都是用 Logger.log 語法，如果你想用 console.log 也可以，這兩者語法都有支援。)

那麼 openById 我們要傳入什麼呢？也就是我們要開啟的試算表 ID，因此這邊就會需要剛剛我們建立的 Google 試算表 Url

1

https://docs.google.com/spreadsheets/d/1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0/edit#gid=0

裡面有一段 1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0 這個就是我們要的試算表 ID

1
2

var ss = SpreadsheetApp.openById("1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0");
Logger.log(ss.getName());

這樣子你就可以取得我們剛剛的 Google 試算表，你可以試著將它貼入到 Google Apps Script 中

接下來就可以來按一下上方執行按鈕囉

初次執行 Google Apps Script 的狀況

第一次執行這個 Google Apps Script 時你會跳出一個審查權限的畫面，不用怕按下審查權限

接下來選擇自己當前使用的帳號

最後你必須允許你當前的 Google Apps Script 檔案有權限去讀取試算表，否則你會無法使用

都允許完畢之後，你就可以在下方看到執行結果了，而底下顯示的就是我們剛剛在試算表命名的名稱

取得 Google 試算表內容

接下來我們必須設定要取得試算表的「哪一張工作表」，因為一個 Google 試算表內可能會有多個工作表，因此這邊就必須寫清楚你要取得哪一個試算表，那麼取得的方式有兩種寫法，我都直接提供上來

第一種寫法：

1
2
3
4
5

function myFunction() {
  const ss = SpreadsheetApp.openById("1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0");
  const SheetName = ss.getSheetByName('工作表1');
  ...
}

第二種寫法：

1
2
3
4
5

function myFunction() {
  const ss = SpreadsheetApp.openById("1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0");
  const SheetName = ss.getSheets()[0];
  ...
}

第一種寫法就是直接指定工作表名稱，第二種則是使用 getSheets 回傳一個工作表的陣列透過陣列取值的方式取得，底下我會一率用第一種的寫法。

接下來如果你有嘗試輸出 Logger.log(SheetName); 的話，你會發現 Logger 只會出現 Sheet 的字樣，因為我們只是取得工作表而還沒取出內容，而取出來的方式非常簡單

1
2
3
4
5

function myFunction() {
  const ss = SpreadsheetApp.openById("1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0");
  const SheetName = ss.getSheetByName('工作表1');
  Logger.log(SheetName.getDataRange().getValues());
}

我們要先取得工作表內的全部範圍資料(getDataRange)在取得裡面的內容(getValues)，接下來你再按一次執行就可以看到剛剛建立的 Google 試算表內容

但是我們可以發現使用 getDataRange 語法會連標題都取進來，因此如果你不想要連標題都取得，而是你指定的範圍拉出資料的話，則可以改寫成以下

1
2
3
4
5

function myFunction() {
  const ss = SpreadsheetApp.openById("1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0");
  const SheetName = ss.getSheetByName('工作表1');
  Logger.log(SheetName.getRange('A2:D6').getValues());
}

這樣子你就可以只取得資料而不出現標題列了

恭喜你到這邊已經完成八成囉。

撰寫 Get API

那麼我們已經知道如何取得 Google 試算表內容了，那接下來該如何改成 API 形式呢？其實非常簡單，只需要命名一個 doGet 函式

1
2
3

function doGet() {
  ...
}

反之如果要做 Post 則是 doPost，請務必注意這個名稱是絕對的，如果你對於 HTTP Methods 不熟悉的話，建議你閱讀「淺談 API 與 RESTful API」這一篇。

那麼回歸正題，我們接下來要將剛剛取得的 Google 資料做成 API，我們可以透過官方文件看到 doGet 的範例程式碼如下

1
2
3
4

function doGet(e) {
  var params = JSON.stringify(e);
  return HtmlService.createHtmlOutput(params);
}

因此我們要將內容變成我們串接 Google 試算表的內容，但底下 HtmlService.createHtmlOutput(params) 我們要改用另一個方法，也就是 ContentService.createTextOutput，否則會出現錯誤

1
2
3
4
5
6

function doGet() {
  const ss = SpreadsheetApp.openById("1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0");
  const SheetName = ss.getSheetByName('工作表1');
  const values = SheetName.getRange('A2:D6').getValues();
  return ContentService.createTextOutput(values);
}

接下來你可以嘗試按下執行，你會發現沒有任何問題的。

部署 API

那我們 API 算是撰寫完畢了，因此接下來你要將這個 Google Apps Script 部署出去，否則你無法取得資料，因此點一下上方的「部署」按鈕，然後選擇「新增部署作業」

接下來依照提示選擇部署類型

這邊要選擇「網頁應用程式」

內容的話改成以下，唯一重點在於「誰可以存取」請改成「所有人」，否則等一下你無法測試

沒問題後，你就可以按下部署按鈕，屆時你會在畫面上取得 API Url 複製起來

接下來你可以打開 Postman，然後使用貼上去戳戳看，不意外你會看到以下狀況

而這個畫面上呈現格式你會發現不太正常，主要原因是 createTextOutput 語法導致，因此我們要將資料先轉換成 JSON 格式並設置 content-type，否則預設會是 text/html 而不是我們常用的 JSON 格式

1
2
3
4
5
6
7

function doGet() {
  const ss = SpreadsheetApp.openById("1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0");
  const SheetName = ss.getSheetByName('工作表1');
  const values = SheetName.getRange('A2:D6').getValues();
  const dataExportFormat = JSON.stringify(values);
  return ContentService.createTextOutput(dataExportFormat).setMimeType(ContentService.MimeType.JSON);
}

沒問題後在重新部署一次，請注意每次修正 API 內容都必須重新部署取得新的 API Url，否則你會一直取得舊的資料

重新部署後請記得複製新的 API Url

最後你再拿這個 API Url 到 Postman 戳戳看就可以看到正確結果了

API 格式調整

最後我想補充調整一個地方，也就是我們剛剛有看到 API 回傳的結果是這樣子

1
2
3
4
5
6
7
8
9
10
11
12
13
14

[
    [
        1,
        "原子筆",
        "文具",
        10
    ],
    [
        2,
        "收納櫃",
        "收納",
        5
    ]
]

但正確來講我們要有一個屬性才對，因此程式碼要調整成以下，而這邊我就不再說明了，而是直接提供最終結果給予參考。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

function doGet() {
  const sheetId = '1MQPaKCV446eLXg2mDlLeKpsMc4nRnHDC7swlijAzkz0';
  const sheetName = '工作表1';

  const spreadsheet = SpreadsheetApp.openById(sheetId);
  const sheet = spreadsheet.getSheetByName(sheetName);

  const keys = sheet.getRange('A1:D1').getValues()[0];
  const values = sheet.getRange('A2:D6').getValues();

  const newData = [];
  values.forEach((data) => {
    newData.push({
      [keys[0]]: data[0],
      [keys[1]]:data[1],
      [keys[2]]:data[2],
      [keys[3]]:data[3],
    })
    
  })
  const dataExportFormat = JSON.stringify(newData);
  return ContentService.createTextOutput(dataExportFormat).setMimeType(ContentService.MimeType.JSON);
}

這樣子結果就會是我們常見的 JSON 格式了

1
2
3
4
5
6
7
8

[
    {
        "ID": 1,
        "名稱": "原子筆",
        "分類": "文具",
        "數量": 10
    },
]

當然寫法還可以更漂亮一點，只是這邊就只是一個示範而已哩。

參考文獻

• Google Apps Script
• openById(id)
• doGet