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:


Thursday, June 1, 2017

Managing Shared (formerly Team) Drives with Python and the Google Drive API

2023 UPDATE: We are working to put updated versions of all the code into GitHub... stay tuned. The link will provided in all posts once the code sample(s) is(are) available.

2019 UPDATE: "G Suite" is now called "Google Workspace", "Team Drives" is now known as "Shared Drives", and the corresponding supportsTeamDrives flag has been renamed to supportsAllDrives. Please take note of these changes regarding the post below.

NOTE 1: Team Drives is only available for G Suite Business Standard users or higher. If you're developing an application for Team Drives, you'll need similar access.
NOTE 2: The code featured here is also available as a video + overview post as part of this series.

Introduction

Team Drives is a relatively new feature from the Google Drive team, created to solve some of the issues of a user-centric system in larger organizations. Team Drives are owned by an organization rather than a user and with its use, locations of files and folders won't be a mystery any more. While your users do have to be a G Suite Business (or higher) customer to use Team Drives, the good news for developers is that you won't have to write new apps from scratch or learn a completely different API.

Instead, Team Drives features are accessible through the same Google Drive API you've come to know so well with Python. In this post, we'll demonstrate a sample Python app that performs core features that all developers should be familiar with. By the time you've finished reading this post and the sample app, you should know how to:
  • Create Team Drives
  • Add members to Team Drives
  • Create a folder in Team Drives
  • Import/upload files to Team Drives folders

Using the Google Drive API

The demo script requires creating files and folders, so you do need full read-write access to Google Drive. The scope you need for that is:
  • 'https://www.googleapis.com/auth/drive' — Full (read-write) access to Google Drive
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, assume you have a service endpoint to the API and have assigned it to the DRIVE variable.

Create Team Drives

New Team Drives can be created with DRIVE.teamdrives().create(). Two things are required to create a Team Drive: 1) you should name your Team Drive. To make the create process idempotent, you need to create a unique request ID so that any number of identical calls will still only result in a single Team Drive being created. It's recommended that developers use a language-specific UUID library. For Python developers, that's the uuid module. From the API response, we return the new Team Drive's ID. Check it out:
def create_td(td_name):
    request_id = str(uuid.uuid4())
    body = {'name': td_name}
    return DRIVE.teamdrives().create(body=body,
            requestId=request_id, fields='id').execute().get('id')

Add members to Team Drives

To add members/users to Team Drives, you only need to create a new permission, which can be done with  DRIVE.permissions().create(), similar to how you would share a file in regular Drive with another user.  The pieces of information you need for this request are the ID of the Team Drive, the new member's email address as well as the desired role... choose from: "organizer", "owner", "writer", "commenter", "reader". Here's the code:
def add_user(td_id, user, role='commenter'):
    body = {'type': 'user', 'role': role, 'emailAddress': user}
    return DRIVE.permissions().create(body=body, fileId=td_id,
            supportsTeamDrives=True, fields='id').execute().get('id')
Some additional notes on permissions: the user can only be bestowed permissions equal to or less than the person/admin running the script... IOW, they cannot grant someone else greater permission than what they have. Also, if a user has a certain role in a Team Drive, they can be granted greater access to individual elements in the Team Drive. Users who are not members of a Team Drive can still be granted access to Team Drive contents on a per-file basis.

Create a folder in Team Drives

Nothing to see here! Yep, creating a folder in Team Drives is identical to creating a folder in regular Drive, with DRIVE.files().create(). The only difference is that you pass in a Team Drive ID rather than regular Drive folder ID. Of course, you also need a folder name too. Here's the code:
def create_td_folder(td_id, folder):
    body = {'name': folder, 'mimeType': FOLDER_MIME, 'parents': [td_id]}
    return DRIVE.files().create(body=body,
            supportsTeamDrives=True, fields='id').execute().get('id')

Import/upload files to Team Drives folders

Uploading files to a Team Drives folder is also identical to to uploading to a normal Drive folder, and also done with DRIVE.files().create(). Importing is slightly different than uploading because you're uploading a file and converting it to a G Suite/Google Apps document format, i.e., uploading CSV as a Google Sheet, or plain text or Microsoft Word® file as Google Docs. In the sample app, we tackle the former:
def import_csv_to_td_folder(folder_id, fn, mimeType):
    body = {'name': fn, 'mimeType': mimeType, 'parents': [folder_id]}
    return DRIVE.files().create(body=body, media_body=fn+'.csv',
            supportsTeamDrives=True, fields='id').execute().get('id')
The secret to importing is the MIMEtype. That tells Drive whether you want conversion to a G Suite/Google Apps format (or not). The same is true for exporting. The import and export MIMEtypes supported by the Google Drive API can be found in my SO answer here.

Driver app

All these functions are great but kind-of useless without being called by a main application, so here we are:
FOLDER_MIME = 'application/vnd.google-apps.folder'
SOURCE_FILE = 'inventory' # on disk as 'inventory.csv'
SHEETS_MIME = 'application/vnd.google-apps.spreadsheet'

td_id = create_td('Corporate shared TD')
print('** Team Drive created')
perm_id = add_user(td_id, 'email@example.com')
print('** User added to Team Drive')
folder_id = create_td_folder(td_id, 'Manufacturing data')
print('** Folder created in Team Drive')
file_id = import_csv_to_td_folder(folder_id, SOURCE_FILE, SHEETS_MIME)
print('** CSV file imported as Google Sheets in Team Drives folder')
The first set of variables represent some MIMEtypes we need to use as well as the CSV file we're uploading to Drive and requesting it be converted to Google Sheets format. Below those definitions are calls to all four functions described above.

Conclusion

If you run the script, you should get output that looks something like this, with each print() representing each API call:
$ python3 td_demo.py
** Team Drive created
** User added to Team Drive
** Folder created in Team Drive
** CSV file imported as Google Sheets in Team Drives folder
When the script has completed, you should have a new Team Drives folder called "Corporate shared TD", and within, a folder named "Manufacturing data" which contains a Google Sheets file called "inventory".

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
import uuid

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

SCOPES = 'https://www.googleapis.com/auth/drive'
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()))

def create_td(td_name):
    request_id = str(uuid.uuid4()) # random unique UUID string
    body = {'name': td_name}
    return DRIVE.teamdrives().create(body=body,
            requestId=request_id, fields='id').execute().get('id')

def add_user(td_id, user, role='commenter'):
    body = {'type': 'user', 'role': role, 'emailAddress': user}
    return DRIVE.permissions().create(body=body, fileId=td_id,
            supportsTeamDrives=True, fields='id').execute().get('id')

def create_td_folder(td_id, folder):
    body = {'name': folder, 'mimeType': FOLDER_MIME, 'parents': [td_id]}
    return DRIVE.files().create(body=body,
            supportsTeamDrives=True, fields='id').execute().get('id')

def import_csv_to_td_folder(folder_id, fn, mimeType):
    body = {'name': fn, 'mimeType': mimeType, 'parents': [folder_id]}
    return DRIVE.files().create(body=body, media_body=fn+'.csv',
            supportsTeamDrives=True, fields='id').execute().get('id')

FOLDER_MIME = 'application/vnd.google-apps.folder'
SOURCE_FILE = 'inventory' # on disk as 'inventory.csv'... CHANGE!
SHEETS_MIME = 'application/vnd.google-apps.spreadsheet'

td_id = create_td('Corporate shared TD')
print('** Team Drive created')
perm_id = add_user(td_id, 'email@example.com') # CHANGE!
print('** User added to Team Drive')
folder_id = create_td_folder(td_id, 'Manufacturing data')
print('** Folder created in Team Drive')
file_id = import_csv_to_td_folder(folder_id, SOURCE_FILE, SHEETS_MIME)
print('** CSV file imported as Google Sheets in Team Drives folder')
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

Write a simple application that moves folders (and its files or folders) in regular Drive to Team Drives. Each folder you move should be a corresponding folder in Team Drives. Remember that files in Team Drives can only have one parent, and the same goes for folders.

Wednesday, February 22, 2017

Adding text & shapes with the Google Slides API

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

Introduction

This is the fourth entry highlighting primary use cases of the Google Slides API with Python; check back in the archives to access the first three. Today, we're focused on some of the basics, like adding text to slides. We'll also cover adding shapes, and as a bonus, adding text into shapes!

Using the Google Slides API

The demo script requires creating a new slide deck (and adding a new slide) so you need the read-write scope for Slides:
  • '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, assume you have a service endpoint to the API and have assigned it to the SLIDES variable.

Create new presentation & get its objects' IDs

A new slide deck can be created with SLIDES.presentations().create()—or alternatively with the Google Drive API which we won't do here. From the API response, we save the new deck's ID along with the IDs of the title and subtitle textboxes on the default title slide:
rsp = SLIDES.presentations().create(
        body={'title': 'Adding text formatting DEMO'}).execute()
deckID = rsp['presentationId']
titleSlide = rsp['slides'][0]     # title slide object IDs
titleID    = titleSlide['pageElements'][0]['objectId']
subtitleID = titleSlide['pageElements'][1]['objectId']
The title slide only has two elements on it, the title and subtitle textboxes, returned in that order, hence why we grab them at indexes 0 and 1 respectively.

Generating our own unique object IDs

In the next steps, we generate our own unique object IDs. We'll first explain what those objects are followed by why you'd want to create your own object IDs rather than letting the API create default IDs for the same objects.

As we've done in previous posts on the Slides API, we create one new slide with the "main point" layout. It has one notable object, a "large-ish" textbox and nothing else. We'll create IDs for the slide itself and another for its textbox. Next, we'll (use the API to) "draw" 3 shapes on this slide, so we'll create IDs for each of those. That's 5 (document-unique) IDs total. Now let's discuss why you'd "roll your own" IDs.

Why and how to generate our own IDs

It's advantageous for all developers to minimize the overall number of calls to Google APIs. While most of services provided through the APIs are free, they'll have some quota to prevent abuse (Slides API quotas page FYI). So how does creating our own IDs help reduce API calls?

Passing in object IDs is optional for "create" calls. Providing your own ID lets you create an object and modify it using additional requests within the same API call to SLIDES.presentations().batchUpdate(). If you don't provide your own object IDs, the API will generate a unique one for you.

Unfortunately, this means that instead of one API call, you'll need one to create the object, likely another to get that object to determine its ID, and yet another to update that object using the ID you just fetched. Separate API calls to create, get, and update means (at least) 3x more than if you provided your own IDs (where you can do create & update with a single API call; no get necessary).

Here are a few things to know when rolling your own IDs:
  • IDs must start with an alphanumeric character or underscore (matches regex [a-zA-Z0-9_])
  • Any remaining characters can also include a hyphen or colon (matches regex [a-zA-Z0-9_-:])
  • The length of the ID must conform to: 5 ≤ len(ID) ≤ 50.
  • Object IDs must be unique across all objects in a presentation.

You'll somehow need to ensure your IDs are unique or use UUIDs (universally unique identifiers) for which most languages have libraries for. Examples: Java developers can use java.util.UUID.randomUUID().toString() while Python users can import the uuid module plus any extra work to get UUID string values:
import uuid
gen_uuid = lambda : str(uuid.uuid4())  # get random UUID string
Finally, be aware that if an object is modified in the UI, its ID may change. For more information, review the "Working with object IDs" section in the Slides API Overview page.

Back to sample app

All that said, let's go back to the code and generate those 5 random object IDs we promised earlier:
mpSlideID   = gen_uuid() # mainpoint IDs
mpTextboxID = gen_uuid()
smileID     = gen_uuid() # shape IDs
str24ID     = gen_uuid()
arwbxID     = gen_uuid()
With that, we're ready to create the requests array (reqs) to send to the API.

Create "main point" slide

The first request creates the "main point" slide...
reqs = [
    {'createSlide':
        'objectId': mpSlideID,
        'slideLayoutReference': {'predefinedLayout': 'MAIN_POINT'},
        'placeholderIdMappings': [{
            'objectId': mpTextboxID,
            'layoutPlaceholder': {'type': 'TITLE', 'index': 0}
        }],
    }},
...where...
  • objectID—our generated ID we're assigning to the newly-created slide
  • slideLayoutReference—new slide layout type ("main point")
  • placeholderIdMappings—array of IDs ([inner] objectId) for each of the page elements and which object (layoutPlaceholder) they should map or be assigned to
The page elements created on the new slide (depends [obviously] on the layout chosen); "main point" only has the one textbox, hence why placeholderIdMappings only has one element.

Add title slide and main point textbox text

The next requests fill in the title & subtitle in the default title slide and also the textbox on the main point slide.
{'insertText': {'objectId': titleID, 'text': 'Adding text and shapes'}},
{'insertText': {'objectId': subtitleID, 'text': 'via the Google Slides API'}},
{'insertText': {'objectId': mpTextboxID, 'text': 'text & shapes'}},
The first pair use IDs that were generated by the Slides API when the presentation was created while the main point textbox ID was generated by us.

Create three shapes

Above, we created IDs for three shapes, a "smiley face," a 24-point star, and a double arrow box (smileID, str24ID, arwbxID). The request for the first looks like this:
{'createShape': {
    'objectId': smileID,
    'shapeType': 'SMILEY_FACE',
    'elementProperties': {
        "pageObjectId": mpSlideID,
        'size': {
            'height': {'magnitude': 3000000, 'unit': 'EMU'},
            'width':  {'magnitude': 3000000, 'unit': 'EMU'}
        },
        'transform': {
            'unit': 'EMU', 'scaleX': 1.3449, 'scaleY': 1.3031,
            'translateX': 4671925, 'translateY': 450150,
        },
    },
}}
The JSON for the other two shapes are similar, with differences being: the object ID, the shapeType, and the transform. You can see the corresponding requests for the other shapes in the full source code at the bottom of this post, so we won't display them here as the descriptions will be nearly identical.

Size & transform for slide objects

When placing or manipulating objects on slides, key element properties you must provide are the sizes and transforms. These are components you must either use some math to create or derive from pre-existing objects. Resizing, rotating, and similar operations require some basic knowledge of matrix math. Take a look at the Page Elements page in the official docs as well as the Transforms concept guide for more details.

Deriving from pre-existing objects: if you're short on time, don't want to deal with the math, or perhaps thinking something like, "Geez, I just want to draw a smiley face on a slide." One common pattern then, is to bring up the Slides UI, create a blank slide & place your image or draw your shape the way you want, with the size you want, & putting it exactly where you want. For example:


Once you have that desired shape (and size and location), you can use the API (either presentations.get or presentations.pages.get) to read that object's size and transform then drop both of those into your application so the API creates a new shape in the exact way, mirroring what you created in the UI. For the smiley face above, the JSON payload we got back from one of the "get" calls could look something like:

If you scroll back up to the createShape request, you'll see we used those exact values. Note: because the 3 shapes are all in different locations and sizes, expect the corresponding values for each shape to be different.

Bonus: adding text to shapes

Now that you know how to add text and shapes, it's only fitting that we show you how to add text into shapes. The good news is that the technique is no different than adding text to textboxes or even tables. So with the shape IDs, our final set of requests along with the batchUpdate() call looks like this:
    {'insertText': {'objectId': smileID, 'text': 'Put the nose somewhere here!'}},
    {'insertText': {'objectId': str24ID, 'text': 'Count 24 points on this star!'}},
    {'insertText': {'objectId': arwbxID, 'text': "An uber bizarre arrow box!"}},
] # end of 'reqs'
SLIDES.presentations().batchUpdate(body={'requests': reqs},
        presentationId=deckID).execute()

Conclusion

If you run the script, you should get output that looks something like this, with each print() representing each API call:
$ python3 slides_shapes_text.py 
** Create new slide deck & set up object IDs
** Create "main point" slide, add text & interesting shapes
DONE
When the script has completed, you should have a new presentation with a title slide and a main point slide with shapes which should look something like this:

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
import uuid

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

gen_uuid = lambda : str(uuid.uuid4())  # get random UUID string

SCOPES = '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)
SLIDES = discovery.build('slides', 'v1', http=creds.authorize(Http()))

print('** Create new slide deck & set up object IDs')
rsp = SLIDES.presentations().create(
        body={'title': 'Adding text & shapes DEMO'}).execute()
deckID = rsp['presentationId']
titleSlide  = rsp['slides'][0]      # title slide object IDs
titleID     = titleSlide['pageElements'][0]['objectId']
subtitleID  = titleSlide['pageElements'][1]['objectId']
mpSlideID   = gen_uuid()            # mainpoint IDs
mpTextboxID = gen_uuid()
smileID     = gen_uuid()            # shape IDs
str24ID     = gen_uuid()
arwbxID     = gen_uuid()

print('** Create "main point" slide, add text & interesting shapes')
reqs = [
    # create new "main point" layout slide, giving slide & textbox IDs
    {'createSlide': {
        'objectId': mpSlideID,
        'slideLayoutReference': {'predefinedLayout': 'MAIN_POINT'},
        'placeholderIdMappings': [{
            'objectId': mpTextboxID,
            'layoutPlaceholder': {'type': 'TITLE', 'index': 0}
        }],
    }},
    # add title & subtitle to title slide; add text to main point slide textbox
    {'insertText': {'objectId': titleID,     'text': 'Adding text and shapes'}},
    {'insertText': {'objectId': subtitleID,  'text': 'via the Google Slides API'}},
    {'insertText': {'objectId': mpTextboxID, 'text': 'text & shapes'}},
    # create smiley face
    {'createShape': {
        'objectId': smileID,
        'shapeType': 'SMILEY_FACE',
        'elementProperties': {
            "pageObjectId": mpSlideID,
            'size': {
                'height': {'magnitude': 3000000, 'unit': 'EMU'},
                'width':  {'magnitude': 3000000, 'unit': 'EMU'}
            },
            'transform': {
                'unit': 'EMU', 'scaleX': 1.3449, 'scaleY': 1.3031,
                'translateX': 4671925, 'translateY': 450150,
            },
        },
    }},
    # create 24-point star
    {'createShape': {
        'objectId': str24ID,
        'shapeType': 'STAR_24',
        'elementProperties': {
            "pageObjectId": mpSlideID,
            'size': {
                'height': {'magnitude': 3000000, 'unit': 'EMU'},
                'width':  {'magnitude': 3000000, 'unit': 'EMU'}
            },
            'transform': {
                'unit': 'EMU', 'scaleX': 0.7079, 'scaleY': 0.6204,
                'translateX': 2036175, 'translateY': 237350,
            },
        },
    }},
    # create double left & right arrow w/textbox
    {'createShape': {
        'objectId': arwbxID,
        'shapeType': 'LEFT_RIGHT_ARROW_CALLOUT',
        'elementProperties': {
            "pageObjectId": mpSlideID,
            'size': {
                'height': {'magnitude': 3000000, 'unit': 'EMU'},
                'width':  {'magnitude': 3000000, 'unit': 'EMU'}
            },
            'transform': {
                'unit': 'EMU', 'scaleX': 1.1451, 'scaleY': 0.4539,
                'translateX': 1036825, 'translateY': 3235375,
            },
        },
    }},
    # add text to all 3 shapes
    {'insertText': {'objectId': smileID, 'text': 'Put the nose somewhere here!'}},
    {'insertText': {'objectId': str24ID, 'text': 'Count 24 points on this star!'}},
    {'insertText': {'objectId': arwbxID, 'text': "An uber bizarre arrow box!"}},
]
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

Create a 2x3 or 3x4 table on a slide and add text to each "cell." This should be a fairly easy exercise, especially if you look at the Table Operations documentation. HINT: you'll be using insertText with just an extra field, cellLocation. EXTRA CREDIT: generalize your solution so that you're grabbing cells from a Google Sheet and "import" them into a table on a slide. HINT: look for the earlier post where we describe how to create slides from spreadsheet data.

Tuesday, December 13, 2016

Formatting text with the Google Slides API

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

Introduction

If you know something about public speaking, you're aware that the most effective presentations are those which have more images and less text. As a developer of applications that auto-generate slide decks, this is even more critical as you must ensure that your code creates the most compelling presentations possible for your users.

This means that any text featured in those slide decks must be more impactful. To that end, it's important you know how to format any text you do have. That's the exact subject of today's post, showing you how to format text in a variety of ways using Python and the Google Slides API.

The API is fairly new, so if you're unfamiliar with it, check out the launch post and take a peek at the API overview page to acclimate yourself to it first. You can also read related posts (and videos) explaining how to replace text & images with the API or how to generate slides from spreadsheet data. If you're ready-to-go, let's move on!

Using the Google Slides API

The demo script requires creating a new slide deck so you need the read-write scope for Slides:
  • '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, assume you have a service endpoint to the API and have assigned it to the SLIDES variable.

Create deck & set up new slide for text formatting

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, "Slides text formatting DEMO" and save its ID along with the IDs of the title and subtitle textboxes on the auto-created title slide:
DATA = {'title': 'Slides text formatting 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']
The title slide only has two elements on it, the title and subtitle textboxes, returned in that order, hence why we grab them at indexes 0 and 1 respectively. Now that we have a deck, let's add a slide that has a single (largish) textbox. The slide layout with that characteristic that works best for our demo is the "main point" template:



While we're at it, let's also add the title & subtitle on the title slide. Here's the snippet that builds and executes all three requests:
print('** Create "main point" layout slide & add titles')
reqs = [
  {'createSlide':
     {'slideLayoutReference': {'predefinedLayout': 'MAIN_POINT'}}},
  {'insertText':
     {'objectId': titleID, 'text': 'Formatting text'}},
  {'insertText':
     {'objectId': subtitleID, 'text': 'via the Google Slides API'}},
]
rsp = SLIDES.presentations().batchUpdate(body={'requests': reqs},
        presentationId=deckID).execute().get('replies')
slideID = rsp[0]['createSlide']['objectId']
The requests are sent in the order you see above, and responses come back in the same order. We don't care much about the 'insertText' directives, but we do want to get the ID of the newly-created slide. In the array of 3 returned responses, that slideID comes first.

Why do we need the slide ID? Well, since we're going to be using the one textbox on that slide, the only way to get the ID of that textbox is by doing a presentations().pages().get() call to fetch all the objects on that slide. Since there's only one "page element," the textbox in question, we make that call and save the first (and only) object's ID:
print('** Fetch "main point" slide title (textbox) ID')
rsp = SLIDES.presentations().pages().get(presentationId=deckID,
        pageObjectId=slideID).execute().get('pageElements')
textboxID = rsp[0]['objectId']
Armed with the textbox ID, we're ready to add our text and format it!

Formatting text

The last part of the script starts by inserting seven (short) paragraphs of text—then format different parts of that text (in a variety of ways). Take a look here, then we'll discuss below:
reqs = [
    # add 6 paragraphs
    {'insertText': {
        'text': 'Bold 1\nItal 2\n\tfoo\n\tbar\n\t\tbaz\n\t\tqux\nMono 3',
        'objectId': textboxID,
    }},
    # shrink text from 48pt ("main point" textbox default) to 32pt
    {'updateTextStyle': {
        'objectId': textboxID,
        'style': {'fontSize': {'magnitude': 32, 'unit': 'PT'}},
        'textRange': {'type': 'ALL'},
        'fields': 'fontSize',
    }},
    # change word 1 in para 1 ("Bold") to bold
    {'updateTextStyle': {
        'objectId': textboxID,
        'style': {'bold': True},
        'textRange': {'type': 'FIXED_RANGE', 'startIndex': 0, 'endIndex': 4},
        'fields': 'bold',
    }},
    # change word 1 in para 2 ("Ital") to italics
    {'updateTextStyle': {
        'objectId': textboxID,
        'style': {'italic': True},
        'textRange': {'type': 'FIXED_RANGE', 'startIndex': 7, 'endIndex': 11},
        'fields': 'italic'
    }},
    # change word 1 in para 7 ("Mono") to Courier New
    {'updateTextStyle': {
        'objectId': textboxID,
        'style': {'fontFamily': 'Courier New'},
        'textRange': {'type': 'FIXED_RANGE', 'startIndex': 36, 'endIndex': 40},
        'fields': 'fontFamily'
    }},
    # bulletize everything
    {'createParagraphBullets': {
        'objectId': textboxID,
        'textRange': {'type': 'ALL'},
    }},
]
After the text is inserted, the first operation this code performs is to change the font size of all the text inserted ('ALL' means to format the entire text range) to 32 pt. The main point layout specifies a default font size of 48 pt, so this request shrinks the text so that everything fits and doesn't wrap. The 'fields' parameter specifies that only the 'fontSize' attribute is affected by this command, meaning leave others such as the font type, color, etc., alone.

The next request bolds the first word of the first paragraph. Instead of 'ALL', the exact range for the first word is given. (NOTE: the end index is excluded from the range, so that's why it must be 4 instead of 3, or you're going to lose one character.) In this case, it's the "Bold" word from the first paragraph, "Bold 1". Again, 'fields' is present to indicate that only the font size should be affected by this request while everything else is left alone. The next directive is nearly identical except for italicizing the first word ("Ital") of the second paragraph ("Ital 2").

After this we have a text style request to alter the font of the first word ("Mono") in the last paragraph ("Mono 3") to Courier New. The only other difference is that 'fields' is now 'fontFamily' instead of a flag. Finally, bulletize all paragraphs. Another call to SLIDES.presentations().batchUpdate() and we're done.

Conclusion

If you run the script, you should get output that looks something like this, with each print() representing execution of key parts of the application:
$ python3 slides_format_text.py 
** Create new slide deck
** Create "main point" layout slide & add titles
** Fetch "main point" slide title (textbox) ID
** Insert text & perform various formatting operations
DONE
When the script has completed, you should have a new presentation with these slides:




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

SCOPES = '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)
SLIDES = discovery.build('slides', 'v1', http=creds.authorize(Http()))

print('** Create new slide deck')
DATA = {'title': 'Slides text formatting 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 "main point" layout slide & add titles')
reqs = [
    {'createSlide': {'slideLayoutReference': {'predefinedLayout': 'MAIN_POINT'}}},
    {'insertText': {'objectId': titleID, 'text': 'Formatting text'}},
    {'insertText': {'objectId': subtitleID, 'text': 'via the Google Slides API'}},
]
rsp = SLIDES.presentations().batchUpdate(body={'requests': reqs},
        presentationId=deckID).execute().get('replies')
slideID = rsp[0]['createSlide']['objectId']

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

print('** Insert text & perform various formatting operations')
reqs = [
    # add 7 paragraphs
    {'insertText': {
        'text': 'Bold 1\nItal 2\n\tfoo\n\tbar\n\t\tbaz\n\t\tqux\nMono 3',
        'objectId': textboxID,
    }},
    # shrink text from 48pt ("main point" textbox default) to 32pt
    {'updateTextStyle': {
        'objectId': textboxID,
        'style': {'fontSize': {'magnitude': 32, 'unit': 'PT'}},
        'textRange': {'type': 'ALL'},
        'fields': 'fontSize',
    }},
    # change word 1 in para 1 ("Bold") to bold
    {'updateTextStyle': {
        'objectId': textboxID,
        'style': {'bold': True},
        'textRange': {'type': 'FIXED_RANGE', 'startIndex': 0, 'endIndex': 4},
        'fields': 'bold',
    }},
    # change word 1 in para 2 ("Ital") to italics
    {'updateTextStyle': {
        'objectId': textboxID,
        'style': {'italic': True},
        'textRange': {'type': 'FIXED_RANGE', 'startIndex': 7, 'endIndex': 11},
        'fields': 'italic'
    }},
    # change word 1 in para 6 ("Mono") to Courier New
    {'updateTextStyle': {
        'objectId': textboxID,
        'style': {'fontFamily': 'Courier New'},
        'textRange': {'type': 'FIXED_RANGE', 'startIndex': 36, 'endIndex': 40},
        'fields': 'fontFamily'
    }},
    # bulletize everything
    {'createParagraphBullets': {
        'objectId': textboxID,
        'textRange': {'type': 'ALL'},
    }},
]
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!

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.