使用 JavaScript 透過 Google Sheets API 存取 Google 試算表

Others
, , ,

前置作業

首先要取得一個存取 Google Sheets API 的金鑰。步驟如下：

  1. 前往 Google Cloud。
  2. 在控制台輸入「Google Sheets API」，並啟用。
  3. 點選「IAM 與管理」頁籤，點選「服務帳戶」頁籤，建立一個服務帳戶。
  4. 點選建立好的服務帳戶，點選「金鑰」頁籤，建立一個 JSON 格式的金鑰。
  5. 建立一個試算表，與服務帳戶共用。

建立專案

建立專案。

1
2
mkdir google-sheets-api-example
cd google-sheets-api-example

初始化專案。

1
npm init

修改 package.json 檔。

1
2
3
{
  "type": "module",
}

安裝依賴套件。

1
npm i googleapis

建立 GoogleSheetsClient.js 檔。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
import fs from 'fs';
import { google } from 'googleapis';

/**
 * GoogleSheetsClient class
 * Handles operations with Google Sheets API
 */
class GoogleSheetsClient {
  #spreadsheetId;
  #credentialsPath;
  #auth;
  #sheets;

  /**
   * Constructor
   * @param {Object} params - Constructor parameters
   * @param {string} params.spreadsheetId - Google Spreadsheet ID
   * @param {string} [params.credentialsPath='./credentials.json'] - Path to credentials file
   */
  constructor({
    spreadsheetId,
    credentialsPath = './credentials.json',
  }) {
    this.#spreadsheetId = spreadsheetId;
    this.#credentialsPath = credentialsPath;
    this.#auth = null;
    this.#sheets = null;
  }

  /**
   * Initialize and authorize
   * Establishes connection with Google Sheets API
   */
  async initialize() {
    try {
      const credentials = JSON.parse(fs.readFileSync(this.#credentialsPath, 'utf8'));

      this.#auth = new google.auth.GoogleAuth({
        credentials,
        scopes: ['https://www.googleapis.com/auth/spreadsheets'], // Updated to allow write access
      });

      this.#sheets = google.sheets({ version: 'v4', auth: this.#auth });
      console.log('Google Sheets API authorization successful');

      return this;
    } catch (err) {
      console.error('Initialization error:', err);
      throw err;
    }
  }

  /**
   * Ensures the specified directory exists
   * @param {string} dir - Directory path
   */
  ensureDirectoryExists(dir) {
    if (!fs.existsSync(dir)) {
      fs.mkdirSync(dir, { recursive: true });
      console.log(`Directory created: ${dir}`);
    }
  }

  /**
   * Get list of all worksheets in the spreadsheet
   * @returns {Promise<Array>} Array containing worksheet names and IDs
   */
  async getSheetList() {
    try {
      const { data: { sheets } } = await this.#sheets.spreadsheets.get({
        spreadsheetId: this.#spreadsheetId,
      });

      return sheets.map(({ properties: { title, sheetId } }) => ({
        title,
        sheetId,
      }));
    } catch (err) {
      console.error('Error fetching worksheet list:', err);
      throw err;
    }
  }

  /**
   * Get spreadsheet data for specified range
   * @param {string} range - Range to fetch, e.g. 'Sheet1!A1:D10'
   * @returns {Promise<Array>} Spreadsheet data array
   */
  async getSheetData(range) {
    try {
      const { data: { values: rows } } = await this.#sheets.spreadsheets.values.get({
        spreadsheetId: this.#spreadsheetId,
        range,
      });

      if (!rows?.length) {
        console.log('No data found in specified range');
        return [];
      }

      return rows;
    } catch (err) {
      console.error('Error fetching spreadsheet data:', err);
      throw err;
    }
  }

  /**
   * Get all data from a specific worksheet
   * @param {string} sheetName - Worksheet name
   * @returns {Promise<Array>} Worksheet data array
   */
  getEntireSheetData = (sheetName) => this.getSheetData(`${sheetName}!A:Z`);

  /**
   * Update spreadsheet data for specified range
   * @param {string} range - Range to update, e.g. 'Sheet1!A1:D10'
   * @param {Array} values - 2D array of values to write
   * @returns {Promise<Object>} Update result
   */
  async updateSheetData(range, values) {
    try {
      const result = await this.#sheets.spreadsheets.values.update({
        spreadsheetId: this.#spreadsheetId,
        range,
        valueInputOption: 'RAW', // or 'USER_ENTERED' for formula support
        resource: {
          values,
        },
      });

      console.log(`Updated ${result.data.updatedCells} cells in range "${range}"`);
      return result.data;
    } catch (err) {
      console.error('Error updating spreadsheet data:', err);
      throw err;
    }
  }

  /**
   * Clear and then update sheet data (ensures consistent structure)
   * @param {string} sheetName - Sheet name
   * @param {Array} values - 2D array of values to write
   * @returns {Promise<Object>} Update result
   */
  async clearAndUpdateSheet(sheetName, values) {
    try {
      // First, clear the existing data
      await this.#sheets.spreadsheets.values.clear({
        spreadsheetId: this.#spreadsheetId,
        range: `${sheetName}!A:Z`,
      });

      // Then write the new data
      return this.updateSheetData(`${sheetName}!A1`, values);
    } catch (err) {
      console.error(`Error clearing and updating sheet "${sheetName}":`, err);
      throw err;
    }
  }
}

export default GoogleSheetsClient;

建立 index.js 檔。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
import GoogleSheetsClient from './GoogleSheetsClient.js';

const {
  GOOGLE_SPREADSHEET_ID,
} = process.env;

const client = new GoogleSheetsClient({
  spreadsheetId: GOOGLE_SPREADSHEET_ID,
});

client.initialize();

const sheets = await client.getSheetList();

console.log(`Available sheets: ${sheets.map(sheet => sheet.title).join(', ')}`);

sheets.forEach(async (sheet) => {
  const data = await client.getEntireSheetData(sheet.title);
  console.log(`Data from ${sheet.title}:`, data);
});

執行程式。

1
GOOGLE_SPREADSHEET_ID=your-spreadsheet-id node index.js

程式碼

參考資料