Using Google Sheets in Flow XO

In Flow XO, your bot can read and edit spreadsheet data in Google Sheets. In this tutorial, you’ll get an overview of Google Sheets. You’ll also learn how to use a bot to search for information in a spreadsheet, then update it if necessary.

If you don’t already have a free Flow XO account, get yours now.

For example, say you own a bakery, and periodically email your customers with news and deals. In Flow XO, Google Sheets is a good way to collect contact information. When a customer interacts with your bot for the first time, you can get their email address. Later, you might want to verify and update the email if necessary.

About Google Sheets

Google Sheets is a free spreadsheet tool that you can access here: https://drive.google.com/. If you don’t have a Google account, it’s free to sign up for one.

When you have your account, go to Google Drive and create a new spreadsheet. Click New → Google Sheets.

Later, you’ll authorize your Google Sheets account in Flow XO. Your bot can then access spreadsheets that you create in Google Sheets.

Google Sheets Setup

It’s important to set up any spreadsheets you want to use before you create your flow. Your bot will use the header row and other content as a reference point when it interacts with your spreadsheet.

In this example, you want to gather customer contact information in Google Sheets. Create a new spreadsheet, then give it a clear title and a header row:

The header row is the first row of the spreadsheet. It specifies the data that will go in each column. In this case, we want to collect each customer’s Name and Email.

Authorize Google Sheets

When you use Google Sheets in your flow for the first time, you’ll need to authorize your account. To do this:

1. Sign in to your Google account.
2. In your flow, add an action and select Google Sheets → Search Rows. Then click Next.
3. In the Authorize the Service window, select Connect a new account. Then click Authorize.
4. When the Google Accounts window opens, choose the account you want to authorize.

The next time you use Google Sheets in a flow, Flow XO will connect to your authorized account.

Search Rows

After you set up your spreadsheet, you can interact with it in your flow. One useful interaction is to Search Rows for data.

In this tutorial, you’ve created a spreadsheet to collect customer names and email addresses for your bakery mailing list. Before you add a customer to the spreadsheet, it’s a good idea to check if they’re already on the list. This will help you avoid redundant data.

If you followed the steps above, under Authorize Google Sheets, you should have the start of a Search Rows action.

From the Authorize the Service window, click Next, then complete the Settings window. This window tells your bot what spreadsheet to search in and what value to search for:

• Spreadsheet: Select the spreadsheet you want your bot to search.
• Worksheet: Select the worksheet you want to use. If your spreadsheet has several worksheets, they will all be listed here. In this case, there’s only one worksheet, so select Sheet1.
• Column: This field takes content from the header row in your spreadsheet. Select the header for the column that you want to search. For this example, tell your bot to search the Name column to see if the customer name is already on the list.
• Value: Enter the value you’re searching for. This can be a plain text value, or you can use an output from your flow. Here, the value {{bakery_bot.user_name}} tells the bot to search for text that matches the user name.

Note: Your bot will capture the user name for you on most platforms. To access it, type {{, then select User Name:

Click Next to finish and save your action. The next step is to create another action that depends on whether your bot finds a match.

Filtering Search Results

Once you have your search results, you can use a filter to tell your bot what to do next.

For your mailing list, you might want to do two different actions, depending on search results:

• If the user is not on the the mailing list, ask for an email address.
• If the user is on the mailing list, verify that the email address on record is correct.

First, set up an action for users who are not on the mailing list.

1. In your flow, add an action. Select Bot → Ask a Question.
2. Type a Question to ask for the user’s email address. Make sure to use the Email Address validation. Then click Next.
3. In the Filter window, create a filter to check if there are matching results from your Search Rows action.

For the filter, you want your bot to check the results count. Then, you only want it to do the action if there are no results. Your filter should look something like this:

This action will only run if there are no matching results from your Search Rows action.

For the second action, you’ll create a similar filter. But this time, you’ll only want it to run if there is a match. In other words, if the results count is 1, then do the second action. We’ll cover the second action later in this tutorial.

Add a Row

In Flow XO, your bot can add new data to Google Sheets. You can do this with the Add a Row action.

At this point, you’ve asked the user for their email address. Now you want to take that data and add it to your spreadsheet. To set this up:

1. In your flow, add an action and select Google Sheets → Add a Row. Then click Next.
2. At this point, you’ve authorized your Google account, so select Choose a connected account. Click Next.
3. In the Settings window, tell your bot what to add to the spreadsheet.

Your Settings window should look like this. The Name and Email fields correspond to the columns in your spreadsheet:

• Spreadsheet: Select the spreadsheet you are adding a row to.
• Worksheet: Select the worksheet to edit.
• Name: The value in this field will go in the Name column of your spreadsheet. In this example, the bot will grab the user name.
• Email: The value in this field will go in the Email column of your spreadsheet. Here we’ve told the bot to take the output from our last question, “What is your email address?” and add it to the spreadsheet.

When a new user enters an email address, your bot will add it to the spreadsheet:

Update a Row

Another useful feature of Flow XO is to update a row in your spreadsheet. If the user changes information, you can tell your bot to update it in Google Sheets.

At this point, you’ve handled new users who aren’t on the mailing list yet. For returning users, you might want to verify that their email is correct.

To do this, there are a few steps you need to follow in sequence.

1. Get a Row

Use Get a Row to tell your bot what spreadsheet row to read. Later you will use the output from this action to tell your bot what to update. To get a row:

1. In your flow, add a new action. Chose Google Sheets → Get a Row, and click Next.
2. Connect to your authorized account, and click Next.
3. In the Settings window, tell your bot what row to get.

• Spreadsheet: Select the spreadsheet you want your bot to look in.
• Worksheet: Select the worksheeet that contains the row you want to update.
• Row ID: You can use this field if you want your bot to get an absolute row (Row 3, for example). But, in this case, you want to your bot to get a row that will vary depending on who your bot is chatting with. So, leave this field blank. Use the other fields below it.
• Column: Select the column you want your bot to look in.
• Value: Enter the value you’re searching for. In this case, the bot will search for text that matches the user name.

You bot will log all data in the row that matches the user name. In this example, it will log the user name and email address. You can now use and update this data in your flow.

2. Ask a Question

The next step is to ask the user if the email address you have on record is correct.

1. In your flow, add an action. Select Bot → Ask a Question, and click Next.
2. In the Question field, ask the user to verify the email address on record. Use the output from the Get a Row action, and tell your bot to show the data that is in the Email column:

Now, add a filter so that this action will only run if the Search Rows action you set up earlier returns a match:

The user can signal whether the email address you have is correct.

3. Ask for the Correct Email

If the user signals that the email address is incorrect, you need to ask for the correct email:

1. In your flow, add an action. Select Bot → Ask a Question, and click Next.
2. Ask your user to enter the correct email address. Remember to set Validation to Email Address. Click Next.
3. Create a filter that will only run if the user answers “No” to the previous question. Then click Next and save the action:

Now you have the correct email address. You’ll use the output from this action in the last step.

4. Update a Row

Finally, your bot can update a row with the new email address.

1. In your flow, add an action. Select Google Sheets → Update a Row.
2. In the Settings window, tell your bot to look the row that you logged with the Get a Row action. Then specify what data to put in each column:

• Spreadsheet and Worksheet: Select the same spreadsheet and worksheet you used for the Get a Row action.
• Row ID: Tell your bot to use the same Row ID as the row that you logged with the Get a Row action.
• Name: Keep the original value in the Name column. Use the value that you logged with the Get a Row action.
• Email: Update the Email column with the output from your last question. This will update the spreadsheet with the new email address.

Add a filter to this action as well. It should only run if the user answers your request for a new email address:

Now your bot can verify and update an email address.

Conclusion

In this tutorial, you learned how to use Flow XO with Google Sheets. We covered the basics of reading and editing a spreadsheet, as well as using spreadsheet data in your flow. You can use Google Sheets with even more Flow XO features, like cards, attributes, and flow triggers.

To continue this tutorial on Google Sheets and learn more great ways to use Google Sheets with Flow XO, head over to part 2.

Want to find out more? We have full documentation for Google Sheets and other features in Flow XO over at our Help Center, or get help & advice from other Flow XO users on the Community Site.