How To Create a Skill for Amazon Alexa Using Google Sheets And Storyline

Imagine an Alexa skill that gives you some facts. It’s a simple skill — you just launch it, and it tells you a fact on some topic.

Simple Alexa skills for Amazon Echo share the same facts every time you launch them. But there are skills, where creators update content on a regular basis. We call them dynamic skills.

In this article, I’ll explain how to make dynamic Alexa skills using Storyline, Google Sheets, and random content feature.


Intro to Dynamic Content

The reason people love dynamic Alexa skills is that they are not dull. If you hear the same content every day from your Amazon Echo, you’re not going to continue using the skills after a few times.

There are a lot of use cases for skills with dynamic content. You can get facts about your favorite blogger, university, or even about Amazon. Alexa skills with dynamic content can help you choose a child name or give you a different quote every day.

For skill creators, adding dynamic content to their skills solves a big problem: it’s easier to add, delete, and manage your content in a convenient interface. Google Sheets is an excellent example of such an interface.

The way creators used to make dynamic Amazon Alexa skills in Storyline was to create a server, put content there, connect it with Storyline using API Request feature, and write a function to randomize responses from that server. This process was clunky and hard to follow. Here’s how to make it easy.

Step 1: Create Your Sheet With Data

Let’s imagine I want to create an Alexa skill that shares a random fact about Amazon. The first thing I need to do is to find content.

After I find some facts about Amazon, I paste them into a spreadsheet.

Step 2: Convert Your Spreadsheet Into a JSON

To send your data from Google Sheets to Storyline you need to convert it to JSON — a lightweight format for transferring data.

Here’s how Storyline API Request feature works: you make a GET request to some server, it returns a JSON. Then you take some data out of it, and Alexa pronounces that data to the user.

We are going to use native Google Sheets feature to convert the spreadsheet to JSON.

First, you need to publish your spreadsheet to the web, using File -> Publish To Web in your spreadsheet.

After you publish your spreadsheet, you need to get your spreadsheet ID. It is a part of your spreadsheet URL. In our example, the URL of the spreadsheet is:

https://docs.google.com/spreadsheets/d/1GVfUWL3iZEGJ6YJfycLXzqTXKB2UFl65bjopI8LczS8/edit#gid=0

From this URL, spreadsheet ID is:

1GVfUWL3iZEGJ6YJfycLXzqTXKB2UFl65bjopI8LczS8

Now you can access your simple JSON using the following URL:

https://spreadsheets.google.com/feeds/list/SPREADSHEET_ID/od6/public/basic?alt=json

All you need to do is to replace SPREADSHEET_ID with your real ID. Here’s mine:

https://spreadsheets.google.com/feeds/list/1GVfUWL3iZEGJ6YJfycLXzqTXKB2UFl65bjopI8LczS8/od6/public/basic?alt=json

If you follow that link we created below, you’ll see a real JSON with data from your spreadsheet.

But narrow data is hard to read and understand. Let’s beautify it a little bit. To make JSON readable, you can use beautifyjson.com or JSON Viewer chrome extension.

Here’s how it looks with JSON Viewer extension installed:

Step 3: Create an Alexa Skill and Access Your JSON Using Storyline API Requests

After you created your JSON, you need to create an Alexa skill in Storyline to be able to receive it. My example about Amazon looks like this.

On this screenshot, @api.spreadhsheet_with_facts is my API request to Google Sheets and {{random_fact}} is a variable I created to store the fact.

To make this work you need to create corresponding API request using API requests feature.

Here’s a screenshot of API request in my skill:

URL here is the same URL to JSON format of my spreadsheet I created during the Step 2:

https://spreadsheets.google.com/feeds/list/1GVfUWL3iZEGJ6YJfycLXzqTXKB2UFl65bjopI8LczS8/od6/public/basic?alt=json

Response handling rules field contains a path to a fact from our spreadsheet JSON and a variable, where we store that fact to use it in Storyline.

random_fact=api_response.feed.entry.0.title.$t

Here’s what’s inside of it:

random_fact is a variable that stores the fact we grab from the Google sheet. We are going to use it in the diagram of our skill.

api_response is an operator that helps Storyline understand that this is an API response.

feed.entry contains the content or our spreadsheet. In other words, cells.

When you’re constructing this line, you’re going down the JSON structure and separating each JSON entry with a dot. If you’re using JSON Viewer extension, you can toggle JSON elements to see the structure in a clear way.

Here’s how it looks if you toggle all fields of a JSON:

Then we can toggle entry field and go one level deeper. Entry here is an array of objects, and each object looks like this:

The field we need is $t, which is located in thetitle.

Here’s how the whole line looks like combined.

api_response.feed.entry.0.title.$t

If you have any questions on this part, please feel free to ask them in our Storyline Community of Alexa skills developers.

At this part, you should already have a working Alexa skill that grabs the first fact from your spreadsheet and pronounces it in Storyline. The next step is to make it dynamic.

Step 4: Randomize Facts in Your Amazon Alexa Skill

To access the first element of your spreadsheet, you use .0 key. To access the second cell you use .1.

To call one element from a column randomly, you need to write .random instead of .0.

The whole response handling line who grabbing a random fact from a spreadsheet looks like this:

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

By doing this, you make Storyline understand that you want to access a random element of that array.

If you followed the tutorial, at this moment, you should have an Alexa skill that grabs a random fact about Amazon from a spreadsheet and uses it in Storyline.

You can play with it right in the browser or publish to your Echo device (and Amazon App Store, for sure).


Thanks for reading! 🙌

I look forward to hearing your thoughts. Let’s keep the conversation going, you can connect with me on Facebook or join our community for Alexa Skills creators.