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 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.
Its called SheetsMore, and you can find it at this key
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
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.
The SheetsMore library works like this for filter
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.
If you want to see how to use the sheets v4 with excel, look at Using the Google Sheets V4 API from VBA to integrate Sheets and Excel
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.
You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Integrating Apps with other platforms >