Technical difficulty: Medium

What you'll learn:

  • How to store and publish data on Google sheets
  • How to use the Endpoint Tool to test if your requests are working
  • How to pull data from Google sheets randomly

Pulling from Google Sheets overview:

Voiceflow can use information stored in Google sheets to be used in your Alexa skill. To do this, you can use the Voiceflow API block to make an API request, and store the data in variables to be used throughout your skill. Don't worry if you don't know about APIs or how to do this yet - that's what this tutorial is for. 

If you want to learn more about APIs however, we wrote a piece on them here.

In this tutorial, we'll set up a list of countries in a Google sheets and use them in our skill. Towards the end of the article we will find out how to pull a random country from the list.

Video Tutorial:

STEP 1: Google sheets set up:

First let's set up our spreadsheet. We'll head over to:

https://docs.google.com/spreadsheets

and create a new spreadsheet.

Once we've started a new spreadsheet, we can fill it with data. For our example, I'm going to pull in a list of countries.

Now that we have our list of countries lets get our JSON so we can pass it into Voiceflow.

You don't have to know what JSON is to use this tutorial, but if you're curious and would like to know more you can find out here.

Thankfully Google makes it incredibly easy to do this. Just hit:

File ->  Publish to Web

Make sure to publish your entire document as a web page as shown above. 

Now our spreadsheet is published!

STEP 2: Change URL to JSON format

Now we need to change our URL to get this in JSON format. We're going to use this URL:

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

Make sure to replace the bolded "SPREADSHEET_ID" with your the ID of your spreadsheet. If we head over to the spreadsheet we created, we can find it as highlighted below:

The ID of your spreadsheet is the ID found on your original spreadsheet URL in the search bar. Copy the section highlighted below. If your URL does not follow this general format - you're not on the right tab!


Once we have this id, we can past it into the link and replace SPREADSHEET_ID:

Thanks to our handy test tool within the API block, we can easily see the JSON information we need from Google sheets.


We need to scroll down to see the information we need. The names of our countries are nested inside specific parts of the JSON. 

We now have two methods on how to retrieve the data that we want. 

Method 1: Copy tool

Head into the results you've received and to the right of the specific value, click on the clipboard icon. This will give you the exact value you need to input into your variable mappings.


Now that we've copied our value, we can paste it into our result variable mapping:

Method 2: Manual JSON entry


Above, we see one of our countries. In order to retrieve this bit of data, we're going to have to use the following query:

response.feed.entry.1.title.$t

If you do not know how to write JSON queries, you can read a post we did on JSON, and how to write queries here.

Because all the values in the spreadsheet are stored within the "entry" array, we need to specify the exact number we want. In our case here, we use the number 1 for our entry.

Then, we want to put in our JSON query for that particular GET request into the result variable mapping field. 


Displaying our data:

We want to create a variable to store the API's response to our query. Because we are asking for the name of a country, I created a variable called {country}. We are mapping the result of the API call into the variable {country}, meaning the result will get stored in that variable.

Above, we've created a speak block with our new {country} variable to test whether our operation is working correctly. Let's try it out in the test field below:

If we head back into our API test tool and hit the "Results" tab, we'll see whether our variable was mapped properly. Below we can see that the value we used was mapped to the variable called {countries}.

It looks like "Albania" was stored successfully in our variable.

And if we head into our test tool to see our skill working in action:

Success! Our API pulled the first country from this list as we wanted with our query.

Pulling random entries

In order to pull a random entry from our spreadsheet, it's as easy as replacing the number in our array from the previous query.

Earlier we wrote:

response.feed.entry[1].title.$t

This got us a specific entry in the list.

In order to get a random one, it's as easy as changing it to:

response.feed.entry[{random}].title.$t

All we changed above was the number [1] which got us the first entry, to {{random}} which gets us any entry in that category.

Now if we check out our project, we see that we are receiving a new country every time!

Try one...

Try two... 

We're receiving a new country every time. Success!

Did this answer your question?