# Create a spreadsheet data heat map using an ONLYOFFICE macro

**URL:** https://community.onlyoffice.com/t/create-a-spreadsheet-data-heat-map-using-an-onlyoffice-macro/13969
**Category:** News
**Created:** [16 April 2025 12:56 UTC](https://community.onlyoffice.com/t/create-a-spreadsheet-data-heat-map-using-an-onlyoffice-macro/13969 "2025-04-16T12:56:18Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![ONLYOFFICE\_team](https://community.onlyoffice.com/user_avatar/community.onlyoffice.com/onlyoffice_team/32/431_2.png) [@ONLYOFFICE\_team](https://community.onlyoffice.com/u/ONLYOFFICE_team)
#### Post date: [16 April 2025 12:56 UTC](https://community.onlyoffice.com/t/create-a-spreadsheet-data-heat-map-using-an-onlyoffice-macro/13969/1 "2025-04-16T12:56:18Z")

</div>

Originally published at: [Create a spreadsheet data heat map using an ONLYOFFICE macro | ONLYOFFICE Blog](https://www.onlyoffice.com/blog/2025/04/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.

![Create a spreadsheet data heat map using an ONLYOFFICE macro](https://static-blog.onlyoffice.com/wp-content/uploads/2025/04/15112011/Use-an-ONLYOFFICE-macro-to-create-a-Data-HeatMap.png)

## **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 **dat**** a** 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 **maxV**** alue** 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:

![Create a spreadsheet data heat map using an ONLYOFFICE macro](https://static-blog.onlyoffice.com/wp-content/uploads/2025/04/15111433/1-3.png)

After we run the macro:

![Create a spreadsheet data heat map using an ONLYOFFICE macro](https://static-blog.onlyoffice.com/wp-content/uploads/2025/04/15111539/3-2.png)

https://www.youtube.com/embed/ugsyNpae9SM?si=paQEg4lFnF_gdCuy

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](https://www.onlyoffice.com/contacts.aspx?utm_source=blog&utm_medium=post&utm_id=data_heatmap_article_macro). We value your input and look forward to collaborating with you. Best of luck in your exploratory endeavors!

## About the author

![Create a spreadsheet data heat map using an ONLYOFFICE macro](https://static-blog.onlyoffice.com/wp-content/uploads/2025/04/16132140/Aleksa-Radenkovic_blog-.png)

### **Useful links**

[ONLYOFFICE API methods](https://api.onlyoffice.com/docs/office-api/get-started/overview/?utm_source=blog&utm_medium=post&utm_id=data_heatmap_article_macro)

[FREE COURSE – Macros in ONLYOFFICE Docs](https://www.youtube.com/playlist?list=PLCF48HEKMOYNBygGjr9lSw3PLpVXQ6s_t)

[Get a free desktop suite](https://www.onlyoffice.com/desktop.aspx?utm_source=blog&utm_medium=post&utm_id=data_heatmap_article_macro)

[ONLYOFFICE on GitHub](https://github.com/ONLYOFFICE)

[Handle events in your macro code](https://www.onlyoffice.com/blog/2024/11/use-the-attachevent-method-to-handle-events-in-your-macro-code/?utm_source=blog&utm_medium=post&utm_id=data_heatmap_article_macro)