GASでredashの最新の結果を取得してスプレッドシートに反映する

僕らの会社では、非エンジニアでも簡単なプログラムをGAS（Google App Script）で書いたり、SQLを書いてデータ分析をしたり、業務効率化をしたりしています。3ヶ月前にアルバイトから社員になった女の子が普通にゴリゴリ書いてたりします。そもそも難しいという偏見さえ持たなければ、そんなに難しくないんですよね。 なんならGASなんか別に僕も普段そんなに書くわけでもないし、僕より得意な人も普通にいるのでは

さておき、彼ら彼女らが良くすることとして、RedashでSQLを書いて、その結果をjsonやCSVとしてGoogleスプレッドシートへインポートするということがあります。

ただ、redashはクエリの結果をキャッシュするので、例えばIMPORTDATAを使ったような単純な方法だと最新の結果を取得できなかったり、SQLの中にパラメータが組み込まれていると正しい結果を取得できなかったりします。

そこで今回は、redash上で書いた次のような非常にシンプルなSQLの最新の結果を、GASを使ってGoogleスプレッドシートへインポートする方法を書こうと思います。

今回例として扱う、とてもシンプルなSQL:

SELECT posts.title FROM posts WHERE posts.user_id = {{ user_id }};

ここでは、redash上での上記SQLのクエリIDは1とします。
(URLのhttps://hogehoge.com/queries/<クエリID>/source?p_user_id=1)の<クエリID>の部分）

あと今回はcsvではなく、json形式でリクエストを実行していきます。

前提知識

• 上記のような簡単なSQLの知識
• Redashとは何ぞ、を知っている（一言で言えば、ブラウザ上でSQLを書いて誰でも簡単にデータ分析・可視化をできるツール）
  • Redash上でパラメータを使う方法を知っている（上記SQLにもある{{ user_id }}のように書くだけなんですが）
• GAS（Google App Script）の簡単な知識（知らない人はググってください）
• JSONとは何ぞ、の知識（同じくググってもらえると…CSVと同じく、一種のファイル形式です）

処理の流れ

実際に準備をしながら、処理の流れを説明していきます。
スプレッドシートで、ツール > スクリプトエディタと進んで、GASのエディタを開きます。
このスプレッドシートで特にGASを何も変更していなければ、こんなコードが書いてあるはずです。

function myFunction() {
  
}

この関数は残しておいて、これから下記の4つの関数を作っていきます。

1. fetch_latest_query_result （最新のSQLの結果を取得する関数）
2. refresh （SQLの結果を更新するようにredashにリクエストする関数）
3. poll_job （2.の更新が完了するまで待機する関数）
4. fetch_json (リクエストを実行してjsonを取得する関数)

処理の流れとしては、 まず、2. refreshを実行してSQLの結果を更新するようredashに伝えます。
ただ、結果が更新されるまでには（SQLの実行時間にもよりますが）ある程度時間がかかります。
なので、更新の処理が終了するまで、3. poll_job関数によって待ちます。
そしてそれらを実行して最新のクエリの結果を取得するのが1. fetch_latest_query_result関数になります。

GASのコード

最終的には、次のようなプログラムになります。

var REDASH_HOST="https://hogehoge.com"; // RedashのURL
var USER_API_KEY="XXXXXXX"; // Redashの設定画面で取得できる、ユーザー用APIキー（クエリ別のAPIキーとは異なるので注意）
var WAIT_TIMEOUT=60; // SQLの結果が更新されるまで待機時間のタイムアウト（秒）

function myFunction() {
  // Redashのクエリ（ID=1）の最新の結果（user_id=1）を取得
  var results = fetch_latest_query_result(1, "?p_user_id=1");
  
  // 「シート1」という名前のシートを取得
  var sheet = SpreadsheetApp.getActive().getSheetByName('シート1');
  sheet.getRange('A1').setValue("ユーザーの投稿タイトル");

  // A2以降のセルに、取得したSQLの結果（posts.title）を記入
  for(var i = 0; i < results.length; i++) {
    sheet.getRange(i+2, 1).setValue(results[i].title);
  }
}

// クエリの最新の結果を取得
function fetch_latest_query_result(redash_query_id, params) {
  var job = poll_job(refresh(redash_query_id, params));
  var url = REDASH_HOST + "/api/queries/" + redash_query_id + "/results/" + job.query_result_id + ".json";
  var json = fetch_json('get', url);
  return json["query_result"]["data"]["rows"];
}

// SQLの結果を更新するようにredashにリクエスト
function refresh(redash_query_id, params) {
  var url = REDASH_HOST + "/api/queries/" + redash_query_id + "/refresh" + params;
  var json = fetch_json('post', url);
  return json.job;
}

// SQLの結果が更新されるまで待機
function poll_job(job) {
  var i = 0
  while(i < WAIT_TIMEOUT) {
    var url = REDASH_HOST + "/api/jobs/" + job.id;
    var json = fetch_json('get', url);
    if(json.job.status == 3 || json.job.status == 4) {
      return json.job;
    }
    i++;
    Utilities.sleep(1000);
  }
  return job;
}

// JSONを取得
function fetch_json(method, url) {
  var response = UrlFetchApp.fetch(url, {
    'method': method,
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'Key ' + USER_API_KEY,
    },
  });
  return JSON.parse(response.getContentText());
}

これでmyFunction関数を実行することで、redashで記述したパラメータ付きのSQLの最新の結果を取得できます。

だいぶ説明端折ってしまったので、また暇なときに更新します。（多分）