【Googleスプレッドシート】施設利用状況をWebに公開できるようにしてみた

ども、最近は仕事の関係で「施設」だったりのシステム環境を改善することに楽しさを感じておりますクロです。

今日は施設予約状況を管理するエクセルを特定の情報を隠してWeb公開するものをGoogleスプレッドシートで作ったのでご紹介。

1,利用したもの

1. Googleドライブ

2. Googleスプレッドシート

3. 掲載したいWebサイト（今回はWix）

2,Googleドライブに格納先を作る。

まずはフォルダがごちゃごちゃしないようにわかりやすく「施設予約」とフォルダを作り、その中に「Web公開」という子フォルダを作成します。

2-2,予約状況のわかるファイルを作成。

Googleスプレッドシートにて施設の予約状況を管理するためのファイルを作成します。
今回は既存の環境を変えないように各施設に「団体名」「時間」「許可」「取消」を準備しました。

利用した数式の説明

1列目には「2022」「年」「1月」「=SUBSTITUTE(C1,"月","")」
A2には見えていませんが（フォント色を白色にしている）、「=day(TODAY())」を記載しています。
　▶︎後ほど今日の日付を色付けする用

2-2-1,日にちの自動記載

日にちは「=IFERROR(DAY($A$1&"/"&$D$1&"/"&1),"")」で表記し、月が変わった際に自動的に消えたり出てきたりしました。
2日の場合太字の部分を&2、3日の場合&3というふうに記載する。

数式の説明
IFERROR関数
もし数式がエラーした場合どのように表記したらいいかを指示している関数。
今回は月によって末尾が30日、31日、28日と異なるためエラーした部分を空欄として記載するようにしている。

2-2-2,曜日の自動記載

曜日は「=text(A4,"ddd")」で自動的に記載。

3,公開用シートを作成

別シートに情報を抽出して一般の人に見られてもいい情報のみを公開するシートを作成します。
今回は許可された時間のみを掲示する方法を採用しました。

3-1,参照シートを可変にする

上部の月を変更することで参照先を変更できるようにしました。

利用した数式の説明
「=if(indirect($C$1&"!I"&ROW())="","",if(indirect($C$1&"!J"&ROW())="",indirect($C$1&"!H"&ROW())&"(予約済)",""))」

IF関数
「=IF (論理式, 真の場合, 偽の場合)」
もし「論理式」が正しい場合、「真の場合」を、間違っている場合「偽の場合」を表示する。
今回だと、「空欄だった場合とそうでない場合」で場合分けをしていったので記載内容に左右されないようになっている。

INDIRECT関数
「＝INDIRECT(セル参照の文字列)」
文字列で指定したセル番地の値を表示するExcel関数。
指定した文字列を「そういう名前をつけたセルだ」として認識することができます。
可変にしたい文字列がある場合に組み込むことで数式をセルに書き込むことで書き換えられるようになります。
参考（https://excelcamp.jp/media/pulldown/5642/）（https://www.becoolusers.com/excel/indirect01.html）

ROW関数
現在の行を数字として利用できる関数です。

上記の関数を組み合わせると
「=if(indirect($C$1&"!I"&ROW())="",
　"",
　if(indirect($C$1&"!J"&ROW())="",
　indirect($C$1&"!H"&ROW())
　&"(予約済)",
　""))」
というものが出来ます。

やっていることは
「もし、
　プルダウンで指定したセルのシートの同じ行が空なら、
　空欄にする。
　そうでないなら
　プルダウンで指定したセルのシートの取消行が空なら
　同じ行の時間を転記して（予約済）というテキストを追記する。
　そうでないなら
　空欄にする」
ということをやっています。

数式が長くてややこしいですが
「=if(indirect($C$1&"!I←これは許可の列"&ROW())="","",
　if(indirect($C$1&"!J←これは取消の列"&ROW())="",
　indirect($C$1&"!H←これは時間の列"&ROW())&"(予約済)",""))」
のように施設ごとに太字部分の変更が必要です。

曜日を綺麗に表記する処理として
=text(A4,"ddd"&$B$3)
という処理を施しています。

4,公開用シートを転記する

Web公開をする際には他のシートを見られたくないので２,で作った「Web公開」のフォルダ内にスプレッドシートを新しく作成します。

ここでは簡単に転記ができる関数を使います。

IMPORTRANGE関数
「=IMPORTRANGE（“スプレッドシートキー”,“シート名!範囲の文字列”）」
他のシートから指定した範囲のデータを読み込むことができる関数。この関数によって別のスプレッドシートの内容を挿入することができます。

「スプレッドシートキー」は参照したいシートのURL
「シート名」は公開用が可変としてあるのでコチラでは固定したシート名「公開用」を設定。
「範囲」は全範囲を指定します。

5,条件付き書式

そのままでも完成ですが、色がつくようになっていると見間違いが少なくなりますのでおすすめです！

設定したのは
・週末の曜日だけ色が変わる
・今日の日付の色が変わる
・許可されたら施設の行が緑色になる
・取消されたら施設の行が赤色になる
の四種類です。

5-1,週末の曜日(土、日)色が変わる

セルの書式設定は完全一致、「土」もしくは「日」で色が変わるように指定しました。

5-2,今日の日付の色が変わる

カスタム書式に「=COUNTIF(A:A,A:A)>1」といれて重複したもののみ色がつくようにしています。（ここでA2のTODAY関数が役立ちます。）

5-3,許可されたら施設の行が緑色になる

カスタム書式を「=$E4<>""」として空欄ではなくなった際に緑色になるように指定しました。
※$E4はもちろん他の施設になれば指定が変わるのでそれぞれに指定が必要です。

5-4,取消されたら施設の行が赤色になる

5-3,と同じように取消行に文字が入ったら色が変わるようにしています。数式も同じです。

最後に

どうでしたか？
前回のフォーム作成のものと合わせることで予約システムがGoogleで簡単に作成できるのでもしよかったら試してみてください！
許可しない限りは「仮予約」であることを明記しておき、先着順や簡単な審査があることを了承してもらう方がいいですね！これができると二重申込などがあっても対応がスムーズですね！

また予約をフォームから行う場合は予約が殺到する可能性もあるので行数を増やしたり施設ごとに別シートで作って最後に統合するやり方もあるかと思いますので工夫してみてください！

タダで使えるものと仕組みで仕事を減らして「働かなくても生きていける」ようになりたいですね！それでは！