Google Apps Sctriptで作る備品検索・登録システム
もうだいぶ春めいてきましたね。公園の芝生も柔らかい色になってきて、黒いアスファルトにちらほらと転がっていく花びらもいる。花粉さえなければ、程よく肌寒く日差しの暖かい過ごしやすい良い季節です。
卒業シーズンでもありますが、大学院へ進学するのであんまり実感がわかない…。先輩や友人が卒業していくので、「門出だね！」とは思いますが、所詮それまで。気軽に遊べなくなるんだろうなあとちょっと寂しい気持ちはあります。
さて今回は題目の通り、Google Apps Script(以下、GAS)で作る検索・登録システム。前々から「研究室の備品をどうにか管理したい」「何があるんだかなんもわからん」みたいな状況だったので、春休みであれこれとデータ化とかをしたかったわけです。卒論もあり、私の後回し癖もあり結構終盤での(暫定)完成。実作業時間は1日ないくらいですが、思っていたよりも苦戦したので、どうせまた同じとこで躓くでしょという気持ちもありまとめてみることにしました。
前提
要件
機能
- 1枚のページで登録・検索ができる
- 検索結果はテーブル(表)で示す
- 基本PCで見ることを想定しているが、スマホでも見られる
非機能
- 全データ要求以外のレスポンスは3秒以内
- ローカルに依存せず、クラウド上で完結する
- 外部ライブラリなどを極力使わない
- 研究室の人以外が見れないようにする
環境
あれこれの制約によって自動的に環境が絞られてこんな感じ
- Google Chrome
- Google Apps Script
- SpreadSheet
とっても普通な感じのGAS動作環境です。作業環境はMac Book Pro。
GASってなーに
Googleが提供してるサーバ用意しなくてもWebアプリケーションとかマクロ的な処理が作れる便利なやつ。基本構文はおおよそjavascript。この辺はいろんな人が解説してるので、ざっとだけ。
フロントがHTML・CSS・javascript、サーバサイドがGAS、データベースがSpreadSheet。利点はサーバを用意しなくていいこととGoogle提供のアプリケーションが簡単に使えること、欠点は書き方というか処理の流れに癖があってなんて？？ってなるとこが度々あること。いわゆる業務効率化的なものにめちゃくちゃ良いタイプのやつ。トリガー実行とかあるから定期的に同じ処理をする人とか。
今回は普通にWebアプリケーションを作ってくよ。じゃーいってみよー
データ作り
なんと言っても検索システムにはデータが必要。ということで、研究室の掃除兼備品確認を1週間と少しかけてやりました。その結果データ数は474レコード。お掃除の方は友人のお手伝いって感じだったけど、大変だった。
手作業で取ったデータのカラム数は4。
| 備品名 | これは何ですよという情報。型番書いてたり商品名だったり |
| 使用中の個数 | 消耗品とかで開いてるのとか、個人が占有してるもの |
| 未使用の個数 | 消耗品で開いてないやつ、誰も使ってないやつ |
| 備考 | キーボードの接続方法とか紛失してるものとか |
こんな感じ。これを元に検索システムで使えるデータの構造を考えていきます。
まず検索システムである以上「人ってどうやってもの探す？」ということを考えざるを得ないわけです。例えば、ノートパソコン。「Lenovoの」「HPの」というメーカー名、「Let's note」「LIFEBOOK」などのシリーズ名で呼ばれるし、検索したときに欲しい情報には「OSが何か」「型番は？」「いくつあるの」なんてのもある。消耗品だったら「今何があるのか」はもちろん、「こういう種類のやつって何があるの」というアバウトな検索だってあり得る。工具とかなら「壊れていないか」「どういうものなのか(電源はバッテリー？)」も必要。とか色々考えた結果、カラム数は11になった。
| 名称 | 検索の時とかに使える、用途的な説明 |
| メーカー名 | どこが作ったものか |
| 型番 | メーカーが付与した物品を示すコード。検索で出るものを書く |
| 大分類 | 事務機器・情報機器などの大雑把な分類 |
| 中分類 | 大分類よりも細かな分類。どういう用途のものか |
| 小分類 | 中分類よりもものの特性に基づいた分類。何に使うのか |
| 種別 | 消耗品か、備品か、ケーブルか。絞り込みであると便利 |
| 状態 | 破損、廃棄などものそのものの状態 |
| 詳細 | 備考といってもいい。デバイスの対応OSとか付属品紛失とか |
| 使用中 | 個人が占有している/開封済みの消耗品の個数 |
| 全数 | 使用中の個数も含めた存在個数 |
こんな感じ！最初はこれに所在や学校の管理下にあるかも入れていたのだけど、別に必要でないよね、ということでリストラ。
例としてプリンタの互換インクだと
| 名称 | Canonプリンタインク シアン C |
| メーカー名 | Astere |
| 型番 | 371 XL |
| 大分類 | 事務機器類 |
| 中分類 | 印刷・製本類 |
| 小分類 | 印刷 |
| 種別 | 消耗 |
| 状態 | - |
| 詳細 | 互換インク |
| 使用中 | 1 |
| 全数 | 2 |
これで1レコードになる。で、これを474レコード分作成するとデータの出来上がり。文章にすると一瞬だけどこれだけで数日かかってるので割と大変だった。
ということで、続いてはシステム構成的な部分に入っていきまーす
開発準備
作り終わったデータはExcelの形式(.xlsx)だったのでDriveにアップロード。メニューのファイルからSpreadSheetに変換して保存、拡張機能からGASを選んだら環境構築終了！
必要な部品を書く
サーバサイド
GASでWebアプリケーションを作るときに必ず必要な部品がいくつかあって、ベストプラクティスに則って書いていきます。
普通に公式を参照します。
developers.google.com
doGet関数、HTMLにcssとjavascriptとかを持ってくるためのinclude関数を書きます。下の方に「レスポンシブなUIのためにテンプレートHTMLじゃなくて非同期実行してよね！」と書いてありますが今回の実装では普通にテンプレートHTMLで書きました。なんでかっていうとformタグを使ってPOSTして検索かけて返り値を持ってくる実装にしたんだけど、非同期実行が面倒だったから。ぶん回した方が速かったのもある。
で、結果的にdoGetとincludeとdoPostを書いて必ず必要なものは終了。
//getリクエストに対する処理 function doGet() { let template = HtmlService.createTemplateFromFile("index"); template.myData = []; return template.evaluate(); } //htmlファイル内でincludeができるようにする function include(filename) { return HtmlService.createHtmlOutputFromFile(filename).getContent(); } //postされた時の処理。レスポンスはkey-value型 function doPost(e) { console.log(e.parameter); let search=e.parameter }
フロント
Webサイトに必要な普通のあれこれをやっていきます。ひとまずGAS特有の.htmlと.gs(GASの拡張子)以外作れない仕様に従って、index.html、style.html、script.htmlを作る。
さっき作ったinclude関数を使ってindex.html内にstyle.htmlとscript.htmlを読み込んでいくんだけど、これはいわゆる埋め込みの形をとる。
<!-- index.html --> <!DOCTYPE html> <html> <head> <base target="_top"> <?!= include('style'); ?> </head> <body> <?!= include('script'); ?> </body> </html>
なのでこの場合、style.htmlは
<!-- style.html --> <style> <!-- 内容 --> </style>
でなければ解釈できないし、script.htmlも当然scriptタグから書き始めないといけない。でもまあここまで書いてしまえば後は普通にWebアプリケーションを書いていけばいい。
検索に必要なデータの形式を考える
まあ行き当たりばったりだと当然辛いので、どういう形式で検索データとするか、どうやってレスポンスさせるかを考えていきます。
カラムの情報をデータの状態に着目してまとめるとこんな感じ。
| 名称 | 文字列 | 傾向がない |
| メーカー名 | 文字列 | ある程度偏りがある。空白を許容 |
| 型番 | 文字列。多くの場合英数 | 法則性がない。空白を許容 |
| 大分類 | 文字列 | データが決まっている |
| 中分類 | 文字列 | データが決まっている。大分類に伴って変化 |
| 小分類 | 文字列 | データが決まっている。中分類に伴って変化 |
| 種別 | 文字列 | データが決まっている |
| 状態 | 文字列 | データが決まっている |
| 詳細 | 文字列 | 傾向がない。空白を許容 |
| 使用中 | 数値 | 空白を許容 |
| 全数 | 数値 | 空白を許容 |
とりあえずここで、検索するときのパターンを考えてみます。考えられるのは2つ。
- 明確に探しているものがわかる
- ぼんやりとこんなものだった気がする
この2つのどちらもが検索で見つけられるようにすればいい。
1. は何を探しているか明確にわかっているから全部のカラムが並んでいても迷わない。それに対して2. はぼんやりとしかわからないから並んでいると困る。
逆に、2. はキーワードだけで検索できたら嬉しいけど、1. はキーワード検索だけだと必要のないものも引っかかってくる可能性が高い。
なるほどーー？？つまりキーワード検索と詳細検索を作ればいいんだな！そういうことです。
キーワードの方はいいとして、詳細検索はどうするか。
データの状態から、メーカー名・種別・状態は独立したドロップダウンメニューで、名称と型番はtextboxで部分一致検索すれば充足できそうです。分類は大分類に伴って中分類が、中分類に伴って小分類が変化する、つまりは木構造的にカテゴリデータがあるのでいわゆる連動するタイプのプルダウンの方が親切。
つまりこういうこと
| キーワード | 詳細検索 | |
| 名称 | ○ | 自由入力 |
| メーカー名 | ○ | プルダウン |
| 型番 | ○ | 自由入力 |
| 大分類 | ○ | プルダウン |
| 中分類 | ○ | 連動プルダウン |
| 小分類 | ○ | 連動プルダウン |
| 種別 | - | プルダウン |
| 状態 | - | プルダウン |
| 詳細 | ○ | - |
| 使用中 | - | - |
| 全数 | - | - |
ということでデータ形状が決まったので、これの通りに作っていきます。
結果表示の方法について考える
元々がテーブルつまり表だし、物品の詳細的な情報ページを持たないので普通にhtmlのtableタグを使って表で構成しようと思います。で、そうなるとGASから二次元配列的にデータをもらってきたい。レガシーにhtmlへデータ渡してぶん回すとして、そのデータをどうやって持ってくるか、という問題があります。
今回はシステムの非機能要件としてレスポンスまでのターンアラウンドタイムの上限が3秒。そして試しにデータを全件取得、表示したときの時間は18秒。といって探してたらこんな記事が。
pineplanter.moo.jp
なるほど、Query関数。
support.google.com
どうもSQLライクにかけるっぽいし、SpreadSheetの組み込みなら手元で扱うときはテキストで良い。計算資源と待ち時間的にも良さそうなので使ってみることに。結局早かったんで採用しました。
ざっとサーバサイドの話もすると、
- フロントのHTMLからformのデータをPOST
- JSONでパラメータを取得
- パラメータでQuery関数の条件式を生成
- SpreadSheetのQueryシートのA1にsetValue
- 出力された結果をgetDataRangeでgetValues
- HTMLへ直接渡して、template HTMLの生成
- 回される配列、生成されるtableタグのtd要素
こんな感じの実装。
検索システムの実装
というわけでいよいよたくさん書くターン、とはいっても今回は構造がシンプルなのでそんなにたくさんはないです。
フロントを先に作る
今回はHTMLのformからデータを受け取ってーという処理なので先にフロントをいい感じに作ります。
作りました。
とってもシンプル。虫眼鏡アイコンはGoogle Material Icon、つまりGoogle Web Fontから持ってきてペイってした。
fonts.google.com
で、このページはこんな感じの構成。
それぞれのformの中にnameがtype、valueが識別子になるようにhidden要素が入っていて、GASのdoPostが受け取るパラメータによって処理を変える。
キーワード検索ボックス
HTML5から導入されたのか、前からあって気づかなかったのかわからないけどinputタグのtypeにsearchというのがあった。特徴的な部分としてはいわゆる検索ボックスあるある、「×押したら全部消える」がデフォルト機能っぽい感じになるみたい。
ここのcssは割とどうしようか悩んだ。虫眼鏡アイコンを内側に置くか外側に置くか、みたいな割としょうもないことだけど。参考にした記事はこれ。
qiita.com
とてもわかりやすかった。感謝。
アコーディオンメニュー
そしてHTML5すごい話なんだけど、アコーディオンメニュー(クリックしたらびよーんってでてくるやつ)が要素2つで実装できる。すごい。
<!-- 詳細検索の時のフォーム --> <details> <summary>詳細検索</summary> <form method="post" action="https://script.google.com/a/macros/xxx/s/xxxxx/exec"> <input type="hidden" name="type" value="shousai"> <label>名称<br><input type="text" name="name"></label><br> <!-- 省略 --> <input type="submit" value="検索" class="detsubmit"> </form> </details> <!-- 詳細検索の時のフォーム終了 -->
この、detailsとsummaryを書くだけでアコーディオンメニューができる。面倒なcssでdisplay:hidden;がとかそういう実装が一切いらない。これからも消えないでほしい。
ドロップダウンメニュー
詳細検索のドロップダウンメニューは実装ベースで3種類。
- HTMLのsection要素に全て手打ちしたもの
- GASからSpreadSheetのデータを受け取ってHTMLを生成したもの
- 連動のために連想配列をjavascriptで宣言してjavascriptで生成したもの
1. は特になんというわけでもなく単純なやつ。数が少ないのと、増えても1種とかなのでこの実装。編集も難しくないので、メンテ負担も少ないはず。
2. はSpreadSheetにfilterとuniqueで一意のメーカー名データを取ってきて、GASが取得、javascriptから呼び出したら値をポイってしてくるのでそれを加工するだけのお手軽な感じです。
3. はライブラリの使用制約によって、いわゆる便利ツールの使用が制限されてるのでjsで実装。
参考ページ
blog.ver001.com
木構造的になってるカテゴリのせいで非常に数が多くて割としんどかった。
サーバサイド
実は全く特別なことはやってなくて、特に苦労もしなかったから書くことがないという。
流れだけそれぞれまとめておきます
メーカー名ドロップダウン
- 空白以外の行をFilterで取得し、Unique関数に通して、Sortで文字コード昇順に並べ替え
- GASでgetMakerName関数を作り、getRangeで場所を指定しメーカー名の一覧をgetValueで取得
- javaScriptのwindow.onloadでgoogle.script.runからgetMakerName関数を呼び出し
- javaScriptで自作したプルダウンを作るmaker関数に返り値を渡して実行
- document.getElementByNameでプルダウンの作成先を取得
- データの終端までoptionタグのvalue等に値を設定しながらappendChild
検索結果表示
- フロントのHTMLからformのデータをPOST
- JSONでパラメータを取得
- パラメータでQuery関数の条件式を生成
- SpreadSheetのQueryシートのA1にsetValue
- 出力された結果をgetDataRangeでgetValues
- HTMLへ直接渡して、template HTMLの生成
- 回される配列、生成されるtableタグのtd要素
getPost関数でのQuery関数の条件式生成
Postに渡された送信データのkey：typeがキーワード検索の指定
- 取得したキーワードにtoLowerCaseをかけて、小文字に変換
- 名称・メーカー名・型番・分類・詳細の列にLower関数をかけて、列名 contains 'キーワード' の条件式を作る
- 条件格納用の変数へ文字列結合してA1へsetValueする
- Query関数の結果を取得
Postに渡された送信データのkey：typeが詳細検索の指定
- 取得した各パラメータに値が格納されている時に、対応する列と部分一致か完全一致の条件を作って文字列結合する
- 条件の結合はANDで行う
- 終端が必ずAND演算子になってしまうので最後のカラムの1つ先の列を指定して、列名=''で条件を終了させる
- 条件格納用の変数へ文字列結合してA1へsetValueする
- Query関数の結果を取得
Postに渡された送信データのkey：typeが備品登録の指定
- データ格納用の空配列を宣言して、取得したデータを順にkey指定しながらpush
- getRangeで最終行の次の行をgetLastRow()+1で示し範囲を指定してデータをsetValueする
全て終了後、Queryの結果を引き渡しindex.htmlの生成結果をreturnする。
以上！！マジで特別なこと何もやってない。
おしまい
全件データを取得しない限りちゃんと3秒以内におさまったのでよし。ちょっと良くない実装とかあるかもしれないけど、単純に何も考えたくなーいと放り出しかねないので気にしないことにします。なんとなーくテストはやったけどぜーったいバグあるじゃんね。こわいこわい。
ということで、またそのうちにー
