Tweeting Google Slides Automatically

An app called Keynote Tweet has been around (in various working and non-working states) since the late 2000’s and let users auto-tweet images of their Keynote slides during a presentation to a hashtag or stream. Google released the Slides API this year and one of the API methods allows you to get a thumbnail of the image which can then be sent to other applications. You can see an example of this in a slideshow now by going to View > HTML View. It opens a tab with slide images embedded in plain HTML formatting. Since we can now get the image, we can start to push them out to other platforms with Google Apps Script.

This post is going to be technical in nature and is really meant as a proof-of-concept. I’ll explain some of the shortcomings of this implementation in context. The code is broken up into several chunks and the entire source is posted to GitHub.

Setup

First, the Slides API has to be enabled in the Google Cloud Console. Once that’s done, getting the thumbnails is pretty easy.

Off the bat, the API doesn’t have event triggers like the Forms, Sheets, or Docs. I wanted each slide to be tweeted as the presentation advanced, so I needed a custom presentation view. To get this to work, I wrote up a web app presentation window served by Google’s HtmlService.

This simple HTML page requests and displays the slides from an array created by the backend. There are some controls that hide on the bottom of the screen and a position indicator in the top right. Hover the mouse and they’ll pop up for interaction.

Issue 1

  1. The initial page load for the web app varies depending on the size of the presentation. The request for slides on line 37 fires as soon as the document loads in the browser. The loading GIF is replaced by the slides when they’re returned.

  2. The slide thumbnails are returned as 1600×900 pixel PNGs, so they’re big, which increases load time. There is no way to specify the size of the image returned at this point.

Each slide is sent as an image on a tweet as they show is advanced and has posted class added to prevent multiple tweets of the same slide. The “previous” button does not trigger a tweet in the event you go backwards.

I used Martin Hawksey’s TwtrService library to connect my Twitter account. He has a detailed post on how to connect and use the library, so I’m not going to go through that here. This is also where the second major snag comes up.

Issue 2

Google recommends not using libraries in production code because they can negative impact on script runtime. This is especially apparent on the first slide in this script – it times out frequently (3 of 5 times?) and I’m not sure why. Subsequent slides come in between 20-50 seconds, which isn’t terrible, considering the image size being uploaded. But, if you’re a fast talker, this won’t be able to keep up unless some kind of queueing is implemented.

To do this without a library, the OAuth flow needs to be incorporated into the main script. It’s beyond my ability at the moment, so if you’d like to contribute that component and help this run as a standalone app, you can do submit a pull request on the GitHub repo.

Tweeting

Sending the tweet is actually a two-step process. First, the slide thumbnail is posted and then the media_id assigned is attached to the tweet. This is all done on the Google Apps Script side of the code to account for security considerations.

Google’s thumbnail is generated and hosted on their server, so I used the UrlFetchApp to request the content as a blob. This is serialized data that can be passed on to Twitter’s image hosting service.

Once the image is uploaded, we can take the returned media_id string and attach it to a tweet. The Twitter API object for a tweet has a number of options, but all I’m using is status (what you’re saying) and media_ids, which takes the image ID string from the upload.

Right now, the string is hard-coded into the script. This could be set via the Apps Script UI tools if this gets turned into an AddOn at some point if I can speed it up.

Issue 3

Twitter requires a high degree of authorization for posting. I tried implementing the OAuth flow without using a library to speed up performance, but I couldn’t get it to work. TwtrService stores the app credentials for the OAuth flow and has both an upload and post method that make the tweeting easy. But, performance varies for 20 seconds to as long as 300.

Conclusion

The app works, which was exciting to put together and see. It’s a function that would be great in a number of situations and implementation will only get better as the Slides API improves. I’d love to work with someone with more experience to speed the API calls up significantly by including all the necessary authentication in the main script rather than in a library. If you’d be willing to contribute, the source code is on GitHub.

If you’d like to play with it, you can either copy all the files from GitHub or copy and paste the separate embeds here into an empty project. Add postTweet and getThumbnails to the code below.

Mountain Bluebird flickr photo by Andrej Chudy shared under a Creative Commons (BY-NC-SA) license

Date Countdown in Sheets for Triggers

I have a Google Sheet which displays all upcoming PD in the district. It also tracks registrations for people through a web app. I’ve documented that in other places, so I want to focus on an easy method of calculating days until an event to use as a script trigger.

This started because teachers were looking for an automated email reminder a few days before the workshop so they didn’t forget to come. I’d rather they get a Calendar invitation when they register for the event, but I ran into some authentication snags, so that aspect is back burner for the time being. Currently, the sheet is using today’s date and the date of the workshop to trigger an email four days in advance.

Calculating the “days remaining” is pretty easy. The cell formula is:

=ARRAYFORMULA(IF(ISBLANK(B2:B),””,ROUNDDOWN(B2:B – NOW())))

There are several components of this:

ARRAYFORMULA applies formulas to a range of cells rather than a single cell. Saves me from having to copy the formula down to each new entry.

ISBLANK checks for data in a cell. Because it’s inside ARRAYFORMULA, it looks at the cell in the matching row. If it is blank, TRUE is returned.

ROUNDDOWN rounds a result to a whole integer. This is useful because the subtraction taking place inside the formula returns a large decimal. This makes it easier to test in the script.

NOW gives the date and time when the sheet is updated. Any time you make a change, NOW is calculated.

– The IF conditional keeps the sheet clean and wraps everything up. The syntax is, IF(_logical test_, _value if true_, _value if false_). So, this reads, “If the cell column B for this row is blank, show nothing. If it’s false (is not blank), calculate the difference between the PD date in column B and NOW.

The core of the function is the count down calculation. For instance, today is Friday, September 8. Subtracting it from a date in the future like Monday, September 11, returns a whole integer: 3. I can test for that integer (or any integer) in a simple script.

This is particularly helpful with timed triggers in scripts. I have a utility script wrapped in a conditional:

“`

if(date === 3) {

// do something here

}

“`

If the condition isn’t met in the script, nothing happens and I don’t get a failure email notification. This is also nice because if I want to adjust the timing, the trigger can stay the same (daily, for instance) without changing the codebase.