Google Spread Sheet のデータを毎分チェックし、変更があったら Firestore に保存する(Node.js)

TL;DR

タイトルから全てを察した方はこちらで十分かと思います。

import * as functions from "firebase-functions";
import admin from "firebase-admin";
import { google, sheets_v4 } from "googleapis";

// 公開関数
export const checkSpreadSheet = functions
  .region("asia-northeast2")
  .pubsub.schedule("every 1 minutes")
  .onRun(async () => {

    const sheetId = "スプレッドシートのID"
    const ranges = [
      {
        sheet: "シート",
        start: "A1",
        end:"B1"
      }
    ];

    const sheetData = await getSpreadSheetData(sheetId, ranges);
    const modifiedTime = await getModifiedTime(sheetId);
    await store(modifiedTime, sheetData);

  });

// 引数用定義
export interface SpreadSheetRange {
  sheet: string;
  start: string;
  end: string;
}

// シートの情報を取得
async function getSpreadSheetData(
  id: string,
  params: SpreadSheetRange[]
): Promise<sheets_v4.Schema$Spreadsheet> {
  const auth = await google.auth.getClient({
    scopes: ["https://www.googleapis.com/auth/spreadsheets"]
  });
  const sheets = google.sheets("v4");
  const spreadsheetId = id;
  const ranges: string[] = [];
  for (const r of params) {
    ranges.push(`${r.sheet}!${r.start}:${r.end}`);
  }
  const res = await sheets.spreadsheets.get({
    auth,
    spreadsheetId,
    ranges,
    includeGridData: true
  });
  return res.data;
}

// 更新日取得
async function getModifiedTime(fileId: string): Promise<string> {
  const auth = await google.auth.getClient({
    scopes: ["https://www.googleapis.com/auth/drive"]
  });
  const d = google.drive("v3");
  const res = await d.files.get({
    auth,
    fileId,
    fields: "modifiedTime"
  });
  return res.data.modifiedTime as string;
}

// firestore に保存
async function store(
  modifiedTime: string,
  data: sheets_v4.Schema$Spreadsheet
): Promise<void> {
  if (data.sheets) {
    const f = admin
      .firestore()
      .collection("spreadsheet")
      .doc(data.spreadsheetId as string);
    const ss = await f.get();
    const ssdata = ss.data();
    if (ssdata && ssdata.lastUpdate === modifiedTime) {
      return;
    }
    const d = ssdata ? ssdata : {};
    d.lastUpdate = modifiedTime;
    const items: string[] = [];
    for (const s of data.sheets) {
      if (!s.data) continue;
      for (const col of s.data) {
        if (!col.rowData) continue;
        for (const row of col.rowData) {
          if (row.values && row.values.length !== 0) {
            items.push(row.values[0].formattedValue as string);
          }
        }
      }
    }
    d.items = items;
    await f.set(d);
  }
}

Google Spread Sheet を簡易 CMS として使う

ウェブサイトを作っていると、「この部分は更新できるようにしたい！」と言われる事が良くあります。
大がかりなものであればCMSを入れて対応するのが良いと思うのですが、「お知らせだけ」とか、「トップページの文言だけ」が対象だった場合は、そのためだけにCMSを入れるのはためらいますし、かといって自分でCRADのUIを作るのは面倒です。

僕はそういった場合にGoogle Spread Sheet を使用して簡易的なCMSとしてしまう事が多いです。
以前はこのライブラリを介して、リクエストの度に直接 Google Sheets API を叩いていました。

懸念

ただ、 Google Sheets API 自体に下記の制限があります。

• プロジェクト毎に 500リクエスト / 100秒
• ユーザー毎に 100リクエスト / 100秒

小規模の案件であれば問題にならなそうですが、少し規模が大きくなると不安になってくる数字です。
何より、webという不特定多数のユーザーからアクセスされる環境の中、制限を下回る確実な補償がない以上「場合によっては制限を超えるかもしれない」という懸念を抱えながら運用しなければいけません。

Google Spread Sheet + Cloud Functions + Firestore

そこで、今回

• Google Spread Sheet のデータを
• Cloud Functions で1分毎に更新チェックして
• 変更があれば Firestore に保存する

という方法を採ってみたので、記事として共有しようと思います。

サービスアカウントを取得する / APIを有効にする

こちらの記事を参考に、サービスアカウントの情報を取得します。

今回は更新日時を取得するために Google Drive API も使用するので、 Google Sheets API と合わせて有効にしておいてください。

必要な機能を実装する

ここから実際にコードを書いていきますが、順を追って説明していこうと思います。
また、全編を通して TypeScript で記述しています。

Firebase を初期化

この辺りはやり方も流儀も色々なので、ここでは詳しく触れません。
公式ドキュメントの通りにするのが一番シンプルかなと思います。

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

初期化が終わったら、今回必要な依存パッケージを入れます。

npm i -S googleapis firebase-admin

スプレッドシートの情報を取得

importは省略

// 引数用の定義
export interface SpreadSheetRange {
  sheet: string;
  start: string;
  end: string;
}

// シートの情報を取得
async function getSpreadSheetData(
  id: string,
  params: SpreadSheetRange[]
): Promise<sheets_v4.Schema$Spreadsheet> {
  const auth = await google.auth.getClient({
    scopes: ["https://www.googleapis.com/auth/spreadsheets"]
  });
  const sheets = google.sheets("v4");
  const spreadsheetId = id;
  const ranges: string[] = [];
  for (const r of params) {
    ranges.push(`${r.sheet}!${r.start}:${r.end}`);
  }
  const res = await sheets.spreadsheets.get({
    auth,
    spreadsheetId,
    ranges,
    includeGridData: true
  });
  return res.data;
}

getSpreadSheetData が本体で、 SpreadSheetRange はその引数のための定義になります。
sheet で指定したシートの内容を、 start で指定したセルから、 end で指定したセルまで読み込みます。また、引数を配列で取っている通り、複数のシートから同時にデータの取得ができます。

基本的には素直に Google Sheets API を呼び出しており、戻り値もAPIが返したデータそのままです。
呼び出しは以下のようになります。

const sheetData = await getSpreadSheetData("対象のスプレッドシートID", [
  {
    sheet: "シート",
    start: "A1",
    end:"B1"
  }
]);

返り値は Schema$Spreadsheet になります。
定義は以下にソースがありますが、VSCode等でインテリセンスを見た方が早いと思います。

更新日を取得

importは省略

// 更新日取得
async function getModifiedTime(fileId: string): Promise<string> {
  const auth = await google.auth.getClient({
    scopes: ["https://www.googleapis.com/auth/drive"]
  });
  const d = google.drive("v3");
  const res = await d.files.get({
    auth,
    fileId,
    fields: "modifiedTime"
  });
  return res.data.modifiedTime as string;
}

Google Sheets API だけでスプレッドシート自体の最終更新日が分かれば良かったのですが、僕にはその方法が見つけられなかったので、Drive API を使用してドキュメント自体の更新日を取得しています。
ここで、fields になにも指定しないと最低限の情報しか取得できなかったので、ご注意ください。

使い方は、説明するまでもありませんが以下の通りです。

const modifiedTime = await getModifiedTime("対象のスプレッドシートID");

Firestore に保存

importは省略

// firestore に保存
async function store(
  modifiedTime: string,
  data: sheets_v4.Schema$Spreadsheet
): Promise<void> {
  if (data.sheets) {
    const f = admin
      .firestore()
      .collection("spreadsheet")
      .doc(data.spreadsheetId as string);
    const ss = await f.get();
    const ssdata = ss.data();
    if (ssdata && ssdata.lastUpdate === modifiedTime) {
      return;
    }
    const d = ssdata ? ssdata : {};
    d.lastUpdate = modifiedTime;
    const items: string[] = [];
    for (const s of data.sheets) {
      if (!s.data) continue;
      for (const col of s.data) {
        if (!col.rowData) continue;
        for (const row of col.rowData) {
          if (row.values && row.values.length !== 0) {
            items.push(row.values[0].formattedValue as string);
          }
        }
      }
    }
    d.items = items;
    await f.set(d);
  }
}

最初に更新日判定をしていて、更新日が同じだった場合は何も処理をしないようにしています。
それ以降の部分は、スプレッドシートがどういったフォーマットで書かれているかの仕様次第になるので、ケースによって最適解は大きく異なってくると思います。
今回は指定の列にあるテキストだけを拾ってくれば良い仕様だったので、スプレッドシートIDを用いてドキュメントを作り、その中の Array にデータを入れるような作りになっています。

前に出てきた2つの関数と合わせて使う事しか想定していないので、呼び出し方は割愛します。

Cloud Functions に登録する

前章で書いた機能を合わせ、以下のようなコードで今回の目的が達成できます。

const sheetId = "スプレッドシートのID"
const ranges = [
  {
    sheet: "シート",
    start: "A1",
    end:"B1"
  }
];

const sheetData = await getSpreadSheetData(sheetId, ranges);
const modifiedTime = await getModifiedTime(sheetId);
await store(modifiedTime, sheetData);

これを、Cloud Functions に登録できる形にすると

export const checkSpreadSheet = functions
  .region("asia-northeast2")
  .pubsub.schedule("every 1 minutes")
  .onRun(async () => {

    const sheetId = "スプレッドシートのID"
    const ranges = [
      {
        sheet: "シート",
        start: "A1",
        end:"B1"
      }
    ];

    const sheetData = await getSpreadSheetData(sheetId, ranges);
    const modifiedTime = await getModifiedTime(sheetId);
    await store(modifiedTime, sheetData);

  });

このような形になります。
ここまでの全ての定義を1つのファイルに書き、importを足すと冒頭のコードになります。region や schedule その他定数は要件に合わせて変更して下さい。

終わりに

今後も良く使いそうだったので、自分用のメモも兼ねて今回まとめてみました。
ざっとググってみても同じような考えの方はたくさんいらっしゃるので、これが何番煎じの記事かは分かりませんが、同じような事をしようとしている人の労力を少しでも減らせれば幸いです。