🐕

Node.js(TypeScript)でスプレッドシートを操作する

2022/09/04に公開

Node.js

Google Sheets

tech

はじめに

Node.js(TypeScript)でスプレッドシートを操作する方法の紹介です。

今回試した挙動は下記です。

スプレットシートのタイトルを取得
スプレットシートのタイトルを変更
新規シートを追加
シート名の一覧を取得
1行追加する
複数行追加する
行の一覧を取得
ヘッダを取得
削除対象のシート名を指定して、シートを削除
全てのシートを削除

コードはGitHub上にもあります。

事前準備

Google Sheets API を有効化する
スプレットシート側に設定するサービスアカウントを作成する。
スプレットシートの共有ユーザーに作成したサービスアカウントのアドレスを追加する。

コード

事前準備で作成した情報を環境変数に入れます。

# スプレッドシートのURLに含まれる文字列
SHEET_ID='xxxxx'
# サービスアカウントのアドレス
GOOGLE_SERVICE_ACCOUNT_EMAIL='xxxxx'
# サービスアカウントのkeyのJSONに含まれる`"private_key"`の値
GOOGLE_PRIVATE_KEY='xxxxx'

スプレッドシートの操作を行うコードは以下の通りです。
ライブラリは google-spreadsheet を使ってます。

import { GoogleSpreadsheet, GoogleSpreadsheetWorksheet } from 'google-spreadsheet';
import * as dotenv from 'dotenv';

dotenv.config();

/**
 * GoogleSpreadsheet を操作する Service
 *
 * @see APIドキュメント {@link https://theoephraim.github.io/node-google-spreadsheet/#/}
 */
export class GoogleSpreadsheetService {
  private static instance?: GoogleSpreadsheetService;
  private doc: GoogleSpreadsheet;

  private constructor() {
    this.doc = new GoogleSpreadsheet(process.env.SHEET_ID);
  }

  /**
   * GoogleSpreadsheetService のインスタンスを取得する
   */
  static async getInstance() {
    if (this.instance) {
      return this.instance;
    }

    const instance = new GoogleSpreadsheetService();

    await instance.doc.useServiceAccountAuth({
      client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL ?? '',
      private_key: (process.env.GOOGLE_PRIVATE_KEY ?? '').replace(/\\n/g, '\n'),
    });
    await instance.doc.loadInfo();

    return instance;
  }

  /**
   * スプレットシートのタイトルを取得する
   *
   * @returns スプレットシートのタイトル
   */
  getTitle() {
    return this.doc.title;
  }

  /**
   * スプレットシートのタイトルを変更する
   *
   * @param title 変更後のタイトル名
   */
  async getRenameTitle(title: string) {
    await this.doc.updateProperties({ title });
  }

  /**
   * 新規シートを追加する
   *
   * @param sheetName シート名
   * @param headerValues ヘッダの値
   * @returns 追加したシート
   */
  async addSheet(sheetName: string, headerValues?: string[]): Promise<GoogleSpreadsheetWorksheet> {
    return await this.doc.addSheet({ title: sheetName, headerValues });
  }

  /**
   * 引数で渡したシート名を削除する。
   *
   * @param sheetTitle 削除対象のシート名
   */
  async deleteSheetByTitle(sheetTitle: string) {
    const sheet = this.doc.sheetsByTitle[sheetTitle];

    if (!sheet) {
      return;
    }
    await sheet.delete();
  }

  /**
   * 全てのシートを削除する。
   */
  async deleteAllSheet() {
    const sheets = this.doc.sheetsByIndex;

    Promise.all(sheets.map((s) => s.delete()));
  }

  /**
   * sheet名の一覧を取得する。
   */
  async sheetNames(): Promise<string[]> {
    return this.doc.sheetsByIndex.map((sheet) => sheet.title);
  }

  /**
   * 行を追加する
   *
   * @param sheetTitle シート名
   * @param values 追加する行の値
   */
  async addRow(sheetTitle: string, values: string[] | Record<string, string | number | boolean>) {
    const sheet = this.doc.sheetsByTitle[sheetTitle];

    if (!sheet) {
      return;
    }
    await sheet.addRow(values);
  }

  /**
   * 複数行を追加する
   *
   * @param sheetTitle シート名
   * @param values 追加する行の値の配列
   */
  async addRows(sheetTitle: string, values: (string[] | Record<string, string | number | boolean>)[]) {
    const sheet = this.doc.sheetsByTitle[sheetTitle];

    if (!sheet) {
      return;
    }
    await sheet.addRows(values);
  }

  /**
   * 行の一覧を取得する。
   *
   * @param sheetTitle シート名
   * @param header 項目を取得するヘッダーを指定する
   */
  async getRows(sheetTitle: string, header: string[]) {
    const sheet = this.doc.sheetsByTitle[sheetTitle];

    if (!sheet) {
      return [];
    }
    const rows = await sheet.getRows();

    return rows.map((row) => header.map((h) => row[h]));
  }

  /**
   * ヘッが行を取得する。
   *
   * @param sheetTitle シート名
   * @returns ヘッダの値の配列
   */
  getHeader(sheetTitle: string) {
    const sheet = this.doc.sheetsByTitle[sheetTitle];

    if (!sheet) {
      return [];
    }
    return sheet.headerValues;
  }
}

使われ方のイメージです。

const spreadService = await GoogleSpreadsheetService.getInstance();

// スプレットシートのタイトルを取得
console.log(spreadService.getTitle());

// スプレットシートのタイトルを変更
await spreadService.getRenameTitle('rename title');

// 新規シートを追加
await spreadService.addSheet('newSheet1');
await spreadService.addSheet('newSheet2', ['name', 'email']); // ヘッダーも一緒に設定

// シート名の一覧を取得
console.log(await spreadService.sheetNames());

// 1行追加する
await spreadService.addRow('newSheet1', ['aaa', 'bbb', 'ddd']);
await spreadService.addRow('newSheet2', {name: 'suzuki', email: 'aaa'}); // ヘッダ似合うような形で行を追加

// 複数行追加する
await spreadService.addRow('newSheet1', [['aaa', 'bbb', 'ddd'], ['eee', 'fff]]);
await spreadService.addRow('newSheet2', {name: 'suzuki', email: 'aaa'}, {name: 'sato', email: 'bbb'} ); // ヘッダ似合うような形で行を追加

// 行の一覧を取得
console.log(await spreadService.getRows('newSheet2', ['name', 'email']));

// ヘッダを取得
console.log(await spreadService.getHeader('newSheet2'));

// 削除対象のシート名を指定して、シートを削除
await spreadService.deleteSheetByTitle('newSheet1');

// 全てのシートを削除
await spreadService.deleteAllSheet();

Node.js(TypeScript)でスプレッドシートを操作する

はじめに

事前準備

コード

参考

Discussion