はじめに
この記事は LIFULL Advent Calendar 2022 の8日目の記事です。
今回もGASについて書きます。
紹介する仕組みを作った背景等に関しては、別のところで書く機会があれば触れるとして、
今回は作り方に特化した紹介をします。
今回作ったもの
SQL（※BigQuery限定）の投稿や実行ができる仕組みを作りました。
テキストだけだとうまく伝えられないので、実際どういうものなのかは作り方と並行して説明していきます。
準備するもの
- Googleドライブ
- Googleドキュメント
これだけです。
アウトプットとしてスプレッドシートを使用しますが、
GASで生成するため、準備としては不要です。
Googleドキュメントの中にSQLを記載し、レシピ化し、それをGoogleドライブ上に配置します。
Googleドライブ上に適当なフォルダを作成し、置き場所を用意しておきましょう。
レシピテンプレの作り方
まず、先ほど用意しました置き場所にGoogleドキュメントを新規作成します。
作成したファイルを開いたら、「拡張機能」メニューから「App Script」を選んでGASの編集画面を開きます。
BigQueryライブラリの設定
画面左にある「サービス」の右側にある「＋」アイコンをクリックします。
「サービスを追加」の画面が表示されますので、「BigQuery API」を選択し、「追加」をクリックしたら完了です。
呼び出しメニューの設定
Googleドキュメントを開いたタイミングで発動する「onOpen」メソッドを用意し、
独自のメニューが追加されるようにします。
これによって、GASの画面を開くことなくGASを実行できるようになります。
function onOpen() {
var ui = DocumentApp.getUi(); // Uiクラスを取得する
var menu = ui.createMenu('独自メニュー'); // Uiクラスからメニューを作成する
menu.addItem('SQLを実行', 'runQuery'); // メニューにアイテムを追加する
menu.addToUi(); // メニューをUiクラスに追加する
}
図形を描画してメソッドを紐づける方法もありますが、
Googleドキュメントの文言を書く領域には純粋にSQLだけを書くようにしたかったため、
この方法を採用しました。
SQLを実行し、スプレッドシートへ書き出し
ここからがメインとなる処理です。
データを取得するコードはこちらのを使わせていただきました。
参考サイトではスプレッドシート上で実行するため、そのまま書き出す内容になっていますが、
今回はコードをシンプルに書けるGoogleドキュメントを用いたかった（理由は後述）ため、
動的にスプレッドシートファイルを生成し、結果画面で生成したファイルへのURL導線を貼る方式を取りました。
function runQuery() {
try {
// Googleドキュメントから取得したSQL文をBigQuery APIを用いて実行
const projectId = 'xxx';
const doc = DocumentApp.getActiveDocument();
const query = doc.getBody().getText();
const request = {
query: query,
useLegacySql: false
};
let queryResults = BigQuery.Jobs.query(request, projectId);
const jobId = queryResults.jobReference.jobId;
let sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
let rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
// 取得データが空だった場合
if (!rows) {
showNoDataDialog();
return;
}
// スプレッドシートを生成し、取得したデータを出力
const spreadsheet = SpreadsheetApp.create('BiqQuery Results');
const sheet = spreadsheet.getActiveSheet();
// Append the headers.
const headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
sheet.appendRow(headers);
// Append the results.
var data = new Array(rows.length);
for (let i = 0; i < rows.length; i++) {
const cols = rows[i].f;
data[i] = new Array(cols.length);
for (let j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
}
sheet.getRange(2, 1, rows.length, headers.length).setValues(data);
showSuccessDialog(spreadsheet.getUrl());
}
catch (e) {
showErrorDialog(e);
}
}
「データが取得できた場合」「データが何も返ってこなかった場合」「データ取得中に何かしらの問題が発生した場合」
の３種類の結果画面表示用メソッドを用意しています。
// データ取得に成功した場合に呼び出し
function showSuccessDialog(targetUrl) {
const contents = '<a href="' + targetUrl + '" target="_blank">取得結果を見る</a>'; //html
var htmlOutput = HtmlService.createHtmlOutput(contents) //変数contentsで作成したhtmlを入力
.setWidth(400) //幅250のサイズを設定
.setHeight(100); //高さ300のサイズを設定
DocumentApp.getUi().showModalDialog(htmlOutput, '取得結果');
}
// 返ってきたデータが空だった場合に呼び出し
function showNoDataDialog() {
const contents = "取得されたデータはありませんでした";
var htmlOutput = HtmlService.createHtmlOutput(contents) //変数contentsで作成したhtmlを入力
.setWidth(400)
.setHeight(100);
DocumentApp.getUi().showModalDialog(htmlOutput, '取得結果');
}
// データ取得中に何かしらのエラーが発生した場合に呼び出し
function showErrorDialog(error) {
const contents = "実行中エラーが発生しました。作成したSQL文に誤りがないか、コンソール画面上にてご確認ください。<br><br>"
+ "＜エラー内容＞<br>"
+ error + "<br><br>"
+ "コンソール画面は<a href='https://console.cloud.google.com/bigquery?project=lifull-bq-slots-sharing' target='_blank'>こちら</a>";
var htmlOutput = HtmlService.createHtmlOutput(contents) //変数contentsで作成したhtmlを入力
.setWidth(500)
.setHeight(250);
DocumentApp.getUi().showModalDialog(htmlOutput, 'エラー');
}
これで終了です。記載した内容を保存し、Googleドキュメントファイルを開きなおすと、以下のようなメニューが出てきます。
BigQueryへのデータ取得とスプレッドシートへの記載が完了すると、以下の画面が表示されます。
画面内にある「取得結果を見る」をクリックすると、取得したデータ内容が記載されたスプレッドシートファイルが開きます。
使い方
作成したテンプレを複製し、作成したSQLをGoogleドキュメントの文面に貼り付けるのですが、
テンプレをスプレッドシートではなくGoogleドキュメントにしたのには理由があります。
記載されたSQLで改善すべき箇所をピンポイントで選択してインラインコメントをすることで、
添削としての機能を果たせるようになるためです。
おまけ：SQLの書き方
基本的にはSQLを書くだけなのですが、
変数機能を備えていないため、SQLの中での指定箇所が多いと対応が大変です。
下記のように可変となる値を冒頭に集約させることで、SQL実行時の手間を軽減させることができます。
declare START_DATE, END_DATE DATE;
set START_DATE = DATE('2022-08-01');
set END_DATE = DATE('2022-08-31');
select
*
from
`xxxxx`
where
create_date between START_DATE and END_DATE
最後に
お読みいただいてありがとうございました。
BigQuery上にあるデータのみが対象となりますが、
特別に有償なツールを導入することなく、GoogleAppsの機能を活用するだけで
SQLレシピ集の「共有」「実行」「添削」ができるツールへと昇華しました。
有償でも便利なツールを使う前に、在りものを組み合わせてできるものが無いか考えてみては如何でしょうか。