Add comments and change cell colors with ONLYOFFICE macro

Automating spreadsheet tasks can save a lot of time and effort. And ONLYOFFICE macros offer a helping hand by allowing to perform actions based on conditions. This article will guide you through creating a macro that adds comments and changes cell color based on their values.

Building the macro

First we target the currently active worksheet:

var oWorksheet = Api.GetActiveSheet();

Then we target the selection on the document:

var oRange = Api.GetSelection()

After that we create a ForEach loop that iterates through each cell in the selected range and retrieves values from the selected cells:

oRange.ForEach(function (range) {
    var sValue = range.GetValue();
});

These values are then compared to a specific criterion. Depending on the comparison result, a comment is added to the cell using the AddComment() method. After adding the comment, we set the fill color by using the SetFillColor():

  if (sValue > 10000000) {
       range.AddComment("The value is bigger than 10000000.");
       range.SetFillColor(Api.CreateColorFromRGB(255, 213, 191))
    } 
else {
           range.AddComment("The value is less than 10000000.");
       range.SetFillColor(Api.CreateColorFromRGB(204, 255, 255))
    }
});

Here we check whether the cell’s value goes beyond a specific limit. If it does, we add a comment and adjust the cell’s color to a light orange shade (RGB: 255, 213, 191). When the value is below the limit, a different comment is added along with a light blue color (RGB: 204, 255, 255).

The entire macro code is the following:

(function()
{
    var oWorksheet = Api.GetActiveSheet();
var oRange = Api.GetSelection()
oRange.ForEach(function (range) {
    var sValue = range.GetValue();
    if (sValue > 10000000) {
       range.AddComment("The value is bigger than 10000000.");
       range.SetFillColor(Api.CreateColorFromRGB(255, 213, 191))
    } else {
           range.AddComment("The value is less than 10000000.");
       range.SetFillColor(Api.CreateColorFromRGB(204, 255, 255))
    }
});
})();

Now, let’s run our macro and see how it works!

We hope you will find this macro a valuable tool for streamlining your spreadsheet tasks. By using ONLYOFFICE macros, you can boost your productivity and shift your focus to other essential aspects of your workflow.

We invite you to explore the potential of the ONLYOFFICE API and create your own macros. If you have questions or ideas, feel free to comment or contact us. We’re eager to hear from you and excited to work together. Best of luck in your future endeavors!