How to Create an Interactive FAQ Chatbot from Google Sheet

Friday, September 28, 2018

Two weeks ago I wrote about creating an event chatbot from Google Calendar. This article is the second part, where we will be adding some content from Google Sheet to make it an interactive FAQ chatbot.

Spreadsheet-Powered Event Chatbot

ProBotDev can create a chatbot for your events from Google Calendar, but the information you can put inside a calendar event is limited: name, time, venue, and some description. How about the list of speakers? The logo? Toilet location? That’s where Google Sheet comes in.

ProBotDev Event Chatbot uses the information you put inside Google Sheet to answer inquiries. Sort of like FAQ, but interactive. For this to work, you need to structure the spreadsheet in a certain way. I have created a template that you can use—just create a copy and edit it.

The Structure

As you can see from the template, there are three tabs (sheets): Events, Speakers, and FAQ. Inside, there are some predefined columns. Note that the label of the sheets and the columns must not be changed.

Also, keep in mind that everything will be imported as plain text because most messaging platforms don’t support text formatting, and you can’t use any formula other than “=IMAGE()”.

The “Events” Sheet

The Events sheet.

The Events sheet.

This sheet is where you put extra information that can’t be set in Google Calendar. At the moment, ProBotDev Event Chatbot only takes a hero image.

This sheet has two columns: Name and Hero Image. The name must be the exact match—but case-insensitive—with the one you put in Google Calendar. For example, if you put “Awesome Event” there, putting “AWESOME event” here will work, but simply “AWESOME” won’t.

As for the image, put it using this formula:

=image("THE_URL")

For example:

=image("https://i.imgur.com/VfJ3gTQ.png")

It doesn’t matter where you host the image as long as it’s accessible—verify it by opening the link in private/incognito mode in your browser.

The “Speakers” Sheet

The Speakers sheet.

The Speakers sheet.

This sheet has 4 columns: “Name”, “Portrait”, “Bio”, and “Website”. The name must match the name (including any symbol) you put in Google Calendar. The bio can be up to 2,000 characters, but remember that most people will use your Event Chatbot from their phone, so shorter is better. Everything (except the name, obviously) is optional.

The “FAQ” Sheet

The FAQ sheet.

The FAQ sheet.

This sheet has three columns: Event Name, Question, and Answer. For the event name, you can put “*” (star, without the quotes) which basically means, “all events.” You don’t need to repeat the event name; subsequent rows will assume the same name.

The question column contains the “keys.” For example, when someone asks, “Where is this event?”, ProBotDev knows it’s ABOUT_EVENT, and will check your spreadsheet for the answer.

If the sheet has empty answer for the key (or simply doesn’t exist at all), ProBotDev will try to find the fallback answer—the answer you put “*“. If it can’t find the fallback answer, it will say that it is not programmed to answer the question.

Setting Up ProBotDev Event Chatbot

Once the spreadsheet is ready (you can always refine it later, ProBotDev will auto-sync), head to ProBotDev and open the project, and select the Sheet.

If you’re still confused, just open the template and create a copy, then use it as-is with the event chatbot from Google Calendar you created last time.

Event Chatbot from a spreadsheet.

Event Chatbot from a spreadsheet.

Interactive FAQ chatbot.

Interactive FAQ chatbot.