Google Apps Scriptでtrocco®をイベント駆動にしようとする

はじめに

データ統合自動化SaaSのtrocco®では、システム／サービス間でのデータの転送やDWH内でのデータマートの作成、そしてそれらを依存関係を含めてまとめたワークフローの実行ができます。これらの処理はスケジュール設定することで、毎時／毎日／毎週／毎月のように決まった時間に実行できます。

とはいえ、ときにはファイルを格納したら転送処理やワークフローが実施されるという、イベント駆動の処理もできるようであって欲しいですよね。実は、それを実現する機能として、外部からジョブを開始させるためのAPIがあります。

今回は、このtrocco APIをGoogle Apps Scriptで叩いて、イベント駆動にするための方法を検討してみます。

こんな方におすすめ

• trocco APIについて詳しく知りたい
• 特別なサービスを使うのではなく、Google Apps Scriptで簡易な形に留めておきたい
• Google Apps Scriptの起動条件について詳しく知りたい（後半だけでも勉強になるかと思います！）

trocco APIとは

まず、概要について紹介します。trocco APIはtrocco®の有償オプション機能の1つで、具体的には下記のようなものです。

troccoの転送ジョブ・ワークフローに対して、API経由でジョブの起動・ステータス確認ができます（1日最大10,000コール、15分間で最大100コールまでの機能制限あり）。API経由での実行時には、カスタム変数の指定も可能です。
（出典：公式ドキュメント）

実はカスタム変数やcontext_timeを指定して実行できるというのが、データ基盤の運用をする上で非常に便利な機能だったりするのですが、今回はその部分は割愛して、シンプルな転送ジョブ／ワークフローの実行について取り上げようと思います。

ジョブを実行する

下記のコマンドを叩くと（{}部分には特定の値が入ります）、

curl 'https://trocco.io/api/jobs?job_definition_id={JOB_DEFINITION_ID}' \
-X POST \
-H 'Authorization:Token {API_KEY}' \
-d 'context_time={YYYY-MM-DD HH:MM:SS}' \
-d 'time_zone=Asia/Tokyo' \
-d 'custom_variables[][name]=${VARIABLE_NAME}$' \
-d 'custom_variables[][value]={VARIABLE_VALUE}'

下記のJSONがレスポンスで返ってきます。

{
  "id":12345,
  "job_definition_id":54321,
  "job_definition_name":"sample-job-definition-name",
  "status":"queued",
  "created_at":"2023-05-19T19:14:00.000+09:00"
}

ワークフローを実行する

下記のコマンドを叩くと、

curl 'https://trocco.io/api/pipeline_jobs?pipeline_definition_id={PIPELINE_DEFINITION_ID}' \
-X POST \
-H 'Authorization:Token {API_KEY}' \
-d 'context_time={YYYY-MM-DD HH:MM:SS}' \
-d 'time_zone=Asia/Tokyo'

下記のJSONがレスポンスで返ってきます。

{
  "id":12345,
  "pipeline_definition_id":54321,
  "name":"sample-pipeline-definition-name",
  "status":"queued",
  "started_at":null,
  "finished_at":null,
  "context_time":"2023-05-18T17:10:20.000+09:00",
  "created_at":"2023-05-18T17:15:20.000+09:00"
}

このように、trocco APIは簡単に利用することができます。では、ここから具体的な設定に入っていきましょう。

API KEYの取得

まず、API KEYの取得からはじめます。API機能が有効化になっているアカウントのユーザーであれば、右上の設定「API KEYの設定」から行うことができます。

Google Apps ScriptでAPIを叩く

取得したAPI KEYを利用して、下記のようにHTTPのGETリクエストを投げることで、転送ジョブやワークフローの実行ができます。簡単ですね。

function callTroccoAPI(target_type='job', target_id='********') {  // 該当するIDを入力する
  const headers = {
    // API KEYはスクリプト プロパティに格納している
    'Authorization': 'Token ' + PropertiesService.getScriptProperties().getProperty('TROCCO_API_KEY')
  }
  const options = {
    'method': 'post',
    'headers': headers
  }
  const url_dict = {
    'job': 'https://trocco.io/api/jobs?job_definition_id=',
    'workflow': 'https://trocco.io/api/pipeline_jobs?pipeline_definition_id='
  }

  const res = UrlFetchApp.fetch(url_dict[target_type] + target_id, options)
  const res_json = JSON.parse(res.getContentText())
  const definition_name = res_json[Object.keys(res_json).filter(value => value.match('name'))[0]]
  Logger.log(res.getContentText())
  return [res_json['id'], definition_name]
}

実行するのがジョブとワークフローのどちらなのか、そして実行定義のIDを引数として指定することで、汎用的に使えるコードにしています。

Google Apps Scriptでイベント駆動を模索する

もともとこれを考え始めたきっかけが、CSVダウンロードしたファイルをどこかに格納したら自動更新したいということで、それを操作が手軽なGoogle Drive起点で何とかできないかというのがありました。

もちろん、ちゃんと作ろうとすればGoogle Cloud等のサービスを使えば簡単にできそうに思えますが、非エンジニアでも実装に時間がかからず、かつ簡易に内容を調整できるものとして、Google Apps Script内での処理に限定しています。

では、ぶっちゃけあまり上手くいかなかったのですが、やり方を考えてみましょう。

Google Apps Scriptのトリガーとは

Google Apps Scriptでは、作成した関数を実行するための機能として、トリガーがあります。また、スクリプトはスタンドアロン（独立ファイル）にするか拡張機能として特定のファイル（スプレッドシート、スライド、フォーム、ドキュメント）に埋め込むことができ、後者であればファイルの操作と連動をさせたスクリプトの実行ができます。
（doGet(e)、doPost(e)という見慣れないものがあるのですが、これは後ほど・・・）

（出典：公式ドキュメント「シンプルなトリガー」

まず、タイムドリブンは分かりやすいですね。一般によく使われている、毎週X曜日n時〜n+1時に実行するなどというものです。私も大変お世話になりまくっているのですが、定期実行なので今回の目的には適合しません。

次に、埋め込み形式についてはイベント駆動のものがいくつか見当たります。しかしドライブにファイルを格納したらというのはありませんでした。残念ですね・・・。

IFTTT→スプレッドシートで起動する（できない）

改めて考え直してみると、「Google Driveの特定のフォルダにファイルを格納したら～」というのは、IFTTTやZapierを使えば実現できそうです。また先ほどの一覧にあったように、スプレッドシートの編集をトリガーにすると、IFTTTでファイルの格納を検知→スプレッドシートのセルを編集→編集を検知してスクリプトを実行、までつなげることができそうです。

・・・と考えたのですが、これは実装してみると機能しませんでした。調べてみると、下記の制約が要因となっていました。

スクリプトの実行や API リクエストによってトリガーが実行されることはありません。たとえば、Range.setValue() を呼び出してセルを編集した場合、スプレッドシートの onEdit トリガーは実行されません。
出典：公式ドキュメント「シンプルなトリガー」

困ったものですね。というかちゃんとドキュメント見ろって感じですね。ということで一覧で残っている、doGet(e)／doPost(e)という見慣れないものを改めて調べてみます。

doGet(e)／doPost(e)とはなにか

これらのトリガーについては、以下のような説明が記載されています。

ユーザーがウェブアプリにアクセスすると、またはプログラムが HTTP GET リクエストをウェブアプリに送信すると、doGet(e) トリガーが自動的に実行されます。プログラムが HTTP POST リクエストをウェブアプリに送信すると、doPost(e) が実行されます。
出典：公式ドキュメント「シンプルなトリガー」

つまり、URLを公開して、外部からのアクセスを起点に起動させるようです。IFTTTにはWebhookの機能があるので、そこからGETリクエストを投げれば良さそうです。
（ちなみに、直接trocco APIを叩けばいいといえばそうなのですが、ファイル名の加工等の別の処理と連動させやすそうなので、Google Apps Scriptの起動のみに留めておきます。）

Webアプリとして公開して、doGet()起点で起動する

では、

• IFTTTでGoogle Driveへのファイル格納を検知して、Webhookを投げる
• GETリクエストを起点にGoogle Apps Scriptを起動して、troccoのAPIにリクエストを投げる
• troccoのジョブが実行される

という形でできそうですね！ということでこの設定をしてみましょう。

IFTTTの設定はUIを見れば簡単に設定できるので割愛します。

Google Apps ScriptのWebアプリとしての公開ですが、画面右上のデプロイを行うことで設定ができます。

この際、Webhookを利用して起動することを踏まえると、アクセスできるユーザーを全員にする必要が出てきます。致し方ないことではありますが、ここで上手くアクセス元を制限できないかというのが困りどころですね…

また、スクリプトの保存≠Webアプリの更新であることにも注意が必要です。ただ単純に保存しただけでは、Webアプリとしては古い設定がそのままになっています。編集を加えた際には、新しいバージョンとしてデプロイするよう注意してください。

スクリプトは単純に関数名をdoGet(e)にするだけで大丈夫です。このとき、デフォルトの設定でクエリパラメータを抽出することができます。この機能を使うと、WebhookのURLを工夫するだけで、実行する転送ジョブやワークフローの使い分けができるようになります。

// WebhookでのURLを下記にしておく
// https://script.google.com/macros/s/{デプロイID}/exec?target_type={job/workflow}&target_id=********
function doGet(e) {
  const target_type = e.parameter['target_type']
  const target_id = e.parameter['target_id']
  const res = callTroccoAPI(target_type, target_id)
  Logger.log(res)
  const each_id = res[0]
  const definition_name = res[1]
  return ContentService.createTextOutput(definition_name + ' ' + each_id + ' started!')
}

パラメータの取り扱いについては、公式ドキュメント「ウェブアプリ」を参考にしてください。

これで、Google Driveにファイルを格納すると、イベント駆動でtrocco®️のジョブを実行するところまでを自動化できました。

対象ファイルが1つでないとき、全て格納後にSpreadsheetを開くことで起動する

と、ここまで上手くできたつもりでいたのですが、改めて詳細の要件を確認してみると、複数のファイルを格納したあとに、一括で更新したいという形でした。今のままだと、個別のファイルを格納するたびに起動してしまうので、やっぱりまとめてワークフローを起動したいよね、とボツになりました。

ということで改めて考えると、いったんは複数ファイルを格納する→指定のSpreadsheetを開くというのが現実的になりそうです。これなら当然アクセス制御をかけられるというのもありますからね。

この設定は簡単です。まず、スタンドアロンではなく使いたいスプレッドシートを開いて、「拡張機能」タブ→「Apps Script」でGoogle Apps Scriptを開きます。そして、トリガーとしてファイルを開いたら起動するトリガーを設定するか、onOpen(e)という関数を設定するだけです。今回は後者でやってみます。

function onOpen(e) {
  const target_type = 'job'
  const target_id = '********'
  const res = callTroccoAPI(target_type, target_id)
  Logger.log(res)
  const each_id = res[0]
  const definition_name = res[1]
  Logger.log(definition_name + ' ' + each_id + ' started!')
  Browser.msgBox('Notice', definition_name + ' ' + each_id + ' started!', Browser.Buttons.OK)
}

これで実行対象の転送ジョブ／ワークフロー名と起動の確認ができます。

さいごに

まとめると、下記のようなフローでデータ格納後のデータ更新が自動化できました。

• 複数のファイルをGoogle Driveの指定のフォルダに格納する
• 特定のスプレッドシートを開く
• スプレッドシートを開くことで、Google Apps Scriptが起動する
• Google Apps Scriptでtrocco APIにリクエストをしてワークフローを実行する
• ワークフロー内で複数の転送ジョブを実行する（ここは記載していないですが）

スプレッドシートを開くのに1つ手作業が入るので、満足し切れてはいませんが、これでイベント駆動に近いようなデータ更新が実現できるようになりました。色々試行錯誤で時間がかかりましたが、Google Apps Scriptの利用方法も理解が深まり、今後も幅広く使っていけそうな予感がしてきました！

おまけ：スプレッドシートで占いを表示する

onOpen(e)の動作検証がてら、雑に作ってみたので掲載しておきます（笑）

function onOpen(e) {
  let reply = Browser.msgBox('お知らせ', 'GASが起動しました！！占いをしますか？', Browser.Buttons.YES_NO)
  if (reply = 'yes'){
    Utilities.sleep(2000)
    const point = Math.random()
    let fortune = ''
    if (point < 0.01){fortune = '大凶'} 
    else if (point < 0.1) {fortune = '凶'}
    else if (point < 0.25) {fortune = '末吉'}
    else if (point < 0.5) {fortune = '吉'}
    else if (point < 0.75) {fortune = '小吉'}
    else if (point < 0.95) {fortune = '中吉'}
    else {fortune = '大吉'}
    Browser.msgBox('本日の運勢', fortune + 'です！', Browser.Buttons.OK)
  }
}