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

H
Published in: Google Apps Script - Google Sheets

Hide Rows in Google Sheets

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)
  }
}
Published in: Google Apps Script - Google Sheets

Looking for something? Find here!

Meet the Author

Web Geek, Tech Columnist
A
Amit Agarwal

Amit Agarwal is a Google Developer Expert in GSuite and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India. Read more on Lifehacker and YourStory

Get in touch