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 to hide any rows that matches the 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.

There are two ways to check for hidden and filtered rows in Google Sheets. You can either use the SpreadsheetApp service of Google Scripts or use 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 next example uses (ES6 Chrome V8](/es6-google-apps-script-v8-200206). The script fetches all the rows in the currently active Google Sheet and iterates through each of them to finds all rows that are either 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;
};

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

Alternatively, you may enable the Sheets API directly in your appsscript.json file.

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

Spreadsheets Quota Limitation

Google Spreadsheets Quota will allow your addon project to make up 100 Spreadsheet reads per 100 seconds and this limit is shared across all users of the project. Thus if your project has too many simultaneous 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'

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

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

source:https://ift.tt/2WDcodT