Google Sheets: How to search in Google Sheets and save results as a custom field

One of the most powerful features of Google Sheets integration is the ability to search and return the data as a custom field to use it in your bot. This can open the door to tons of useful use cases, for example we can ask the lead to insert their order ID and we return the order status, or we can return info about something they ask. 

How to get data from a google sheet and save it as a custom field

1

The first step, you must connect your Google Sheets account.

2

Open the bot you want to use, drag and drop Google Sheets object into the Canvas.

3

Click on the object to edit it. Choose the account you want to use, select the spreadsheet and the sheet. Select the action Get.

 

4

Assume we have the following data in our sheets. We want to let the user enter their Order ID and we will search for that in our sheet and save the answer as a custom field. Then we will display that custom field in the bot. 

5

Let's set up the object like the following:

Lookup column: We’ll search this column for a matching value starting from the top of the sheet. When we find a match for the lookup value we will select that row. We will set this as column D: Order ID

Lookup value to find row: The value we will search for in the Lookup column. We added a question before the Google sheets object and saved its answer as a custom field called order_id. We will use the custom field order_id as the lookup value.

Get data from column: When we find a match, we will retrieve data from this column for the matched row. We will select the Order Status column.

Save as: Specify the custom field where we'll store the value we find. We created a custom field called order_status that we will save the data into.

Note: to get the value for more than one column, click on the "Add another column button"

6

Let's add a message to tell the user the status of their order. Add the custom field   order_status in that message:

 

7

Let's test it now:

Notice how the bot displayed the data from the spreadsheet.