Integrate your chatbot with Google Sheets

SnatchBotチーム、 23/06/2020

Integrate your chatbot with Google Sheets

Spreadsheet integration allows you to export chatbot data to a spreadsheet hosted on Google Sheets platform, or retrieve the data from a specific sheet row.

This feature can be used in hundreds of use cases, whenever you need to save the chatbot generated data outside of the bot, including:

  1. Loyalty campaigns;
  2. Lead generation;
  3. Retrieving product, prices, description, and features;
  4. Making table reservations in restaurants and bars;
  5. Retrieving user’s shipment information;
  6. … and various other use cases across all business verticals.

Both imported and exported data needs to be saved within dedicated attributes, so make sure you configure the right attribute structure before using the feature.

Note: Make sure you name attributes in accordance with their contents, so you don’t get confused when assigning them to spreadsheet columns.

The spreadsheet action is only available for the PRO-plan users, so make sure you have upgraded your chatbot.

Start by clicking on the interaction that is the exact point where you would like to export or import the bot data to/from the spreadsheet. In the right part of the screen, find the tab menu, and choose the “Automations” option.

Then click on the “Create Automation” button.

… and click on the “Google Sheets” option in the pop-up.

The new pop-up will open, and you will be able to choose one of two options:

  1. Insert a new row - Inserts a new row in the sheet, when the user reaches this interaction, and fills it with chosen user attributes.
  2. Get raw by value - Chooses one row from a prefilled spreadsheet and replaces current user attributes’ values with the ones from the sheet row.

Use your chatbot to insert a row on Google sheets

After choosing the “Insert a row” button, choose the Google account where your spreadsheet is located. If you already connected your account, you will have it as one of the options, just click on the radio button next to the email address:

If you haven’t added it earlier, the automation will connect to Google, and you will have to choose one of the accounts you are logged on your computer, or log into the desired account by typing your email and password within the pop-up.

Connecting a new Google account requires you to provide Snatchbot with necessary permissions. Click “Allow” on the next two pop-up screens.

Now, after you have connected your Google account with Snatchbot, you can continue to the second step of the setup.

Note: Connected Google account can be disconnected in the Profile section (click on your name in the vertical menu on the left) under the “Automations” tab. Here you can also connect new accounts.

In the second step, you can choose to:

  1. Create a new spreadsheet - Opens a new sheet on your Google Docs account, and a column for each attribute whose value you would like to add;
  2. Use existing spreadsheet - Opens a premade sheet and allows you to choose which attribute value will go to which column;

The third and the last step allows us to choose the name of the new spreadsheet and worksheet and to assign sheet columns to attributes on Snatchbot. These are the values that you should add:

  1. Spreadsheet - this field contains the name of the sheet, you can also open the dropdown menu and choose some other spreadsheet from your Google Drive;
  2. Worksheet - Allows you to select the worksheet, a newly created spreadsheet will only have one spreadsheet, but if you choose some other sheet in the “Spreadsheet” filed, with more worksheets, you will be able to browse them in a dropdown menu;
  3. Open sheet button - Opens a spreadsheet link in another tab of your browser;
  4. Snatchbot Attributes column - Column where you choose which attribute will align with which column in the Google Spreadsheet, you can choose between all chatbot attributes, including the Facebook-generated ones;
  5. Google Columns column - List of Google columns that you should use for aligning the attribute values;
  6. Refresh - the button that refreshes the connection with Google Sheets, and reloads the columns (in case you have made some changes);
  7. Cancel - cancels your input;
  8. Save - saves your input and activates the automation;

First, choose the spreadsheet and the worksheet, then select an attribute from the dropdown menu for every Google Sheet column and click the “Save” button when you finish adding.

You have just activated the automation that will export attribute values from your bot, and place them in the new Google Sheet row, each one in the desired column.

Get a Google Sheet row by value for your chatbot

After you export the attribute values to the spreadsheet, you can easily retrieve them and include them in current or future conversations. “Get row by value” function also allows us to retrieve any column value and turn it into an attribute value. It doesn’t matter whether it is placed into the bot by the Snatchbot’s “Insert a row” feature, by hand or by some other app or bot.

If you would like to import the data left by a particular user in some previous conversation,, you will need to create an identifier that will allow the chatbot to find the correct row. Some of the common identifiers are:
 

  1. Email;
  2. Phone number;
  3. Name;
  4. User name or a unique number the bot would issue to the user.

Most of these identifiers require you to make the chatbot flow GDPR compliant and to follow all other local or national privacy laws.

If your chatbot is working only on Facebook Messenger, you can use automatically-generated Facebook attributes for identifying users, so you don’t need to ask them for any other data upfront. Just make sure you export them to the spreadsheet in the first place.

The “Get row by value” feature setup is equally simple as the “Insert a row” one. Before you reach the screen where you will find the desired row and import the data, you will first need to :

  • Open the Automations tab;
  • Click on create automation;
  • Choose “Get row by value” option;

In the first step, choose the Google account you want to use. The second step will look like this:

You should choose:

  1. Spreadsheet - from which you would like to import data;
  2. Worksheet - from which you would like to import data;
  3. Lookup Column - the column you would use to identify the row from which you would like to import data (which contains the identifier, like email, name, unique number, etc.);
  4. Lookup Value - The attribute value you would like to use as an identifier and that will help you to find the row (user) in question. If there are more than one rows with the same value, Snatchbot will import data from the latest added row (first from the bottom);

The scheme for assigning columns values to attributes looks like this:

On the left, you can see the list of Google Sheet columns. On the right, you can choose an attribute for each column in the dropdown menus. This means you are importing the column into the attribute, and you can later use it in the conversation with getAttribute command.

If you leave the Snatchbot Attribute row empty, the value from the column that aligns with it won’t be imported into the chatbot. After you align columns with the right attributes, click the Save button, and each time users reach this interaction, the column values from the sheet will be imported to the chosen attributes, and you will be able to engage users with the data they have left in their previous conversations.