ロコガイド テックブログ

「地域のくらしを、かしこく、たのしく」する、株式会社ロコガイドの社員がいろいろな記事を書いています。

「地域のくらしを、かしこく、たのしく」する、株式会社ロコガイドの社員がいろいろな記事を書いています。

GAS×Redashで月18時間の工数削減を実現！ ～API操作は難しいけど便利だった～

GAS 情シス Redash API

f:id:hryk-suzuki:20201022114633p:plain

こんにちは。ITシステム部の鈴木です。
社内のメンバーの仕事が少しでも楽になるよう頑張っています！

私は本格的なプログラミングはやったことがないのですが、情シスとして社内の困りごとを改善していくというミッションのもと、これまでVBA、VBScriptやシェルスクリプト等を広く浅くやってきました。
今回は、2月にロコガイドに入社してから入門したGoogle Apps Script（GAS）を使ってメンバーのお役に立った話をします。

背景

私たちはITに関する様々な困り事をアンケート・チャットでヒアリングしながら、いろんな便利ツールを作っています。
今回はその中から、直近で作った「Redashからスプレッドシートに情報を入れるGAS」をご紹介します。

きっかけ

きっかけはメンバーの次のような発言でした。

「ルーティーンでRedashからデータを抜き出してエクセルとかで集計してパワポにしてるんだけど、これポチッとなで出来ない？毎週この作業で自分だけでも30分くらいかかっててメンドクサイんですよー。最大7人でやってるので、210分も無駄にしていることになりますよね？」

そこで、

「Redashから自動でデータ取ってきて集計してあげればいいのかな？ 」
「Redashは見たことあるけど、本格的に使ったことないしなぁ… 」

と思いつつも、話を詳しく聞きながら解決方法を考えてみることにしました。

最初の失敗

関数でチャレンジ

スプレッドシートの関数で、外部サイトのデータを持ってこれるものがあった記憶がありました。

→importdataだ！
→ダメだ、URLにクエリパラメータを入れるとエラーになる。

例えば、=importdata(“https://＜ホスト名＞/queries/＜クエリID＞?＜パラメータ名１=＞○○○○&?＜パラメータ名２=＞××××”)すると、結果は#N/Aになってしまいます。

一旦この部分は諦めて先に後続処理だけ作成

そこでRedashからのデータ取得部分は後で考えるとして、まずはCSV形式のデータを集計して、項目ごとに別シートにしてあげるところまでを作ることにしました。 しかし、

  • Redashでクエリを実行して
  • CSVをダウンロードして
  • スプレッドシートに貼る

というのを手動でやってって言うのもかっこ悪いし、工数削減効果が少なすぎるよなぁ…

神の声

というやり取りをSlack上でやっていたら、弊社のCTO前田が助け舟を出してくれました。

前田：「APIでとれるっしょ？」
鈴木：「なるほど！やってみます！（API接続やったことないぞ....）」

私はAPIという言葉とか概念は知ってますが、実際に使ったことはなかったのです。 しかし、今回の案件ではAPIを避けては通れないということはすぐに理解できたので、GASからRedashのAPIを通してデータを取得する方法をググってみました。 いくつか目的に近いものを見つけ、内容を読んでみたら自分でもできるのではないかと思い、さっそく作ってみることにしました。

作ったものの仕様

ユーザーは以下のシートに得意先IDを並べて、集計処理実行ボタンを押します。 得意先名には対象の得意先名が入り、シートのリンクとなるようにします。 f:id:hryk-suzuki:20201021180157p:plain

f:id:hryk-suzuki:20201021153912p:plain

利用するRedashクエリは１つの得意先の指定期間のデータを１レコード／日というデータで取得するものなので、 上記の得意先IDと処理日から１年の期間をパラメータとしてRedashに渡します。 返ってきたCSVデータを配列で処理して、得意先毎に以下のシートが作成されます。 f:id:hryk-suzuki:20201021180758p:plain

プログラム概要

GASからRedashのAPIを使ってデータを取り出す方法を説明すると、以下のようになります。

1. クエリを更新する

1-1. 更新処理のリクエストをRedashに送る

パラメータ付きのURLを投げて、欲しいデータの作成を要求します。

1-2. リクエストの処理状況を監視する

1-1のレスポンスからジョブIDを取り出し、そのジョブの状況をチェックします。
未完了の場合はbに戻り、完了したら次の処理へ進みます。

2. クエリの結果を取得する

レスポンスはそのままではただのテキストデータですので、CSVとして配列に入れます。

以下でこの詳細を説明していきます。

GASでRedashのAPIを使う方法

1-1. 更新処理のリクエストをRedashに送る

UrlFetchApp.fetchでエンドポイントにPOSTします。
ここで行うのは更新処理のリクエストだけで、すぐには完了しませんので、ステータスが完了となるまで状況をチェックする必要があります。

const USER_API_KEY = '×××××××××××××××××××';
const BASE_URL = 'https://＜ホスト名＞/api/queries/＜クエリID＞'

const queryParams = `p_パラメータ=${＜パラメータ＞}&api_key=${USER_API_KEY}`;
const queryUrl = `${BASE_URL}/refresh?${params}`;
const response = UrlFetchApp.fetch(queryUrl, {
  method: 'post',
  muteHttpExceptions: true
});

1-2. リクエストの処理状況を監視する

ジョブステータスのレスポンスを繰り返しチェックし、更新処理のステータスが完了となるまで待つ処理です。
まず、このレスポンスがJSON形式のデータなのでJSON.parse(response.getContentText())でパースします。
パースしたものの「job」パラメータにジョブIDが入っているので取っておきます。
次に、ジョブのステータスを見るエンドポイントにリクエストします。

これが３（SUCCESS）か４（FAILURE）以外だと完了していないので、sleepで少し待ってからもう一度リクエストします。

const jobJson = JSON.parse(response.getContentText());
const completedJob = poll_job(jsonJson.job);

if (completedJob == null) {
  // ここでエラーハンドリング
}

function poll_job(job) {
  let i = 0
  // あらかじめポーリング（処理を待つ処理）の回数を決めておく
  while (i < 600) {
    const url = `${BASE_URL}/jobs/${job.id}?&api_key=${USER_API_KEY}`;
    const response = UrlFetchApp.fetch(url, {
      method: 'get',
      muteHttpExceptions: true
    });
    const json = JSON.parse(response.getContentText());
    // ステータス番号が３（SUCCESS）か４（FAILED）だったら完了とする
    if (json.job.status == 3) {
      return json.job;
    } else if (json.job.status == 4) {
    return null;
    }
    i++;
    Utilities.sleep(5000); // 5000×0.001=5秒スリープ
  }
  return null;
}

2. クエリの結果を取得する

クエリの結果を取得するエンドポイントにリクエストし、帰ってきたテキストをcsvとしてパースします。

const resultUrl = `${BASE_URL}/queries/＜ジョブID＞/results/${completedJob.query_result_id}.csv?api_key=${USER_API_KEY}`;
const resultResponse = UrlFetchApp.fetch(resultUrl, {
  method: 'get',
  muteHttpExceptions: true
});
const csvData = resultResponse.getContentText();
const csvValues = Utilities.parseCsv(csvData); // 元がテキストなのでCSV形式としてparseして配列に格納する

苦労した点

APIの使い方を理解するのに時間がかかった

これは私固有の問題ですが、同じような境遇の方の助けになればと思い書きました。この案件を例に書きます。

APIとは

外部からRedash固有の処理を実行するために、Redashに用意されている「窓口」のようなものです。やりたい処理の種類によって窓口は分かれていて、例えばクエリを実行したいときと、処理状態を確認したいときでは窓口（エンドポイント）が違います。

1. RedashのAPIの作法

Redashそのものの概念を理解していないと難しいのですが、Redashには実行したクエリのデータを一時的に保管してくれる機能があります。
なので必ず最新のデータを取り出したい場合は、更新処理というものを実行する必要があります。

2. GAS側で難しいところ

プログラム概要の「1. クエリを更新する」のロジックが難しかったところで、他の人が書いたコードを読んでもなかなか理解できませんでした。
私は何度も読み込んで理解してから自分なりに書いてみました。
更に、自分でも数カ月後には解らなくなってしまうと思うので、極力シンプルに書き、下のようなコメントで見やすくしました。
GASのエディタが見づらいことや、コーディングに不慣れなのもありますが、関数を使うとコードの実行場所が飛ぶので、処理の終了後はどこに戻るんだっけ？？となってしまうことがあるためです。飛び先と元の記号を同じにして、いくつかの記号を使い分けると見やすくなると思います。

//インポート処理に飛ぶ★★★★★★

//★★★★★★★★★★★★★★★★★★★★redashからデータをCSV形式で取得する処理★★★★★★★★★★★★★★★★★★★

クエリ結果のCSVが大きくてエラーになってしまった

GASでクエリの実行結果をCSVとしてダウンロードする際に、このCSVが大きい場合、タイムアウトエラーとなってしまうことがあります。
今回は１年分のデータをまとめて取得しようとしてエラーになったので、一回で取得する範囲を １年分 → 半年分 → ４ヶ月 と、エラーが発生しなくなるところまで検証しながら減らしました。
再度エラーが発生した時は更に短くして対応しようと考えています。

おわりに

何事も為せば成る！ですね。
初めはAPIなんて使い方解るのか？って不安でしたが、実際にテスト用のクエリを作って、無事GASからAPI経由でデータを取得することができました。
返ってきたカンマ区切りのテキストデータは、GASのCSVパース関数（Utilities.parseCsv）で配列に簡単に入れられましたし、JSON形式のレスポンスの中身を見るにはJSONパース関数（JSON.parse）を使うといいというのも、勉強になりました。

今回のツールでは全部で月に18時間くらいの削減になるそうなのですが、まだまだGAS×Redashには活用シーンがありそうです。Redashに限らず、APIを使えばカバーできる範囲が広がっていきそうな予感がしています。
今後もみんなのメンドクサイをITの力で楽にしていければと思います！