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

Hello, Guys welcome back Here Learn How to Get Hidden and Filtered Rows in Google Sheets with Google Script easily. In Google Sheets, you can manually hide entire rows or use filters to conceal any rows that fit the stated criteria. For example, if you have a sheet with orders from all over the world, you may use a nation filter to hide any rows where the country is not the United States.

If you have a Google Script that iterates through each row in the Google Sheet to conduct actions on the row, such as sending emails or merging documents, you may easily bypass the hidden and filtered rows from the process.

In Google Sheets, there are two ways to look for hidden and filtered rows. You can either utilise Google Scripts’ SpreadsheetApp service or the Spreadsheet V4 API.

Check for hidden rows with Google Scripts

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

The following example makes use of (ES6 Chrome V8](/es6-google-apps-script-v8-200206). The script retrieves all of the rows in the currently active Google Sheet and iterates through them to find any rows that are hidden or filtered.

/**
 * Get the hidden and filtered rows in the specified Google Sheet
 * @param {string} spreadsheetId - Drive File ID of the Google Spreadsheet
 * @param {string} sheetId - The unique ID of the Google Sheet
 * @returns {Array} Index of the hidden rows (first row's position is 0)
 */
const getHiddenRowsinGoogleSheets = (
  spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId(),
  sheetId = SpreadsheetApp.getActiveSheet().getSheetId()
) => {
  const fields = 'sheets(data(rowMetadata(hiddenByFilter,hiddenByUser)),properties/sheetId)';
  const { sheets } = Sheets.Spreadsheets.get(spreadsheetId, { fields });

  const [sheet] = sheets.filter(({ properties }) => {
    return String(properties.sheetId) === String(sheetId);
  });

  const { data: [{ rowMetadata = [] }] = {} } = sheet;

  const hiddenRows = rowMetadata
    .map(({ hiddenByFilter, hiddenByUser }, index) => {
      return hiddenByUser || hiddenByFilter ? index : -1;
    })
    .filter((rowId) => rowId !== -1);

  return hiddenRows;
};

Go to Resources > Advanced Google Services and enable the Google Sheets API to use the Spreadsheet service in your Google Apps Script project.

You may also enable the Sheets API directly in the Sheets API directly in your appsscript.json file.

  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "Sheets",
      "serviceId": "sheets",
      "version": "v4"
    }]
  }

Spreadsheets Quota Limitation

The Google Spreadsheets Quota will allow your addon project to do 100 Spreadsheet readings per 100 seconds, and this limit will be shared by all project users. As a result, if your project has too many concurrent users, the Spreadsheet service may fail with the error:

API call to sheets.spreadsheets.get failed with error: Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per 100 seconds' of service 'sheets.googleapis.com'

You can either cache the results of the expensive getHiddenRows method or use a try-catch block to keep within the quota. If the Spreadsheet API fails due to a quota error, check for hidden rows using the SpreadsheetApp service.

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