Showing posts with label Google Apps. Show all posts
Showing posts with label Google Apps. Show all posts

Wednesday, May 9, 2018

Creating Hangouts Chat bots with Python

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

Introduction

Earlier today at Google I/O, the Hangouts Chat team (including yours truly) delivered a featured talk on the bot framework for Hangouts Chat (talk video here [~40 mins]). If you missed it several months ago, Google launched the new Hangouts Chat application for G Suite customers (but not consumer Gmail users at this time). This next-generation collaboration platform has several key features not available in "classic Hangouts," including the ability to create chat rooms, search, and the ability to attach files from Google Drive. However for developers, the biggest new feature is a bot framework and API allowing developers to create bots that interact with users inside "spaces," i.e., chat rooms or direct messages (DMs).

Before getting started with bots, let's review typical API usage where your app must be authorized for data access, i.e., OAuth2. Once permission is granted, your app calls the API, the API services the request and responds back with the desired results, as illustrated here:

Bot architecture is quite different. With bots, recognize that requests come from users in chat rooms or DMs. Users direct messages towards a bot, then Hangouts Chat relays those requests to the bot. The bot performs all the necessary processing (possibly calling other APIs and services), collates the response, and returns it to Chat, which in turn, displays the results to users. Here's the summarized bot workflow:

A key takeaway from this discussion is that normally in your apps, you call APIs. For bots, Hangouts Chat calls you. For this reason, the service is extremely flexible for developers: you can create bots using pretty much any language (not just Python), using your stack of choice, and hosted on any public or private cloud. The only requirement is that as long as Hangouts Chat can HTTP POST to your bot, you're good to go.

Furthermore, if you think there's something missing in the diagram because you don't see OAuth2 nor API calls, you'd also be correct. Look for both in a future post where I focus on asynchronous responses from Hangouts Chat bots.

Event types & bot processing

So what does the payload look like when your bot receives a call from Hangouts Chat? The first thing your bot needs to do is to determine the type of event received. It can then process the request based on that event type, and finally, return an appropriate JSON payload back to Hangouts Chat to render within the space. There are currently four event types in Hangouts Chat:
  1. ADDED_TO_SPACE
  2. REMOVED_FROM_SPACE
  3. MESSAGE
  4. CARD_CLICKED
The first pair are for when a bot is added to or removed from a space. In the first case, the bot would likely send a welcome message like, "Thanks for adding me to this room," and perhaps give some instructions on how to communicate with the bot. When a bot is removed from a space, it can no longer communicate with chat room participants, so there's no response message sent in this case; the most likely action here would be to log that the bot was removed from the space.

The 3rd message type is likely the most common scenario, where a bot has been added to a room, and now a human user is sending it a request. The other common type would be the last one. Rather than a message, this event occurs when users click on a UI card element. The bot's job here is to invoke the "callback" associated with the card element clicked. A number of things can happen here: the bot can return a text message (or nothing at all), a UI card can be updated, or a new card can be returned.

All of what we described above is realized in the pseudocode below (whether you choose to implement in Python or any other supported language) and helpful in preparing you to review the official documentation:
def process_event(req, rsp):
    event = json.loads(req['body']) # event received
    if event['type'] == 'REMOVED_FROM_SPACE':
        # no response as bot removed from room
        return
    elif event['type'] == 'ADDED_TO_SPACE':
        # bot added to room; send welcome message
        msg = {'text': 'Thanks for adding me to this room!'}
    elif event['type'] == 'MESSAGE':
        # message received during normal operations
        msg = respond_to_message(event['message']['text'])
    elif event['type'] == 'CARD_CLICKED':
        # result from user-click on card UI
        action = event['action']
        msg = respond_to_click(
          action['actionMethodName'], action['parameters'])
    else:
        return
    rsp.send(json.dumps(msg))
Regardless of implementation language, you still need to make the necessary tweaks to run on the hosting platform you choose. Our example uses Google App Engine (GAE) for hosting; below you'll see the obvious similarities between our actual bot app and this pseudocode.

Polling chat room users for votes

For a basic voting bot, we need a vote counter, buttons for users to upvote or downvote, and perhaps some reset or "new vote" button. To implement it, we need to unpack the inbound JSON object, determine the event type, then process each event type with actions like this:
  1. ADDED_TO_SPACE — ignore.. we don't do anything unless asked by users
  2. REMOVED_FROM_SPACE — no action (bot removed)
  3. MESSAGE — start new vote
  4. CARD_CLICKED — process "upvote", "downvote", or "newvote" request
Here's a handler implementation for Python App Engine using its webapp2 micro framework:
class VoteBot(webapp2.RequestHandler):
    def post(self):
        event = json.loads(self.request.body)
        user = event['user']['displayName']
        if event['type'] == 'CARD_CLICKED':
            method = event['action']['actionMethodName']
            if method == 'newvote':
                body = create_message(user)
            else:
                delta = 1 if method == 'upvote' else -1
                vote_count = int(event['action']['parameters'][0]['value']) + delta
                body = create_message(user, vote_count, True)
        elif event['type'] == 'MESSAGE':
            body = create_message(user)
        else: # no response for ADDED_TO_SPACE or REMOVED_FROM_SPACE
            return
        self.response.headers['Content-Type'] = 'application/json'
        self.response.out.write(json.dumps(body)) 
If you've never written an app using webapp2, don't fret, as this example is easily portable to Flask or other web framework. Why didn't I write it with Flask to begin with? Couple of reasons: 1) the webapp2 framework comes native with App Engine... no need to go download/install it, and 2) because it's native, I don't need to deploy any other files other than bot.py and its app.yaml config file whereas with Flask, you're uploading another ~1300 files in addition this pair. (More on this towards the end of this post.)

Compare this real app to the pseudocode... similar, right? Our bot is a bit more complex in that it renders UI cards, so the create_message() function will need to do more than just return a plain text response. Instead, it must generate and return the JSON markup rendering the card in the Hangouts Chat UI:
def create_message(voter, vote_count=0, should_update=False):
    PARAMETERS = [{'key': 'count', 'value': str(vote_count)}]
    return {
        'actionResponse': {
            'type': 'UPDATE_MESSAGE' if should_update else 'NEW_MESSAGE'
        },
        'cards': [{
            'header': {'title': 'Last vote by %s!' % voter},
            'sections': [{
                'widgets': [{
                    'textParagraph': {'text': '%d votes!' % vote_count}
                }, {
                    'buttons': [{
                        'textButton': {
                            'text': '+1',
                            'onClick': {
                                'action': {
                                    'actionMethodName': 'upvote',
                                    'parameters': PARAMETERS,
                                }
                            }
                        }
                    }, {
                        'textButton': {
                            'text': '-1',
                            'onClick': {
                                'action': {
                                    'actionMethodName': 'downvote',
                                    'parameters': PARAMETERS,
                                }
                            }
                        }
                    }, {
                        'textButton': {
                            'text': 'NEW',
                            'onClick': {
                                'action': {
                                    'actionMethodName': 'newvote',
                                }
                            }
                        }
                    }]
                }]
            }]
        }]
    }

Finishing touches

The last thing App Engine needs is an app.yaml configuration file:
runtime: python27
api_version: 1
threadsafe: true

handlers:
- url: /.*
  script: bot.app
If you've deployed apps to GAE before, you know you need to create a project in the Google Cloud Developers Console. You also need to have a project for bots, however you can use the same project for both your use of the Hangouts Chat bot framework (link only works for G Suite customers) as well as hosting your App Engine-based bot.

Once you've deployed your bot to GAE, go to the Hangouts Chat API configuration tab, and add the HTTP endpoint for your App Engine-based bot to the Conenctions Settings section:

As you can see, there are several options here for where Hangouts Chat can post messages destined for bots: standard HTTPS bots like our App Engine example, Google Apps Script (a customized JavaScript-in-the-cloud platform with built-in G Suite integration [intro video]), or Google Cloud Pub/Sub. Pub/Sub is the message queue proxy for when your bot is hosted on-premise behind a firewall, requiring you to register a pull subscription to retrieve bot messages from Hangouts Chat.

Once you've published your bot, add the bot to a room or @mention it in a DM. Send it a message, and it returns an interactive vote card like what you see below. Cast some votes or create a new vote to test drive your new bot.

Conclusion

Congrats for creating your first Python bot! Below is the entire bot.py file for your convenience. (At this time, Python App Engine standard only supports Python 2, so if you want to run Python 3 instead, use the Python App Engine flexible environment.) Also don't forget the app.yaml configuration file above.
import json
import webapp2

def create_message(voter, vote_count=0, should_update=False):
    PARAMETERS = [{'key': 'count', 'value': str(vote_count)}]
    return {
        'actionResponse': {
            'type': 'UPDATE_MESSAGE' if should_update else 'NEW_MESSAGE'
        },
        'cards': [{
            'header': {'title': 'Last vote by %s!' % voter},
            'sections': [{
                'widgets': [{
                    'textParagraph': {'text': '%d votes!' % vote_count}
                }, {
                    'buttons': [{
                        'textButton': {
                            'text': '+1',
                            'onClick': {
                                'action': {
                                    'actionMethodName': 'upvote',
                                    'parameters': PARAMETERS,
                                }
                            }
                        }
                    }, {
                        'textButton': {
                            'text': '-1',
                            'onClick': {
                                'action': {
                                    'actionMethodName': 'downvote',
                                    'parameters': PARAMETERS,
                                }
                            }
                        }
                    }, {
                        'textButton': {
                            'text': 'NEW',
                            'onClick': {
                                'action': {
                                    'actionMethodName': 'newvote',
                                }
                            }
                        }
                    }]
                }]
            }]
        }]
    }

class VoteBot(webapp2.RequestHandler):
    def post(self):
        event = json.loads(self.request.body)
        user = event['user']['displayName']
        if event['type'] == 'CARD_CLICKED':
            method = event['action']['actionMethodName']
            if method == 'newvote':
                body = create_message(user)
            else:
                delta = 1 if method == 'upvote' else -1
                vote_count = int(event['action']['parameters'][0]['value']) + delta
                body = create_message(user, vote_count, True)
        elif event['type'] == 'MESSAGE':
            body = create_message(user)
        else: # no response for ADDED_TO_SPACE or REMOVED_FROM_SPACE
            return

        self.response.headers['Content-Type'] = 'application/json'
        self.response.out.write(json.dumps(body))

app = webapp2.WSGIApplication([
    ('/', VoteBot),
], debug=True)
Yep, the entire bot is made up of just these 2 files. While porting to Flask is fairly straightforward and Flask apps are supported by App Engine, Flask itself doesn't come with App Engine (quickstart here), so you'll need to upload all of Flask (in addition to those 2 files) to host a Flask version of your bot on GAE.

Both files are also available from this app's GitHub repo which you can fork. I'm happy to entertain PRs for any bugs you find or ways we can simplify this code even more. Below are some additional resources for learning about Hangouts Chat bots and GAE:
Python's a great language to implement bots with because not only can you create something quickly, but choose any platform to host it on, Google App Engine, Amazon EC2/Google Compute Engine, or any cloud that runs Python apps.

Epilogue: code challenge

While usable, this vote bot can be significantly improved. Once you get the basic bot working, I recommend the following enhancements as exercises for the reader:
  1. Support vote topics: users starting new votes must state topic in bot message; use as card header
  2. Add images (like the Node.js version of our vote bot in the docs hosted on Google Cloud Functions)
  3. Track users who have voted (you decide on implementation)
  4. Don't let the vote count to go below zero
  5. Allow downvotes only from users who have at least one upvote
  6. Port your working bot from webapp2 to Flask

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

Thursday, November 6, 2014

Authorized Google API access from Python (part 2 of 2)

Listing your files with the Google Drive API

NOTE: You can also watch a video walkthrough of the common code covered in this blogpost here.

UPDATE (Mar 2020): You can build this application line-by-line with our codelab (self-paced, hands-on tutorial) introducing developers to G Suite APIs. The deprecated auth library comment from the previous update below is spelled out in more detail in the green sidebar towards the bottom of step/module 5 (Install the Google APIs Client Library for Python). Also, the code sample is now maintained in a GitHub repo which includes a port to the newer auth libraries so you have both versions to refer to.

UPDATE (Apr 2019): In order to have a closer relationship between the GCP and G Suite worlds of Google Cloud, all G Suite Python code samples have been updated, replacing some of the older G Suite API client libraries with their equivalents from GCP. NOTE: using the newer libraries requires more initial code/effort from the developer thus will seem "less Pythonic." However, we will leave the code sample here with the original client libraries (deprecated but not shutdown yet) to be consistent with the video.

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

UPDATE (Jun 2016): Updated to Python 2.7 & 3.3+ and Drive API v3.

Introduction

In this final installment of a (currently) two-part series introducing Python developers to building on Google APIs, we'll extend from the simple API example from the first post (part 1) just over a month ago. Those first snippets showed some skeleton code and a short real working sample that demonstrate accessing a public (Google) API with an API key (that queried public Google+ posts). An API key however, does not grant applications access to authorized data.

Authorized data, including user information such as personal files on Google Drive and YouTube playlists, require additional security steps before access is granted. Sharing of and hardcoding credentials such as usernames and passwords is not only insecure, it's also a thing of the past. A more modern approach leverages token exchange, authenticated API calls, and standards such as OAuth2.

In this post, we'll demonstrate how to use Python to access authorized Google APIs using OAuth2, specifically listing the files (and folders) in your Google Drive. In order to better understand the example, we strongly recommend you check out the OAuth2 guides (general OAuth2 info, OAuth2 as it relates to Python and its client library) in the documentation to get started.

The docs describe the OAuth2 flow: making a request for authorized access, having the user grant access to your app, and obtaining a(n access) token with which to sign and make authorized API calls with. The steps you need to take to get started begin nearly the same way as for simple API access. The process diverges when you arrive on the Credentials page when following the steps below.

Google API access

In order to Google API authorized access, follow these instructions (the first three of which are roughly the same for simple API access):
  • Go to the Google Developers Console and login.
    • Use your Gmail or Google credentials; create an account if needed
  • Click "Create a Project" from pulldown under your username (at top)
    • Enter a Project Name (mutable, human-friendly string only used in the console)
    • Enter a Project ID (immutable, must be unique and not already taken)
  • Once project has been created, enable APIs you wish to use
  • Select "Credentials" in left-nav
    • Click "Create credentials" and select OAuth client ID
    • In the new dialog, select your application type — we're building a command-line script which is an "Installed application"
    • In the bottom part of that same dialog, specify the type of installed application; choose "Other" (cmd-line scripts are not web nor mobile)
    • Click "Create Client ID" to generate your credentials
  • Finally, click "Download JSON" to save the new credentials to your computer... perhaps choose a shorter name like "client_secret.json" or "client_id.json"
NOTEs: Instructions from the previous blogpost were to get an API key. This time, in the steps above, we're creating and downloading OAuth2 credentials. You can also watch a video walkthrough of this app setup process of getting simple or authorized access credentials in the "DevConsole" here.

    Accessing Google APIs from Python

    In order to access authorized Google APIs from Python, you still need the Google APIs Client Library for Python, so in this case, do follow those installation instructions from part 1.

    We will again use googleapiclient.discovery.build(), which is required to create a service endpoint for interacting with an API, authorized or otherwise. However, for authorized data access, we need additional resources, namely the httplib2 and oauth2client packages. Here are the first five lines of the new boilerplate code for authorized access:

    from __future__ import print_function
    
    from googleapiclient import discovery
    from httplib2 import Http
    from oauth2client import file, client, tools
    
    SCOPES = # one or more scopes (strings)
    
    SCOPES is a critical variable: it represents the set of scopes of authorization an app wants to obtain (then access) on behalf of user(s). What's does a scope look like?

    Each scope is a single character string, specifically a URL. Here are some examples:
    • 'https://www.googleapis.com/auth/plus.me' — access your personal Google+ settings
    • 'https://www.googleapis.com/auth/drive.metadata.readonly' — read-only access your Google Drive file or folder metadata
    • 'https://www.googleapis.com/auth/youtube' — access your YouTube playlists and other personal information
    You can request one or more scopes, given as a single space-delimited string of scopes or an iterable (list, generator expression, etc.) of strings.  If you were writing an app that accesses both your YouTube playlists as well as your Google+ profile information, your SCOPES variable could be either of the following:
    SCOPES = 'https://www.googleapis.com/auth/plus.me https://www.googleapis.com/auth/youtube'

    That is space-delimited and made tiny by me so it doesn't wrap in a regular-sized browser window; or it could be an easier-to-read, non-tiny, and non-wrapped tuple:

    SCOPES = (
        'https://www.googleapis.com/auth/plus.me',
        'https://www.googleapis.com/auth/youtube',
    )

    Our example command-line script will just list the files on your Google Drive, so we only need the read-only Drive metadata scope, meaning our SCOPES variable will be just this:
    SCOPES = 'https://www.googleapis.com/auth/drive.metadata.readonly'
    The next section of boilerplate represents the security code:
    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)
    
    Once the user has authorized access to their personal data by your app, a special "access token" is given to your app. This precious resource must be stored somewhere local for the app to use. In our case, we'll store it in a file called "storage.json". The lines setting the store and creds variables are attempting to get a valid access token with which to make an authorized API call.

    If the credentials are missing or invalid, such as being expired, the authorization flow (using the client secret you downloaded along with a set of requested scopes) must be created (by client.flow_from_clientsecrets()) and executed (by tools.run_flow()) to ensure possession of valid credentials. The client_secret.json file is the credentials file you saved when you clicked "Download JSON" from the DevConsole after you've created your OAuth2 client ID.

    If you don't have credentials at all, the user much explicitly grant permission — I'm sure you've all seen the OAuth2 dialog describing the type of access an app is requesting (remember those scopes?). Once the user clicks "Accept" to grant permission, a valid access token is returned and saved into the storage file (because you passed a handle to it when you called tools.run_flow()).

    Note: tools.run() deprecated by tools.run_flow()
    You may have seen usage of the older tools.run() function, but it has been deprecated by tools.run_flow(). We explain this in more detail in another blogpost specifically geared towards migration.

    Once the user grants access and valid credentials are saved, you can create one or more endpoints to the secure service(s) desired with googleapiclient.discovery.build(), just like with simple API access. Its call will look slightly different, mainly that you need to sign your HTTP requests with your credentials rather than passing an API key:

    DRIVE = discovery.build(API, VERSION, http=creds.authorize(Http()))

    In our example, we're going to list your files and folders in your Google Drive, so for API, use the string 'drive'. The API is currently on version 3 so use 'v3' for VERSION:

    DRIVE = discovery.build('drive', 'v3', http=creds.authorize(Http()))

    If you want to get comfortable with OAuth2, what it's flow is and how it works, we recommend that you experiment at the OAuth Playground. There you can choose from any number of APIs to access and experience first-hand how your app must be authorized to access personal data.

    Going back to our working example, once you have an established service endpoint, you can use the list() method of the files service to request the file data:

    files = DRIVE.files().list().execute().get('files', [])

    If there's any data to read, the response dict will contain an iterable of files that we can loop over (or default to an empty list so the loop doesn't fail), displaying file names and types:

    for f in files:
        print(f['name'], f['mimeType'])

    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 files().list(). For more information on what other operations you can execute with the Google Drive API, take a look at the reference docs and check out the companion video for this code sample. Don't forget the codelab and this sample's GitHub repo. That's it!

    Below is the entire script for your convenience:
    '''
    drive_list.py -- Google Drive API demo; maintained at:
        http://github.com/googlecodelabs/gsuite-apis-intro
    '''
    from __future__ import print_function
    
    from googleapiclient import discovery
    from httplib2 import Http
    from oauth2client import file, client, tools
    
    SCOPES = 'https://www.googleapis.com/auth/drive.readonly.metadata'
    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)
    
    DRIVE = discovery.build('drive', 'v3', http=creds.authorize(Http()))
    files = DRIVE.files().list().execute().get('files', [])
    for f in files:
        print(f['name'], f['mimeType'])
    
    When you run it, you should see pretty much what you'd expect, a list of file or folder names followed by their MIMEtypes — I named my script drive_list.py:
    $ python3 drive_list.py
    Google Maps demo application/vnd.google-apps.spreadsheet
    Overview of Google APIs - Sep 2014 application/vnd.google-apps.presentation
    tiresResearch.xls application/vnd.google-apps.spreadsheet
    6451_Core_Python_Schedule.doc application/vnd.google-apps.document
    out1.txt application/vnd.google-apps.document
    tiresResearch.xls application/vnd.ms-excel
    6451_Core_Python_Schedule.doc application/msword
    out1.txt text/plain
    Maps and Sheets demo application/vnd.google-apps.spreadsheet
    ProtoRPC Getting Started Guide application/vnd.google-apps.document
    gtaskqueue-1.0.2_public.tar.gz application/x-gzip
    Pull Queues application/vnd.google-apps.folder
    gtaskqueue-1.0.1_public.tar.gz application/x-gzip
    appengine-java-sdk.zip application/zip
    taskqueue.py text/x-python-script
    Google Apps Security Whitepaper 06/10/2010.pdf application/pdf
    
    Obviously your output will be different, depending on what files are in your Google Drive. But that's it... hope this is useful. You can now customize this code for your own needs and/or to access other Google APIs. Thanks for reading!

    EXTRA CREDIT: To test your skills, add functionality to this code that also displays the last modified timestamp, the file (byte)size, and perhaps shave the MIMEtype a bit as it's slightly harder to read in its entirety... perhaps take just the final path element? One last challenge: in the output above, we have both Microsoft Office documents as well as their auto-converted versions for Google Apps... perhaps only show the filename once and have a double-entry for the filetypes!