How to skip hidden rows while iterating through Google Spreadsheet w/ Google Apps Script

I have a Google Spreadsheet with many hidden rows in it, and I want to skip them when iter开发者_StackOverflow社区ating through a list of rows in the spreadsheet.

It's mainly an efficiency issue, as I'm dealing with over half of my rows being hidden and in no need of being checked.

Any help would be appreciated.

New API as of 2018 that's useful for this problem: isRowHiddenByUser. See also isRowFilteredByUser.

There's no direct way of doing this in Apps Script, but there is a feature request open to provide a way get the show/hide status of a row, if you want to star it.

A workaround using SUBTOTAL. Create 2 columns A and B. A must always have a value and B has a set of formulas. These 2 columns look like this:

A |           B
---------------------------
1 | =NOT(SUBTOTAL(103, A1))
1 | =NOT(SUBTOTAL(103, A2))
1 | =NOT(SUBTOTAL(103, A3))

SUBTOTAL returns a subtotal using a specified aggregation function. The first argument 103 defines the type of function used for aggregation. The second argument is the range to apply the function to.

• 3 means COUNTA and counts the number of values in the range
• +100 means ignore hidden cells in the range.

The result of SUBTOTAL with a range of 1 cell will be 0 when the cell is hidden and 1 when the cell is shown. NOT inverts it.

Now you can read the column B with your script to know if a row is hidden.

Here's the transposed question and answer: https://stackoverflow.com/a/27846180/1385429

The issue tracker holds that request since Aug 3, 2010 with a Medium priority and just a "Triaged" status. More than 3 years and no signs of solution from the GAS team.

My workaround was to use a special leading character that would indicate the visibility state of the row/column, it is a leading backtick (`) in the cells of top header row/column. In case if merged cells are used in column headers, then an empty top row should be dedicated just for that functionality until the google engineers will improve the API. Same applies if there are formulas in the 1st row/column cell(s). These dedicated rows/columns itself can be hidden.

After starting to use this functionality each show/hide column/row command should be performed from a customized menu, otherwise there'll be errors when iterating through the range programmatically, because of the missing/excessive backtick.

e.g. to hide rows of selected cells the following function is invoked

function hideSelectedRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = SpreadsheetApp.getActiveRange();

  // hide rows and add a ` backtick to the header cell
  for (var row = range.getRow(); row <= range.getLastRow(); row++)
  {
    // add backtick only if it isn't there (that may happen when manually unhiding the rows)
    var cellHeader      = sheet.getRange(row, 1)
    var cellHeaderValue = cellHeader.getValue()    
    if ( !cellHeaderValue.match(/^`/) ) {
      cellHeader.setValue('`' + cellHeaderValue)
    }

    // hide rows of selected range
    sheet.hideRows( row );
  }
}

and the menu

SpreadsheetApp.getActiveSpreadsheet()
.addMenu("Show/Hide", [
    { name : "Hide Selected Rows",    functionName : "hideSelectedRows"    },
    { name : "Hide Selected Columns", functionName : "hideSelectedColumns" },
    null,
    { name : "Hide Rows",             functionName : "hideRows"            },
    { name : "Hide Columns",          functionName : "hideColumns"         },
    null,
    { name : "Show Rows",             functionName : "showRows"            },
    { name : "Show Columns",          functionName : "showColumns"         },
    null,
    { name : "Show All Rows",         functionName : "unHideAllRows"       },
    { name : "Show All Columns",      functionName : "unHideAllColumns"    }
  ])

Once google engineers find the time to improve the onChange event, it will be possible to put those backticks automatically. Currently the changeType is limited to EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, OTHER without any details on which Row/Column was inserted/removed. Looks like the team behind GAS is scanty. I wish they could hire more programmers (khm khm)

As for workaround, it is possible by using SUBTOTAL function which can returns a subtotal for a vertical range of cells.

Syntax is:

SUBTOTAL(function_code, range1, [range2, ...])

where hidden values can be skipped for any of these codes by prepending 10 (to the single-digit codes).

For example 102 for COUNT while skipping hidden cells, and 110 for VAR while doing so.

Related: Google Spreadsheets sum only shown rows at Webapps SE