Google Spreadsheet APIを用いたNode.jsでのスプレッドシート操作

概要

この記事では、Google Spreadsheet APIを使ってNode.jsアプリケーションからスプレッドシートを操作する方法を解説します。具体的には、Googleのサービスアカウントを使用してスプレッドシートにデータを 書き込み、特定のセルの値を 読み取る という操作を行います。さらに、CLIからキーワードを入力し、スプレッドシート内のデータと比較・検索する処理を実装します。

期待した結果

A列２行目以降にハロースプレッドシートと書き込まれる
B列２行目以降に書かれている任意のテキストを検索して表示される

Message	searchKeyword
ハロースプレッドシート	任意のテキスト

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

事前準備

1. Google Cloudでサービスアカウントの作成

1. Google Cloud Console にログインし、プロジェクトを作成します。
2. APIとサービス > 認証情報 から「サービスアカウント」を作成し、 JSON形式のキー をダウンロードします。
3. Google Sheets API と Google Drive API を有効にしておきます。

2. 環境設定 (.envファイル)

取得したサービスアカウントのキー情報を使って .env ファイルを作成します。

GOOGLE_SERVICE_ACCOUNT_EMAIL=[e-mail]
GOOGLE_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\nMIIEv...<your-private-key>...\n-----END PRIVATE KEY-----\n"

使用技術

• Node.js
• google-spreadsheet パッケージ
• .env ファイルで環境変数管理
• readline モジュールでCLIからのキーワード入力

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

以下のコマンドで必要なパッケージをインストールします。

npm install google-spreadsheet dotenv readline

プロジェクト構成

プロジェクトは次のような構成になります。

project/
│── index.js  # メインのNode.jsスクリプト
│── .env      # 環境変数を格納
└── package.json  # プロジェクトの依存関係管理

コード解説

1. スプレッドシートへの書き込み

まず、Googleサービスアカウントを使ってスプレッドシートにデータを書き込む関数を定義します。

require('dotenv').config();
const { GoogleSpreadsheet } = require('google-spreadsheet');

// スプレッドシートID
const doc = new GoogleSpreadsheet('your-spreadsheet-id');

async function writeToSpreadsheet() {
    try {
        // サービスアカウント認証
        await doc.useServiceAccountAuth({
            client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
            private_key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, '\n'),
        });

        // スプレッドシート情報を読み込む
        await doc.loadInfo();
        console.log(`Title: ${doc.title}`);

        const sheet = doc.sheetsByIndex[0]; // 1枚目のシートを取得

        // 空欄のセルを見つけて書き込み
        const rows = await sheet.getRows();
        const emptyRow = rows.find(row => !row.Message);
        if (emptyRow) {
            emptyRow.Message = 'ハロースプレッドシート';
            await emptyRow.save();
            console.log('A2セルに書き込みました。');
        } else {
            await sheet.addRow({ Message: 'ハロースプレッドシート' });
            console.log('新しい行に書き込みました。');
        }
        
    } catch (err) {
        console.error('Error accessing the spreadsheet:', err);
    }
}

2. CLIでキーワードを入力し、スプレッドシートから検索

次に、CLIからキーワードを入力し、そのキーワードに一致するデータをスプレッドシート内から検索します。

const readline = require('readline');

const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
});

rl.question('検索したいキーワードを入力してください: ', (keyword) => {
    searchKeywordInSpreadsheet(keyword);
    rl.close();
});

async function searchKeywordInSpreadsheet(searchKeyword) {
    try {
        await doc.useServiceAccountAuth({
            client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
            private_key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, '\n'),
        });

        await doc.loadInfo();
        const sheet = doc.sheetsByIndex[0];
        const rows = await sheet.getRows();

        let found = false;
        for (let i = 0; i < rows.length; i++) {
            if (rows[i].Message === searchKeyword) {
                console.log(`"${searchKeyword}" が ${i + 2} 行目に見つかりました！`);
                found = true;
                break;
            }
        }

        if (!found) {
            console.log(`"${searchKeyword}" は見つかりませんでした。`);
        }

    } catch (err) {
        console.error('Error accessing the spreadsheet:', err);
    }
}

3. 完全なコード

require('dotenv').config();
const { GoogleSpreadsheet } = require('google-spreadsheet');
const readline = require('readline');

// スプレッドシートID
const doc = new GoogleSpreadsheet('your-spreadsheet-id');

// CLIからキーワードを入力するための設定
const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
});

rl.question('検索したいキーワードを入力してください: ', (keyword) => {
    searchKeywordInSpreadsheet(keyword);
    rl.close();
});

async function searchKeywordInSpreadsheet(searchKeyword) {
    try {
        await doc.useServiceAccountAuth({
            client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
            private_key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, '\n'),
        });

        await doc.loadInfo();
        const sheet = doc.sheetsByIndex[0];
        const rows = await sheet.getRows();

        let found = false;
        for (let i = 0; i < rows.length; i++) {
            if (rows[i].Message === searchKeyword) {
                console.log(`"${searchKeyword}" が ${i + 2} 行目に見つかりました！`);
                found = true;
                break;
            }
        }

        if (!found) {
            console.log(`"${searchKeyword}" は見つかりませんでした。`);
        }

    } catch (err) {
        console.error('Error accessing the spreadsheet:', err);
    }
}

async function writeToSpreadsheet() {
    try {
        await doc.useServiceAccountAuth({
            client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
            private_key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, '\n'),
        });

        await doc.loadInfo();
        const sheet = doc.sheetsByIndex[0];
        const rows = await sheet.getRows();
        
        const emptyRow = rows.find(row => !row.Message);
        if (emptyRow) {
            emptyRow.Message = 'ハロースプレッドシート';
            await emptyRow.save();
            console.log('空欄に書き込みました。');
        } else {
            await sheet.addRow({ Message: 'ハロースプレッドシート' });
            console.log('新しい行に書き込みました。');
        }
        
    } catch (err) {
        console.error('Error accessing the spreadsheet:', err);
    }
}

writeToSpreadsheet();

改善できる点

1. 冗長なコードの分割: writeToSpreadsheet 関数と searchKeywordInSpreadsheet 関数のサービスアカウント認証部分は重複しているため、共通化することでコードが簡潔になります。

2. エラーハンドリング: エラーメッセージをCLIに表示する際に、もう少し具体的な情報を追加するとトラブルシューティングがしやすくなります。

3. メモリ効率: 全ての行を取得して検索する部分に関して、対象行が見つかった時点で検索を終了するロジックを強化することで、メモリ効率が向上します。

まとめ

この記事では、Google Spreadsheet APIを用いてNode.jsでスプレッドシートにデータを書き込み、CLIからのキーワード入力を基にスプレッドシートを検索する方法を紹介しました。Google Cloudのサービスアカウントを利用することで、認証が自動化され、スプレッドシートをプログラムから簡単に操作できるようになります。