Google Sheets Integration

How to use Voiceflow's API Integration with Google Sheets.

Video walkthrough is here.​

In this article we will walk through how to use Google Sheets with the Integration block in Voiceflow. If you're unfamiliar with sheets, or don't have a G Suite account, check out Google's tutorial here.

In this article, we will cover:

  • How to connect a G Suite account for sheets integration

  • Tracking the number of times a user has opened your skill (sessions)

  • How to capture a user's name and Amazon ID into a variable, to be referenced again in the future

  • How to upload and test on ADC

Create a Spreadsheet

Let's start by creating a new file on https://docs.google.com/spreadsheets​

In the top of the screen, select the "Blank" template to begin.​​

Name your sheet! for this tutorial, we chose "Users," but you can name the sheet anything you'd like.

Add three headers to your sheet: ID, Name and Sessions

Keep this page open and go to Voiceflow to create a new project.

For the purpose of this example, we will create a skill that capture's the users' Amazon ID, name, and number of sessions, and put that information into our Google sheet.

Using the Integrations Block

In your new project, drag and drop the Integration block from the Advanced section to the canvas.

Select Google Sheets for the integration choice.

We are going to create a new record for the user, so select Create Data

Click on + Add User

And login with your Google account

Select which account you'd like to sign in with.

On the next popup window, click on Allow to link Voiceflow to Google Sheets.

On the next window, click on Confirm

Then, select the spreadsheet (Users) and the sheet you've just created (Sheet1 by default)

In the With values step, use the Voiceflow variable {user_id} for ID (0) and {sessions} for Sessions (2)

You can leave the Name (1) field blank for now.

​

On your project's canvas, add a speak block with a welcome message and a prompt to ask the user's name.​

​​

Go to the Voiceflow Variables tab and add the name variable. We will use this variable with the Capture block to get the user's name.

​

​

Finally, add a Capture block with the US_FIRST_NAME as Input Type and select your name variable in the Capture Input To field.

Please note that US_First_Name only works if you have the US, CA, or UK locales selected.

Go back to the Integration block and add the new name variable in the Name (1) field.

You can also rename the block to Create User for example.

​​

We are now ready to test it so click on the Test Integration button

​​

On the next window, fill the fields with some test values

And hit the Run button

You should have this response if everything went well

And your spreadsheet now have a new row with the test values

This is great but if we run this skill multiple times, you will have a new record for each session.

So what about updating the user's record instead of creating a new one each time?

As we have already seen, we ask for the user's name at the beginning of our skill, so why not check if we already have a name for this user at each launch and if so, update the number of sessions for this user?

Let's use the If block to do just that

​​​​

​

​Here we do a simple If to check that our name variable is equal to zero (If we don't have the user's name yet).

Our project looks like this now. If the name is 0 the If block will use the 1 port, if we already have the user's name we are going the else port.

​​

Now that we've created the user, we can move on to updating the data.

Let's add two new Integration blocks to our canvas. We've renamed the first Retrieve User and the second Update User.

​​

Before continuing, let's take a look at how the Integration block works for the update.

In the Update User Integration block, select Update Data for I want to, your account in As user and the Users spreadsheet and the Sheet1.

​

​​

As you can see, to perform an update we will need a row number. So we will have to find the row number corresponding to the user's record in the spreadsheet..

We will use another Integration block but this time we will do a search with the user id, as this number is unique, we are sure to find the corresponding record.

So in the Retrieve User Integration block, I want to Retrieve Data, Using sheet Sheet1 With settings ID (0) = {user_id}

​​

Before going further, we will add the user_row variable to store the result of our search.

​​

We can now map the Row Number to the user_row variable.

Let's finally go back to the settings of the Update User Integration block to complete its configuration.

Fill in the fields as below and click on Next.

​

​​

Finally, let's add a speak block to welcome back our user with their name.

​​

And two speak blocks at the end to confirm the creation or the update.

​​​​

Here is what your project should look like now.

​​

Uploading to ADC and Testing

You can now upload your project to Alexa.

And test it in the Alexa Developer Console (ADC) simulator by clicking on the link in the confirmation message or directly on your device.

​​​​

Here is the result in the spreadsheet after the creation of a user after the first launch of your skill.

​​

And here is the result after the second launch

​​​​

So, as you can see, we checked if we already had the user's name, we greeted him with his name and finally, updated the number of sessions for this user.

With what you have learned, you can now store and update user data as well as create your own analytics tool.