Help us understand the problem. What is going on with this article?

BigQueryとスプレッドシートとGASと。

More than 5 years have passed since last update.

BigQueryのフロントエンド

BigQueryを使っていてフロントエンドのアプリケーションは何を使っているのか良く聞かれる。もちろん、BigQueryのwebインターフェイスでクエリを作って、CSVでダウンロードとかも良いと思うのですが、やっぱりちょっと違うなぁと。

どんなものがあるのでしょうか?

BigQueryの公式にはこんなものがありました。
https://cloud.google.com/bigquery/third-party-tools?hl=ja

Tableau

Googleの中の人も使ってるぐらい良いものです。
私自身も何度か使っているのですが、やっぱり良いです。ただし、複雑なことしたいなぁと思うととんでもなく遅くなったり。
後ほど書きますけど、Viewなんかで回避すれば良いし、ローカル(もしくはサーバ)にキャッシュデータを持っておけばかなり使えます。便利だし、可視化も良い感じ。

BIME

こちらもなかなか良いです。クラウドサービスでEC2上で動いています。
Salesforceなんかとも連動するようで、うちの会社でも検討しているところです。

あ、あ、あの〜題目と違いますけど?

で、本題。
正直言って、この手のツールってお値段がかなりのものです。
一人、もしくは少数で使うのには良いのですが、みんなで使うとなると結構な金額になります。まぁ、使い方なんだと思うんですけどね。じゃ、どうする?

スプレッドシートがあるじゃないか!(笑)

もちろんスプレッドシート単独だと無理なわけで。ここでGAS(GoogleAppsScript)の登場です。
やってみましょう!

GASでBigQueryAPIが使えるように。

まずはスプレッドシートを開いて『ツール』→『スクリプトエディタ』を選択してGASの画面にします。
spreadsheet_001.jpg

GASの編集画面が立ち上がったら『リソース』→『Googleの拡張サービス』を選びます。
spreadsheet_002.jpg

各APIを有効にする画面になるので、BigQueryAPIを有効にしてやってください。
ただし、これだけじゃありません!この画像にもありますが、『Googleデベロッパーコンソール』でも有効にする必要があります!(つーか、これ面倒。1回でいいじゃん!といつも思う)
spreadsheet_003.jpg

と、いうことでデベロッパーコンソールでもAPIを許可します。
spreadsheet_004.jpg
これで使えるようになりました。

ではGASをぶち込んでやりましょう!

今回のスクリプトはこんな感じです。
①入力するシート名『master』、結果を出力するシート名『result』としました。
 (あらかじめこの2つのシートをスプレッドシートに作っておいてください)
②masterシートのC2セルにSQLを入力するようにしました。
③出力結果の開始位置を指定できます。
 ・masterシートのC3にresultシートの何行目から出力するか指定しています。
 ・masterシートのC4にresultシートの何列目から出力するか指定しています。
④スプレッドシートのメニューバーにBigQuery→実行というメニューを追加しています。
 spreadsheet_005.jpg

なんでこうやったかと言うと、出力結果の列名などを自由に決めることが出来るので、汎用性があるかなぁ?と思った次第です。実際にこれ使ってます。(笑)

サンプルスクリプトです。

bigquery.gs
//自分のProjectNumberを入れてやってください。
var projectNumber = '*******';
var ss = SpreadsheetApp.getActiveSpreadsheet();

//SQLとか、出力セルを入力するシート
var master_sheet = ss.getSheetByName('master');

//SQLの結果を出力するシート
var result_sheet = ss.getSheetByName('result');

function onOpen() {
  var menus = [{name:'実行',functionName:'onRunQuery'}];
  ss.addMenu('BigQuery',menus);
}

function onRunQuery() {

  var sql = master_sheet.getRange('C2').getValue();
  var start_row = master_sheet.getRange('C3').getValue();
  var start_column = master_sheet.getRange('C4').getValue();

  var del_col = result_sheet.getLastColumn();
  var del_row = result_sheet.getLastRow();
  result_sheet.getRange(start_row,start_column,del_row,del_col).clear();
  result_sheet.getRange(start_row,start_column,del_row,del_col).clearFormat();
  result_sheet.getRange(start_row,start_column,del_row,del_col).clearDataValidations();


  var query_results;
  var resource = {
    query : sql,
    timeoutMs: 1000000
  };

  try {
    query_results = BigQuery.Jobs.query(resource, projectNumber);
  }
  catch (err) {
    Logger.log(err);
    Browser.msgBox(err);
    return;
  }
  while (query_results.getJobComplete() == false) {
    try {
      query_results = BigQuery.Jobs.getQueryResults(projectNumber, queryResults.getJobReference().getJobId());
      if (query_results.getJobComplete() == false) {
        Utilities.sleep(3000);
      }
    }
    catch (err) {
      Logger.log(err);
      Browser.msgBox(err);
      return;
    }
  }

  var resultCount = query_results.getTotalRows();
  var resultSchema = query_results.getSchema();

  var resultValues = new Array(resultCount);
  var tableRows = query_results.getRows();

  for (var i = 0; i < tableRows.length; i++) {
    var cols = tableRows[i].getF();
    resultValues[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      resultValues[i][j] = cols[j].getV();
    }
  }
  result_sheet.getRange(start_row,start_column,resultCount,tableRows[0].getF().length).setValues(resultValues).setBorder(true,true,true,true,true,true);
}

あとはこれを保存してやって、スプレッドシートをリロードしてやってください。メニューに追加されていると思います。

どーでしたか?

ごめんなさい。結果画面がなくて。
でも、ここまでくればもう結果画面が出てるかと。(笑)
私の場合はこのスクリプトをライブラリ化しています。わざわざGASにいちいち書くの面倒ですからね。
そのライブラリを仕込んだスプレッドシートにSQLを書いて、現場に渡しています。そうすれば現場部門はメニューの『BigQuery』→『実行』とやるだけでレポートが出てくるわけで。
もしくは、SQLを好きに書きたい現場はこいつにぶち込んだりしています。(笑)

ここでいくつか注意点。
①SQLはあまり複雑にしない。
文字数制限があるようです。
ですから、BigQueryのwebコンソールである程度作って、Viewにすると良いと思います。
②描画はもっと速くできる!
すいません、実はもっと描画を速くする方法があるんだけど、スクリプト忘れちゃった。
(てへぺろ)

と、いうことで・・・

どっちかって言うとGASじゃね?と言われそうですが、私はこれでBigQueryを色々使ってます。
現場にもこれで使ってもらっています。現場の人間ってExcel使いの人多いですからこういうのがとっつきやすいみたいですね。
もし、わかんないこととかあれば是非コメントで書いてみてください!

次回はこれをバッチで処理してメールで定期的にレポートを出すを書いてみます。
じゃ、ばいなら。

satoru_mag
社内なんでも屋さん。 BigQueryとGoogleAnalytics360が大好物。 最近はがんばってpython勉強中。 GoogleDevelopersExpert(GCP)
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