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
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
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()
var values = sMore
| new SheetsMore.SheetsMore()
|| create a new instance of the SheetsMore object.
|| give it an access token to use. Since your script willl already be accessing spreadsheets, then just give it yours.
|| the id of the sheet to operate on. Here I’m using the current sheet, but you could use any id
|| 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.
|| This will interrogate and update the query for the latest applied filters. Normally you would execute this just before getting values
|| Pass a Range for the data against which filter need to be applied
|| 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.
I’m using this in my next version of the Sankey Snip
add-on to which I’ve added this
which can now detect and respects filters in data such as
to filter associated charts such as
So it’s very handy to make add-ons that use server side data more in tune with what’s going on in the UI.
This can eat up your UrlFetchApp quota, since every time you .applyFilters() it makes a fetch, and although exponential backoff is used, there is still a daily quota on UrlFetch too. The Sheets API itself also has a quota which is quite low. If you are doing server polling from the client, then you may want to read Watching for server side changes from the client html service
for how to control polling to minimize quota effects.
I’ll be adding to this library over time as I think of things or see requirements in the community that could be solved with the Sheets API
Why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.