Google Sheets: How to search in Google Sheets and save results as a variable
One of the most powerful features of Google Sheets integration is the ability to search and return the data as a variable 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 variable
- The first step, you must connect your Google Sheets account.
- Open the bot you want to use, drag and drop Google Sheets object into the Canvas.
- Click on the object to edit it. Choose the account you want to use, select the spreadsheet and the sheet. Select the action Get.
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 variable. Then we will display that variable in the bot.
- 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 variable called order_id. We will use the variable 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 variable where we'll store the value we find. We created a custom field called order_status that we will save the data into.
- Let's add a message to tell the user the status of their order. Add the custom variable order_status in that message:
- Let's test it now:
Notice how the bot displayed the data from the spreadsheet.
Tip: You can use the External API variables with JSON paths in the Google Sheets integration.