Using Google Sheets in Flow XO, Part 2

In Part 1 of this tutorial, we covered how to use Flow XO to search, add, and update information in Google Sheets. This tutorial will show you how you can use Google Sheets with even more features in Flow XO.

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

In the last tutorial, you created a mailing list bot for a bakery. You set up a flow to collect and verify contact information from your customers. Now you can build on your bot. In this tutorial, you’ll learn a time-saving way to use Google Sheets when you respond to grouped words or phrases. You’ll also learn how to use Google Sheets to make a card set that will be quick to update later.

Respond to Grouped Words or Phrases

In Flow XO, you can use groups to categorize words or phrases you might expect from users. You can then account for variable wording with a single response.

For example, this New Message trigger accounts for different ways users might send a greeting, request hours, or ask for a phone number. The bracketed word next to each message is the group:

With Google Sheets, you can set up a spreadsheet that contains a response to each group. This can save you time when you set up your flow. Instead of creating a new action for every response, you can create just two actions: one to search your spreadsheet and one to send the response.

Set Up Responses in Google Sheets

Remember, always set up your spreadsheet before you use it in your flow. Flow XO needs an existing spreadsheet with a header row as a reference point.

Follow these steps to create a spreadsheet that contains a response to each group in your trigger.

  1. In Google Sheets, create a new spreadsheet and give it a clear name.
  2. Give your spreadsheet a header row that labels the contents of each column. In this case, label the columns Question and Answer.
  3. In the Question column, list the exact group names that you’ll use in the trigger for your flow.
  4. In the Answer column, type your response to each group. Your spreadsheet should look like this:

All of your responses are now in one place. Later, if you decide to add more groups and responses, you only need to update the trigger and the spreadsheet. Because of what you’ll do in the next steps, any new responses won’t require new actions in your flow.

Search Rows for a Response

By now, you should have a trigger with grouped words or phrases and a spreadsheet with corresponding questions and answers. Now you’ll want your bot to respond to the user with an answer from your spreadsheet.

First, you need your bot to search your spreadsheet for the group that corresponds to the user’s input. For instance, “hello” is in the [greeting] group. If the user says “hello”, your bot should search for “greeting” in your spreadsheet. Here’s how you can do this:

  1. In your flow, add an action. Select Google Sheets → Search Rows, then click Next.
  2. Your Google Sheets account should already be authorized (we covered this in the last tutorial). Click Next.
  3. In the Settings window, select the Spreadsheet and Worksheet you want your bot to search.
  4. Select the Column you want your bot to search. Remember that the options in this menu match the header row of your spreadsheet. Choose the column that contains your group names, in this case, the Question column.
  5. In the Value field, tell your bot what to look for. You want the bot to take the group from the trigger output and find it in your spreadsheet. In the Value field, type {{, find your trigger in the drop-down, and select Group below it.

Your Settings window should look something like this:

Click Next, then finish and save the action. When this action runs, your bot will search the Question column of your spreadsheet and find the group that matches the trigger output.

Send a Message

Next, your bot should display the Answer on your spreadsheet that corresponds to the Question it found in the last step. To do this:

  1. In your flow, add an action. Select Bot → Send a Message, and click Next.
  2. Instead of typing a message in the Message box, tell your bot to look back at the output from your Search Rows action. Then have it display the contents of the corresponding Answer column.

Here’s what the Send a Message window should look like:

Your bot will send a response from your spreadsheet that depends on the trigger output. So, if the user sends a greeting, your bot will reply with the text in the greeting row, Answer column.

With this setup, your bot can respond to any input from the user with just two actions. As long as there is a group in the trigger and matching group in your spreadsheet, your bot will always have the right response.

Send Cards with Google Sheets

In Flow XO, you can also use Google Sheets to send a card set to users. With cards, your bot can send links and captioned images. If you want to change card content regularly, it can be helpful to keep data in a spreadsheet rather than editing your flow directly.

For example, you might want your bakery bot to display weekly specials as a card set. You can set up your flow once, and change the specials each week in Google Sheets.

Set Up Cards in Google Sheets

The first step is to set up a spreadsheet for cards. You’ll input the same data you would in a normal card set. But, instead of putting it directly in your flow, you’ll put it in a spreadsheet. To do this:

  1. Create a new spreadsheet in Google Sheets and give it a clear name.
  2. Add a header row to your spreadsheet. In this example, we’ll use the headings, Special, Price, and Image.
  3. Add content to your spreadsheet. Describe each Special and add a Price. In the Image column, paste the public URL to the image you want to display:

The next step is to log this data so you can use it in your flow.

List Rows

To log your spreadsheet data, use the List Rows action in your flow. This action will read and log the entire spreadsheet so that you can use all of the data in later actions. Here’s how you can set this up:

  1. In your flow, add an action. Select Google Sheets → List Rows, then click Next.
  2. In the Settings window, select the Spreadsheet and Worksheet you want your bot to log. Because this action reads the entire spreadsheet, there’s no need to specify any rows:

When this action runs, your bot will read all of the data in the spreadsheet. You’ll use this data in the next action.

Send a Card Set

Now you can set up a card set. You’ll use the output from the List Rows action in the fields for each card. To do this:

  1. In your flow, add an action. Select Bot → Send a Card Set, and click Next.
  2. For the first card, use the data from the first row of your column. You’ll use collection outputs to signal the row you want to use.

In the Card Title field, type {{. Then, in the drop-down menu, find the output from your List Rows action. Select the column that you want to take the card title from, in this case Special:

As soon as you select a column, the Special Modifier window will pop up. Here is where collection outputs come in. In this case, your spreadsheet is the collection.

  • For the first card, you want your bot to look in the first row of the spreadsheet. The first row is item 1 of the collection.
  • So, type 1 in the Special Modifier window. This will tell your bot to only use the data from the first row:
Note: If you leave the Special Modifier blank, it will default to 1. You can either type “1” or leave a blank for the first row. You must specify a collection output for all other rows.

When you press Enter, your first Card Title will look like this:

Complete the rest of the fields in the same way. Use your Price column in the Card Text field and your Image column in the Card Image URL field:

To add the next card, click + Add Card. For the second card, follow the same steps but change the collection output to 2. For the third card, change the collection output to 3.

Now your bot can read the data on your spreadsheet and send it to the user as a card set.

Conclusion

In Flow XO, Google Sheets can help you save time when you create a flow and when you update it later. In addition to collecting data, you can use Google Sheets to respond to users and even send images.

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.