Step 1 : Go to Menu >> Settings
Step 2 : Go to Settings and select Integrations in the top menu.
AStep 3 : Look for the Google Sheets Integration field and click the Connect button
Step 4 : From here, you need to select the account associated with the Google Sheet.
Step 5 : Once you have connected your Google Sheet, you will get the integration window like this image below.
*Note that you can disconnect Google Sheets with allchat at any time by clicking the button.
Google Sheets integration in action
Now, you’ll learn how to use the Google Sheets integration.
How to find Google Sheets actions
To be able to use the Google Sheets integration, you must use it in a flow.
From your menu go to Flows . Once you get to the Flows overview, click the top left Add Flow button.
Once you get to the flow editor you can click on the message block you start with > choose an action > Google sheets. See below for how to find the Google Sheets integration
What kind of actions does the Google Sheets integration have?
There are several options to choose from when choosing the Google Sheets integration. It all depends on your needs. Here are the options we currently support:
- Send data to Google Sheets
- Get Row (Value) from Google Sheets
- Update the Google Sheet row
- Clear a row (value) in Google Sheets
- Get a random row in Google Sheets
- Create a dynamic gallery with Google Sheets
Setting up your Google Sheet
Before using any of the above actions, you need to create a Google Sheet with predefined information.
Let’s say you want to capture the subscriber’s data like name, email and phone number. Then export it to a Google Sheet. To do this, we need to create some headers within the Google Sheet itself into which we can export the data.
Create a Google Sheet as “Subscription Data”.
Then create the headers for each column. We will use full name, user ID, email and phone number
Now that we’ve prepared the Google Sheet it’s time to export the data to that sheet from our Messenger bot.
Important fact!
Every time you connect your Google Sheet to allchat, you need to keep in mind that every time you change the name of the worksheet or spreadsheet, you need to redo that Google Sheet operation.
The combination is set to work with the sheet name instead of the ID. So changing this will cause the integration to stop working.
So if you change one of the names, remember that you have to redo the Google Sheet action inside your flow builder.
Send data to Google Sheets
Once you have collected all the information we can use the Google Sheets Send Data action .
Select the action (Send data to Google Sheets) and select the name of the spreadsheet you want to export the data to. The next step is choosing the worksheet.
Now match the allchat bot data you collected to the Google column headings. You can choose any of the custom fields, subscriber data or bot data. Everything is available for you to export.
Once the subscriber went through your flow and you used the Google Sheet Send Data action at the end of that flow, all the data should be exported nicely
Really easy and simple to do right?
get row(value)
Instead of exporting data to a Google Sheet, we can also do it the other way around. Getting data from a Google Sheet and displaying that information in a Messenger bot for the subscriber to see.
Let’s say your client has a food truck and changes locations a few times a week. He wants to be able to give his current location to his customers when they request the location. Instead of having to update the bot every time, you can just let your client update their Google Sheet instead.
Whenever he changes a location he simply updates it in the Google Sheet and the Messenger bot will fetch the latest location.
To be able to do this, you need to create two bot fields. One bot field is used as a lookup value within the sheet, while the other will store the location of the food truck. In this case, we will create:
- Current Location
- Current address
The current location bot field will need to contain the same value in the column of that name in your Google Sheet. So in this case we give the same value as the bot field ” current location “
Once you’ve created that, create your own Google Sheet with the same headers and fill in the information. It should look like this;
So all you need to do inside your flow builder is go to > Google Sheets > Get Row > Select Sheet > Select Sheet > Select Lookup Column > Map data from sheets to Messenger bot .
The lookup column will be the current position because this value remains constant. So you select that columns and define the lookup must be equal to. Here you enter your bot’s current location field .
As we do below
Now your customer has the latest location of their food truck inside the Messenger bot without touching it. The perfect solution!
Gets a random row
Similar to getting the row data, we can also present a random row instead. It’s great for creating dynamic combinations.
An example would be when creating a Messenger bot to provide baby boy and girl names.
We will have a Google Sheet with 3 columns:
- value check
- girls names
- boys names
It looks like this;
So for it to work we will ask the subscriber to choose if he would like to hear a boy’s name or a girl’s name. We’ll use a user input block for this so we can store the value in a custom field. This will allow us to choose a random name from either the boy’s or the girls’ column.
The next step is to determine the test value. For this, we will use the Lookup Value column . Since we want to generate a random answer/value, we will now need to give it a range to search in the sheet.
It will look something like this;
From here the answer is saved in the personalized field of the subscribers and you can display it directly within the messenger conversation.
Update the Google Sheets row
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 subscriber list for your client as an easy way to get a good overview of their subscribers.
So for new subscribers you would export some data like Name, UserId.
Now that they’re joining a leadmagnet loyalty program, you might want to capture their email or phone number as well. Instead of creating a new row with this information, you can simply update the row you already have for the same subscription.
So let’s look at subscription data with basic information
As you can see we currently only have their name and user ID. The user ID is important because this will be your check value when you want to update the subscription data. This is the column that will never change because the user ID is unique to that subscriber.
So after you have captured the additional subscriber’s data such as phone number and email, we can select the Google Sheets action Update Row .
This way you will always be up to date in your Google Sheet with the latest information collected from your Messenger bot.
This will fill in or update the information that wasn’t there before.
Clear a row in Google Sheet
Same as the update row, we can also clear a row in Google Sheets. It’s more or less the same process. For example, if you want to delete subscribers from your sheet who have unsubscribed from your Messenger bot.
You choose the action Google Sheets -> Clear row . The same principle applies. You choose your spreadsheet and worksheet. Then select your search column. In this case, as mentioned, it will be the user ID since it remains constant.
Create dynamic galleries
This feature has been updated and simplified while providing much more powerful features. You can find the documentation on this special new update here .
Summary
The allchat Google Sheet integration helps you in many ways to streamline the export and import of your data to and from Google Sheets. This gives you a lot of flexibility and more features are added on a regular basis.
If that happens this documentation will be updated to help you understand the new features so you can easily implement them.