こんにちは！
Yuki (@yukibnb) です。
今回はGoogle Apps Script(GAS)を使ってスプレッドシートのセルの背景色の数をカウントする方法を紹介します。
コピペで使用できるGASだけでなく、みなさん自身が自由にカスタマイズして使用できるようにポイントも詳しく解説します。
では見ていきましょう！
はじめに
今回実現したいこと
今回はこの画像のスプレッドシートをサンプルとして使用します。
赤色セルの個数をカウントして合計数をG2セルに入力するGASを作成します。
目視で数えると赤色セルは11個ありますね。GASを使ってG2セルに"11"と入力できれば成功です。
処理の流れ
今回記述するGASの処理は大まかに以下のような流れです。
- カウントしたいカラーコードを指定する
- カウント対象範囲の背景色のカラーコードを二次元配列で取得する
- カウント用の変数(値は0)を宣言する
- 取得した二次元配列をループし、要素が(1)のカラーコードと一致する場合、(3)のカウントに+1する
- (3)の変数に格納されている値をG2セルに記入する
文字だけ見るとわかりにくいかもしれませんが、後ほど個別に詳しく解説しますのでご安心ください！
コピペでOK！スプレッドシートの背景色の数をカウントするGAS
まずはGAS全体を見てみましょう。
★印の箇所を変更するだけで、みなさん自身のスプレッドシートに応用できます。
function countBgc() { //★スプレッドシートIDを入力してください var ss = SpreadsheetApp.openById("スプレッドシートIDをここに入力"); //★シート名を入力してください var sh = ss.getSheetByName("シート名をここに入力"); //★カウントしたいカラーコードを指定する //今回のテストでは"#ff0000"(赤色)を指定します var targetColor = "#ff0000"; //★カウント対象範囲の背景色のカラーコードを二次元配列で取得する //今回のテストではA1～D13セルの背景色のカラーコードを取得します var bgColors = sh.getRange(1,1,13,4).getBackgrounds(); //カウント用の変数(値は0)を宣言する var count = 0; //二次元配列bgColorsのすべての要素をZ字の順番でループする for(var r = 0; r < bgColors.length; r++){ for(var c = 0; c < bgColors[r].length; c++) { //二次元配列bgColorsの要素がtargetColorと一致する場合、 //変数countに+1する //toUpperCaseメソッドを使ってどちらのカラーコードも大文字にする if(bgColors[r][c].toUpperCase() == targetColor.toUpperCase()){ count++ } } } //↑↑ループはここまで↑↑ //★G2セルに変数countに格納されている値を記入する sh.getRange(2,7).setValue(count); }
関数countBgcを実行すると、無事G2セルに赤色セルの個数である"11"が入力されました。
では次にポイントごとに細かく見ていきましょう！
ポイント解説！
カウントしたいカラーコードを指定する
//★カウントしたいカラーコードを指定する //今回のテストでは"#ff0000"(赤色)を指定します var targetColor = "#ff0000";
まずどの色をカウントすべきかGASに教えてあげる必要があります。
今回は赤色セルをカウントしたかったので、赤色のカラーコード"#ff0000"を変数targetColorに格納しました。
"#ff0000"のように#とそれに続く6桁の英数字からなるコードをカラーコードと呼びます。「カラーコードから色」「色からカラーコード」を検索できるサイトは沢山ありますので、難しく考えなくても大丈夫です。
"#ff0000"をこちらのウェブサイトで確認すると、確かに赤色ですね。
【応用】カラーコードがわからない時
今回は赤色のカラーコードである"#ff0000"を直接GASに記述し、変数targetColorに格納しました。
ただし「カウントしたい背景色のカラーコードがわからない」「カウントしたい背景色がたくさんあるのでカラーコードを直接GASに記述するのは非効率」という場合もあります。
その際は以下のようにgetBackgroundメソッドを使って、カラーコードをスプレッドシートから取得し変数targetColorに格納すると便利です。
//★スプレッドシートIDを入力してください var ss = SpreadsheetApp.openById("スプレッドシートIDをここに入力"); //★シート名を入力してください var sh = ss.getSheetByName("シート名をここに入力"); //F2セルの背景色を取得し、変数targetColorに格納する var targetColor = sh.getRange(2,6).getBackground();
GASを実行する前にまずスプレッドシートのF2セルの背景色を手動で赤にしておきます。
そしてGASを実行し、getBackgroundメソッドを使ってF2セルの背景色のカラーコードを変数targetColorに格納しました。
この方法を使用すれば赤色のカラーコードは"#ff0000"ということを知らなくても問題ありませんし、わざわざカラーコードを調べる必要もないので便利です。
getBackgroundメソッドについて詳しく確認したい方は当ブログのこちらの記事をご覧ください。
カウント対象範囲の背景色のカラーコードを二次元配列で取得する
//★カウント対象範囲の背景色のカラーコードを二次元配列で取得する //今回のテストではA1～D13セルの背景色のカラーコードを取得します var bgColors = sh.getRange(1,1,13,4).getBackgrounds();
カウント対象範囲(今回はA1～D13セル)の背景色のカラーコードをgetBackgroundsメソッドを使って取得し、変数bgColorsに格納しました。
getBackgroundsメソッドを使うと背景色を二次元配列で取得するため、変数bgColorsの中身は二次元配列となります。
getBackgroundメソッドは背景色のカラーコードを文字列で返すのに対して、getBackgroundsメソッドは背景色のカラーコードを二次元配列で返します。詳しい解説は当ブログのこちらの記事をご覧ください。
Logger.log(bgColors)で変数bgColorsのログを確認すると、以下のように二次元配列でカラーコードを取得できていることが確認できます。
改行して見やすくするとこのようになります。
//変数bgColorsの中身 [[#434343, #434343, #434343, #434343], [#ffffff, #ffff00, #ffff00, #ffff00], [#ffffff, #ff0000, #00ff00, #ffff00], [#ffffff, #ffff00, #ffff00, #ff0000], [#ffffff, #00ff00, #00ff00, #00ff00], [#ffffff, #00ff00, #00ff00, #ffff00], [#ffffff, #00ff00, #ff0000, #00ff00], [#ffffff, #00ff00, #ff0000, #ff0000], [#ffffff, #ffff00, #ffff00, #ff0000], [#ffffff, #ff0000, #ffff00, #ff0000], [#ffffff, #ff0000, #00ff00, #00ff00], [#ffffff, #ff0000, #00ff00, #ff0000], [#ffffff, #00ff00, #ffff00, #ffff00]]
僕たち人間は目視でスプレッドシートの表の背景色をカウントしますが、GASは二次元配列である変数bgColors内のカラーコードをひとつひとつ確認して背景色をカウントします。
getRangeメソッドでの範囲指定の方法や、二次元配列とはどういうものかについて詳しく確認したい方は、当ブログの以下記事をご覧ください。
・【超初心者向け】getRangeを解説
・【基本操作】セル範囲を取得する方法
・【超初心者向け】二次元配列を解説
カウント用の変数(値は0)を宣言する
//カウント用の変数(値は0)を宣言する var count = 0;
実際にカウントを開始する前にまずカウント用の変数countを宣言し、値に0を代入します。
遊園地の入場口で係員さんが入場者数をカチカチしてカウントしている機械(数取器と言います)のようなイメージです。
以降の処理でこの変数countを使い、背景色をカウントしていきます。
取得した二次元配列をループしてカウントし、変数countに値を追加していく
//二次元配列bgColorsのすべての要素をZ字の順番でループする for(var r = 0; r < bgColors.length; r++){ for(var c = 0; c < bgColors[r].length; c++) { //二次元配列bgColorsの要素がtargetColorと一致する場合、 //変数countに+1する //toUpperCaseメソッドを使ってどちらのカラーコードも大文字にする if(bgColors[r][c].toUpperCase() == targetColor.toUpperCase()){ count++ } } } //↑↑ループはここまで↑↑
二次元配列bgColorsをZ字の順番でループします。
そしてループ内で二次元配列の要素bgColors[r][c]と変数targetColorのカラーコードが一致するか比較します。一致する場合のみcount++を使って、変数countに+1します。
bgColors[r][c]と変数targetColor両方にtoUpperCase()メソッドを使用しています。toUpperCase()メソッドは、対象の文字列を大文字に変換するメソッドです。例えば片方のカラーコードが"#ff0000"、もう片方が"#FF0000"の場合、同じ色ですが「一致しない」と判定されるため、toUpperCase()メソッドで両方大文字に揃えています。
二次元配列のループ処理順序について詳しく確認したい方は当ブログのこちらの記事をご覧ください。
変数countの値をG2セルに入力する
ループを使って変数targetColorに一致する背景色の数をカウントしました。
カウントされた数は変数countに格納されているため、あとはG2セルに変数countの値を入力すれば完了です。
//★G2セルに変数countに格納されている値を記入する
sh.getRange(2,7).setValue(count);
setValueメソッドを使って変数countの値をG2セルに入力しました。
無事赤色セルの数をカウントし、スプレッドシートに数を入力することができました！
【おまけ】関数countBgcの実行ボタンを作成する
背景色をカウントする関数countBgcを無事作成できました。
関数countBgcの実行ボタンをスプレッドシートのメニューバーに追加して、いつでも好きな時に簡単にカウントできるようにする方法は以下記事を参照ください。
カスタマイズのヒント
今回はA1～D13セルの中にあるすべての赤色セルの数をカウントしました。
実務では「東京本社(B列)の赤色セルだけカウントしたい」、「6月の赤色セルだけカウントしたい」、「赤色セルがある月の数をカウントしたい」など、『特定の○○の場合の背景色』をカウントしたいことがあると思います。
そのような場合には、今回のコードの二次元配列のループ処理順序やループ内のif文を調整すれば、よっぽど複雑な条件でない限り比較的容易に実現できます。
二次元配列のループ処理順序をしっかり理解することが鍵ですので、もしこんがらがった場合こちらの記事でループの処理順序を視覚的に確認ください。
まとめ
今回はGoogle Apps Script(GAS)を使ってスプレッドシートのセルの背景色の数をカウントする方法を紹介しました。
紹介したGASをそのままコピペして使用することができますが、ポイントを理解すればみなさん自身でカスタマイズしてもっと柔軟な条件で背景色をカウントできるようになります。
次回記事もご期待ください！
