Introduction: Alexa Skill: Read the Latest Tweet (in This Case, God's)

By Wrote an app, me ;-)
About: Technology is subversive.

I made an Alexa Skill to read "God's Latest Tweet" -- the content, that is, from @TweetOfGod, the 5 million+ subscriber account created by a former Daily Show comedy writer. It uses IFTTT (If This Then That), a Google Spreadsheet, and the incredibly easy-to-use Alexa Skill Builder, Storyline.

To get an idea of the end result, you can add the skill to your Alexa Device here, or you can preview the skill on Storyline if your Alexa account isn't in the US or you don't have an Alexa device.

If you want to build an Alexa skill that reads tweets, this is relatively easy way to do it. There's no coding involved if you just use my templates, but if you want to wander off the path it helps to know a bit about code in general and in particular how JSON calls are structured. But if you are just replicating this skill for a different Twitter account, it's not going to require technical skills beyond cut and paste.

What you'll need:

All of these accounts are free.

I won't go into to much detail about the basics of how to create a Storyline skill -- there are excellent tutorials at the site for learning how to create blocks, connect them, and branch between conditions. This tutorial will focus on three things I learned building this skill: getting an MP3 sound effect into your skill, connecting twitter content via IFTTT and Google Sheets, and how to generate a random tweet from a set of pre-created options.

(And a big shout out to Alexa Skill Developer George Collier, whose excellent tutorial on getting twitter integrated into Alexa got me started.)

Step 1: Step 1: Think About the Overall Flow of Your Skill

Storyline is a fantastic way to create Alexa skills with little or no coding. You can drag-and-drop blocks into place and set up connections and pathways between actions through an easy-to-understand graphic interface. If you ever used Yahoo Pipes, you'll recognize the interface.

Now, one of the cool things about Storyline is that it makes it fairly easy to get Alexa to speak the results of any JSON query. Getting data out of a Google Spreadsheet is easy with JSON queries. Getting tweets INTO a google spreadsheet with If This Than That is easy. Easy. Easy. Easy.

I find it's best to just visualize your entire skill in abstract before you start.

When I thought about my skill, I knew it's primary purpose was just to deliver the latest tweet. But I could enhance that with a little bit of sound design (Storyline allows your skill to play any MP3), and one tweet might not be enough to give people a flavour of the account -- I could stockpile some of the older tweets and let the user hear one of those after the latest. So my skill flow sketch might look like this:

  1. Welcome the user with a bit of spoken text and an appropriate introductory sound
  2. Read the latest tweet
  3. Play a signature sound
  4. Ask the user if they'd like to hear an older tweet
    1. Yes? Read an older tweet.
    2. Play the signature sound
    3. No? Exit the skill.

The source of the "Latest Tweet" is a google spreadsheet, fed by an If This Then That script. That process looks ike this:

  1. IF there's a new tweet from the @TweetOfGod account, it gets copied into the spreadsheet
  2. If the tweet contains a link or an image, the spreadsheet filters it out
  3. If the tweet is a retweet, the spreadsheet filters it out
  4. Tweets which get by those two filters are then processed for reading: # is replaced with the word "Hashtag" and several other characters are replaced with readable equivalents
  5. The final tweet is copied into the "latest tweet" cell which Alex reads

Step 2: Step 2: Create a Nice Welcome Block With Introductory Sound

Before Alexa reads the latest tweet, I randomly generate one of four humorous heavenly sounds. These are MP3s that have been processed for Alexa by storyline. I found my MP3s on Freesound (And everything there really is free, but be a decent human being and leave a tip).

  1. Download your MP3. It needs to be less than 90 Seconds. Alexa is particular about format. If you know it's MPEG Version 2 and 48kps you can skip the next step. But if you don't know or it's anything different, it's easy to convert.
  2. Upload it to Storyline for processing at their Audio Converter
  3. Host your downloaded audio on an HTTPS server

If step 3 has you going "DO WHAT NOW?" you probably don't have access to an https server that you can host your files on. Don't worry, you can do that with dropbox. You'll need an account, but again, a free one is fine. Here are the steps:

  1. Go to https://www.dropbox.com/h and log in to your account.
  2. Click Upload files button
  3. Select the mp3 file that you converted.
  4. Click Share
  5. Click Create a link and Copy the link
  6. In the link you copied, replace "dropbox" with "dl.dropboxusercontent" without the quotes
  7. Copy that URL

You're now going to go to the bottom of your welcome block and click the Musical note icon.

Paste your URL in. If you want to add random variations, repeat the process for a few more MP3 and click on the Hamburger menu below the URL paste box.

Step 3: Step 3: Set Up IFTTT

  1. Go to your IFTTT account and select "Create a new applet"
  2. Select TWITTER as the IF Service.
  3. Select "New tweet by specific user as the Trigger. Fill in the name of the account you want to follow
  4. Select "Google Sheets" as the THEN service
  5. Select "Add Row to Spreadsheet"
  6. In the "Formatted Row" Field, remove everything except the {{TEXT}} field.
  7. Create your skill.

This creates a new spreadsheet and adds a row every time a new tweet goes out. You may prefer to use a single cell on your spreadsheet and simply overwrite its content every time. In that case, in step 5 you can choose the option to write to a single cell. I like to keep a record of the tweets, as I periodically move the ones that are not topical or reacting to news to an "Older Tweets" spreadsheet. Be aware that if you choose this option, you'll need to do some maintenance on your sheet: a new one will be created after 2000 rows.

Step 4: Step 4: Set Up Your Google Sheet

This Google Sheet is the heart of this particular skill, because it filters out tweets that don't work really well with Alexa (tweets that refer to images, for example, or tweets with links) and it makes text-only tweets much more Alexa-friendly with a few simple replacements.

Let IFTT create your spreadsheet with a few entries -- so just wait for a few tweets from the account your tracking to land there, open Google Sheets, and sort by time created. You'll see your shiny new spreadsheet right on top. Now, every new tweet will be in a new row, so we want to create a formula that will filter out the tweets that have links or images and run through to find the last one in the column.

You can just replicate this copy of my spreadsheet or you can build your own with the following steps:

  1. Rename the tab with tweets in it "Live from IFTTT"
  2. Add a Tab called "Processing Tweets" to the spreadsheet
  3. Add this formula into cell A8 of the Processing Tweets tab:
=QUERY( 'Live from IFTTT'!A3:A2000 , "Select A Where not A contains 'https' " )

That pulls all the tweets that don't have a link into Column A of your processing tab.

Now we need to find the last tweet in that column. Paste the following formula into cell B7 of the processing tab:

=INDEX( FILTER( A:A , NOT( ISBLANK( A:A ) ) ) , ROWS( FILTER( A:A , NOT( ISBLANK( A:A ) ) ) ) )

Now we want to do a few replacements to make the tweet easier for Alexa to read. These can actually all be one in a single cell formula, but I broke them up for clarity:

In Cell B6 paste of the Processing tab:

=trim(regexreplace(B7, "#", "Hashtag "))

That looks at the content of the cell below and replaces the # sign with the word "Hashtag"

In Cell B5 paste the next iteration:

=trim(regexreplace(B6, "@", "at "))

You get the idea.

In Cell B4 paste:

=trim(regexreplace(B6, "&", "and "))

In Cell B3:

=trim(regexreplace(B6, "%", "percent "))

In Cell B2 we're going to place a slightly more complicated formula:

=ArrayFormula( REGEXREPLACE( B3 , "([^A-Za-z0-9.,!?:;'’])" , " " ) )

This one simply gets rid of ANYTHING that's not a number, letter, or one of the punctuation points that Alexa understands.

In Cell B1 we're simply going to copy the final tweet:

=index(B2)

That's the final text and you can programme storyline to grap that cell if you know a bit of JSON, but to make things a little simpler at the storyline end, I like to copy the contents into the "Live from IFTTT" tab by putting this formula in A2 in the "Live from IFTTT" tab:

='Processing Center'!B1

Groovy. Now your spreadsheet is all set up and ready to be read by a Storyline JSON query.


Step 5: Step 5: Set Up Your "Latest Tweet" JSON Query in Storyline

  1. Go to your storyline skill's welcome block and add a "What Alexa Says" step.
  2. Add an introductory phrase like "Here's the latest tweet from TheTweetOfGod:"
  3. Use the Hamburger Menu to add variations
  4. Click on the little rightwards arrow and select "Create New Block"

I called my new block "Get God Tweet" The task here is to go fetch that latest, filtered tweet from the spreadsheet cell A2 of the primary tab. You do that by fetching the data with a JSON request delivered via the Google Sheets API: It's really nothing more than a fancy URL.

  1. Click the little icon far right on your new block to add a JSON request.
  2. Name your API request. I called mine "GetGodTweet"
  3. Find the URL of your spreadsheet by doing this:
    1. Click on File -> Publish To Web in your spreadsheet.
    2. Use the default values and just click "Publish"
    3. Copy the URL and paste it into a note file.

My example is:

"https://docs.google.com/spreadsheets/d/e/2PACX-1vSUnz43PEORZbBES1lQ8ZlJjH_4voh4Guc6SWrfjeGk2bZlY5EBYzLD5-fT633ygo_35Jz97cuUwKuy/pubhtml" rel="nofollow">https://docs.google.com/spreadsheets/d/e/2PACX-1vS...</a></p>

Now you'll need to extract your document ID from that long string of ugliness. It's the bit between the /d/e and the next / charcter. So in this case: 

2PACX-1vSUnz43PEORZbBES1lQ8ZlJjH_4voh4Guc6SWrfjeGk2bZlY5EBYzLD5-fT633ygo_35Jz97cuUwKuy

You swap that long number in for the bit in the following url which says "SPREASHEET_ID":

"https://spreadsheets.google.com/feeds/list/SPREADSHEET_ID/od6/public/basic?alt=json"
  1. Now take THAT url, and paste it into the URL box of the JSON Query in Storyline.
  2. Select option "GET"
  3. Leave the "Headers" Box blank
  4. In the next box, you're going to create a variable with the contents of Cell A2 of your first tab by pasting this formula in:
tweet=api_response.feed.entry.0.title.$t

The variable is named "tweet". It's copying the contents from the primary tab 0.

Now if you add an "Alexa Says" block below your JSON query, and put just the word {{tweet}} in curly brackets, Alexa will say the content of the cell. Make sure the case matches the variable you named in step 4!!!

Press the PLAY button on Storyline and test your skill! If you get the word "Null" it means somethings gone wrong with your API request.

That's really it for the basic skill. I added a funny little audio signature with another MP3, and asked if the user wants to hear an older tweet. The next step shows you a nice trick for generating a random older tweet, but it's frosting for those who want to bring their skill some extra usability.

Step 6: Optional Extra: Generating a Random Result From Google Sheets for Alexa to Read

If you want to randomly generate one of a set of older tweets, here's a nifty trick.

I created a third tab in my spreadsheet called "Older Tweets." These all occupy cells A1-A36 in my spreadsheet

  1. In Storyline, create a new block called "Oldertweets"
  2. Add a JSON Query step
  3. Give it a name
  4. In the URL box, use the same API URL you constructed with your Spreadsheet ID in the Latest Tweet step, with one variation:
    1. Change the bit toward the end which says /od6/basic/public to /3/basic/public - this calls TAB 3 instead of Tab 1.
  5. Select "GET"
  6. Leave Headers blank
  7. In the next box, paste this:

oldtweet=api_response.feed.entry.random.title.$t

You've created a new variable, called "oldtweet" and that little word "random" means the variable will change every time the JSON query is called.

Add another "Alexa Says" step and insert your new variable, {{oldtweet}} with those curly brackets. Boom! Random goodness!

If you enjoyed this Instructable, please give my skill a few stars or a review!