• Sridhar Rajendran

Chatfuel tutorial to save and read information from Google Sheets using Integromat

Updated: Aug 23, 2018

Almost every chatbot requires saving some information from the user for performing some function. Ideally some database management software (DBMS) like MySQL, SQLServer, etc is used in both desktop software and mobile applications. While DBMS is quite powerful and allows users to manipulate information in myriad ways, it may be an overkill for simple apps like chatbots. Also for small businesses developing chatbots for customer service, it adds additional licensing fee. Google Sheets (or Airtable) is a viable alternative for such scenarios.

In traditional sense, a database is nothing more than a table of information. The rows are called records and columns are called fields. Each record corresponds to a particular entity such as a customer. Each column in a record is an attribute related to the entity. Each record needs a unique identifier to identify the correct record. This is called a primary key in DBMS.

We can setup a database to save customer details in Google Sheets like below. Each row contains information related to a customer. Here the Customer IDis a unique identifier. So it will be the primary key.

To pass the information from Facebook Messenger bot to Google sheets, we will use a service called Integromat. It is an online automation tool that connects various apps and services and enables users to automate manual tasks with little to no coding. eg: If someone mentions my twitter handle, send me an email. If you do not have an account, sign up and create one for free.

3. By default Chatfuel populates the bot template with certain blocks. For the purpose of this tutorial we will not be using any of it. So feel free to delete everything except the block ‘Welcome message’ and ‘Default answer’.

4. Rename the bot with a meaningful name like ‘Save and read data from Google Sheets’.

5. Modify the ‘Welcome message’ block as shown below.

6. Modify the ‘Default answer’ block as shown below. (Feel free to play around with this depending on the tone and purpose of your bot)

7. Go to ‘Save data’ block. Enter the questions you want to ask your user and save it to a variable respectively.

8. Add a JSON API plugin to send this information to Integromat. (Check this article to know more about JSON). Change the TYPE to ‘POST’ as it is more secure than ‘GET’. Select the attributes that you want to save in Google Sheets. {{messenger user id}} will be a unique id for each person interacting with the chatbot and can be used as the primary key.

Leave the URL field blank for now. We will get it from Integromat.

9. Login to your Integromat account and click on ‘Create a new scenario’. Each automation we create is referred to as a scenario.

10. Choose ‘Webhooks’ and click Next. (Check this article to know more about Webhooks)

This is the screen where we can edit the scenario.

11. Rename the scenario to something like ‘Save data to Google Sheets from Chatfuel’. Click on the big question mark bubble and you will get the below screen. Choose the option ‘Custom webhook’.

12. Click on the Add button. Enter a name for the webhook and Save.

13. Integromat now generates a custom URL for this webhook. We will use this to send the data from Messenger bot. Copy the address to clipboard.

14. Paste the webhook url in the JSON API plugin in Chatfuel.

15. Now its time to test if the link is working fine. Click on ‘Test this chatbot’ and open in Messenger.

In Integromat, click on ‘Run once’ in the scenario we created.

The webhook is triggered and is waiting for input.

In Messenger, click on Save data and enter some details. Ignore the error for now as we have not returned any value from Integromat.

In Integromat, the status of the operation shows successful. Click on the magnifying glass icon and observe that the data sent from Messenger has been received correctly.

16. The next step is to save this data. Hover the mouse over the Webhooks module and menu ‘Add another module’ pops up. Click on it.

Choose Google Sheets.

Choose an operation — ‘Add a row’.

Link your Google account with Integromat. If it is already linked, it will show up in the list. Or else click on Add and enter your credentials.

Choose the file we created earlier — ‘Chatfuel test’ and the corresponding worksheet. Map the information received in the webhook to the corresponding column in the sheet.

17. Add a ‘Webhook response’ module (Webhook -> Webhook response) after Google Sheets to inform Chatfuel that action is completed.

The Body section contains the JSON object that will be sent to Chatfuel. The format should be in a format that can be understood by Chatfuel. Refer this document for the accepted response types.

Here, we will return a simple text message. You can modify this section depending on your application.

18. The scenario is complete. Schedule it by clicking on the lightning icon in Webhook module (added at the beginning) and select the option Immediately. Ensure that the ‘SCHEDULING’ toggle option at the bottom is turned ON.

The complete scenario looks as below.

19. The setup is complete. Test the chatbot. We receive a confirmation message ‘Data saved’ instead of the JSON error observed earlier.

The information is updated in the Google sheet.

19. Let’s create another scenario for reading the stored data from Google Sheets.

Module 1: Refer to step # 11 for creating the Webhook.

Module 2: Refer to step # 16 for connecting with Google Sheets. Here instead of choosing the option ‘Add a row’, you will select ‘Select rows’. In order to filter the records corresponding to a specific user, specify the condition in the field ‘Filtering’.

Each column in the sheet is referred by a name assigned by Integromat. For instance the column ‘Customer ID’ is referred as customerid. You can see this mapping from the drop down list ‘Order by’.

There is also an option to limit the number of rows returned for the matching filter. By default it is 5. If more than 5 matching rows are found, then the first 5 will be returned.

Module 3: A text aggregator module is used to combine the records returned by Google Sheets. The Text field specifies the format in which the information is to be packaged. For now, let us return all the details about a user as a simple text message. The ‘\n’ is called a newline character i.e the text following it will be displayed in the next time.

Module 4: Refer to step # 11 for creating the Webhook response.

The output of the text aggregator is returned to Chatfuel as a JSON object.

20. Enable the scenario and test the chatbot.

Yay! You have successfully learnt to store and retrieve data from a Google Sheet. You can improvise this by using different JSON plugins supported by Chatfuel.

Suppose this table has more than one record for the same customer ID, then the bot would display.

The steps described above are similar for saving and reading data from Airtable as well. Use the Airtable block instead of Google Sheets block and it should work fine.