Daizen Ikehara for Twilio

Posted on May 20, 2020 • Originally published at twilio.com on May 19, 2020

GoogleスプレッドシートからNode.jsでシフトデータを読み出す方法

#googlespreadsheet #sheetapi #node #japanese

Google Cloud Platform（GCP）にはGoogle Sheets APIが提供されており、このAPIを利用してGoogleスプレッドシートのデータにアクセスすることができます。今回はGoogleスプレッドシートのスタッフ、シフトデータをNode.jsで読み込む方法を紹介します。

前提条件

Google Cloud Platform （GCP）のアカウントの作成、ならびに有効な支払い方法が登録されていること
Googleスプレッドシートを利用できること

シフトを管理するGoogleスプレッドシート

こちらにスプレッドシートのサンプルを用意しました。ファイルメニューからこのスプレッドシートを自分のGoogleアカウントで利用できるようにコピーします。スプレッドシートの中身をみてみましょう。Shiftシートには日ごとの担当者を4名まで設定しています。

また、Staffシートには担当者ごとの連絡先電話番号がE.164フォーマットで登録されています。

GCPでSheets APIを有効化

はじめてGCPを利用する場合は、コンソールからプロジェクトを作成します。

例ではプロジェクト名を google-sheet-studio としましたが、任意のプロジェクト名で構いません。

作成ボタンをクリックするとリソースの管理画面に戻り、プロジェクトの作成が開始されます。数十秒〜数分程度で作成が完了します。

次にAPI ライブラリを開きます。

先ほど作成したプロジェクトが選択されていることを確認します。

Google Sheets APIを検索し、プロジェクトに追加します。

詳細画面から 有効にする ボタンをクリックするとGoogle Sheets APIが有効になります。

APIが有効化されると、概要画面に遷移します。次に 認証情報を作成 ボタンをクリックし、このAPIを使用するための認証情報を作成します。

認証情報の追加画面において次の設定を行い、必要な認証情報 ボタンをクリックします。

使用する API - Google Sheets API
API を呼び出す場所 - ウェブサーバー（node.js、Tomcat など）
アクセスするデータの種類 - アプリケーションデータ
App Engine または Compute Engine でこの API を使用する予定はありますか？ - いいえ、使用していません

続けてサービスアカウント名とロールを設定します。例では、test およびロールを Project の 閲覧者 とし、キーのタイプを JSON としました。

次へ ボタンをクリックすると認証情報を含んだJSONファイルが作成されダウンロードされます。このファイルはGoogle Sheets APIを利用するために必要になります。作成されたJSONファイルを開くと client_email という名前のキーの値に先ほど作成したサービスアカウント IDが記載されています。Googleスプレッドシートを共有する際にこの情報が必要になります。

Googleスプレッドシートの共有とURLや情報の確認

次に、Google Sheets APIからアクセスをできるようにGoogleスプレッドシートをサービスアカウントに共有します。先ほど複製したGoogleスプレッドシートを開き、右上の 共有 ボタンをクリックします。表示された共有ダイアログに先ほどのサービスアカウント IDを入力します。

Enterキーを押すと、権限の設定を行えます。書き込み権限は必要ないため閲覧者としました。共有 ボタンをクリックし、共有を完了します。

更に、このGoogleスプレッドシートから次の情報を控えておきます。

スプレッドシートID（https://docs.google.com/spreadsheets/d/ の後に表示されている英数文字列のうち、次の’/’ の前の値。例: https://docs.google.com/spreadsheets/d/ 12312321xxx21232131212 /edit#gid=0 の太字部分）
Shift、StaffそれぞれのワークシートのID（URLが #gid=0 の場合は、 0 となる）

これでシートから情報を取得する前準備が整いました。

Node.jsプロジェクトの作成とパッケージのインストール

Google Sheets APIを利用することでGoogleスプレッドシートのデータにアクセスすることができます。このAPIに対応するNode.jsクライアントライブラリも用意されており、クイックスタートのようにセルの値を取得することもできるのですが、取得するセルの範囲を指定する必要があり、使いにくいと感じるかもしれません。そのため、今回はGoogle Sheets APIを使いやすくラップしたgoogle-spreadsheetパッケージを利用します。このパッケージを利用すると、セルの範囲を指定することなく、ワークシートから行オブジェクトとしてデータを読み込むことができます。

Node.jsアプリケーションを作成し、google-spreadsheetと環境変数を.envファイルからロードできるdotenvパッケージをインストールします。

npm i google-spreadsheet dotenv

次にGoogleスプレッドシートやシートのIDを記録しておく.envファイルを作成します。

touch .env

.envファイルには次の環境変数を追加しておきます。

SPREADSHEET_ID=
SHIFT_WORKSHEET_ID=
STAFF_WORKSHEET_ID=

SPREADSHEET_ID、STAFF_WORKSHEET_ID、SHIFT_WORKSHEET_ID には先ほど控えておいたGoogleスプレッドシートのIDやそれぞれのシートのIDを追加します。

最後にGCPからダウンロードしたJSONファイルをプロジェクトフォルダーにコピーし、わかりやすいように名前を credentials.json と変更します。これで準備完了です。

Googleスプレッドシートからシフト表を取得

ここからはGoogleスプレッドシートからシフト表を取得するコードを実装します。Node.jsアプリケーションに新しくjsファイルを追加します。index.js という名前で作成しました。

touch index.js

index.jsをエディタで開き、環境変数の読み込みや必要なパッケージをインポートします。

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

次にGoogleスプレッドシートからシフトデータを読み取り、担当者の電話番号を返す非同期関数を実装します。

// Googleスプレッドシートからシフト情報をロードし、担当者の電話番号を取得
async function loadShiftPhoneNumbers() {
    // 処理を実装
}

この loadShiftPhoneNumbers 関数でGoogleスプレッドシートをロードします。ここでGCPへの接続に必要になるのが先ほどコピーし、名前を変更した credentials.json です。

// Googleスプレッドシートからシフト情報をロードし、担当者の電話番号を取得
async function loadShiftPhoneNumbers() {
    // スプレッドシートIDと資格情報を用いてGoogleスプレッドシートをロード
    const doc = new GoogleSpreadsheet(process.env.SPREADSHEET\_ID);
    const credentials = require('./credentials.json');
    await doc.useServiceAccountAuth(credentials);
    await doc.loadInfo();
}

ワークシートを取得する場合は、GoogleSpreadsheet.sheetsById、またはGoogleSpreadsheet.sheetsbyIndexを利用できます。さらに、GoogleSpreadsheetWorksheet.getRowsメソッドを使用し、ワークシートの行を取得できます。残念ながら特定の列の値をキーにフィルタリングはできないようなので全ての行を取得します。

// Googleスプレッドシートからシフト情報をロードし、担当者の電話番号を取得
async function loadShiftPhoneNumbers() {
    // スプレッドシートIDと資格情報を用いてGoogleスプレッドシートをロード
    const doc = new GoogleSpreadsheet(process.env.SPREADSHEET\_ID);
    const credentials = require('./credentials.json');
    await doc.useServiceAccountAuth(credentials);
    await doc.loadInfo();

    //シフト情報を取得
    const shiftSheet = await doc.sheetsById[process.env.SHIFT_WORKSHEET_ID];
    const shiftRows = await shiftSheet.getRows();
    // 従業員情報を取得const staffSheet = await doc.sheetsById[process.env.STAFF_WORKSHEET_ID];
    const staffRows = await staffSheet.getRows();
}

GoogleSpreadsheetWorksheet.getRowsメソッドはGoogleSpreadsheetRowの配列を返します。また、このGoogleSpreadsheetRowオブジェクトは最初の行をプロパティのキーとしてアクセスできるため、Array.prototype.find()メソッドを利用し、Date列をキーとして特定の日付のデータを抜き出すことができます。この記事では、2020年5月15日を例として取得します。実際のアプリケーションでは new Date() などを利用し、当日のデータを取得することになるでしょう。

// Googleスプレッドシートからシフト情報をロードし、担当者の電話番号を取得
async function loadShiftPhoneNumbers() {
    // スプレッドシートIDと資格情報を用いてGoogleスプレッドシートをロード
    const doc = new GoogleSpreadsheet(process.env.SPREADSHEET\_ID);
    const credentials = require('./credentials.json');
    await doc.useServiceAccountAuth(credentials);
    await doc.loadInfo();

    //シフト情報を取得
    const shiftSheet = await doc.sheetsById[process.env.SHIFT_WORKSHEET_ID];
    const shiftRows = await shiftSheet.getRows();
    // 従業員情報を取得const staffSheet = await doc.sheetsById[process.env.STAFF_WORKSHEET_ID];
    const staffRows = await staffSheet.getRows();

    // シフト情報からDate列の値と指定した日付をロケール情報に基づいて取得
    let shiftRow = shiftRows.find(row =>
        new Date(row.Date).toLocaleDateString() ===
        new Date('2020/5/15').toLocaleDateString());
}

あとは、取得した行から必要なデータを読み取り、アプリケーションで使用できます。

データの活用例として、このシフトデータから担当者の電話番号をカンマ区切りの文字列で取得する方法も実装します。

shiftRow.Employee1のように各列のキーを指定してデータを取得することもできますが、shiftRow._rawData shiftRow には、行のデータが配列として保持されています。2020年5月15日のデータは、['5/15/2020', 'Mitsuharu', 'Yoshihiro'] となります。この配列をArray.prototype.slice()メソッドで最初の日付データを除外した配列とし、さらに、Array.prototype.map()メソッドでシフト担当の従業員の電話番号の配列へと変換します。そして、最後に、Array.prototype.join()メソッドで文字列として返すという処理を実装しました。

// Googleスプレッドシートからシフト情報をロードし、担当者の電話番号を取得
async function loadShiftPhoneNumbers() {
    // スプレッドシートIDと資格情報を用いてGoogleスプレッドシートをロード
    const doc = new GoogleSpreadsheet(process.env.SPREADSHEET\_ID);
    const credentials = require('./credentials.json');
    await doc.useServiceAccountAuth(credentials);
    await doc.loadInfo();

    //シフト情報を取得
    const shiftSheet = await doc.sheetsById[process.env.SHIFT_WORKSHEET_ID];
    const shiftRows = await shiftSheet.getRows();
    // 従業員情報を取得const staffSheet = await doc.sheetsById[process.env.STAFF_WORKSHEET_ID];
    const staffRows = await staffSheet.getRows();

    // シフト情報からDate列の値と指定した日付をロケール情報に基づいて取得
    let shiftRow = shiftRows.find(row =>
        new Date(row.Date).toLocaleDateString() ===
        new Date('2020/5/15').toLocaleDateString());

        // 元データ['5/15/2020', 'Mitsuharu', 'Yoshihiro']
        // Date列(最初の列)を取り除き、シフト担当の従業員を含む配列を取得する
        let employeesOnDuty = shiftRow._rawData.slice(1); 
        // ['Mitsuharu', 'Yoshihiro']
        // 名前から電話番号の配列に置換
        employeesOnDuty = employeesOnDuty.map(m => 
            staffRows.find(row => row.Name === m).PhoneNumber); 
        // ['+815012341235', '+815012341237']

        return employeesOnDuty.join(',');}

ここまで実装を終えた段階で、きちんとデータを読み込めるかどうかを確認しましょう。
loadShiftNumbers関数のスコープ外に次のコードを追加します。

// 実装した関数が正しく動作するかテスト
loadShiftPhoneNumbers()
    .then (numbers => console.log(numbers))
    .catch(error => console.error(error));

index.jsを実行し、次のような結果がコンソールに出力されていれば成功です。

node index.js

実行結果

+815012341235,+815012341237

想定した結果が得られない場合は、出力されたエラーを参考にGCPの設定や、JSONファイルの読み込みなどを確認してください。

まとめ

ご覧いただいたように、Google Sheets APIを使うことでGoogleスプレッドシートをデータソースとしたアプリケーションを構築することができます。ぜひご活用ください。

このエントリについての問い合わせ

不明点があればぜひ、お問い合わせください。オンライン登壇のご依頼等もこちらまで！

DEV Community

GoogleスプレッドシートからNode.jsでシフトデータを読み出す方法

シフトを管理するGoogleスプレッドシート

GCPでSheets APIを有効化

Googleスプレッドシートの共有とURLや情報の確認

Node.jsプロジェクトの作成とパッケージのインストール

Googleスプレッドシートからシフト表を取得

まとめ

このエントリについての問い合わせ

Oldest comments (0)

Read next

Using pnpm with the GitLab package registry in GitLab CI

Utilizando o Prisma com TypeScript e SQLite: Um Guia Prático

Redis Queue: Optimising Workflows with Message Queues

A broader perspective of serverless