Create a spreadsheet data heat map using an ONLYOFFICE macro

In data analysis, recognizing variations in numerical values at a glance is crucial for decision-making. Manually formatting each cell can be time-consuming and error-prone. This ONLYOFFICE macro solves this problem by dynamically adjusting cell colors, making it easier to interpret complex data efficiently.

Building the macro

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

Starting off, we initialize the active sheet, selection, and range inside the sheet, range, and data variables, respectively.

“No data selected” error handling

Immediately after this, we check if any data is actually selected. If no data is selected, we terminate the macro function with an appropriate message: “No data selected.”

//We check if no data is selected, and show message if that is the case
  if (!data) {
    console.log("No data selected");
    return;
  }

Extracting row and column indexes from a selection

If data is present, we proceed by retrieving its parameters—column start, column end, row start, and row end indexes.

//Indexes indicating where rows and columns start and end
  var firstRowIndex = range.GetCells().Row;
  var firstColIndex = range.GetCells().Col;
  var lastRowIndex = data.length + firstRowIndex;
  var lastColIndex = data[0].length + firstColIndex;

There are many ways we can obtain the indexes, but one of them is shown in the code snippet above:

• range.GetCells() – we access the cells of our selection, and by appending .Row() or .Col(), we get the index of the first row and the first column.
• Once we have that, we can use data.length, which will give us the number of rows in our data. Then, we add this to the firstRowIndex variable to get the index of the last row. We do the same for columns, using data[0].length to get the number of columns, and then adding the firstColIndex variable.

Why data[0]?

Since we do not know how many rows we have, and each of data[0], data[1], etc. represents a row, the only thing we can be certain of is that our data will always have at least one row. This means that data[0] will always be valid if the variable data contains any values.

Collecting numbers from selected spreadsheet cells

Next, we create the values array and fill it with numbers from our selection.

var values = []; //We will store number from selected data here


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //We are checking if the value is a number
      //If it is, we store it to values array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }

 

After initializing the values array, we iterate through each cell in our selection.

By default, the value in each cell is treated as a string, so when we check if the value is a number, we first need to use the parseFloat() method to convert it into a number.

If the string inside the cell represents a number, parseFloat will turn it into a number. If not, it will turn it to a NaN (Not a Number).

The isNaN(…) function checks if the value inside the parentheses is not a number. If we add a “!” before isNaN(…), we’re checking if the value is a number.

If the value is a number, we initialize the value variable and store the number in it. We then append this value to the values array.

After the iteration, we end up with the values array, which contains all the numbers from the selected cells.

The reason we need this array is so we can find the minimum and maximum numbers from the selected cells using the Math.min() and Math.max() JavaScript methods.

Finding the minimum and maximum value

//Storing minimum and maximum values from the values array
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);

Applying colors

Now that we have all the necessary information, we can apply custom colors to the cells containing numbers.

We have both the minimum and maximum numbers from the selected cells, so we can iterate through the cells once more.

for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Again we have to check if the value is a number
      //If it is, we create the color depending on that value
      //As well as minimum and maximum value from the array
      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));
        //We want colors to go from green to red
      }
    }
  }

When creating a color using the RGB system, we need red, green, and blue parameters to generate any desired color. In this case, the color should range from green to red, meaning the blue parameter remains at 0 while the red and green parameters vary depending on the desired shade.

If the number is small—closer to the minimum value—the color will be greener (the minimum value corresponds to pure green). Conversely, if the number is large, the color will shift toward red (the maximum value corresponds to pure red).

To determine how large or small the number is, we use the Min-Max Normalization method to obtain a “ratio” between 0 and 1. A larger number results in a ratio closer to 1, while a smaller number yields a ratio closer to 0. This ratio is calculated using the minValue and maxValue variables.

We can then use this ratio to determine the red and green parameters. Unlike the ratio, which ranges from 0 to 1, both the red and green values scale from 0 to 255, so we multiply the ratio accordingly.

Since red should be more dominant for larger numbers, we calculate it by multiplying the ratio by 225 and rounding it to the nearest whole number.

For green, the calculation is different. Green should be more dominant for smaller numbers, so we use 1−ratio, multiplying it by 225. When the number is larger, the ratio approaches 1, making 1−ratio smaller, which in turn reduces the green value.

Once we obtain the red and green parameters, we use Api.CreateColorFromRGB(r, g, b) to create the color and .SetFillColor(color) to apply it to the cell.

The full macro code

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


  //We check if no data is selected, and show message if that is the case
  if (!data) {
    console.log("No data selected");
    return;
  }


  //Indexes indicating where rows and columns start and end
  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); //Testing if we got the right column indexes, first should be on spot, last should be higher by 1
  console.log(firstRowIndex + " " + lastRowIndex); //Testing if we got the right row indexes, first should be on spot, last should be higher by 1


  var values = []; //We will store number from selected data here


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //We are checking if the value is a number
      //If it is, we store it to values array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }


  //Storing minimum and maximum values from the values array
  var minValue = Math.min(...values);
  var maxValue = Math.max(...values);


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //Again we have to check if the value is a number
      //If it is, we create the color depending on that value
      //As well as minimum and maximum value from the array
      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));
        //We want colors to go from green to red
      }
    }
  }
})();

Let’s run the macro and see how it works!

Before we run the macro:

After we run the macro:

 

Now you can easily visualize data distribution in your spreadsheet, making analysis more intuitive. Whether you’re working on the desktop or web version of ONLYOFFICE, this macro seamlessly integrates into your workflow.

Don’t miss the opportunity to explore the full potential of the ONLYOFFICE API. With an extensive range of methods, you can bring your automation ideas to life. If you have any questions or innovative concepts, feel free to share them with us. We value your input and look forward to collaborating with you. Best of luck in your exploratory endeavors!

About the author