スプレッドシートとGASを用いた蔵書検索システムの作成 #柏の葉高校
今回は蔵書検索システムを開発しました。入力された単語に該当する本を表示するもので，現時点ではタイトル検索のみですが，必要に応じて著者名での検索など機能を拡充することも可能です。
実行画面
1．GAS (Google Apps Script) とは？
Google Apps Script，通称GASはGoogleが提供しているプログラミング言語です。コーディングする際に使用する言語は，主にHTML, JavaScript,CSSになります。
主な使用用途として，スプレッドシートのデータを利用したシステムの作成が挙げられます。スプレッドシートとは，Googleが提供しているWebベースの表計算ソフトウェアです。複数人での同時編集ができることや，保存の必要がないことが大きなメリットです。
1-1．GASの使い方
① Googleドライブを開く。
② 左上の”新規”からスプレッドシートを作成する。
Googleドライブを開いた画面
③ スプレッドシート上のツールバーから”拡張機能”を選択し，”Apps Script”を選択する。
Apps Scriptの追加 ※コードの詳細は，後述する「コード.gs」にて解説
2．蔵書検索システム
2-1.ファイルの作成
今回の蔵書検索システムでは，htmlファイル3個とgsファイル2個を使用しました。具体的な用途とファイル名の対応は以下の通りです。
htmlファイル
・home.html : サイトの構成を設定するファイル
・home_js.html : JavaScript専用のファイル
・home_css. html : CSS専用のファイル
gsファイル
・コード.gs : コードをGoogleのブラウザ上にサイトとして表示する為のファイル
・home_gs.gs : スプレッドシートの要素を検索する為のファイル
htmlファイルにもコメントで記載してありますが，GASでJSやCSSを使う際には，htmlファイル内で記述する必要があります。具体的にはJSはscriptタグの中に，CSSはstyleタグの中に記述する必要があります。
ここまでの手順を踏めば，コーディングする準備はOKです。
2-2.コードの解説
最初にファイルごとのコードの解説をしていきます。
/*コード.gs*/
function doGet(e) {
if (!e || !e.parameter || !e.parameter.page) {
return HtmlService.createTemplateFromFile('home').evaluate();
}
return HtmlService.createTemplateFromFile(e.parameter.page).evaluate();
}
function getScriptUrl() {
return ScriptApp.getService().getUrl();
}このコードはいわゆる「おまじない」だと思ってください。このままコピーして使ってOKです。4行目の ’home’ は，一番最初に表示したいページのファイル名を.html無しで記述しましょう。
/*home.html*/
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<?!= HtmlService.createHtmlOutputFromFile('home_css').getContent(); ?>
<?!= HtmlService.createHtmlOutputFromFile('home_js').getContent(); ?>
</head>
<body>
<h1>高校図書館</h1>
<? var url = getScriptUrl(); ?>
<div id="topScroll" class="topIcon" onclick="goTop()"></div>
<p>本の検索</p>
<div class="searchArea" id="makeImg">
<input type="text" id="inText" class="searchText" placeholder=" 題名入力">
<div class="searchButton" onclick="search_js()">
<span>検索</span>
</div>
<div id="book_sum"></div>
<div id="button_space"></div>
</div>
<div class="hm_wrap">
<input id="hm_menu" type="checkbox" name="hm_menu" class="hm_menu_check"/>
<label for="hm_menu" class="hm_btn"></label>
<h2 class="hm_title">メニュー</h2>
<nav class="hm_menu_wrap">
<ul class="hm_list">
<li><a href="https://">高校ホームページTopへ</a></li>
<li><a href="https://">高校図書館へ</a></li>
<li><a onclick="window.top.location.href = '<?!= url ?>?page=home';">本の検索Topへ</a></li>
</ul>
</nav>
<div class="hm_menu_close"><label for="hm_menu"></label></div>
</div>
</body>
</html>補足
<?!= HtmlService.createHtmlOutputFromFile('home_css').getContent(); ?> このコードは，このhtmlファイルと別のファイルを結びつけるものです。( )の中に，拡張子込みでファイル名を記述しましょう。ボタンを押した時に動かすJSの関数なども，このコードでhome.htmlに認識させないと動きません。文字の色が変わらず，真っ黒ですがしっかり動くので安心してください。
home_js.html
<script>
function search_js(){
/*入力されたワードを取得する*/
var inText_word = document.getElementById('inText').value;
var val = "button_id";
/*検索結果を表示するスペースを取得する*/
var button_space_element = document.getElementById("button_space");
/*該当数を表示するスペースを取得する*/
var sum_id = document.getElementById("book_sum");
/*検索し直した時に前の検索結果を消す*/
button_space_element.innerHTML = '';
google.script.run.withSuccessHandler(function(result){
for(var j=0; j<result.length; j++){
/*空っぽのボタンを作成する*/
var button = document.createElement('button');
/*ボタンに本のタイトルを付ける*/
button.innerText = result[j];
/*ボタンにidを付ける*/
button.setAttribute("id",val);
/*完成したボタンを検索結果表示スペースに入れる*/
button_space_element.appendChild(button);
}
/*合計冊数を表示する*/
sum_id.innerHTML = j + "冊表示";
}).search(inText_word);
}
var vGoTop = {};
// ページトップボタンの関数
function goTop(){
vGoTop["coef"] = 38; // ←滑らか係数（大きいほど滑らか）
vGoTop["cnt"] = 0;
// --- 現在のスクロール位置取得 -----
var startX = document.body.scrollLeft || document.documentElement.scrollLeft;
var startY = document.body.scrollTop || document.documentElement.scrollTop;
// --- スクロールの単位計算 ---------
var moveSplitCnt = 0;
for(var i = 1; i <= vGoTop["coef"]; i++) {
moveSplitCnt += i * i;
}
vGoTop["unitH"] = startY / ( moveSplitCnt * 2 );
vGoTop["nextX"] = startX;
vGoTop["nextY"] = startY;
// --- スクロール開始 ---------------
goTopLoop();
}
function goTopLoop(){
// ============================================================================
// スクロール実行
// ============================================================================
vGoTop["cnt"]++;
// --- 次のスクロール位置計算 -------
var Coef = 0;
if(vGoTop["cnt"] <= vGoTop["coef"]){
Coef = vGoTop["cnt"];
}else{
Coef = ((vGoTop["coef"] * 2) + 1) - vGoTop["cnt"];
}
vGoTop["nextY"] = vGoTop["nextY"] - Math.round( vGoTop["unitH"] * ( Coef * Coef) );
if((vGoTop["cnt"] >= (vGoTop["coef"] * 2))||(vGoTop["nextY"] <= 0)){
vGoTop["nextY"] = 0;
}
// --- スクロール実行 ---------------
window.scrollTo(vGoTop["nextX"], vGoTop["nextY"]);
// --- 次のスクロールを設定 ---------
if(vGoTop["nextY"] <= 0){
clearTimeout(vGoTop["timer"]); // 終了：タイマクリア
}else{
vGoTop["timer"] = setTimeout("goTopLoop()",10); // 次のループ
}
}
window.addEventListener("scroll", goTopDisp, false);
function goTopDisp(){
// ============================================================================
// 先頭表示時のボタン消去
// ============================================================================
var btn = document.getElementById("topScroll");
var nowY = document.body.scrollTop || document.documentElement.scrollTop;
if(nowY ==0){
btn.style.display = "none";
}else{
btn.style.display = "";
}
}
// メニュー
// jQuery無しでナビゲーションを開閉する関数
function navToggle() {
// 開閉ボタンを取得
var toggleBtn = document.getElementById('nav-toggle');
// 開閉するナビゲーション本体を取得
var navView = document.getElementById('nav-list');
// 開閉ボタンの現在のクラスを取得
var toggleBtnClass = toggleBtn.getAttribute('class');
// 開閉ボタンのクラスで条件分岐
// 1. 開閉ボタンのクラスが「close」だったら
if(toggleBtnClass == 'nav-toggle-button close') {
// 閉じている状態のクラスを削除
toggleBtn.classList.remove('close');
navView.classList.remove('close');
// 開いている状態のクラスを付与
toggleBtn.classList.add('open');
navView.classList.add('open');
}
// 2. 開閉ボタンのクラスが「open」だったら
else {
// 開いている状態のクラスを削除
toggleBtn.classList.remove('open');
navView.classList.remove('open');
// 閉じている状態のクラスを付与
toggleBtn.classList.add('close');
navView.classList.add('close');
}
}
// 指定IDをクリックした際に関数を実行
document.getElementById('nav-toggle').onclick = navToggle;
</script>home_gs.gs
function test(){
var inText_word ="日本";
search(inText_word);
}
function search(inText_word) {
var count;
var result = [];
/*このGASの親スプレッドシートから情報を取得*/
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getActiveSheet();
var range = sheet.getRange(1, 1, sheet.getLastRow()-1,3);//3列
const values = range.getValues();
var textFinder = sheet.createTextFinder(inText_word);
const cells = textFinder.findAll();
/*検索に該当した行数*/
count = cells.length;
/*JSファイルに送る検索結果を準備*/
for (var i = 0; i < count; i++) {
var row = cells[i].getRow();
var col = cells[i].getColumn();
var titleData = values[row-1][0];//cells[i].getDisplayValue();
var authData = values[row-1][1];
result.push([i,row, col, titleData,authData]);
}
/*JSファイルに送る*/
return result;
}
home_jsのコードは１個の大きな関数です。検索ボタンを押した時に，この関数が動きます。それぞれのコードの役割はコメントアウトの通りですが，複雑な部分はコメントアウトを省いています。以下に，細かい解説をするので確認してください。
gsファイルは，「おまじない」の部分でスプレッドシートから本のタイトルを探し，resultという配列に格納します。textFinderの ( ) の中には入力されたワードを取得した変数を入れてください。今回のコードの場合は，home_jsの5行目の変数です。
因みにresultの中には，{タイトル1, タイトル2,}のような形でタイトルが入っています。これがreturnでhome_jsに送られます。
google.script.run.withSuccessHandler(function(){ }).別のgsファイルの関数名();この関数は一見分かりづらいですが，JSの関数の中でgsファイルの関数を使う時に使用します。
１つ前で解説したgsファイルの関数を先に動かし，そこで完成したデータをこの関数に送り込み，JSの処理に活用するという流れです。今回のコードに当てはめると，「別のgsファイルの関数名()」は，１つ前で解説した，gsファイルのsearchという関数になります。( )の中には，スプレッドシートから検索するときに使用する，inText_wordを入れます。
Gsファイルの処理が終わり，resultが送られてきたら，次にfunctionが動きます。このfunctionは，これ自身が関数名なので，別の名前にはしないでください。( )の中にはresultを入れることで，gsファイルからのデータをしっかり受け取りましょう。
CSS
見た目に関しては，様々なサイトのコードを見て良いものを使っていきました。是非アレンジしてみて下さい。
CMAN : 検索ボタン・検索窓など https://webparts.cman.jp/button/a/
2-3．実行方法
右上の「デプロイ」から「新しいデプロイ」を選び進めていくことで，Google上に作成されたサイトとして使うことが出来ます。
3．補足資料 Raspberry Piの起動及びWi-Fiの接続
今回はRaspberry Piの使用まで進めることが出来なかったが，得られた情報や手順を記載しておく。
3-1．Raspberry Piの起動方法
3-1-1．用意する物
キーボード
マウス
Raspberry Pi 4 Computer Model B 4GB RAM
SDカード 32GB ELECOM
HDMIケーブル VX-HD110V-B JVC
Ksy UU318-0530 ラズパイ電源コード
3-1-2．OSの作成
『Raspberry Pi Downloads - Software for the Raspberry Pi』のサイトに行き，ダウンロードページから，「Raspbery Pi Imager」というSDカード作成ツールをダウンロードします。
ダウンロードしたファイルをダブルクリックすると，インストーラが起動するので，「install」をクリックします。終了画面で「Finish」をクリックしてインストール完了です。
そのままソフトウェアを起動し，Raspberry Pi OSをSDカードにインストールして下さい。
「CHOOSE OS」でインストールするOSを選択することが出来るので，一番上にあるRaspberry Pi OSを選択すると，自動で最新版をインストールしてくれます。
次に「CHCHOOSE SD CARD」をクリックして，書き込み先のデバイスを選択します。そして，「WRITE」ボタンをクリックして，確認ダイアログが表示されるので，「YES」をクリックすると書き込みが始まります。
3-1-3．起動
無事にインストールが終了していれば，あとはRaspberry Pi本体に差し込み，起動に必要な機器を接続していれば，勝手に起動します。
※参考サイト
Raspberry Pi OSのインストール – 公式Imager対応 – Indoor Corgi (indoorcorgielec.com)
3-2．Raspberry Piの固定IPアドレスの設定(Wi-Fi)
※ここから先は，Raspberry Piのターミナルコマンドという場所で設定を行っていきます。なお，その他のRaspberry Piの設定・コマンドに関しても主にここで行います。
3-2-1. ターミナルコマンド上で，
Sudo nano /etc/dhcpcd.confとコマンドを入力，実行し，設定ファイルを開きます。
設定ファイルの末尾(一番下)に，以下を追加します。
interface wlan0
static ip_address=[設定したい固定IPアドレス]/24
static routers=[デフォルトゲートウェイのIPアドレス]
static domain_name_servers=[サーバーのDNSIPアドレス]入力例：環境に合わせて数値を変えて下さい。
interface wlan0
static ip_address=192.168.1.93/24
static routers=192.168.1.1
static domain_name_servers=8.8.8.8編集し終えたら，
Ctrl + Oで上書き保存をし，Ctrl + Xでnanoを終了します。
3-2-2. 続いて，wpa_supplicant.conf設定ファイルを編集します。
sudo nano /etc/wpa_supplicant/wpa_supplicant.confとコマンド入力，実行し，設定ファイルを開きます。
設定ファイルの末尾に，以下の文を追記します。
network={
ssid="[SSID名]"
key_mgmt=[認証方式]
psk="[暗号化キー]"
}
入力例
network={
ssid="AIUEO"
key_mgmt=WPA-PSK
psk="abcedf1234567"
}編集し終えたら，Ctrl + Oで上書き保存し，Ctrl + Xでnanoを終了します。
3-2-3. 設定変更の適用のために，無線LANインターフェースを再起動します。
sudo ifconfig wlan0 down
sudo ifconfig wlan0 upとコマンドを入力，実行することで，1.2.で編集した設定が適用されます。
3-2-4. 設定変更の適用の確認をしたい場合，
sudo ifconfigを実行し，IP等が取得できていれば，設定は正しく適用されています。
通信を確認する場合は、
static routers=[デフォルトゲートウェイのIPアドレス]で入力したIPアドレス宛に，ping デフォルトゲートウェイのIPアドレスを実行し，確認してみて下さい。