Posted by Wesley Chun (@wescpy), Developer Advocate, G Suite
The G Suite team recently launched the very first Google Slides API, opening up a whole new set of possibilities, including leveraging data already sitting in a spreadsheet or database, and programmatically generating slide decks or slide content based on that data. Why is this a big deal? One of the key advantages of slide decks is that they can take database or spreadsheet data and make it more presentable for human consumption. This is useful when the need arises to communicate the information reflected by that data to management or potential customers.
Walking developers through a short application demonstrating both the Sheets and Slides APIs to make this happen is the topic of today's DevByte video. The sample app starts by reading all the necessary data from the spreadsheet using the Sheets API. The Slides API takes over from there, creating new slides for the data, then populating those slides with the Sheets data.
Developers interact with Slides by sending API requests. Similar to the Google Sheets API, these requests come in the form of JSON payloads. You create an array like in the JavaScript pseudocode below featuring requests to create a cell table on a slide and import a chart from a Sheet:
requests
sheetID
chartID
slideID
presentations().batchUpdate()
SLIDES
Creating tables is fairly straightforward. Creating charts has some magical features, one of those being the linkingMode. A value of "LINKED" means that if the Sheet data changes (altering the chart in the Sheet), the same chart in a slide presentation can be refreshed to match the latest image, either by the API or in the Slides user interface! You can also request a plain old static image that doesn't change with the data by selecting a value of "NOT_LINKED_IMAGE" for linkingMode. More on this can be found in the documentation on creating charts, and check out the video where you'll see both those API requests in action.
linkingMode
For a detailed look at the complete code sample featured in the video, check out the deep dive post. We look forward to seeing the interesting integrations you build with the power of both APIs!
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" }} ]}
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!
Calling all developers: try our developer preview today!
As you can see from above, Android add-ons offer a great opportunity to build innovative integrations and reach Docs and Sheets users around the world. They’re basically Android apps that connect with Google Apps Script projects on the server-side, allowing them to access and manipulate data from Google Docs or Sheets using standard Apps Script techniques. Check out our documentation which includes UI guidelines as well as sample code to get you started. We’ve also made it easy for you to publish your apps with the Apps Script editor.
Android add-ons are available today as a developer preview. We look forward to seeing what you build!
Posted by Wesley Chun (@wescpy), Developer Advocate, Google Apps
At Google I/O 2016, 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 functionality found in the Sheets desktop and mobile user interfaces. My latest DevByte video shows developers 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 brand new Google Sheet.
Let’s take a sneak peek of the code covered in the video. Assuming that SHEETS has been established as the API service endpoint, SHEET_ID is the ID of the Sheet to write to, and data is an array with all the database rows, this is the only call developers need to make to write that raw data into the Sheet:
SHEETS
SHEET_ID
data
SHEETS.spreadsheets().values().update(spreadsheetId=SHEET_ID, range='A1', body=data, valueInputOption='RAW').execute()
rows = SHEETS.spreadsheets().values().get(spreadsheetId=SHEET_ID, range='Sheet1').execute().get('values', []) for row in rows: print(row)
If you’re ready to get started, take a look at the Python or other quickstarts in a variety of languages before checking out the DevByte. If you want a deeper dive into the code covered in the video, check out the post at my Python blog. Once you get going with the API, one of the challenges developers face is in constructing the JSON payload to send in API calls—the common operations samples can really help you with this. Finally, if you’re ready to get going with a meatier example, check out our JavaScript codelab where you’ll write a sample Node.js app that manages customer orders for a toy company, the database of which is used in this DevByte, preparing you for the codelab.
We hope all these resources help developers create amazing applications and awesome tools with the new Google Sheets API! Please subscribe to our channel, give us your feedback below, and tell us what topics you would like to see in future episodes!
Ever look at the data returned when using the Drive API? A files.list call, even if just returning a single file, can yield upwards of 4kb of data. Drive has a rich set of metadata about files, but chances are your application only needs a small fraction of what’s available.
files.list
One of the simplest but most effective optimizations you can make when building apps with the Drive API is limiting the amount of data returned to only those fields needed for your particular use case. The fields query parameter gives you that control, and the results can be dramatic.
A simple example of this is using the files.list call to display a list of files to a user. The naive query, https://www.googleapis.com/drive/v2/files?maxResults=100, generated more than 380kb of data when I ran it against my own corpus. But to render this list nicely, an app only needs a few bits of information -- the document title, icon & thumbnail URLs, the mime type, and of course the file ID.
https://www.googleapis.com/drive/v2/files?maxResults=100
Using the fields query parameter, the results can be trimmed to just the necessary fields and those needed for fetching subsequent pages of data. The optimized query is https://www.googleapis.com/drive/v2/files?maxResults=100&fields=items(iconLink%2Cid%2Ckind%2CmimeType%2CthumbnailLink%2Ctitle)%2CnextPageToken.
https://www.googleapis.com/drive/v2/files?maxResults=100&fields=items(iconLink%2Cid%2Ckind%2CmimeType%2CthumbnailLink%2Ctitle)%2CnextPageToken
After modifying the query the resulting data was only 30k. That’s more than a 90% reduction in data size! Besides reducing the amount of data on the wire, these hints also enable us to further optimize how queries are processed. Not only is there less data to send, but also less time spent getting it in the first place.
Our newest set of APIs - Tasks, Calendar v3, Google+ to name a few - are supported by the Google APIs Discovery Service. The Google APIs Discovery service offers an interface that allows developers to programmatically get API metadata such as:
The APIs Discovery Service is especially useful when building developer tools, as you can use it to automatically generate certain features. For instance we are using the APIs Discovery Service in our client libraries and in our APIs Explorer but also to generate some of our online API reference.
Because the APIs Discovery Service is itself an API, you can use features such as partial response which is a way to get only the information you need. Let’s look at some of the useful information that is available using the APIs Discovery Service and the partial response feature.
You can get the list of all the APIs that are supported by the discovery service by sending a GET request to the following endpoint:
GET
https://www.googleapis.com/discovery/v1/apis?fields=items(title,discoveryLink)
Which will return a JSON feed that looks like this:
{ "items": [ … { "title": "Google+ API", "discoveryLink": "./apis/plus/v1/rest" }, { "title": "Tasks API", "discoveryLink": "./apis/tasks/v1/rest" }, { "title": "Calendar API", "discoveryLink": "./apis/calendar/v3/rest" }, … ] }
Using the discoveryLink attribute in the resources part of the feed above you can access the discovery document of each API. This is where a lot of useful information about the API can be accessed.
discoveryLink
Using the API-specific endpoint you can easily get the OAuth 2.0 scopes available for that API. For example, here is how to get the scopes of the Google Tasks API:
https://www.googleapis.com/discovery/v1/apis/tasks/v1/rest?fields=auth(oauth2(scopes))
This method returns the JSON output shown below, which indicates that https://www.googleapis.com/auth/tasks and https://www.googleapis.com/auth/tasks.readonly are the two scopes associated with the Tasks API.
https://www.googleapis.com/auth/tasks
https://www.googleapis.com/auth/tasks.readonly
{ "auth": { "oauth2": { "scopes": { "https://www.googleapis.com/auth/tasks": { "description": "Manage your tasks" }, "https://www.googleapis.com/auth/tasks.readonly": { "description": "View your tasks" } } } } }
Using requests of this type you could detect which APIs do not support OAuth 2.0. For example, the Translate API does not support OAuth 2.0, as it does not provide access to OAuth protected resources such as user data. Because of this, a GET request to the following endpoint:
https://www.googleapis.com/discovery/v1/apis/translate/v2/rest?fields=auth(oauth2(scopes))
Returns:
{}
Using the API-specific endpoints again, you can get the lists of operations and API endpoints, along with the scopes required to perform those operations. Here is an example querying that information for the Google Tasks API:
https://www.googleapis.com/discovery/v1/apis/tasks/v1/rest?fields=resources/*/methods(*(path,scopes,httpMethod))
Which returns:
{ "resources": { "tasklists": { "methods": { "get": { "path": "users/@me/lists/{tasklist}", "httpMethod": "GET", "scopes": [ "https://www.googleapis.com/auth/tasks", "https://www.googleapis.com/auth/tasks.readonly" ] }, "insert": { "path": "users/@me/lists", "httpMethod": "POST", "scopes": [ "https://www.googleapis.com/auth/tasks" ] }, … } }, "tasks": { … } } }
This tells you that to perform a POST request to the users/@me/lists endpoint (to insert a new task) you need to have been authorized with the scope https://www.googleapis.com/auth/tasks and that to be able to do a GET request to the users/@me/lists/{tasklist} endpoint you need to have been authorized with either of the two Google Tasks scopes.
POST
users/@me/lists
users/@me/lists/{tasklist}
You could use this to do some automatic discovery of the scopes you need to authorize to perform all the operations that your applications does.
You could also use this information to detect which operations and which endpoints you can access given a specific authorization token ( OAuth 2.0, OAuth 1.0 or Authsub token). First, use either the Authsub Token Info service or the OAuth 2.0 Token Info Service to determine which scopes your token has access to (see below); and then deduct from the feed above which endpoints and operations requires access to these scopes.
[Access Token] -----(Token Info)----> [Scopes] -----(APIs Discovery)----> [Operations/API Endpoints]
Example of using the OAuth 2.0 Token Info service:
Request:
GET /oauth2/v1/tokeninfo?access_token= HTTP/1.1 Host: www.googleapis.com
Response:
HTTP/1.1 200 OK Content-Type: application/json; charset=UTF-8 … { "issued_to": "1234567890.apps.googleusercontent.com", "audience": "1234567890.apps.googleusercontent.com", "scope": "https://www.google.com/m8/feeds/ https://www.google.com/calendar/feeds/", "expires_in": 1038 }
There is a lot more you can do with the APIs Discovery Service so I invite you to have a deeper look at the documentation to find out more.
POST /calendar/feeds/default/private/full HTTP/1.1Host: www.google.com Authorization: ... Content-Type: application/jsonGData-Version: 2.0 Content-Length: 233{ "data": { "title": "Tennis with Beth", "details": "Meet for a quick lesson.", "transparency": "opaque", "status": "confirmed", "location": "Rolling Lawn Courts", "when": [ { "start": "2010-04-17T15:00:00.000Z", "end": "2010-04-17T17:00:00.000Z" } ] }}
POST /calendar/feeds/default/private/full HTTP/1.1Host: www.google.com Authorization: ...Content-Type: application/atom+xmlGData-Version: 2.0 Content-Length: 571<entry xmlns='http://www.w3.org/2005/Atom' xmlns:gd='http://schemas.google.com/g/2005'> <category scheme='http://schemas.google.com/g/2005#kind' term='http://schemas.google.com/g/2005#event'/> <title type='text'>Tennis with Beth</title> <content type='text'>Meet for a quick lesson.</content> <gd:transparency value='http://schemas.google.com/g/2005#event.opaque'/> <gd:eventStatus value='http://schemas.google.com/g/2005#event.confirmed'/> <gd:where valueString='Rolling Lawn Courts'/> <gd:when startTime='2006-04-17T15:00:00.000Z' endTime='2006-04-17T17:00:00.000Z'/></entry>
GET /calendar/feeds/default/private/full/1234567890?alt=jsonc HTTP/1.1Host: www.google.comAuthorization: ...GData-Version: 2.0
HTTP/1.1 200 OKContent-Type: application/json; charset=UTF-8...{ "apiVersion": "2.3", "data": { "title": "Tennis with Beth", "details": "Meet for a quick lesson.", "location": "Rolling Lawn Courts", ... }}
Posted by Nicolas Garnier, Google Calendar API Team
Want to weigh in on this topic? Discuss on Buzz
