はじめに
前回の記事でもご紹介しましたが、私の担当プロダクトでは「自分の開発したものは自分で効果測定しましょう」 というルールがあります。
エンジニアの場合、「BigQueryにデータあるからSQL書いて分析してね」で完了ですが、非エンジニアの場合はそうはいきません。
SQLってなに？から始まって、ER図の見方、各種テーブル・カラムの責務の説明など、なかなかに学習コストが高いのが現実です。 また、「定期的に更新してほしい」という要望もよくありますが、エンジニアでもどう実現するかは一度考えなくてはなりません。
今回紹介するのはGoogle App Scriptを利用した方法です。
メリットは、実現がめちゃくちゃ簡単でシンプルなことです。
※なんだGASかよ、と思う気持ちはわかりますがここにコストかけるほうがナンセンスです
SpreadSheetの準備
新規でスプレッドシートを生成したら、下記のようなシート構成にします。
- settings
- output_sheet_1
- 出力先1
- output_sheet_2
- 出力先2
GASの準備
APIの追加
新規でGASファイルを生成したら、サービスにBigQuery APIを追加します。
ソースコードの貼り付け
下記をソースコードに貼り付け。SHEET_IDは前述したスプレッドシートのものを指定してください。
function bigQuery() {
const spreadsheet = SpreadsheetApp.openById("SHEET_ID");
const querySheet = spreadsheet.getSheetByName("settings");
querySheet.getDataRange().getValues().map((row, index) => {
const [projectId, query, output] = row;
if (projectId === "projectId") {
return
}
console.log(output);
const result = BigQuery.Jobs.query(
{
useLegacySql: false,
timeoutMs: "1000000",
location: "asia-northeast1",
query: query,
},
projectId
);
const rows = result.rows.map(row => {
return row.f.map(cell => cell.v)
})
const resultSheet = spreadsheet.getSheetByName(output)
// header を退避 > シートをリセット > header追加
const header = fetchHeader(resultSheet)
resultSheet.clearContents()
resultSheet.getRange(1, 1, 1, header.length).setValues([header])
resultSheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows)
// 実行ログ
querySheet.getRange(1 + index, row.length,1,1).setValue(new Date().toLocaleString('ja-JP'))
});
}
function fetchHeader(sheet) {
var last_col = sheet.getLastColumn();
return sheet.getRange(1, 1, 1, last_col).getValues().find(value => true);
}
ポイント
- BigQuery.Jobs.queryのuseLegacySql: falseに設定する
- これをやらないとSQLが実行されないです
- BigQuery.Jobs.queryのtimeoutMs: "1000000"
- 重めのSQL実行する場合は、タイムアウトしてしまうので値を大きめにしておいたほうが良いです。
- ちなみにデフォルトは10秒です。
- https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
- 出力先シートへの更新は下記手順で行っていて、何度実行しても破損しない形にしてあります。
- ヘッダーの退避 > シートのリセット > ヘッダーの復元 > 値の更新
定期実行設定
これはお好みで。
利用してみての所感
準備が楽ですね。とにかく。必要な権限設定もスプレッドシートに振っておけば良いので考えることが少ないです。
あと元がスプレッドシートなので、SQLの一部を値参照するようにしたら、「種別ごとのデータの定期取得」なんかもすぐ用意できます。
是非参考になさってください。
