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

mir

2023年2月8日 22:20

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

前回の記事

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

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

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

これまでの 3回の検証を通じて、Excelのスピンボタンに近いサクサク感（スピード）を出す為には、サーバー側の処理である GASでは難しく、クライアント側（ローカル）での処理とする必要があるってことが確認できました。

GASのスピンボタンの処理

1.スピンボタンクリック
↓
2.サーバー側に処理を投げかける（GAS ここが遅い）
↓
3.サーバー側から処理が返ってくる（GAS ここも遅い）
↓
4.対象セルの数値が増減する（結果モッタリした動きに）

1回目、2回目の GASを使った方法だと、スピンと連動する数字の変更が、GASによるサーバーサイドの処理だったので、どうしてもモッタリ感が出てしまいました。

関数のスピンボタンの処理

1.スピンボタン(チェックボックス）クリック
↓
2.再計算で対象セルの数値が増減する （ここはローカル処理で早い）
↓
3.増減した計算結果をサーバー側と同期・自動保存（ここが遅い）
↓
結果として連続でクリック（1，2を繰り返）した場合に 3の処理が追い付かず、サーバー側の数字とズレが発生

一方、前回（3回目）の関数スピンボタンの場合は、計算部分はクライアント側でサクサク処理されるものの、サーバー側の同期（保存）処理が追い付かず、 サーバー側の認識している数字とブラウザ表示されている数字にズレが生じてしまいました。

早いクリックでスピンさせた場合、一度閉じて開きなおすと数字が変わってしまうという点が、どうにも解決できない悩ましい問題。

この点を許容すれば、関数スピンはもっとも動きが早い（サクサクな）理想的スピンボタンなのですが・・・。

というわけで、少し見方（アプローチ）を変えてみましょう。

A4.HTMLServiceでスピンボタン出来るか試してみる

1.スプレッドシートの対象セルの数字を HTMLにわたす（GAS）
↓
2. HTMLのフォーム上のスピンボタンをクリック
↓
3. スピンに合わせ数値が増減する（<input>の type "number"の機能）
↓
4. OKボタンで変更後の数値を取得しスプレッドシートの対象セルに戻す
（Javascript → GAS）

このように、スピンボタン連打の部分だけクライアントで処理 して、最初と最後のセルの数値の取得、処理後の数値の書き込みだけ GASによるサーバー側の処理としたら良さげじゃない？

ってのが、今回のテーマです。

これを実現する為にGASの HTML Service を利用します。

さて、悩ましいのは HTML Serviceまで持ち出して、どこまで詳しく解説するか・・・。

HtmlServiceを使ったスピンボタン機能の動作

とりあえず完成形（答え）から先に。

動作の流れとしては

スピン対象の数値の入ったセルを選択
メニュー＞特殊操作＞スピンをクリック
ダイアログが立ち上がり選択しているセルの数字が入力フォームに入る
マウスポインタを近づける（フォーカスをあてる）とスピンボタン登場
クリックで入力フォームの数字が増減する
OKボタンを押すとダイアログが終了
フォーム上で変更した数字が選択したセルに入る

こんな感じ。

スピンの動作はクライアント側で動かしてるんで、サクサクとクリックに合わせてスピーディーに数字が動きますね。

いい感じです！サクサク食感触感です！

HtmlServiceを使ったスピンボタン機能のコード

コードは GASとHTML を分けて書く必要があります。
エディタでファイル追加でHTMLをファイルを用意しましょう。

GAS側（コード.gs）

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getActiveRange();
const stepNum = 1; //スピンの増減値

//オープン時のメニュー追加
function onOpen() {
  const ui = SpreadsheetApp.getUi();         
  const menu = ui.createMenu('特殊操作');  // メニューを作成
  menu.addItem('スピン', 'spin');   // メニューにアイテムを追加
  menu.addToUi();
}

//アクティブセルの数値取得～ダイアログ生成・表示
function spin(){
  const startNum = range.getValue();

  //数値以外の時の処理
  if(isNaN(startNum)){
    return Browser.msgBox("数値じゃないよ");
  }

  let html = HtmlService.createTemplateFromFile('dialog');
  html.startNum = startNum;
  html = html.evaluate();
  SpreadsheetApp.getUi().showModalDialog(html, "HTMLServiceスピン");
}

//ダイアログで処理済みの数値を戻す
function returnValue(num){
  range.setValue(num);
}

HTML側（daialog.html)

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <style>
      .input{
        font-size: 130%;
        width: 20%;
      }
    </style>
    <div>
      <input class="input" type="number" style="text-align: center;" id ="spin" step="<?=stepNum?>" value="<?=startNum?>" >
      <button onclick="numSet()">OK</button>
    </div>
  </body>

  <script> 
    function numSet() {
      const num = document.getElementById("spin").value;
      google.script.run.returnValue(num);
      google.script.host.close();
    }
  </script>
</html>

もちろん初回実行の際は、GASの権限の承認が必要になります。

HtmlServiceを使ったスピンボタンのポイント

GASのHtmlServiceやHTML、Javascriptの部分を解説しだすと、とても1回じゃ終わらないのでさらっといきます。

GAS側の構成

冒頭のグローバル領域の下に 3つの関数を記述しています。

onOpen
シンプルトリガーでスプレッドシートを開いた時に実行される。
スピンダイアログを実行させる為のカスタムメニューを生成する用

spin
アクティブセルの値を取得し HtmlSeriveで中身を生成しスピンボタン入りダイアログを起動させる

returnValue
スピンした数字を HTML側から受け取り、アクティブセルに書き込む

onOpenでスプレッドシートにメニューを追加する

getUi による Uiクラスで、GAS（spin）を実行させる為のカスタムメニューを追加しています。

これを onOpen によって、スプレッドシートが開かれた時に実行させることで、メニューの一番右（ヘルプの右）にカスタムメニュー追加となります。

onOpenはトリガー設定不要で、スクリプト実行できる シンプルトリガーの一つです。

↑ メニュー追加は addMenuを使う方法もありますが、ドキュメントやスライドでも同様に使える getUi での記述の方がよさそうです。

多分古い記事なんで addMenuを使ってるのかと。
今回は onOpenの説明だけ参考にしてください。

これで 特殊操作＞スピン から、次の spin 関数が実行できるようになりました。

ダイアログってどうやって作るんだ？

二番目の関数 spin が HtmlSeriveを使って中身を生成してダイアログを立ち上げるものです。

ダイアログは、画面上でぴょこんと飛び出す アレですね。

ダイアログってどうやって作るんだ？ という方、その他諸々詳しく知りたい方は、こちらも 「いつも隣にITのお仕事」さんを参考にされることをお勧めします！

ダイアログを作る 3つの方法について書かれています。

一番簡単なのは Browserクラスで、たぶん私を含め初学者が一度は使ったことのある Browser.msgBox なんかがメジャーですね。

でも、出来ることが限られているので今回のスピンボタンには使えません。

スピンボタン作成には、ダイアログの中身を HTMLで書くことができる HTML Service を使う必要があります。

そもそもHTML Serviceってなんだ？

GASのHTML Serviceは、その名の通り GASからHTMLを生成するサービスです。なんと、サーバーを用意したり別のサービスを契約する必要なく、自分で作成したWebページや Webアプリを公開できちゃいます。

doGet関数を使ってデプロイすることでスプレッドシートとは別に、もしくはスプレッドシートをDB（データベース）とした Webページ、Webアプリも作れますし、スプレッドシート上で表示する、 サイドバーや ダイアログの中身を作る際にも使います。

HtmlServiceには幾つかメソッドがあるんですが、今回は HTMLを別ファイルで用意しており、かつ getValueで取得したアクティブセルの値を渡してHTMLのテンプレートを生成したいので、createTemplateFromFile というメソッドを使っています。

createHtmlOutput 系との違いは、過去の teratailの回答が参考になりました。

HTMLだとスピンボタン（スピン機能）が用意されてる

これが楽ですよね。

わざわざスピン「ボタン」を作ってそのクリックを検知して数値を連動して増やすJavascriptを書く必要はなく、最初から用意されいている

<input type="number">

これでスピンボタンが実装できちゃいます。

変更する数字の増減値や下限、上限といった設定も出来るので便利～。

ちなみに、マウスを近づけないとスピボタンが出てこないんですが、HTML側のCSSの部分に少し記述を追加してあげると、常時スピンボタン表示になります。

でも、スタイルの記述に関しては、画像掲載の為に見やすくするべく入れてるだけなんで、面倒ならこの部分は丸ごとなくても問題ないです。

    <style>
      .input{
        font-size: 130%;
        width: 20%;
      }
      /* スピンボタンを常時表示する */
      input[type=number]::-webkit-inner-spin-button,
      input[type=number]::-webkit-outer-spin-button {
        opacity: 1;
      }
    </style>

参考にしたサイト

増減値の変更への対応

GASのコードの冒頭、グローバル領域で stepNum を宣言し、HTML側では
スクリプトレットで呼び出すことで、input の step（増現値）として設定しています。

step="<?=stepNum?>"

スクリプトレットは、GAS → HTML間で変数の受け渡しをする際に使う記述です。

今回の場合は GAS側の

const stepNum = 1

↑ ここの数字を変更し保存すれば、増減値を変えることが出来るってことです。

アクティブセルへの対応

これも同じく GAS側のグローバル領域で

const range = sheet.getActiveRange();

としているので、選択したセル（アクティブセル）を自由にスピンさせることが出来ます。

数値以外のセルを選択した状態で、スピンダイアログを立ち上げた際のエラーを回避するため

  //数値以外の時の処理
  if(isNaN(startNum)){
    return Browser.msgBox("数値じゃないよ");
  }

という分岐処理を入れています。

isNaN は数値ではない時に trueとなります。

ここは面倒なんでシンプルな Browser.msgBox を利用ｗ

google.script.run で HTML側から GASを実行

HTML側の唯一の関数（Javascript）が、

  <script> 
    function numSet() {
      const num = document.getElementById("spin").value;
      google.script.run.returnValue(num);
      google.script.host.close();
    }
  </script>

これです。

上で書いた通りスピンによる数字の増減はスクリプトを使っていないのでシンプルですね。

処理としてはスピン後の数字を取得して numに格納。それを引数として GAS側の returnValue(num) を実行しています。

このHTML（クライアント）側から、GAS（サーバー）側を動かしているのが、

google.script.run の部分です。↓解説はこちらを。

最後にダイアログを google.script.host.close() で閉じて終了です。
これも解説は↓を参照で。

今回の場合は、別にGAS側の処理の成功を待たずに閉じちゃっていいので、
withSuccessHandler は使ってません。

一応、出来たけどコレどこで使うんだ？

さて、HTMLServiceによるスピンボタンが実装できました。

が、コレってどんな時に使えるんだろ？という根本的な疑問がｗ

スピンするのに、わざわざダイアログ立ち上げるのはちょっと手間ですし、前回の関数スピンのようなポ〇モン図鑑をめくるような使い方もできないし・・・。

セルが保護されている場合の動作

そもそも GASは実行者権限で動かしているので、仮にセルをユーザーが編集できないように保護をかけて、誤編集防止としてこのスピンを使いたいと思っても、最後の setValue() での書き込みができません。

↑ このような 警告を出す保護設定の場合は、スピンを使うことで 警告を出さずに数値変更できますが、誤操作防止としてはちと弱いかなー。。

あと、ダイアログはスマホアプリ版ではサポートされていないので、この方式は スマホ対応は不可ってのも悩ましいところ。

ダイアログ上でスピンで動かすポ〇モン図鑑を完結させる

じゃあ、スピンで動かすポ〇モン図鑑を HTMLServiceのスピンボタンで実現する為に、スプレッドシート上のポ〇モン図鑑データを丸ごと HTML側にわたして DBとして使ってみたらどうか？

節子、それスピンボタンやない。Webアプリや。

もう、スピンボタン実装って話じゃなくなってますね。。

これならダイアログじゃなくてデプロイして Webアプリ化して使った方がいいですｗ

【余談】AppSheet を使う方法も

さらに言えば、このようなシンプルな Webアプリならノーコードで作れる AppSheetあたりで実装してしまうのもアリです。

もともとスマホアプリを想定したものなので、スマホでの利用も快適。

ちょっとスピンボタンから脱線しちゃいましたね。

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

A4.HtmlServiceで動かすスピンボタンで、実現できたこと

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

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

　(その他のメリット）
・キーボードの上、下押しっぱなしで高速スピン可能

ダイアログ上ではありますが、 Googleスプレッドシートで使える サクサク動くスピンボタン が実装できました。

メニューからダイアログ開く
↓
スピンで数値変更
↓
ダイアログ閉じる（数値がセルに反映）

この手順が少し面倒かなーという気もしますし、スマホで使えない他、幾つか欠点はありますが、どうしてもスピンボタンで数値変更したい！という場合は一番アリじゃないでしょうか。

もちろん作成（コード）の部分は一番難しく、HTMLや Javascriptも書かないといけないので少しハードルは高いですが。

HTML Serviceを学ぶのには、ちょどいいくらいのレベル感かなと思います。

次回、スピンボタン番外編で、関数スピンボタンの欠点を解決！

全4回に渡ってGoogleスプレッドシートでスピンボタンの実装をテーマに書いてきました。

今回の HTMLServiceを使ったスピンボタンは、最後に登場しただけあって一番良さそうですが、GAS使わない人や GAS初級者にはコードがちと難しいですよね。

というわけで、mirのお勧めは前回検証した 関数スピンボタンだったりします。

アクティブセル対応は無理でも、スマホ対応や対象セルを保護しても動くという点がメリットですし、何より GAS使わないので一番簡単に実装できます。（当然ユーザー側の初回の承認も不要ですし）

その唯一の欠点、保存時（サーバー側）の数字のズレですが、その後検証を重ねて解決できる方法を見つけました！

次回 「スピンボタン番外編」として、関数スピンボタンの完成版を紹介して、スピンボタンシリーズを終えたいと思います。

いいなと思ったら応援しよう！

チップ大歓迎です。やる気がアップしますｗ