ノーコードで掲示板を作ろうよ with スプレッドシート

生徒会情報機構のKombumoriです。

はじめに

私たちは普段、会話、チャット、SNSなど多くの連絡手段を駆使して生活しています。しかしながら、ときにその連絡手段は制限されます…例えば学校とかね？
ならば、どうすれば良いか。絶対に制限されない連絡手段を自作してしまえばいいですね。

-- 次節駄文、読みとばしてもヨシ --

絶対に制限されないとは？

「絶対に制限されない」方法を考えるのはなかなかに難しいものがあるので、まずは制限されるサービスの特徴を考えます。
それはひとくちに「業務に必要のないサービス」です。逆に考えれば、「業務に必要のあるサービス」は決して制限されることはありません。だって制限しちゃったら業務に支障があるもんね。

つまり？

この記事では業務にGoogle Workspaceを使用していることを想定します。すなわち、ドキュメント、スライド、スプレッドシート、フォーム、サイト…etcを使える、ということにしましょう。
※ なお以後紹介する方法はMicrosoft 365でも実現できないことはないはずです。

何ができるってこと？

例えば、生徒会。一般的なSNS（Xなど）でのアカウントの作成は許可してくれない。学校で導入しているサービスしか使えない。コードは書きたくない。結構学校の制限（フィルタリング）は厳しい…
そんな場合であっても、手軽に校内掲示板を立てられます！

「超」簡単にチャットをする方法

Google Chatが許可されているならばそれを使えばよし。結構便利ですよね。
また、ドキュメント、スライド、スプレッドシートには「コメント」という形で共同編集者と連絡が取れるようになっています。コメントは抹消してしまえば何も残らない（はず）ので、上級権限で復元される…なんてことはありません。
ただ、この記事は校内掲示板のようにある範囲において不特定多数が使えるようなサービスを作りたいと考えているので、コメントは残念ながら使えませんね…

ためんな

そろそろ本題に移りましょう。今回使うサービスは以下です。

• Google Spreadsheets
• Google Forms
• Google Sites（任意）

この組み合わせで察した方、さすがです。ただ、今回はその察しの上を行きます（願望）。

ちなみにうちの学校ではSitesが使えません…若干利便性が下がりますが、必須ではないので…

Microsoft 365に関して

後述の方法は

• Microsoft Excel
• Microsoft Forms
• Microsoft Sway

の組み合わせでも実現できることがわかるはずです。（ただしExcelとFormsが同期できるプランである必要がある）

ちなみにSpreadsheetsを使ったことがあることを前提に話を進めています。

概要

ものすごく簡単に言うと次のようになります

• Formsが投稿画面
• Spreadsheetsが閲覧画面

Formsには、質問の回答をSpreadsheetsに同期（自動転記）する機能があります。これを利用して、Formsに投稿された内容をSpreadsheetsで見ることで掲示板にしようじゃないか！というわけですね。

ただ、これだけだと面白くありません。この記事ではさらに

• 閲覧画面をデザインしよう
• 投稿と閲覧をしやすくしよう

といったところまで踏み込みたいと思います。

れっつ掲示板づくり！

①フォームを作成

こちらから新規でフォームが作れます。

ちなみに

.newドメインのショートカットは（あまり知られていませんが）便利です。Workspace系のみならず、Office Onlineなどでも一発で新規にファイルを作成できます。詳しくはこちらの記事を参照。

設定はこんなふうにしておけば良いでしょう。

• 「投稿内容」の回答は必須にしましょう。
• メールアドレスの収集は「確認済み」にしましょう。
• 画像には入っていませんが、「表示設定」>「確認メッセージ」に更新に時間がかかる旨を掲載しておくと親切でしょう。

②フォームとスプレッドシートを同期

スプレッドシートの名前は任意です。

おめでとうございます！これで完成です！

何いってんの、という方へ。

「スプレッドシートで表示」をクリックするとスプレッドシートが開かれます。

さて、フォーム作成画面の「送信」からフォームのリンクをコピーし、開いて回答してみましょう。
あら不思議、回答内容がスプレッドシートに転記されるではありませんか。
これでベースのシステムは実質完成です。

③デザインの準備

もちろんこのまま完成にしてもOKですが、ここはあえて一工夫。
デザインしましょう！

でもデザインってHTMLだのCSSだの書いてしっかりコーディングしなきゃじゃん…いや、使いません。スプレッドシートを用いてデザインします！（したがって、そんな高度なデザインはできません。少なくとも私には。）
仕組みは単純。スプレッドシートの関数を用いてうまい感じに整形してやるだけです。

まずフォームの回答が集まっているシートの名前が「フォームの回答 1」であることを確認してください。初期設定ではそのはずですが、たとえば言語設定が日本語以外であったりすればこのシート名は別のものになっているかもしれません。その場合は以下シート名を読み替えるか、回答の集まっているシート名を　「フォームの回答 1」に変更してください。

さて、回答の集まっているスプレッドシートに新たなシートを加えます。「掲示板」とでもしておきましょうか。
また、テストがてらフォームに何件か回答を送信しておきましょう。

④デザインしていこう

以下のデザインに関しては一例です。自由にデザインしていきましょう！なお、フォームは上で作ったものを前提としているので、別の設問を設定したフォームの場合はまた状況が異なります。

「掲示板」シートのB1、C2、D3、A4セルに以下のように入力します。

B1

=MAP(TRANSPOSE(SPLIT(TEXTJOIN("\ \ \ \",TRUE,MAP('フォームの回答 1'!B$2:B,LAMBDA(Val,SUBSTITUTE(Val,"\","%5C")))),"\")),LAMBDA(Val,SUBSTITUTE(SUBSTITUTE(Val,"%5C","\")," ","")))

C2

=MAP(TRANSPOSE(SPLIT(TEXTJOIN("\ \ \ \",TRUE,MAP('フォームの回答 1'!C$2:C,LAMBDA(Val,SUBSTITUTE(Val,"\","%5C")))),"\")),LAMBDA(Val,SUBSTITUTE(SUBSTITUTE(Val,"%5C","\")," ","")))

D3

=MAP(TRANSPOSE(SPLIT(TEXTJOIN("\ \ \ \",TRUE,MAP('フォームの回答 1'!A$2:A,LAMBDA(Val,SUBSTITUTE(Val,"\","%5C")))),"\")),LAMBDA(Val,SUBSTITUTE(TEXT(SUBSTITUTE(Val,"%5C","\"),"yyyy年mm月dd日")," ","")))

A4

=MAP(TRANSPOSE(SPLIT(TEXTJOIN("\ \ \ \",TRUE,MAP('フォームの回答 1'!A$2:A,LAMBDA(Val,SUBSTITUTE(Val,"\","%5C")))),"\")),LAMBDA(Val,IF(SUBSTITUTE(Val," ","")="","","———————————————————————————————————————")))

全て入力すると以下のようになるはず。

お気づきであろうか。回答送信日と記事投稿日との大きな乖離に…

式の仕組み

それぞれの式は、簡単に説明すると「フォームの回答 1」シートのそれぞれの列に並んだデータを間隔を開けながら配置することを行なっています。例えば、「フォームの回答 1」にはB2に[e-mail]、B3に[e-mail]というメールアドレスが並んでいます。これらを取得すると[e-mail],[e-mail]という配列になりますが、これを\ \ \ \で接続し（[e-mail]\ \ \ \[e-mail]となる）、その後\で分けてあげることで配列は[e-mail], , , , ,[e-mail]となり、それをセルに配置することで空白セルが３つ入った状態で配置されるようになります。細かく言うと元データの\を%5Cにしたり空白セルから空白を取り払ったりしていますが。
つまり、間にある空白セルの数を変えたい場合は \を増やしたり減らしたりすればいいわけですね。

あとは文字寄せや色、フォントサイズを変えたり変えなかったりすることでうまくデザインできます。が、次の過程で調節が必要になってくるかもしれません…

こんな感じになりました

⑤公開しよう

さあ、いよいよ公開していきます。その方法はいくつかあります…

1. Sitesにおいてドライブのファイルとして埋め込む方法（推奨）

Google Sitesから新規にサイトを作成、右メニュー「ドライブ」ボタンからスプレッドシートを選択して埋め込む方法です。リアルタイム更新が行われますが、利用者が見られるシートを制限できないなど場合によっては難点があります。なお、スプレッドシートの閲覧権限を「リンクを知っている人全員」にしておく必要があります。
Google Sitesに埋め込む場合は作成したフォームと一緒にしておくと良いでしょう。

この方法でも見られるシートを制限する方法

=IMPORTRANGE()関数を使ってあげることで見せたくないシートを見せずに公開することもできます。Google等で使い方を検索していただいければわかるはず！読み込み元に閲覧権限を与える必要がないからこそできる方法ですね。
ちなみに見られるシートを制限するような状況とは「メールアドレスを公開したくない場合」などでしょうかね…。メアドとユーザー名の対応表を作り、表示するのはユーザー名にすることで表面的にはSNSっぽくなるかもしれません。

2. 「ウェブに公開」機能を使う方法

Spreadsheetsの「ウェブに公開」機能を使うことでスプレッドシートに閲覧用のリンクを与えられます。

ブラウザ版のSpreadsheetsから「ファイル」>「共有」>「ウェブに公開」

公開したいシートを指定、公開ボタンを押すとリンクを得られます

得たリンクを使って埋め込んだり（埋め込みリンクがこの場合はベター）、リンクとして普通に送ったり…使いみちは色々ありそう。

以上。完成です。

まとめたかった

まあ正直言って、掲示板としての使い勝手は悪いです。ただ、掲示板を自作するってのはロマンがあるよね。

ちなみに、スプレッドシートで掲示板を作るなんて羽目になったのはGASであまりにも汚いコードを書いた故に校内掲示板として保守性がなくなったからですね。南無阿弥陀仏。より保守性がなくなったって？