How to return unique values from a spreadsheet lookup
Last updated: 19 June 2023
Imagine the following scenario.
You work at a furniture store. You are creating a chatbot that helps users purchase your furniture. You have a handy spreadsheet on your computer that lists all your furniture for sale like desks, chairs, tables. Each of those items has tens of variants.
Early on in the conversation, you want to guide the user to a specific item they’d like to buy – for example a chair. You decide to do that by creating a custom fuzzy composer – great idea!
Only one problem… your spreadsheet lists hundreds of items in each category. How will you present only one unique category to the user?
This is the challenge we will solve in this tutorial.
Step 1: Tidy up your spreadsheet
Before we get to the meaty part of this tutorial, let’s cover the basics. Your spreadsheet must:
Be a .CSV file.
Be populated starting from cell A1.
Have a header per column.
Now, I don’t know anything about desks, chairs, and tables. So, for the purpose of this tutorial, I’m going to use animals. Here’s what your spreadsheet should look like:
Step 2: Create your spreadsheet integration
Go to Integrations. Under the Spreadsheet section, click Add spreadsheet integration.
Give your integration a name and import your spreadsheet.
Now let’s create a lookup.
For this example, our lookup will be somewhat simple: if there’s something in a row, we want to return the animal from that row. This will allow us to create a composer where each row is an option.
Click on Lookup and Add new lookup. Give your lookup a name and a description.
In the rules, enter your first column’s name and has more letters than 1.
This lookup will return all the items from the animal column.
Click Save.
Step 3: Create your composer
Go to Engage → Composers and click Create composer.
We want to create a fuzzy composer which is populated by the different types of animals pulled from the spreadsheet (dog, cat, and bird).
To do this, we’re going to use a Dynamic composer.
In the Variables section, select Use spreadsheet values and pick your spreadsheet and lookup.
We can now use templating to loop around our spreadsheet integration and create a fuzzy option for each item.
{
"type": "fuzzy",
"content": {
"placeholder": "Choose an option...",
"multi": false,
"items": [
{% for row in lookup.data %}
{"label": "{{row.animal}}", "value": "{{row.animal}}"}{% if not loop.last %},{% endif %}
{% endfor %}
]
}
}But wait!
If you save this and trigger your composer, what happens?
That’s right. Because our lookup is pulling all the values from the animal column, our fuzzy composer is rightfully returning all of them; six times dog, six times cat, and six times bird.
Not ideal.
Step 4: Use the Unique formatter
Thankfully, we have a nifty trick to avoid this.
You can append the unique formatter to variables. The unique formatter removes all duplicates from an array of values. In this example, we can modify our loop like so:
{
"type": "fuzzy",
"content": {
"placeholder": "Choose an option...",
"multi": false,
"items": [
{% for row in lookup.data|unique("animal") %}
{"label": "{{row.animal}}", "value": "{{row.animal}}"}{% if not loop.last %},{% endif %}
{% endfor %}
]
}
}By appending |unique("animal") to our loop (line 7), we tell our composer to loop across all the values and create the composer but first remove any duplicates.
Easy as that!