Google Sheets (Insert, Update and Get data)

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!

How to add an integration and sync it

Add a Google Sheet integration block

1. To configure a Google Sheet integration choose from the blocks selector Google Sheets, by typing "Google Sheets", like in the picture below:

   

Sync with one of your Google Spreadsheets

Now you need to connect your Google account. Click on the button and you'll be redirected to Google to enter your email, password and give permissions to the app to manage data:

1. Next, select from the first drop-down the spreadsheet you want to be working with, as well as the specific sheet where your data is:

   

Now, select the Action top perform. We'll explain now what does each action help you with and how to configure each one of them:

How to Insert, Update and Get data

Insert a new row in a sheet.

The classic one. This action sends data collected by Landbot to your Sheet. All you have to do is:

1. Select which column of the spreadsheet (left) corresponds to which field collected by Landbot (right).
   In the example below we're going to send the variable @name to the column "Name".

   

   

   

   

Update a row in a sheet.

With this Action you'll be able to:

1. Search for a value collected by landbot within a certain column of the spreadsheet (e.g. "id").
2. Update another value (column) in the same row with another field collected by Landbot. (e.g. "phone")

   

   

   

   

   1. Success. Path triggered then the name provided by the visitor exists in the Sheet, so Landbot was able to add the email next to it.
   2. Fail. Path triggered when the name provided doesn't exist.

Get data from a sheet.

The way it works is by searching for the ROW in your spreadsheet that has in a COLUMN (that you need to choose in the REFERENCE COLUMN section) one value that you have collected and stored in one Landbot variable (e.g. @email) as the "reference column value".

Then you can get as many values, and save it in Landbot variables, from CELLS in COLUMNS (that you choose too in the GET ROW VALUES section) that are in this specific ROW.

STEPS:

1. Once is selected, your Google Account, Spreadhseet and Sheet, Go to the "REFERENCE COLUMN"
2. In the top selector (of the REFERENCE COLUMN), you will be able to select one of the columns of the spreadsheet (based on the names that are in the first row of the spreadsheet)
3. In the bottom selector (of the REFERENCE COLUMN) you will add the Landbot variable (or value) you want to search for. It is easier if you press the button below (VARIABLES) to select the Landbot Variable
4. Now we go to the REFERENCE COLUMN
5. Here in the top selector you will find again the names of the columns, select the want you want to extract data from the row found
6. In the bottom selector we will select (or create if needed) the Landbot variable where we want to save the value from the cell.
7. If we want to pull more information, please select the + sign, and repeat the steps 5 and 6

With this action you can:

1. Search for a variable collected by landbot within a certain column of the spreadsheet (e.g. "email").
2. Send back to the chat the info of another column in the same row (e.g. "name").

   

   

   

   

   1. Success. Path triggered then the name provided by the visitor exists in the Sheet, so Landbot was able to add the email next to it.
   2. Fail. Path triggered when the name provided doesn't exist.

FAQs & Troubleshooting:

I have one integration after another integration and is not working

• Google Sheet blocks cannot be chained one after the other, directly. They need to have another block in between them

The data is uploading to the wrong column

• Once the integration is set up in Landbot, any column move or name change in the header of a column, will make the integration stop working properly. After any of this changes the Integration has to be redone from scratch with a new Google Sheets block integration

The block configurator keeps loading after selecting the sheet

• This means there are no columns in the sheet selected. Move on to your Drive and create some 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 (or edit anything in the sheet integrated already) but you don't update (and save) the settings in the Google sheet block of your integration, the integration won't work as intended or even might not upload any information at all.

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

• Currently the Get Data From a Sheet makes a search based on a "Contains" condition. The fix to this problem will be to 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 RED output.

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.