Chat with us, powered by LiveChat

How to Get Hidden and Filtered Rows in Google Sheets with Google Script

You can hide entire rows in Google Sheets manually or use filters and hide on or more or rows in the sheet that matches your specified criteria. For instance, if you have a sheet containing orders from different countries, you can set up a country filter to hide all rows where the country is not the United States.

If you have a Google Script that iterates through each row in the Google Sheet for performing actions on the row, like sending emails or merging documents, you can check for the hidden and filtered rows and easily skip them from the workflow.

Also, a row in the Google Sheet can be filtered and hidden at the same time.

function getHiddenAndFilteredRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var d=0; d<data.length; d++) {
    if (sheet.isRowHiddenByFilter(d+1)) {
      Logger.log("Row #" + d + " is filtered - value: " + data[d][0]);
      continue;
    }
    if (sheet.isRowHiddenByUser(d+1)) {
      Logger.log("Row #" + d + " is hidden - value: " + data[d][0]);
      continue;
    }
    // processRow(d)    
  }
}