スプレッドシートで黄色のセルだけ、枚数を数える

GAS
目次

スプレッドシートで業務を進めていると、進捗管理表や点検リストなどで「未対応のタスク」や「確認が必要なデータ」の行を一時的に黄色で塗りつぶして目立たせることがよくあります。運用が続いてデータ件数が増えてくると、全体の中で黄色のセルが何枚存在するのかを正確に把握したくなります。しかし、スプレッドシートの組み込み関数（COUNTAやCOUNTIFなど）には、セルの背景色を条件にして判定する機能が備わっていません。そこでGoogle Apps Script（GAS）を使って背景色を取得し、特定のカラーコードに一致するセルだけをカウントする処理が必要になります。

この記事では、背景色判定の核となる処理を整理し、実務で意図した結果が得られない場合の主な原因や、処理を安定させるための工夫についてまとめています。Google Apps Scriptでスプレッドシート 色付きセルを数える処理を実装する際の手元メモとして活用してください。

黄色の背景色を配列で拾ってカウントする最小構成

function countYellowCells() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const colors = sheet.getRange("B2:B").getBackgrounds();
  const n = colors.filter(([c]) => c === "#ffff00").length;
  Logger.log(n);
}

このコードは、B列の2行目から最終行までの背景色データを一度に取得し、その中から標準的な黄色を示すカラーコードである「#ffff00」と一致する要素の数をカウントして出力するものです。スプレッドシート上で色付けによる目視確認を行っており、その作業結果を迅速に数値化したい場面で利用します。例えば、カスタマーサポートの対応履歴シートで、一次回答が完了していない案件をスタッフが手動で黄色に塗りつぶしている場合、このスクリプトを実行することで未処理件数の全体像を即座に把握できます。

ただし、このコードをそのまま実務のシートに適用した際、画面上ではどう見ても黄色に見えるセルが存在するにもかかわらず、カウント結果が「0」になったり、実際の枚数と大きくズレてしまうという現象が頻繁に発生します。そのズレが生じる主な要因は、セルを塗った際に選択したカラーパレットの色相の違いや、文字列として保持されるカラーコードの英大文字・小文字の表記揺れ、さらには手入力の色付けではなく条件付き書式によって色が付いているケースなどが挙げられます。これらの不一致を回避するためには、スクリプト側で判定ロジックを柔軟にするか、集計対象となるシート側の塗りつぶしルールを統一する前処理が必要になります。

指定したカラーコードと実際の背景色で一致しない原因

スプレッドシートのUI上でセルを黄色に塗りつぶす際、カラーパレットには「黄色」に見える選択肢が複数存在します。標準のカラーパレットで最も鮮やかな「黄色」を選んだ場合は16進数カラーコードで「#ffff00」となりますが、少し落ち着いたトーンのテーマカラー（例えば「明るい黄色1」など）を選んだ場合、コードは「#fff2cc」や「#ffe599」といった別の値になります。目視ではすべて「黄色」と認識していても、プログラムによる完全一致判定（=== “#ffff00″）を行っていると、それらの類似色は一切カウントされません。これが「枚数が合わない」というトラブルの最も代表的な理由です。

さらに、GASの getBackgrounds メソッドが返すカラーコードは基本的に小文字（#ffff00）に統一されていますが、比較する側の定数記述や他の処理経由で取得したコードが大文字（#FFFF00）になっていると、文字列比較で偽判定となってしまいます。

この不一致を回避するためには、判定処理を記述する前に、実際に集計対象となっているセルが保持しているカラーコードをログに出力して確認する手順を踏むのが確実です。また、コード比較時に toLowerCase() を噛ませて大文字小文字の違いを吸収したり、複数の黄色のバリエーションを許容できるように配列に許可するカラーコードの一覧を定義しておき、includes メソッドなどで判定する設計に切り替えることで、実務上の色の揺れによる集計漏れを防ぐことができます。

条件付き書式で塗られた黄色をこのコードで集計できない理由

スプレッドシートでセルに色を付ける方法には、ユーザーがツールバーから手動で塗りつぶしを実行する方法と、「特定の値が入ったら自動で黄色にする」という条件付き書式ルールを設定する方法の2種類があります。ここで注意が必要なのは、getBackgrounds メソッドが取得できるのは「手動またはスクリプトによってセル自体に直接設定された背景色」のみであるという点です。

条件付き書式によって動的に変更されている背景色は、セル自体の値としては無色（白：#ffffff）のままであり、画面上の表示レイヤーでのみ色付けされて見えている状態になっています。そのため、条件付き書式で黄色に塗られたセルに対して getBackgrounds を実行しても、取得されるコードは「#ffffff」となり、上記の集計用コードでは1枚もカウントすることができません。タイトルで挙げている「黄色のセルだけ数えたい」という状況において、対象のシートがどちらの方法で色付けされているかを事前に見極めることが不可欠です。

もし集計対象が条件付き書式で黄色になっている場合は、色そのものを判定しようとするのではなく、その条件付き書式が発火している「元のセルの値や条件（例：ステータス列が『未対応』である、日付が期限を過ぎている等）」をGAS側で判定するロジックに変更するのが正解です。どうしても条件付き書式の色そのものを取得したい場合は、getConditionalFormatRules メソッドを使ってシートに設定されたルールをパースし、各セルの値がルールに合致しているかを評価する複雑なスクリプトを組む必要がありますが、通常は値そのものを COUNTIF や filter で集計する方が処理も軽く、実装の手間も大幅に削減されます。

大規模なシートで処理速度を落とさないための範囲指定

集計対象となるデータが数千行から数万行に及ぶ大規模なスプレッドシートで色付きセルを数える場合、APIの呼び出し方とメモリ上の処理方法によって実行速度が極端に変わります。最悪のパターンは、for文などのループ処理の中で getValue() や getBackground() を1セルずつ繰り返し呼び出す実装です。Google Apps Scriptにおいて、スプレッドシートのセルへ都度アクセスするAPI通信は非常に重い処理であり、数百回の呼び出しだけで実行時間が数秒から数拾秒に膨れ上がり、GASの実行時間上限（6分制限）に引っかかってエラー停止する原因になります。

掲載したコード例のように、getRange(“B2:B”).getBackgrounds() を使って一括で範囲全体の背景色を2次元配列としてメモリ上に取得し、JavaScriptの組み込み配列メソッドである filter を使ってメモリ内で一気にカウント処理を完結させるアプローチが必須となります。これにより、スプレッドシートへのアクセスは1回で済み、数万行のデータであっても一瞬で処理が完了します。

ただし、「B2:B」のように最終行まで無制限に取得する範囲指定を行うと、データが存在しないシート下部の膨大な空白行まで読み込んでしまうことになります。空白行の色が「#ffffff」であれば集計結果の数値自体には影響しませんが、無駄なデータ配列をメモリ上に保持することになり、処理負荷が上がります。これを回避するには、sheet.getLastRow() を使って実際にデータが入力されている最終行を特定し、getRange(2, 2, lastRow – 1, 1) のように必要な行数分だけをピンポイントで取得する記述に補正するのが実務的です。

セル色を変更しても自動再計算されない仕様への現場的アプローチ

スプレッドシートの組み込み関数（SUMやAVERAGEなど）は、参照しているセルの「値」が書き換わると自動的に再計算が行われ、画面上の表示更新が即座に反映されます。しかし、ユーザーがセルの「背景色」を変更したという操作は、スプレッドシートの値の更新イベントとして検知されません。そのため、GASで作成した集計処理をカスタム関数（例: `=COUNT_YELLOW_CELLS()` のようにセル内に直接記述して呼び出す形式）としてシート上に埋め込んだ場合、いくらセルの背景色を黄色に塗ったり白に戻したりしても、数式の結果は過去のキャッシュのまま自動更新されないという問題に直面します。

この仕様を知らずにカスタム関数として運用すると、画面上は黄色のセルが5枚あるのに、集計セルには「3」と表示されたままになり、現場でデータのズレによる混乱を招く原因になります。

このズレや更新漏れを回避するための実務的な設計としては、以下のアプローチが取られます。

1つ目は、カスタム関数ではなく、カスタムメニュー（スプレッドシートの上部メニューバーに「集計実行」などのボタンを追加する）や、シート上に配置した図形ボタンにスクリプトを割り当てて、ユーザーが意図したタイミングでボタンを押して集計結果を特定のセルへ書き込ませる運用です。これであれば確実に最新の背景色状態を取得して計算が行われます。

2つ目は、時間主導型トリガー（定時実行トリガー）を設定し、数分おき、あるいは1時間おきにバックグラウンドで集計スクリプトを走らせ、集計結果をダッシュボード用シートに書き込む方式です。リアルタイム性は若干落ちるものの、ユーザーによるボタン押し忘れのリスクを排除できます。

単に「コードを書いて動かす」だけでなく、セルの色の変化というイベントの特殊性と再計算が行われない仕様を考慮し、ユーザーがどのように集計結果を利用するかに合わせたインターフェースを設計することが、スプレッドシートの色付きセル集計を現場で安定して運用するためのポイントになります。

タイトルとURLをコピーしました