arrow-right hamburger logo-mark social-facebook social-github social-twitter
2017.12.04

Node.jsでGoogle SpreadSheetsを操作してみよう。【GAS不使用】

のびすけ

Node.js Magazine

こんにちは、代表ののびすけです。

この記事はNode.jsアドベントカレンダー2017の記事になります。

GASじゃなくてNode.jsから直接スプレッドシートにアクセスしたい!

そう思って調べるとNode.js Quickstart | Sheets API | Google Developersが出て来ますが、JS記述も古いし英語なので少しとっつきにくいです。

ということでちょっと試して記録残そうと思います。

Google Sheets API

Google Sheets APIはスプレッドシートにアクセスできるようになるAPIです。

Google Sheets API を使用すると、スプレッドシートの要素を読み取って変更できます。 スプレッドシートは多くの設定を備えており、美しく機能的なシートを作成することができます。

普段慣れている言語でスプレッドシートにアクセス出来まるので、簡単な操作であれば、GASを使う必要もないです。

今回のサンプルはこちらに置いておきます。

環境

参考までに今回の僕の環境です。

では実際に作っていきます。

1. Google Sheets APIを使えるようにする

ちなみに2017年12月時点でのウィザードです。こういうのはアップデートによって画面遷移変わるので適宜読み替えてください。

Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.

こちらのリンクからディベロッパーコンソールにいき、

新機能のお知らせ、パフォーマンスに関するアドバイス、フィードバック調査、特典に関する最新情報をメールで受け取ります。の質問はいいえでもたぶん大丈夫です。

認証情報に進むを選択

この画面が出ますが、キャンセルを選択します。

スクリーンショット 2017-12-03 21.00.17.png

OAuth同意画面のタブを選択し、メールアドレスを確認し、ユーザーに表示するサービス名を入力します。

保存を選択しましょう。

スクリーンショット 2017-12-03 21.01.07.png

次に、認証情報のタブを選択し、 認証情報を作成 -> OAuthクライアントIDを選択します。

その他を選択し、任意の名前をつけて作成

スクリーンショット 2017-12-03 21.06.47.png

OKで進みます。

スクリーンショット 2017-12-03 21.07.25.png

client_secret_xxxxxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxxx.apps.googleusercontent.com.jsonのようなファイルがダウンロードされます。

これをclient_secret.jsonにリネームしましょう。

2. Node.jsプロジェクトの準備

任意のディレクトリを作成して移動します。

sheetstestとしました。

mkdir sheetstest
cd sheetstest

npm init -ypackage.jsonを作成します。

npm init -y

このディレクトリに先ほどのclient_secret.jsonを移動させます。

ls

package.json client_secret.json

こんな感じです。

利用するモジュールを追加します。

npm i googleapis google-auth-library --save

3. トークンの作成&保存 (最初だけ)

本家とは少し違うやり方です。

本家のコードだとトークンの作成&保存作業と実際のAPI呼び出しが一緒になったコードですがトークンの作成&保存は最初のみ行えば良いので手順を分けて紹介します。

getAndStoreToken.jsを作成します。

//getAndStoreToken.js
'use strict';

const fs = require('fs');
const readline = require('readline');
const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
});
const {promisify} = require('util');

const google = require('googleapis');
const googleAuth = require('google-auth-library');

//promisifyでプロミス化
const readFileAsync = promisify(fs.readFile);
const writeFileAsync = promisify(fs.writeFile);
const rlQuestionAsync = promisify(rl.question);

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
const TOKEN_DIR = __dirname;
const TOKEN_PATH = TOKEN_DIR+'/sheets.googleapis.com-nodejs-quickstart.json';

const main = async () => {
    const content = await readFileAsync(__dirname+'/client_secret.json');
    const credentials = JSON.parse(content); //クレデンシャル
    //認証
    const clientSecret = credentials.installed.client_secret;
    const clientId = credentials.installed.client_id;
    const redirectUrl = credentials.installed.redirect_uris[0];
    const auth = new googleAuth();
    const oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);

    //get new token
    const authUrl = oauth2Client.generateAuthUrl({
        access_type: 'offline',
        scope: SCOPES
    });

    console.log('Authorize this app by visiting this url: ', authUrl);

    rl.question('Enter the code from that page here: ', (code) => {
        rl.close();

        oauth2Client.getToken(code, async (err, token) => {
            if (err) {
                console.log('Error while trying to retrieve access token', err);
                return;
            }

            oauth2Client.credentials = token;

            try {
                fs.mkdirSync(TOKEN_DIR);
            } catch (err) {
                if (err.code != 'EEXIST') throw err;
            }

            await writeFileAsync(TOKEN_PATH, JSON.stringify(token));
            console.log('Token stored to ' + TOKEN_PATH);
        });
    });
};

main();

このプログラムを実行します。

node getAndStoreToken.js

https://accounts.google.com/~で始まるURLが表示されるのでこれをコピーしてブラウザで開きます。

スクリーンショット 2017-12-04 11.52.54.png

アカウント選択画面になります。 複数Googleアカウントを持っている人は、今回アクセスしようとしているアカウントを選択します。

スクリーンショット 2017-12-04 11.53.02.png

許可で進みます。

スクリーンショット 2017-12-04 11.53.09.png

認証用のコードが発行されます。

スクリーンショット 2017-12-04 11.53.27.png

これをコピーしてターミナルに貼り付けてエンターキーで進みます。

Token stored to /Users/n0bisuke/dotstudio/playground/sheetstest/sheets.googleapis.com-nodejs-quickstart.json

などと表示されてjsonファイルが保存されます。

スクリーンショット 2017-12-04 11.57.50.png

sheets.googleapis.com-nodejs-quickstart.jsonがフォルダ内に確認できればOKです。

4. いよいよシートにアクセス

さて最後の手順です。

getSheetsData.jsを作成しましょう。

//getSheetsData.js
'use strict';

const fs = require('fs');
const {promisify} = require('util');
const google = require('googleapis');
const googleAuth = require('google-auth-library');
const sheets = google.sheets('v4');

//promisifyでプロミス化
const readFileAsync = promisify(fs.readFile);
const ssValuesGetAsync = promisify(sheets.spreadsheets.values.get);

const TOKEN_DIR = __dirname;
const TOKEN_PATH = TOKEN_DIR + '/sheets.googleapis.com-nodejs-quickstart.json';

const main = async () => {
    //クレデンシャル情報の取得
    const content = await readFileAsync(__dirname+'/client_secret.json');
    const credentials = JSON.parse(content); //クレデンシャル

    //認証
    const clientSecret = credentials.installed.client_secret;
    const clientId = credentials.installed.client_id;
    const redirectUrl = credentials.installed.redirect_uris[0];
    const auth = new googleAuth();
    const oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);
    const token = await readFileAsync(TOKEN_PATH);
    oauth2Client.credentials = JSON.parse(token);

    //API経由でシートにアクセス
    const apiOptions = {
        auth: oauth2Client,
        spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
        range: 'Class Data!A2:E',
    };
    const response = await ssValuesGetAsync(apiOptions);

    //結果を表示
    console.log(response);
};

main();

これを実行します。

$ node getSheetsData.js

{ range: '\'Class Data\'!A2:E101',
  majorDimension: 'ROWS',
  values:
   [ [ 'Alexandra', 'Female', '4. Senior', 'CA', 'English' ],
     [ 'Andrew', 'Male', '1. Freshman', 'SD', 'Math' ],
     [ 'Anna', 'Female', '1. Freshman', 'NC', 'English' ],
     [ 'Becky', 'Female', '2. Sophomore', 'SD', 'Art' ],
     [ 'Benjamin', 'Male', '4. Senior', 'WI', 'English' ],
     [ 'Carl', 'Male', '3. Junior', 'MD', 'Art' ],
     [ 'Carrie', 'Female', '3. Junior', 'NE', 'English' ],
     [ 'Dorothy', 'Female', '4. Senior', 'MD', 'Math' ],
     [ 'Dylan', 'Male', '1. Freshman', 'MA', 'Math' ],
     [ 'Edward', 'Male', '3. Junior', 'FL', 'English' ],
     [ 'Ellen', 'Female', '1. Freshman', 'WI', 'Physics' ],
     [ 'Fiona', 'Female', '1. Freshman', 'MA', 'Art' ],
     [ 'John', 'Male', '3. Junior', 'CA', 'Physics' ],
     [ 'Jonathan', 'Male', '2. Sophomore', 'SC', 'Math' ],
     [ 'Joseph', 'Male', '1. Freshman', 'AK', 'English' ],
     [ 'Josephine', 'Female', '1. Freshman', 'NY', 'Math' ],
     [ 'Karen', 'Female', '2. Sophomore', 'NH', 'English' ],
     [ 'Kevin', 'Male', '2. Sophomore', 'NE', 'Physics' ],
     [ 'Lisa', 'Female', '3. Junior', 'SC', 'Art' ],
     [ 'Mary', 'Female', '2. Sophomore', 'AK', 'Physics' ],
     [ 'Maureen', 'Female', '1. Freshman', 'CA', 'Physics' ],
     [ 'Nick', 'Male', '4. Senior', 'NY', 'Art' ],
     [ 'Olivia', 'Female', '4. Senior', 'NC', 'Physics' ],
     [ 'Pamela', 'Female', '3. Junior', 'RI', 'Math' ],
     [ 'Patrick', 'Male', '1. Freshman', 'NY', 'Art' ],
     [ 'Robert', 'Male', '1. Freshman', 'CA', 'English' ],
     [ 'Sean', 'Male', '1. Freshman', 'NH', 'Physics' ],
     [ 'Stacy', 'Female', '1. Freshman', 'NY', 'Math' ],
     [ 'Thomas', 'Male', '2. Sophomore', 'RI', 'Art' ],
     [ 'Will', 'Male', '4. Senior', 'FL', 'Math' ] ] }

このサンプルではこちらのワークブックへアクセスして情報を取得しています。

ちなみに用語的には

というニュアンスです。

//省略

    const apiOptions = {
        auth: oauth2Client,
        spreadsheetId: '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
        range: 'Class Data!A2:E',
    };

//省略

ほかのワークブックにアクセスする場合はhttps://docs.google.com/spreadsheets/d/の後に続くIDをspreadsheetIdに指定して実行しましょう。rangeシート名:取得する範囲という形式で指定して、指定したシートの指定した範囲からセルの情報を取得します。

おわりに

これで次回以降は4番目の手順だけでスプレッドシートにアクセスできます。

client_secret.jsonsheets.googleapis.com-nodejs-quickstart.jsonをコピーしてサーバー設置しておけば自動更新する仕組みなども作れます。

スプレッドシートは色々な場面で利用されているツールなので是非システム連携などに活用してみて下さい。

応用としてはこちらの内容で使っています。

勉強会( #IoTLT )の登壇者管理でHeadless Chromeを利用している話

それでは!