【Node.js + Express】Google スプレッドシートを無理やりデータベース化した話

## はしがき
この前遊びでちょっとしたワークフローサービスみたいなのを作ったときの話。
MySQL等の本物DBは扱いが面倒くさいのと、デプロイしても無料でデータベースを使いたいという理由からGoogle スプレッドシートを無理やりDB運用しました。
Google スプレッドシートだとデータベースへのアクセス・確認と直接のデータ操作が容易でUIもきれいなので**非エンジニアでも使えるDB**として個人的には結構有用かと思います。
色々な記事を参考にしながら作りましたが、バージョンの違いや認証方法の違いなどで苦労しつつ、サービスアカウントを使った、一番簡単な方法で実装できたかと思います。

## 環境
フレームワーク: Node.js
API: Google APIs

## やり方
### Google Cloudでの作業
[Google Cloud]("https://cloud.google.com/f")にアクセスしてコンソールを開き、新規プロジェクトを作成してください。
ナビゲーションメニューから`APIとサービス`に進んで、`Google Sheets API`を有効にしてください。
![CleanShot 2025-01-31 at 23.18.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/ede8c70e-562f-6494-7408-fc7d17ce0f54.png)

![CleanShot 2025-01-31 at 23.19.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/91814456-390a-85e5-1d10-349a2b06e058.png)

続きまして、`APIとサービス`>`認証情報`に進んでください。
`認証情報を作成`から`サービスアカウント`を選んでください。
![CleanShot 2025-01-31 at 23.09.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/36fbd7d7-703b-e21f-94e5-6b77f929adae.png)

適当なIDを付けたあと、`このサービスアカウントにプロジェクトへのアクセスを許可する`で編集者ロールを付けてあげてから作成してください。
![CleanShot 2025-01-31 at 23.22.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/a0a7cb43-e64e-80b2-552e-82dde2de0cab.png)

作成後、作ったサービスアカウントを選択して`鍵`から`キーを追加`>`新しい鍵を作成`に進んでください。
形式はそのままJSONにしてください。作成すると自動的にファイルがダウンロードされます。これは後で使うものです。

:::note alert
このJSONファイルはその名の通り**鍵**です。絶対に外部へ流出しないようにしてください。
:::

![CleanShot 2025-01-31 at 23.15.32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/c76655f9-7aeb-7378-6243-b5129e447f2f.png)

Google Cloudでの作業は以上ですが、最後にサービスアカウントのIDをコピーしてください。
![CleanShot 2025-01-31 at 23.37.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/ff05b0ea-2399-19aa-c91d-95b9c320e62f.png)

## Google スプレッドシートでの作業
DB用のスプレッドシートを作ります。
適当に命名した後、共有から先程コピーしたサービスアカウントのIDで共有してください。
![CleanShot 2025-01-31 at 23.36.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/62a66a8a-feff-e8a5-e48d-941cfb3fe57b.png)

Google スプレッドシートでの作業はこれだけです。

## APIのインストール&コーディング
プロジェクトディレクトリでターミナルから`Google APIs`をインストールします。
```terminal:ターミナル
npm install googleapis
```
先ほどダウンロードされたJSONファイルを任意のフォルダ(publicとか)に投げます。
導入したいjsファイルで以下のコードを挿入します。
```javascript
// モジュール関係/定数定義
const KEYFILE_PATH = "[先ほどのJSONファイルのパス]";
const { google } = require("googleapis");
// SPREADSHEET_IDはスプレッドシートのURLの~/spreadsheets/d/[ココ]/edit~の[ココ]の部分です。
const SPREADSHEET_ID = "[スプレッドシートのID]"

//認証関係(Google スプレッドシート用です)
function googleAuthenticate(){
    console.log("||Start Authentication||")
    try {
        // ここで認証。
        const auth = new google.auth.GoogleAuth({
            keyFile: KEYFILE_PATH,
            scopes: ['https://www.googleapis.com/auth/spreadsheets'],
        });
        console.log("||Authentication successful||");
        // ここでGoogle Sheets APIを返しています。なので関数名とはちょっと矛盾しています(笑)。
        return google.sheets({version: "v4", auth});
    } catch(error) {
        console.log(error);
    }
}
```
以上がGoogle スプレッドシートを操作するために必要な認証関係です。

### サンプルコード
以下で操作のサンプルコードを紹介します。

:::note info
関数以外のコードはExpressのgetやpostの中(?)に書かないとエラーを吐きます。
requireを使うのがだめなようなのですが、今回はExpress環境で動くので良いことにさせてください。
:::

↓今回のサンプルデータベース
![CleanShot 2025-02-01 at 00.43.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/3c3cbf19-1850-5a25-314b-436c7d846f09.png)

Google SheetsをDB化するにあたって取得したデータからはレコードの行番号を取得できないのでF列に`=ROW()`を入れることで行番号を無理やり取得できるようにしています。工夫ポイントです(^^)

### データ取得(Select)
```javascript:データ取得
async function getData(sheet){
    // Google Sheets APIのインスタンス化
    const sheets = googleAuthenticate();
    try {
        // データ取得
        const result = await sheets.spreadsheets.values.get({
            spreadsheetId: SPREADSHEET_ID, // スプレッドシートのID
            range: `${sheet}!A2:F` // 取得する範囲指定
        });
        return result.data;
    } catch (error) {
        console.log(error);
    }
}

const data = await getData("シート1");
console.log(data);
```
#### 実行結果
```javascript:ターミナル
||Start Authentication||
||Authentication successful||
{
  range: "'シート1'!A2:F1000",
  majorDimension: 'ROWS',
  values: [
    [ '1', '渋沢 栄一', '91', 'Male', '実業家', '2' ],
    [ '2', '津田 梅子', '64', 'Female', '女子教育家', '3' ]
  ]
}
```

:::note info
Google Sheets APIの戻り値はオブジェクトです。
データは`values`に2次元配列でレコードごとに格納されています。
なのでデータだけを取り出したければ`console.log(data.values);`になります。
また、要素はすべて文字列になります。数値などは適宜キャストする必要があります。
:::

### データ書き込み(Insert)
```javascript:データ書き込み
// この関数は最終行のレコードを取得するための関数です。
const getLastData = async (sheet) => {
    const data = await getData(sheet);
    if(data.values === undefined){
        return null;
    }
    return data.values[data.values.length - 1];
}


async function addData(sheet, name, age, sex, job){
    // Google Sheets APIのインスタンス化
    const sheets = googleAuthenticate();
    try {
        // IDインクリメントのために最終行のレコードを取得
        const lastData = await getLastData(sheet);
        // インクリメント処理
        let incrementId;
        if(lastData === null){
            incrementId = 1; // データがまだなければIDは1
        }else{
            incrementId = parseInt(lastData[0]) + 1; //最終レコードのID + 1
        }
        
        // データ追加(append)
        const result = await sheets.spreadsheets.values.append({
            spreadsheetId : SPREADSHEET_ID,
            range: `${sheet}!A1`, // テーブルの左上端を指定。appendだと最後に挿入してくれます。
            valueInputOption: "USER_ENTERED", // 入力データの解釈方法
            insertDataOption: "INSERT_ROWS", // 入力データの挿入方法　
            // 追加するデータ
            resource:{
                values: [[incrementId, name, age, sex, job, "=ROW()"]]
            }
        })
        console.log("||New record saved||");
        console.log(result.data);
    } catch (error) {
        console.log(error);
    }
}

            // シート名, 名前, 年齢, 性別, 職業
await addData("シート1", "野口 英世", 51, "Male", "細菌学者");
const data = await getData("シート1");
console.log(data);
```
#### 実行結果
```javascript:ターミナル
||Start Authentication||
||Authentication successful||
||Start Authentication||
||Authentication successful||
||New record saved||
{
  spreadsheetId: '1npUO3tdGDw73HwUGBt93OffZQv9H_lLI2TmGtzZ9aiY',
  tableRange: "'シート1'!A1:F3",
  updates: {
    spreadsheetId: '1npUO3tdGDw73HwUGBt93OffZQv9H_lLI2TmGtzZ9aiY',
    updatedRange: "'シート1'!A4:F4",
    updatedRows: 1,
    updatedColumns: 6,
    updatedCells: 6
  }
}
||Start Authentication||
||Authentication successful||
{
  range: "'シート1'!A2:F1001",
  majorDimension: 'ROWS',
  values: [
    [ '1', '渋沢 栄一', '91', 'Male', '実業家', '2' ],
    [ '2', '津田 梅子', '64', 'Female', '女子教育家', '3' ],
    [ '3', '野口 英世', '51', 'Male', '細菌学者', '4' ]
  ]
}
```
![CleanShot 2025-02-01 at 17.09.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/5b62010a-4c92-f350-b5bc-1d695ec575e2.png)

:::note info
`valueInputOption: `というのはデータを入力した際に数式(`=SUM()`や`=ROW()`)があった際にどう処理するかというものです。`"USER_ENTERED"`の場合、そのまま数式として処理されます。`"RAW"`とすると文字列として処理されます。
今回`"USER_ENTERED"`で`"=ROW()"`をインデックス5番に指定しているので、5列目(F列)に動的な行番号が記録されます。
:::

### データ書き換え(Update)
新紙幣のメンツのなかに旧紙幣の野口 英世さんが混ざってしまいました。
ということで野口 英世さんから北里 柴三郎さんに書き換えましょう。
```javascript:データ書き換え
// この関数はIDから特定のデータを取得するための関数です。
const getTargetedIdData = async (sheet, id) => {
    try {
        const data = await getData(sheet);
        let target = [];
        data.values.forEach((item) => {
            if(parseInt(item[0]) === parseInt(id)) {
                console.log(item);
                console.log(item[0]);
                target.push(item);
            }
        })
        return target;
    } catch(error) {
        console.log(error);
    }
}



async function updateData(sheet, id, name, age, sex, job){
    // Google Sheets APIのインスタンス化
    const sheets = googleAuthenticate();
    try {

        const targetData = await getTargetedIdData(sheet, id);
        
        // データ書き換え
        const result = await sheets.spreadsheets.values.update({
            spreadsheetId : SPREADSHEET_ID,
            range: `${sheet}!A${targetData[0][5]}`, // 対象レコードのF列に格納した行番号を指定
            valueInputOption: "USER_ENTERED", // 入力データの解釈方法
            // 書き換えるデータ。書き換えなくて良いところはnullを指定します。
            resource:{
                values: [[null, name, age, sex, job, null]]
            }
        })
        console.log("||Record updated||");
        console.log(result.data);
    } catch (error) {
        console.log(error);
    }
}

                // シート名, ID, 名前, 年齢, 性別, 職業
await updateData("シート1", 3, "北里 柴三郎", 78, "Male", "微生物学者, 教育者");
const data = await getData("シート1");
console.log(data);
```
:::note note
この書き換え処理をするときに行番号が必要になってきます。
`` range: `${sheet}!A${targetData[0][5]}` ``
`targetData`には書き換え対象のレコードが2次元配列で格納されており、そのレコードのF列(インデックス番号でいう5番)に格納されている行番号を取得してます。
この場合だと`` range: `${sheet}!A${targetData[0][5]}` ``の変数代入後は`` range: `シート1!A4` ``となります。
:::

#### 実行結果
```javascript:ターミナル
||Start Authentication||
||Authentication successful||
||Start Authentication||
||Authentication successful||
[ '3', '野口 英世', '51', 'Male', '細菌学者', '4' ]
3
||Record updated||
{
  spreadsheetId: '1npUO3tdGDw73HwUGBt93OffZQv9H_lLI2TmGtzZ9aiY',
  updatedRange: "'シート1'!A4:E4",
  updatedRows: 1,
  updatedColumns: 4,
  updatedCells: 4
}
||Start Authentication||
||Authentication successful||
{
  range: "'シート1'!A2:F1001",
  majorDimension: 'ROWS',
  values: [
    [ '1', '渋沢 栄一', '91', 'Male', '実業家', '2' ],
    [ '2', '津田 梅子', '64', 'Female', '女子教育家', '3' ],
    [ '3', '北里 柴三郎', '78', 'Male', '微生物学者, 教育者', '4' ]
  ]
}
```
![CleanShot 2025-02-01 at 17.00.24@2x.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3949821/6b736bdc-4006-d0d7-edf3-54739eb1ad76.png)

新紙幣メンツが揃いましたね。

以上がサンプルコードです。
Deleteの方法は公式ドキュメントなどを参考にしながら作ってみてください(作れって言われたら作ります)。

:::note info
ちなみに、このサンプルコードは見れば分かる通りGoogle Auth認証を処理ごとに何度も繰り返すようになっています。
無駄な認証リクエストをなくしたい場合は、ご自身でのコードの修正をお願いします(_ _)。
:::

## まとめ
最後の最後に適当なところが残っていますが、だいたいこんな感じです。v4でサービスアカウントを使って実装する方法がなかなかインターネットを調べても見つからず苦労しました。本当にお遊び程度ならデータの管理も楽ちんで、お金もかからないので、もしよかったら使ってみてください。
他のデータの取得方法などについては公式ドキュメントをご参照ください。

https://developers.google.com/sheets/api/guides/concepts?hl=ja

https://developers.google.com/sheets/api/reference/rest?hl=ja

## 参考

https://qiita.com/l_ollz/items/fac213f60e2a2db6299c

https://qiita.com/n0bisuke/items/47ee043fc6cf1b5168c2

https://www.npmjs.com/package/googleapis