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

2022年2月1日 11:11

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

実行画面

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) {
   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のコードは１個の大きな関数です。検索ボタンを押した時に，この関数が動きます。それぞれのコードの役割はコメントアウトの通りですが，複雑な部分はコメントアウトを省いています。以下に，細かい解説をするので確認してください。

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

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

オススメした記事はフォロワーのタイムラインに表示されます！

これからもよろしくお願いします！

千葉県立柏の葉高等学校情報理数科の公式note / 全国専門学科「情報科」高等学校長会事務局 / 日本で唯一の「情報理数科」の高校 / 【投稿内容】◇専門教科「情報」の授業　◇情報理数科独自の取り組み　◇Google Workspace＆Microsoft Teamsの活用