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 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 

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



Enable 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;

Walkthrough

 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.


Completeness

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

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.


Cautions

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.

Excel

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

More

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 forumfollow 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

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.






Comments