{"id":3363,"date":"2020-10-01T14:52:40","date_gmt":"2020-10-01T14:52:40","guid":{"rendered":"https:\/\/www.leadshook.com\/help\/?p=3363"},"modified":"2024-02-14T11:40:42","modified_gmt":"2024-02-14T11:40:42","slug":"using-google-sheets-as-a-lookup-table","status":"publish","type":"post","link":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/","title":{"rendered":"Using Google Sheets as a Lookup Table"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p>In this article you&#8217;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.<\/p>\n\n\n\n<p>There are 2 methods available&#8230;<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">METHOD 1: Easy Method Using a 3rd Party App (Developer NOT Required)<\/h3>\n\n\n\n<p>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.<br><br>Third Party App: <a href=\"https:\/\/nocodeapi.com\/\">https:\/\/nocodeapi.com\/<\/a><\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-vimeo wp-block-embed-vimeo wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe src=\"https:\/\/player.vimeo.com\/video\/558964085?dnt=1&amp;app_id=122963\" width=\"980\" height=\"551\" frameborder=\"0\" allow=\"autoplay; fullscreen; picture-in-picture\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">METHOD 2: Using Scripts (Developer Required) <\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Its possible to use server side javascript to process an API call in LeadsHook<\/li>\n\n\n\n<li>Basically you send a request to the GS server with a parameter to search with.<\/li>\n\n\n\n<li>Then the script will search a given column where the parameter matches.<\/li>\n\n\n\n<li>When it finds the matching column value \u2013 the whole row is retrieved, converted into JSON, and sent back to the LeadsHook API call as response parameters.<\/li>\n\n\n\n<li>These parameters can then be assigned to custom fields in LeadsHook<\/li>\n<\/ul>\n\n\n\n<p><br><strong>Step 1:<\/strong> Make sure you are logged in to your gmail account.<br><br><strong>Step 2:<\/strong> Click the link below, or paste it on your browser.<br><br><a href=\"https:\/\/docs.google.com\/spreadsheets\/d\/1GsGKwaDZDnOqjCEbtvIraLTFf9hhzurpiMZEbWd1B9g\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/docs.google.com\/spreadsheets\/d\/1GsGKwaDZDnOqjCEbtvIraLTFf9hhzurpiMZEbWd1B9g<\/a><br><br><strong>Step 3:<\/strong> On your Google Sheet, go to File -> Make a Copy, and it should then be saved on your google drive&#8217;s account.<br><br><strong>Step 4:<\/strong> You will be using that Copy as your Google Sheet of reference for your own Database ( Lookup table )<br><br><strong>Step 5:<\/strong> 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.<br><br><strong>Step 6:<\/strong> Update the script:<br>&#8211; Update the field that you are passing from Leadshook to Google sheets by changing line 2: \u00a0<br>var ZIPCODE=e.parameter.<strong>YOUR_FIELD_HERE<\/strong><br><br>-Update the row number on line 17:<br>\u00a0if(row[<strong>CHANGE_NUMBER<\/strong>]==ZIPCODE) return result=row<br><br>Step 7: Deploy and use the app URL in a webhook node in Leadshook.<br>&#8211; Make sure to use GET method and append ?<strong>YOUR_FIELD_HERE<\/strong>={<strong>LEADSHOOK_FIELD_HERE<\/strong>}<\/p>\n\n\n\n<p>Grab Sample Decision Tree: <a href=\"https:\/\/www.leadshook.com\/help\/wp-content\/uploads\/2022\/03\/grab-data-from-google-sheet_2022-03-11.zip\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.leadshook.com\/help\/wp-content\/uploads\/2022\/03\/grab-data-from-google-sheet_2022-03-11.zip<\/a><\/p>\n\n\n\n<p><strong>Video explanation:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-vimeo wp-block-embed-vimeo wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"Firefox - API to GSheets - Google Sheets\" src=\"https:\/\/player.vimeo.com\/video\/687102298?h=30892d31b8&amp;dnt=1&amp;app_id=122963\" width=\"980\" height=\"551\" frameborder=\"0\" allow=\"autoplay; fullscreen; picture-in-picture\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In this article you&#8217;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&#8230; METHOD 1: Easy Method Using a 3rd Party App (Developer NOT Required) In method 1, you [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,620,281],"tags":[737,830,226,391,36,829,567,394,696,271],"class_list":["post-3363","post","type-post","status-publish","format-standard","hentry","category-decision-tree","category-scripts","category-webhooks","tag-database","tag-excel","tag-google","tag-google-sheets","tag-javascript","tag-lookup","tag-script","tag-sheets","tag-spreadsheet","tag-table"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Using Google Sheets as a Lookup Table - LeadsHook Knowledge Base<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using Google Sheets as a Lookup Table - LeadsHook Knowledge Base\" \/>\n<meta property=\"og:description\" content=\"Introduction In this article you&#8217;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&#8230; METHOD 1: Easy Method Using a 3rd Party App (Developer NOT Required) In method 1, you [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/\" \/>\n<meta property=\"og:site_name\" content=\"LeadsHook Knowledge Base\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/leadshook\/\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-01T14:52:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-14T11:40:42+00:00\" \/>\n<meta name=\"author\" content=\"Nik T\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@leadshook\" \/>\n<meta name=\"twitter:site\" content=\"@leadshook\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nik T\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/using-google-sheets-as-a-lookup-table\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/using-google-sheets-as-a-lookup-table\\\/\"},\"author\":{\"name\":\"Nik T\",\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/#\\\/schema\\\/person\\\/4bfe37d814563cc729828b7055313f4d\"},\"headline\":\"Using Google Sheets as a Lookup Table\",\"datePublished\":\"2020-10-01T14:52:40+00:00\",\"dateModified\":\"2024-02-14T11:40:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/using-google-sheets-as-a-lookup-table\\\/\"},\"wordCount\":398,\"publisher\":{\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/#organization\"},\"keywords\":[\"database\",\"excel\",\"Google\",\"Google Sheets\",\"javascript\",\"lookup\",\"script\",\"Sheets\",\"spreadsheet\",\"table\"],\"articleSection\":[\"Decision Tree\",\"Scripts\",\"Webhooks\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/using-google-sheets-as-a-lookup-table\\\/\",\"url\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/using-google-sheets-as-a-lookup-table\\\/\",\"name\":\"Using Google Sheets as a Lookup Table - LeadsHook Knowledge Base\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/#website\"},\"datePublished\":\"2020-10-01T14:52:40+00:00\",\"dateModified\":\"2024-02-14T11:40:42+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/using-google-sheets-as-a-lookup-table\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.leadshook.com\\\/help\\\/using-google-sheets-as-a-lookup-table\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/using-google-sheets-as-a-lookup-table\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using Google Sheets as a Lookup Table\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/#website\",\"url\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/\",\"name\":\"LeadsHook Knowledge Base\",\"description\":\"LeadsHook Training &amp; Technical Help\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/#organization\",\"name\":\"LeadsHook\",\"url\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/wp-content\\\/uploads\\\/2018\\\/12\\\/leadshook-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/wp-content\\\/uploads\\\/2018\\\/12\\\/leadshook-logo.png\",\"width\":350,\"height\":83,\"caption\":\"LeadsHook\"},\"image\":{\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/leadshook\\\/\",\"https:\\\/\\\/x.com\\\/leadshook\",\"https:\\\/\\\/www.linkedin.com\\\/showcase\\\/leadshook\\\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/#\\\/schema\\\/person\\\/4bfe37d814563cc729828b7055313f4d\",\"name\":\"Nik T\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/48d53e68db0b1db1cd5c54a750130fb2ee0c663306ff1bd434373f1a368ef7a0?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/48d53e68db0b1db1cd5c54a750130fb2ee0c663306ff1bd434373f1a368ef7a0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/48d53e68db0b1db1cd5c54a750130fb2ee0c663306ff1bd434373f1a368ef7a0?s=96&d=mm&r=g\",\"caption\":\"Nik T\"},\"sameAs\":[\"http:\\\/\\\/www.LeadsHook.com\"],\"url\":\"https:\\\/\\\/www.leadshook.com\\\/help\\\/author\\\/nik\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Using Google Sheets as a Lookup Table - LeadsHook Knowledge Base","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/","og_locale":"en_US","og_type":"article","og_title":"Using Google Sheets as a Lookup Table - LeadsHook Knowledge Base","og_description":"Introduction In this article you&#8217;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&#8230; METHOD 1: Easy Method Using a 3rd Party App (Developer NOT Required) In method 1, you [&hellip;]","og_url":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/","og_site_name":"LeadsHook Knowledge Base","article_publisher":"https:\/\/www.facebook.com\/leadshook\/","article_published_time":"2020-10-01T14:52:40+00:00","article_modified_time":"2024-02-14T11:40:42+00:00","author":"Nik T","twitter_card":"summary_large_image","twitter_creator":"@leadshook","twitter_site":"@leadshook","twitter_misc":{"Written by":"Nik T","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/#article","isPartOf":{"@id":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/"},"author":{"name":"Nik T","@id":"https:\/\/www.leadshook.com\/help\/#\/schema\/person\/4bfe37d814563cc729828b7055313f4d"},"headline":"Using Google Sheets as a Lookup Table","datePublished":"2020-10-01T14:52:40+00:00","dateModified":"2024-02-14T11:40:42+00:00","mainEntityOfPage":{"@id":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/"},"wordCount":398,"publisher":{"@id":"https:\/\/www.leadshook.com\/help\/#organization"},"keywords":["database","excel","Google","Google Sheets","javascript","lookup","script","Sheets","spreadsheet","table"],"articleSection":["Decision Tree","Scripts","Webhooks"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/","url":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/","name":"Using Google Sheets as a Lookup Table - LeadsHook Knowledge Base","isPartOf":{"@id":"https:\/\/www.leadshook.com\/help\/#website"},"datePublished":"2020-10-01T14:52:40+00:00","dateModified":"2024-02-14T11:40:42+00:00","breadcrumb":{"@id":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.leadshook.com\/help\/using-google-sheets-as-a-lookup-table\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.leadshook.com\/help\/"},{"@type":"ListItem","position":2,"name":"Using Google Sheets as a Lookup Table"}]},{"@type":"WebSite","@id":"https:\/\/www.leadshook.com\/help\/#website","url":"https:\/\/www.leadshook.com\/help\/","name":"LeadsHook Knowledge Base","description":"LeadsHook Training &amp; Technical Help","publisher":{"@id":"https:\/\/www.leadshook.com\/help\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.leadshook.com\/help\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.leadshook.com\/help\/#organization","name":"LeadsHook","url":"https:\/\/www.leadshook.com\/help\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.leadshook.com\/help\/#\/schema\/logo\/image\/","url":"https:\/\/www.leadshook.com\/help\/wp-content\/uploads\/2018\/12\/leadshook-logo.png","contentUrl":"https:\/\/www.leadshook.com\/help\/wp-content\/uploads\/2018\/12\/leadshook-logo.png","width":350,"height":83,"caption":"LeadsHook"},"image":{"@id":"https:\/\/www.leadshook.com\/help\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/leadshook\/","https:\/\/x.com\/leadshook","https:\/\/www.linkedin.com\/showcase\/leadshook\/"]},{"@type":"Person","@id":"https:\/\/www.leadshook.com\/help\/#\/schema\/person\/4bfe37d814563cc729828b7055313f4d","name":"Nik T","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/48d53e68db0b1db1cd5c54a750130fb2ee0c663306ff1bd434373f1a368ef7a0?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/48d53e68db0b1db1cd5c54a750130fb2ee0c663306ff1bd434373f1a368ef7a0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/48d53e68db0b1db1cd5c54a750130fb2ee0c663306ff1bd434373f1a368ef7a0?s=96&d=mm&r=g","caption":"Nik T"},"sameAs":["http:\/\/www.LeadsHook.com"],"url":"https:\/\/www.leadshook.com\/help\/author\/nik\/"}]}},"_links":{"self":[{"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/posts\/3363","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/comments?post=3363"}],"version-history":[{"count":35,"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/posts\/3363\/revisions"}],"predecessor-version":[{"id":7197,"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/posts\/3363\/revisions\/7197"}],"wp:attachment":[{"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/media?parent=3363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/categories?post=3363"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.leadshook.com\/help\/wp-json\/wp\/v2\/tags?post=3363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}