用 Javascript 串接 Google Sheets API 取得試算表資料

前言

學習 GPC 時意外發現有 Google Workspace API 可以用，上網查了一下發現有人串 Google Sheets API 來取得雲端試算表中的資料。

稍微想想，這不就意味著可以拿來當資料庫用（？）

例如有時候有資料儲存的需求不大，只需要有支 API 能吐資料就好，但又覺得不必因此多處理後端和資料庫，如果試算表能作為解決這種需求的方案之一，也蠻值得一用。

目前網路上查到的多半為後端的實作，所以這篇內容會關注在前端串接的做法。

建立一個 GCP 專案

新增專案

先進到 Google Cloud 服務後，開啟新的專案：

輸入專案名稱就建立完成了。

等待通知建立專案完成後，點擊「選取專案」。

啟用 Google Sheet API 服務

進入專案管理頁面後，點擊側邊「API 和服務」標籤，然後選擇「已啟用的 API 和服務」。

點擊上方「啟用 API 和服務」。

在 API 程式庫中往下找到 Google Sheets API 的選項，並點擊進去。

選擇「啟用」。

啟用完成後會來到 Google Sheets API 的詳細資料頁面。

API 憑證選擇

想從試算表取得資料的話，會需要有打 API 的 KEY，而 Google 憑證提供了三種選擇，讓我們可以根據各自的需求選擇。

剛使用的我也太不知道這三種的差別，所以三種都先大概實作過了，以下是我個人選擇憑證的分析：

• API 金鑰
  會得到一組 API_KEY，可以透過這組 KEY 直接打 API，但 Google Sheet 共用選項的一般存取權必須設定為「知道連結的任何人」。

• OAuth 用戶端 ID
  使用者都會需要先登入 Google 帳號，登入的帳號必須要有該 Google Sheet 的訪問權限，才能透過 API 訪問到資料內容。

• 服務帳戶
  會得到一個 Google 服務帳戶，透過將該服務帳戶分享給 Google Sheet 後，再透過該服務帳戶身份打 API 來取得資料。

API 金鑰因為必須將 Google Sheet 權限設為公開，所以比較適合資料可以公開的情況。

而 OAuth 用戶端 ID 則適合登入的帳號都有權限，但又希望能以 API 的方式取得 Google Sheet 資料的情況。

實際情況來說，API 金鑰和 OAuth 用戶端 ID 會比較適合直接在前端使用。

服務帳戶則比較適合在後端使用，因為會需要存取服務帳戶本身的資料與私鑰，直接放在前端會有安全性的問題。

而且不管是否有公開 Google Sheet 都能得到資料，如果需要權限功能也能由後端處理。

下面的內容我會分別寫這三種憑證的實作方式，可以根據自己的需求跳著觀看。

使用 API 金鑰

取得 API 金鑰

首先進入「API 與服務」選單中的憑證頁面，點擊上方的「建立憑證」後選擇「API 金鑰」。

接下來就會獲得一組金鑰，但要注意的是下面有提到金鑰沒有設定限制，這邊建議先點擊連結設定。

進入頁面後，需要滑到下方設定應用類型和允許的網域，如果開發時會使用到 Development Server，例如使用的是 http://localhost:3000，開發階段也可以一併設定進去。

往下滑會看到「API 限制」的選項，建議可以開啟。我只使用 Google Sheets API，所以只設定這一項即可。

取得 Google Sheets 權限

串接 API 前還需要設定一下雲端試算表的權限。

如果是使用 API 金鑰，需要在試算表的共用權限中將「一般存取權」設定為「知道連結的任何人」。

如果沒設定的話，API 時會無法取得資料：

取得試算表 ID

打 API 會需要 試算表的 ID，可以直接從試算表頁面的網址中取得，就是下方大括號起來的部分：

# 試算表網址範例，其中 1wV33k2UTB80X7uTFbKBvwJxIYd8pkvmC75EBmsod55M 就是試算表的 ID
https://docs.google.com/spreadsheets/d/{1wV33k2UTB80X7uTFbKBvwJxIYd8pkvmC75EBmsod55M}/edit#gid=0

API 金鑰取得資料實作

詳細可以參考官方 spreadsheets.values.get 文件，下面直接附上程式碼：

const apiKey = "<你的 API KEY>";
const sheetId = "<你的試算表 ID>";
// Sheets 中要取得的資料範圍，格式如下
const range = "工作表1!A1:B5";
// Sheets API 的 URL
const url = `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/values/${range}?key=${apiKey}`;
 
// 使用 fetch 打 API
fetch(url)
  .then((response) => response.json())
  .then((data) => {
    console.log(data);
  })
  .catch((error) => console.error("Error:", error));

成功取得資料～

OAuth 用戶端 ID

以下實作主要參考官方的 JavaScript 快速入門導覽課程。

設定 OAuth 同意畫面

首先進入「API 與服務」選單中的 OAuth 同意畫面，「User Type」選擇外部後點擊「建立」。

接下來參考右邊的說明自行填寫內容，只有應用程式名稱、使用者支援電子郵件、開發人員聯絡資訊的電子郵件為必填。

範圍部分則根據自己需求設定，這邊我直接按「儲存並繼續」。

測試使用者為使用 OAuth 用戶端的應用程式尚未發布時，能使用 API 的帳號，這邊要設定一組自己測試用的 Google 帳號。

如果沒設定的話，驗證登入時會遇到權限問題：

設定 OAuth 用戶端 ID

設定完成後回到憑證頁面，選擇「OAuth 用戶端 ID」。

接下來需要設定應用程式類別、名稱、已授權的 JavaScript 來源。

應用程式類別選擇「網頁應用程式」，名稱可以隨意填寫，已授權的 JavaScript 來源要填寫會使用這個 OAuth 用戶端 ID 的網址，如果開發有使用 Development Server，例如使用的是 http://localhost:3000，也要被設定才能測試。

建立完成後就會跳出設定完成的頁面，需要記得用戶端編號，稍後串接 API 時會使用到。

取得 API 金鑰

使用 OAuth 用戶端 ID 也會需要使用到 API 金鑰，可以參考上方使用 API 金鑰段落實作。

要特別注意的是，使用 OAuth 用戶端 ID 登入的帳號也必須也要有 Google Sheets 的權限，否則一樣無法取得資料。

可以參考上方取得 Google Sheets 權限段落，在「共用」中新增有權限的使用者。

然後參考取得試算表 ID段落，取得你要使用的試算表 ID。

OAuth 用戶端 ID 取得資料實作

這邊參考了官方 JavaScript 快速入門導覽課程中教學範例的程式碼，如果使用 JS 可以直接參考。

這邊放上我參考之後改用 Vue 的實作，但因為程式碼稍長不適合直接放在文章中，所以我改放在 Github 上，可以到這邊參考。

範例程式碼效果，如果 gapi 和 gis 載入成功就會顯示驗證按鈕。

點擊驗證後選擇「繼續」即可。

成功取得資料！

服務帳戶

建立服務帳戶

進入「API 與服務」選單中的憑證頁面，點擊上方的「建立憑證」後選擇「服務帳戶」。

輸入想使用的服務帳戶名稱後，下方服務帳戶 ID 會被自動填入，然後選擇「建立並繼續」。

第二步設定服務帳戶的專案存取權，因為是選用所以可以跳過，這邊我設定角色為「擁有者」。

最後可以設定能存取服務帳戶的用戶，一樣選用，我沒有需求所以跳過，點擊「完成」就建立成功了。

新增服務帳戶金鑰

服務帳戶建立完成後回到憑證頁面，可以看到下方服務帳戶區塊已經有我們剛剛新增的服務帳號，接下來我們需要取得這個服務帳戶的金鑰。

點擊「服務帳戶」區塊旁的「管理服務帳戶連結」。

在頁面中選擇我們要使用的服務帳戶，在動作選項中點擊「管理金鑰」。

在金鑰頁面中選擇「建立新的金鑰」。

金鑰類型選擇「JSON」後點擊「建立」。

建立金鑰完成後會自動下載一包 JSON 檔，並提醒你私鑰已經儲存。

JSON 檔中的格式如下，後面取得資料時會用到裡面的 private_key。

設定 Google Sheets 權限

設定服務帳戶完成後，需要將前面建立的服務帳戶電子郵件添加到試算表的權限中。

服務帳戶取得資料實作

服務帳戶的實作部分需要使用到後端，這邊我使用 Node.js 開一支 API /sheet-data 來取得試算表的資料，我的實作範例如下：

const { google } = require("googleapis");
const express = require("express");
 
const app = express();
 
const SHEET_ID = "<你的 Google Sheet ID>";
const RANGE = "<你的 Google Sheet 資料範圍>";
const SERVICE_ACCOUNT = "<你的服務帳戶>";
// 如果是部署到 Vercel 並透過 env 設置，金鑰字串需要使用 `replace(/\\n/g, "\n")` 處理，否則會無法正常運作。
// 相關 issue：https://github.com/vercel/next.js/discussions/38430
const PRIVATE_KEY = "<服務帳戶金鑰的 private_key>";
 
const jwtClient = new google.auth.JWT(SERVICE_ACCOUNT, null, PRIVATE_KEY, [
  "https://www.googleapis.com/auth/spreadsheets",
]);
 
jwtClient.authorize(function (err) {
  if (err) {
    console.log(err);
    return;
  }
});
 
app.get("/sheet-data", (req, res) => {
  const sheets = google.sheets({ version: "v4", auth: jwtClient });
  sheets.spreadsheets.values.get(
    {
      spreadsheetId: SHEET_ID,
      range: RANGE,
    },
    (err, response) => {
      if (err) {
        console.error("The API returned an error: " + err);
        res.status(500).send("Error");
        return;
      }
      res.send(response.data.values);
    },
  );
});
 
const port = process.env.PORT || 3000;
app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

處理好 Node Server 後，前端就可以直接打 API 取得資料了：

fetch("http://localhost:3000/sheet-data")
  .then((response) => response.json())
  .then((data) => {
    console.log(data);
  })
  .catch((error) => console.error("Error:", error));

成功～

另外 Node.js 的實作部分也能透過 google-spreadsheet 這個套件處理，可以參考這篇好文章：【我可以你也可以的Node.js】第二三篇 - 蛤！原來串接 Google Sheet API 那麼簡單? - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

結語

以上分享了前端使用三種憑證串接 Google Sheets API 的方法。

剛開始串接時搞不清楚，不知不覺繞了一圈都給它試了一遍，就索性寫文章紀錄下來，結果就是跨年連假有一半都獻給這篇了😅

熟悉設定後其實 GCP 的調整上不會花費太多時間，也比想像中方便。

因為內容比較多，如果有疏漏或補充都歡迎在下面留言，希望這篇能幫助到閱讀完這篇文章的各位。

參考資料

• Google Sheets API 總覽  |  Google for Developers
• JavaScript 快速入門導覽課程  |  Google Sheets  |  Google for Developers
• 【我可以你也可以的Node.js】第二三篇 - 蛤！原來串接 Google Sheet API 那麼簡單? - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天