Get from spreadsheet allows you to pull content from a Google Sheet or Excel spreadsheet into a bot. This particular functionality takes the content from your spreadsheet and presents it back to the user in the form of a carousel, when the user types in certain topics or search terms.
An employee wants to find out more about the pension plans, so they're using the company FAQ bot to find their answer. With the help of get from spreadsheet, the user is able to type in their topic (in this case "pension") and find a carousel of helpful content:
The get from spreadsheet message part is able to recognise the term typed in, then present relevant information back to the user. They can then use the carousel buttons to carry on through the bot flow.
• pull in content from a pre-existing spreadsheet to save time on bot building
• keep your bot updated in real time, by updating your spreadsheet
How it works
1) Create your spreadsheet
First things first, create the spreadsheet that will be connected up to your bot. You will need to create a spreadsheet containing two columns - I've named mine "Topics" and "Content", but you can choose any title. The topics act as a keyword, so when the user types any of the terms from the Topics column into the bot, they will receive a carousel containing the information from the Content column. Here is an example of what your spreadsheet should look like:
As you can see, the topics are laid out very similarly to how keywords are. You can add the same topic up to 9 times (this is the carousel card limit).
The content column will contain the info that will be presented in the carousel, more specifically in the title line.
2) Create your message
Now it's time to create the message that will contain the "Get from Spreadsheet" message part. This message part can be found under "External Spreadsheets" in the message part menu:
Once you have added your message part, type in the text that the user will see. In my example, I have prompted the user to type in a search term in order to find a response:
3) Connect up your spreadsheet
Click on "Click to configure" on the message part in order to launch the spreadsheet settings. You will first be asked to choose your connection. Please note that you will have to create a new connection with Microsoft for each bot.
You can also manage your connections in the settings page of the platform. You can find out more about that here.
Then add in your spreadsheet URL. Make sure you take this URL from the address bar of your spreadsheet.
Then click the "Next" button.
4) Set up your fields
Now it's time to make sure the bot is recognising the right info from your spreadsheet. Four dropdown menus will appear:
• Select column to search from
• Select column with results
• Choose a message to send if no results were found
• Select attribute to save search term
For the first drop down menu (Select column to search from), select the column that contains your topics (i.e. the search terms). For the second column (Select column with results), select the column that contains the content you want to serve back to the user:
As you can see these corresponding with the headings from my spreadsheet:
The bot will now know to pull information from those particular columns.
Then choose/create a message that will be sent to the user if their search term cannot be found in the spreadsheet. Add this into the "Choose a message to send if no results were found" dropdown menu.
"Select attribute to save search term" allows you to save the search terms typed in by a user. This attribute will save terms that does and doesn't match topics from the spreadsheet, and can be accessible in your data download CSV.
5) Set up your results card
Lastly, set up your carousel cards to show the results. The title will be pulled in from the attribute you allocated to "Select attribute to save search term", and the subtitle will contain the information from your content column in your spreadsheet. It is then your choice to add an image and button(s) (you can add up to three buttons per card). You will also have to set up a final card - this can contain extra info for the user i.e. where to go if the results didn't contain what they were looking for.
Buttons on the carousel have the option to save the entire result via user attribute, which can then be used to send, in a new message, the full result. To do this, create your button, label it and select "save the entire result via user attribute" from the dropdown menu and choose/create your reply message:
Save your Get from Spreadsheet settings, and then your message then head over to the reply message you just created in the modal. Add in a text box that introduces your message then add in the attribute that you created under the "save the entire result via user attribute" setting:
This will now pull in the information from the content column, which allows the user to see the answer in more detail:
Please note the the character limit is 500 characters for a full response, so please factor that in when creating your content. Also, make sure the most important information is at the top of your content, so the user can quickly understand what topics they are scrolling through in the carousel. The character limit for the subtitle is 80 characters, so make sure the most top line information fits into that space.
6) Test your flow
Now go through and test your flow. Please remember to launch the message containing the get from spreadsheet message part in order for the search terms to be recognised. We recommend setting up a keyword that launches this message, so the user can go in and launch that message at any time in the bot flow. For example, I have added the keyword "search" in order to launch my get from spreadsheet message part: