Googleスプレッドシートで使えるスピンボタン検証 -2 onEditを使う方法

2023年1月25日 21:23

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

今回はその2です。

前回の記事

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

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

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

今回のスピン機能お題の要件

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

GAS でスピンボタンを作ってみる
モバイル対応 onEdit で動くスピンボタンを考える
関数でスピンボタン出来るか試してみる
HtmlService使ってダイアログでスピン

前回の 1の方法は、結果としてはイマイチでした。
今回はどうでしょうか。

A2.モバイル対応 onEdit で動くスピンボタンを考える

前回の図形にGASを割り当てて実行ボタンとする方法は、スマホのアプリ版では機能しませんでした。

では、スマホアプリ版でGASを動かすにはどうすればよいか？

以前タイムスタンプの記事でも紹介した、編集時トリガー であればスマホアプリ版からでも GASが実行できます。

おなじみのシンプルトリガー onEdit を使う方法を、今回はスピンボタンに応用してみましょう。

GAS実行ボタンはチェックボックスで代用 するのが良いですね。

クリックやタップでチェックを付けたり外したり出来るので、そのまま UP / DOWN の実行ボタンとして利用できます。

チェックボックスと onEditの組み合わせは、一括 ON/OFFネタでも取り上げています。

Googleスプレッドシートのチェックボックスは、セル内のオブジェクトという扱いなので、セルの位置情報も検知できるし、GASからも制御しやすくてとても便利です。

完成イメージを用意して、コードを書いてみよう

今回の onEdit スピンは、以下のような完成イメージです。
これを実現するコードを書いてみましょう。

onEditでチェックボックスをスピンボタンとして使う GASコード

↓ コードはこんな感じ

// チェックボックス + onEditを使ったスピン機能
// スピンセルは A2固定

function onEdit(e){
  const upButtonRange = "B2" //アップボタン セル位置
  const downButtonRange = "B3"; //ダウンボタン セル位置
  const spinRangeAdd = "A2:B3"; //操作範囲
  const targetSheet = "スピン2";　//使用するシート名
  const num = 1;　//増減値

  const range = e.range;
  const rangeAdd = range.getA1Notation();
  const sheet = range.getSheet();
  
  if(sheet.getName() != targetSheet) return;
  if(rangeAdd != upButtonRange && rangeAdd != downButtonRange) return;

  const flag = rangeAdd == upButtonRange? 1 : -1 ;
  const spinRange = sheet.getRange(spinRangeAdd);
  spinRange.setValues([[spinRange.getValue() + num * flag,false],["",false]]);  

}

今回のコードのポイント

前回の図形を実行ボタンとして使うスピンボタンのコードとは少し違いますね。ポイントだけ少し解説します。

if と三項演算子で条件分岐

トリガーイベントの e から変更があった位置を A1形式で取得( rangeAdd )
、同じく変更のあったシート名を取得することで、

  if(sheet.getName() != targetSheet) return;
  if(rangeAdd != upButtonRange && rangeAdd != downButtonRange) return;

シート名がスピン2ではない編集＝スピン2以外のシートの編集
スピン2 の B2、B3 以外のセルの編集

これらは処理を実施せずに終了としています。

たまに GASの実行回数制限を気にして

「〇〇というシートが編集された時だけトリガー実行させたいが、どうすればよいか？」

といった質問を見かけますが、残念ながら「編集時トリガー」は編集されたら漏れなく発動します。 特定の条件で発動させることは出来ません。

どんな編集でも編集時トリガーでGASが実行されるなら、対象のシート、対象のセル以外の編集（処理を実行しない条件の場合）は、 いち早く終わらせることが大切です。

そして、このif文を通過した時点で編集されたセルは、targetSheet （スピン2）の B2セルかB3セルのどちらかとなります。

  const flag = rangeAdd == upButtonRange? 1 : -1 ;

ということは、この 三項演算子で

変更されたセル rangeAdd は B2
flag ・・・ 1

変更されたセル rangeAddが B3 （upButtonRange ではない）
flag ・・・ -1

とさせることが出来ます。

この flagを使って数値を プラスするかマイナスするかを制御します。

あえて複数セル範囲に getValue

const spinRange = sheet.getRange(spinRangeAdd);

spinRange は、A2:B3の範囲となります。

通常は単体セルの値を取得する場合は getValue
複数セル（範囲）から値を二次元配列で取得する場合は getValues

という使い分けですが、あえて

spinRange.getValue()

として、A2:B3の範囲に対して getValue を使うことで範囲の左上の起点セル（A2）の値、つまりスピンさせる数値の 4だけを取得しています。

※複数セル範囲に対して getValueを使うと範囲の左上の起点セルの値を取得します。

spinRange.getValue() + num * flag

このようにすることで、スピンさせた（A2セルの数値に numを加算または減算させた）数値を生成しています。

最後のspinRangeへの setValuesは二次元配列を用意してあげる必要があるので、

↑ のようなイメージの二次元配列が必要となります。これが、

[ [ spinRange.getValue() + num * flag , false ] , [ "" , false ] ]

の部分です。

チェックボックスの ON/OFF は true, false を書き込めばOK。

もうちょっと可読性の高いコードの方が良いかも・・・と思いますが、前回のGASによるスピンの動きがあまりにもモッタリしていたトラウマがあります。

なるべくget、set系メソッドを使わないことで、少しでも処理速度を上げようという涙ぐましい努力をしているわけですｗ

onEditで動かすスピンボタンを試してみよう

onEditなので、スクリプトの承認は不要なのが楽ですね。
早速動かしてみましょう。

もったり感はあるものの、ボタン実行の時に比べ動きはよくなった印象。

しかも、うざかった「スクリプトを実行しています」の文字が表示されないだけでも、だいぶマシじゃないでしょうか？

チェックボックスをボタンっぽく見せる為に、条件付き書式で ▲PLUS と ▼MINSU の表記をチェック入りの時は濃く表示させてます。

こんな感じのちょっとした工夫があると映えますね。

早いクリックに対応できるか試してみよう

もう少し早いクリックも試してみましょう。

上記は 1秒間に2回くらいのペースで、10回 ON/OFFを繰り返しています。

うーん、クリックに処理が追い付いてなのかなと。本来 10となるはずが最終的に 1回拾いきれなかったようで、 9で終了しちゃいました。ボタン実行よりは 良くはなってるけど、惜しい感じです。

体感的には、3連打までなら早いクリックでも大丈夫そうです。

スマホ（アプリ版）でも動かしてみよう

スマホでの動作を確認してみましょう。画像は Androidですが、今回のスクリプトであれば iPhone版も同じ挙動です。

サクサクとは言えませんが、パソコンのブラウザで動かした時と、ほぼ同様の感覚で使えます。なかなか良いんじゃないでしょうか。

出来たこと、出来なかったこと、その他のマイナスポイントをまとめてみましょう。

A2.モバイル対応 onEdit で動くスピン機能で、実現できたこと＆注意点

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

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

（その他の良い点）
・スクリプトの承認が不要（ユーザーへの展開が楽）
・条件付き書式と組み合わせて、ボタンっぽく表示出来る

（その他の注意点）
・チェックボックスを削除、または移動させられる可能性がある
・連打するとスピンが追い付かないことがある

A2.モバイル対応 onEdit で動くスピン機能まとめ

結果としては、ボタンにスクリプトを割り当てるよりもだいぶ良いですね。

特に、例の「スクリプトを実行しています」表示がないのと、ユーザー毎に初回の承認がないってだけでも、だいぶストレスが軽減されますし、ユーザーにも展開しやすいです。

そしてスマホ対応している点も高評価。

注意点としては、ユーザーがチェックできる = チェックボックスを保護できないってことで、誤操作でチェックボックス自体が消されたり移動されたりするリスクがあるってことでしょうか。

連打しなくてよい使い方なら、悪くないと思います。

でも、やっぱり Excelスピンのサクサク動作と比べちゃうと・・・。
これはGASの 「限界」なのか。

今回は onEditで動かすスピン機能を実装してみました。

悪くはないですが、よりサクサク感を求めて次回は 関数で動かすスピンを検証してみましょう。

関数でスピンさせる為に、またまた例の禁術に手を出すことに！

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

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