【チェックボックス2】厳密な一括 ON/OFF切り替え【GASでやろう】

mir

2022年11月16日 18:13

前回のチェックボックスネタの続きです。今回も GAS回です。土日更新の方も今のシリーズはGASなんで、タイミングがかぶっちゃいましたね。。

前回の記事

GoogleスプレッドシートのチェックボックスをGASで制御する

前回リージョンという考え方と、スペースキーを使ったチェックボックス一括ON/OFF、さらにその動きを GAS化しました。

ただ、GASのコードはスペースキーでの挙動と完全に同じではなく、例外的なケースで思うように作動しないことが判明しました。

今回は面倒がらずにチェック状態を厳密に判定して、正しく一括チェック ON/OFFが実行されるコードを書いてみましょう。

チェックボックスに関連するメソッドを確認する

厳正なチェック判定をする為に、改めて適したメソッド がないか調べてみましょう。

本家のリファレンスを見るのがベストなんですが、チェックボックスのような括りでまとまってはいないのと英語に抵抗感のある人もいるでしょうから、今回は「快ブログ」さんを見てみましょう。

うーん、RangeList （複数のとびとび範囲）に対して checkや uncheck が有効なのはわかりましたが、判定系はやはり isChecked しかないですね。

先週も書いた通り、isCheckedはチェックあり、なしが混在するケースの時は nullを返すんですが、チェックボックス以外のセルが範囲に含まれている場合も同じく nullを返すので、文字や数値、空白セルを含むリージョンにそのまま使えません。

その前段階で「チェックボックスかどうか？」といった判定処理に適したメソッドがあれば良さそうな気がしますが・・・。

入力規制関連のメソッドに getDataValidations というそれっぽいのがありますね。最後に sが付いてるので 範囲を一括で処理できそうでいい感じです。

さらに、その下 DataValidation クラスを見ていくと

それっぽいのがありました。 getCriteriaType が使えそうです。
でも、DataValidationCriteria列挙体 ってなんだ？？

その記述の少し上に公式へのリンクがあるので、そこから飛んで

ここにたどり着きました。 列挙型 Enum(イーナム） ってやつですね。
その中に・・・

これこれ！

↑ データ入力規制の呼吸チェックボックスの型もありました～。

サンプルコードを見る限り、なんとなく判別方法も理解できますね。
ここの DATE_BETWEEN の箇所を CHECKBOX とすれば良さそうです！

こんな感じでメソッドや書き方がわからない処理は、公式リファレンスや日本語の解説サイトを調べながら進めていくと良いです。

もちろん、この後に シンプルなテストデータでのログや挙動をトライ＆エラーしながら確認・検証していく必要があります。(この部分はロッキーだと修行シーンなんでロッキーのテーマをBGMにダイジェストが流れる感じ）

そして試合当日・・・　（って感じですね）

チェックボックス判定の流れ

上記のメソッドの動作を検証した結果、以下のようにコードを記述すれば、厳密な判定でのチェックボックス一括ON/OFF が出来そうだとわかりました。

■判定から一括処理までの流れ
1. 前回と同じく範囲（リージョン）を取得
2. getDataValidations でデータ入力規制を二次元配列で取得
3. 二次元配列から一つずつ dataValidation を取り出すループ処理
4. 入力規制が設定されていない場合 dataValidationは null となる。
　null 以外が対象
5. getCriteriaType で CHECKBOX 判定。チェックボックスのみが対象
6. チェックボックスである単体セルをリージョン内から取得
7. 単体セルに対して isChecked をする（必ず true か false になる）
8. false であれば、その時点でチェック終了 true ならループを繰り返す
9. false が一つでもあればリージョンに対して checkd（一括ON）
　一つもなければ uncheck（一括OFF）

ステップが多いですが、思ったよりはシンプルに書けそうですね。

スペースキー一括チェック ON/OFF GAS化完全版

これを実際のコードにしてみましょう。

コードはこれ（コピペでも使えるけど理解してね）

//厳密判定の一括チェックボックス ON/OFF
function allcheck2(){
  const sheet = SpreadsheetApp.getActiveSheet()

  //1. 前回と同じく 範囲（リージョン）を取得

  //アクティブセルの所属するリージョンを取得
  let region = sheet.getActiveRange().getDataRegion();

  //リージョンのA1表記の範囲情報を取得
  const add = region.getA1Notation();

  //リージョンが複数セルなら A1:B2 といった形で ":" が含まれる。
  //含まれない場合 = 単体セルの場合は DataRnage に差し替え
  region = add.includes(":")? region : sheet.getDataRange();

 //↑ここまでは前回と一緒

  //2. getDataValidations で データ入力規制を 二次元配列で取得
  let dataValidations = region.getDataValidations();

  //ループ前に判定の初期値を設定。 trueとする
  let flag = true;　

  //3.二次元配列から 一つずつ dataValidation を取り出すループ処理
  rowLoop:
  //縦方向ループ
  for(i=0; i<dataValidations.length; i++){
    //横方向ループ
    for(j=0; j<dataValidations[0].length; j++){
      const dataValidation = dataValidations[i][j];

      //4. 入力規制が設定されていない場合 dataValidationは  null となる。null 以外が対象
      if(dataValidation != null){

        //5. getCriteriaType で CHECKBOX 判定。 チェックボックスのみが対象
        if(dataValidation.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX){

          //6. チェックボックスである 単体セルを リージョン内から取得
          //7. 単体セルに対して isChecked をする （必ず true か false になる）
          if(region.getCell(i+1,j+1).isChecked() === false){
            flag = false;　//フラグを falseに差し替え

            //8. false であれば、その時点で チェック終了 true ならループを繰り返す
            break rowLoop;
          };
        }
      }
    }
  }
  //9. false が一つでもあれば リージョンに対して checkd（一括ON）　一つもなければ uncheck（一括OFF）
  flag? region.uncheck() : region.check() ; 
}

前回の getValues判定に比べると少し長いですね。
本職のエンジニアからしたら、この程度で長いとかぷぷぷーｗでしょうが。

解説1： for ループのネスト（入れ子）記述

for ループのネスト（入れ子）記述は、Googleスプレッドシートの二次元配列を処理する際によく使われます。

//縦方向ループ
for(i=0; i<dataValidations.length; i++){
　//横方向ループ
　for(j=0; j<dataValidations[0].length; j++){
　　const dataValidation = dataValidations[i][j];

この部分

基本なのでしっかり理解しましょう。

解説2：取得した範囲からさらに単体セルを取得する

「6. チェックボックスである単体セルをリージョン内から取得」

この一度取得した範囲（Range）内から、さらに位置を指定して 特定のセルを取得する処理は、 rangeクラスの getCellが使えます。
※offset でもアリ

getCell を使う際、配列では 0スタートですがセル取得の際の引数（行番号、列番号）は、1スタート であることに注意です。

なので、region.getCell(i+1,j+1) と +1 しています。

getRange と getCell があるけど、Range と Cellってなにが違うのー？？

と思うかもしれませんが、

getRange()　sheet Class（シートに対して使うメソッド）
・ A1表記か行、列、高さ、幅という数値で取得
・単体セルだけでなくセル範囲も取得できる

getCell()　Rnage Claass (シート直ではなく範囲に対して使うメソッド)
・範囲内の行番号、列番号を指定して取得
・単体セルを取得する（範囲は取得できない）

こんな違いがあります。普段使うのは getRangeの方ですね。

今回のような取得した範囲からさらに単体セルを取り出すケースでは、 getCellが使えるってのを覚えておくと便利。

解説3：ラベルと break で一気にネストループを脱出

for ループ開始前の rowLoop: は「rowLoop」という名前のラベルを設定する記述です。

今回の場合は 1つでもチェックボックス OFFが見つかったら check(
全チェックボックスON）処理をすればいいんで、処理速度を上げる為にも見つかった時点で ２段階ループから抜けたいわけです。

そのため、外側（縦方向）の for文にラベル rowLoop を設定し、
チェックOFFがあったら break rowLoop で一気にラベルの外へ脱出しています。

解説4：フラグ切り替えで最終的な処理を分岐

最終的な処理を分岐させるために、 forループ前に flag を用意しています。1つでもチェックOFFのチェックボックスを見つけたら falseに切り替える、切り替えスイッチ（鉄オタ向けにいうと、どっちのホームに入るかを切り替えるポイント）みたいなイメージ。

for ループ内の

if(region.getCell(i+1,j+1).isChecked() === false){

この if の中で region.check() として return で終了してもよいんですが、判定後の処理が長いコードの場合が今後出てくることもあるので、学習も兼ねてフラグ切り替えを使った記述にしました。

この手の切り替えの記述を使うケースはよくあるんで、似たような処理の際は積極的に活用してみましょう。

最後に flagの結果（true,false) に応じて check または uncheck どちらかの処理を実行します。ここは前回も登場した 三項演算子です。もちろん if 文で記述しても良いです。

なんとなく全体感（なにをしているか？）が理解できれば、とりあえずは OKです。

一括チェックボタン完全版の動作確認

それでは、実際に問題なく動くか動作を検証してみましょう。
ボタンに割り当てるスクリプトを allcheck2 に差し替えて動かしてみます。

1. 通常のチェックボックスでの動作

こちらは前回同様、問題なく動いています。

処理が増えたので速度低下が心配でしたが、このくらいのデータ量なら実行時間も気にならないですね。

2. 例外的なチェックボックスでの動作

次に前回作成のコード getValuesして FALSE判定では正しく動かなかった、例外的なケースを試しましょう。

Q3 のチェックボックスをカスタムセル値として

チェックあり　・・・　like
チェックなし　・・・　notLike

と設定しました。G5:G10 に中身を出力させています。

さらに、範囲内の E15セルを チェックボックスではない FALSE を置いています。

こちらも問題なく動いてますね。厳密判定が機能してます。
コードが完成しました。

ただ、若干切り替わりまで時間がかかっているような・・・。

3. 大量データデータのチェックボックスでの動作

では、チェックボックスが 1000行、となりの列に文字列というちょっと大きめの範囲を対象とした場合の処理時間はどうでしょうか？

全チェックボックスががOFFから一括チェックONは早いですが、その後の 1000行目付近のチェックボックスのみがチェックOFFのケース、そして全チェックボックス ONから一括チェックOFFとするケースは、いずれも結構処理に時間がかかっています。

エディタ上の実行ログで処理時間を確認すると、5秒もかかってますね。これは、チェックボックスが 1000あって全てチェックONの場合は、その1000セル全てを一つひとつ getCellして判定している為です。

1つでもチェックOFFがあればループを抜けるので、全てチェックOFFの時は１つ目のチェックボックスでループ終了なんで処理が早いというわけです。

スペースキーの場合は、大量データに対しても一括ON 一括OFFどちらも処理時間に大きな差はないので、GASの処理と結果は同じでも処理の仕方が違うのかなと思います。（サーバー側の処理じゃない？？）

でも、これ以上はちょいコード書ける程度の mir じゃわからないですね。

まぁ手動操作を前提としたチェックボックスで大量データを扱うケースはあまり無いと思うので、そこまで気にする必要はないかと。

チェック判定をなくしてボタンを分ければ大量データでもＯＫ

どうしても大量データのチェックボックスで一括ON/OFF をしたい場合はどうすれば良いか？

そもそもスペースキーの挙動と同じ動きを GASで再現しようと、頑張ってチェック判定部分の記述を試行錯誤してきましたが、もっとシンプルに一括ONと一括OFFで実行ボタン（関数）を分けちゃうのが簡単ですよね。

大量データを扱うケースも チェック判定で時間がかかっているわけですから、単純にチェックON、チェックOFFの処理のみとすれば速度も向上するはず。

判定なしチェックON、チェックOFFコード

//実際の処理コード
function allCheckSwitch(flag){
  const sheet = SpreadsheetApp.getActiveSheet();
  let region = sheet.getActiveRange().getDataRegion();
  const add = region.getA1Notation();
  region = add.includes(":")? region : sheet.getDataRange();

  flag? region.uncheck() : region.check() ; 
}

//一括ON
function checkOn(){
  allCheckSwitch(false);
}

//一括OFF
function checkOff(){
  allCheckSwitch(true);
}

ベースとなるのが allCheckSwitch 、リージョンの範囲判定部分の記述は今までと同じです。

チェック判定の必要はないので、あとは checkOn, checkOff の関数内で引数として false,trueを渡してあげるだけ。

だいぶシンプルになりました。

一括ONボタン、一括OFFボタンを使ってみる

checkOn, checkOff をボタンに割り当てて使ってみましょう。
※ 関数名の大文字小文字は判別されます。注意。

だいぶ早くなりましたね。これでも十分かもしれません。

小ネタのわりには 2回にわたって、チェックボックスの一括ON / OFF の記事をガッツリ書いてしまいました。

チェックボックスの一括 ON/OFFの GAS処理として記事を書いてきましたが、前回のリージョンという考え方、そして今回コードを書いた厳密なチェック判定を組み合わせると、Googleスプレッドシートには標準機能では無い チェックボックスのラジオボタン化 が GASで実現できそうです。

次回、チェックボックスの最後の記事で GASによるラジオボタン化 に挑戦してみましょう。

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

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

【チェックボックス2】厳密な 一括 ON/OFF切り替え 【GASでやろう】

Googleスプレッドシートの チェックボックスをGASで制御する

チェックボックスに関連するメソッドを確認する

チェックボックス判定の流れ

スペースキー一括チェック ON/OFF GAS化 完全版

コードはこれ （コピペでも使えるけど 理解してね）

解説1： for ループのネスト（入れ子）記述

解説2： 取得した範囲からさらに 単体セルを取得する

解説3：ラベル と break で一気に ネストループを脱出

解説4：フラグ切り替えで 最終的な処理を分岐

一括チェックボタン 完全版の動作確認