Using the Sheets API to make apps script respect filters

In Using the Google Sheets V4 API from VBA to integrate Sheets and Excel I showed a VBA API that could be used to talk to the Sheets API, to create some integration between Excel and Office. Since the Sheets v4 API has access to some things about a sheet that Apps Script doesn’t,  it can also be used from Apps Script  (as if it was a different platform) to access parts of the sheet that the SpreadsheetApp service cannot yet reach.

As you know, there is no mechanism in Apps Script currently that allows you to know if there is a filter active on a sheet. Ideally what we need is getValues() and getFilterValues() – similar to what you can do in Excel. No doubt it will come, but for now, I’ve created a library for apps script that I’ll add things like this to.

It is called SheetsMore and you can find it at this key

14kDMORQL5oxUt2qfXcUTNb1RwRt4d6_7uAHRVPrjaiPGn6bM0H-7wctO

and on github.

Enabling the API

Before you do anything, then you need to enable the Sheets API in your project.

Go to the Developers Console project associated with your project

Find it

How to use

For now I’ve implemented only the ability to get filtered data from a given sheet, but all the heavy lifting is done, and it’s simple to use.
Here’s how to set up and get values from a given sheet.

var sMore = new SheetsMore.SheetsMore()
  .setAccessToken(ScriptApp.getOAuthToken())
  .setId(SpreadsheetApp.getActiveSpreadsheet().getId())
  .setApplyFilterViews(false);

var values = sMore
  .applyFiltersToData()
  .getValues(range)
  .filteredValues;

Item	Purpose
new SheetsMore.SheetsMore()	create a new instance of the SheetsMore object.
.setAccessToken(ScriptApp.getOAuthToken())	give it an access token to use. Since your script willl already be accessing spreadsheets, then just give it yours.
.setId(SpreadsheetApp.getActiveSpreadsheet().getId())	the id of the sheet to operate on. Here I’m using the current sheet, but you could use any id
.setApplyFilterViews(false);	This applies to filterviews (as opposed to simple data filters). The API can tell if the user has applied simple data filters at a given time, as well as what they are. It always respects these simple data filters. In addition, it can also tell if a sheet is associated with a filter view, but it can’t tell if that filterview is currently active. This says whether or not to respect the filter views associated with this sheet.
.applyFiltersToData()	This will interrogate and update the query for the latest applied filters. Normally you would execute this just before getting values
.getValues(range)	Pass a Range  for the data against which filter need to be applied
.filteredValues;	This is the type of data to return. .filterValues is a values area only including the data that matches the filter criteria. .values is all the values and is the same result as range.getValues() would give. .filterMap is a list of all row numbers that should be visible. Using this you could filter things like background colors and other properties.

The SheetsMore library works like this for filter

• gets any filter definitions from the v4 API that are applicable to the requested sheet and range.
• gets the values for the given range
• applies the filter definitions to the given range

So in other words, the Sheets API is no help in actually applying the filters, so it’s SheetsMore that then attempts to interpret and apply the filter definitions to the values.

Because of this, I haven’t implemented all of the filter conditions yet (such as custom formulas), but the many of them are done which should cover most common uses, since most values are data values or ranges. I won’t make a list of the conditions supported as they’ll be updated from time to time, but you can see from the enums script on github which are done and which are not.

It’s quite a tedious task, so if you want to help do a few then you’re welcome to make a pull request on GitHub

Note that it currently works on actual values ( as opposed to displayed values) so that’s another enhancement I’ll need to get to.

Use case

More