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"
    }]
  }

#google apps script #google sheets #mobile app