この記事は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!記事をご参照ください。
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()を実行します。
// 行の存在に応じて追加もしくは更新を行う
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()を実行します。
// 行の存在に応じて追加もしくは更新を行う
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¶m2=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に新着通知する