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

1. Overview

\[ \begin{array}{l} \textbf{Google Apps Script can detect and work with hidden or filtered rows --} \\ \text{in Google Sheets to allow better control over data processing.} \\ \textbf{This is useful for excluding hidden rows from reports --} \\ \text{or identifying filtered-out data for review.} \end{array} \]

2. Core Steps

\[ \begin{array}{ll} \textbf{Step 1:} & \text{Open your Google Sheet that contains hidden or filtered rows.} \\ \textbf{Step 2:} & \text{Go to Extensions $\rightarrow$ Apps Script to open the editor.} \\ \textbf{Step 3:} & \text{Write a script to iterate through rows and check their visibility.} \\ \textbf{Step 4:} & \text{Log or process only those rows that match your requirements.} \\ \textbf{Step 5:} & \text{Run the script and check the execution logs for results.} \end{array} \]

3. Sample Google Apps Script

// Get hidden and filtered rows from active sheet
function getHiddenAndFilteredRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  for (var i = 1; i <= lastRow; i++) {
    var rowHidden = sheet.isRowHiddenByUser(i);
    var rowFiltered = sheet.isRowHiddenByFilter(i);
    
    if (rowHidden) {
      Logger.log("Row " + i + " is hidden by the user.");
    }
    if (rowFiltered) {
      Logger.log("Row " + i + " is hidden by a filter.");
    }
  }
}

4. Important Notes

\[ \begin{array}{l} \text{• isRowHiddenByUser() detects rows manually hidden by the user.} \\ \text{• isRowHiddenByFilter() detects rows hidden by active filters.} \\ \text{• You can combine both checks to handle all hidden rows.} \\ \text{• Scripts require sheet edit permissions to check row visibility.} \end{array} \]

5. Conceptual Flow (in LaTeX)

The process can be described as:

\[ \text{Google Sheet Rows} \xrightarrow{\text{Apps Script Checks}} \text{List of Hidden or Filtered Rows} \]

Where: \[ \text{Hidden Rows} = \{ \text{User-Hidden}, \text{Filter-Hidden} \} \]