GASでスプレッドシートの色付きセルの値を合計する方法

今回のスクリプトは、スプレッドシート内の背景色ごとのセルに入っている数値を合計し、指定した列に結果を出力します。

関数①sumValuesByColor() – 背景色ごとのセルの合計を計算

指定したシートの範囲内で、背景色ごとにセル内の数値を合計し、オブジェクト形式で返します。
透明セル（デフォルトの背景）と白色（#ffffff）は計算の対象外です。

指定のシートと範囲を取得

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var colorRange = sheet.getRange(rangeAddress);
var valueRange = sheet.getRange(valueRangeAddress);

• getSheetByName(sheetName) で指定のシートを取得します。
• getRange(rangeAddress) で色を判定する範囲を取得します。
• getRange(valueRangeAddress) で合計する数値が入った範囲を取得します。

セルの背景色と値を取得

var bgColors = colorRange.getBackgrounds();
var values = valueRange.getValues();

• getBackgrounds() でセルの背景色を2次元配列として取得
• getValues() でセル内の数値データを2次元配列として取得

セルごとの背景色をチェックし、数値を合計

for (var i = 0; i < bgColors.length; i++) {
  for (var j = 0; j < bgColors[i].length; j++) {
    var color = bgColors[i][j];
    var value = values[i][j];

    // 透明セル（デフォルトの背景）と白色（#ffffff）はスキップ
    if (color === "" || color.toLowerCase() === "#ffffff") {
      continue;
    }

    // 数値セルのみ合計
    if (typeof value === "number") {
      colors[color] = (colors[color] || 0) + value;
    }
  }
}

• 2次元配列をループ処理で走査
• 透明セル（""）と白色セル（#ffffff）はスキップします。
• 数値セルのみ合計を計算し、同じ色のセルがあれば足していきます。

背景色ごとの合計値をオブジェクトとして返す

return colors;

• 例えば、{ "#ff0000": 12000, "#00ff00": 8000, "#ffff00": 5000 } のようなオブジェクトを返します。

関数②outputColorSums() – 合計結果をスプレッドシートに出力

sumValuesByColor() の結果を取得し、E列（背景色）と F列（合計）に出力します。

アクティブシートを取得

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

• getActiveSheet() を使用し、現在開いているシートを取得します。

計算対象の範囲と出力先を設定

var colorRangeAddress = "B2:B"; // 色判定の範囲
var valueRangeAddress = "C2:C"; // 数値データの範囲
var outputColumnE = 5; // 色の出力先（E列）
var outputColumnF = 6; // 合計の出力先（F列）
var headerRow = 1; // ヘッダー行
var dataStartRow = 2; // データ出力開始行

• B2:B の範囲をカウント対象に指定
• C2:C の範囲の数値を合計対象に指定
• E列（5列目） に色を出力
• F列（6列目） に合計を出力
• ヘッダーを1行目に設定データ出力開始は2行目（dataStartRow = 2）

計算データを取得

var colorSums = sumValuesByColor(sheet.getName(), colorRangeAddress, valueRangeAddress);
if (Object.keys(colorSums).length === 0) {
  Logger.log("No color data found.");
  return;
}

• sumValuesByColor() を実行し、色ごとのセル内の合計を取得します。
• 計算結果が空なら処理を終了します。

ヘッダーを追加

sheet.getRange(headerRow, outputColumnE).setValue("色");
sheet.getRange(headerRow, outputColumnF).setValue("合計");

• E1 に「色」、F1 に「合計」 をセットします。

データをリストに格納

for (const [color, sum] of Object.entries(colorSums)) {
  outputData.push([sum]); // F列（合計）
  backgroundColors.push([color]); // E列（背景色）
}

• Object.entries(colorSums) で { "#ff0000": 12000, "#00ff00": 8000 } のようなデータを配列化します。
• 背景色のリスト backgroundColors と合計値のリスト outputData に追加します。

E列とF列にデータを出力

var rowCount = outputData.length;
var outputRangeValues = sheet.getRange(dataStartRow, outputColumnF, rowCount, 1);
var outputRangeColors = sheet.getRange(dataStartRow, outputColumnE, rowCount, 1);

outputRangeValues.setValues(outputData); // F列に合計値を設定
outputRangeColors.setBackgrounds(backgroundColors); // E列に背景色を設定

• F列（6列目）に合計結果を表示します。
• E列（5列目）に 各色の背景色を適用します。