【車輪の】Githubで実装するスプレッドシートのバックアップ【再発明】 #Node.js

突然ですが皆さん、Googleスプレッドシートは使っておられるでしょうか。
便利ですよね、スプレッドシート。
今日はそんなGoogleスプレッドシートについての話です。

Googleスプレッドシートには編集履歴機能があります。
この編集履歴はバックアップを兼ねており、バックアップ間の差分をセル単位で確認したり、任意の地点に復元・タグ付け・コピーを作成したりとまあまあ便利です。
しかし保存期間に上限があったり、動作が重かったりと若干不便な部分もあります。
過去の履歴・差分を永続的に管理できるような、そんな便利なサービスはどこかに…

ありました。

やったこと

ちょっとプライベートで必要性を感じたので、スプレッドシートの内容をGithubのリポジトリに保存/復元するかんたんな仕組みを作りました。
FetchとGenerateの2つのワークフローをGithub Actionsで用意し、それぞれ以下のような機能を持たせます。

Fetch: スプレッドシートの内容を取得しリポジトリに直接コミットして保存する
Generate: リポジトリに保存された最新の内容で新規スプレッドシートを作成、Googleドライブの規定のフォルダに保存する

機能としてはこれだけですが、Github Actionsをインターフェースとして、リポジトリをそのままストレージとして扱うシンプルな構造に収められました。
何よりリポジトリ単体で完結しているのがいいですね。~~GASなんていらんかったんや~~
今回はとりあえず手動で動かしていますが外部からhttpで呼び出したり定期実行したりといった制御が容易なのもGithub Actionsの仕組みに乗せる利点です。

Fetch workflow

各ワークフローの処理の実態として、node.jsでgoogle APIを呼び出す実装をしてスプレッドシートとのデータのやりとりを行います。

fetch.yml


name: Fetch sheet

on:
  workflow_dispatch:
    inputs:
      sheet_id:
        required: true

jobs:
  fetch:
    # secretsを保存したenvironment
    environment: googleapi
    
    permissions:
      contents: "write"
      id-token: "write"
      
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-node@v3
        with:
          node-version: 20.x
          cache: "npm"
          cache-dependency-path: package/package-lock.json

      # google認証
      - id: "auth"
        name: "Authenticate to Google Cloud"
        uses: "google-github-actions/auth@v1"
        with:
          workload_identity_provider: ${{ secrets.WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.SERVICE_ACCOUNT }}
      
      # nodejs スクリプトの実行
      - run: npm ci
        working-directory: package
      - run: npm run fetch ${{ github.event.inputs.sheet_id }}
        working-directory: package

      # バックアップ時刻の取得
      - env:
          TZ: 'Asia/Tokyo' # タイムゾーン指定
        run: echo "CURRENT_DATETIME=$(date +'%Y-%m-%d %H:%M:%S')" >> $GITHUB_ENV

      # スクリプトで保存したファイルをcommitする
      - id: diff
        run: |
          git add -N .
          git diff --name-only --exit-code
        continue-on-error: true
      - name: Commit & Push
        run: |
          set -x
          git config user.name github-actions[bot]
          git config user.email 128466751+github-actions[bot]@users.noreply.github.com
          git add .
          git commit --author=. -m 'backup ${{ env.CURRENT_DATETIME }}'
          git push
        if: steps.diff.outcome == 'failure'

jsコード側ではsheets APIを叩き、スプレッドシート毎にディレクトリを切ってスプレッドシートの内容を保存します。tsvで保存するとGithubがリポジトリ上でもいい感じに表示してくれます。

fetch.js

import { google } from "googleapis";
import * as fs from "node:fs";

const SCOPES = [
  "https://www.googleapis.com/auth/spreadsheets",
  "https://www.googleapis.com/auth/spreadsheets.readonly",
];

const auth = new google.auth.GoogleAuth({
  scopes: SCOPES,
});

// 対象スプレッドシートの情報取得
const sheetId = process.argv[2];
const sheets = google.sheets({ version: "v4", auth });
const resSheet = await sheets.spreadsheets.get({
  spreadsheetId: sheetId
});

// 取得した情報をsheetdata.jsonに保存
const dstDir = "../backup"
const sheetDir = dstDir + '/' + sheetId
fs.mkdirSync(sheetDir, { recursive: true });
fs.writeFile(sheetDir + "/sheetdata.json", JSON.stringify(resSheet.data, null, 2), (err, data) => {
  if(err) console.log(err);
})

// 各シートのセルの値を取得
const sheetNames = resSheet.data.sheets.map(sheet => sheet.properties.title)
const resRanges = await sheets.spreadsheets.values.batchGet({
  spreadsheetId: sheetId,
  ranges: sheetNames
})

for (var i = 0; i < sheetNames.length; ++i) {
  const sheetName = sheetNames[i]
  const range = resRanges.data.valueRanges[i].values
  const maxlength = range.reduce((acc, arr) => Math.max(acc, arr.length), 0)

  // tsvとして保存する際、列数を最も多い列に合わせる
  const expandedRange = range.map(line => {
    if (line.length < maxlength)
      return line.concat(new Array(maxlength - line.length).fill(""))
    else
      return line
  })

  // 各シートごとに保存
  const content = expandedRange.map(line => line.map(str => str.replaceAll('\n', '\\n')).join('\t')).join('\n') + '\n'
  fs.writeFile(sheetDir + "/" + sheetName + ".tsv", content, (err, data) => {
    if(err) console.log(err);
  })
}

Generate workflow

Generate側もおおむね同じです。jsコードでsheets APIを呼び出してシートを作成後、規定のドライブへと保存します。

generate.yml


name: Generate from Backup

on:
  workflow_dispatch:
    inputs:
      sheet_id:
        required: true

jobs:
  fetch:
    # secretsを保存したenvironment
    environment: googleapi
    
    permissions:
      contents: "read"
      id-token: "write"
      
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-node@v3
        with:
          node-version: 20.x
          cache: "npm"
          cache-dependency-path: package/package-lock.json

      # google認証
      - id: "auth"
        name: "Authenticate to Google Cloud"
        uses: "google-github-actions/auth@v1"
        with:
          workload_identity_provider: ${{ secrets.WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.SERVICE_ACCOUNT }}

      # nodejs スクリプトの実行
      - run: npm ci
        working-directory: package
      - run: npm run generate ${{ github.event.inputs.sheet_id }}
        working-directory: package

generate.js

import { google } from "googleapis";
import { parse } from "csv-parse/sync"
import * as fs from "node:fs";

const SCOPES = [
  "https://www.googleapis.com/auth/drive.file",
  "https://www.googleapis.com/auth/spreadsheets",
  "https://www.googleapis.com/auth/spreadsheets.readonly",
];

const auth = new google.auth.GoogleAuth({
  scopes: SCOPES,
});

const sheets = google.sheets({ version: "v4", auth });
const drive = google.drive({version: "v3", auth});

// 保存先のスプレッドシートを作成
const createdSheet = (await sheets.spreadsheets.create()).data

// sheetdata.jsonの読み取り
const sheetId = process.argv[2];
const dstDir = "../backup"
const sheetDir = dstDir + '/' + sheetId
const savedProperty = JSON.parse(fs.readFileSync(sheetDir + "/sheetdata.json", "utf8"))

// スプレッドシートに書き込む内容を作成する
var requestList = []

// スプレッドシートの基本情報
requestList.push({
    updateSpreadsheetProperties: {
        properties: savedProperty.properties,
        fields: "*"
    }
})
//個別のシートの情報
for (const sheet of savedProperty.sheets) {
    // シートの追加
    requestList.push({
        addSheet: {
            properties: sheet.properties
        }
    })

    // ファイルに保存した値を読み込んでリクエストに合わせた形式に変換
    const content = fs.readFileSync(sheetDir + "/" + sheet.properties.title + ".tsv", "utf8")
    const matrix = parse(content, {delimiter: '\t', relax_quotes: true, escape: '\\'})
    const requestMatrix = matrix.map(row => {
        return {
            values: row.map(value => {
                return {
                    userEnteredValue: {
                        stringValue: value
                    }
                }
            })
        }
    })
    requestList.push({
        updateCells: {
            rows: requestMatrix,
            fields: "userEnteredValue",
            start: {
                sheetId: sheet.properties.sheetId,
                rowIndex: 0,
                columnIndex: 0
            }
        }
    })
}


// まとめて書き込み
sheets.spreadsheets.batchUpdate({
    spreadsheetId: createdSheet.spreadsheetId,
    requestBody: {
        requests: requestList
    }
})
// 作成したシートをGoogleドライブの指定のフォルダに移動
drive.files.update({
    fileId: createdSheet.spreadsheetId,
    addParents: "xxxxx",
    supportsAllDrives: true
})

ケース上必要ではなかったので上記実装では省いていますが、結合されたセルや保護セルの情報などもsheetdata.jsonに記録されています。必要に応じてこのあたりも実装することになります。

ここから色々メモ

Github Actionsとスプレッドシート間での認証について

キーレスで認証できる仕組みがある。既にやってる人が多くいるので参考にするのがいいと思います。

これは個人的なことなんですが、作業中なんか既視感があると思ったら2年前にも全く同じことをやってました（普通に忘れてた）

google APIのローカル開発に必要な認証について

上記キーレス認証を行うコードをローカルでも動作させる際は、gcloud CLIツールを導入してログインしデフォルトの認証情報と割り当てプロジェクトを設定してやる必要があります。
割り当てプロジェクトの意味はよくわかっていません。

gcloud auth application-default login --scopes="https://www.googleapis.com/auth/spreadsheets.readonly,https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/drive.file,https://www.googleapis.com/auth/cloud-platform"
gcloud auth application-default set-quota-project [your GCP project id]

ちなみに手元の環境はWindows PowerShellなのですが、scopesの指定はダブルクォーテーションで囲わないとエラーが出ます。
これが若干謎な挙動をしていて、囲わなくてもスコープ自体は正しく認識されてOAuth2認証画面までは出るのでそこそこハマりました。

よくわからないエラー

gcloud auth application-default login --scopes=https://www.googleapis.com/auth/spreadsheets.readonly,https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
(中略)
ERROR: gcloud crashed (Warning): Scope has changed from "https://www.googleapis.com/auth/spreadsheets.readonly https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/cloud-platform" to "https://www.googleapis.com/auth/spreadsheets.readonly https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/cloud-platform".

足りないもの

ここまで書いてなんですが、今回実装したスプレッドシートのバックアップ機構には欠陥があります。
それはSheets APIではスプレッドシートの書式情報を取得することができない(多分)という点です。
これにより保存→復元の過程で書式情報が完全に失われています。
これは実装中に気付いたのですがこの点に関してはどうにもスマートな解決策が思いつかなかったので、一旦見なかったことにして進めることにしました。
もしかしたらAPIに対する理解が及んでいないだけかもしれないので、より詳しい方がいましたら教えていただけると助かります。

おわり

以上になります。
正直Sheets APIで取得できない情報があることが分かった時点で放り投げかけたのですが、とりあえず形になる部分まで出すことにしました。
まあこれでもそこそこバックアップとして機能するはずなので、適度に活用していこうと思います。
元々必要だから作ったはずなので…

その他参考文献

Google Sheets API
Google Drive API
変更があるときだけコミット [GitHub Actions]
GitHub Actionsで現在日時を取得する
 Google Cloud を利用するCLIアプリケーションの認証