Integrate Google Sheets

The Google Sheets integration enables seamless data exchange— send data, get data, get a random row, and generate dynamic galleries from Google Sheet .

Video Tutorial here.

Step 1: Go to Menu > Settings

Step 3: Look for the Google Sheets Integration field and click the Connect button.

Step 4: From here, you need to choose the account associated with the Google Sheet.

Step 5: Once you connected your Google Sheet, you will get the integration window like this image below.

*Note that you can disconnect the Google Sheets with ChatBot Builder anytime by pressing the button.

Now, you will learn how to use the Google Sheets integration.

To be able to use the Google Sheets integration you need to use this in a flow.

From your menu go to Flows. Once you arrive at the flows overview press the top right button Add Flow.

Once you reach the flow editor you can press the message block that you start with select Action > Google Sheets. Take a look below on how to find the Google Sheets integration.

There are several options to choose from when selecting the Google Sheet integration. It all depends on your needs. Below are the options we currently support:

• Send Data to Google Sheets
• Get Row(Value) from Google Sheets
• Update Google Sheet Row
• Clear Row(Value) on Google Sheets
• Get Random Row in Google Sheets
• Create Dynamic Gallery with Google Sheets

Before using any of the above actions you need to create a Google Sheet with some preset information.

Suppose you want to gather subscriber data such as name, email, and phone number and then export it to a Google Sheet. In that case, we need to set up a few headers within the Google Sheet to organize and export the data.

Create a Google sheet like “Subscriber’s Data”.

Then create the Headers for each Column. We will use Full Name, User ID, Email and Phone Number. You will be required to create Custom Fields to store the data that you need to send/get from Google Sheets.

Now that we prepared the Google Sheet it is time to export the data to this sheet from our bot.

Whenever you connect your Google Sheet with ChatBot Builder  you need to take into account that whenever you change the name of the worksheet or spreadsheet you need to redo that Google Sheet action.

The integration is configured to operate with the sheet's name rather than its ID.

Any changes to this may result in the integration ceasing to function.

So if you change either name keep in mind that you need to redo the Google Sheet action inside of your flow builder.

Once you collected all the information, we can use the Google sheets action Send Data.

Choose the action (Send data to Google Sheets) and select the name of the spreadsheet that you want to export the data to. The next step is selecting the worksheet.

Now match the ChatBot Builder   bot data you collected to the Google column titles. You can select any of the custom fields, subscriber's data, or bot data. It is all available to you to export.

Once the subscriber has gone through your flow and you used the Google Sheet action Send Data at the end of that flow, all the data should nicely have been exported like this.

Really easy and simple to do right?

Instead of exporting of data to a Google Sheet, we can also do it the other way around. Getting data from a Google Sheet and display that information into the bot for the subscriber to see.

Let’s say your client has a food truck and changes location a few times a week. He wants to be able to give his current location to his customers when they ask for the location. Instead of having to update the bot every single time you can just let your client update his Google Sheet instead.

Every time he changes location, he just updates that into the Google Sheet and the bot will fetch the most recent location.

To be able to do this you have to create two botfields. One botfield is used as a lookup value inside the sheet, while the other will store the location of that food truck. In this case, we will create:

• Current Location
• Current Address

The Current Location botfield will need to have the value that is the same in the likewise named column in your Google Sheet. So in this case we give the value same as the botfield “Current Location“

Once you created this, create your Google sheet with the same titles and fill in the information. It should look like this;

Then all you need to do inside your flow builder is to go to Google Sheets > Get Row > Select sheet > Select Worksheet > Select lookup column > Map data from sheets to bot.

The lookup column will be the current location as this value stays constant. So you choose that columns and set the search to must be equal to. Here you insert your botfield Current Location.

Like the example below

Now your client has the most recent location to his food truck inside the bot without touching it. The perfect solution!

Similar to the getting row data, we can also get a random row presented to us instead. This is great for generating dynamic combinations.

An example could be when creating a bot to provide cat & dog names for pets.

We will have a Google Sheet with 3 columns:

• Value Lookup
• Cat Names
• Dog Names

That looks like this;

So, for this to work we will ask a subscriber to choose if they would like to hear a Dog’s name or that of a Cat. We will use a user input block for this so we can store the value to a custom field. This will allow us to pick a random name from either the Dog’s column or that of the Cat's.

The next step is the determine the lookup value. For this, we will use the column Lookup Value. because we want to generate a random answer/value, we will now need to give it a range to search the sheet for.

That will look something like this;

From here the answer is saved to the subscriber's custom field and you can display it directly inside of the conversation.

Another great action with the Google Sheets integration is the ability to update a row if you already have values stored. Let’s say you want to provide a subscribers list for your client as an easy way to for him to have a good overview of his subscribers.

So for new subscribers, you would have exported some data like Name, User Id.

Now, when they opt-in for a lead magnet or loyalty program, you might want to capture their email and/or phone number as well. Instead of making a new row with this information, you can just update the existing one you have for that subscriber.

So let’s take a look at subscriber data with basic information.

As you can see, right now we only have their Name and User ID. The User ID is crucial because it helps us find and update the subscriber's data. This column stays the same because each user ID is unique to a subscriber.

So, after you captured the additional subscriber’s data like phone number and email, we can select the Google Sheets action Update Row.

This way you will always have your Google Sheet up to date with the most recent information gathered from your bot.

It will fill in or update the information that wasn’t there before.

Similar to updating a row, the process of clearing a row in Google Sheets follows a similar path. This can be handy, for instance, when you want to remove subscribers who have unsubscribed from your bot.

You choose the action Google Sheets > Clear Row. The same principle applies. You select your spreadsheet and your worksheet. Then choose your Lookup Column. In this case, as mentioned before that will be the user id since this one remains constant.

This feature has been updated and simplified while even providing much more powerful features. You can find the documentation about dynamic galleries HERE.

ChatBot Builder Google Sheet integration provides extensive support for automating the import and export of your data with Google Sheets. Offering flexibility, it continues to evolve with regular feature updates. In case of any additions, this documentation will be promptly updated, ensuring you grasp new features effortlessly for seamless implementation.