Batch Hiding Function in Google Script

by user79883   Last Updated January 14, 2018 06:03 AM

I wrote a function that iterates through every non-blank row in the sheet, checks the value of a certain cell, and then if that cell matches my criteria its row number is added to an array.

I then iterate through the array containing the row numbers hiding one by one. Instead of hiding one by one (which tends to run extremely slow over the amount of data I am running it on), is there a way that I can batch hide? When I run a hide-all or show-all script it is practically instant. The iterating seems to be the problem.

The built-in hiding functions for the sheet class are:

  • hideRow(row), hides the rows in the given range.
  • hideRows(rowIndex), hides the row at the given index.
  • hideRows(rowIndex, numRows)

My thoughts were to somehow make a custom range (equivalent to the array holding the row indexes that I already have) that I could pass to the hideRow function instead of iteratively passing indexes to the hideRows function.

Here is my current function:

function hideRows()
    var dataI = sheet.getRange('I2:I').getValues();
    var dataN = sheet.getRange('N2:N').getValues();
    var count = getLastPopulatedRow();
    var array = new Array(count);
    var arrayCount = 0;
    var startingRow = 0;

    for (var i=startingRow; i<count; i++)
        if ((dataI[i][0] == "Completed") || (dataI[i][0] == "Duplicate") || (dataI[i][0] == "Refunded") || (dataI[i][0] == "Was Already Fulfilled") || (dataI[i][0] == "Cancelled")) {
          array[arrayCount] = i+2;

    for (var i = 0; i < arrayCount; i++)   

Answers 1

To hide rows, Google Apps Script only include the methods that the OP already mentioned. The one that is able to hide several rows at a time, hideRows(rowIndex,rows) works for consecutive rows. If the rows are not consecutive, one alternative is to sort the data in advance in order to make the rows to hide to be consecutive.

January 14, 2018 05:28 AM

Related Questions

Which is better: onEdit or onChange trigger?

Updated August 14, 2018 16:03 PM

How to format the time in this Google Docs script

Updated July 12, 2018 14:03 PM

How to Process Rows of Google Sheet Singly?

Updated April 05, 2018 19:03 PM