Retrieving Hidden Rows and Showing Rows by Filter View on Google Spreadsheet using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a sample script for retrieving the hidden rows and showing rows by the filter view on Google Spreadsheet using Google Apps Script. In the current stage, there are no methods for directly retrieving the hidden rows and showing rows by the filter view in Spreadsheet service (SpreadsheetApp). And, isRowHiddenByFilter of Class Sheet cannot be used for the filter view. But, fortunately, when Sheets API is used, the filter view can be retrieved and created. In this report, the hidden rows and showing rows by the filter view are retrieved using Sheets API.

The flow of this script is as follows.

  1. Retrieve the settings of the filter view (filterViews) you want to use.
    • In this case, the method of “spreadsheets.get” can be used.
  2. Create new basic filter to the sheet you want to use using the retrieved settings of the filter view.
    • In this case, the method of “spreadsheets.batchUpdate” can be used.
  3. Retrieve the values of rowMetadata of the sheet.
    • In this case, the method of “spreadsheets.get” can be used.
    • At the values of rowMetadata, the filtered rows have the property of "hiddenByFilter": true,. Using this, you can retrieve the hidden rows and/or the showing rows.
  4. Delete the created basic filter.

IMPORTANT: In this flow, when the basic filter is used in the sheet, the basic filter is cleared. Please be careful this. When you test this script, please use the sample Spreadsheet.

When this flow is reflected to Google Apps Script, it becomes as follows.

Sample script

Before you use this script, please enable Sheets API at Advanced Google services. And, please manually set the filter view and set the filter view name. This script uses this filter view.

function myFunction() {
  const spreadsheetId = "###"; // Please set the Spreadsheet ID.
  const sheetName = "Sheet1"; // Please set the sheet name.
  const filterViewName = "sampleFilter1"; // Please set the filter view name.

  // 1. Retrieve the settings of the filter view (`filterViews`) you want to use.
  const res1 = Sheets.Spreadsheets.get(spreadsheetId, { ranges: [sheetName] });
  const sheetId = res1.sheets[0].properties.sheetId;
  const filterViews = res1.sheets[0].filterViews.filter(
    (e) => e.title == filterViewName
  );
  if (filterViews.length == 0) {
    throw new Error("Filter view cannot be found.");
  }

  // 2. Create new basic filter to the sheet you want to use using the retrieved settings of the filter view.
  const obj = filterViews[0];
  obj.range.sheetId = sheetId;
  const reqs = [
    { clearBasicFilter: { sheetId: sheetId } },
    {
      setBasicFilter: {
        filter: {
          criteria: obj.criteria,
          filterSpecs: obj.filterSpecs,
          range: obj.range,
          sortSpecs: obj.sortSpecs,
        },
      },
    },
  ];
  Sheets.Spreadsheets.batchUpdate({ requests: reqs }, spreadsheetId);

  // 3. Retrieve the values of `rowMetadata` of the sheet.
  const res2 = Sheets.Spreadsheets.get(spreadsheetId, {
    ranges: [sheetName],
    fields: "sheets",
  });
  const values = res2.sheets[0].data[0].rowMetadata.reduce(
    (o, r, i) => {
      if (r.hiddenByFilter && r.hiddenByFilter === true) {
        o.hiddenRows.push(i + 1);
      } else {
        o.showingRows.push(i + 1);
      }
      return o;
    },
    { hiddenRows: [], showingRows: [] }
  );

  // 4. Delete the created basic filter.
  Sheets.Spreadsheets.batchUpdate(
    { requests: [{ clearBasicFilter: { sheetId: sheetId } }] },
    spreadsheetId
  );

  console.log(values);
}

Result

When above script is used for the following sample Spreadsheet,

Before filter view is not set.

After filter view was set.

Result value

From above Spreadsheet, the following result is obtained.

{
  "hiddenRows": [2, 3, 5, 6, 8, 9],
  "showingRows": [1, 4, 7, 10, 11, 12, 13, 14, 15]
}
  • hiddenRows is the hidden row numbers.
  • showingRows is the showingRows row numbers.

Note

  • In this case, even when the filter view is not activated, the filtered rows can be retrieved using the existing filter views. By this, for example, prepare several filter views , and you can also retrieve the filtered rows using each filter view.

  • This is a workaround using a simple script. So please modify above script for your actual situation.

References

 Share!