スプレッドシートとGASを用いた蔵書検索システムの作成 #柏の葉高校
見出し画像

スプレッドシートとGASを用いた蔵書検索システムの作成 #柏の葉高校

千葉県立柏の葉高等学校 情報理数科

今回は蔵書検索システムを開発しました。入力された単語に該当する本を表示するもので,現時点ではタイトル検索のみですが,必要に応じて著者名での検索など機能を拡充することも可能です。

図1

実行画面

1.GAS (Google Apps Script) とは?

Google Apps Script,通称GASはGoogleが提供しているプログラミング言語です。コーディングする際に使用する言語は,主にHTML, JavaScript,CSSになります。
主な使用用途として,スプレッドシートのデータを利用したシステムの作成が挙げられます。スプレッドシートとは,Googleが提供しているWebベースの表計算ソフトウェアです。複数人での同時編集ができることや,保存の必要がないことが大きなメリットです。 

1-1.GASの使い方

① Googleドライブを開く。
② 左上の”新規”からスプレッドシートを作成する。

図1

Googleドライブを開いた画面

③ スプレッドシート上のツールバーから”拡張機能”を選択し,”Apps Script”を選択する。

図1

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) {
   Logger.log( Utilities.jsonStringify(e) );
   if (!e.parameter.page) {
       return HtmlService.createTemplateFromFile('home').evaluate();
   }
   return HtmlService.createTemplateFromFile(e.parameter['page']).evaluate();
}

function getScriptUrl() {
   var url = ScriptApp.getService().getUrl();
   return url;
}

このコードはいわゆる「おまじない」だと思ってください。このままコピーして使ってOKです。4行目の ’home’ は,一番最初に表示したいページのファイル名を.html無しで記述しましょう。

/*home.html*/

<!DOCTYPE html>
<html>
 <head>
   <base target="_top">
   <?!= HtmlService.createHtmlOutputFromFile('home_css').getContent(); ?>  <!--ファイル(home_css)を読み込む-->
   <?!= HtmlService.createHtmlOutputFromFile('home_js').getContent(); ?>   <!--ファイル(home_js)を読み込む-->
 </head>
 <body>
   <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>
 <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);
}

</script>

home_gs.gs

var count;
var result = [];
function search(inText_word){

 /*おまじない*/
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = spreadsheet.getActiveSheet();
 var textFinder = sheet.createTextFinder(inText_word);
 const cells = textFinder.findAll();

 /*検索に該当した冊数*/
 count = cells.length;

 /*JSファイルに送る検索結果を準備*/
   for(var i=0;i< count;i++){
     result[i] = cells[i].getDisplayValue();
   }
 
 /*JSファイルに送る*/
 return result;
}

home_jsのコードは1個の大きな関数です。検索ボタンを押した時に,この関数が動きます。それぞれのコードの役割はコメントアウトの通りですが,複雑な部分はコメントアウトを省いています。以下に,細かい解説をするので確認してください。

gsファイルは,「おまじない」の部分でスプレッドシートから本のタイトルを探し,resultという配列に格納します。textFinderの ( ) の中には入力されたワードを取得した変数を入れてください。今回のコードの場合は,home_jsの5行目の変数です。
因みにresultの中には,{タイトル1, タイトル2,}のような形でタイトルが入っています。これがreturnでhome_jsに送られます。

google.script.run.withSuccessHandler(function(){ }).別のgsファイルの関数名();

この関数は一見分かりづらいですが,JSの関数の中でgsファイルの関数を使う時に使用します。
1つ前で解説したgsファイルの関数を先に動かし,そこで完成したデータをこの関数に送り込み,JSの処理に活用するという流れです。今回のコードに当てはめると,「別のgsファイルの関数名()」は,1つ前で解説した,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の設定・コマンドに関しても主にここで行います。

図1

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アドレスを実行し,確認してみて下さい。


みんなにも読んでほしいですか?

オススメした記事はフォロワーのタイムラインに表示されます!
これからもよろしくお願いします!
千葉県立柏の葉高等学校 情報理数科
千葉県立柏の葉高等学校 情報理数科の公式note / 全国専門学科「情報科」高等学校長会 事務局 / 日本で唯一の「情報理数科」の高校 / 【投稿内容】◇専門教科「情報」の授業 ◇情報理数科独自の取り組み ◇Google Workspace&Microsoft Teamsの活用