Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

僕らの会社では、非エンジニアでも簡単なプログラムを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_job2.の更新が完了するまで待機する関数)
  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の最新の結果を取得できます。

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

Iwark
ScovilleというスタートアップのCTOをしています。 海外エンジニア比率が8割のグローバルなチームです。日本のスタートアップでは珍しいですし、面白い環境だと思っています。 純粋で技術に一直線な人が集まっているので、英語に苦手意識がない方は是非、一緒に働きましょう。 Facebookは一言添えていただけると承認しやすいです。 Twitterは @iwark02 です。
https://sc0ville.com
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away