Add Personalised Links to Google Sheets

Add Personalised Links to Google Sheets

Using Personalised Short Links, you can provide a simple short link to your prospect, with embedded personalised Open Graph data, meaning when its shared socially, it will generate a personalised preview.

eg:  https://lnk.your-domain.com/YE78H6i

                                  ^ you control the domain                       ^ unique to each link

For example sharing a personalised short link in LinkedIn will generate a preview like this:

This can also be replicated in other platforms, like this:

To create your personalised links, you must first add you custom link domain to your account.

To do this go to Settings > Custom Domain to setup.  For further details on this process, check out the Link Domain Setup Guide.

Once you've domain is setup, you have a few choices to create the links.  You can do this programmatically using our API (here's the API Guide) or using a CSV file (here's the CSV Guide), this guide will focus on using a Google Sheet.

How to create Personalised Short Links using a Google Sheet

1) Add a Short Links column to your Sheet

2) Edit the image you want to use for the personalised preview

3) Select Integrations and choose Google Sheets

4) Add the Sheet, map the Short Links column to enable the Short Links Setup button

5) Add the Short Link Destination URL and personalised Title and Desc

The above five steps will populate your Sheet with Short Links for all existing rows into the column you have defined for the Short Links.  

If you also want to dynamically add short links when new rows are added, then setup a Sheet webhook.

1) Select the Sheet column to show the Data source hash

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, you can now add the webhook to the sheet.

WARNING: this isn't beginner level stuff 😉 

Open up the script editor from Extensions > Apps Script Tools

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 triggers the webhook, enabling Hyperise to create new personalised short links, each time a new row is added/updated in the Sheet.

Thats it you're done, each time you update the Sheet, the webhook will be called and Hyperise will do its work.

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.