Table of Contents

Google Sheets (Insert, Update and Get data)

Dilyara Updated by Dilyara

Note: available on Professional and Business plans.

Prefer watching than reading? No problem, we've got you covered ๐Ÿ‘‡

Google has recently updated its authentication permissions. This update requires all of our users to migrate their current Google Sheets blocks to the new version to ensure that all of the blocks are up to date to Google's new policies. This migration has to be done before July 15th, otherwise after this date, all the bots that have Google Sheets blocks that have not been migrated will becomeย non-operational.

For more information on how to migrate your Google Sheets integration, please check here.

Google Sheets integration

Check out this article to learn how to configure our Google Sheet integration and perform complex actions without a single line of code: Sheets is now your Landbot's favourite database!

To start setting up a Google Sheet integration, choose the Google Sheets block in the block searcher, by typing "Google Sheets":

Tip: Please keep in mind that you will use Variables to send the information to the Google Sheets block.

If you are not familiar with Landbot's variables, please check out this video tutorial before proceeding with the integration.

Sync with one of your Google Spreadsheets

  1. Sign into your Google account: Click on the button Add account, and you will be redirected to Google.
    Enter your email, password and give permissions to the app to manage data. After logging in, select the account from the dropdown to integrate with Landbot.
  2. Select the Spreadsheet: Select from the drop-down the Spreadsheet you want to integrate with Landbot, as well as the specific sheet where the data will be saved in:

The Google Sheet integration needs to find the headers (first row/line) of your Spreadsheet to work correctly, as you will see in the next steps of this article.

So if the block keeps loading when configuring it and doesn't show any options, it is because:

  1. The app cannot find the headers of the columns of the selected sheet.
  2. There is no data in the selected sheet.

If this is the case, change the setup of your Spreadsheet or add data to it, and try the integration again.

WARNING: The Spreadsheet must be created in its final version before integrating with Landbot.

If you perform any changes in the structure of the Spreadsheet, the Google Sheets integration must be set up again.
Note: As usual, the integration will only be activated if the block is triggered on the flow.

We recommend you place it right after you have collected the data that is important to you, to make sure visitors go through it and trigger the integration (instead of placing it at the end of the flow).

How to Insert, Update and Get data

After integrating with a Spreadsheet, you must select the Action to perform.

We will explain now how the 3 possible actions work and how to configure each one of them.

Insert a New Row in a Sheet

The classic one. This action sends data collected by Landbot to your Spreadsheet, creating a new row (a new line) in the Spreadsheet.

  1. Select the column you want to insert data in: First you must select what column of the Spreadsheet will be updated with the information collected by Landbot. Select from the dropdown the the name of the column, as set in the Spreadsheet.
  2. Define what information will be sent to that column. You must determine what information collected by your bot will be sent to Google Sheets.
    The value (saved in a variable) will be sent to Google Sheets, creating a new row (a new line) in that column you chose.

Example

In the example below we are going to send the value saved in the variable @name to the column NAME.

This is how the Spreadsheet was set up:

First we used a Question: Email block, and saved the answer in the variable @name . This is the variable we will insert in the Google Sheets block in the next step.

This is how it will look in the Google Sheets block, when we choose to create a new row, sending the value @name to the column NAME:

This is how the conversation will look like from the user's point of view:

And after the Google Sheets is triggered, the data will be sent to the Spreadsheet, creating a new row under the column NAME.

Update a row in a sheet.

With this Action you will be able to Update the rows of a spreadsheet following two steps:

  1. Search for a value collected by Landbot (REFERENCE COLUMN): First you must check if a value (saved in a variable) is existent in a certain column of the Spreadsheet.
    (for example, search for @name, in the column NAME ).

    This is where the spreadsheets starts looking for the information. After it finds the information, it will continue the search from left to right, in the same row (line).
  2. Update the row with a new value: Once you have chosen the value to start looking for in the same row, you can update the information of that row with a value collected by Landbot.
    (for example, update in the column EMAIL the value saved in the variable @email).

Outcomes:

There are two possible outcomes from the Google Sheets block when you are trying to Update a Row in a Sheet:

  1. Success (Green arrow): Path triggered when the value we are searching for (step 1) exists in the Spreadsheet.
  2. Failed (Red arrow). Path triggered, but the variable we are searching for does not exist in the Spreadsheet. So it was not possible to update the information (since we did not find this row).

Example

This is how our Spreadsheet was set:

Please pay special attention to the fields from the columns NAME and EMAIL.

This is how we set the flow:

We want to use the user's name, saved in the variable @name , to find the row with his registry and update the email he has given us.

So we used the column NAME as a reference, and the value that we can find in the variable @name .

We will then update the information from the column EMAIL , with the value kept in the variable @email .

Here is how the user would see the flow:

And this is how it would look in the Spreadsheet after the integration was triggered. Please note that the email was changed, since it was updated with the new information:

Before:

After:

Get Data From a Sheet

This action works by searching in a row (line) from your Spreadsheet, according to the Column you have chosen as a reference (Reference Column).

If the Google Sheets finds the value in that row, it will get this information and send it to Landbot. Once you get the data from the Spreadsheet, you can use this value as a variable in your bot.

  1. Search for a value collected by Landbot (REFERENCE COLUMN): First you must check if a value (saved in a variable) is existent in a certain column of the Spreadsheet.
    (For example, search for @name, in the column NAME ).
    This is where the Spreadsheet starts looking for the information. After it finds the information, it will continue the search from left to right, in the same row (line).
  2. Get Row Values: Once you have chosen the value to start looking for in a row, you can get a value from a determined column (still in that same row), sending this value to Landbot.
    (for example, get from the column EMAIL the value saved in the variable @email).

Outcomes:

There are two possible outcomes from the Google Sheets block when you are trying to Get Data from a Sheet:

  1. Success (Green arrow): Path triggered when the value we are searching for (step 1) exists in the Sheet, and we were able to get the information and send it into Landbot.
  2. Failed (Red arrow). Path triggered, but the variable we are searching for does not exist in the Spreadsheet. So it was not possible to get the information (since we did not find this row).

Example

This is how our Spreadsheet was set:

Please pay special attention to the fields from the columns NAME and EMAIL .

This is how we set the flow:

Reference Column: We want to use the user's name, saved in the variable @name, as a Reference Column to find the information in this row.

So we used the column NAME as a reference, and the value that we can find in the variable @name . We chose this variable from the variables list, by clicking on the button "VARIABLES".

Get data from the Spreadsheet: Starting searching in the column where we find the user's name (@name), we will then get the information from the column EMAIL , with the value kept in the variable @email .

Here is how the user would see the flow:

Please note that the Spreadsheet will not be changed, because we are GETTING information from it, instead of inserting or updating.

But we were able to import into the bot the variable @email , which was not provided by the user during the interaction.

FAQs & Troubleshooting:

If your Google Sheets integration is not working, please see if one of the following cases applies to your bot.

FAQ & Troubleshooting

Can I use 2 Google Sheets blocks, one right after the other?

No. Google Sheet blocks can't be chained one after the other, directly. They need to have another block in between them.

How can I send a URL (external link) from the Spreadsheet to the bot?

Using the Get data from the Spreadsheet action, you must add the variable in the format @{link1} .

The data is uploading to the wrong column

Once the integration is set up in Landbot, if you move a column, or change the name in the header of the column will make the integration stop working properly.

After any of this changes the Integration has to be redone from scratch, setting up a new Google Sheets block.

The block configurator keeps loading after selecting the sheet

This happens when there are no columns set in the sheet selected.

Move on to your Google Sheets account and create some columns in the Spreadsheet to complete the integration!

I've created a copy of the sheet and the data isn't uploading in the new sheet

If you create a new sheet in the Spreadsheet (or edit anything in the sheet integrated already), you will need to reset the settings in the Google Sheets block.

The integration won't work as intended or even might not upload any information at all if the integration is not reset.

The method "Get Data From a Sheet", returns a similar value, but not the exact one that I was looking for.

The Get Data From a Sheet action makes a search based on a "Contains" condition. So if more than one row contains the same value, this may happen.

To fix this problem, add another column with the same value, for example, email, but with the "|" symbol like |email@email.com.ย 

Use it in the Google Sheet Integration as well. This way, the search will contain all the email address, not just a part of it.

What happens if I search for a value in a column, but this value doesn't exist?

If no value is found, the flow will continue through the Failed (red arrow) output. Make sure to connect this output to another block to use this option.

If the failed path is not connected, the bot will stop.

What happens if there are 2 or more cells with the same value?

The integration only returns values from one row. In this case, will return values from the row that is located higher in the Spreadsheet.

Can I use a Code Block before a Google Sheets integration?

No. If you use a Code block before a Google Sheets block, both the Code block and the Google Sheets block will not work, and the bot will stop.

Why I can't get dates from a sheet?

Keep in mind that in Landbot, the date is saved in the format of YYYY-MM-DD, with the dash or hyphen (-), and won't be accepted if the date is added with a slash (/). To solve this, you need to use a Set a variable block, and the format for a Date variable should be YYYY-MM-DD. Check out this article for more information about setting a date variable.

Channel Type compatibility

for Web

This feature is fully compatible.

for WhatsApp

This feature is fully compatible.

for Messenger

This feature is fully compatible.

How did we do?

How to use Google Sheets to create a simple verification system for returning visitors

Contact