Hide specific rows in the selection using ONLYOFFICE macro

When working with a spreadsheet, sometimes we want to hide specific rows to make manipulating huge data chunks more convenient. This blog post will show you how to facilitate this feature with a small but quirky ONLYOFFICE macro.

To materialize this idea, we will implement the SetHidden method. It has already been used in another ONLYOFFICE macro that allows unhiding all the rows and columns in the spreadsheet.

The macro code is the following:

var activeSheet = Api.ActiveSheet;
var indexRowMax = 1048576;
var n = 1;
    for (let i = 0; i < indexRowMax; i++) {
        activeSheet.GetRows(n).SetHidden(false);
        n++;
    }

To unhide all the rows in the document, we set the indexRowMax variable to the maximum amount of rows. And then, we utilize it in a for loop that iterates through the entire spreadsheet and unhides each row by changing the SetHidden method property to false.

Building the “Hide rows in the selection” macro

As you might have already guessed, the SetHidden method can work both ways. It allows not only unhiding the rows but hiding them as well. However, if we want to hide some particular rows, the for-loop solution might not be the best option. Instead, we suggest targeting the selection on the document and iterating through it with the ForEach function. This will facilitate hiding the specific rows on the selection depending on the cell values.

So, first, we get the active sheet and the selection:

var activeSheet = Api.ActiveSheet;
var selection = activeSheet.Selection;

Then we add the ForEach function and iterate through each cell in the selection to fetch the values:

selection.ForEach(function (range) { 
    var value = range.GetValue(); 
} 

Now we add an if statement that will get the index of the row containing a value lesser than 35. We store this index in a variable and use it as a parameter to set the row hidden:

selection.ForEach(function (range) { 
    var value = range.GetValue();  
      if (value < "35") {   
    var row  = range.GetRow();
      range.GetRows(row[range]).SetHidden(true);
    }

The entire macro code is the following:

(function()
{
var activeSheet = Api.ActiveSheet;
var selection = activeSheet.Selection;
selection.ForEach(function (range) { 
    var value = range.GetValue();  
      if (value < "35") {   
    var row  = range.GetRow();
      range.GetRows(row[range]).SetHidden(true);
    }
});
})();

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

We hope that this little macro will make managing your spreadsheet data more convenient. It is just one of the many examples of what can be achieved by implementing our API methods. Our macros are JavaScript based, which makes them extremely versatile and easy to tailor to your needs.

We kindly encourage you to experiment and make your own macros. Feel free to ask questions or share your ideas with us. We are open to discussion and cooperation. Best of luck in your exploratory endeavors!