​ ​
08_img.jpg

Node.jsからExecution APIを実行してスプレッドシートのデータを取得する

こんにちは。ファームノートプロダクト開発グループの伊藤です。
先日、スタッフブログで帯広本社に牧場を作りました。今回もその牧場を強化しようと思ったのですが、粘土の手配が間に合わなかったので業務で使った内容をまとめてみます。

私はエンジニアではなくアシスタントとしてテストを行っています。テストケースやテストデータを作っていて、何度もテストデータを流し直したりデータを1件づつ登録したりと時間もかかって大変でした。そこで、せめて1件ずつ登録する作業を自動化したいと思い色々調べたところ、Googleのスプレッドシートのデータを外部から取得するAPIがあったのです。スプレッドシートに登録したいデータを保存しておいて、外部からデータを取得しプログラムで自動的に登録する。最高です。やってみましょう。

今回参考にさせていただいたサイトがこちらです。

【GAS】スプレッドシートのデータをJSON出力する関数をExecution APIで外部のNode.jsから実行する

とても丁寧に画像付きでわかりやすく解説してくださっています。
今回はExecution APIの使い方はさらっと流して私が困った点をご紹介したいと思います。
詳しい説明はこちらのサイトを参考にしてください。

スプレッドシートの準備

スプレッドシート側の設定を簡単にまとめると

・スプレッドシートで表を作る
・「ツール」>「スクリプトエディタ」にGoogleAppsScript(GAS)でシートのデータを取得する関数をかく
・「ツール」>「実行可能なAPIとして導入」でバージョンを指定し導入する
・「現在のAPI ID」を控えておく
・「ファイル」>「プロジェクトのプロパティ」の「スコープ」を控える
  (今回はスプレッドシートなのでhttps://www.googleapis.com/auth/spreadsheets)
・「リソース」>「Developers Console」でExecution APIを使えるようにする
・クライアントIDを発行する(※1)
・認証情報が書かれたJSONファイルをダウンロードする

スプレッドシートの表はこのようにしました。

02_spreadsheet.jpg

スクリプトエディタのソースはこちらです。

var bookUrl = 'https://docs.google.com/spreadsheets/d/< スプレッドシートのURL >';

function getSheetData() {
  
  var book = SpreadsheetApp.openByUrl(bookUrl);
  var sheetName = "sheet1";
  
  var sheet = book.getSheetByName(sheetName);
  
  var jsonData = convertSheet2Json(sheet)
  Logger.log(JSON.stringify(jsonData));
  return JSON.stringify(jsonData);
}

function convertSheet2Json(sheet) {
  var colStartIndex = 1;
  var rowNum = 1;
  var firstRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var firstRowValues = firstRange.getValues();
  var titleColumns = firstRowValues[0];

  var lastRow = sheet.getLastRow();
  var rowValues = [];
  for(var rowIndex=2; rowIndex<=lastRow; rowIndex++) {
    var colStartIndex = 1;
    var rowNum = 1;
    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
    var values = range.getValues();
    rowValues.push(values[0]);
  }

  var jsonArray = [];
  for(var i=0; i<rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j<titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }
    jsonArray.push(json);
  }
  return jsonArray;
}

Node.jsの準備

npmで必要なライブラリをインストールします。

$ npm install googleapis --save
$ npm install google-auth-library --save

次にGASの公式サイトからサンプルソースを持ってきて適当な場所に保存します。
ファイル名は「quickstart.js」です。

 var fs = require('fs');
 var readline = require('readline');
 var google = require('googleapis');
 var googleAuth = require('google-auth-library');
 // If modifying these scopes, delete your previously saved credentials
 // at ~/.credentials/script-nodejs-quickstart.json
 var SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
 var TOKEN_DIR = (process.env.HOME || process.env.HOMEPATH ||
process.env.USERPROFILE) + '/.credentials/'; var TOKEN_PATH = TOKEN_DIR + 'script-nodejs-quickstart.json';
・・・・・・・・・・・・・ (中略)・・・・・・・・・・・・・・・
function callAppsScript(auth) { var scriptId = '******* 現在のAPI IDを記入 *******'; var script = google.script('v1'); script.scripts.run({ auth: auth, resource: { function: 'getSheetData' }, scriptId: scriptId }, function(err, resp) { if (err) { console.log('The API returned an error: ' + err); return; } if (resp.error) {
・・・・・・・・・・・・・ (中略)・・・・・・・・・・・・・・・
if (error.scriptStackTraceElements) { // There may not be a stacktrace if the script didn't start executing. for (var i = 0; i < error.scriptStackTraceElements.length; i++) { var trace = error.scriptStackTraceElements[i]; console.log('\t%s: %s', trace.function, trace.lineNumber); } } } else { var folderSet = resp.response.result; console.log(folderSet); } }); }

「SCOPES」にスクリプトエディタでコピーしたOAuthのスコープを貼ります。
「scriptId」に現在のAPI IDを貼ります。
「function」がサンプルと違うのでスクリプトエディタで作成した呼び出す関数名(今回はgetSheetData)を記入します。GASの関数の戻り値がサンプルと違うのでちょっと変えて、後はサンプルと同じです。(サンプルは英語でコメントに解説が書いてあります。長いので削除していますが詳しく知りたい方は本物を是非ご覧になってみてください。)

動かす

準備が完了したので動かしていきます。

$ node quickstart.js

を実行するとターミナルに以下のものが表示されます。

$ node quickstart.js 
Authorize this app by visiting this url: https://accounts.google.com/***  長いです ***
Enter the code from that page here:

表示されたURLをコピーしてブラウザにペーストし許可のボタンを押すとTokenがもらえるのでこれを上の「here:」に入力します。

03_Token.jpg

04_result.jpg

取得できました!

さて、本題の私の困りポイントを書いていきます。

困りポイント1

※1のクライアントIDを作成する時「ウェブアプリケーション」だとどうしてもうまくいきませんでした。公式サイトの英語をなんとか解読すると「Node.js」では「その他」を選択すると書いてあるのでそのようにしました。

01_clientId.jpg困りポイント2

クライアントIDの取得を「ウェブアプリケーション」で試したり「その他」で試したり色々していたら「許可したアクセス方法じゃないよ!」というエラーが返ってきました。
取得したTokenに色々書いてあるんだと思い、保存された場所をネットで検索すると英語で書かれたサイトが出てきました。解読して消すことはできたのですが、全部quickstart.jsに書かれているんですよね。

05_TokenPlace.jpg

/Users/user/.credentioals/の中にscript-nodejs-quickstart.jsonが作られてこの中にTokenが書いてあるのでクライアントを変えるのであればこれを削除してください。気がつかなかったのは完全に私の注意不足です。

困りポイント3

結構困ったのがこれです。Node.jsからGAS側にパラメータを渡したかったのですが、Node.jsでの渡し方は紹介されているサイトが多かったのですが、GAS側の受け取り方が見つけられませんでした。ちょっとご紹介します。

パラメータにシート名を指定して指定したシートのデータを取得するように変更しようと思います。
スプレッドシートにシートを追加します。先ほどの表が記載されているシートの名前は「sheet1」で新しく追加したシートは「sheet2」としました。sheet2に追加した表がこちらです。

06_sheet2.jpg

内容は牛の毛色ですね。ちなみに私は赤白が一番好きです。

quickstart.jsの関数名を指定した部分にパラメータを追記します。

resource: { 
function: 'getSheetData',
parameters: [ 'sheet2']
},

「parameters」でシート名を指定します。

では、GASのスクリプトを変更しましょう。
簡単に言うと以下のように引数に入ってきます。

function getSheetData(param) {
  var book = SpreadsheetApp.openByUrl(bookUrl);
  var sheetName = param;
  var sheet = book.getSheetByName(sheetName);
  
  var jsonData = convertSheet2Json(sheet)
  Logger.log(JSON.stringify(jsonData));
  return JSON.stringify(jsonData);
}

GASのスクリプトを変更したので「公開」>「実行可能APIとして導入」でバージョンを上げてください。

そして、実行すると

07_result2.jpg取得できました!

パラメータは配列で渡せるようなのでパラメータの数を増やす時はGASの関数の引数を

function getSheetData (param1, param2, param3) {

のように増やすと取得できるようです。数があってなくても特にエラーは起きません。

まとめ

Execution APIはGASの設定の手順を覚えてしまえば簡単に使用することができるようです。
データが返ってくるのに少し時間がかかるのでRDBのように使うのは難しいような気がしますが、色々な利用方法があるように思えます。
また、サンプルのソースや英語も拒否反応を起こさずよく読むことが大事だと思いました。
以上です。

弊社では一緒に働いてくれる仲間を募集しています!特にエンジニアを求めています!弊社HPWantedlyからのご応募お待ちしています。

このエントリーをはてなブックマークに追加