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]);   
    } 
}
  • 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? – Emerson Melonakos 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.

| improve this answer | |

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.