Tag Archives: Google Sheets API

Using field masks with update requests to Google APIs

Originally posted on the G Suite Developers Blog
Posted by Wesley Chun (@wescpy), Developer Advocate, G Suite

We recently demonstrated how to use field masks to limit the amount of data that comes back via response payloads from read (GET) calls to Google APIs. Today, we'll focus on a different use case for field masks: update requests.

In this scenario, field masks serve a different, but similar purpose—they still filter, but function more like bitmasks by controlling which API fields to update. The following video walks through several examples of update field mask usage with both the Google Sheets and Slides APIs. Check it out.


In the sample JSON payload below, note the request to set the cells’ bold attribute to true (per the cell directive below), then notice that the field mask (fields) practically mirrors the request:

{
"repeatCell": {
"range": {
"endRowIndex": 1
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"bold": true
}
}
},
"fields": "userEnteredFormat/textFormat/bold",
}
}

Now, you might think, "is that redundant?" Above, we highlighted that it takes two parts: 1) the request provides the data for the desired changes, and 2) the field mask states what should be updated, such as the userEnteredFormat/textFormat/bold attribute for all the cells in the first row. To more clearly illustrate this, let's add something else to the mask like italics so that it has both bold and italic fields:

        "fields": "userEnteredFormat/textFormat(bold,italic)"
However, while both elements are in the field mask, we've only provided the update data for bold. There's no data for italic setting specified in the request body. In this case, italics for all cells will be reset, meaning if the cells were originally italicized, those italics will be removed after this API request completes. And vice versa, if the cells were not italicized to begin with, they'll stay that way. This feature gives developers the ability to undo or reset any prior settings on affected range of cells. Check out the video for more examples and tips for using field masks for update requests.

To learn more about using field masks for partial response in API payloads, check out this video and the first post in this two-part series. For one of the most comprehensive write-ups on both (read and update) use cases, see the guide in the Google Slides API documentation. Happy field-masking!

Using field masks with update requests to Google APIs



We recently demonstrated how to use field masks to limit the amount of data that comes back via response payloads from read (GET) calls to Google APIs. Today, we’ll focus on a different use case for field masks: update requests.

In this scenario, field masks serve a different, but similar purpose—they still filter, but function more like bitmasks by controlling which API fields to update. The following video walks through several examples of update field mask usage with both the Google Sheets and Slides APIs. Check it out.
2
In the sample JSON payload below, note the request to set the cells' bold attribute to true (per the cell directive below), then notice that the field mask (fields) practically mirrors the request:
{
"repeatCell": {
"range": {
"endRowIndex": 1
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"bold": true
}
}
},
"fields": "userEnteredFormat/textFormat/bold",
}
}
Now, you might think, “is that redundant?” Above, we highlighted that it takes two parts: 1) the request provides the data for the desired changes, and 2) the field mask states what should be updated, such as the userEnteredFormat/textFormat/bold attribute for all the cells in the first row. To more clearly illustrate this, let’s add something else to the mask like italics. Here, the updated field mask now has both bold and italic fields:
"fields": "userEnteredFormat/textFormat(bold,italic)"

However, while both elements are in the field mask, we’ve only provided the update data for bold. There’s no data for italic setting specified in the request body. In this case, for all cells will be reset, meaning if the cells were originally italicized, those italics will be removed after this API request completes. And vice versa, if the cells were not italicized to begin with, they’ll stay that way. This feature gives developers the ability to undo or reset any prior settings on affected range of cells. Check out the video for more examples and tips for using field masks for update requests.

To learn more about using field masks for partial response in API payloads, check out this video and the first post in this two-part series. For one of the most comprehensive write-ups on both (read and update) use cases, see the guide in the Google Slides API documentation.  Happy field-masking!

Using Google Sheets filters in Add-ons with Google Apps Script



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

Using field masks with Google APIs for partial response

Originally posted on the G Suite Developers Blog by Wesley Chun, Developer Advocate, G Suite

When you write applications using Google APIs (not just G Suite ones, but most Google APIs including YouTube or Google Cloud Platform APIs), it's important to be mindful of the data that’s returned in the response payloads from API calls. If you're not, your apps are likely getting back much more data than they need which can affect the performance of your apps whether on mobile or a server backend.

That's why most Google APIs allow you to only filter the data you need from response payloads with field masks. To get you comfortable with field masks, we’ve put together a video to demonstrate their use with various Google APIs: With field masks, you can specify exactly what fields an API should return in its response payload by providing a fields or part parameter in your calls. And once the API knows what you want, it will likely spend less time assembling your response too. Here’s an example Python call to fetch your sender addresses using the Gmail API (if GMAIL is your service endpoint):
     addresses = GMAIL.users().settings().sendAs().list(
userId='me'
).execute().get('sendAs')

Whether you’re using a Client Library (as our Python call) or using HTTP directly with GET https://www.googleapis.com/gmail/v1/users/userId/settings/sendAs, this is the payload you get back from the API:
     {
"sendAs": [{
"sendAsEmail": string,
"displayName": string,
"replyToAddress": string,
"signature": string,
"isPrimary": boolean,
"isDefault": boolean,
"treatAsAlias": boolean,
"smtpMsa": {
"host": string,
"port": integer,
"username": string,
"password": string,
"securityMode": string
},
"verificationStatus": string
}, ...]
}

The sendAs array gives you everything you need to know about each of your sender addresses. Did you know you can change a user’s email signature using the Gmail API without all of the data from above? You only need one field, or at most two: sendAsEmail and perhaps the isPrimary flag. By specifying a field mask with just those names from the sendAs attribute, you can cut out all those unneeded fields. Check it out here in Python with the field mask bolded for emphasis (versus the sample code above that doesn’t filter):
     addresses = GMAIL.users().settings().sendAs().list(
userId='me', fields='sendAs(sendAsEmail,isPrimary)'
).execute().get('sendAs')
Field masks filter our unnecessary data from Google API call responses.

In part two of this video series (coming soon), we’ll show you a different use case for field masks...for update API calls. We’ll also provide some usage tips and demonstrate how field masks can be used in both read and update calls, how both types of calls are discrete, and how in some cases, you may use both as part of a single API call. Stay tuned!

To learn more about using field masks for partial response in API payloads, check out this section of the Client Library docs. For one of the most comprehensive write-ups on both (read and update) use cases, see the guide in the Google Slides API documentation.

Using field masks with Google APIs for partial response



When you write applications using Google APIs (not just G Suite ones, but most Google APIs including YouTube or Google Cloud Platform APIs), it's important to be mindful of the data that’s returned in the response payloads from API calls. If you're not, your apps are likely getting back much more data than they need which can affect the performance of your apps whether on mobile or a server backend.

That's why most Google APIs allow you to only filter the data you need from response payloads with field masks. To get you comfortable with field masks, we’ve put together a video to demonstrate their use with various Google APIs: With field masks, you can specify exactly what fields an API should return in its response payload by providing a fields or part parameter in your calls. And once the API knows what you want, it will likely spend less time assembling your response too. Here’s an example Python call to fetch your sender addresses using the Gmail API (if GMAIL is your service endpoint):
     addresses = GMAIL.users().settings().sendAs().list(
userId='me'
).execute().get('sendAs')

Whether you’re using a Client Library (as our Python call) or using HTTP directly with GET https://www.googleapis.com/gmail/v1/users/userId/settings/sendAs, this is the payload you get back from the API:
     {
"sendAs": [{
"sendAsEmail": string,
"displayName": string,
"replyToAddress": string,
"signature": string,
"isPrimary": boolean,
"isDefault": boolean,
"treatAsAlias": boolean,
"smtpMsa": {
"host": string,
"port": integer,
"username": string,
"password": string,
"securityMode": string
},
"verificationStatus": string
}, ...]
}

The sendAs array gives you everything you need to know about each of your sender addresses. Did you know you can change a user’s email signature using the Gmail API without all of the data from above? You only need one field, or at most two: sendAsEmail and perhaps the isPrimary flag. By specifying a field mask with just those names from the sendAs attribute, you can cut out all those unneeded fields. Check it out here in Python with the field mask bolded for emphasis (versus the sample code above that doesn’t filter):
     addresses = GMAIL.users().settings().sendAs().list(
userId='me', fields='sendAs(sendAsEmail,isPrimary)'
).execute().get('sendAs')
Field masks filter our unnecessary data from Google API call responses.

In part two of this video series (coming soon), we’ll show you a different use case for field masks...for update API calls. We’ll also provide some usage tips and demonstrate how field masks can be used in both read and update calls, how both types of calls are discrete, and how in some cases, you may use both as part of a single API call. Stay tuned!

To learn more about using field masks for partial response in API payloads, check out this section of the Client Library docs. For one of the most comprehensive write-ups on both (read and update) use cases, see the guide in the Google Slides API documentation.

A new issue tracker for G Suite developers

, Developer Advocate, G Suite
You may have read recently that the Google Cloud Platform team upgraded to Issue Tracker, the same system that Google uses internally. This allows for improved collaboration between all of us and all of you. Issues you file will have better exposure internally, and you get improved transparency in terms of seeing the issues we’re actively working on. Starting today, G Suite developers will also have a new issue tracker to which we’ve already migrated existing issues from previous systems. Whether it’s a bug that you’ve found, or if you wish to submit a favorite feature request, the new issue tracker is here for you. Heads up, you need to be logged in with your Google credentials to view or update issues in the tracker.
The new issue tracker for G Suite developers. 

Each G Suite API and developer tool has its own “component” number that you can search. For your convenience, below is the entire list. You may browse for issues relevant to the Google APIs that you’re using, or click on the convenience links to report an issue or request a new/missing feature:
To get started, take a look at the documentation pages, as well as the FAQ. For more details, be sure to check out the Google Cloud Platform announcement, too. We look forward to working more closely with all of you soon!

G Suite Developer Sessions at Google Cloud Next 2017

Originally posted on the G Suite Developers Blog

Posted by Wesley Chun (@wescpy), Developer Advocate, G Suite

There are over 200 sessions happening next month at Google Cloud's Next 2017 conferencein San Francisco... so many choices! Along with content geared towards Google Cloud Platform, this year features the addition of G Suite so all 3 pillars of cloud computing (IaaS, PaaS, SaaS) are represented!


There are already thousands of developers including Independent Software Vendors (ISVs) creating solutions to help schools and enterprises running the G Suite collaboration and productivity suite (formerly Google Apps). If you're thinking about becoming one, consider building applications that extend, enhance, and integrate G Suite apps and data with other mission critical systems to help businesses and educational institutions succeed.


Looking for inspiration? Here's a preview of some of the sessions that current and potential G Suite developers should consider:


The first is intro blog post & video for the latest Google Sheets API as well as the intro blog post & video for the Google Slides API. Part of the talk also covers Google Apps Script, the Javascript-in-the-cloud solution that gives developers programmatic access to authorized G Suite data along with the ability to connect to other Google and external services.


If that's not enough Apps Script for you, or you're new to that technology, swing by to hear its Product Manager give you an introduction in his talk, quick intro video to give you an idea of what you can do with it!


Did you know that Apps Script also powers "add-ons" which extend the functionality of Google Docs, Sheets, and Forms? Then come to "the G Suite Marketplace where administrators or employees can install your add-ons for their organizations.


In addition to Apps Script apps, all your Google Docs, Sheets, and Slides documents live in Google Drive. But did you know that Drive is not just for individual file storage? Hear directly from a Drive Product Manager on how you can, "the Drive API and Team Drives, you can extend what Drive can do for your organization. One example from the most recent Google I/O tells the story of how WhatsApp used the Drive API to back up all your conversations! To get started with your own Drive API integration, check out this blog post and short video. Confused by when you should use Google Drive or Google Cloud Storage? I've got an app, err video, for that too! :-)


Not a software engineer but still code as part of your profession? Want to build a custom app for your department or line of business without having to worry about IT overhead? You may have heard about Google App Maker, our low-code development tool that does exactly that. Curious to learn more about it? Hear directly from its Product Manager lead in his talk entitled, "

All of these talks are just waiting for you at
Next, the best place to get your feet wet developing for G Suite, and of course, the Google Cloud Platform. Start by checking out the session schedule. Next will also offer many opportunities to meet and interact with industry peers along with representatives from all over Google who love the cloud. Register today and see you in San Francisco!




G Suite Developer Sessions at Google Cloud Next 2017

Posted by Wesley Chun (@wescpy), Developer Advocate, G Suite

There are over 200 sessions happening next month at Google Cloud's Next 2017 conferencein San Francisco... so many choices! Along with content geared towards Google Cloud Platform, this year features the addition of G Suite so all 3 pillars of cloud computing (IaaS, PaaS, SaaS) are represented!


There are already thousands of developers including Independent Software Vendors (ISVs) creating solutions to help schools and enterprises running the G Suite collaboration and productivity suite (formerly Google Apps). If you're thinking about becoming one, consider building applications that extend, enhance, and integrate G Suite apps and data with other mission critical systems to help businesses and educational institutions succeed.


Looking for inspiration? Here's a preview of some of the sessions that current and potential G Suite developers should consider:


The first is intro blog post & video for the latest Google Sheets API as well as the intro blog post & video for the Google Slides API. Part of the talk also covers Google Apps Script, the Javascript-in-the-cloud solution that gives developers programmatic access to authorized G Suite data along with the ability to connect to other Google and external services.


If that's not enough Apps Script for you, or you're new to that technology, swing by to hear its Product Manager give you an introduction in his talk, quick intro video to give you an idea of what you can do with it!


Did you know that Apps Script also powers "add-ons" which extend the functionality of Google Docs, Sheets, and Forms? Then come to "the G Suite Marketplace where administrators or employees can install your add-ons for their organizations.


In addition to Apps Script apps, all your Google Docs, Sheets, and Slides documents live in Google Drive. But did you know that Drive is not just for individual file storage? Hear directly from a Drive Product Manager on how you can, "the Drive API and Team Drives, you can extend what Drive can do for your organization. One example from the most recent Google I/O tells the story of how WhatsApp used the Drive API to back up all your conversations! To get started with your own Drive API integration, check out this blog post and short video. Confused by when you should use Google Drive or Google Cloud Storage? I've got an app, err video, for that too! :-)


Not a software engineer but still code as part of your profession? Want to build a custom app for your department or line of business without having to worry about IT overhead? You may have heard about Google App Maker, our low-code development tool that does exactly that. Curious to learn more about it? Hear directly from its Product Manager lead in his talk entitled, "

All of these talks are just waiting for you at
Next, the best place to get your feet wet developing for G Suite, and of course, the Google Cloud Platform. Start by checking out the session schedule. Next will also offer many opportunities to meet and interact with industry peers along with representatives from all over Google who love the cloud. Register today and see you in San Francisco!




Formatting cells with the Google Sheets API

Posted by Wesley Chun (@wescpy), Developer Advocate, G Suite
At Google I/O earlier this year, we launched a new Google Sheets API (click here to watch the entire announcement). The updated API includes many new features that weren't available in previous versions, including access to more functionality found in the Sheets desktop and mobile user interfaces. Formatting cells in Sheets is one example of something that wasn't possible with previous versions of the API and is the subject of today's DevByte video.
In our previous Sheets API video, we demonstrated how to get data into and out of a Google Sheet programmatically, walking through a simple script that reads rows out of a relational database and transferring the data to a new Google Sheet. The Sheet created using the code from that video is where we pick up today.

Formatting spreadsheets is accomplished by creating a set of request commands in the form of JSON payloads, and sending them to the API. Here is a sample JavaScript Object made up of an array of requests (only one this time) to bold the first row of the default Sheet automatically created for you (whose ID is 0):

{"requests": [
{"repeatCell": {
"range": {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 1
},
"cell": {
"userEnteredFormat": {
"textFormat": {
"bold": true
}
}
},
"fields": "userEnteredFormat.textFormat.bold"
}}
]}
With at least one request, say in a variable named requests and the ID of the sheet as SHEET_ID, you send them to the API via an HTTP POST to https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}:batchUpdate, which in Python, would be a single call that looks like this:
SHEETS.spreadsheets().batchUpdate(spreadsheetId=SHEET_ID,
body=requests).execute()

For more details on the code in the video, check out the deepdive blog post. As you can probably guess, the key challenge is in constructing the JSON payload to send to API calls—the common operations samples can really help you with this. You can also check out our JavaScript codelab where we guide you through writing a Node.js app that manages customer orders for a toy company, featuring the toy orders data we looked at today but in a relational database. While the resulting equivalent Sheet is featured prominently in today's video, we will revisit it again in an upcoming episode showing you how to generate slides with spreadsheet data using the new Google Slides API, so stay tuned for that!

We hope all these resources help developers enhance their next app using G Suite APIs! Please subscribe to our channel and tell us what topics you would like to see in other episodes of the G Suite Dev Show!