Zenn
Closed4

Node.js + TypeScriptでスプレッドシートの行を所得・追加する

Node.js
TypeScript
Google Sheets
catnosecatnose

個人サイトに自分の活動を時系列でまとめたTimelineページを作った。

このTimelineに表示されているイベントの一覧はGoogleスプレッドシートで管理されている。GitHub ActionsでRSSを定期的にフェッチするようになっており、新しいURLのページ（記事）が見つかればスプレッドシートに自動挿入される。

以下Node.js + TypeScriptでスプレッドシートの行を取得・追加する方法を雑にまとめておく。

catnosecatnose

Googleスプレッドシートを操作するための準備

サービスアカウントの設定

  1. GCPコンソールでGoogle Sheets APIを有効にする
  2. GCPコンソールのサービスアカウント設定でサービスアカウントを作成する。ロールは特に指定しなくてOK。ここでサービスアカウントのkeyとなるJSONを取得する
  3. 操作したいスプレッドシートを開き、共有設定にサービスアカウントのアドレス（foo@bar.iam.gserviceaccount.com）を追加する。参考

環境変数の設定

以上の操作が完了したらプロジェクトの環境変数として以下の3つを追加する（ハードコードは避ける）。

SHEET_ID='foo' # スプレッドシートのURLに含まれる文字列
GOOGLE_SERVICE_ACCOUNT_EMAIL='foo@bar.iam.gserviceaccount.com' # サービスアカウントのアドレス
GOOGLE_PRIVATE_KEY='-----BEGIN PRIVATE KEY-----\nabcdefg...' # サービスアカウントのkeyのJSONに含まれる`"private_key"`の値をそのままコピペする
catnosecatnose

パッケージのインストール

ここではgoogle-spreadsheetというnpmパッケージを使うことにする

$ npm i google-spreadsheet
$ npm i --save-dev @types/google-spreadsheet
catnosecatnose

操作する

今回のケースでは複数の場所から繰り返し呼び出したかったので、classを用意することにした。

sheet-service.ts
import { GoogleSpreadsheet } from "google-spreadsheet";
import type {
  GoogleSpreadsheetWorksheet as GoogleSpreadsheetWorksheetType,
  GoogleSpreadsheet as GoogleSpreadsheetType,
} from "google-spreadsheet";

// さっき設定した環境変数
const sheetId = process.env.SHEET_ID
const clientEmail = process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL
const privateKey = process.env.GOOGLE_PRIVATE_KEY

// シートのヘッダー行の値
const sheetHeaderValues = ["title", "url", "date"];

export class SheetService {
  doc: GoogleSpreadsheetType;
  sheet: GoogleSpreadsheetWorksheetType;
  
  // 複数のシートを操作したい場合はconstructorの引数としてsheetIdを渡す形にするのが良さそう
  constructor() {
    //  ブラウザで呼び出した場合はエラーに（環境変数が露出するのを防ぐためにも）
    if (typeof window !== "undefined") throw new Error("DO NOT CALL THIS CLASS IN BROWSER!!!");
    this.doc = new GoogleSpreadsheet(sheetId);
  }
  
  // 本来はconstructor()の中でやりたかったがasync/awaitが使えないので仕方なく分ける
  async init() {
    await this.doc.useServiceAccountAuth({
      client_email: clientEmail,
      private_key: privateKey.replace(/\\n/g, "\n"),
    });

    // prepare for getting sheet values
    await this.doc.loadInfo();
    this.sheet = this.doc.sheetsByIndex[0];
    await this.sheet.loadHeaderRow();

    // 安全のため、シートのヘッダー行が意図している値になっているか確認する
    if (
      JSON.stringify(this.sheet.headerValues) !==
      JSON.stringify(sheetHeaderValues)
    ) {
      console.error(
        `Your sheet must have the following header columns ${sheetHeaderValues
          .map((v) => `"${v}"`)
          .join(", ")} in the exact same order.`
      );
      process.exit(1);
    }
  }

  // 行の一覧を取得する
  async getRows() {
    return await this.sheet.getRows();
  }

  // 行を追加する
  async addRows(newRows: Parameters<typeof this.sheet.addRows>[0]) {
    return await this.sheet.addRows(newRows);
  }
  
  // その他したい操作があれば追加する
  async deleteRow() { ... }
}

あとはこのclassを好きな場所から呼び出す

// すべての行を取得する
const sheet = new SheetService();
await sheet.init();
const allRows = await sheet.getRows();

// 新しい行を追加する
sheetWorker.addRows({
  title: "Title",
  url: "https://example.com",
  date: "2021-12-27"
});
このスクラップは2021/12/27にクローズされました