How to check if a value exists in a spreadsheet
Last updated: 15 November 2023
Imagine we have a spreadsheet with a bunch of coupon codes. Our chatbot asks the customer to enter a coupon code to get a discount on their purchase.
If the coupon exists in the spreadsheet, we'll give them the discount. If not, we'll deny the discount.
Let's build this.
Step 1: Create a spreadsheet
To get us started, build a spreadsheet with your coupon codes. Your spreadsheet must:
Be a .CSV file.
Be populated starting from cell A1.
Have a header (in A1).
Have all the coupons inside the same column (A).
Here's my spreadsheet of made up coupon codes:
Step 2: Create your variable
In our automation, we plan on asking the user for a coupon code. We'll need to save what they say in variable, so we can use it to check against our spreadsheet values.
In Settings → Variables, create a new variable (e.g. code).
Step 3: 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 the lookup.
Click on Lookup and Add new lookup. Give your lookup a name and a description.
In the rules, enter your column's header equals the variable you just created.
This lookup will check column A (below the header) for the value stored in the variable.
Step 4: Create your conversation
Go to Builder and create a new conversation.
This conversation will have two steps. In the first, we'll ask the user to type their coupon code. In the second, we'll tell them whether their code is correct (aka exists in our spreadsheet) or not.
Here's an example copy for the first step:
Create the second step. For this one, we'll use dynamic templating, more specifically an IF statement, to display the right message depending on the result of our lookup.
Under Message type select Dynamic.
To keep it simple, we'll return a standard in both cases; you can of course make that message whatever you need it to be.
[
{% if lookup.data.0.voucher == '' %}
{
"type": "standard",
"content": {
"text": "Sorry, this coupon doesn't exist."
}
}
{% else %}
{
"type": "standard",
"content": {
"text": "Great, we'll apply your 10% discount at checkout!"
}
}
{% endif %}
]
In this template, we're simply checking whether lookup.data.0.voucher has a value or not. If it doesn't, as represented by the empty '', we return the first message. If it does, we return the second.
Finally, in the step's advanced settings select Use spreadsheet values, and pick your spreadsheet and lookup.
Hit Save.
In transition between step one and step two, simply drop your variable.
All done!