【Google Apps Script】その15 Execution APIを使い、外部からAPIとして叩く

この記事はGoogle Apps Scriptを実例交えて基礎からざっくり学ぶ Advent Calendar 2017　15日目の記事です。

本アドベントカレンダーは@rt_pの個人プロジェクトですが、筆者はAteam Brides Inc. Advent Calendar 2017にも参加しています。そちらでも出張版記事を書いているので、覗いていただけると嬉しいです。

はじめに

遂に来ました！！
本アドベントカレンダーで肝となるExecution APIです。

これが何なのか簡単にお伝えすると、GET/POSTのAPIとして、GASを外部から叩ける仕組みです。

…それだけ？と思われましたか？
実はこれ、とても革新的です。

これを使えばGAS以外のWebサーバからGASにデータをPOSTしたり、逆にGETでスプレッドシートの中身をWebサーバで受け取ったり、GASのスクリプト同士をAPIとして連携することもできます。

スプレッドシートの準備

スプレッドシートにダミーデータを準備します。
また、シートに「アクセス数レポート」という名前を付けます。

日付	UU
2017/12/1	100
2017/12/2	120
2017/12/3	100
2017/12/4	110
2017/12/5	130
2017/12/6	140
2017/12/7	130
2017/12/8	120
2017/12/9	110
2017/12/10	110
2017/12/11	100
2017/12/12	130
2017/12/13	120
2017/12/14	110
2017/12/15	160
2017/12/16	110

このスプレッドシートに対し、日付指定でデータを挿入、更新したいと思います。
ちょうど5日目の記事を外部から操作できるようにしているイメージですね。

GAS内でデータのやり取りができる仕組みを作る

まずは、5日目の記事のコードほぼそのままですが以下内容を追加します。

スクリプトエディタを開き、以下コードに置き換えて実行します。
スクリプトエディタの開き方や承認が必要ですメッセージが出た際の対処法が分からない場合は
アドベントカレンダー1日目のHello, world!記事をご参照ください。

insert_or_update.gs

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('アクセス数レポート');
  var newData = ['2017/12/17', 150];
  insertOrUpdate(sheet, newData);
}

// 行の存在に応じて追加もしくは更新を行う
function insertOrUpdate(sheet, data) {
  var row = findRow(sheet, data[0]);
  if (row) { // 行が見つかったら更新
    sheet.getRange(row, 1, 1, data.length).setValues([data]);
  } else { // 行が見つからなかったら新しくデータを挿入
    sheet.appendRow(data);
  }
}

// 行番号を返す
function findRow(sheet, date) {
  var searchDate = Utilities.formatDate(new Date(date), 'Asia/Tokyo','yyyy/MM/dd'); // タイムゾーン違いが面倒なので、GASでの日付比較はgetTimeよりこっちの方が楽
  var values = sheet.getDataRange().getValues();

  for (var i = values.length - 1; i > 0; i--) {
    var dataDate = Utilities.formatDate(new Date(values[i][0]), 'Asia/Tokyo','yyyy/MM/dd');
    if (dataDate == searchDate) {
      return i + 1; // 配列のキーは0から始まり、行数は1から始まるのでズレを直す
    }
  }
  return false;
}

変わったところと言えば、

• シートを名前で取得した箇所
• セットする要素の数を減らした

点ぐらいでしょうか。

シートをgetActive()ではなくシート名で取得した意図としては、外部からAPIとしてGASを実行するとどのシートがアクティブになっているか分からず、思わぬシートに追記されたり動かない不具合を減らす為です。
シート名を直接入力することでやや冗長な書き方にはなりますが、別のシートに書き込まれることは無くなります（ただしシート名を勝手に変更されると動かなくなるリスクが増えます）。

実行すると以下のように、最新の行に新しい値が追加されているはずです。

doGetで値を取得する

スプレッドシートの値を外部から取得できるようにしてみましょう。

APIアプリケーションを公開しGETのリクエストが来た際、GASはdoGet()という関数が実行されます。
今回はdoGet()が正常に動くか検証する為、デバッグ用にdummyGet()を用意してそちらを実行します。

insertOrUpdate()、findRow()は上記コードをそのまま流用します。
以下コードを追記し、dummyGet()を実行します。

insert_or_update_do_get.gs

// 行の存在に応じて追加もしくは更新を行う
function insertOrUpdate(sheet, data) {
...
}

// 行番号を返す
function findRow(sheet, date) {
...
}

// doGetのデバッグ用
function dummyGet() {
  var e = {
    'parameter': {date: '2017/12/01'}
  };
  var res = doGet(e);
  var content = res.getContent();
  var getData = JSON.parse(content);
  Logger.log(getData);
  Logger.log(getData['uu']);
}

// getでデータ取得を行う
function doGet(e) {
  var res = {status: null, message: ''};
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('アクセス数レポート');
    var date = Utilities.formatDate(new Date(e.parameter.date), 'Asia/Tokyo','yyyy/MM/dd');
    var row = findRow(sheet, date);
    if (row) { // 値があった場合
      res['uu']      = sheet.getRange(row, 2).getValue();
      res['message'] = 'success';
    } else {
      res['uu']      = null;
      res['message'] = 'no data.';
    }
    res['status'] = 200;
  } catch (e) {
    res['status'] = 500;
    res['message'] = e;
  }

  var data = JSON.stringify(res);
  return ContentService.createTextOutput(data).setMimeType(ContentService.MimeType.JSON);
}

dummyGet()を実行すると、コードに問題がなければ2017/12/01のデータが取得されるので以下のように実行されます。

この表示が出たら、恐らくウェブアプリケーションとしてAPIを公開しても正常に動作するはずです。

手順としては公開→ウェブアプリケーションとして導入を選択します。

アプリケーションにアクセスできるユーザーの選択肢は全員（匿名ユーザーを含む）を選択します。
これを選ぶことでGoogleアカウントにログインしていないユーザーでもAPIを叩くことができます。

URLが表示されます。アクセスすると、以下のような表示が出るはずです。

パラメータがないので正常にデータが取得できていないようです。
GETなのでURLでパラメータを指定しましょう。

もし

現在、ファイルを開くことができません。

という、あまりにもざっくりしすぎたエラーが出たときは、1回Googleアカウントをログアウトするとうまくいったりします。

超重要！APIのコードを修正したらバージョンを変えること

開発用のURL（最新のコード　のURL）はコードを修正すると即座に反映されますが、本番用のURL（現在のウェブ アプリケーションの URL）は新しいバージョンとして新規作成する必要があります。
これに気付くまで、いくらコード修正しても動かないことに頭を悩ませていました…

doPostで値を追加する

スプレッドシートの値を外部から追加・更新できるようにしてみましょう。

APIアプリケーションを公開しPOSTのリクエストが来た際、GASはdoPost()という関数が実行されます。
doGetに対するdummyGetと同じように、doPost()が正常に動くか検証する為、デバッグ用にdummyPost()を用意してそちらを実行します。

insertOrUpdate()、findRow()、dummyGet()、doGet()は上記コードをそのまま流用します。
以下コードを追記し、dummyPost()を実行します。

insert_or_update_post.gs

// 行の存在に応じて追加もしくは更新を行う
function insertOrUpdate(sheet, data) {
...
}

// 行番号を返す
function findRow(sheet, date) {
...
}

// doGetのデバッグ用
function dummyAccess() {
...
}

// getでデータ取得を行う
function doGet(e) {
...
}

// doPostのデバッグ用
function dummyPost() {
  var e = {
    'parameter': {date: '2017/12/17', uu: 1000}
  };
  var res = doPost(e);
  var content = res.getContent();
  var getData = JSON.parse(content);
  Logger.log(getData);
}

// postでデータ追加を行う
function doPost(e) {
  var res = {status: null, message: ''};
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('アクセス数レポート');
    var date = Utilities.formatDate(new Date(e.parameter.date), 'Asia/Tokyo','yyyy/MM/dd');
    var uu   = e.parameter.uu;
    var newData = [date, uu];
    insertOrUpdate(sheet, newData);
    res['status'] = 200;
    res['message'] = 'success';
  } catch (e) {
    res['status'] = 500;
    res['message'] = e;
  }

  var data = JSON.stringify(res);
  return ContentService.createTextOutput(data).setMimeType(ContentService.MimeType.JSON);
}

コードが合っていると、スプレッドシートに値が追加されます。
日付や値を変えて実行してみましょう。

あとは、これがPOSTリクエストの際に正常に動作するか試してみましょう。

WebブラウザでPOSTリクエストを送るのは面倒なので、Linuxコマンドラインでcurlを実行します。

curl -L -d "param1=value1&param2=value2" http://example.com/

-L　リダイレクトを追う
-d　POSTパラメータの指定

成功すると、以下のようにスプレッドシートに値が追加されているのが確認できます。

本日は以上です。

おわりに

本アドベントカレンダーでは、今後もこのExecution APIを使ってアプリケーション同士を組み合わせていきます。

明日

【Google Apps Script】その16　GASとスプレッドシートだけで簡易RSSリーダーを作り、Chatworkに新着通知する

GASでRSSリーダーを作って、Chatworkに通知してみます。

前の記事
【Google Apps Script】その14　SEO用にタイトルとディスクリプションを収集する
次の記事
【Google Apps Script】その16　GASとスプレッドシートだけで簡易RSSリーダーを作り、Chatworkに新着通知する