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