Batch Hiding Function in Google Script

Question

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;
          arrayCount++;
        }   
    }

    for (var i = 0; i < arrayCount; i++)   
    {
      sheet.hideRows(array[i]);   
    } 
}

Answer 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.

Answer 2

The following code will find consecutive rows that fulfill some criteria and hide such rows fast by using just one sheet.hideRows() call per group.

/**
* Hides rows that have a given value in a given column.
*/
function hideRowsByValue() {
  const columnToWatch = 7; // 1-indexed
  const valueToWatch = 'hide';
  const sheet = SpreadsheetApp.getActive().getActiveSheet();
  const rowStart = sheet.getFrozenRows() + 1;
  const rowsToHide = sheet.getRange(rowStart, columnToWatch, sheet.getLastRow(), 1)
    .getValues()
    .flat()
    .map((value, index) => value === valueToWatch ? rowStart + index : 0)
    .filter(Number);
  hideRows_(sheet, rowsToHide);
}

/**
* Hides rows fast by grouping them before hiding.
*
* @param {SpreadsheetApp.Sheet} sheet The sheet where to hide rows.
* @param {Number[]} rowsToHide The 1-indexed row numbers of rows to hide.
*/
function hideRows_(sheet, rowsToHide) {
  countConsecutives_(rowsToHide.sort((a, b) => a - b))
    .forEach(group => sheet.hideRows(group[0], group[1]));
}

/**
* Counts consecutive numbers in an array and returns a 2D array that
* lists the first number of each run and the count of numbers in each run.
* Duplicate values in numbers will give duplicates in result.
*
* The numbers array [1, 2, 3, 5, 8, 9, 11, 12, 13, 5, 4] will get
* the result [[1, 3], [5, 1], [8, 2], [11, 3], [5, 1], [4, 1]].
*
* Typical usage:
* const runLengths = countConsecutives_(numbers.sort((a, b) => a - b));
*
* @param {Number[]} numbers The numbers to group into runs.
* @return {Number[][]} The numbers grouped into runs.
*/
function countConsecutives_(numbers) {
  return numbers.reduce(function (acc, value, index) {
    if (!index || value !== 1 + numbers[index - 1]) {
      acc.push([value]);
    }
    acc[acc.length - 1][1] = (acc[acc.length - 1][1] || 0) + 1;
    return acc;
  }, []);
}