GoogleスプレッドシートからNode.jsでシフトデータを読み出す方法
読む所要時間: 13 分
![HowToLoadDataFromGoogleSpreadsheet HowToLoadDataFromGoogleSpreadsheet](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/JfSEBIrPI4CAu3k18bqViRLlvPl1fZIbJ9LpKwl1J86O0ddFyv-atBBKU_KTuZemsLwwPLYeivTXJM.png)
Google Cloud Platform(GCP)にはGoogle Sheets APIが提供されており、このAPIを利用してGoogleスプレッドシートのデータにアクセスすることができます。今回はGoogleスプレッドシートのスタッフ、シフトデータをNode.jsで読み込む方法を紹介します。
前提条件
- Google Cloud Platform (GCP)のアカウントの作成、ならびに有効な支払い方法が登録されていること
- Googleスプレッドシートを利用できること
シフトを管理するGoogleスプレッドシート
こちらにスプレッドシートのサンプルを用意しました。ファイルメニューからこのスプレッドシートを自分のGoogleアカウントで利用できるようにコピーします。スプレッドシートの中身をみてみましょう。Shiftシートには日ごとの担当者を4名まで設定しています。
![Google Spreadsheet - Shift](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/JTcbF8FiG0AyceVQcR4FQNszfxEwj5zwFtf9w0KPLfcwJjNW-EU13VeLAasdOUSP-Xnps_Dr2nPGmN.png)
![Google Spreadsheet - Staff](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/-ke-JpIzwIkEdVu41E8aa4OsC8YPLq9zHavppvy7FuI04CrFKsWIoR-920SBDRAx2vxhgOGGkfoRgT.png)
GCPでSheets APIを有効化
はじめてGCPを利用する場合は、コンソールからプロジェクトを作成します。
![GCP - Create a project](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/ooE7wJ3JEVutwUGFPfuZC9JorRaxWpFOD4K4DS9syz0L9918hrTJKsnKdoT70KV8dfBeV59EMB0tOB.png)
例ではプロジェクト名を google-sheet-studio
としましたが、任意のプロジェクト名で構いません。
![GCP - project name](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/AfZdHBVKjtKyGBsHpFbVGErsr-LOfmrkXp5xIYYN0hLnU09dvY7KJw4m2-5IS2eGDDtKK5kEbfGZuu.png)
作成ボタンをクリックするとリソースの管理画面に戻り、プロジェクトの作成が開始されます。数十秒〜数分程度で作成が完了します。
次にAPI ライブラリを開きます。
![GCP - Open API Library](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/GDhetd6rJRyWJZNxq1leRhhn7bwz3hqWkj74w0eeSCHQhvhQgBjiYsgREhy6iwAqIygI58BACPnXY9.png)
先ほど作成したプロジェクトが選択されていることを確認します。
![GCP - API Library with a project](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/F085blQIr1lxDfm7NuuO1Om1XrHR8RUZazZMvc0CRydar-10c7KI7T4W0m3Nu-pk6Iw2VjE9WvmngS.png)
Google Sheets APIを検索し、プロジェクトに追加します。
![GCP - search sheets API](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/5QjQstd86zQXHcCP_oPQVO5uIvSOipkjGwBqZjTQyKcEpLAYcYoExMdwAwAdhBNbCQQR8_nhyTtzcr.png)
詳細画面から 有効にする
ボタンをクリックするとGoogle Sheets APIが有効になります。
![GCP - enable sheets API](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/iHS-ff4E5U65OLwx-Hz52VM2VCeCDb14rjwmseU2cb3C1WPpfLb74cMDqGSVOfeTsAd7tsf6SoQkTV.png)
APIが有効化されると、概要画面に遷移します。次に 認証情報を作成
ボタンをクリックし、このAPIを使用するための認証情報を作成します。
![GCP - Add auth info](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/mdPFFkjpFUewRvZtgR4HhhwNtONnuvGvs4o3Mbb8YWXGnTwHpOKiDV6j0-unwPCj-Ag-ywIus4K65g.png)
認証情報の追加画面において次の設定を行い、必要な認証情報
ボタンをクリックします。
- 使用する API - Google Sheets API
- API を呼び出す場所 - ウェブサーバー(node.js、Tomcat など)
- アクセスするデータの種類 - アプリケーション データ
- App Engine または Compute Engine でこの API を使用する予定はありますか? - いいえ、使用していません
![GCP - Create a service account](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/d7DzQucz1snBQJBnUZ8AxaWun3kguQxe8_xf6HS2NrjFgrN-S34l_VzPthwmGyliQpLWmmPIh7huWs.png)
続けてサービスアカウント名とロールを設定します。例では、test
およびロールを Project
の 閲覧者
とし、キーのタイプを JSON
としました。
![GCP - Create a service account - name](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/y-iIsGp2LwAvFexgqbUx_lANXcg-bhKlka14JOcK1sb97Dyw5jLCMMNtjgi1qo_2NHGtkTC2huMb-Y.png)
次へ
ボタンをクリックすると認証情報を含んだJSONファイルが作成されダウンロードされます。このファイルはGoogle Sheets APIを利用するために必要になります。作成されたJSONファイルを開くと client_email
という名前のキーの値に先ほど作成したサービス アカウント IDが記載されています。Googleスプレッドシートを共有する際にこの情報が必要になります。
![gcp - service account](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/JgW7p51hA5u-4yggqHymFjLMI_rGBvu0wAHDCpVJXsZeJqekONTgvUCoofGAIIIGZTaP9MBOzsU72l.png)
Googleスプレッドシートの共有とURLや情報の確認
次に、Google Sheets APIからアクセスをできるようにGoogleスプレッドシートをサービス アカウントに共有します。先ほど複製したGoogleスプレッドシートを開き、右上の 共有
ボタンをクリックします。表示された共有ダイアログに先ほどのサービス アカウント IDを入力します。
![Google Spreadsheet - share the sheet](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/eNfWeKahkSszuRfCxD4PgtaVRVJm-vLmfv19RvvXM4xgxbYKe5afBi8QxulBWvGv9SQJBtv7BfwLBV.png)
Enterキーを押すと、権限の設定を行えます。書き込み権限は必要ないため閲覧者としました。共有
ボタンをクリックし、共有を完了します。
![Google Spreadsheet - share as a viewer](/content/dam/twilio-com/global/en/blog/legacy/2020/load-data-from-google-spreadsheet-jp/z5tAPOALrfoTztQVwEDWn1fmusPZP25PbsepJd8VsMp8P71dsyoE9UwkhIQ9q3d93Kf5lFHUB3kmG-.png)
更に、この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パッケージをインストールします。
次にGoogleスプレッドシートやシートのIDを記録しておく.envファイルを作成します。
.envファイルには次の環境変数を追加しておきます。
SPREADSHEET_ID
、STAFF_WORKSHEET_ID
、SHIFT_WORKSHEET_ID
には先ほど控えておいたGoogleスプレッドシートのIDやそれぞれのシートのIDを追加します。
最後にGCPからダウンロードしたJSONファイルをプロジェクトフォルダーにコピーし、わかりやすいように名前を credentials.json
と変更します。これで準備完了です。
Googleスプレッドシートからシフト表を取得
ここからはGoogleスプレッドシートからシフト表を取得するコードを実装します。Node.jsアプリケーションに新しくjsファイルを追加します。index.js
という名前で作成しました。
index.jsをエディタで開き、環境変数の読み込みや必要なパッケージをインポートします。
次にGoogleスプレッドシートからシフトデータを読み取り、担当者の電話番号を返す非同期関数を実装します。
この loadShiftPhoneNumbers
関数でGoogleスプレッドシートをロードします。ここでGCPへの接続に必要になるのが先ほどコピーし、名前を変更した credentials.json
です。
ワークシートを取得する場合は、GoogleSpreadsheet.sheetsById、またはGoogleSpreadsheet.sheetsbyIndexを利用できます。さらに、GoogleSpreadsheetWorksheet.getRowsメソッドを使用し、ワークシートの行を取得できます。残念ながら特定の列の値をキーにフィルタリングはできないようなので全ての行を取得します。
GoogleSpreadsheetWorksheet.getRowsメソッドはGoogleSpreadsheetRowの配列を返します。また、このGoogleSpreadsheetRowオブジェクトは最初の行をプロパティのキーとしてアクセスできるため、Array.prototype.find()メソッドを利用し、Date列をキーとして特定の日付のデータを抜き出すことができます。この記事では、2020年5月15日を例として取得します。実際のアプリケーションでは new Date()
などを利用し、当日のデータを取得することになるでしょう。
あとは、取得した行から必要なデータを読み取り、アプリケーションで使用できます。
データの活用例として、このシフトデータから担当者の電話番号をカンマ区切りの文字列で取得する方法も実装します。
shiftRow.Employee1
のように各列のキーを指定してデータを取得することもできますが、shiftRow._rawData shiftRow
には、行のデータが配列として保持されています。2020年5月15日のデータは、[ '5/15/2020', 'Mitsuharu', 'Yoshihiro' ]
となります。この配列をArray.prototype.slice()メソッドで最初の日付データを除外した配列とし、さらに、Array.prototype.map()メソッドでシフト担当の従業員の電話番号の配列へと変換します。そして、最後に、Array.prototype.join()メソッドで文字列として返すという処理を実装しました。
ここまで実装を終えた段階で、きちんとデータを読み込めるかどうかを確認しましょう。
loadShiftNumbers関数のスコープ外に次のコードを追加します。
index.jsを実行し、次のような結果がコンソールに出力されていれば成功です。
実行結果
想定した結果が得られない場合は、出力されたエラーを参考にGCPの設定や、JSONファイルの読み込みなどを確認してください。
まとめ
ご覧いただいたように、Google Sheets APIを使うことでGoogleスプレッドシートをデータソースとしたアプリケーションを構築することができます。ぜひご活用ください。
このエントリについての問い合わせ
不明点があればぜひ、お問い合わせください。オンライン登壇のご依頼等もこちらまで!
- Twitter (@Neri78)
- Email: dikehara@twilio.com
- Github: https://github.com/neri78
- Twitch: https://twitch.tv/neri78