見出し画像

Googleスプレッドシート で使えるスピンボタン検証 -3　関数でスピン

mir

Excelにはあるけど Googleスプレッドシートには機能としては無い スピン機能（スピンボタン）。こちらを GAS や 関数で 実装する方法を検証し、どのアプローチがベストなのかを考えてみたいと思います。

前回の記事

Q. A2セルの数字を増減させるスピンボタンを実装したい

以下の4つの方法で検証してみます。

  1. GAS でスピンボタンを作ってみる

  2. モバイル対応 onEdit で動くスピン機能を考える

  3. 関数でスピンボタン出来るか試してみる

  4. HtmlService使って ダイアログでスピン

前回の 2の方法は、動きとしてはだいぶ改善されましたが、あと一歩Excelに近いサクサク食感（操作感）が欲しいなと。

じゃあ関数で実装したらどうなるか？
今回は 3番目の方法、関数でスピンボタンを検証します。

A3.関数でスピンボタン出来るか試してみる

GASを使う以上どうしてもモッタリ感があるというなら、サーバーサイドで処理をするGASを使わずシート関数（数式）で処理できないか？ってのが今回の趣旨です。

関数でスピンボタンを実現するにあたり、以下をどうするか考える必要があります。

  1. スピン「ボタン」をどうするか？

  2. どうやってボタンを押して数字を増減させるか？

まずは、ここをクリアしていきましょう。

1. スピン「ボタン」をどうするか？

これは物理的なボタンの変わりをどうするか？という問題です。

図形をボタンとして使うためにはGASが必須であり、シート関数ではボタンをクリックした挙動を拾うことはできません。

というわけで、前回の onEdit手法と同じく チェックボックスをボタンとして利用 がよいでしょう。

チェックボックスであれば セル参照で関数からも拾えるので簡単です。

乱数系の関数と組み合わせて 表示/非表示切り替え & 再シャッフル ボタンとしての使い方もお勧めです。

乱数も勝手に再計算されちゃう系

スピンボタンにも使えそうですね。


2. どうやってボタンを押して数字を増減させるか？

ボタン（チェックボックス）を押して（クリックして）、どうやったらGAS使わず数字を増減させることが出来るか？

過去の記事を振り返ると、このような一見GASを使うしかない、関数で対応できそうもない処理を実現してきた裏技があります。

GASなしでタイムスタンプを押したりビンゴゲームを作ったり、え、こんなこと出来るの？を実現してきた禁忌の呪法  循環参照の許可・反復計算 です。

反復計算はマイナーテクニックで取り上げてるサイトが多くないんで、mirは好んで取り上げています。直近の note では 流行りの ChatGPTネタでも活用しました。

循環参照を使って自分自身のセルを参照して +1（または -1）させることで、スピンとして動かせそうです。

まずは事前準備が必要です。

Googleスプレッドシートの メニューから
ファイル ＞ 設定 ＞ 計算タブ で、
再計算を オン、最大反復回数を 1 としておきましょう。

タイムスタンプの時と同じ設定

これで準備はOKです。

ちなみに最大反復回数を 1に変更せず 初期設定 50のまま使ってしまうと、自分自身のセル +1 が50回繰り返されてしまい、ワンクリックで0 が 50になってしまいます。


プロトタイプ版 関数スピンボタン

とりあえず作ってみる

まずはプロトタイプの関数スピンボタンを作ってみました。
ガンダムだと RX-78ですかね。

チェックボックスの役割としては以下の設定。

B2　・・・　プラスボタン （チェックアリで +1）
B3　・・・　マイナスボタン（チェックアリで -1）
B4　・・・　リセットボタン（チェックすると 0に戻る）

式は IFSで処理するのがシンプルでよいかなと。

=IFS(B2,A2+1,B3,A2-1,B4,0,true,A2)

IFS関数の説明は ChatGPTの記事でも書きましたが、どれにも該当しない場合（その他）の設定をするには、最後に　true,どれにも該当しなかった時に返す値（処理）　とすればOK。


実際に動かしてみましょう。

うーん惜しい感じ

動きました。が・・・、惜しい感じです。

スピンボタンは「カチ」（1クリック）で 増減させたいのに、TRUE（チェックあり）の時だけ処理を動かす式なので、「カチカチ」（2クリック）で１増える（減る）って動きになってます。

このせいで、なんかイマイチな操作感。

そもそも自分で入れたチェックを、一旦手動で外して再度チェックを入れるという虚しさ。でも、このチェックを外す部分を自動化すると GASを使うことになっちゃうし・・・。

結局、「カチ」（1クリック）で 増減させるには、チェックありでもチェックなしでも、とにかく切り替わったら増減させるような方式にするしかないみたいです。

ただ、それだと悩ましいのは、関数だと「今どのセルが変化したか」が拾えないので、プラスのクリックなのかマイナスのクリックなのか判断が出来ないという点。

というわけで、試行錯誤して改良した最終系スピンボタンが ↓です。


完成形 関数スピンボタン

こんな感じに作ってみた

スピンさせる数値(A2) の式

=IFS(C2="",C3,
　C2="▲PLUS",A2+C4,
　C2="▼MINUS",A2-C4,
　C2="🔲ROCK",A2)

理解しやすいように改行してます

こちらが完成系になります。
C2のプルダウンを モード切替として、IFSで分岐させてます。

C2のプルダウンが
空白　なら　C3（初期値）の値を参照　・・・　リセット
▲PLUS　なら A2（今の値）+C4（増減値）・・・プラス
▼MINUS　なら A2（今の値）-C4（増減値）・・・マイナス
🔲ROCK　なら A2（今の値）のまま　・・・　固定

これ、チェックボックスは単に連打しやすくするためだけのもので、プラスやマイナスモードの状態だと、どのセルを編集しても再計算が走ります。

つまり、チェックボックス以外のセルへの入力・変更があっても 数値が変動するってことです。

 これだと困るので、ROCK 固定モードを選択肢に入れてます。

実際動かしてみましょう。

連打するとオラオラしちゃう

さすが関数だけあって、GASと違い スケスケ サクサクだぜ。（跡部王国） そして、リズムにのるぜ。（スピードのエース）

モード切替時にも 1増えちゃい（減っちゃい）ますが、これは許容範囲かなと。

プルダウンを ROCKとすることで、数値の固定が出来ること、またプルダウンを空白にした際には 初期値リセットとなること。そして、初期値、増減値の変更にも対応できていること。

これらが ↓のgif画像で機能していることが確認できますね。

ロック！って 小学5年生クイズを思い出す

いい感じです！


関数スピンボタンは、実務（お遊び）でも使えそう

この速さなら言える！（古い世代の ２ch用語） じゃないですが、

このスピードなら、もうアレ（Excelでやったスピンボタンでポ〇モン図鑑）いけちゃうんじゃね？って思いますよね。

任〇堂をネタにするのは色々不安があるが・・・

すんごく快適にページめくりできてますね。（検証用に作ったデータなので、130番台だけですが・・・）

プラス、マイナスの切換えは若干面倒ですが、それは贅沢というもの。

GASのスピンボタンの モッタリ感と関数スピンボタンの サクサク感の差は、かつてテレホーダイで深夜に ダイヤルアップ接続でネットにつないでいた時代の人間が、現代の ひかり回線や 5G でのWebサイトの表示スピードを見たくらいの衝撃かもｗ

ミスター味っ子こと 味吉 陽一 が、山芋のすりおろしを混ぜる工夫をした お好み焼きの生地くらいのサクサク感です。

もちろん このスピード感は、元となるデータテーブルを同じスプレッドシート内（別シートで可）に用意しないと無理です。

importrangeで別スプレッドシートを直接参照した場合は、このスピードには対応できません。表示がかなりモッタリしてしまいます。

ちなみに 上の表示はExcelの時と同じく、Noの数字をキーにVLOOKUPやXLOOKUPで簡単に作れます。

さらに 画像に関しては Googleスプレッドシートはセル内画像が使えるので、画像も同じく VLOOKUPやXLOOKUPで簡単に参照できます。

一応 Excelでも設定が面倒ですが VLOOKUPで画像を引っ張るテクはありますし、今後 image関数が一般ユーザーにも展開されるとGoogleスプレッドシートと同じように セル内画像を参照できるようになるかも。


スマホでの動作検証

スマホはダークモード派

プルダウンの反応が悪いのと、チェックボックスを押しミスすると画面キーボードがあがってきちゃうのが難点ですが、 スマホアプリ版でもいい感じに動作しました。

これはスマホでも十分使えると言ってよいでしょう。


関数スピンボタンの 欠点

ここまでの検証で、増減のプルダウン切り替えの手間はあるものの、ほぼ理想形といえる 関数スピンボタンですが、「保存」という点で致命的ともいえる欠点があります。


本当にあった Why 話

その「欠点」に気づいたのは、色々と 試していく中で リロード（画面の更新）、一度閉じてしばらく時間をおいて再度開くといった検証をしていた時です。

ROCKに切れ替えれば、数値は保持されるはずなんですが・・・。

たまに開きなおすと 閉じる前と数値がズレている（ような気がする）時があったんですよね。。

なんか変だなー、変だなー、怖いなー、おかしいなー・・

でも、単に mirが 閉じる前の数値を勘違いしただけ、「モルダー、あなた疲れてるのよ」 ってことかなと思っていたら・・・

ギャー！！！

それは更新の検証をしていた時のお話

おわかりいただけただろうか

誰もいないのに、リロードしたら 数字が 20から 9に！？
なぜか数値が変わってしまいました。

 Why Googleスプレッドシート！？

で、色々検証して 原因はわかったんですが・・・。


俺のクリックがチート過ぎて Googleスプレッドシートがまるでついてこれないんですが。-ΑΩ-

左が 別窓表示用、右が実際の操作画面

このように 操作用と別で 同じスプレッドシートの画面を表示用として開き、両方並べて操作してみると原因がわかりますね。

序盤のゆっくり目のクリック時は、若干のタイムラグがあるものの 表示用も同じ数値に更新されますが、途中から クリックスピードについていけなくなり、クリックの取りこぼし（つまり 数値カウントのズレ）が発生しています。

これは、Googleスプレッドシートが Webアプリケーションであり、サーバーサイドで処理されていることによるものと考えられます。

つまりクリックによる再計算が早すぎて、サーバー側との通信で拾いきれない計算があるってことです。

このスピードで再計算が繰り返されることは Googleスプレッドシート的には、想定外ってことなんでしょう。

本当に禁じ手だったのかもｗ

なんか Googleに 「バカめ、それは残像だ。」 って言われた感じですね。

このブラウザ側で見えてる数値と サーバー側の数値のズレ、過去記事でも同じようにこの問題に直面しています。

GASなしで チェックボックスで ビンゴゲームを作る 回 で発生した、乱数のズレと同じ現象です。

で、更新される前の ブラウザで表示されている値（画像の 20）を、どうにか取得できないものかと色々検証してみましたが、どうやっても自動では取得できませんでした！！

唯一出来る方法は手動でによる コピー・値を貼り付けですが、これをGASでやろうと getValue すると 画面表示の 20ではなく、サーバー側の数値 11がとれてしまいます。 getDisplayValue でも同様。

そもそも 関数 スピンボタンなんだから、GAS使っちゃ意味ないですしね。

他にも オフラインモードで試したり、 importrange で自分自身のシートを 一回外部を経由させて 参照させたりと試しましたが、いずれの方法も サーバー側の数値とズレのある、このブラウザでのみ表示されている 数値取得は 無理そうでした。

これは、誰か本職のエンジニアさんにでも 検証してもらいたいもんです。

↓

その後、色々と検証した結果 GASに頼らない解決策が見つかりました。


割り切れば使えるし、無理なものは運用でカバー

無理なものは仕方ないんで、結論としては

  • 前回の続きからスピンするような使い方をしない

  • 続きからスピンする場合は初期値変更で対応

といった形で、割り切って使うか運用でカバーって感じでしょうか。

スピンボタンでポ〇モン図鑑 みたいな使い方であれば、別に再度開いた時に前回の続きからじゃなくても問題ないですし、続きから使いたいなら

  1. ROCKに切り替えて数値を固定

  2. 初期値を表示されている数値に書き換える（手動）

  3. モード切替プルダウンを Delteで空に

  4. A2の数字は 初期値として固定される

といったセーブ手順を踏めば、次に開いた時に 前回の続きからスピンを始められます。

オートセーブに慣れ切った 平成・令和の世代からすると、いちいち 閉じる前にこんなことやるの面倒だよーって意見が出そうですが・・・

昭和のゲームは セーブデータが飛んだり、復活の呪文を書き間違えてデータ死亡は当たり前だったんじゃい！って 言ってやりましょうｗ

常に死を意識することで、充実した生がおくれるわけです。
メメントモリｗ

というわけで、今回のまとめです。


A3.関数で動かすスピンボタンで、実現できたことと注意点

・A2セルの数字 スピンボタンで 増減させたい　→　OK

（以下は追加要件）
・できれば Excelみたいに サクサク動かしたい　→　◎（早い）
・できれば スマホ（アプリ版）でも使えるとよい　→　〇
・増減の値は カスタムできるとよい。 5ずつとか。 → 〇
・セル固定ではなく、アクティブセルでスピン機能使えたりする？ → ×

（その他の良い点 ）
・圧倒的なサクサク感
・GAS不要で簡単に使える

（その他の注意点）
・厳密には増減のボタンではなくプルダウンでのモード切替
・早いクリックの場合、サーバー側と数字のズレが発生。
　（再度開いた時に前回の数字が保持されていないことがある）

結果としては、サーバー側の処理である GASに比べると 圧倒的にサクサク動くスピンボタン となりました。

もちろん、上に書いた通り 増・減 2つのボタンがあるわけではなく、プルダウンでモードを切り替える必要があり 厳密にはスピンボタンと言えませんが、それでも 「ポ〇モン図鑑」の例みたいに 紙をめくる感覚に一番近い操作ができます。

スピンさせるセルにはあらかじめ数式を入れておく必要があるので、アクティブセルへの対応は残念ながら無理です。

そして、一度閉じた際に 数字が変わってしまう欠点もあります。

でも、この点だけ割り切れば 一番実用的な Googleスプレッドシートでの スピンボタン って言えるんじゃないでしょうか？


そして 今回の関数を使ったスピンボタンの検証と 前の2回の GASを使ったスピンボタン検証を比較すると、やはり クライアント側の処理で動かさないとExcelと同じようなスピンボタンのサクサク感は出ないってことがわかりました。


それなら GASから HTML・Javascript を生成することで クライアントサイドのサクサクの操作感が出せないか？

ってことで、次回 スピンボタンシリーズの最後は GASの HtmlService で スピンボタンを検証してみましょう！


■このシリーズの次の記事


この記事が気に入ったらサポートをしてみませんか？