Last updated: 22 April 2021 

Integrations allow you to connect your chatbot with external sources of data.

Often, integrations come into the form of an API. However, in some cases, your data may reside in a spreadsheet instead of a piece of accessible software.

For such cases, we have a a spreadsheet integration.

Spreadsheet integrations allow you to use data stored inside a .CSV (or .TXT) file into your chatbot.

Spreadsheet integrations, though amazing, are more limited than API integrations. For example, you cannot update a piece of data into a spreadsheet from your chatbot. You may only pull data from your spreadsheet into your chatbot.

Below are a couple of examples of how powerful a spreadsheet integrations can be:

  • Upload a spreadsheet of your inventory. Use it to inform chatbot users of how many items are available to purchase.

  • Upload a spreadsheet listing each of your employees' particular skills. As employees talk to your chatbot about a project they’re working on, your chatbot can recommend other employees with complimenting skills.

  • Upload a spreadsheet listing various vacancies at your business. As candidates talk to your chatbot and share their experience, the chatbot directs them to a suitable vacancy.

Step 1: Format your spreadsheet

Before importing a spreadsheet, make sure it suits the following requirements:

  • Your spreadsheet has to be a .CSV or .TXT. Excel spreadsheets will not work.

  • Content within the spreadsheet must be organised from column A, row 1.

  • Ensure there are no spaces at the end of the content inside a cell.

  • Ensure the column names are all lowercase.

Spreadsheet formatted? Fantastic. Let’s import it into your platform.

Step 2: Import your spreadsheet

To create a spreadsheet integration, head to IntegrationsIntegrations.

Click Upload spreadsheet.

A popup invites you to create your import.

You must give your file a name before you are able to import it. In this instance, we’ll call it inventory.

Hit Create.

Once you’ve created the file name, the upload button becomes available.

Click Upload file and find your spreadsheet on your local computer.

Step 3: Create your lookups

Our spreadsheet is now uploaded to the platform. Time to create the lookups.

Lookups are the equivalent of VLOOKUP functions in Excel. They allow you to use the value in one column to return the corresponding value under a different column, vertically.

In your newly created spreadsheet import, click Lookups, then Add new lookup.

Give your lookup a name and a description, for internal use only.

In the name_row field, enter the name of the column you would like to use as a starting point for your lookup. For example, if your spreadsheet has two columns, items and inventory, enter items in this field.

Select the conditional you need.

In the last field, enter the value you’d like to lookup.

To make your chatbot and its spreadsheet integration more powerful, use variables in this last field.

Hit Save.

Step 4: Use your spreadsheet in a conversation

Your spreadsheet has been imported and you’ve set up your first lookup. Now, you can use the conversation builder to resurface values from your spreadsheet to your users.

Go to ChatsBuilder.

Find the conversation you’d like to use and click View.

Within your conversation, find the step at which you would like to resurface your spreadsheet data. Click Options.

Head to Message content and click Advanced settings.

In the popup, select the following options:

  • Use spreadsheet values

  • Your spreadsheet name

  • Your spreadsheet lookup

Hit Save.

Your spreadsheet lookup values are now available to pull into this specific message. To do so, use the following variable format inside the content of your message:

{{0.column}}

0 means look for the first element within the spreadsheet to match the lookup.

column is the name of the column you’d like to look up.

Once you’re done, hit Save.

More about the 0.column format

Spreadsheet lookups might appear complicated, particularly due to the format you must use to bring values into the message copy.

It helps to understand a little bit more about the format.

This format represents an array. Arrays are common in programming languages. They represent a structured collection of data.

In our spreadsheet example, the data inside the spreadsheet forms the array. The columns and the rows allow us to programmatically navigate within the array by giving the platform instructions on where to land.

A perhaps confusing aspect of arrays is they are zero-indexed. This means instead of numbering each element of the array starting by 1, we actually start at 0 – hence the example {{0.column}} meaning ‘find the first element within the spreadsheet to match the lookup’.

Once you’ve moved past this little quirk, though, it all makes sense. Creating a lookup for items and telling your platform to resurface {{0.inventory}} would simply mean:

Go through the spreadsheet of items and their inventory I’ve uploaded. Find the item I want and vertically look up the inventory we have on this item. Resurface the first value you find. Thanks, friend.

You can change the number from 0 to anything you’d like. Writing {{2.inventory}} for instance would be asking not for the first value but the third value we can find in the array.

If this is all still a bit confusing, make sure you reach out to your account manager for help.

Can I download my spreadsheets?

Yes. Once you’ve uploaded a spreadsheet, it is available to download using the previous to last button.