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!
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!
PATCH requests allow you to perform partial updates on many of our REST APIs and in most cases can save bandwidth.
PATCH
If you have ever tried to do a PATCH request on an App Engine application, you probably realized that it is not possible and that the list of HTTP Methods allowed is whitelisted to the following methods only: GET, POST, HEAD, PUT and DELETE. Trying to perform a PATCH request raises the following Exception:
GET
POST
HEAD
PUT
DELETE
java.net.ProtocolException: PATCH is not one of the supported http methods: [GET, POST, HEAD, PUT, DELETE]
There is a workaround to this. Most of our APIs support the X-HTTP-Method-Override header. This header can be used in a POST request to “fake” other HTTP methods. Simply set the value of the X-HTTP-Method-Override header to the HTTP method you would like to actually perform.
X-HTTP-Method-Override
For example, to make a PATCH request to the Google Tasks API to update only the Notes field of a particular task you could use the following HTTP request:
Notes
POST /tasks/v1/lists/@default/tasks/TASK_ID HTTP/1.1 Host: www.googleapis.com X-HTTP-Method-Override: PATCH Authorization: Bearer Content-Type: application/json Content-Length: 31 {“Notes” : “Patch is working!”}
Which would be equivalent to this HTTP Request, which is not supported on App Engine:
PATCH /tasks/v1/lists/@default/tasks/TASK_ID HTTP/1.1 Host: www.googleapis.com Authorization: Bearer Content-Type: application/json Content-Length: 31 {“Notes” : “Patch is working!”}
For instance, in an App Engine Java environment you could construct and execute this request this way:
URL url = new URL("https://www.googleapis.com/tasks/v1/" + "lists/@default/tasks/" + TASK_ID); HTTPRequest request = new HTTPRequest(url, HTTPMethod.POST); request.addHeader(new HTTPHeader("X-HTTP-Method-Override", "PATCH")); request.addHeader(new HTTPHeader("Authorization", "Bearer " + ACCESS_TOKEN)); request.setPayload("{\"Notes\" : \"Patch is working!\"}".getBytes()); URLFetchService fetchService = URLFetchServiceFactory.getURLFetchService(); HTTPResponse response = fetchService.fetch(request);
This trick can also be used if your application server is behind a firewall, behind a proxy server or in any other environment where HTTP methods other than POST might not be allowed. In that case you could use the X-HTTP-Method-Override header the same way to workaround these limitations.
You may also use our Google APIs Client library for Java or our Google APIs Client library for Python, both of which have support for PATCH requests and use the X-HTTP-Method-Override header when appropriate.
Google Apps domain administrators can use the Email Audit API to download mailbox accounts for audit purposes in accordance with the Customer Agreement. To improve the security of the data retrieved, the service creates a PGP-encrypted copy of the mailbox which can only be decrypted by providing the corresponding RSA key.
When decrypted, the exported mailbox will be in mbox format, a standard file format used to represent collections of email messages. The mbox format is supported by many email clients, including Mozilla Thunderbird and Eudora.
If you don’t want to install a specific email client to check the content of exported mailboxes, or if you are interested in automating this process and integrating it with your business logic, you can also programmatically access mbox files.
You could fairly easily write a parser for the simple, text-based mbox format. However, some programming languages have native mbox support or libraries which provide a higher-level interface. For example, Python has a module called mailbox that exposes such functionality, and parsing a mailbox with it only takes a few lines of code:
import mailbox def print_payload(message): # if the message is multipart, its payload is a list of messages if message.is_multipart(): for part in message.get_payload(): print_payload(part) else: print message.get_payload(decode=True) mbox = mailbox.mbox('export.mbox') for message in mbox: print message['subject'] print_payload(message)
Let me know your favorite way to parse mbox-formatted files by commenting on Google+.
For any questions related to the Email Audit API, please get in touch with us on the Google Apps Domain Info and Management APIs forum.
Google Docs supports sharing collections and their contents with others. This allows multiple Google Docs resources to be shared at once, and for additional resources added to the collection later to be automatically shared.
Class.io, an EDU application on the Google Apps Marketplace, uses this technique. When a professor creates a new course, the application automatically creates a Google Docs collection for that course and shares it with all the students. This gives the students and professor a single place to go in Google Docs to access and manage all of their course files.
A collection is a Google Docs resource that contains other resources, typically behaving like a folder on a file system.
A collection resource is created by making an HTTP POST to the feed link with the category element’s term set to http://schemas.google.com/docs/2007#folder, for example:
http://schemas.google.com/docs/2007#folder
<?xml version='1.0' encoding='UTF-8'?> <entry xmlns="http://www.w3.org/2005/Atom"> <category scheme="http://schemas.google.com/g/2005#kind" term="http://schemas.google.com/docs/2007#folder"/> <title>Example Collection</title> </entry>
To achieve the same thing using the Python client library, use the following code:
from gdata.docs.data import Resource collection = Resource('folder') collection.title.text = 'Example Collection' # client is an Authorized client collection = client.create_resource(entry)
The new collection returned has a content element indicating the URL to use to add new resources to the collection. Resources are added by making HTTP POST requests to this URL.
content
<content src="https://docs.google.com/feeds/default/private/full/folder%3A134acd/contents" type="application/atom+xml;type=feed" />
This process is simplified in the client libraries. For example, in the Python client library, resources can be added to the new collection by passing the collection into the create_resource method for creating resources, or the move_resource method for moving an existing resource into the collection, like so:
create_resource
move_resource
# Create a new resource of document type in the collection new_resource = Resource(type='document', title='New Document') client.create_resource(new_resource, collection=collection) # Move an existing resource client.move_resource(existing_resource, collection=collection)
Once resources have been added to the collection, the collection can be shared using ACL entries. For example, to add the user user@example.com as a writer to the collection and every resource in the collection, the client creates and adds the ACL entry like so:
user@example.com
writer
from gdata.acl.data import AclScope, AclRole from gdata.docs.data import AclEntry acl = AclEntry( scope = AclScope(value='user@example.com', type='user'), role = AclRole(value='writer') ) client.add_acl_entry(collection, acl)
The collection and its contents are now shared, and this can be verified in the Google Docs user interface:
Note: if the application is adding more than one ACL entry, it is recommended to use batching to combine multiple ACL entries into a single request. For more information on this best practice, see the latest blog post on the topic.
The examples shown here are using the raw protocol or the Python client library. The Java client library also supports managing and sharing collections.
For more information on how to use collections, see the Google Documents List API documentation. You can also find assistance in the Google Documents List API forum.
Since March of this year, Google has supported OAuth 2.0 for many APIs, including Google Data APIs such as Google Calendar, Google Contacts and Google Documents List. Google's implementation of OAuth 2.0 introduces many advantages compared to OAuth 1.0 such as simplicity for developers and a more polished user experience.
We’ve just added support for this authorization mechanism to the gdata-python-client library-- let’s take a look at how it works by retrieving an access token for the Google Calendar and Google Documents List APIs and listing protected data.
First, you will need to retrieve or sync the project from the repository using Mercurial:
hg clone https://code.google.com/p/gdata-python-client/
For more information about installing this library, please refer to the Getting Started With the Google Data Python Library article.
Now that the client library is installed, you can go to your APIs Console to either create a new project, or use information about an existing one from the API Access pane:
Your application will require the user to grant permission for it to access protected APIs on their behalf. It must redirect the user over to Google's authorization server and specify the scopes of the APIs it is requesting permission to access.
Available Google Data API’s scopes are listed in the Google Data FAQ.
Here's how your application can generate the appropriate URL and redirect the user:
import gdata.gauth # The client id and secret can be found on your API Console. CLIENT_ID = '' CLIENT_SECRET = '' # Authorization can be requested for multiple APIs at once by specifying multiple scopes separated by # spaces. SCOPES = ['https://docs.google.com/feeds/', 'https://www.google.com/calendar/feeds/'] USER_AGENT = '' # Save the token for later use. token = gdata.gauth.OAuth2Token( client_id=CLIENT_ID, client_secret=CLIENT_SECRET, scope=' '.join(SCOPES), user_agent=USER_AGENT) # The “redirect_url” parameter needs to match the one you entered in the API Console and points # to your callback handler. self.redirect( token.generate_authorize_url(redirect_url='http://www.example.com/oauth2callback'))
If all the parameters match what has been provided by the API Console, the user will be shown this dialog:
When an action is taken (e.g allowing or declining the access), Google’s authorization server will redirect the user to the specified redirect URL and include an authorization code as a query parameter. Your application then needs to make a call to Google’s token endpoint to exchange this authorization code for an access token.
import atom.http_core url = atom.http_core.Uri.parse_uri(self.request.uri) if 'error' in url.query: # The user declined the authorization request. # Application should handle this error appropriately. pass else: # This is the token instantiated in the first section. token.get_access_token(url.query)
The redirect handler retrieves the authorization code that has been returned by Google’s authorization server and exchanges it for a short-lived access token and a long-lived refresh token that can be used to retrieve a new access token. Both access and refresh tokens are to be kept private to the application server and should never be revealed to other client applications or stored as a cookie.
To store the token object in a secured datastore or keystore, the gdata.gauth.token_to_blob() function can be used to serialize the token into a string. The gdata.gauth.token_from_blob() function does the opposite operation and instantiate a new token object from a string.
gdata.gauth.token_to_blob()
gdata.gauth.token_from_blob()
Now that an access token has been retrieved, it can be used to authorize calls to the protected APIs specified in the scope parameter.
import gdata.calendar.client import gdata.docs.client # Access the Google Calendar API. calendar_client = gdata.calendar.client.CalendarClient(source=USER_AGENT) # This is the token instantiated in the first section. calendar_client = token.authorize(calendar_client) calendars_feed = client.GetCalendarsFeed() for entry in calendars_feed.entry: print entry.title.text # Access the Google Documents List API. docs_client = gdata.docs.client.DocsClient(source=USER_AGENT) # This is the token instantiated in the first section. docs_client = token.authorize(docs_client) docs_feed = client.GetDocumentListFeed() for entry in docs_feed.entry: print entry.title.text
For more information about OAuth 2.0, please have a look at the developer’s guide and let us know if you have any questions by posting them in the support forums for the APIs you’re accessing.
Updated 9/30/2011 to fix a small typo in the code
