Posted by Bruce Mcpherson and Romain Vialard, Google Developers Experts, and posted by Wesley Chun (@wescpy), G Suite Developer AdvocateDevelopers using
Google Apps Script can now access the richer feature set of the updated
Google Sheets API with the
recent launch of the
Advanced Sheets Service. One key benefit of using an advanced service vs. native Apps Script objects, is that developers can access current API features (without having to wait for native support to come along). For example, the advanced service allows developers to access Sheets filters which make Add-ons more engaging.
Filter functionality
With the Sheets API, developers can already get filtered rows or set new filters on Sheets data. With the
Advanced Sheet Service, developers can now have their Add-ons respect those filters and apply new filters to modify what data is visible in the Sheets UI. Plus, with
any of the
Apps Script advanced services, you can easily access the Sheets and other Google APIs without using the
UrlFetch service nor managing the authorization flow that you’d otherwise have to perform if using the REST API directly. The snippet below will return the indexes of the filtered rows in a given Sheet. Note that it is also possible to retrieve the list of rows hidden manually, using the "hide row" menu item in Google Sheets, as indicated in the
API documentation. In the code sample here, we’re only exposing rows hidden by filter.
function getIndexesOfFilteredRows(ssId, sheetId) {
var hiddenRows = [];
// limit what's returned from the API
var fields = "sheets(data(rowMetadata(hiddenByFilter)),properties/sheetId)";
var sheets = Sheets.Spreadsheets.get(ssId, {fields: fields}).sheets;
for (var i = 0; i < sheets.length; i++) {
if (sheets[i].properties.sheetId == sheetId) {
var data = sheets[i].data;
var rows = data[0].rowMetadata;
for (var j = 0; j < rows.length; j++) {
if (rows[j].hiddenByFilter) hiddenRows.push(j);
}
}
}
return hiddenRows;
}
The fields parameter in the code snippet limits what's returned in the Sheets API response, requesting only the values that matter to your app. For more information,
check out this page in the Sheets API doc or
this recent video on field masks.
See how some Add-ons use filtering
There are a number of Add-ons that use advanced filtering in Sheets. Here are some good examples:
- Yet Another Mail Merge: this Add-on helps users send email campaigns from a spreadsheet and is built to process only the filtered rows of a Sheet. Let's say you have a list of people who are registered for an event, but you've only accepted some of these registrants and need to send an email confirmation. With Yet Another Mail Merge and the updated API, you can filter out people you don't approve to attend and the Add-ons skips them without sending confirmations.
- Sankey Snip and Chord Snip: these Add-ons helps users create special chart types that aren't available in the Google Sheets UI. When respecting filters is enabled with these Add-ons, the charts will dynamically visualize filtered data. Check out the example below from the Chord Snip Add-on.
Of course the API also provides the ability to add, update or delete filters on a Sheet. This is useful if you want to quickly display rows with a specific status to your users. One example would be if you built a workflow approval Add-on. You can show the user rows that are waiting for approval. The snippet below applies the requested filter on a given Sheet—the API documentation describes a standard
basic filter object:
function setSheetBasicFilter(ssId, BasicFilterSettings) {
//requests is an array of batchrequests, here we only use setBasicFilter
var requests = [
{
"setBasicFilter": {
"filter": BasicFilterSettings
}
}
];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}
Yet Another Mail Merge, as many mass-mailing tools do, keeps track of all emails sent, opened and clicked. A tracking report is available in the spreadsheet sidebar, and clicking on the number of emails opened will automatically apply a filter to display only the matching rows—all rows with the status “opened.”
Now, you can determine filters applied in a Sheet directly through the
Sheets API or through
Apps Script apps and
Add-ons using the
Advanced Sheets Service, and continue to build the best experience for your users.
About the Authors
Romain Vialard is a
Google Developer Expert. After some years spent as a G Suite consultant, he is now focused on products for G Suite and Google Apps users, including add-ons such as
Yet Another Mail Merge and
Form Publisher.
Bruce Mcpherson is a
Google Developer Expert, an independent consultant,
blogger and author of
Going GAS,
Google Apps Script for Beginners, and
Google Apps Script for Developers.
