【GAS】Google Apps Script 活用事例 簡易スタッフ検索システムを作ちゃった話。
年をとるにしたがって、増えるアレ、ソレ。
スタッフが増えると「えーと、誰だっけ？ 新宿のあの人、名前は分かるんだけどIDが分からない。勤怠管理システム見れば分かるかも。」スプレッドシートから移動して.....
なんて事を繰り返していないでしょうか？スプレッドシートから他のサイトに移動せず、しかも特定のシートへ移動しなくても、スタッフの名前を調べられたらいいのになと思い、簡易スタッフ検索システムを作りました。
2020/11/07 ソースコードのリライトしました。
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('追加メニュー')
.addItem('スタッフ検索', 'setStaffName')
.addToUi();
}
function setStaffName(){
//UIを変化させるための処理
const ui = SpreadsheetApp.getUi();
const response = ui.prompt(
'スタッフ検索',
'名前を部分一致で検索可能です。',
ui.ButtonSet.OK
);
const inputName = response.getResponseText();
switch (response.getSelectedButton()) {
case ui.Button.OK:
console.log('%s と入力され、OKが押されました。',inputName);
const html = getStaffInformation_(inputName);
ui.showModelessDialog(html, 'スタッフ名検索');
break;
case ui.Button.CLOSE:
console.log('閉じるボタンが押されました。');
}//switch
}//end
function getStaffInformation_(inputName) {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('従業員名簿');
const values = sheet.getDataRange().getDisplayValues();
values.shift();
//console.log(values);
let showMessage = '';
for (let i = 0; i < values.length; i++){
//スタッフIDが空白だった場合はスキップ
if(!values[i][0]) continue
const information = {
id: values[i][0],
name: values[i][1],
department: values[i][2],
startDate: values[i][3],
}
if(information.name.includes(inputName) === true){
showMessage += '<p>ID : ' + information.id + '</p>';
showMessage += '<p>名前 ： ' + information.name + '</p>';
showMessage += '<p>部署 ： ' + information.department + '</p>';
showMessage += '<p>入社 ： ' + information.startDate + '</p>';
}
}//for文
console.log(showMessage);
const html = HtmlService.createHtmlOutput(showMessage)
.setWidth(450)
.setHeight(300);
return html;
}//endUIからスクリプトを実行する。
表示もソースコードも、かなりシンプルになりました。
ポイントは、HtmlService.createHtmlOutput(showMessage) の部分です。このエントリーを書いた当初は、HtmlService.createTemplateFromFile('dialog') としていました。
htmlを直接書くか、GASのプロジェクト内に、htmlファイルを別途用意して読み込ませるかという違いです。過去記事では、dialog.htmlをテンプレートとして読み込んでいました。
後者は、前者と比べて圧倒的に手間が掛かります。UIをスタイルシート等で細かく指定したい時にオススメです。特にこだわりがない方は前者で良いと思います。
サンプル公開しています。
サンプルでは、閲覧権限しかありませんので、スクリプトの実行は出来ません。ですので「いいな」と思った方は、ファイル、コピーを作成してお使いください。
過去のエントリーでは、表示はこんな感じでした。
配列の要素に、改行を入れているものの、表示領域に、文字列ではなく、1次元配列を貼り付けていたため、カンマが表示されたままになっています。当時はカンマの消し方がどうしてもわからず、そのままにしていました。
エントリーを書いた当初のロジック
1. 名前を入力可能なウインドウを表示
2. 入力された値をスタッフ検索シートのB2セルに書き込む
3. 関数が埋め込まれたセルの値が変化する
4. B列全体をGASで読み取る
5. 読み取ったスタッフ情報を表示する
といった感じです。テンプレートとして読み込んだ、htmlファイル側に存在するJavaScriptの処理と、GAS側の処理が混在しており、ちょっと複雑でした。
当初、このエントリーを書いた頃は、全ての処理を、GASで行うだけの技量がなかったので、一部を中途半端に関数で行っていました。今回のリライトしたコードでは、従業員名簿のシートだけで、全てを完結しています。
実は、関数を埋めても、関数の出力結果がすぐに反映されない.....。
sheet.getRange('B2').setValue('紅莉栖');
//flushで、強制的に関数の結果を反映させる。
SpreadsheetApp.flush();
//以後の処理では、関数の結果反映後の値を取得できる。関数の出力結果の反映よりもGASの方が処理速度が速いようで、GASで取得する値が、関数の結果反映前の値という事がしばしば発生します。そこで、強制的に関数の出力結果を最新にする。SpreadsheetApp.flush(); を使います。
SpreadsheetApp.flush()
引用： Google翻訳
Applies all pending Spreadsheet changes.
Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.
保留中のすべてのスプレッドシートの変更を適用します。
Range.getValueを複数回呼び出す場合など、パフォーマンスを向上させるために、スプレッドシート操作がバンドルされることがあります。ただし、スクリプトの実行中にユーザーデータを表示する場合など、保留中のすべての変更がすぐに行われるようにしたい場合があります。
B2セルに入力、B3セルにVLOOKUP関数で、曖昧検索が可能になるようにしています。そして、IDは、INDEX関数で、出力しています。
VLOOKUP関数
=IFERROR(VLOOKUP("*"&$B$2&"*",'従業員名簿'!$B$2:$D,1,0),"")図では、橋田と入力されたら、橋田至と、フルネームが返されます。
INDEX関数
=IFERROR(INDEX('従業員名簿'!$A$2:$D,MATCH($B$3,'従業員名簿'!$B$2:$B,0),1),"")こちらは、名前の一致からIDを探すためにINDEX関数を使っています。
スタッフ検索のシートでB3（橋田至）を検索値として従業員名簿という別シートのB2:B（薄い青で塗りつぶした部分）のどこにあるかを割り出し、従業員名簿の1列目に書いてある内容を取得.....やっぱ、説明するのも難しい。
本来であれば、ラボメンナンバー002は、まゆしぃーだったな.....。
HTML側の記述
GASをバリバリ書いた1年が経過した今でも、結構難しい事をやっていたんだなーと、理解するまで時間がかかったのと、別の所で、使うかもしれないので残して後と思います。
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<?!= HtmlService.createHtmlOutputFromFile('styleSheet').getContent(); ?>
<!--高さや横幅は、コード.gsの.setWidth(300).setHeight(450); で定義している -->
</head>
<body>
<div class ="innner_contents">
<p>値が更新されました。スタッフ検索のシートをご確認ください。</p>
<textarea id="serchStaff"></textarea>
</div>
</body>
<script>
window.onload = function() {
google.script.run
.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.staffInformation(); //gsファイルの関数にアクセス ここ本当に重要!!
}
function onSuccess(result) {
document.getElementById("serchStaff").value = result;
}
function onFailure(e) {
document.getElementById("serchStaff").value = "Error";
}
</script>
</html>ポイントは、google.script.runの部分、ここでGASのコードを起動します。起動して、静的な骨組みであるHTMLファイルに変化を加えます。今回は、staffInformationというfunction名です。
GAS側の処理 staffInformationの解説
function staffInformation (){
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('スタッフ検索');
console.log(values);
let showMessage = '';
for (var i = 2; i < values.length; i++){
const personalInfo = values[i][1];
if(!personalInfo)continue;
showMessage += personalInfo +'\n';
}//for文
return showMessage;
}values[i][1]は、B列のVLOOKUP関数やINDEX関数が組み込まれた列です。この列をまるっと読み取って表示するみたいな感じです。
for文が2から始まるのは、0は見出し、1は曖昧検索の入力値で不要だからです。
document.getElementById("serchStaff").value = result;<textarea id="serchStaff"></textarea>とgetElementById("serchStaff")の部分が一致しないと、エラーになってしまいます。そして最後にstylesheetは、超シンプル。たったのこれだけ。#serchStaffはtextareaタグの事です。
<link href="https://fonts.googleapis.com/css?family=Noto+Sans+JP&display=swap" rel="stylesheet">
<style> #serchStaff {
overflow-x : hidden;
width: 100%;
height: 200px;
font-size: 20px;
}
</style><base target="_top">
<?!= HtmlService.createHtmlOutputFromFile('styleSheet').getContent(); ?>
<!--高さや横幅は、コード.gsの.setWidth(300).setHeight(450); で定義している -->ファイル名がstyleSheet 大文字になっています。もしかしたら、まるっとコピペした時にエラーが出るかもしれないので、気をつけてください。
似たような事がSlackのSlash Commandsで出来ます。
この記事が気に入ったらサポートをしてみませんか？