How To Track Link Clicks With Apps Script And Google Sheets

In this post, we’ll explore an easy way to track link clicks on a website using Google Sheets and Apps Script.

This setup is simple and not comparable to the sophisticated event tracking configurations in dedicated analytics software. However, it’s a nice, lightweight alternative that could be useful in limited scenarios. If nothing else, it’s a nice example showing how to create a webhook with Apps Script and Google Sheets to log data.

In this image you can see that when a link is clicked, it’s logged in a Google Sheet with information about the link:

Track link clicks with Apps Script

It uses the rather obscure “ping” attribute of an HTML anchor link, to send a POST request to a listening webhook set up using a Google Sheet and Apps Script.

Track Link Clicks Demo

I’ve added link tracking to this link (it doesn’t go anywhere, it’s just for this demo):

Click Here For Link Tracking Demo

Open this read-only Google Sheet to see the clicks on this link in real-time:

Google Sheet

Here is the embedded version of that Sheet, showing all the clicks on this link to date (scroll down to see all the clicks):

Note: there is a 5-minute lag with embedded Google Sheets. This means that although the tracking shows up immediately in the native Google Sheet, it won’t show in this embedded Sheet for up to 5 minutes.

How To Track Link Clicks With Google Sheets And Apps Script

Step 1: Create A Blank Google Sheet

Type sheet.new into your browser to create a new Google Sheet instantly.

Step 2: Set Up The Webhook

Open the Apps Script IDE from the Google Sheets toolbar:

Extensions > Apps Script

Add the following code and click save:

/**
 * doPost function webhook to track link clicks
 */
function doPost(e) {

  // get spreadsheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1');
  
  // proceed if e exists
  if (typeof e !== 'undefined') {
    
    // parse webhook data to extract parameters
    const name = e.parameter.name;
    const section = e.parameter.section;
    const link = e.parameter.link;
    
    // create new timestamp
    const d = new Date();

    // append new row of data to Sheet
    sheet.appendRow([d, section, name, link]);
    
    // return undefined
    return;
  }
}

(View this code on GitHub.)

The ping attribute in an anchor text link sends a POST request when the link is clicked.

This doPost(e) function runs when it receives this POST request and parses the data.

The e argument represents an event parameter containing information about the request. In this case, we parse the parameters to extract the relevant link data, which we append to a new row in our Google Sheet.

Step 3: Deploy The Webhook

Afer the code is added, we need to deploy the script as a web app before it will behave as a webhook.

Click on Deploy in the top right of the Apps Script window.

Select New deployment:

New Deployment in Apps Script

Next, click on the gear wheel icon and select Web app:

New Deployment Web App in Apps Script

Then add a description, e.g. “Track link clicks”, and set Who has access to Anyone:

Apps Script New Deployment Configuration

Click Deploy.

If this is the first deployment, you’ll need to authorize your script to access the spreadsheet scope. Click Authorize access and follow the prompts:

New Deployment Authorize Access

Finally, copy the Web app URL from the New deployment modal window:

New Deployment Copy URL

Note: if you update the code, you’ll need to re-deploy it. This generates a new URL for the web app.

Great work!

This is our Google Sheet project ready to track link clicks on our website.

Step 4: Create The Tracking Link

Putting our Google Sheet aside, we can now create a tracking link on our website (or modify an existing one).

To track link clicks, we need to add the ping attribute to the links.

The link should have the following format:

<a href="/link" ping="/trackingLink">Example Link</a>

This ping URL (“/trackingLink”) consists of our webhook URL (from Step 3) with (optional) additional query parameters added.

In this specific example, I added three parameters: 1) a section for the area of the website where the link is, 2) a name to describe the link, and 3) the URL of the link destination:

<a href="#demo" ping="https://script.google.com/macros/s/XXXXXXXXXXXX/exec?name=TrackLinkClicksDemo&section=BlogPost&link=https://www.benlcollins.com/apps-script/track-link-clicks/">Click Here For Link Tracking Demo</a>

These additional query parameters can be used to include additional information that is sent to the webhook, for example, the name of the link and the link URL.

If you don’t include any query parameters, the data sent by the ping is sparse and of limited value.

To illustrate, compare the data (stringified JSON) received with no query parameters:

{"contentLength":5,"parameters":{},"parameter":{},"queryString":"","contextPath":"","postData":{"contents":"PING\u0000","length":5,"name":"postData","type":"text/ping"}}

versus the data received with query parameters added (shown in red):

{"contextPath":"","queryString":"name=TrackLinkClicksDemo§ion=BlogPost&link=https://www.benlcollins.com/apps-script/track-link-clicks/","parameter":{"link":"https://www.benlcollins.com/apps-script/track-link-clicks/","name":"TrackLinkClicksDemo","section":"BlogPost"},"contentLength":5,"postData":{"contents":"PING\u0000","length":5,"name":"postData","type":"text/ping"},"parameters":{"section":["BlogPost"],"link":["https://www.benlcollins.com/apps-script/track-link-clicks/"],"name":["TrackLinkClicksDemo"]}}

Notice that the query parameters are also available in the parameters field and the queryString field. For more information on these request parameters, see the Google documentation.

Note: if you use different parameters in your ping URL, you’ll need to also modify your code to parse these parameters, and also re-deploy your web app again (which creates a new URL remember!).

Track Link Clicks Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings.

In this case, right-click the link to open it in an Incognito window to view it.

See Also

I used this same webhook technique to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script. See:

How to build a real-time sales dashboard for E-junkie using Google Sheets and Apps Script

Hat tip to Sven for this tweet that inspired this post.

4 thoughts on “How To Track Link Clicks With Apps Script And Google Sheets”

  1. Do you have an example of how you would add it to a Google Sheet and then have a counter on the Google Sheet versus having this separate website link that is used? I’m more interested in keeping this ONLY within google sheets and tracking certain links from a Google Sheet.

  2. Thanks for this article, very helpful. I just tried this script to track a link sent on email (gmail). However, it seems that gmail removes the ping attribute. Is there any workaround for this?

  3. will this work for Intranet sites – I want to track our team’s usage of our intranet site, where all of our process documentation etc. is stored

Leave a Reply

Your email address will not be published. Required fields are marked *