Node.jsによるGoogleスプレッドシートとの連携

こんにちは！Kenです。

久しぶりの投稿となりますが、今回はGoogleスプレッドシートとの連携を行いたいと思います。

普通の人であればきっとGAS（Google Apps Script）でスプレッドシートを操作することでしょう。

単純にスプレッドシートを操作することだけでしたらGASで事足りると思います。

しかし、LINEBOTやSQLデータベースの併用など、全体アプリの中の一部分としてスプレッドシートを用いる場合はGASでは無理があります。

また、VS Codeに慣れてしまった私はGASのエディタに馴染めませんでした。

というわけで、今回はNode.jsによるスプレッドシートとの連携方法を解説してみたいと思います。

スプレッドシートを使えるようにすると、これ自体がデータベース代わりになるし、データの入出力画面の作成も不要となるので非常に便利です。

この記事で作成するアウトプット

LINEBOTを題材に記事を書いていきたいと思います。

LINEへ送ったメッセージがそのままスプレッドシートに転記されるという単純なものです。

私のnoteのLINEBOT作成（第1話〜第6話）が終わっている前提で解説を進めたいと思います。

GCPの設定

　まずはGoogle Cloud Platformの設定をしていきましょう。スプレッドシートと連携するためには、GoogleAPIを使いますが、その認証情報を設定する必要があります。

以下ページから設定をしていきます。

Google Cloud プラットフォーム

画面左上のところクリックです。

新しいプロジェクトを作成します。

プロジェクト名を入力し、作成します。今回のは「for-note-test」としました。

次の画面です。OAuth同意画面を開きましょう。User Type外部を選択し、作成します。

次の画面はこんな感じですね。アプリ名とユーザーサポートメールは入力必須項目です。

そして下の方にいき、デベロッパーの連絡先を入力します。上のユーザーサポートメールアドレスと合わせる必要はありません。入力したら、保存して次へいきましょう。

次のスコープについては特に何もせず、保存して次へいきましょう。

次のテストユーザー画面では、このAPIを用いるgmailアドレスを設定します。このgmailアドレスに対し、権限が割り当てられるイメージです。

ユーザーを追加したら、保存して次へいきましょう。

最後は概要なので、眺めていただき、よかったらダッシュボードへ戻ります。

ちなみにここはOAuthのダッシュボード画面ですが、アプリを公開をクリックし、本番モードにすることで、どんなユーザーでもこのAPIにアクセスすることが可能となります。本番のサービスとして公開する場合は、ここで公開します。

さて次は認証情報を作成していきます。サイドバーから認証情報を選んで、上の認証情報の作成をクリックします。

OAuthクライアントを作成します。

アプリケーションの種類は自身のアプリに合わせたものを選択してください。今回はウェブアプリケーションを選択します。

その他は何も入力せずに、作成をクリックすると次のようにOAuthクライアントが作成されます。

次は認証情報を作成→サービスアカウントの作成です。

サービスアカウント名を入力し作成をします。このアカウント名は一意のものである必要があります。

ロールはオーナーを選択し、続行をクリックします。

そしてその他は何も入力せずに完了します。

これでサービスアカウントも作成できました。

次に秘密鍵を追加します。サービスアカウントの右の方の鉛筆マークをクリックすると編集画面へ移れます。

キーのタブを選択し、鍵を追加をクリックし、新しい鍵を作成します。この時、JSON形式を選択します。

そうすると鍵が作成され、左下にJSONファイルができたことがわかります。

このファイルをLINEBOTプロジェクトのルートディレクトリに移動しましょう。VS Codeで見るとこんな感じです。右側がファイルの中身です。private_keyという項目を後ほど使います。

では次に必要なAPIサービスを有効にします。

ダッシュボードにて、APIとサービスの有効化をクリックします。

Google Sheets APIを選択します。

有効にします。

また同様にGoogle Drive APIを有効にします。

長かった設定もようやくこれで終わりです。

スプレッドシートの作成

Googleドライブの中にスプレッドシートを作成しましょう。

そしてシートができたら、右上の共有ボタンをクリックし、先ほど作成したサービスアカウントがこのシートにアクセスできるように設定します。

ここでサービスアカウントのメールアドレスを入力する必要があるので、先ほど作成した鍵JSONファイルの中のメールアドレスをコピペしましょう。

client_emaiという項目ですね。

こんな感じですね。権限は編集者にしておきましょう。（後でスプレッドシートに書き込むため）

完了をし、登録します。

そして後ほどプログラムの中でスプレッドシートIDを使うことになりますので、どこかにメモっておくと良いでしょう。スプレッドシートIDはスプレッドシートファイルごとに固有の値で、ブラウザのURL表示部分から確認することができます。

ここがスプレッドシートIDです。

スプレッドシート操作のコーディング

では、いよいよスプレッドシート操作のコーディングになります。

サーバーサイドの処理なので、index.jsに書いていきます。

まずgoogleapisというnpmパッケージをインストールします。

$ npm i --save googleapis

次にこれをプログラムで読み込みます。

また、鍵JSONファイルもprivatekeyという名前で読み込みましょう。

さらに、先ほどのスプレッドシートIDもプログラムの中で使えるように宣言しておきます。

冒頭の宣言部分は次のようになるはずです。

const express = require('express');
const app = express();
const line = require('@line/bot-sdk');
const { google } = require('googleapis');
const privatekey = require('./for-note-test-fa87d57faa01.json');
const sheetId = '1RISpQbS78dJvz1p-xJjGmz0SlybHzW8LLYRIQ2BKIlg';
const PORT = process.env.PORT || 5000

次に認証を与えるauthorizeメソッドを書いていきます。

次のようなメソッドをスタンドアロンでコーディングします。

const authorize = () => {

 //authの設定
 const jwtClient = new google.auth.JWT(
   privatekey.client_email,
   null,
   privatekey.private_key,
   ['https://www.googleapis.com/auth/spreadsheets']
   );
   
 //リクエストの承認をチェックする
 jwtClient.authorize( (err, tokens) => {
   if (err) {
     console.log(err);
     return;
   } else {
     console.log('Authorize OK!!');
   }
 });
 
 return google.sheets({version: 'v4', auth: jwtClient});
}

このようにメソッド化しておくと、認証が必要となった際は常にこれを呼び出せば良いだけなので、非常に楽です。

ではLINEにメッセージを送ったらそれがシートに追加されるな処理とするので、コードを各場所はhandleMessageEvent関数の中ですね。

現状のコードは以下のようになっているかと思います。ここに追加していきます。

const handleMessageEvent = async (ev) => {

 const profile = await client.getProfile(ev.source.userId);
 const text = (ev.message.type === 'text') ? ev.message.text : '';
 
 return client.replyMessage(ev.replyToken,{
     "type":"text",
     "text":`${profile.displayName}さん、今${text}って言いました？`
 });
}

おうむ返しをする前に、シートへ入力する処理を入れることにしましょう。

処理は次のようになります。

const handleMessageEvent = async (ev) => {
 const profile = await client.getProfile(ev.source.userId);
 const text = (ev.message.type === 'text') ? ev.message.text : '';
 
 //認証
 const sheets = authorize();
 
 //リクエストオブジェクト
 const append_request = {
   spreadsheetId: sheetId,
   range: 'シート1!A1',
   insertDataOption: 'INSERT_ROWS',
   valueInputOption: 'USER_ENTERED',
   resource: {
     values: [[`${text}`]]
   }
 };
 
 //APIの実行
 await sheets.spreadsheets.values.append(append_request);
 
 return client.replyMessage(ev.replyToken,{
     "type":"text",
     "text":`${profile.displayName}さん、今${text}って言いました？`
 });
}さ

先ほど実装したauthorizeメソッドを実行して、APIが利用可能な状態にしておきます。

append_requestはAPIの実行する種類によって書き方が変わってきます。今回は、spreadsheets.values.appendというAPIを使いますので、その書き方になってます。

やりたい操作によって使う関数が異なってきますので、必要に応じて公式のドキュメントを読む必要があります。英語ですが（汗）コツがわかると読み方もわかってくると思います。

Google Sheets API  |  Google Developers

今回使ったappendは指定したセル（今回はA１）を参照し、末尾の行にどんどん値を追加していく関数です。

ではデプロイして確かめてみましょう。

動作確認

LINEのトーク画面で好きなメッセージを送ってみてください。

私は「入力できるかな？」にしました。

おうむ返しが返ってきましたね。返ってきたということは成功です！なぜならば、おうむ返しの前にスプレッドシートAPI処理があり、エラーが出ると、おうむ返しまで到達しないからです。

では、スプレッドシートを確認してみましょう。

おお！！入力できてますね^^

では、もう一度メッセージを送ってみましょう。次は「２回目だぞ」です。

下の行に追加されていますね！！

大成功です。

今回は最初のハードルとなる認証のところをメインに解説してきました。

認証さえできればあとは自分のやりたい操作をどんどん追加していくだけです。

例えば、他にも以下のような処理が可能となってきます。

■ドライブ内にシートを新規作成する
■あるシートのコピーをドライブ内に作成する
■シートからの値の読み込み
■シートのセルの値の更新
■シートの値のクリア

まぁエクセルでできることは大方できると思っていただいて大丈夫です。

では、今回はこの辺で！！

MENTAでプログラミングのサポートもさせていただいてます。お気軽にご相談ください。