GoogleスプレッドシートからNode.jsでシフトデータを読み出す方法
Tags: googlespreadsheet, sheetapi, node, japanese
Published: 2020-05-20 07:30:10

---
title: GoogleスプレッドシートからNode.jsでシフトデータを読み出す方法
published: true
date: '2020-05-19'
tags: Google spreadsheet, Sheet API, NodeJs, Japanese 
canonical_url: https://www.twilio.com/blog/load-data-from-google-spreadsheet-jp
---
![Header Image](https://twilio-cms-prod.s3.amazonaws.com/images/JfSEBIrPI4CAu3k18bqViRLlvPl1fZIbJ9LpKwl1J86O0d.width-808.png)
[Google Cloud Platform（GCP）](https://console.cloud.google.com/?hl=ja)には[Google Sheets API](https://developers.google.com/sheets/api)が提供されており、このAPIを利用してGoogleスプレッドシートのデータにアクセスすることができます。今回はGoogleスプレッドシートのスタッフ、シフトデータをNode.jsで読み込む方法を紹介します。

前提条件

- [Google Cloud Platform （GCP）のアカウント](https://console.cloud.google.com/?hl=ja)の作成、ならびに有効な支払い方法が登録されていること
- [Googleスプレッドシート](https://www.google.com/intl/ja_jp/sheets/about/)を利用できること

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

[こちら](https://docs.google.com/spreadsheets/d/1KHozXisufSbldqeTtkTO3-fRT9McdVxrzVz7YyCWSqM/edit?usp=sharing)にスプレッドシートのサンプルを用意しました。ファイルメニューからこのスプレッドシートを自分のGoogleアカウントで利用できるようにコピーします。スプレッドシートの中身をみてみましょう。Shiftシートには日ごとの担当者を4名まで設定しています。  
 ![Google Spreadsheet - Shift](https://twilio-cms-prod.s3.amazonaws.com/images/JTcbF8FiG0AyceVQcR4FQNszfxEwj5zwFtf9w0KPLfcwJj.width-800.png)  
  
また、Staffシートには担当者ごとの連絡先電話番号が[E.164フォーマット](https://www.twilio.com/docs/glossary/what-e164)で登録されています。

![Google Spreadsheet - Staff](https://twilio-cms-prod.s3.amazonaws.com/images/-ke-JpIzwIkEdVu41E8aa4OsC8YPLq9zHavppvy7FuI04C.width-800.png)

## GCPでSheets APIを有効化

はじめてGCPを利用する場合は、[コンソール](https://console.cloud.google.com/cloud-resource-manager)からプロジェクトを作成します。  
 ![GCP - Create a project](https://twilio-cms-prod.s3.amazonaws.com/images/ooE7wJ3JEVutwUGFPfuZC9JorRaxWpFOD4K4DS9syz0L99.width-800.png)

例ではプロジェクト名を `google-sheet-studio` としましたが、任意のプロジェクト名で構いません。  
 ![GCP - project name](https://twilio-cms-prod.s3.amazonaws.com/images/AfZdHBVKjtKyGBsHpFbVGErsr-LOfmrkXp5xIYYN0hLnU0.width-800.png)

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

次に[API ライブラリ](https://console.cloud.google.com/apis/library)を開きます。

![GCP - Open API Library](https://twilio-cms-prod.s3.amazonaws.com/images/GDhetd6rJRyWJZNxq1leRhhn7bwz3hqWkj74w0eeSCHQhv.width-800.png)

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

![GCP - API Library with a project](https://twilio-cms-prod.s3.amazonaws.com/images/F085blQIr1lxDfm7NuuO1Om1XrHR8RUZazZMvc0CRydar-.width-800.png)

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

![GCP - search sheets API](https://twilio-cms-prod.s3.amazonaws.com/images/5QjQstd86zQXHcCP_oPQVO5uIvSOipkjGwBqZjTQyKcEpL.width-800.png)

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

![GCP - enable sheets API](https://twilio-cms-prod.s3.amazonaws.com/images/iHS-ff4E5U65OLwx-Hz52VM2VCeCDb14rjwmseU2cb3C1W.width-800.png)

 

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

![GCP - Add auth info](https://twilio-cms-prod.s3.amazonaws.com/images/mdPFFkjpFUewRvZtgR4HhhwNtONnuvGvs4o3Mbb8YWXGnT.width-800.png)

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

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

![GCP - Create a service account](https://twilio-cms-prod.s3.amazonaws.com/images/d7DzQucz1snBQJBnUZ8AxaWun3kguQxe8_xf6HS2NrjFgr.width-800.png)

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

![GCP - Create a service account - name](https://twilio-cms-prod.s3.amazonaws.com/images/y-iIsGp2LwAvFexgqbUx_lANXcg-bhKlka14JOcK1sb97D.width-800.png)

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

![gcp - service account](https://twilio-cms-prod.s3.amazonaws.com/images/JgW7p51hA5u-4yggqHymFjLMI_rGBvu0wAHDCpVJXsZeJq.width-800.png)

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

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

![Google Spreadsheet - share the sheet](https://twilio-cms-prod.s3.amazonaws.com/images/eNfWeKahkSszuRfCxD4PgtaVRVJm-vLmfv19RvvXM4xgxb.width-800.png)

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

![Google Spreadsheet - share as a viewer](https://twilio-cms-prod.s3.amazonaws.com/images/z5tAPOALrfoTztQVwEDWn1fmusPZP25PbsepJd8VsMp8P7.width-800.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](https://developers.google.com/sheets/api)を利用することでGoogleスプレッドシートのデータにアクセスすることができます。このAPIに対応する[Node.jsクライアントライブラリ](https://github.com/googleapis/google-api-nodejs-client/tree/master/src/apis/sheets)も用意されており、[クイックスタート](https://developers.google.com/sheets/api/quickstart/nodejs)のようにセルの値を取得することもできるのですが、取得するセルの範囲を指定する必要があり、使いにくいと感じるかもしれません。そのため、今回はGoogle Sheets APIを使いやすくラップした[google-spreadsheet](https://developers.google.com/sheets/api/quickstart/nodejs)パッケージを利用します。このパッケージを利用すると、セルの範囲を指定することなく、ワークシートから行オブジェクトとしてデータを読み込むことができます。

Node.jsアプリケーションを作成し、[google-spreadsheet](https://www.npmjs.com/package/google-spreadsheet)と環境変数を.envファイルからロードできる[dotenv](https://www.npmjs.com/package/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](https://theoephraim.github.io/node-google-spreadsheet/#/classes/google-spreadsheet?id=worksheets)を利用できます。さらに、[GoogleSpreadsheetWorksheet.getRows](https://theoephraim.github.io/node-google-spreadsheet/#/classes/google-spreadsheet-worksheet?id=fn-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](https://theoephraim.github.io/node-google-spreadsheet/#/classes/google-spreadsheet-worksheet?id=fn-getrows)メソッドはGoogleSpreadsheetRowの配列を返します。また、この[GoogleSpreadsheetRow](https://theoephraim.github.io/node-google-spreadsheet/#/classes/google-spreadsheet-row)オブジェクトは最初の行をプロパティのキーとしてアクセスできるため、[Array.prototype.find()](https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/Array/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()](https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/Array/slice)メソッドで最初の日付データを除外した配列とし、さらに、[Array.prototype.map()](https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/Array/map)メソッドでシフト担当の従業員の電話番号の配列へと変換します。そして、最後に、[Array.prototype.join()](https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/Array/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スプレッドシートをデータソースとしたアプリケーションを構築することができます。ぜひご活用ください。

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

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

- Twitter ([@Neri78](https://twitter.com/neri78))
- Email: [dikehara@twilio.com](mailto:dikehara@twilio.com)
- Github: [https://github.com/neri78](https://github.com/neri78)
- Twitch: [https://twitch.tv/neri78](https://twitch.tv/neri78)