We get asked a lot about how to easily incorporate a lookup table in decision tree logic. For example, you may have a list of part numbers, and want to show the name, price, and other part info in your tree presentation. This can be easily done with a simple Google Sheets webhook integration that we provide. From one variable, you can retrieve one or more corresponding data values.

Demo

To show how this works, we’ve created a simple demo that returns the city, state, county, and other information from a USA Zipcode.

The Google Sheet with the lookup is here.
Try the Google Sheets Lookup demo Decision Tree

Setup

1. Create your spreadsheet in Google Sheets. The first column is what you will search on, and the second column and beyond are the results. The variable name for each of the results is the header of each column. Our demo Sheet looks like this:

   

2. Important: The tab name for the lookup table should be the default Sheet1. You can override this by including a sheet_name= parameter in the Webhook URL.
   
   
3. Make sure the spreadsheet is publicly accessible. In Sheets, click Share, and make sure “Anyone on the internet with this link can view” appears.

   

4. Note the ID of your spreadsheet. When viewing the sheet, the ID is in the URL – like this (bolded):
   https://docs.google.com/spreadsheets/d/1ACNOEksZ6pL0J2UD1BhxM7zPVWWDcd4Svtpb-oPmRVY/edit?usp=sharing
   
   
5. In Zingtree add a new webhook. From the top menu, click Apps & Integrations, Add a Webhook, and create a webhook like this:

   

   Here’s what the Webhook URL looks like in our demo:
   https://webhooks.zingtree.com/zingtree/v1/google_sheets_lookup?search=#zip#&sheet_id=1nHWaFN2l28VZgKTgIuTDbzKRuiAmKKP9TY0n32nvhWU

   #zip# is a placeholder for the Zipcode variable we use to do the lookup. The sheet_idparameter is the one you noted in step 4.
   
   

6. Now create the first node of your tree. Add a data entry field variable named zip. The lookup will return the other variables from the top row of our spreadsheet.

   

7. Next, add the second node of the tree. Under Apps/Webhooks, select the webhook you just set up. This will set variables named primary_city, county, state, timezone, and others from your Sheet when this node is reached by the end-user.

   

8. In the content area, enter  #primary_city#, #state#, #county# etc. to show the retrieved data. Here’s an example:

   
   
   Note: All cells in columns with titles must have some sort of data entry in them, or the integration won't work. For example, if columns A-E all have titles, but cell E22 is missing data, the webhook won't return any information from row 22. Simply inputting N/A if there is no data will ensure the webhook works properly.