GoogleスプレッドシートではCtrl+Fで表示される検索窓から単語の検索ができますが
検索にヒットすると画面がスクロールしたりして、地味に使い辛いことがあります。
今回は、GAS (Google Apps Script) を使って、スプレッドシートのデータを検索できる簡単なWeb画面を構築してみたいと思います。
やりたいこと
今回やりたいことはこんな感じです。
このような内容のスプレッドシートをデータソースとします。
アプリを起動すると、取得された上記スプレッドシートのデータが、ブラウザでこのように見えます。
検索フィールドに値を入れて検索することも可能です。
検索フィールドに「B3」を入れて「検索」ボタンを押してみると
検索ワードに該当した部分が、色付けされたうえで表示されます。
目次
スクリプトエディタの起動
GASを記述、動作させるには、スクリプトエディタを使用します。
スクリプトは単体でも作成・起動することができますが、今回はスプレッドシートからデータを取得するということもあり、スプレッドシートに付属するスクリプトという形で作成することにします。
まずはスプレッドシートのメニューバーにある「ツール」をクリックし、「スクリプトエディタ」を起動。
（’※「ツール」に見当たらない場合は、「拡張機能」にあるはず）
するとこのように「無題のプロジェクト」として、デフォルトで生成される関数が表示されます。
関数名は自由に変更可能です。ここにメインとなるスクリプトを記述していきます。
doGetの実装
まずはページ読み込み時に行う動作を記述します。メソッド名はdoGetです。
これはGASでWebアプリケーションを実行したときに最初に実行されるメソッドとなります。ここでは次の3つの処理を行います。
- スプレッドシートに格納されている値をパラメータへ格納
- Webブラウザで表示するhtmlファイルを生成
- 1.のパラメータを2.へセット
上記の処理により、スプレッドシートに既に書き込まれている表データが、初期表示されるWeb画面へ表示されることになります。
スプレッドシートに格納されている値をパラメータへ格納
ではソースコードを説明していきます。まずはスプレッドシートに格納されている値を読み込み、”myData”として格納しておく部分です。
|
1 |
let myData = SpreadsheetApp.getActiveSheet().getDataRange().getValues(); |
スプレッドシートに値が存在する領域は、全て読み込み対象としています。
Webブラウザで表示するhtmlファイルを生成
Webブラウザで表示するにはhtmlファイルが必要で、そちらの生成処理もdoGetで行います。
htmlのファイル名は「index.html」とすることにします。この場合は文字列”index”を、HtmlServiceのcreateTemplateFromFileというメソッドに指定します。
|
1 |
let template = HtmlService.createTemplateFromFile("index"); |
htmlのパラメータへ値をセット
スプレッドシートの値をhtmlファイルで表示する為、値の受け渡しを行います。
ここでは、値が格納されている変数を”myData”とします。
|
1 |
template.myData = myData; |
最後に、テンプレートの評価を返して終了です。全体のdoGetコードは以下のようになります。
|
1 2 3 4 5 6 7 |
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()メソッドを使用します。
|
1 |
let ranges = sheet.createTextFinder(searchW).findAll(); |
この部分で、入力された検索文字列を元にシート内検索を行い、該当するセル情報を取得します。
戻り値はRange[]で、該当する全てのセルの情報が格納された配列となります。
ヒットしたレコードの行番号を配列化
先に取得したセルの情報から、レコード番号を格納します。
Range[]にはセルの情報が入っているので、1行に複数のセルがヒットした場合には、その分が重複してしまいます。
なので一旦、行数を配列化しておき、その後重複を排除するというやり方を採ります。
|
1 2 3 4 5 6 7 8 9 10 |
// 検索にヒットした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」パラメータとしてセットします。
途中で（コードに直書きでアレですが）該当するセルへの背景色付与も行っています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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の全量を載せておきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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”パラメータを順に読み込み、テーブルとして組み立てるだけのシンプルなものです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<!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をクリックすると、冒頭で示した画面が立ち上がります。
