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]);
}
}