最近、実務でスプレッドシートをデータベースとしたGASのWebアプリを作成しました。
DBを更新するクエリ生成ツールです。
クエリ生成であれば、スプレッドシート上でも数式を使って可能ですが、Webアプリにすることで以下の利点があります。
- スプレッドシートを直接操作しないので、意図しないスプレッドシートの更新を防ぐ。
- 入力チェックや複雑なクエリでも、スプレッドシート上より、Webアプリのjavascriptの方が柔軟に対応できる。
ここでは、シンプルに要点を抜粋した、クエリ生成ツールの作成の仕方をご紹介します。
作成するツール
顧客テーブルのデータを更新するクエリを生成します。
顧客テーブルには、id、name、ageのカラムがあり、id以外のデータを更新します。
コードを見てもイメージが湧かないと思いますので、先に、作成したものをお見せします。
スプレッドシート顧客テーブルです。スプレッドシート上に行を追加すれば、操作対象を増やすことができます。
セレクトボックスで対象の顧客を選択し、変更したいカラムを入力して、ボタンを押下すると、UPDATE文が生成されます。
セレクトボックスを開いた状態
クエリを生成した状態
作成方法
Apps Scriptを開いて、プロジェクトを作成
拡張機能 > Apps Script を選択します。
Apps Scriptのプロジェクト画面が表示されます。
画像は、すでに実装済のもので、ファイルに実装したファイル表示されています。（cssやjsでも、拡張子がhtmlになっていることに注意）
作成するファイルについて個別に説明します。
実装するファイルの説明
gas（コード.gs）
Webアプリ起動時に実行されるファイルです。
const COLUMN_COUNT = 3;
const START_ROW = 3;
const START_COLUMN = 2;
// HTMLを表示
function doGet() {
return HtmlService.createTemplateFromFile('index').evaluate();
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
// スプレッドシートのテーブルデータを二次元配列にして返す
function getList() {
const sh = SpreadsheetApp.getActiveSheet();
const lastRow = sh.getRange(START_ROW, START_COLUMN).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
return sh.getRange(START_ROW, START_COLUMN, lastRow - 2, COLUMN_COUNT).getValues();
}- doGet
HTMLを呼び出す関数です。Webアプリを表示するのに必要な関数です。
- include
HTML上で、別ファイルに記述した、
cssやjsファイルを展開する、ユーティリティ関数です。複数ファイルの読み込みを簡潔に書けるように実装したもので、必須な関数ではありません。
- getList
スプレッドシート上のテーブルデータを、二次元配列にして返します。
index.html
<!DOCTYPE html>
<html>
<head>
<?!= include('css'); ?>
<script>
const data = JSON.parse(<?= JSON.stringify(getList()) ?>);
</script>
<?!= include('js'); ?>
</head>
<body>
<main>
<select id="list">
<option value="">-- 選択してください --</option>
</select>
<div class="form">
<div>
name:
<input id="name" />
</div>
<div>
age:
<input type="number" id="age" />
</div>
</div>
<button id="execute">クエリ生成</button>
<textarea id="output"></textarea>
</main>
</body>
</html>-
cssとjsを読み込むgasで定義したincludeを使用して読み込みます。 -
javascriptの変数に、テーブルのデータを保存する。gasで定義したgetListを実行して、gas側で、一旦文字列に変換して、javascript側で配列に変換し直しています。
css.html
<style>
main {
display: flex;
flex-direction: column;
gap: 12px;
align-items: flex-start;
}
.form {
display: flex;
align-items: center;
gap: 12px;
}
#name {
width: 100px;
}
#age {
width: 50px;
}
#output {
width: 600px;
height: 100px;
}
</style>通常のcssファイルと同じですが、styleタグで囲います。
特別gas固有の実装はありませんので、細かい説明は割愛します。
js.html
<script>
window.onload = () => {
const list = document.getElementById('list');
const name = document.getElementById('name');
const age = document.getElementById('age');
const button = document.getElementById('execute');
const output = document.getElementById('output');
// selectの中身を表示する
data.forEach(row => {
const option = document.createElement('option');
option.value = row[0];
option.textContent = `${row[0]}: ${row[1]} (${row[2]})`;
list.appendChild(option);
});
// クエリ生成ボタン押下
button.addEventListener('click', () => {
// 入力チェック
if (!list.value || (!name.value && !age.value)) {
return;
}
// クエリ生成
const updates = [];
if (name.value) {
updates.push(`\`name\` = '${name.value}'`);
}
if (age.value) {
updates.push(`\`age\` = ${age.value}`);
}
const sql = `update \`customer\` set ${updates.join(', ')} where \`id\` = ${list.value};`;
// 生成したクエリを表示
output.value = sql;
});
};
</script>通常のjavascriptファイルと同じですが、scriptタグで囲います。
スプレッドシートのデータを元に、セレクトボックスの中身を生成して、クエリを生成する実装です。
これで必要な実装が揃いました
Webアプリをデプロイする
gasプロジェクトの、右上のメニューから、新しいデプロイを選択。
新しいデプロイモーダルが開きますので、デプロイボタンを押下します。
WebアプリのURLが生成されます。
以上、GASとスプレッドシートでWebアプリ作成と、デプロイ方法でした。
ご紹介したのはシンプルな実装ですが、ある程度gasの知識は必要になりますが、通常のWebアプリと同じ感覚で実装することができます。
Googleの認証が利用できますので、開発現場で、必要なメンバーのみに公開するツール類の作成に非常に有用だと思います。