GoogleスプレッドシートではCtrl+Fで表示される検索窓から単語の検索ができますが
検索にヒットすると画面がスクロールしたりして、地味に使い辛いことがあります。
今回は、GAS (Google Apps Script) を使って、スプレッドシートのデータを検索できる簡単なWeb画面を構築してみたいと思います。
やりたいこと
今回やりたいことはこんな感じです。
このような内容のスプレッドシートをデータソースとします。
アプリを起動すると、取得された上記スプレッドシートのデータが、ブラウザでこのように見えます。
検索フィールドに値を入れて検索することも可能です。
検索フィールドに「B3」を入れて「検索」ボタンを押してみると
検索ワードに該当した部分が、色付けされたうえで表示されます。
スクリプトエディタの起動
GASを記述、動作させるには、スクリプトエディタを使用します。
スクリプトは単体でも作成・起動することができますが、今回はスプレッドシートからデータを取得するということもあり、スプレッドシートに付属するスクリプトという形で作成することにします。
まずはスプレッドシートのメニューバーにある「ツール」をクリックし、「スクリプトエディタ」を起動。
（’※「ツール」に見当たらない場合は、「拡張機能」にあるはず）
するとこのように「無題のプロジェクト」として、デフォルトで生成される関数が表示されます。
関数名は自由に変更可能です。ここにメインとなるスクリプトを記述していきます。
doGetの実装
まずはページ読み込み時に行う動作を記述します。メソッド名はdoGetです。
これはGASでWebアプリケーションを実行したときに最初に実行されるメソッドとなります。ここでは次の3つの処理を行います。
- スプレッドシートに格納されている値をパラメータへ格納
- Webブラウザで表示するhtmlファイルを生成
- 1.のパラメータを2.へセット
上記の処理により、スプレッドシートに既に書き込まれている表データが、初期表示されるWeb画面へ表示されることになります。
スプレッドシートに格納されている値をパラメータへ格納
ではソースコードを説明していきます。まずはスプレッドシートに格納されている値を読み込み、”myData”として格納しておく部分です。
let myData = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
スプレッドシートに値が存在する領域は、全て読み込み対象としています。
Webブラウザで表示するhtmlファイルを生成
Webブラウザで表示するにはhtmlファイルが必要で、そちらの生成処理もdoGetで行います。
htmlのファイル名は「index.html」とすることにします。この場合は文字列”index”を、HtmlServiceのcreateTemplateFromFileというメソッドに指定します。
let template = HtmlService.createTemplateFromFile("index");
htmlのパラメータへ値をセット
スプレッドシートの値をhtmlファイルで表示する為、値の受け渡しを行います。
ここでは、値が格納されている変数を”myData”とします。
template.myData = myData;
最後に、テンプレートの評価を返して終了です。全体のdoGetコードは以下のようになります。
function doGet() {
let myData = SpreadsheetApp.getActiveSheet().getDataRange().getValues(); // スプレッドシートの値を取得し"myData"として格納
let template = HtmlService.createTemplateFromFile("index"); // index.htmlファイルを"template"として生成
template.myData = myData; // "template"にある"myData"パラメータに、取得したスプレッドシートの値"myData"をセット
return template.evaluate();
}
doPostの実装
doPostには、画面上で「検索」ボタンが押下されたときの振る舞いを定義します。
いろいろと細かいことをやっていますが、大まかに以下の処理を行うことにしました。
- 検索文字列によるシート内検索を実行し、ヒットしたセルの情報を取得
- 1.で取得した情報から、ヒットしたレコードの行番号（シート内の第何行目か）を配列化
- 2.の配列に格納されている行に該当する情報をhtmlへセット
上記の処理により、検索にヒットしたレコードのみがWebページに表示されることになります
検索にヒットしたセルの情報を取得
順に見ていきます。検索文字列によるシート内検索は、TextFinderクラスのfindAll()メソッドを使用します。
let ranges = sheet.createTextFinder(searchW).findAll();この部分で、入力された検索文字列を元にシート内検索を行い、該当するセル情報を取得します。
戻り値はRange[]で、該当する全てのセルの情報が格納された配列となります。
ヒットしたレコードの行番号を配列化
先に取得したセルの情報から、レコード番号を格納します。
Range[]にはセルの情報が入っているので、1行に複数のセルがヒットした場合には、その分が重複してしまいます。
なので一旦、行数を配列化しておき、その後重複を排除するというやり方を採ります。
// 検索にヒットしたRangeとレコード行を格納
for (let i = 0; i < ranges.length; i++) {
targetRows.push(ranges[i].getRow());
hitRanges.push([ranges[i].getRow(), ranges[i].getColumn()]);
}
// 1行に複数個ヒットした場合の重複行番号排除
targetRows = targetRows.filter(function(x, i, self) {
return self.indexOf(x) === i;
});
後で該当するセルに色付けしたいので、そのために個々のセルの行と列の情報も別途保持しています。
該当するレコードをhtmlにセット
上記までに取得した情報を、htmlにセットする「myData」パラメータとしてセットします。
途中で（コードに直書きでアレですが）該当するセルへの背景色付与も行っています。
for (let i = 0; i < targetRows.length; i++) {
// 検索にヒットしたレコードの取得
let data = sheet.getRange(targetRows[i], 1, 1, sheet.getLastColumn()).getValues();
// 検索にヒットしたセルに背景色を付与
for (let j = 0; j < hitRanges.length; j++) {
if (hitRanges[j][0] == targetRows[i]) {
data[0][hitRanges[j][1]-1] = '<div style="background-color:#EDF7FF;">' + data[0][hitRanges[j][1]-1] + '</div>';
}
}
myData.push(data[0]);
}
}
// htmlを生成
let template = HtmlService.createTemplateFromFile("index");
template.myData = myData;
return template.evaluate();
コード.gsの全量
まとめとして、コード.gsの全量を載せておきます。
function doGet() {
let myData = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
let template = HtmlService.createTemplateFromFile("index");
template.myData = myData;
return template.evaluate();
}
function doPost(e) {
let searchW = e.parameter.search; // 検索ワードの取得
let sheet = SpreadsheetApp.getActiveSheet();
let myData = []; // 検索にヒットしたデータの格納先配列
if (searchW == null || searchW == '') {
myData = SpreadsheetApp.getActiveSheet().getDataRange().getValues(); // 検索ワードがnullの場合は全件取得
} else {
let ranges = sheet.createTextFinder(searchW).findAll(); // キーワードによる検索を実施
let targetRows = []; // 検索にヒットしたレコード行の格納先
let hitRanges = []; // 検索にヒットしたセルのRangeの格納先
// 検索にヒットしたRangeとレコード行を格納
for (let i = 0; i < ranges.length; i++) {
targetRows.push(ranges[i].getRow());
hitRanges.push([ranges[i].getRow(), ranges[i].getColumn()]);
}
// 1行に複数個ヒットした場合の重複行番号排除
targetRows = targetRows.filter(function(x, i, self) {
return self.indexOf(x) === i;
});
for (let i = 0; i < targetRows.length; i++) {
// 検索にヒットしたレコードの取得
let data = sheet.getRange(targetRows[i], 1, 1, sheet.getLastColumn()).getValues();
// 検索にヒットしたセル値に背景色を付与
for (let j = 0; j < hitRanges.length; j++) {
if (hitRanges[j][0] == targetRows[i]) {
data[0][hitRanges[j][1]-1] = '<div style="background-color:#EDF7FF;">' + data[0][hitRanges[j][1]-1] + '</div>';
}
}
myData.push(data[0]);
}
}
// htmlを生成
let template = HtmlService.createTemplateFromFile("index");
template.myData = myData;
return template.evaluate();
}
htmlファイルの作成
GASの実装は終わったので、今度はWebに表示するhtmlファイルを作成していきます。
GASで設定していたように、ファイル名は”index.html”とします。
「ファイル」から「HTML」をクリック。
「index」と打ち込んでEnterすると、index.htmlができます。
では早速以下にコードを記載します。
全量としてはこのようになります。基本的にはGASから渡された”myData”パラメータを順に読み込み、テーブルとして組み立てるだけのシンプルなものです。
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<form method="post" action="https://script.google.com/macros/s/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/exec">
検索：<input type="text" name="search" />
<input type="submit" value="検索" /><br />
<table border=1>
<? for (var i = 0; i < myData.length; i++) { ?>
<tr>
<? for (var j = 0; j < myData[i].length; j++) { ?>
<td>
<? output._=(myData[i][j] + '<br/>'); ?>
</td>
<? } ?>
</tr>
<? } ?>
</table>
</form>
</body>
</html>
formタグのaction属性に記載するURLは、このアプリ固有のURLになります。
デプロイ時に固有のURLが割り当てられるので、それをコピペして貼り付けておきます。
起動・動作確認
では作成したアプリを動かしてみましょう。
アプリを起動するには、スクリプトエディタの右上辺りにある「デプロイ」ボタンをクリックします。
ここでは特に公開する予定はないので、実行するユーザは「自分」で、アクセスするユーザも「自分のみ」としておきます。
いろいろとアクセス許可を求められた後、こちらの画面が表示されます。
表示されたURLをクリックすると、冒頭で示した画面が立ち上がります。
