4

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]);   
    } 
}
3
  • I was the one who originally asked the question but it won't let me comment because I don't have enough rep yet. @Pnuts that could work for doing it manually, but how could I implement that concept into my automated script? Oct 20 '14 at 22:20
  • 3
    If the native filter tool that pnuts refers to is not viable, and you had to do it all in GAS, then you might have to explore reorganising the data by script so that all the rows to be hidden are contiguous (specifying ranges of non-contiguous cells is still not supported, unfortunately). You could create a column (perhaps hidden) that specifies the original row numbers if the sort order was important.
    – AdamL
    Oct 20 '14 at 22:25
  • 1
    @EmersonMelonakos If you use the "Contact Us" link at the bottom of the page, you can have your two accounts merged.
    – jonsca
    Oct 20 '14 at 22:58
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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.