How to search a spreadsheet with a chatbot

Chatbots are tools that communicate with users through text messages with which it is possible to have a conversation, whether we want to ask for some type of information, or carry out more complex processes.

A chatbot that can lookup tables and spreadsheets will allow you to quickly find rows in tables. It will search a row by a key and respond with the value matching that key on the table.

How to Search a Spreadsheet

To search a spreadsheet you will need to add the Spreadsheet component in the visual editor. Press Advanced>Spreadsheet to insert the component in your conversation.

You can search 3 types of spreadsheets, CSV, Excel and Google Sheets. CSV and Excel files can be uploaded into the component or you can also use a public URL that links to those files.

Google Sheets only allows you to search the spreadsheet by using a public link generated by the online app.

To create the link in Google Sheets, press "Share", make the file public and copy the resulting url into the component.

Table Format

The chatbot will only be allowed to search a single table file. It will not search files with multiple sheet so have that in mind.

Valid Table example

Key Value Result
peter@gmail.com 3424 yes
john@gmail.com 6546 yes
michael@gmail.com 1233 no
steve@gmail.com 56767 no
eric@gmail.com 678678 yes

Reading a File

The first step will require you to validate the spreadsheet. After entering the URL or uploading the file, press "Load".

The editor will alert you if the file could not be read, or in the case it is a valid file, it will open additional options where you can configure your search.

Searching for keys

First you need to select the key you will be searching in the spreadsheet. To store keys and user responses use the "Input" component. 

For example send the message "what is your email", and follow that component with "Input". The user response will be stored in that component. Only then you can use it as a variable in your conversation. More information at Global Variables.

Remember to ask for the key before creating the Spreadsheet component, otherwise you will not be able to use that key.

Selecting the key and columns

The next step will require you to select the key you will be searching for by choosing the appropriate variable. 

You will also need to select the key column and the value column. The chatbot will respond with the matching value column row for that key.

Press Create to Finish.

For the table above, take this example. You want the user to enter his email to check if he has due payments or not. Have the chatbot ask for the email address, look for the email in the "Key" Column and return the value on the "Result" column. 

Note: The response is optional, you can choose "No Response" to do simple lookup without returning any value.

Search Result

After you have created the component, you can tell the chatbot what to do in case a match is found or not. 

For example you can ask the user to repeat his email or direct him to an agent in case his key is not found. Or you can filter users by only allowing those which were found to continue.

Would you like to know more?

Get more information