ONLYOFFICEマクロでスプレッドシートのデータヒートマップを作成する方法

2025年04月16日著者：Denis

データ分析では、数値の変動を一目で認識することが意思決定において非常に重要です。各セルを手動でフォーマットする作業は時間がかかり、エラーも発生しやすいです。このONLYOFFICEマクロは、セルの色を動的に調整することにより、この問題を解決し、複雑なデータの解釈をより効率的に行えるようにします。

ONLYOFFICE マクロを使用して、スプレッドシートのデータヒートマップを作成

マクロの作成

var sheet = Api.GetActiveSheet();
var range = sheet.GetSelection();
var data = range.GetValue();

まず、アクティブシート、選択範囲、およびそのデータを、それぞれ sheet、range、data 変数に初期化します。

“データが選択されていない”エラー処理

次に、データが選択されているかどうかを確認します。もしデータが選択されていない場合、適切なメッセージ（“データが選択されていません”）を出力してマクロの処理を終了します。

// データが選択されていない場合にメッセージを表示し、処理を中断する
  if (!data) {
    console.log("データが選択されていません");
    return;
  }

選択範囲から行と列のインデックスを抽出する

データが存在する場合、列の開始、列の終了、行の開始、行の終了のインデックスを取得します。

// 行と列の開始と終了のインデックス
  var firstRowIndex = range.GetCells().Row;
  var firstColIndex = range.GetCells().Col;
  var lastRowIndex = data.length + firstRowIndex;
  var lastColIndex = data[0].length + firstColIndex;

上記のコードスニペットのように、range.GetCells() を使用して選択範囲内のセルにアクセスし、そこから .Row() や .Col() を追加して最初の行と最初の列のインデックスを取得します。そして、data.length でデータの行数を求め、firstRowIndex と足して最終行のインデックスを算出します。同様に、列については data[0].length を使い、firstColIndex と足して最終列のインデックスを求めます。

なぜ data[0] なのか？

データの行数が不明な場合でも、data[0]、data[1] など、各要素が行を表すため、データに少なくとも1行は存在することが保証されます。つまり、data に値が存在すれば、data[0] は常に有効です。

選択されたスプレッドシートのセルから数字を収集する

次に、values 配列を作成し、選択したセルから数字を取り出して格納します。

var values = []; // 選択されたデータから数字を格納する配列

  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      // 値が数字かどうかをチェック
      // 数字であれば、values 配列に格納する
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }

ここでは、values 配列を初期化し、選択範囲内の各セルを反復処理します。

通常、セル内の値は文字列として扱われるため、値が数字であるかどうかを確認する際は、まずparseFloat() メソッドで数値に変換する必要があります。文字列が数値を表していれば、その文字列は数値に変換され、そうでなければ NaN（数値ではない）となります。isNaN(…) 関数は、括弧内の値が数字でないかをチェックします。先頭に「!」を付けることで、数字であるかどうかを確認します。

値が数字であれば、その値を value 変数に格納し、これを values 配列に追加します。繰り返し処理を終えると、選択したセル内のすべての数値が格納された values 配列が完成します。

この配列は、Math.min() および Math.max() JavaScript メソッドを用いて、最小値および最大値を求めるために必要です。

最小値と最大値を見つける

// values 配列から最小値と最大値を格納
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);

色の適用

必要な情報がすべて揃ったので、数字を含むセルにカスタムカラーを適用します。

選択したセルから最小値と最大値が得られているため、再度セルを反復処理できます。

for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      // もう一度、セルの値が数字であるかを確認
      // もし数字であれば、その数値、最小値、最大値に基づいて色を作成する
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        var ratio = (value - minValue) / (maxValue - minValue);
        var red = Math.round(255 * ratio);
        var green = Math.round(255 * (1 - ratio));
        sheet
          .GetCells(j, i)
          .SetFillColor(Api.CreateColorFromRGB(red, green, 0));
        // セルの色は緑から赤へ変化させたい
      }
    }
  }

RGB システムで色を作成するには、赤、緑、青の各パラメーターが必要です。ここでは、色の範囲を緑から赤にしたいので、青の値は 0 のままで、赤と緑の値だけを調整します。

数字が小さい（最小値に近い）場合、そのセルは緑寄りの色になります（最小値は純粋な緑に対応します）。逆に、数字が大きい場合、色は赤寄りになります（最大値は純粋な赤に対応します）。

数値がどれくらい大きいか、または小さいかを判断するために、Min-Max 正規化 法を用いて 0 から 1 の間の「比率」を算出します。数値が大きいほど比率は1に近づき、小さいほど0に近づきます。この比率は、minValue と maxValue の変数を用いて計算されます。

次に、この比率を用いて赤と緑のパラメーターを決定します。比率は 0～1 の値であるのに対し、赤と緑の値は 0～255 の範囲であるため、比率に255を掛けた値を用います。

数字が大きい場合には赤をより強調するため、赤の値は比率に255を掛けて四捨五入します。一方、緑は数字が小さい場合に優勢となるため、1 – ratio に255を掛けて計算します。こうすることで、数値が大きくなると比率が1に近づき、1 – ratio が小さくなり、緑の値が減少します。

赤と緑の値を取得したら、Api.CreateColorFromRGB(r, g, b) を使用して色を作成し、.SetFillColor(color) でそのセルに適用します。

マクロの全コード

(function () {
  var sheet = Api.GetActiveSheet();
  var range = sheet.GetSelection();
  var data = range.GetValue();

  // データが選択されていない場合、メッセージを表示して終了する
  if (!data) {
    console.log("データが選択されていません");
    return;
  }

  // 行と列の開始および終了インデックスの取得
  var firstRowIndex = range.GetCells().Row;
  var firstColIndex = range.GetCells().Col;
  var lastRowIndex = data.length + firstRowIndex;
  var lastColIndex = data[0].length + firstColIndex;

  console.log(firstColIndex + " " + lastColIndex); // 正しい列インデックスかテスト
  console.log(firstRowIndex + " " + lastRowIndex); // 正しい行インデックスかテスト

  var values = []; // 選択されたデータから数字を格納する配列

  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      // セルの値が数字かチェック
      // 数字なら values 配列に格納する
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }

  // values 配列から最小値と最大値を取得
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);

  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      // 再び、セルの値が数字かチェックする
      // 数字であれば、その値、最小値、最大値に基づいて色を作成する
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        var ratio = (value - minValue) / (maxValue - minValue);
        var red = Math.round(255 * ratio);
        var green = Math.round(255 * (1 - ratio));
        sheet
          .GetCells(j, i)
          .SetFillColor(Api.CreateColorFromRGB(red, green, 0));
        // 緑から赤へのグラデーションにする
      }
    }
  }
})();