By default when you add a Google Sheet as a data source to a Hyperise image, we use a Sheet ID and row number to identify the required data.

This works great when using just the Google Sheet, but not if you want to use the Sheet data with another integration, or reference the data in a Sheet by another columns values, rather than row number.

For the full run through check the video, otherwise follow the step-by-step guide below:

Step-by-step Guide

To enable an alternative lookup key to your Hyperise image from Google Sheet data, select the Sheet column to use as the key.  

Go to the Google Sheet settings within your Hyperise Image, below the column mappings, you can select one Sheet column to be used to retrieve that rows data into the image.

Once you have selected a column lookup key, you can now use values from that column against the utm_hyperef URL parameter to retrieve that data.

Once you have enabled a Sheet integration, with lookup key, you can then use that Sheet data with other Image integrations.

For example let's say you use MailChimp to send out emails, you could have a sheet of VIP customers, who you want to message differently, or change the offer.  

Those differences could be stored in a Sheet, and allow the Hyperise image, with MailChimp integration, to pull default data from MailChimp and the Hyperise image and when a VIP matching customer is found, additional data from the Sheet could be pulled in.

OPTIONALLY IF the data in the Sheet is not static, ie you're still adding and amending data, after you add the Sheet as an integration. Then you need to complete the following steps, to setup a Sheet webhook.

1) Select the Sheet column to use as the key

2) Add webhook to Sheet with Script Editor

3) Add custom Function to the Sheet

1) Select the Sheet column to use as the key

Within the Google Sheet settings within your Hyperise Image, below the column mappings, you can select one Sheet column to be used to retrieve that rows data into the image.

Once you have selected the appropriate column, the Data Source Hash will be revealed, you will need this is step three below.

2) Add webhook to Sheet with Script Editor

Now that you have a Hyperise Image connected to your Sheet and new lookup key defined, you can now add the webhook to the sheet.

WARNING: this isn't beginner level stuff 😉 

Open up the script editor from Tools -> Script Editor

Once the Script Editor has loaded paste the following code into the main editor.

function HR_KEY_CHANGED(row, dataSourceHash) {
 var options = {
   'method' : 'post',
   'payload': {data_source_hash: dataSourceHash}
 };
 
 UrlFetchApp.fetch('https://app.hyperise.io/api/v1/regular/data-sources/google-sheet-updated', options);
 return "UpdatedAt: "+ currentTime()
}


function currentTime() {
 var d = new Date();
 var currentTime = d.toLocaleTimeString()
 return currentTime;
}

Once you've added the code you can save the script.

3) Add Custom Function to the Sheet

Now that we've created the webhook function, we need to add the custom function to the sheet.

=HR_KEY_CHANGED(B:B; "Data-Source-Hash" )

The function example above has two parameters, the first B:B is the column being used as the lookup key and the second Data-Source-Hash is the value copied from step one.

Add this function to your Sheet, where it's out the way, this could even be on another tab, but be sure to reference the tab name, if having on different tabs.

Once the custom function has been added, you will notice that each time you update your lookup key column, the functions last change timestamp will update.  This also triggers the webhook, enabling Hyperise to reindex the lookup column ready for use.

Thats it you're done, each time you update the Sheet, the webhook will be called and Hyperise will reindex your lookup key, ready for your images to call.

Example Sheet

If you'd like a copy of the above example sheet, with the script added and custom function setup on a separate tab, select the Sheet below and take a copy:

https://docs.google.com/spreadsheets/d/17JlCZ7hLvGr-WIsmzy5Il42geqeHneWqeN0t4lSiAtU/edit?usp=sharing

Once you've taken a copy simply add your data to the DataSheet tab and set the lookup column and data source hash in the Webhook Settings tab.