GoogleSpreadSheetで月ごとに色を分ける方法

• 概要
• 環境
• 特定の色に変更する方法
• 月分だけ色を用意する方法
• 最後に

概要

特定のカラムで日時を管理している場合に月ごとにセルの色を変更する方法を紹介します
条件付き書式ではできないので GoogleAppScript を使います

環境

• GoogleSpreadSheet (2024/11/29時点)
• GoogleAppScript (2024/11/29時点)

特定の色に変更する方法

事前に30色分(30ヶ月分)用意しています
何度実行しても同じ色になるようにしています

B2 カラムで日時を管理している想定です
B2 カラムの1行目はヘッダで2行目から日時データの想定です
B2 カラムの日時はフォーマットが Date になっていることが条件です

function colorRowsByMonth() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow(); // シート内の最終行を取得
  const range = sheet.getRange(2, 2, lastRow - 1, 1); // B列の2行目から最終行まで取得
  const values = range.getValues(); // データを2次元配列で取得
  const colors = {}; // 月ごとの色を保持するオブジェクト
  const colorPalette = getLargeFixedColorPalette(); // 固定された30色の薄い色パレット
  let paletteIndex = 0;

  for (let i = 0; i < values.length; i++) { // ヘッダーをスキップ
    const date = values[i][0]; // 日時が入力されているカラム (A列の場合)
    if (date instanceof Date) { // 日付形式か確認
      const monthKey = Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy/MM");
      // 月ごとの色を設定（未登録なら新しい色を割り当て）
      if (!colors[monthKey]) {
        colors[monthKey] = colorPalette[paletteIndex % colorPalette.length];
        paletteIndex++;
      }
      // B2から下に行の背景色を設定
      sheet.getRange(i + 2, 2, 1, 1).setBackground(colors[monthKey]);
    }
  }
}

// 固定された30個の薄い色のパレットを生成する関数
function getLargeFixedColorPalette() {
  return [
    "#FFCCCC", "#FFEECC", "#FFFFCC", "#CCFFCC", "#CCE5FF",
    "#CCCCFF", "#FFCCE5", "#FFE4B5", "#D8BFD8", "#B0E0E6",
    "#F0E68C", "#E6E6FA", "#F5DEB3", "#FFDAB9", "#F0FFF0",
    "#FFF0F5", "#FFFACD", "#E0FFFF", "#FAFAD2", "#D3D3D3",
    "#FFC0CB", "#B0C4DE", "#ADD8E6", "#DDA0DD", "#D9F2E6",
    "#F2E2D2", "#F5F5DC", "#FBE5D6", "#EBF4FA", "#FAEBD7"
  ];
}

月分だけ色を用意する方法

例えば 2024/01 と 2025/01 は同じセルの色にしたい場合などはこちらを使います
こちらの方法は12色準備すれば OK なので色の追加などは不要です

function colorRowsByMonth() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow(); // シート内の最終行を取得
  const range = sheet.getRange(2, 2, lastRow - 1, 1); // B列の2行目から最終行まで取得
  const values = range.getValues(); // データを2次元配列で取得
  const monthColors = getMonthColors(); // 月ごとの色を取得

  for (let i = 0; i < values.length; i++) { // ヘッダーをスキップ
    const date = values[i][0]; // 日時が入力されているカラム (A列の場合)
    if (date instanceof Date) { // 日付形式か確認
      const month = date.getMonth(); // 月を取得（0: 1月, 11: 12月）
      const color = monthColors[month]; // 月に対応する色を取得
      // B2から下に行の背景色を設定
      sheet.getRange(i + 2, 2, 1, 1).setBackground(color);
    }
  }
}

// 1月から12月までの固定色を返す関数
function getMonthColors() {
  return [
    "#FFCCCC", // 1月 - 薄い赤
    "#FFEECC", // 2月 - 薄いオレンジ
    "#FFFFCC", // 3月 - 薄い黄色
    "#CCFFCC", // 4月 - 薄い緑
    "#CCE5FF", // 5月 - 薄い青
    "#CCCCFF", // 6月 - 薄い紫
    "#FFCCE5", // 7月 - 薄いピンク
    "#FFE4B5", // 8月 - 薄いベージュ
    "#D8BFD8", // 9月 - 薄いモーブ
    "#B0E0E6", // 10月 - 薄い水色
    "#F0E68C", // 11月 - 薄いカーキ
    "#E6E6FA"  // 12月 - 薄いラベンダー
  ];
}

最後に

レコードが増えたら再度 GAS を実行する必要があるのが不便です
(条件付き書式でもできる方法があるのだろうか)