🎰

Google Docsで始めるYoutuberがライブ配信中に視聴者をランダム選出するためのスプレッドシート。

2022/11/04に公開

Google Docs

Google Sheets

tech

リスナーちゃんはハコりたい！ω

~~おしりだって洗って欲しい。~~

概要

先日とあるYoutuberさんが、リスナーとのゲームの対戦実況をライブ配信されていたのだけど。参加枠争奪戦がちょっと大変そうだった。割りと瞬殺で、いつがタイミングなのかが分かってないと完全にアウトオブ蚊帳になる辛さ。
(別に視聴は出来るので、実際は全然蚊帳の外なわけじゃないけど)。

前回のときも何かいい方法はないかと考えて、認証系も考えるとYoutubeならGoogleアカウントなのでGgoogle App Engine (以下GAE) が良いかなぁと思ったものの。GAEの使い方が良くわからないのに加え、従量課金トラブルも怖い。

そんな中、コーディングなんてせずとも、スプレッドシートで出来るんじゃね？🤔 アンケートフォームでは回答を募ることも出来るし、良識あるリスナー同士なら、性善説に則れるなら、ある程度はプログラミング無しでGoogle Docsだけで出来そうに思った。

という訳で、ちょっと作ってみた。割りとGABBR GABBRだけど。

環境

Googleアカウント
YoutubeにコメントするのにもGoogleアカウントが基本必要だし、最もベターな希ガス。
Google Forms
参加表明用のアンケートフォーム。選出の対象者の情報源。
視聴者にはURLで、閲覧者として共有するのが良いと思うます。
Google SpreadSheet
今回の本丸。
参加表明者をランダムに選出するためのスプレッドシート。 RAND関数 と フィルタ機能 を用いてランダムに並び替えをして、都合に合わせて上位の人を選出者として扱うことにすまし。主な利用機能は以下。
- IMPORTRANGE関数
  外部スプレッドシートの読み込み。
- SPILL機能
  配列の内容を自動展開。
- ARRAYFORMULA関数
  SPILLられた配列領域を処理する。
- RAND関数
  選出のための重み付け。何かしら編集があると、すぐ乱数が更新される。
- フィルタ機能
  重みのソートに利用。ソートした蕎麦から乱数が更新されるので、この ソート自体が選出 みたいな感じ。

ようは、ブラウザーだけでなんとかなるってね。
なのでメール通知とか、選出された人への通知手段は無いです。

完成ピジョン (🐓)

ファイル(？) 構成

アンケートフォームはURLでユーザーに共有するよ。

フォームと回答

参加フォーム と 参加フォーム (回答)

ランダム選出

ランダム選出を行う Pickup! スプレッドシート。

選出前

最後に選出したあとの状態。

選出済みである情報を書いて、フィルタ機能 で 昇順ソートを実行。

上位のユーザーを選出対象者として扱う。
(気に食わなければ繰り返しソートをし直す。)

水曜どうでしょう？

使い方 (とりあえず使ってみる)

手順(1) マイドライブにコピーする。

サンポウルとして以下を共有しますので、コポピーして使ってください。

回答のスプレッドシートをコピーすると、フォームもコピーされてくるので、コピー操作はスプレッドシートだけ選択。マイドライブの直下にコピーされるので、自分の好きな場所に移動してください。

手順(2) フォームのURLをコピーする。

回答のスプレッドシートを開いて、そのURLを選出用のスプレッドシートに貼り付ける。URLの余計なパラメーターは除去して貼り付ける。
( #edit～ など )

手順(3) 回答をリセットする。

コピーした段階ではゴミがあったりするので。

手順(3) 参加表明を募集する。

作成したフォーム権限を、「リンクを知っている人全員」に変更し、閲覧権限でインターネットに公開する。「回答者リンクをコピー」からURLを取得し、参加対象者に教えて回答を待つ。

ｶﾓｰﾝ Щ(ﾟДﾟЩ

手順(4) 選出

回答が集まってきたら選出を行う。フィルターのソートで並び替えを行い、上位のn人を選出する。選出したら、その行に選出回数を記入・更新を行う。

(ソートして)

(キミにキメた！)

手順(5) リセット

46億年後、もしまた再度、ランダム選出を用いたい企画を行う際に、スプレッドシート再利用出来ると嬉しい。毎度毎度コレまでの作業を行って作成するの面倒くさいので。

以下、その際のリセット手順

(5-1) 回答フォームの初期化

手段(1) フォームの再作成

フォームの作成手順を再び行い、フォーム及び回答のスプレッドシートを普通に新規作成する。
その回答スプレッドシートのURLをPickup スプレッドシートに貼り付ける。

手段(2) フォームの回答をリセットして再利用

フォームには回答のリセット機能がある。使用済みのフォームの文言の修正などを行い、回答をリセットする。また、回答のスプレッドシートの中身も消えていない場合もあるので、その際には手動でも削除もをする。

(5-2) 選出用スプレッドシートのリセット

手入力欄を削除る
回答フォームを再作成した場合、新しい回答スプレッドシートのURLの貼り付ける

(5-3) 手順(3)に戻る。

それではまた46億年後に。

ゼロから作る人のための詳細解説

手順(1) フォームと回答のスプレッドシートを作成する。

(1-1) 質問と設定

フォームでは以下の2つを収集する。

選出確定時に呼び出す為のハンドルネーム (H/N)
選出が確定した人には卓に入ってもらうため、その呼び出し名。
Googleアカウント情報は取れないため、自己申告してもらう。
参加可否
途中退席された際にピックアップされない様にするため、参加しない」方の回答も収集する。回答を1回に制限し、編集を許可することで、退席の申告を上書きできる (後述)。すでに参加されていない人を選出したくないし。視聴者も配慮したいと思う人が少なくないと期待できるので。

設定は、以下2項目を設定する。

「回答の編集を許可する」を有効にする
　　退席の自己申告のため。
「回答を1回に制限する」を有効にする
　　参加表明を複数回収集してしまうと、ピックアップ精度に悪くなってしまうため。

なお、本記事では 「メールアドレスの収集」 も行った形で記載している。しかし、あくまで回答のコピーが対話ユーザーにメールで届くだけで運用には全くメリットが無いため、リスナー相手であれば収集しない方が良いと思われる。

(1-2) 回答用のスプレッドシートを生成

スプレッドシートが (勝手に) 出来る。

※ 生成しないで、既存のシート使うのでも良いけど。

手順(2) 選出用スプレッドシートを作成する。

(2-1) スプレッドシートを適当に作る

(2-2) 回答を取り込む為の設定情報を集める。

回答は IMPORTRANGE 及び SPILL 機能を用いて取り込み。そのために必要な情報を収集して記述する。

IMPORTRANGE関数の引数は
IMPORTRANGE("スプレッドシートのURL", "シート名!範囲起点:範囲終点") 。

列単位で参照すれば縦にSPILLられるので、列の為の情報を収集する。また併せて 参加が可能 を判定するための回答文字列も定義シテオク。

(2-3) 回答を列単位で取り込んでSPILLる。

IMPORTRANGEでの取り込み元を列で範囲指定し、SPILLらせる。縦にSPILLられるので、各列を横に並べる。1行目には自分のわかりやすい項目名を付けるため、2列目から取り込みさせる。

取り込み列	式
「タイムスタンプ」列	`=IMPORTRANGE($B$2, $B$4 & "!" & $B$5 & ":" & $B$5)`
「メールアドレス」列	`=IMPORTRANGE($B$2, $B$4 & "!" & $B$6 & ":" & $B$6)`
「H/N」列	`=IMPORTRANGE($B$2, $B$4 & "!" & $B$7 & ":" & $B$7)`
「参加可否」列	`=IMPORTRANGE($B$2, $B$4 & "!" & $B$8 & ":" & $B$8)`

(2-2) 回答に情報を肉付けする。

列でSPILLらせたため、このままでは選出対象行の特定が出来ない。以下の情報の肉付けにより、退席回答の行だけでなく、未回答行やヘッダー行も除外するための情報が出来る。

情報	式	備考
回答のある行かを判定	`=ARRAYFORMULA(IMPORTRANGE($B$2, $B$4 & "!" & $B$5 & ":" & $B$5) <> "")`	タイムスタンプが空でなければ、回答があると判定。
参加の回答かを判定	`=ARRAYFORMULA(IMPORTRANGE($B$2, $B$4 & "!" & $B$8 & ":" & $B$8) = B3)`	特定の文字列と一致すれば、参加の旨の回答であると判定。

ARRAYFORMULA 関数により、範囲それぞれに式が適用され、結果が範囲のためまたSPILLれる。

いずれも TRUE の行が最終的なピックアップ対象として扱いたい。

(2-3) 回答をINDEXで引っ張ってくる。

SPILLはソートの影響を受けないため、行番号で更に別の場所に引っ張ってくる。引っ張るための連番は手で作成して引っ張る。

項目	式	備考
回答No	連番の定数。	引っ張るための行番号
active row	`=INDEX(L:L, $E21 + 2, 1)`	重み付けに使用する項目。
joiner	`=INDEX(M:M, $E21 + 2, 1)`	重み付けに使用する項目。
H/N	`=INDEX(P:P, $E21 + 2, 1)`	ピックアップ時に読み上げたい名前。

(2-4) 選出対象である目印項目の作成。

項目	式	備考
#	`=ROW() - 20`	ソートの影響受けない連番にする。
Pickup! マーク	`=IF(C21<=2, "Pickup!", "")`	ソートの影響を受けない上記項目を用いて、特定の数以下のときにマークを表示させる。

(2-5) 選出済み管理用の項目の作成。

項目	型	備考
Pickup回数	数値 (自然数)	選出した回数。重み付けに使用する項目。
Pickupメモ	文字列	利用時の自由なメモ。

(2-6) 乱数とソート用の重み付け。

今回の本丸。

項目	式	備考
ソ⁠ー⁠ト⁠用	`=(NOT(F21) * 9) & "-" & (NOT(G21) * 1) & "-" & TEXT(I21, "0000") & "-" & TEXT(B21 * 1000000000000 * F21, "000000000000")`	最終的な判定用の重みの項目。各重みをハイフン区切りで連結。
乱数	`=RAND()`	ランダムに選出するための重みの項目。

(2-7) フィルターを作成

重みでソートを行うため、フィルターを作成する。

(3) 整理 (見栄えとか設定項目とか)

必要項目を揃えて整理して完了。

う～ん、センス無いの塊。。。

補足

重みについて解説

重みは 0-0-0000-916932336548 の様な形で複数の値を連結した文字列で表現している。各値を区切り文字によって連結された文字列によってソートを行うことで、各値の長さ違いも吸収する。各値は次の通り。

#	内容	式	説明
1	行⁠の⁠回⁠答の有無	`(NOT(F21) * 9)`	論理値に算術演算すると数値になる。昇順ソートのため、回答の存在する行を小さくしたいので、反転させてから乗算している。 => 0 : 回答あり, 9　: 回答無し
2	参⁠加⁠表⁠明⁠有⁠無	`(NOT(G21) * 1)`	昇順ソートのため、参加表明をしている行を小さくしたいので、反転させてから乗算している。 => 0 : 参加回答, 1 : 不参加表明
3	選⁠出⁠回⁠数	`TEXT(I21, "0000")`	選出回数を 0埋め4桁で文字列化。未選出 (空) と 0 は等価。文字を入れるとそのまま重みに連結されるので、数字より前後のコードポイントの文字を活用することも可能。
4	ラ⁠ン⁠ダ⁠ム⁠文⁠字⁠列	`TEXT(B21 * 1000000000000 * F21, "000000000000")`	小数で生成された乱数から12桁分を抽出しランダムな12文字を抽出。また、乱数に回答の存在も掛け合わせて、回答が無い行は視認性のため 0 にしている。

また、区切り文字には、コードポイントが低い - (ハイフン) を用いることで、前に来やすくしている。

(半角カタカナ？そんな子はここには居ませんよ。。。)

乱数など数値を基本にした重みにしているため、視認を若干得られる。また、RAND 関数はフィルターによるソートでも再生成されてセルが更新されるので、繰り返しランダムにソートを行うことができる。

逆に区切り文字を後ろに回したいなら · ( U+B7 , MIDDLE DOT ) なんかも使いやすいかも？

Q&A

Q. たまにフィルターのソート効かないんだけど。。。

なんかやり過ぎると乱数貰えなくなるのかな。。。
でも、他の列をソートしてからやり直すと復活したりする。
あとは、スプレッドシートを開き直しても良い。

Q. なんでGASらないの？

GASるとスクリプトのコピー面倒いじゃん。。。
(´・ω・｀)

コア機能のコピーのし易さを考えると、スプレッドシートの場合はGASらない方がポータビリティ性が出る。

Q. 回答した人のGoogleアカウント分からないの？

Google Workspaceなら自動で収集してくれるみたいなんだけど。個人アカウントだとダメみたいね。。。
_(:3」∠)_

Q. いたずら防止はどうしたほうがいい？

ﾑﾘｹﾞｰ _(┐「ε:)_

Q. コレってプログラム？

どのへんがチューリング完全？

Q. コレってノーコード？

ノーコード？知らない子ね。

あとがき

ARRAYFORMULA関数 (配列演算) と SPILL クソ楽しい。

謝辞

Google Drive