Go to Qiita Advent Calendar 2024 Top
LoginSignup
37
47

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

Google Apps Scriptを実例交えて基礎からざっくり学ぶAdvent Calendar 2017

Day 15
@rf_p(ryu fukuda)in株式会社エイチームライフデザイン

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

Last updated at Posted at 2017-12-17

この記事は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として連携することもできます。

command.png

sheet4.png

スプレッドシートの準備

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

日付 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

sheet.png

このスプレッドシートに対し、日付指定でデータを挿入、更新したいと思います。
ちょうど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を実行するとどのシートがアクティブになっているか分からず、思わぬシートに追記されたり動かない不具合を減らす為です。
シート名を直接入力することでやや冗長な書き方にはなりますが、別のシートに書き込まれることは無くなります（ただしシート名を勝手に変更されると動かなくなるリスクが増えます）。

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

sheet2.png

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のデータが取得されるので以下のように実行されます。

dummyLog.png

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

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

webapp.png

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

open.png

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

res.png

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

res2.png

もし

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

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

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

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

version.png

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);
}

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

sheet3.png

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

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

command.png

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

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

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

sheet4.png

本日は以上です。

おわりに

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

明日

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

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

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

37
47
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
37
47

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?