Hide and unhide spreadsheet rows and columns with ONLYOFFICE macro

Managing large datasets can be time-consuming. That’s where ONLYOFFICE macros shine, automating tasks and unleashing your spreadsheet’s potential. In this blogpost, we’ll show you how to build a user-friendly macro that hides or unhides specific rows and columns. Additionally, while we have a similar macro in the macro samples section of our documentation, with this one, we will take it up a notch and make it even more user-friendly.

Building the macro

First we retrieve the currently active sheet in the ONLYOFFICE spreadsheet and assign it to the variable sheet:

const sheet = Api.GetActiveSheet()

Then we define a range that includes the cells from column A (cell A1) to column M (cell M1) of the active sheet. We will use this range to manipulate columns:

 const range = sheet.GetRange("A1:M1")

After that the rowsToHide array is created, containing the row numbers that need to be hidden or unhidden. This array can be customized by adding or removing row numbers:

const rowsToHide = [5, 8, 9, 12, 14, 16, 21, 22, 31, 32, 33]

Also we create  the columnsToHide array. It contains the column numbers that need to be hidden or unhidden:

const columnsToHide = [2,4,5,7,8,10,11,13,14,16]

Then we retrieve the hidden status of the first row specified in the rowsToHide array. If the first row is hidden, the SetHidden() property of the remaining rows and columns is set to false. That allows us to unhide the selected rows and columns. If the first row of the rowsToHide array is not hidden, the SetHidden() property is set to true facilitating hiding the targeted range:

const hidden = sheet.GetRows(rowsToHide[0]).GetHidden()

Then we execute the hideUnhideDetails function. It is responsible for hiding or unhiding the rows and columns based on the hidden status. Let’s take a closer look at it:

function hideUnhideDetails(hidden) {
        rowsToHide.forEach(row => {
            sheet.GetRows(row).SetHidden(!hidden)
        })

This loop iterates through each row number specified in the rowsToHide array and uses the SetHidden() method to hide or unhide the respective row based on the opposite of the hidden status. For example, if hidden is true, the row will be unhidden (!hidden is false), and if hidden is false, the row will be hidden (!hidden is true).

  columnsToHide.forEach(column => {
            range.GetCols(column).SetHidden(!hidden)
        })
    }

This loop iterates through each column number specified in the columnsToHide array and then uses SetHidden() to hide or unhide the column.

The entire macro code is the following:

(function()
{
    const sheet = Api.GetActiveSheet()
    const range = sheet.GetRange("A1:M1")
    const rowsToHide = [5, 8, 9, 12, 14, 16, 21, 22, 31, 32, 33]
    const columnsToHide = [2,4,5,7,8,10,11,13,14,16]
    const hidden = sheet.GetRows(rowsToHide[0]).GetHidden()
    hideUnhideDetails(hidden)
    // Unhide if hidden, Hide if unhidden
    function hideUnhideDetails(hidden) {
        rowsToHide.forEach(row => {
            sheet.GetRows(row).SetHidden(!hidden)
        })
        columnsToHide.forEach(column => {
            range.GetCols(column).SetHidden(!hidden)
        })
    }
})();

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

We hope that this macro will save you time and effort when managing large datasets, allowing you to focus on the most relevant information for your work. Using macros enables you to fully tap into ONLYOFFICE’s potential, boosting your productivity significantly.

We also encourage you to continue exploring the possibilities of macros in ONLYOFFICE. With a deeper understanding of ONLYOFFICE macros, you can automate tasks, enhance productivity, and customize documents. Share your ideas and questions with us. We look forward to collaborating with you. Best of luck in your exploratory endeavors!