スプレットシートで色を数えるコードを作ってはみたのですが…。

前提・実現したいこと

当方、プログラミング等に関して素人でサイト等を参考に見様見真似で、スプレットシートのscriptと使用して、色を数えるものを作りました。
作成した関数をセルに入力し使用していますが、不備が多く、作成したコードが正しいかも把握できない為、困っております。
ご教授の程、宜しくお願いいたします。

発生している問題・エラーメッセージ

以下のような問題点があります。
■COUNTを一つ一つ処理することになるため、処理が遅い&重い。。。（一次元配列というのでしょうか？）
■COUNTする該当範囲を塗りつぶし以外の方法で何らかの操作をしないと処理を実行しない。

エラーメッセージ
特になし

該当のソースコード

java script
ソースコード

指定したセルの色を数える
@param {range} セル
@return {Number} 指定色
@customfunction

/
function countColor(countRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/((.),/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/,(.*))/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};

試したこと

別でCOUNTする該当範囲を操作するようなマクロを組み、トリガーを付けてやってはみたのですが、スマートとはかけ離れており、余計に重くなるだけの始末。。。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

退会済みユーザー

2021/05/14 12:04

＞作成した関数をセルに入力し使用していますがとのことですが、具体的にどういう関数を入力しているのでしょうか。単にセルの背景色を取得するだけならば、 var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim(); や var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim(); 等はいらないと思うのですが、これらの行はどういう動作を意図しているのでしょうか？

tttttt

2021/05/14 12:19

すいません、完全に素人に近いのでサイトに記載されてたコードを感覚で張り合わせただけなので作った私も理解できてません。記載したコードにcountColorと名前をつけ、それをセルに入力して試してはみました。下記のような方法です。＝countColor(範囲A2:AA2,数える色A1) 雰囲気で模索し、セルに入力すると一様は形にはなりましたが意味がわからないし不都合が発生するし重いしの始末です。。。

退会済みユーザー

2021/05/14 12:31

要件は「セル範囲と色を指定して実行すると、指定したセル範囲内の、指定した色のセルの数が返される」ような関数、という理解でよろしいでしょうか。

sawa

2021/05/14 12:52 編集

var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim(); の記載は、自作のGAS関数の引数でセル範囲の記載を取得する時に使う書き方ですかね。 GASでセル範囲をそのまま引数として使いたい場合 "A2:AA2" としてあげる必要があるところを、一般的なシート関数と同じように A2:A22と書いても動くように、この自作GAS関数を入れたセルから式を取得して、そこから正規表現で A2:AA2の部分を引っこ抜いているのかと。

tttttt

2021/05/14 14:33

qnoirさん、そういうことです。指定した色の数を指定した一列全てに返したい。説明が下手くそで本当に申し訳ありません。

tttttt

2021/05/14 14:36

sawaさん、なるほどです。。だからシート関数のように使えるのですね。まだ理解が追いついてはいませんが(-_-;) 処理を早くするにはどうすればいいでしょうか？良い方法があればご教示いただけると幸いです。

sawa

2021/05/15 00:32

実際に動かしてみましたが、書き方は置いといて動作としてはこんなもんかなって思います。GASの自作関数ってもったりした動きなんで。極端に遅いってことであれば、色の種類がいっぱいあって、この関数を同じシート内で多用してるか、もしくは対象となるセル数がとんでもなく多いとかでしょうか？

tttttt

2021/05/15 00:45 編集

同じシート内で多様しており、対象となるセルはD:AYまでの46列の複数行にわたります。こんなものなんですね(-_-;) ■COUNTする該当範囲を塗りつぶし以外の方法で何らかの操作をしないと処理を実行しない。上記の内容への対応策みたいなものはあるでしょうか？本当なら塗りつぶし直後に自作関数が動作してくれるのがベストなんですが。。。

退会済みユーザー

2021/05/15 00:51 編集

現状（2021年5月時点）では、Google spreadシートに関して「セルに色を塗るというアクションを検知して、スクリプトで何かを行わせることはできない」ようです。 onEdit()も、セルに色を塗るというアクションだけでは開始しないようです。 onChange()という関数はそもそも動作しません。（stackoverflowというサイトで複数のコメントがありました。） https://stackoverflow.com/questions/33337800/when-i-change-a-cells-background-color-how-can-i-get-all-cells-containing-the https://stackoverflow.com/questions/28637901/detect-background-color-change-in-google-spreadsheet-with-apps-script

tttttt

2021/05/15 03:57

そうですか。。。 sawaさんがチェックボックスの活用による打開策等も提示していただいたので何か良い方法を模索してみます。ご丁寧にURLまで提示いただきありがとうございます！！

行動規範の内容に同意します

回答1件

ベストアンサー

GASの速度低下でよくあるのが、forループ内で、getRangeやgetValue、setValueを多用することで、今回の記述では先に getBackgrounds で配列として取得した後に処理してるので、forのループで遅いってことは無いと思います。

逆にこの関数を多用しているのは問題で、対象範囲の中身（セル内のテキスト等）が変更された時に同時に再計算がされるので重くなっているのかと、もし添付の画像のような縦並びで色を集計するならば自作関数を修正してまとめて複数の色の数を配列として返す形にしてみてはいかがでしょうか？

あと、集計する側の色のところにチェックボックスなんかをつけておくと、チェックのON/OFFで関数が再計算されていい感じになるかと。残念ながら qnoir さんがコメントに書かれているように、色を変えて再計算されるようなものは出来ないです。（ダミーの引数に TODAY() なんかを入れて、定期的に再計算させる逃げ方はありますが）

■修正案　（コードは、あまり元の記述をいかせてません。。）

function countColor2(countRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();

  var rangeAddress = activeformula.match(/((.*)\,(.*))/).map(ad => ad.trim()).slice(1);
  var backGrounds = activeSht.getRangeList(rangeAddress).getRanges().map(r => r.getBackgrounds().flat(1));
  var colorCount = backGrounds[1].map(sc => backGrounds[0].filter(tc => tc==sc).length);

  return colorCount;
}

投稿2021/05/15 01:04

sawa

総合スコア3002

tttttt

2021/05/15 02:41

ご提案ありがとうございます！！元のコードは気にしないでください。コピーして少しいじった程度なので＾＾； >自作関数を修正してまとめて複数の色の数を配列として返す形にしてみてはいかがでしょうか？縦並びで色の数を返すのですが、一つ一つの色別に指定して配列として返すコードがド素人の為、恥ずかしながら思い浮かびません…。 ss getRange(返す場所指定？) copy？色の指定をどうしよう？こんな感じの知識レベルです。。。申し訳ありません。