Node.jsでGoogleスプレッドシートを操作する

Webエンジニアの川辺です。

今回はNode.jsでGoogle スプレッドシートを操作する際に使用したnode-google-spreadsheetの紹介をしたいと思います。

使用したバージョン

• Node.js: 8.11.3
• node-google-spreadsheet: 2.0.6

準備

コード上からGoogleスプレッドシートを操作するため、シートへアクセスを許可するための準備が必要です。それでは順を追って進めていきます。

1. プロジェクトを作成

アクセスを許可するための認証情報を作成するためにまずプロジェクトを作ります。 Google Developers Console にアクセスし「プロジェクトを作成」をクリックしブロジェクトを作成します。

プロジェクトを作成するとこの画面が表示されます。

もし、プロジェクトが複数ある場合は今回作成したプロジェクトに切り替えてください。

2. Google Drive APIを有効化

次にGoogle Drive APIを有効化します。
まず、ライブラリ一覧ページを開きます。

上部にある検索フォームで「Google Drive」を検索して選択します。

「有効にする」ボタンをクリックします。

3. 認証情報を作成

最後に認証情報を作成します。

先ほどあった「ライブラリ」の項目の下にある「認証情報」をクリックします。

「認証情報を作成」をクリックして「サービス アカウント キー」を選択します。

各項目を入力して作成ボタンをクリックします。

• サービス アカウント：新しいサービス アカウント
• サービス アカウント名：任意
• 役割：Project→オーナー
• キーのタイプ：JSON

認証情報の作成が完了すると秘密鍵が保存されます。 今回は取り扱いしやすくするためにファイルを「google-generated-creds.json」にリネームします。

ファイル内に「client_email」という項目があるので、そのメールアドレスを操作したいスプレッドシートの共有設定に追加します。

以上で準備は終了です。ここからコードの実装に入っていきます。

コードの実装

ディレクトリを作成して必要なパッケージをインストールします。

$ mkdir node-spreadsheet-sample
$ cd node-spreadsheet-sample/
$ npm init
$ npm install google-spreadsheet async

先ほどダウンロードした「google-generated-creds.json」をディレクトリに移動します。

次にメインの処理となるindex.jsを作成します。
今回は以下のスプレッドシートの内容を取得してみたいと思います。

var GoogleSpreadsheet = require("google-spreadsheet");
var async = require("async");

var doc = new GoogleSpreadsheet("1D8bt0UpUxxxxxxxxDnYR7lBzoQv0lIU0uNo"); // ここはスプレッドシートごとに書き換える必要がある
var sheet;

async.series(
  [
    function setAuth(step) {
      var creds = require("./google-generated-creds.json");
      doc.useServiceAccountAuth(creds, step);
    },
    function getInfoAndWorksheets(step) {
      doc.getInfo(function(err, info) {
        sheet = info.worksheets[0];
        step();
      });
    },
    function workingWithCells(step) {
      const COLUMNS = {
        name: 1,
        price: 2,
      };
      sheet.getCells(
        {
          "min-row": 2,
          "max-row": 5,
          "return-empty": true
        },
        function(err, cells) {
          for (let i = 0; i < cells.length / sheet.colCount; i += 1) {
            const name = cells[i * sheet.colCount + COLUMNS.name].value;
            const price = cells[i * sheet.colCount + COLUMNS.price].value;
            console.log(name + " " + price);
          }
        }
      );
    }
  ],
  function(err) {
    if (err) {
      console.log("Error: " + err);
    }
  }
);

※ GoogleSpreadsheetの引数にはスプレッドシートのURLの以下の部分を入れます。

node.jsを実行するとコンソールにログが表示されました。

❯ node index.js
名称 価格
りんご 100円
みかん 120円
ぶどう 150円

これらの値はnode-google-spreadsheetのgetCellsメソッドで取得しています。
getCellsメソッドでは取得する範囲を指定するためにmin-rowとmax-rowに数値を設定していますが、この値はインデックスではなく行番号を指しているので注意が必要です。

このメソッドは指定した行と列の全てのCellを取得する仕様なため、大抵の場合はループやフィルタ処理などで必要なデータを抽出する必要がありそうです。

      ...
      sheet.getCells(
        {
          // 2行目から5行目のセルを取得
          "min-row": 2,  
          "max-row": 5,
          "return-empty": true
        },
        function(err, cells) {
          for (let i = 0; i < cells.length / sheet.colCount; i += 1) {
            const name = cells[i * sheet.colCount + COLUMNS.name].value;
            const price = cells[i * sheet.colCount + COLUMNS.price].value;
            console.log(name + " " + price);
          }
        }
      );
      ...

返り値には「行」 x 「列数」分の配列が入っています。

[
  SpreadsheetCell {
  ...
  },
  SpreadsheetCell {
    updateValuesFromResponseData: [Function],
    setValue: [Function],
    _clearValue: [Function],
    value: [Getter/Setter],
    formula: [Getter/Setter],
    numericValue: [Getter/Setter],
    valueForSave: [Getter],
    save: [Function],
    del: [Function],
    id: 'https://spreadsheets.google.com/feeds/cells/1D8bt0UpUxxxxxxxxDnYR7lBzoQv0lIU0uNo/od6/R2C2',
    row: 2,
    col: 2,
    batchId: 'R2C2',
    _links:
     [ self: 'https://spreadsheets.google.com/feeds/cells/1D8bt0UpUxxxxxxxxDnYR7lBzoQv0lIU0uNo/od6/private/full/R2C2',
       edit: 'https://spreadsheets.google.com/feeds/cells/1D8bt0UpUxxxxxxxxDnYR7lBzoQv0lIU0uNo/od6/private/full/R2C2' ],
    _formula: undefined,
    _numericValue: undefined,
    _value: '名称' },
  SpreadsheetCell {
  ...
  },
]

rowやcolの値もあるので、以下のように行や列ごとの値の配列を抽出したりすることもできます。

cells.filter(cell => cell.col === COLUMNS.name) // 名称の列だけ抽出
     .map(col => col.value) // 値を取得

// 結果
// [ '名称', 'りんご', 'みかん', 'ぶどう' ]

行のみを取得する場合はgetRowsメソッドを使用できます。
しかし、特定の列に含まれる行（=セル）を取得する要件の元で扱うには、getCellsメソッドの返り値とは違いランダムなKeyが割り当てられる仕様となっているため取り扱い方に課題がありました。

SpreadsheetRow {
   ...
    _cokwr: '名称',
    _cpzh4: '価格',
   ...
},
SpreadsheetRow {
   ...
    _cokwr: 'りんご',
    _cpzh4: '100円',
   ...
},
...

終わりに

今回は導入編ということで簡単な操作しかしませんでしたが、コードでスプレッドシートが操作できるようになれば色々な作業が捗るようになると思います。 ちなみに弊社では以前のブログにも書いたPuppeteerのE2EテストでアクセスするURLを参照するのに使用しました。 工夫次第で色々と活用できると思うのでぜひ試して見てください。

techblog.lclco.com