Using Google Sheets as a Lookup Table

Introduction

In this article you’ll learn how to use Google Sheets as an external database. You can make API calls from LeadsHook using the API node and SAVE the returning data into custom fields.

There are 2 methods available…

METHOD 1: Easy Method Using a 3rd Party App (Developer NOT Required)

In method 1, you use a 3rd party app that provides you with API end points to use inside LeadsHook. This is quite easy. Please watch this short video below.

Third Party App: https://nocodeapi.com/

METHOD 2: Using Scripts (Developer Required)

  • Its possible to use server side javascript to process an API call in LeadsHook
  • Basically you send a request to the GS server with a parameter to search with.
  • Then the script will search a given column where the parameter matches.
  • When it finds the matching column value – the whole row is retrieved, converted into JSON, and sent back to the LeadsHook API call as response parameters.
  • These parameters can then be assigned to custom fields in LeadsHook


Step 1: Make sure you are logged in to your gmail account.

Step 2: Click the link below, or paste it on your browser.

https://docs.google.com/spreadsheets/d/1GsGKwaDZDnOqjCEbtvIraLTFf9hhzurpiMZEbWd1B9g

Step 3: On your Google Sheet, go to File -> Make a Copy, and it should then be saved on your google drive’s account.

Step 4: You will be using that Copy as your Google Sheet of reference for your own Database ( Lookup table )

Step 5: To edit the script, go to: Extensions -> App Script editor on your Google Sheet. You can then change the script how you like. The main points to note are the GET request parameter, the column index ( which is zero based ). On the actual sheet you must take note of the column headers too.

Step 6: Update the script:
– Update the field that you are passing from Leadshook to Google sheets by changing line 2:  
var ZIPCODE=e.parameter.YOUR_FIELD_HERE

-Update the row number on line 17:
 if(row[CHANGE_NUMBER]==ZIPCODE) return result=row

Step 7: Deploy and use the app URL in a webhook node in Leadshook.
– Make sure to use GET method and append ?YOUR_FIELD_HERE={LEADSHOOK_FIELD_HERE}

Grab Sample Decision Tree: https://www.leadshook.com/help/wp-content/uploads/2022/03/grab-data-from-google-sheet_2022-03-11.zip

Video explanation:

 

Tagged: