Googleスプレッドシート で使えるスピンボタン検証 -番外編 関数でスピン【改良版】
Excelにはあるけど Googleスプレッドシートには機能としては無い スピン機能（スピンボタン）。こちらを GAS や 関数で 実装する方法を検証し、どのアプローチがベストなのかを 全4回にわたってお送りしてきました。
今回はスピンボタンシリーズの 番外編記事となります。
前回の記事
Q. A2セルの数字を増減させるスピンボタンを実装したい
このお題に対して、これまで 以下の4つの方法で検証をしてきました。
GAS でスピンボタンを作ってみる
モバイル対応 onEdit で動くスピン機能を考える
関数でスピンボタン出来るか試してみる
HtmlService使って ダイアログでスピン
前回は GASの HtmlServiceで ダイアログにスピンを実装という、スプレッドシートの限界突破、全王様もおったまげな 領域まで検証しちゃいました。
スピンボタンにどんだけ力入れるんだ？って感じですねｗ
色々な方法を 試してみましたが、mirの結論としては 手間が一番少なく 動作快適、その他諸々の利点のある 3番目の方法、関数でスピンボタンが最強では？ となったわけです。
でも、関数スピンボタンには致命的な欠点がありました。
今回のシリーズ最後の記事、スピンボタン 番外編では、その欠点を解決した改良版 関数スピンを実現できるか？をやってみたいと思います。
関数でスピンボタンの欠点
関数スピボタンの欠点、それは 早すぎるクリック（スピン）だと サーバー側の同期が追い付かず、操作画面上で表示されている数字とサーバー側で認識している数字にズレが生じることでした。
これによって、一度ブラウザを閉じて開きなおしたり、リロードした場合に
閉じる前に表示されていた数字と変わってしまう、つまり 数字が保存されない という問題が発生しました。
この操作画面上で表示される数字を なんとか取得できないものかと、GASを使う方法含め 色々と試行錯誤しましたが、手動で コピー + 値貼付けする以外に 方法はない！
と、ここまでが 第3回の検証でした。
この時点では解決策無しとしましたが、その後も なんか良い方法はないものか？と色々試していたんですが、ようやく解決策をみつけた（思い出した）ので、番外編で書くことにしたわけです。
↑ どんだけ暇なんだ？
関数スピンボタン 改良版
とりあえず 問題をクリアした 改良版 関数スピンボタンの動きを見ていただきましょう。
改良版の動作を確認する
上の画像のクリックスピードだと、改良前の関数スピンだと 拾い切れない分が 8個くらい出るので、リロード前 20だった 数字が リロードで 12 くらいになっていました。
この数字のズレが解消され、20という数字がリロード後も保持されているのがわかります。
サーバー側の数字を 閲覧用に別窓を立ち上げて動きを見ると、より分かりやすいですね。
左が閲覧用（サーバー側）、右が操作用（クライアント側）です。
早いクリックで 数字をスピンさせましたが、 右 21に対して 左は 9 と 半分も拾えていないのがわかりますね。
これが、プルダウンを ROCK に切り替えた瞬間に 左の 閲覧用（サーバー側）の数字が 右（操作画面）と同じ 21になっています。
もちろん 右をリロードしても 21が保持されています。
解決策：プルダウンを 使って 値貼付け風を実現する
プルダウンで ROCK を選択することで、なぜ サーバー側が 同じ数値になるのか？
それはプルダウンが 数式の結果を値化（値貼り付け）する方法の一つだからです。
Googleスプレッドシートにおいて 数式の結果を 値として固定化する方法といえば、
■手動で コピー + 値を貼り付け
■GASで getValue, setValue でする
この2つが思いつきます。
でも、実はもう一つ 違う切り口の方法があります。
■数式を入れたセル範囲を選択肢とした プルダウン で選択する
実は プルダウン で選択することで、数式を値にすることが出来るのです！
ちなみにこのプルダウンによる 数式の値化は、Excelでも同じように出来ます。
このテクニックは、以前 タイムスタンプ番外編で NOW関数と プルダウンを使った 簡易版タイムスタンプを検証した 記事でも紹介しました。（というのをすっかり忘れてました）
今回、これを サーバー側とのズレ問題の解決法として使っています。
改めて作り方を 順を追って説明しましょう。
関数スピンボタン 改良版の 作り方
以下の手順で作ります。（途中までは 改良版ではない 関数スピンボタンと一緒です。）
事前準備（シートの構成）
反復計算の設定
プルダウンの 選択肢の準備
チェックボックス、プルダウンの設定
条件付き書式設定
スピン式の設定
完成
1. 事前準備（シートの構成）
まずは スピボタンの枠を作っていきます。セル位置や 結合範囲、文字サイズ・色などは、 実際に使う状況に応じて変更・改良してください。
スピンさせる数値 を大きく表示させる為に、
A2:A4セルを結合
フォントを Arial サイズ 24
太字、文字色は黒
縦・横 中央寄せ
このようにしています。
B列は 少し列幅を細めにして
B2 チェックボックスを後で挿入する場所
B3 初期値 と入力
B4 増減値 と入力
以下のようになります。
C列は幅を少し広めにして
C2 プルダウンを後で設定する場所
C3 0 を入力（とりあえずの初期値）フォント Arial 10 文字色レッドベリー
C4 1 を入力（とりあえずの増減値）フォント Arial 10 文字色レッドベリー
後で消しますが、A2に数字をいれてどのように表示されるかチェックもしておきましょう。（色や文字の大きさを変えたい場合は調整）
これで枠（セルのバランス）はOK。
2. 反復計算の設定
次に 循環参照エラーを回避して反復計算を動かうための設定です。
Googleスプレッドシートの メニューから
ファイル ＞ 設定 ＞ 計算タブ で、
再計算を オン、最大反復回数を 1 としておきましょう。
3. プルダウンの 選択肢の準備
スピンモードを切り替える為の プルダウン選択肢用の セル範囲を準備します。
今回は E2:E4の3つのセルをプルダウン用範囲として、以下のようにしています。
E2 ▲PLUS
E3 ▼MINUS
E4 ="🔲ROCK "&A2 （数式）
スピン数値 のA2の前に 文字列 "🔲ROCK " を結合しています。
別に =A2 で数値そのままでもいいんですが、🔲ROCK となっていた方がユーザーとしては わかりやすいかなと考え、このようにしています。
モード切替のプルダウンは この 数式を使いたいので セル参照にする必要があり、同じシート内に選択肢のセル範囲を用意する必要があります。
本当は 別シートにしたいのですが、別シートに スピンシートの A2を参照する式を入れると 常に1つ前の 状態の数字 となってしまいます。
別シートだと 再計算のタイミングにズレが生じる為です。
同じシート内であれば参照による 数値のズレは発生しません。
4. チェックボックス、プルダウンの設定
まず B3セルを選択した状態で メニューから
挿入 ＞ チェックボックス で、 スピンボタン用のチェックボックスを作成。
さらにスピンモード切替用のプルダウンをセットする C2セルを選択して、
挿入 ＞プルダウン でサイドバーが開くので
範囲 C2
条件 プルダウン（範囲内）
（条件範囲） E2:E4
と設定しましょう。シート名は勝手につきます。
プルダウンの仕様変更（改悪）によって、デフォルトの表示が「チップ」になってしまいましたが、これは可読性が悪くイマイチです。
詳細オプションの箇所をクリックして、表示スタイルを「矢印」にすると従来のスタイルになるので、こちらを選択しておきましょう。
これでモード切替用の プルダウンが設定できました。
5. 条件付き書式設定
プルダウンの見栄えをよくして、わかりやすくする為に 「条件付き書式」を設定しましょう。
設定は以下の通り
■共通
範囲 B2:C2
書式のルール カスタム数式
設定1 プラスボタン用
=$C2="▲PLUS"
文字色：暗い青2 セル色：明るい青3
設定2 マイナスボタン用
=$C2="▼MINUS"
文字色：暗いオレンジ2 セル色：明るいオレンジ3
設定3 ROCK 固定ボタン用
=REGEXMATCH($C2,"🔲ROCK")
文字色：暗いグレー2 セル色：明るいグレー3
ROCKの選択肢は 後ろに A2セルの数値が付くので、 一致ではなく REGEXMATCH関数を使った「含む」場合に TRUEとなる式にしています。
色の設定は 好みなので、上の通りでなくてかまいません。
実際動かしてみて、色（書式）が反映されていることを確認しておきましょう。
6. スピン式の設定
いよいよメインとなる スピン式です。
=IFS(C2="", C3,
C2="▲PLUS",A2+C4,
C2="▼MINUS",A2-C4,
TRUE,REGEXEXTRACT(C2,"\d+")*1)
改良前の式から大きくは変わりません。IFSで分岐するところも一緒です。
分岐の意味は以下になります。
=IFS(
C2="", C3,
C2(モードプルダウン）が空欄なら 初期値 C3 にリセット
C2="▲PLUS",A2+C4,
C2(モードプルダウン）が▲PLUS なら
A2(自分自身のセル） + C4（増現値）
C2="▼MINUS",A2-C4,
C2(モードプルダウン）が▼MINUS なら
A2(自分自身のセル） - C4（増現値）
TRUE,REGEXEXTRACT(C2,"\d+")*1)
TRUE（上記の2つの条件以外）、つまり今回の場合は ROCK なら
C2 （つまり ="🔲ROCK "&A2 の結果）から 数字部分
A2部分の数字を 数値として返す
IFS関数に関しては、過去のチェックボックスの 番外編（小ネタ紹介）が一番詳しく書いてましたね。そちらを参照ください。
今回の一番のポイントは式の最後、ここです ↓
TRUE,REGEXEXTRACT(C2,"\d+")*1)
TRUE（それ以外、つまりこの場合は ROCK）なら
C2 （つまり ="🔲ROCK "&A2 の結果）から 数字部分
A2部分の数字を 数値として返す
REGEXEXTRACT関数は、正規表現でマッチした部分を抜き出す関数です。
"\d+" は正規表現で 文字列内の 1つ以上の半角数字の繰り返しを意味します。
つまり REGEXEXTRACT(C2,"\d+") は、C2セル（プルダウン）から 数字部分だけを抜き出す 式となっています。
ただ REGEXEXTRACTの結果は 文字列扱いなので、抜き出した 数字を数値化する為に *1 をしています。
仮に A2（スピンセル）が 10だった場合、 C2のプルダウンで ROCKを選択すると、C2は 🔲ROCK 10 となるので 数値部分は 10となります。
もともと 10と入っていたものに 一周回って 10を入れる 意味が分からないかもしれませんが、マネーロンダリング をイメージしてくださいｗ
マネーロンダリング（資金洗浄）とは？
犯罪行為によって得た現金から、出所を消し、正当な手段で得た資金と見せかけることである。
スピンボタン連打で数字を増やした 状態の A2セルの 10は サーバー側では認識されていない、いわば 非合法（ルール無視で高速処理で得た）状態の数字です。
これを上の流れで プルダウンによって 値化したものから 再度数字部分を取り出し A2セルにセットすることで、サーバー側で認識された 合法な（固定化された）数字に生まれ変わらせているのです。
C2 でプルダウンを選択する部分が、いわば 報酬は スイス銀行へ振り込め って感じですかねw
7. 関数スピンボタン 完成版 完成
これが ROCKにした瞬間に 左の閲覧用（サーバー側）の数字が 右と同じになる 理由だったわけですね。
循環参照で 一周したら値になるってのが、なんだか頭が混乱しそうですが、まさに Googleスプレッドシートの法（ルール）の抜け道をつくような、ロンダリング手法ですねｗ
最後に プルダウン用の範囲（列）は 非表示としておくと良いでしょう。
関数プルダウン 改良版 完成しました！
関数スピンボタン 改良版 まとめ
最後に今回の 関数スピンボタン 改良版で 実現できること、その他 メリット・デメリットをまとめましょう。
・A2セルの数字 スピンボタンで 増減させたい → OK
（以下は追加要件）
・できれば Excelみたいに サクサク動かしたい → ◎（早い）
・できれば スマホ（アプリ版）でも使えるとよい → 〇
・増減の値は カスタムできるとよい。 5ずつとか。 → 〇
・セル固定ではなく、アクティブセルでスピン機能使えたりする？ → ×
（その他のメリット ）
・圧倒的なサクサク感
・GAS不要で簡単に使える
・スピンセルは保護しても良い
・チェックボックス、プルダウンなので 全てキーボード操作可能
・スペース押しっぱなしで高速スピン可能
（その他の注意点）
・厳密には増減のボタンではなくプルダウンでのモード切替
・モード切替忘れがありそう
・関数スピンボタンを1つのシート内に複数設置したい場合は工夫が必要
セルの保護に関してですが、スピンした数値が入る計算セル A2は 保護をかけても大丈夫です。ただし プルダウン やチェックボックスは ユーザーが操作するので 保護できません。
誤操作で チェックボックス自体を消さてしまうリスクはあります。
おまけの利点：キーボードショートカットで操作できる ＋ 高速スピン
キーボードでの操作性を考えると、プルダウン と チェックボックスの配置が良くなかったですね。 プルダウンを エンターで選択できるように チェックボックスをプルダウンの下に配置するのが正解かも。
プルダウンを選択して左に移動したいので、Shift + Tab を使っています。
また、チェックボックスは スペースキーで ON/OFF 切り替えできますが、チェックボックス上で スペースキー 押しっぱなしとすると、連続 ON/OFF切り替えとなり 高速スピンが 可能です。
スプレッドシートの負荷的に Googleさんに怒られそうで不安になりますがｗ
番外編を含め 全5回にわたって
Googleスプレッドシートにプルダウンを実装する
をテーマに検証してきました。
基本の SpreadsheetApp のスプレッドシート上でボタンに GASを割り当てたスピンボタンから、onEditとチェックボックスを使った編集時トリガーによるスピン、そして 反復計算・循環参照 とチェックボックスを組み合わせた 関数スピン。さらに HtmlServiceを使ったダイアログスピンと、様々な角度から検証を行い
最もコスパのよさそうな 関数スピンの弱点である、
サーバー側とのズレ を解決した
循環参照 + プルダウンによる 数値化ロンダリング
と 今回も数多くの 秘蔵？テクニックや 関数が登場しました。
聖闘士 星矢 （もしくは リングにかけろ）くらい、必殺技を叫んだら 相手が吹っ飛ぶ のオンパレードだったんじゃないでしょうか！
数値化ロンダリングって書いてて、なんか 語感が ギャラクティカマグナム っぽい響きだなと思ってこんなたとえにｗ
Googleスプレッドシートにおいてスピンボタンは、さほど役に立つとも 需要があるとも思えませんが、学び や 知的好奇心に繋がる noteとなっていれば幸いです。
シリーズものは疲れてきたんで、来週からの平日更新は 少し単発ネタ（お題形式）を やっていこうかなと思います。
この記事が気に入ったらサポートをしてみませんか？