Showing posts with label OAuth2. Show all posts
Showing posts with label OAuth2. Show all posts

Monday, June 26, 2017

Modifying events with the Google Calendar API

NOTE: The code featured here is also available as a video + overview post as part of this developers series from Google.

Introduction

In an earlier post, I introduced Python developers to adding events to users' calendars using the Google Calendar API. However, while being able to insert events is "interesting," it's only half the picture. If you want to give your users a more complete experience, modifying those events is a must-have. In this post, you'll learn how to modify existing events, and as a bonus, learn how to implement repeating events too.

In order to modify events, we need the full Calendar API scope:
  • 'https://www.googleapis.com/auth/calendar'—read-write access to Calendar
Skipping the OAuth2 boilerplate, once you have valid authorization credentials, create a service endpoint to the Calendar API like this:
GCAL = discovery.build('calendar', 'v3',
    http=creds.authorize(Http()))
Now you can send the API requests using this endpoint.

Using the Google Calendar API

Our sample script requires an existing Google Calendar event, so either create one programmatically with events().insert() & save its ID as we showed you in that earlier post, or use events().list() or events().get() to get the ID of an existing event.

While you can use an offset from GMT/UTC, such as the GMT_OFF variable from the event insert post, today's code sample "upgrades" to a more general IANA timezone solution. For Pacific Time, it's "America/Los_Angeles". The reason for this change is to allow events that survive across Daylight Savings Time shifts. IOW, a dinner at 7PM/1900 stays at 7PM as we cross fall and spring boundaries. This is especially important for events that repeat throughout the year. Yes, we are discussing recurrence in this post too, so it's particularly relevant.

Modifying calendar events

In the other post, the EVENT body constitutes an "event record" containing the information necessary to create a calendar entry—it consists of the event name, start & end times, and invitees. That record is an API resource which you created/accessed with the Calendar API via events().insert(). (What do you think the "R" in "URL" stands for anyway?!?) The Calendar API adheres to RESTful semantics in that the HTTP verbs match the actions you perform against a resource.

In today's scenario, let's assume that dinner from the other post didn't work out, but that you want to reschedule it. Furthermore, not only do you want to make that dinner happen again, but because you're good friends, you've made a commitment to do dinner every other month for the rest of the year, then see where things stand. Now that we know what we want, we have a choice.

There are two ways to modifying existing events in Calendar:
  1. events().patch() (HTTP PATCH)—"patch" 1 or more fields in resource
  2. events().update() (HTTP PUT)—replace/rewrite entire resource
Do you just update that resource with events().patch() or do you replace the entire resource with events().update()? To answer that question, ask yourself, "How many fields am I updating?" In our case, we only want to change the date and make this event repeat, so PATCH is a better solution. If instead, you also wanted to rename the event or switch dinner to another set of friends, you'd then be changing all the fields, so PUT would be a better solution in that case.

Using PATCH means you're just providing the deltas between the original & updated event, so the EVENT body this time reflects just those changes:
TIMEZONE = 'America/Los_Angeles'
EVENT = {
    'start':  {'dateTime': '2017-07-01T19:00:00', 'timeZone': TIMEZONE},
    'end':    {'dateTime': '2017-07-01T22:00:00', 'timeZone': TIMEZONE},
    'recurrence': ['RRULE:FREQ=MONTHLY;INTERVAL=2;UNTIL=20171231']
}

Repeating events

Something you haven't seen before is how to do repeating events. To do this, you need to define what’s known as a recurrence rule ("RRULE"), which answers the question of how often an event repeats. It looks somewhat cryptic but follows the RFC 5545 Internet standard which you can basically decode like this:
  • FREQ=MONTHLY—event to occur on a monthly basis...
  • INTERVAL=2—... but every two months (every other month)
  • UNTIL=20171231—... until this date
There are many ways events can repeat, so I suggest you look at all the examples at the RFC link above.

Finishing touches

Finally, provide the EVENT_ID and call events().patch():
EVENT_ID = YOUR_EVENT_ID_STR_HERE # use your own!
e = GCAL.events().patch(calendarId='primary', eventId=EVENT_ID,
        sendNotifications=True, body=EVENT).execute()
Keep in mind that in real life, your users may be accessing your app from their desktop or mobile devices, so you need to ensure you don't override an earlier change. In this regard, developers should use the If-Match header along with an ETag value to validate unique requests. For more information, check out the conditional modification page in the official docs.

The one remaining thing is to confirm on-screen that the calendar event was updated successfully. We do that by checking the return value—it should be an Event object with all the existing details as well as the modified fields:
print('''\
*** %r event (ID: %s) modified:
    Start: %s
    End:   %s
    Recurring (rule): %s
''' % (e['summary'].encode('utf-8'), e['id'], e['start']['dateTime'],
        e['end']['dateTime'], e['recurrence'][0]))
That's pretty much the entire script save for the OAuth2 boilerplate code we've explored previously. The script is posted below in its entirety, and if you add a valid event ID and run it, depending on the date/times you use, you'll see something like this:
$ python gcal_modify.py
*** 'Dinner with friends' event (ID: YOUR_EVENT_ID_STR_HERE) modified:
    Start: 2017-07-01T19:00:00-07:00
    End:   2017-07-01T22:00:00-07:00
    Recurring (rule): RRULE:FREQ=MONTHLY;UNTIL=20171231;INTERVAL=2
It also works with Python 3 with one slight nit/difference being the "b" prefix on from the event name due to converting from Unicode to bytes:
*** b'Dinner with friends' event...

Conclusion

Now you know how to modify events as well as make them repeat. To complete the example, below is the entire script for your convenience which runs on both Python 2 and Python 3 (unmodified!):
from __future__ import print_function
from apiclient.discovery import build
from httplib2 import Http
from oauth2client import file, client, tools

SCOPES = 'https://www.googleapis.com/auth/calendar'
store = file.Storage('storage.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
    creds = tools.run_flow(flow, store)
CAL = build('calendar', 'v3', http=creds.authorize(Http()))

TIMEZONE = 'America/Los_Angeles'
EVENT = {
    'start':  {'dateTime': '2017-07-01T19:00:00', 'timeZone': TIMEZONE},
    'end':    {'dateTime': '2017-07-01T22:00:00', 'timeZone': TIMEZONE},
    'recurrence': ['RRULE:FREQ=MONTHLY;INTERVAL=2;UNTIL=20171231']
}
EVENT_ID = YOUR_EVENT_ID_STR_HERE
e = GCAL.events().patch(calendarId='primary', eventId=EVENT_ID,
        sendNotifications=True, body=EVENT).execute()

print('''\
*** %r event (ID: %s) modified:
    Start: %s
    End:   %s
    Recurring (rule): %s
''' % (e['summary'].encode('utf-8'), e['id'], e['start']['dateTime'],
        e['end']['dateTime'], e['recurrence'][0]))
You can now customize this code for your own needs, for a mobile frontend, a server-side backend, or to access other Google APIs. If you want to learn more about using the Google Calendar API, check out the following resources:


Tuesday, December 6, 2016

Modifying email signatures with the Gmail API

NOTE: The content here is also available as a video and overview post, part of this series.

UPDATE (Feb 2017): Tweaked the code sample as the isPrimary flag may be missing from non-primary aliases; also added link above to video.

Introduction

In a previous post, I introduced Python developers to the Gmail API with a tutorial on how to search for threads with a minimum number of messages. Today, we'll explore another part of the API, covering the settings endpoints that were added in mid-2016. What's the big deal? Well, you couldn't use the API to read nor modify user settings before, and now you can!

One example all of us can relate to is your personal email signature. Wouldn't it be great if we could modify it programmatically, say to include some recent news about you (perhaps a Tweet other social post), or maybe some random witty quote? You could then automate it to change once a quarter, or even hourly if you like being truly random!

Using the Gmail API

Our simple Python script won't be sending email nor reading user messages, so the only authorization scope needed is the one that accesses basic user settings (there's another for more sensitive user settings):
  • https://www.googleapis.com/auth/gmail.settings.basic — Manage basic Gmail user settings
See the documentation for a list of all Gmail API scopes and what each of them mean. Since we've fully covered the authorization boilerplate in earlier posts and videos, including how to connect to the Gmail API, we're going to skip that here and jump right to the action. You can copy the boilerplate from other scripts you've written. Regardless, be sure to create an service endpoint to the API:

GMAIL = discovery.build('gmail', 'v1',
    http=creds.authorize(Http()))


What are "sendAs" email addresses?

First, a quick word about "sendAs" email addresses. Gmail lets you send email from addresses other than your actual Gmail address (considered your primary address). This lets you manage multiple accounts from the same Gmail user interface. (As expected, you need to own or otherwise have access to the alternate email addresses in order to do this.) However, most people only use their primary address, so you may not know about it. You can learn more about sendAs addresses here and here.

Now you may be tempted to use the term "alias," especially because that word was mentioned in those Help pages you just looked at right? However for now, I'd recommend trying to avoid that terminology as it refers to something else in a G Suite/Google Apps context. Can't you see how we already got distracted from the core reason for this post? See, you almost forgot about email signatures already, right? If you stick with "sender addresses" or "sendAs email addresses," there won’t be any confusion.

Using a "Quote of the Day" in your email signature

The Python script we're exploring in this post sets a "Quote of the Day" (or "QotD" for short) as the signature of your primary sendAs address. Where does the QotD come from? Well, it can be as simple (and boring) as this function that returns a hardcoded string:



Cute but not very random right? A better idea is to choose from a number of quotes you have in a relational database w/columns for quotes & authors. Here’s some sample code for data in a SQLite database:



More random, which is cool, but this particular snippet isn't efficient because we’re selecting all rows and then choosing a quote randomly. Obviously there's a better way if a database is your data source. I prefer using a web service instead, coming in the form of a REST API. The code snippet here does just that:



You only need to find a quote-of-the-day service and provide its URL on line 8 that returns a JSON payload. Obviously you'll need a bit more scaffolding if this were a real service, but in this pseudocode example, you can assume that using urllib.{,request.}urlopen() works where the service sends back an empty string upon failure. To play it safe, this snippet falls back to the hardcoded string we saw earlier if the service doesn't return a quote, which comes back as a 2-tuple representing quote and author, respectively.

Setting your new email signature

Now that we're clear on the source for the QotD, we can focus on actually setting it as your new email signature. To do that, we need to get all of your sender (sendAs email) addresses—the goal is only to change your primary addresses (and none of the others if you have any):
addresses = GMAIL.users().settings().sendAs().list(userId='me',
    fields='sendAs(isPrimary,sendAsEmail)').execute().get('sendAs')
As in our other Gmail example, a userId of 'me' indicates the currently-authenticated user. The API will return a number of attributes. If know exactly which ones we want, we can specify them in with the fields attribute so as to control size of the return payload which may contribute to overall latency. In our case, we're requesting just the sendAs.isPrimary flag and sendAs.sendAsEmail, the actual email address string of the sender addresses. What's returned is a Python list consisting of all of your sendAs email addresses, which we cycle through to find the primary address:
for address in addresses:
    if address.get('isPrimary'):
        break
One of your sender addresses must be primary, so unless there's a bug in Gmail, when control of the for loop concludes, address will point to your primary sender address. Now all you have to do is set the signature and confirm to the user:
rsp = GMAIL.users().settings().sendAs().patch(userId='me',
        sendAsEmail=address['sendAsEmail'], body=DATA).execute()
print("Signature changed to '%s'" % rsp['signature'])
If you only have one sender address, there's no need request all the addresses and loop through them looking for the primary address as we did above. In such circumstances, that entire request and loop are extraneous... just pass your email address as the sendAsEmail argument, like this:
rsp = GMAIL.users().settings().sendAs().patch(userId='me',
        sendAsEmail=YOUR_EMAIL_ADDR_HERE, body=DATA).execute()

Conclusion

That's all there is... just 26 lines of code. If we use the static string qotd() function above, your output when running this script will look like this:
$ python gmail_change_sig.py # or python3
Signature changed to '"I heart cats."  ~anonymous'
$
Below is the entire script for your convenience which runs on both Python 2 and Python 3 (unmodified!). By using, copying, and/or modifying this code or any other piece of source from this blog, you implicitly agree to its Apache2 license:
from __future__ import print_function

from apiclient import discovery
from httplib2 import Http
from oauth2client import file, client, tools

import qotd
DATA = {'signature': qotd.qotd()}   # quote source up-to-you!

SCOPES = 'https://www.googleapis.com/auth/gmail.settings.basic'
store = file.Storage('storage.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
    creds = tools.run_flow(flow, store)
GMAIL = discovery.build('gmail', 'v1', http=creds.authorize(Http()))
# this entire block optional if you only have one sender address
addresses = GMAIL.users().settings().sendAs().list(userId='me',
        fields='sendAs(isPrimary,sendAsEmail)').execute().get('sendAs')
for address in addresses:
    if address.get('isPrimary'):
        break
rsp = GMAIL.users().settings().sendAs().patch(userId='me',
        sendAsEmail=address['sendAsEmail'], body=DATA).execute()
print("Signature changed to '%s'" % rsp['signature'])
As with our other code samples, you can now customize for your own needs, for a mobile frontend, sysadmin script, or a server-side backend, perhaps accessing other Google APIs.

Code challenge

Want to exercise your newfound knowledge of using the Gmail API's settings endpoints? Write a script that uses the API to manage filters or configure a vacation responder. HINT: take a look at the official Gmail API docs, including the pages specific to filters and vacation settings.

Tuesday, November 29, 2016

Generating slides from spreadsheet data

NOTE: The code covered in this post are also available in a video walkthrough.


Introduction

A common use case when you have data in a spreadsheet or database, is to find ways of making that data more visually appealing to others. This is the subject of today's post, where we'll walk through a simple Python script that generates presentation slides based on data in a spreadsheet using both the Google Sheets and Slides APIs.

Specifically, we'll take all spreadsheet cells containing values and create an equivalent table on a slide with that data. The Sheet also features a pre-generated pie chart added from the Explore in Google Sheets feature that we'll import into a blank slide. Not only do we do that, but if the data in the Sheet is updated (meaning the chart is as well), then so can the imported chart image in the presentation. These are just two examples of generating slides from spreadsheet data. The example Sheet we're getting the data from for this script looks like this:


The data in this Sheet originates from the Google Sheets API codelab. In the codelab, this data lives in a SQLite relational database, and in the previous post covering how to migrate SQL data to Google Sheets, we "imported" that data into the Sheet we're using. As mentioned before, the pie chart comes from the Explore feature.

Using the Google Sheets & Slides APIs

The scopes needed for this application are the read-only scope for Sheets (to read the cell contents and the pie chart) and the read-write scope for Slides since we're creating a new presentation:
  • 'https://www.googleapis.com/auth/spreadsheets.readonly' — Read-only access to Google Sheets and properties
  • 'https://www.googleapis.com/auth/presentations' — Read-write access to Slides and Slides presentation properties
If you're new to using Google APIs, we recommend reviewing earlier posts & videos covering the setting up projects and the authorization boilerplate so that we can focus on the main app. Once we've authorized our app, two service endpoints are created, one for each API. The one for Sheets is saved to the SHEETS variable while the one for Slides goes to SLIDES.

Start with Sheets

The first thing to do is to grab all the data we need from the Google Sheet using the Sheets API. You can either supply your own Sheet with your own chart, or you can run the script from the earlier post mentioned earlier to create an identical Sheet as above. In either case, you need to provide the Sheet ID to read from, which is saved to the sheetID variable. Using its ID, we call spreadsheets().values().get() to pull out all the cells (as rows & columns) from the Sheet and save it to orders:
sheetID = '. . .'   # use your own!
orders = SHEETS.spreadsheets().values().get(range='Sheet1',
        spreadsheetId=sheetID).execute().get('values')
The next step is to call spreadsheets().get() to get all the sheets in the Sheet —there's only one, so grab it at index 0. Since this sheet only has one chart, we also use index 0 to get that:
sheet = SHEETS.spreadsheets().get(spreadsheetId=sheetID,
        ranges=['Sheet1']).execute().get('sheets')[0]
chartID = sheet['charts'][0]['chartId']
That's it for Sheets. Everything from here on out takes places in Slides.

Create new Slides presentation

A new slide deck can be created with SLIDES.presentations().create()—or alternatively with the Google Drive API which we won't do here. We'll name it, "Generating slides from spreadsheet data DEMO" and save its (new) ID along with the IDs of the title and subtitle textboxes on the (one) title slide created in the new deck:
DATA = {'title': 'Generating slides from spreadsheet data DEMO'}
rsp = SLIDES.presentations().create(body=DATA).execute()
deckID = rsp['presentationId']
titleSlide = rsp['slides'][0]
titleID = titleSlide['pageElements'][0]['objectId']
subtitleID = titleSlide['pageElements'][1]['objectId']

Create slides for table & chart

A mere title slide doesn't suffice as we need a place for the cell data as well as the pie chart, so we'll create slides for each. While we're at it, we might as well fill in the text for the presentation title and subtitle. These requests are self-explanatory as you can see below in the reqs variable. The SLIDES.presentations().batchUpdate() method is then used to send the four commands to the API. Upon return, save the IDs for both the cell table slide as well as the blank slide for the chart:
reqs = [
  {'createSlide': {'slideLayoutReference': {'predefinedLayout': 'TITLE_ONLY'}}},
  {'createSlide': {'slideLayoutReference': {'predefinedLayout': 'BLANK'}}},
  {'insertText': {'objectId': titleID,    'text': 'Importing Sheets data'}},
  {'insertText': {'objectId': subtitleID, 'text': 'via the Google Slides API'}},
]
rsp = SLIDES.presentations().batchUpdate(body={'requests': reqs},
        presentationId=deckID).execute().get('replies')
tableSlideID = rsp[0]['createSlide']['objectId']
chartSlideID = rsp[1]['createSlide']['objectId']
Note the order of the requests. The create-slide requests come first followed by the text inserts. Responses that come back from the API are returned in the same order as they were sent, hence why the cell table slide ID comes back first (index 0) followed by the chart slide ID (index 1). The text inserts don't have any meaningful return values and are thus ignored.

Filling out the table slide

Now let's focus on the table slide. There are two things we need to accomplish. In the previous set of requests, we asked the API to create a "title only" slide, meaning there's (only) a textbox for the slide title. The next snippet of code gets all the page elements on that slide so we can get the ID of that textbox, the only thing on that page:
rsp = SLIDES.presentations().pages().get(presentationId=deckID,
        pageObjectId=tableSlideID).execute().get('pageElements')
textboxID = rsp[0]['objectId'] 
On this slide, we need to add the cell table for the Sheet data, so a create-table request takes care of that. The required elements in such a call include the ID of the slide the table should go on as well as the total number of rows and columns desired. Fortunately all that are available from tableSlideID and orders saved earlier. Oh, and add a title for this table slide too. Here's the code:
reqs = [
    {'createTable': {
        'elementProperties': {'pageObjectId': tableSlideID},
        'rows': len(orders),
        'columns': len(orders[0])},
    },
    {'insertText': {'objectId': textboxID, 'text': 'Toy orders'}},
]
rsp = SLIDES.presentations().batchUpdate(body={'requests': reqs},
        presentationId=deckID).execute().get('replies')
tableID = rsp[0]['createTable']['objectId']
Another call to SLIDES.presentations().batchUpdate() and we're done, saving the ID of the newly-created table. Next, we'll fill in each cell of that table.

Populate table & add chart image

The first set of requests needed now fill in each cell of the table. The most compact way to issue these requests is with a double-for loop list comprehension. The first loops over the rows while the second loops through each column (of each row). Magically, this creates all the text insert requests needed.
reqs = [
    {'insertText': {
        'objectId': tableID,
        'cellLocation': {'rowIndex': i, 'columnIndex': j},
        'text': str(data),
    }} for i, order in enumerate(orders) for j, data in enumerate(order)]
The final request "imports" the chart from the Sheet onto the blank slide whose ID we saved earlier. Note, while the dimensions below seem completely arbitrary, be assured we're using the same size & transform as a blank rectangle we drew on the slide earlier (and read those values from). The alternative would be to use math to come up with your object dimensions. Here is the code we're talking about, followed by the actual call to the API:
reqs.append({'createSheetsChart': {
    'spreadsheetId': sheetID,
    'chartId': chartID,
    'linkingMode': 'LINKED',
    'elementProperties': {
        'pageObjectId': chartSlideID,
        'size': {
            'height': {'magnitude': 7075, 'unit': 'EMU'},
            'width':  {'magnitude': 11450, 'unit': 'EMU'}
        },
        'transform': {
            'scaleX': 696.6157,
            'scaleY': 601.3921,
            'translateX': 583875.04,
            'translateY': 444327.135,
            'unit': 'EMU',
        },
    },
}})
SLIDES.presentations().batchUpdate(body={'requests': reqs},
        presentationId=deckID).execute()
Once all the requests have been created, send them to the Slides API then we're done. (In the actual app, you'll see we've sprinkled various print() calls to let the user knows which steps are being executed.

Conclusion

The entire script clocks in at just under 100 lines of code... see below. If you run it, you should get output that looks something like this:
$ python3 slides_table_chart.py
** Fetch Sheets data
** Fetch chart info from Sheets
** Create new slide deck
** Create 2 slides & insert slide deck title+subtitle
** Fetch table slide title (textbox) ID
** Create table & insert table slide title
** Fill table cells & create linked chart to Sheets
DONE
When the script has completed, you should have a new presentation with these 3 slides:




Below is the entire script for your convenience which runs on both Python 2 and Python 3 (unmodified!). If I were to divide the script into major sections, they would be represented by each of the print() calls above. Here's the complete script—by using, copying, and/or modifying this code or any other piece of source from this blog, you implicitly agree to its Apache2 license:
from __future__ import print_function

from apiclient import discovery
from httplib2 import Http
from oauth2client import file, client, tools

SCOPES = (
    'https://www.googleapis.com/auth/spreadsheets.readonly',
    'https://www.googleapis.com/auth/presentations',
)
store = file.Storage('storage.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
    creds = tools.run_flow(flow, store)
HTTP = creds.authorize(Http())
SHEETS = discovery.build('sheets', 'v4', http=HTTP)
SLIDES = discovery.build('slides', 'v1', http=HTTP)

print('** Fetch Sheets data')
sheetID = '. . .'   # use your own!
orders = SHEETS.spreadsheets().values().get(range='Sheet1',
        spreadsheetId=sheetID).execute().get('values')

print('** Fetch chart info from Sheets')
sheet = SHEETS.spreadsheets().get(spreadsheetId=sheetID,
        ranges=['Sheet1']).execute().get('sheets')[0]
chartID = sheet['charts'][0]['chartId']

print('** Create new slide deck')
DATA = {'title': 'Generating slides from spreadsheet data DEMO'}
rsp = SLIDES.presentations().create(body=DATA).execute()
deckID = rsp['presentationId']
titleSlide = rsp['slides'][0]
titleID = titleSlide['pageElements'][0]['objectId']
subtitleID = titleSlide['pageElements'][1]['objectId']

print('** Create 2 slides & insert slide deck title+subtitle')
reqs = [
  {'createSlide': {'slideLayoutReference': {'predefinedLayout': 'TITLE_ONLY'}}},
  {'createSlide': {'slideLayoutReference': {'predefinedLayout': 'BLANK'}}},
  {'insertText': {'objectId': titleID,    'text': 'Importing Sheets data'}},
  {'insertText': {'objectId': subtitleID, 'text': 'via the Google Slides API'}},
]
rsp = SLIDES.presentations().batchUpdate(body={'requests': reqs},
        presentationId=deckID).execute().get('replies')
tableSlideID = rsp[0]['createSlide']['objectId']
chartSlideID = rsp[1]['createSlide']['objectId']

print('** Fetch table slide title (textbox) ID')
rsp = SLIDES.presentations().pages().get(presentationId=deckID,
        pageObjectId=tableSlideID).execute().get('pageElements')
textboxID = rsp[0]['objectId']

print('** Create table & insert table slide title')
reqs = [
    {'createTable': {
        'elementProperties': {'pageObjectId': tableSlideID},
        'rows': len(orders),
        'columns': len(orders[0])},
    },
    {'insertText': {'objectId': textboxID, 'text': 'Toy orders'}},
]
rsp = SLIDES.presentations().batchUpdate(body={'requests': reqs},
        presentationId=deckID).execute().get('replies')
tableID = rsp[0]['createTable']['objectId']

print('** Fill table cells & create linked chart to Sheets')
reqs = [
    {'insertText': {
        'objectId': tableID,
        'cellLocation': {'rowIndex': i, 'columnIndex': j},
        'text': str(data),
    }} for i, order in enumerate(orders) for j, data in enumerate(order)]

reqs.append({'createSheetsChart': {
    'spreadsheetId': sheetID,
    'chartId': chartID,
    'linkingMode': 'LINKED',
    'elementProperties': {
        'pageObjectId': chartSlideID,
        'size': {
            'height': {'magnitude': 7075, 'unit': 'EMU'},
            'width':  {'magnitude': 11450, 'unit': 'EMU'}
        },
        'transform': {
            'scaleX': 696.6157,
            'scaleY': 601.3921,
            'translateX': 583875.04,
            'translateY': 444327.135,
            'unit': 'EMU',
        },
    },
}})
SLIDES.presentations().batchUpdate(body={'requests': reqs},
        presentationId=deckID).execute()
print('DONE')
As with our other code samples, you can now customize it to learn more about the API, integrate into other apps for your own needs, for a mobile frontend, sysadmin script, or a server-side backend!

Code challenge

Given the knowledge you picked up from this post and its code sample, augment the script with another call to the Sheets API that updates the number of toys ordered by one of the customers, then add the corresponding call to the Slides API that refreshes the linked image based on the changes made to the Sheet (and chart). EXTRA CREDIT: Use the Google Drive API to monitor the Sheet so that any updates to toy orders will result in an "automagic" update of the chart image in the Slides presentation.

Wednesday, September 28, 2016

Formatting cells in Google Sheets with Python

Introduction

One of the critical things that developers have not been able to do in previous versions of the Google Sheets API is to format cells... that's a big deal! Anyway, the past is the past, and I choose to look ahead. In my earlier post on the Google Sheets API, I introduced Sheets API v4 with a tutorial on how to transfer data from a SQL database to a Sheet. You'd do that primarily to make database data more presentable rather than deliberately switching to a storage mechanism with weaker querying capabilities. At the very end of that post, I challenged readers to try formatting. If you got stuck, confused, or haven't had a chance yet, today's your lucky day. One caveat is that there's more JavaScript in this post than Python... you've been warned!

Using the Google Sheets API

We need to write (formatting) into a Google Sheet, so we need the same scope as last time, read-write:
  • 'https://www.googleapis.com/auth/spreadsheets' — Read-write access to Sheets and Sheet properties
Since we've fully covered the authorization boilerplate fully in earlier posts and videos, including how to connect to the Sheets API, we're going to skip that here and jump right to the action.

Formatting cells in Google Sheets

The way the API works, in general, is to take one or more commands, and execute them on the Sheet. This comes in the form of individual requests, either to cells, a Sheet, or the entire spreadsheet. A group if requests is organized as a JavaScript array. Each request in the array is represented by a JSON object. Yes, this part of the post may seem like a long exercise in JavaScript, but stay with me here. Continuing... once your array is complete, you send all the requests to the Sheet using the SHEETS.spreadsheets().batchUpdate() command. Here's pseudocode sending 5 commands to the API:
SHEET_ID =  . . .

reqs = {'requests': [
    {'updateSheetProperties':
        . . .
    {'repeatCell':
        . . .
    {'setDataValidation':
        . . .
    {'sortRange':
        . . .
    {'addChart':
        . . .
]}
SHEETS.spreadsheets().batchUpdate(
        spreadsheetId=SHEET_ID, body=reqs).execute()
What we're executing will be similar. The target spreadsheet will be the one you get when you run the code from the previous post, only without the timestamp in its title as it's unnecessary:


Once you've run the earlier script and created a Sheet of your own, be sure to assign it to the SHEET_ID variable. The goal is to send enough formatting commands to arrive at the same spreadsheet but with improved visuals:


Four (4) requests are needed to bring the original Sheet to this state:
  1. Set the top row as "frozen", meaning it doesn't scroll even when the data does
  2. Also bold the first row, as these are the column headers
  3. Format column E as US currency with dollar sign & 2 decimal places
  4. Set data validation for column F, requiring values from a fixed set

Creating Sheets API requests

As mentioned before, each request is represented by a JSON object, cleverly disguised as Python dictionaries in this post, and the entire request array is implemented as a Python list. Let's take a look at what it takes to together the individual requests:

Frozen rows

Frozen rows is a Sheet property, so in order to change it, users must employ the updateSheetProperties command. Specifically, frozenRowCount is a grid property, meaning the field that must be updated is gridProperties.frozenRowCount, set to 1. Here's the Python dict (that gets converted to a JSON object) representing this request:
{'updateSheetProperties': {
    'properties': {'gridProperties': {'frozenRowCount': 1}},
    'fields': 'gridProperties.frozenRowCount',
}},
The properties attribute specifies what is changing and what the new value is. The fields property serves as an attribute "mask." It's how you specify what to alter and what to leave alone when applying a change. In this case, both the properties and fields attributes refer to the same thing: the frozen row count grid property. If you leave out the fields attribute here, sure the frozen row count would be set but all other grid properties would be undone, not such a good side effect. It's okay if it doesn't make a lot of sense yet... there are more examples coming.

Bold formatting

Text formatting, such as bold or italics, is a cell operation. Since we want to apply this formatting to multiple cells, the correct command is, repeatCell. Specifically, what needs to be changed about a cell? A cell's userEnteredFormat.textFormat.bold attribute. This is a simple Boolean value, so we set it to True. The fields masks are as described above... we need to tell the API to explicitly change the just userEnteredFormat.textFormat.bold attribute. Everything else should stay as-is.

The last thing we need is to tell the API which cells in the Sheet should be formatted. For this we have range. This attribute tells the API what Sheet (by ID) and which cells (column and row ranges) in that Sheet to format. Above, you see that we want to bold just one row, row #1. Similarly, there are currently six columns to bold, A-F.

However, like most modern computer systems, the API supports start and end index values beginning with zero... not alphabetic column names nor rows consisting of whole numbers, and the range is exclusive of the end index, meaning it goes up to but does not include the ending row or column. For row 1, this means a starting index of 0 and an ending index of 1. Similarly, columns A-F have start & end index value of 0 and 6, respectively. Visually, here's how you compare traditional row & column values to 0-based index counting:


Here's the dict representing this request:
{'repeatCell': {
    'range': {
        'sheetId': 0,
        'startColumnIndex': 0,
        'endColumnIndex': 6,
        'startRowIndex': 0,
        'endRowIndex': 1
    },
    'cell': {'userEnteredFormat': {'textFormat': {'bold': True}}},
    'fields': 'userEnteredFormat.textFormat.bold',
}},
Before we move on, let's talk about some shortcuts we can make. The ID of the first Sheet created for you is 0. If that's the Sheet you're using, then you can omit passing the Sheet ID. Similarly, the starting row and column indexes default to 0, so you can leave those out too if those are the values to be used. Finally, while an ending column index of 6 works, it won't if more columns are added later. It's best if you just omit the ending index altogether, meaning you want that entire row formatted. All this means that the only thing in the range you need is the ending row index. Instead of the above, your request can be shortened to:
{'repeatCell': {
    'range': {'endRowIndex': 1},
    'cell': {'userEnteredFormat': {'textFormat': {'bold': True}}},
    'fields': 'userEnteredFormat.textFormat.bold',
}},

Range quiz

Okay, now that you know about ranges, take this quiz: assumming the Sheet ID is 0, what are the starting and ending column and row indexes for the four cells highlighted in blue in this Sheet?


If you went with starting and ending column indexes of 3 and 5 and row indexes of 2 and 4, then you're right on the money and ready for more!

Currency formatting

Currency formatting is similar to text formatting, only with numbers, meaning that instead of userEnteredFormat.textFormat, you'd be setting a cell's userEnteredFormat.numberFormat attribute. The command is also repeatCell. Clearly the starting and ending column indexes should be 4 and 5 with a starting row index of 1. But just like the cell bolding we did above, there's no need to restrict ourselves to just the 5 rows of data as more may be coming. Yes, it's best to leave off the ending row index so that the rest of the column is formatted. The only thing you need to learn is how to format cells using US currency, but that's pretty easy to do after a quick look at the docs on formatting numbers:
{'repeatCell': {
    'range': {
        'startRowIndex': 1,
        'startColumnIndex': 4,
        'endColumnIndex': 5,
    },
    'cell': {
        'userEnteredFormat': {
            'numberFormat': {
                'type': 'CURRENCY',
                'pattern': '"$"#,##0.00',
            },
        },
    },
    'fields': 'userEnteredFormat.numberFormat',
}}

More on fields

One caveat to our sample app here is that all of the fields mask only have a single value, the one we want to change, but that's not always the case. There may be situations where you want to effect a variety of changes to cells. To see more examples of fields, check out this page in the docs featuring more formatting examples. To learn more about how masking works, check out this page and this one too.

Cell validation

The final formatting request implements cell validation on column F as well as restricting their possible values. The command used here is setDataValidation. The range is similar to that of currency formatting, only for column F, meaning a starting row index of 1, and starting and ending column indexes of 5 and 6, respectively. The rule implements the restriction. Similar to other spreadsheet software, you can restrict cell values in any number of ways, as outlined by the ConditionType documentation page. Ours is to allow for one of three possible values, so the ConditionType is ONE_OF_LIST.

When you restrict cell values, you can choose to allow but flag it (weak enforcement) or disallow any value outside of what you specify (strict enforcement). If you wish to employ strict enforcement, you need to pass in a strict attribute with a True value. The default is weak enforcement, or False. In either case, users entering invalid values will get a default warning message that the input is not allowed. If you prefer a custom message over the default option, you can pass that to the API as the inputMessage attribute. I prefer the system default and elect not to use it here. Here are the 4 combinations of what shows up when you use or don't use inputMessage with strict and weak enforcement:


No inputMessage (default) + weak enforcement


With inputMessage (my custom msg) + weak enforcement


No inputMessage (default) + strict enforcement


With inputMessage (my custom msg) + weak enforcement

The last attribute you can send is showCustomUi. If the showCustomUi flag is set to True, the Sheets user interface will display a small pulldown menu listing the values accepted by the cell. It's a pretty poor user experience without it (because users won’t know what the available choices are), so I recommend you always use it too. With that, this request looks like this:
{'setDataValidation': {
    'range': {
        'startRowIndex': 1,
        'startColumnIndex': 5,
        'endColumnIndex': 6,
    },
    'rule': {
        'condition': {
            'type': 'ONE_OF_LIST',
            'values': [
                {'userEnteredValue': 'PENDING'},
                {'userEnteredValue': 'SHIPPED'},
                {'userEnteredValue': 'DELIVERED'},
            ]
        },
        #'inputMessage': 'Select PENDING, SHIPPED, or DELIVERED',
        #'strict': True,
        'showCustomUi': True,
    },
}}
Since we're not modifying cell attributes, but instead focusing on validation, you'll notice there's no fields mask in these types of requests.

Running our script

Believe it or not, that's the bulk of this application. With the reqs list of these four requests, the last line of code calls the Sheets API exactly like the pseudocode above. Now you can simply run it:
$ python sheets_cell_format.py # or python3
$
There's no output from this script, so you should only expect that your Sheet will be formatted once it has completed. If you bring up the Sheet in the user interface, you should see the changes happening in near real-time:


Conclusion

Below is the entire script for your convenience which runs on both Python 2 and Python 3 (unmodified!):
from apiclient import discovery
from httplib2 import Http
from oauth2client import file, client, tools

SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
store = file.Storage('storage.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('client_id.json', SCOPES)
    creds = tools.run_flow(flow, store)
SHEETS = discovery.build('sheets', 'v4', http=creds.authorize(Http()))

SHEET_ID = ... # add your Sheet ID here
reqs = {'requests': [
    # frozen row 1
    {'updateSheetProperties': {
        'properties': {'gridProperties': {'frozenRowCount': 1}},
        'fields': 'gridProperties.frozenRowCount',
    }},
    # embolden row 1
    {'repeatCell': {
        'range': {'endRowIndex': 1},
        'cell': {'userEnteredFormat': {'textFormat': {'bold': True}}},
        'fields': 'userEnteredFormat.textFormat.bold',
    }},
    # currency format for column E (E2:E7)
    {'repeatCell': {
        'range': {
            'startRowIndex': 1,
            'endRowIndex': 6,
            'startColumnIndex': 4,
            'endColumnIndex': 5,
        },
        'cell': {
            'userEnteredFormat': {
                'numberFormat': {
                    'type': 'CURRENCY',
                    'pattern': '"$"#,##0.00',
                },
            },
        },
        'fields': 'userEnteredFormat.numberFormat',
    }},
    # validation for column F (F2:F7)
    {'setDataValidation': {
        'range': {
            'startRowIndex': 1,
            'endRowIndex': 6,
            'startColumnIndex': 5,
            'endColumnIndex': 6,
        },
        'rule': {
            'condition': {
                'type': 'ONE_OF_LIST',
                'values': [
                    {'userEnteredValue': 'PENDING'},
                    {'userEnteredValue': 'SHIPPED'},
                    {'userEnteredValue': 'DELIVERED'},
                ]
            },
            #'inputMessage': 'Select PENDING, SHIPPED, or DELIVERED',
            #'strict': True,
            'showCustomUi': True,
        },
    }},
]}

res = SHEETS.spreadsheets().batchUpdate(
        spreadsheetId=SHEET_ID, body=reqs).execute()
As with our other code samples, you can now customize for your own needs, for a mobile frontend, sysadmin script, or a server-side backend, perhaps accessing other Google APIs.

Code challenge

Once you fully grasp this sample and are ready for a challenge: Use the API to create a column "G" with a "Total Cost" header in cell G1, set cell G2 with the formula to calculate the cost based on toys ordered & cost in columns D & E then and create an autoFill request to replicate that formula down column G. When you're done, the right-hand side of your Sheet now looks like this:

Here are some steps you can take to achieve this improvement:
  1. Create column G with a "Total Cost" header in cell G1; make sure it's bold too (or do you have to?)
  2. Set cell G2 with formula =MULTIPLY(D2,E2)
  3. Use autoFill command to copy formula from G2 down the entire column (HINT: you only need the range attribute)
You're now well under way to being able to writing useful applications with the Sheets API!

Thursday, June 9, 2016

Migrating SQL data to Google Sheets using the new Google Sheets API

NOTE: The code covered in this post are also available in a video walkthrough.
UPDATE (Sep 2016): Removed use of argparse module & flags (effective as of Feb 2016).

Introduction

In this post, we're going to demonstrate how to use the latest generation Google Sheets API. Launched at Google I/O 2016 (full talk here), the Sheets API v4 can do much more than previous versions, bringing it to near-parity with what you can do with the Google Sheets UI (user interface) on desktop and mobile. Below, I'll walk you through a Python script that reads the rows of a relational database representing customer orders for a toy company and pushes them into a Google Sheet. Other API calls we'll make: one to create new Google Sheets with and another that reads the rows from a Sheet.

Earlier posts demonstrated the structure and "how-to" use Google APIs in general, so more recent posts, including this one, focus on solutions and use of specific APIs. Once you review the earlier material, you're ready to start with authorization scopes then see how to use the API itself.

    Google Sheets API authorization & scopes

    Previous versions of the Google Sheets API (formerly called the Google Spreadsheets API), were part of a group of "GData APIs" that implemented the Google Data (GData) protocol, an older, less-secure, REST-inspired technology for reading, writing, and modifying information on the web. The new API version falls under the more modern set of Google APIs requiring OAuth2 authorization and whose use is made easier with the Google APIs Client Libraries.

    The current API version features a pair of authorization scopes: read-only and read-write. As usual, we always recommend you use the most restrictive scope possible that allows your app to do its work. You'll request fewer permissions from your users (which makes them happier), and it also makes your app more secure, possibly preventing modifying, destroying, or corrupting data, or perhaps inadvertently going over quotas. Since we're creating a Google Sheet and writing data into it, we must use the read-write scope:
    • 'https://www.googleapis.com/auth/spreadsheets' — Read/write access to Sheets and Sheet properties

    Using the Google Sheets API

    Let's look at some code that reads rows from a SQLite database and creates a Google Sheet with that data. Since we covered the authorization boilerplate fully in earlier posts and videos, we're going straight to creating a Sheets service endpoint. The API string to use is 'sheets' and the version string to use is 'v4' as we call the apiclient.discovey.build() function:

    SHEETS = discovery.build('sheets', 'v4', http=creds.authorize(Http()))

    With the SHEETS service endpoint in hand, the first thing to do is to create a brand new Google Sheet. Before we use it, one thing to know about the Sheets API is that most calls require a JSON payload representing the data & operations you wish to perform, and you'll see this as you become more familiar with it. For creating new Sheets, it's pretty simple, you don't have to provide anything, in which case you'd pass in an empty (dict as the) body, but a better bare minimum would be a name for the Sheet, so that's what data is for:

    data = {'properties': {'title': 'Toy orders [%s]' % time.ctime()}}

    Notice that a Sheet's "title" is part of its "properties," and we also happen to add the timestamp as part of its name. With the payload complete, we call the API with the command to create a new Sheet [spreadsheets().create()], passing in data in the (eventual) request body:

    res = SHEETS.spreadsheets().create(body=data).execute()

    Alternatively, you can use the Google Drive API (v2 or v3) to create a Sheet but would also need to pass in the Google Sheets (file) MIME type:
    data = {
        'name': 'Toy orders [%s]' % time.ctime(),
        'mimeType': 'application/vnd.google-apps.spreadsheet',
    }
    res = DRIVE.files().create(body=data).execute() # insert() for v2
    
    The general rule-of-thumb is that if you're only working with Sheets, you can do all the operations with its API, but if creating files other than Sheets or performing other Drive file or folder operations, you may want to stick with the Drive API. You can also use both or any other Google APIs for more complex applications. We'll stick with just the Sheets API for now. After creating the Sheet, grab and display some useful information to the user:
    SHEET_ID = res['spreadsheetId']
    print('Created "%s"' % res['properties']['title'])
    
    You may be wondering: Why do I need to create a Sheet and then make a separate API call to add data to it? Why can't I do this all when creating the Sheet? The answer (to this likely FAQ) is you can, but you would need to construct and pass in a JSON payload representing the entire Sheet—meaning all cells and their formatting—a much larger and more complex data structure than just an array of rows. (Don't believe me? Try it yourself!) This is why we have all of the spreadsheets().values() methods... to simplify uploading or downloading of only values to or from a Sheet.

    Now let's turn our attention to the simple SQLite database file (db.sqlite) available from the Google Sheets Node.js codelab. The next block of code just connects to the database with the standard library sqlite3 package, grabs all the rows, adds a header row, and filters the last two (timestamp) columns:
    FIELDS = ('ID', 'Customer Name', 'Product Code', 'Units Ordered',
            'Unit Price', 'Status', 'Created at', 'Updated at')
    cxn = sqlite3.connect('db.sqlite')
    cur = cxn.cursor()
    rows = cur.execute('SELECT * FROM orders').fetchall()
    cxn.close()
    rows.insert(0, FIELDS)
    data = {'values': [row[:6] for row in rows]}
    
    When you have a payload (array of row data) you want to stick into a Sheet, you simply pass in those values to spreadsheets().values().update() like we do here:
    SHEETS.spreadsheets().values().update(spreadsheetId=SHEET_ID,
        range='A1', body=data, valueInputOption='RAW').execute()
    
    The call requires a Sheet's ID and command body as expected, but there are two other fields: the full (or, as in our case, the "upper left" corner of the) range of cells to write to (in A1 notation), and valueInputOption indicates how the data should be interpreted, writing the raw values ("RAW") or interpreting them as if a user were entering them into the UI ("USER_ENTERED"), possibly converting strings & numbers based on the cell formatting.

    Reading rows out of a Sheet is even easier, the spreadsheets().values().get() call needing only an ID and a range of cells to read:
    print('Wrote data to Sheet:')
    rows = SHEETS.spreadsheets().values().get(spreadsheetId=SHEET_ID,
        range='Sheet1').execute().get('values', [])
    for row in rows:
        print(row)
    
    The API call returns a dict which has a 'values' key if data is available, otherwise we default to an empty list so the for loop doesn't fail.

    If you run the code (entire script below) and grant it permission to manage your Google Sheets (via the OAuth2 prompt that pops up in the browser), the output you get should look like this:
    $ python3 sheets-toys.py # or python (2.x)
    Created "Toy orders [Thu May 26 18:58:17 2016]" with this data:
    ['ID', 'Customer Name', 'Product Code', 'Units Ordered', 'Unit Price', 'Status']
    ['1', "Alice's Antiques", 'FOO-100', '25', '12.5', 'DELIVERED']
    ['2', "Bob's Brewery", 'FOO-200', '60', '18.75', 'SHIPPED']
    ['3', "Carol's Car Wash", 'FOO-100', '100', '9.25', 'SHIPPED']
    ['4', "David's Dog Grooming", 'FOO-250', '15', '29.95', 'PENDING']
    ['5', "Elizabeth's Eatery", 'FOO-100', '35', '10.95', 'PENDING']
    

    Conclusion

    Below is the entire script for your convenience which runs on both Python 2 and Python 3 (unmodified!):

    '''sheets-toys.py -- Google Sheets API demo
        created Jun 2016 by +Wesley Chun/@wescpy
    '''
    from __future__ import print_function
    import sqlite3
    import time
    
    from apiclient import discovery
    from httplib2 import Http
    from oauth2client import file, client, tools
    
    SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
    store = file.Storage('storage.json')
    creds = store.get()
    if not creds or creds.invalid:
        flow = client.flow_from_clientsecrets('client_id.json', SCOPES)
        creds = tools.run_flow(flow, store)
    SHEETS = discovery.build('sheets', 'v4', http=creds.authorize(Http()))
    
    data = {'properties': {'title': 'Toy orders [%s]' % time.ctime()}}
    res = SHEETS.spreadsheets().create(body=data).execute()
    SHEET_ID = res['spreadsheetId']
    print('Created "%s"' % res['properties']['title'])
    
    FIELDS = ('ID', 'Customer Name', 'Product Code', 'Units Ordered',
            'Unit Price', 'Status', 'Created at', 'Updated at')
    cxn = sqlite3.connect('db.sqlite')
    cur = cxn.cursor()
    rows = cur.execute('SELECT * FROM orders').fetchall()
    cxn.close()
    rows.insert(0, FIELDS)
    data = {'values': [row[:6] for row in rows]}
    
    SHEETS.spreadsheets().values().update(spreadsheetId=SHEET_ID,
        range='A1', body=data, valueInputOption='RAW').execute()
    print('Wrote data to Sheet:')
    rows = SHEETS.spreadsheets().values().get(spreadsheetId=SHEET_ID,
        range='Sheet1').execute().get('values', [])
    for row in rows:
        print(row)
    
    You can now customize this code for your own needs, for a mobile frontend, devops script, or a server-side backend, perhaps accessing other Google APIs. If this example is too complex, check the Python quickstart in the docs that way simpler, only reading data out of an existing Sheet. If you know JavaScript and are ready for something more serious, try the Node.js codelab where we got the SQLite database from. That's it... hope you find these code samples useful in helping you get started with the latest Sheets API!

    EXTRA CREDIT: Feel free to experiment and try cell formatting or other API features. Challenge yourself as there's a lot more to Sheets than just reading and writing values! 

    Wednesday, September 9, 2015

    Creating events in Google Calendar from Python

    NOTE: The code covered in this blogpost is also available in a video walkthrough here.

    UPDATE (Jan 2016): Tweaked the code to support oauth2client.tools.run_flow() which deprecates oauth2client.tools.run(). You can read more about that change and migration steps here.

    Introduction

    So far in this series of blogposts covering authorized Google APIs, we've used Python code to access Google Drive and Gmail. Today, we're going to demonstrate the Google Calendar API. While Google Calendar, and calendaring in general, have been around for a long time and are fairly stable, it's somewhat of a mystery as to why so few developers create good calendar integrations, whether it be with Google Calendar, or other systems. We'll try to show it isn't necessarily difficult and hopefully motivate some of you out there to add a calendaring feature in your next mobile or web app.

    Earlier posts (link 1, link 2) demonstrated the structure and "how-to" use Google APIs in general, so more recent posts, including this one, focus on solutions and apps, and use of specific APIs. Once you review the earlier material, you're ready to start with authorization scopes then see how to use the API itself.

      Google Calendar API Scopes

      Below are the Google Calendar API scopes of authorization. There are only a pair (at the time of this writing): read-only and read/write. As usual, use the most restrictive scope you possibly can yet still allowing your app to do its work. This makes your app more secure and may prevent inadvertently going over any quotas, or accessing, destroying, or corrupting data. Also, users are less hesitant to install your app if it asks only for more restricted access to their calendars. However, it's likely that in order to really use the API to its fullest, you will probably have to ask for read-write so that you can add, update, or delete events in their calendars.
      • 'https://www.googleapis.com/auth/calendar.readonly' — Read-only access to calendar
      • 'https://www.googleapis.com/auth/calendar' — Read/write access to calendar

      Using the Google Calendar API

      We're going to create a sample Python script that inserts a new event into your Google Calendar. Since this requires modifying your calendar, you need the read/write scope above. The API name is 'calendar' which is currently on version 3, so here's the call to apiclient.discovery.build() you'll use:
      GCAL = discovery.build('calendar', 'v3',
          http=creds.authorize(Http()))
      Note that all lines of code above that is predominantly boilerplate (that was explained in earlier posts and videos). Anyway, we've got an established service endpoint with build(), we need to come up with the data to create a calendar event with, at the very least, an event name plus start and end times.

      Timezone or offset required

      The API requires either a timezone or a GMT offset, the number of hours your timezone is away from Coordinated Universal Time (UTC, more commonly known as GMT). The format is +/-HH:MM away from UTC. For example, Pacific Daylight Time (PDT, also known as Mountain Standard Time, or MST), is "-07:00," or seven hours behind UTC while Nepal Standard Time (NST [or NPT to avoid confusion with Newfoundland Standard Time]), is "+05:45," or five hours and forty-five minutes ahead of UTC. Also, the offset must be in RFC 3339 format, which implements the specifications of ISO 8601 for the Internet. Timestamps look like the following in the required format: "YYYY-MM-DDTHH:MM:SS±HH:MM". For example, September 15, 2015 at 7 PM PDT is represented by this string: "2015-09-15T19:00:00-07:00".

      If you wish to avoid offsets and would rather use timezone names instead, see the next post in this series (link at bottom).

      The script in this post uses the PDT timezone, so we set the GMT_OFF variable to "-07:00". The EVENT body will hold the event name, and start and end times suffixed with the GMT offset:
      GMT_OFF = '-07:00'    # PDT/MST/GMT-7
      EVENT = {
          'summary': 'Dinner with friends',
          'start':   {'dateTime': '2015-09-15T19:00:00%s' % GMT_OFF},
          'end':     {'dateTime': '2015-09-15T22:00:00%s' % GMT_OFF},
      }
      Use the insert() method of the events() service to add the event. As expected, one required parameter is the ID of the calendar to insert the event into. A special value of 'primary' has been set aside for the currently authenticated user. The other required parameter is the event body. In our request, we also ask the Calendar API to send email notifications to the guests, and that's done by passing in the sendNotifications flag with a True value. Our call to the API looks like this:
      e = GCAL.events().insert(calendarId='primary',
          sendNotifications=True, body=EVENT).execute()
      The one remaining thing is to confirm that the calendar event was created successfully. We do that by checking the return value — it should be an Event object with all the details we passed in a moment ago:
      print('''*** %r event added:
          Start: %s
          End:   %s''' % (e['summary'].encode('utf-8'),
              e['start']['dateTime'], e['end']['dateTime']))
      
      Now, if you really want some proof the event was created, one of the fields that's created is a link to the calendar event. We don't use it in the code, but you can... just use e['htmlLink'].

      Regardless, that's pretty much the entire script save for the OAuth2 code that we're so familiar with from previous posts. The script is posted below in its entirety, and if you run it, depending on the date/times you use, you'll see something like this:
      $ python gcal_insert.py
      *** 'Dinner with friends' event added:
          Start: 2015-09-15T19:00:00-07:00
          End:   2015-09-15T22:00:00-07:00
      It also works with Python 3 with one slight nit/difference being the "b" prefix on from the event name due to converting from Unicode to bytes:
      *** b'Dinner with friends' event added:

      Conclusion

      There can be much more to adding a calendar event, such as events that repeat with a recurrence rule, the ability to add attachments for an event, such as a party invitation or a PDF of the show tickets. For more on what you can do when creating events, take a look at the docs for events().insert() as well as the corresponding developer guide. All of the docs for the Google Calendar API can be found here. Also be sure to check out the companion video for this code sample. That's it!

      Below is the entire script for your convenience which runs on both Python 2 and Python 3 (unmodified!):
      from __future__ import print_function
      from apiclient import discovery
      from httplib2 import Http
      from oauth2client import file, client, tools
      
      SCOPES = 'https://www.googleapis.com/auth/calendar'
      store = file.Storage('storage.json')
      creds = store.get()
      if not creds or creds.invalid:
          flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
          creds = tools.run_flow(flow, store)
      GCAL = discovery.build('calendar', 'v3', http=creds.authorize(Http()))
      
      GMT_OFF = '-07:00'      # PDT/MST/GMT-7
      EVENT = {
          'summary': 'Dinner with friends',
          'start':  {'dateTime': '2015-09-15T19:00:00%s' % GMT_OFF},
          'end':    {'dateTime': '2015-09-15T22:00:00%s' % GMT_OFF},
          'attendees': [
              {'email': '[email protected]'},
              {'email': '[email protected]'},
          ],
      }
      
      e = GCAL.events().insert(calendarId='primary',
              sendNotifications=True, body=EVENT).execute()
      
      print('''*** %r event added:
          Start: %s
          End:   %s''' % (e['summary'].encode('utf-8'),
              e['start']['dateTime'], e['end']['dateTime']))
      
      You can now customize this code for your own needs, for a mobile frontend, a server-side backend, or to access other Google APIs. If you want to see another example of using the Calendar API (listing the next 10 events in your calendar), check out the Python Quickstart example or its equivalent in Java (server-side, Android), iOS (Objective-C, Swift), C#/.NET, PHP, Ruby, JavaScript (client-side, Node.js), or Go. That's it... hope you find these code samples useful in helping you get started with the Calendar API!

      Code challenge

      To test your skills and challenge yourself, try creating recurring events (such as when you expect to receive your paycheck), events with attachments, or perhaps editing existing events. UPDATE (Jul 2017): If you're ready for the next step, we cover the first and last of those choices in our follow-up post.

      Thursday, August 6, 2015

      Accessing Gmail from Python (plus BONUS)

      NOTE: The code covered in this blogpost is also available in a video walkthrough here.

      UPDATE (Aug 2016): The code has been modernized to use oauth2client.tools.run_flow() instead of the deprecated oauth2client.tools.run(). You can read more about that change here.

      Introduction

      The last several posts have illustrated how to connect to public/simple and authorized Google APIs. Today, we're going to demonstrate accessing the Gmail (another authorized) API. Yes, you read that correctly... "API." In the old days, you access mail services with standard Internet protocols such as IMAP/POP and SMTP. However, while they are standards, they haven't kept up with modern day email usage and developers' needs that go along with it. In comes the Gmail API which provides CRUD access to email threads and drafts along with messages, search queries, management of labels (like folders), and domain administration features that are an extra concern for enterprise developers.

      Earlier posts demonstrate the structure and "how-to" use Google APIs in general, so the most recent posts, including this one, focus on solutions and apps, and use of specific APIs. Once you review the earlier material, you're ready to start with Gmail scopes then see how to use the API itself.

        Gmail API Scopes

        Below are the Gmail API scopes of authorization. We're listing them in most-to-least restrictive order because that's the order you should consider using them in  use the most restrictive scope you possibly can yet still allowing your app to do its work. This makes your app more secure and may prevent inadvertently going over any quotas, or accessing, destroying, or corrupting data. Also, users are less hesitant to install your app if it asks only for more restricted access to their inboxes.
        • 'https://www.googleapis.com/auth/gmail.readonly' — Read-only access to all resources + metadata
        • 'https://www.googleapis.com/auth/gmail.send' — Send messages only (no inbox read nor modify)
        • 'https://www.googleapis.com/auth/gmail.labels' — Create, read, update, and delete labels only
        • 'https://www.googleapis.com/auth/gmail.insert' — Insert and import messages only
        • 'https://www.googleapis.com/auth/gmail.compose' — Create, read, update, delete, and send email drafts and messages
        • 'https://www.googleapis.com/auth/gmail.modify' — All read/write operations except for immediate & permanent deletion of threads & messages
        • 'https://mail.google.com/' — All read/write operations (use with caution)

        Using the Gmail API

        We're going to create a sample Python script that goes through your Gmail threads and looks for those which have more than 2 messages, for example, if you're seeking particularly chatty threads on mailing lists you're subscribed to. Since we're only peeking at inbox content, the only scope we'll request is 'gmail.readonly', the most restrictive scope. The API string is 'gmail' which is currently on version 1, so here's the call to apiclient.discovery.build() you'll use:

        GMAIL = discovery.build('gmail', 'v1', http=creds.authorize(Http()))

        Note that all lines of code above that is predominantly boilerplate (that was explained in earlier posts). Anyway, once you have an established service endpoint with build(), you can use the list() method of the threads service to request the file data. The one required parameter is the user's Gmail address. A special value of 'me' has been set aside for the currently authenticated user.
        threads = GMAIL.users().threads().list(userId='me').execute().get('threads', [])
        If all goes well, the (JSON) response payload will (not be empty or missing and) contain a sequence of threads that we can loop over. For each thread, we need to fetch more info, so we issue a second API call for that. Specifically, we care about the number of messages in a thread:
        for thread in threads:
            tdata = GMAIL.users().threads().get(userId='me', id=thread['id']).execute()
            nmsgs = len(tdata['messages'])
        
        We're seeking only all threads more than 2 (that means at least 3) messages, discarding the rest. If a thread meets that criteria, scan the first message and cycle through the email headers looking for the "Subject" line to display to users, skipping the remaining headers as soon as we find one:
            if nmsgs > 2:
                msg = tdata['messages'][0]['payload']
                subject = ''
                for header in msg['headers']:
                    if header['name'] == 'Subject':
                        subject = header['value']
                        break
                if subject:
                    print('%s (%d msgs)' % (subject, nmsgs))
        
        If you're on many mailing lists, this may give you more messages than desired, so feel free to up the threshold from 2 to 50, 100, or whatever makes sense for you. (In that case, you should use a variable.) Regardless, that's pretty much the entire script save for the OAuth2 code that we're so familiar with from previous posts. The script is posted below in its entirety, and if you run it, you'll see an interesting collection of threads... YMMV depending on what messages are in your inbox:
        $ python3 gmail_threads.py
        [Tutor] About Python Module to Process Bytes (3 msgs)
        Core Python book review update (30 msgs)
        [Tutor] scratching my head (16 msgs)
        [Tutor] for loop for long numbers (10 msgs)
        [Tutor] How to show the listbox from sqlite and make it searchable? (4 msgs)
        [Tutor] find pickle and retrieve saved data (3 msgs)
        

        BONUS: Python 3!

        As of Mar 2015 (formally in Apr 2015 when the docs were updated), support for Python 3 was added to Google APIs Client Library (3.3+)! This update was a long time coming (relevant GitHub thread), and allows Python 3 developers to write code that accesses Google APIs. If you're already running 3.x, you can use its pip command (pip3) to install the Client Library:

        $ pip3 install -U google-api-python-client

        Because of this, unlike previous blogposts, we're deliberately going to avoid use of the print statement and switch to the print() function instead. If you're still running Python 2, be sure to add the following import so that the code will also run in your 2.x interpreter:

        from __future__ import print_function

        Conclusion

        To find out more about the input parameters as well as all the fields that are in the response, take a look at the docs for threads().list(). For more information on what other operations you can execute with the Gmail API, take a look at the reference docs and check out the companion video for this code sample. That's it!

        Below is the entire script for your convenience which runs on both Python 2 and Python 3 (unmodified!):
        from __future__ import print_function
        
        from apiclient import discovery
        from httplib2 import Http
        from oauth2client import file, client, tools
        
        SCOPES = 'https://www.googleapis.com/auth/gmail.readonly'
        store = file.Storage('storage.json')
        creds = store.get()
        if not creds or creds.invalid:
            flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
            creds = tools.run_flow(flow, store)
        GMAIL = discovery.build('gmail', 'v1', http=creds.authorize(Http()))
        
        threads = GMAIL.users().threads().list(userId='me').execute().get('threads', [])
        for thread in threads:
            tdata = GMAIL.users().threads().get(userId='me', id=thread['id']).execute()
            nmsgs = len(tdata['messages'])
        
            if nmsgs > 2:
                msg = tdata['messages'][0]['payload']
                subject = ''
                for header in msg['headers']:
                    if header['name'] == 'Subject':
                        subject = header['value']
                        break
                if subject:
                    print('%s (%d msgs)' % (subject, nmsgs))
        
        You can now customize this code for your own needs, for a mobile frontend, a server-side backend, or to access other Google APIs. If you want to see another example of using the Gmail API (displaying all your inbox labels), check out the Python Quickstart example in the official docs or its equivalent in Java (server-side, Android), iOS (Objective-C, Swift), C#/.NET, PHP, Ruby, JavaScript (client-side, Node.js), or Go. That's it... hope you find these code samples useful in helping you get started with the Gmail API!

        EXTRA CREDIT: To test your skills and challenge yourself, try writing code that allows users to perform a search across their email, or perhaps creating an email draft, adding attachments, then sending them! Note that to prevent spam, there are strict Program Policies that you must abide with... any abuse could rate limit your account or get it shut down. Check out those rules plus other Gmail terms of use here.

        Tuesday, April 7, 2015

        Google APIs: migrating from tools.run() to tools.run_flow()

        Got AttributeError? As in: AttributeError: 'module' object has no attribute 'run'? Rename run() to run_flow(), and you'll be good-to-go. TL;DR: This mini-tutorial slash migration guide slash PSA (public service announcement) is aimed at Python developers using the Google APIs Client Library (to access Google APIs from their applications) currently calling oauth2client.tools.run() and likely getting an exception (see Jan 2016 update below), and need to oauth2client.tools.run_flow(), its replacement. 

        UPDATE (Aug 2016): The flags parameter in run_flow() function became optional in Feb 2016, so tweaked the blogpost to reflect that.

        UPDATE (Jun 2016): Revised the code and cleaned up the dialog so there are no longer any instances of using run() function, significantly shortening this post.

        UPDATE (Jan 2016): The tools.run() function itself was forcibly removed (without a fallback) in Aug 2015, so if you're using any release on or after that, any such calls from your code will throw an exception (AttributeError: 'module' object has no attribute 'run'). To fix this problem, continue reading.

        Prelude

        We're going to continue our look at accessing Google APIs from Python. In addition to the previous pair of posts (http://goo.gl/57Gufk and http://goo.gl/cdm3kZ), as part of my day job, I've been working on corresponding video content, some of which are tied specifically to posts on this blog.

        In this follow-up, we're going to specifically address the sidebar in the previous post, where we bookmarked an item for future discussion where the future is now: in the oauth2client package, tools.run() has been deprecated by tools.run_flow(). Note you need at least Python 2.7 or 3.3 to use the Google APIs Client Library. (If you didn't even know Python 3 was supported at all, then you need to see this post and this Quora Q&A.)

        Replacing tools.run() with tools.run_flow()

        Now let's convert the authorized access to Google APIs code from using tools.run() to tools.run_flow(). Here is the old snippet I'm talking about that needs upgrading:
        from apiclient import discovery
        from httplib2 import Http
        from oauth2client import file, client, tools
        
        SCOPES = # one or more scopes (str or iterable)
        store = file.Storage('storage.json')
        creds = store.get()
        if not creds or creds.invalid:
            flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
            creds = tools.run(flow, store)
        
        SERVICE = discovery.build(API, VERSION, http=creds.authorize(Http()))
        
        If you're using the latest Client Library (as of Feb 2016), all you need to do is change the tools.run() call to tools.run_flow(), as italicized below. Everything else stays exactly the same:
        from apiclient import discovery
        from httplib2 import Http
        from oauth2client import file, client, tools
        
        SCOPES = # one or more scopes (str or iterable)
        store = file.Storage('storage.json')
        creds = store.get()
        if not creds or creds.invalid:
            flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
            creds = tools.run_flow(flow, store)
        If you don't have the latest Client Library, then your update involves the extra steps of adding lines that import argparse and using it to get the flags argument needed by tools.run_flow() plus the actual change from tools.run(); all updates italicized below:
        import argparse
        
        from apiclient import discovery
        from httplib2 import Http
        from oauth2client import file, client, tools
        
        SCOPES = # one or more scopes (str or iterable)
        store = file.Storage('storage.json')
        creds = store.get()
        if not creds or creds.invalid:
            flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
            flow = client.flow_from_clientsecrets('client_id.json', SCOPES)
            creds = tools.run_flow(flow, store, flags)
        
        SERVICE = discovery.build(API, VERSION, http=creds.authorize(Http()))
        

        Command-line argument processing, or "Why argparse?"

        Python has had several modules in the Standard Library that allow developers to process command-line arguments. The original one was getopt which mirrored the getopt() function from C. In Python 2.3, optparse was introduced, featuring more powerful processing capabilities. However, it was deprecated in 2.7 in favor of a similar module, argparse. (To find out more about their similarities, differences and rationale behind developing argparse , see PEP 389 and this argparse docs page.) For the purposes of using Google APIs, you're all set if using Python 2.7 as it's included in the Standard Library. Otherwise Python 2.3-2.6 users can install it with: "pip install -U argparse". 

        Irregardless of whether you need argparse, once you migrate to either snippet with tools.run_flow(), your application should go back to working the way it had before.