Measuring Google Spreadsheet Visits Using Google Analytics 4

One of my clients had an interesting challenge, they’ve created an ROI calculator using Google Spreadsheets as a brand utility. They want to invest in making this ROI calculator more interactive and developing it as a custom form on their website, but before doing so, why not measure how many people are actually using it?

And this is the challenge.

How do you measure spreadsheet visits?

Well, you could use a custom bit.ly link and measure that, but if someone will share the link directly, or even use Google’s share option – it’ll bypass the analytics.

You could use Google’s action script to up a counter internally in the spreadsheet each time someone opens it, but there are two main caveats:

1. Each time someone clones the spreadsheet they’ll get a “warning” about this spreadsheet using an action script.
2. You’ll have to go to your own spreadsheet to see the counter, which is not only not intuitive but also increases the count…

As a data driven tech savvy marketer, my customer just loves using GA4 (not.), so he went and found some article about using GA Universal + action script to measure analytics. But this already has a caveat of using action scripts, and it’s not really supported in GA4 easily.

So instead, by using the old school image pixel trick, we could trigger a GA4 event every time someone opens the spreadsheet and sees the company’s logo.

How to measure spreadsheet visits using GA4?

1. Create a php script that will fire an event to GA4 while returning an image
2. Get the API key and measurement ID from GA4
3. Use the spreadsheet formula =IMAGE() to fetch the image
4. Watch the analytics and enjoy the numbers

Writing the PHP Script

Well, if you’re not into coding and don’t care about how it works, just share it with your developer and ask them to put this script in your website (assuming you’re using WordPress website).

We’ll probably make this into a plugin soon, just because.

If you’re not using a PHP website (say you’re using Webflow or Static HTML generator) – a no code option using Integromat can do the trick.

Here is the snippet broken down:

$measurement_id = "G-XXXXXX";

Your measure id from GA4, follow the steps below to get it.

$api_secret = "XXXXXXXXXX";

Your API key from Google Analytics 4, follow the steps below to get it.

$event_name = $_GET['e'] ?:  "ImagePixelEvent";

Get the event name from the query string or have it hardcoded to whatever you want.

$image_link = urldecode($_GET['i']) ?: "https://www.example.com/logo.jpg"; 

Get the logo or image URL from the query string, or hardcode it.

$ip = str_replace('.', '', $_SERVER['REMOTE_ADDR']); 

This will generally not work as you’ll see the source as google proxy due to the way the IMAGE function works. But I guess it’s fine.

Just putting it all for GA4 in their API:

$data = array(
		'client_id' => $ip,
		'user_id' => '123',
		'events' => array(
		'name' => $event_name
	)
);

$datastring = json_encode($data);
$post_url = 'https://www.google-analytics.com/mp/collect?api_secret=' . $api_secret '&measurement_id=' . $measurement_id;
$ch = curl_init($post_url);
curl_setopt($ch, CURLOPT_POSTFIELDS, $datastring);
curl_setopt($ch, CURLOPT_HTTP_VERSION,CURL_HTTP_VERSION_1_1);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_URL, $post_url);
curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));
curl_setopt($ch, CURLOPT_POST, TRUE);
$result = curl_exec($ch);

The code above will use the information to trigger a GA4 event to your analytics using your key.

Now it’s time to return the image:

header('Content-type: image/jpeg');imagejpeg(imagecreatefromjpeg($image_link));

This is how it all comes together:

<?php
        
    $api_secret = "12345-XXXXXXXXXX";
    $measurement_id = "G-XXXXXXXXX";

    $event_name = $_GET['e'] ?:  "ImagePixelEvent";
    $image_link = urldecode($_GET['i']) ?:  "https://www.example.com/logo.jpg";
    $ip = str_replace('.', '', $_SERVER['REMOTE_ADDR']);
    $data = array(
            'client_id' => $ip,
            'user_id' => '123',
            'events' => array(
            'name' => $event_name
            )
        );

    $datastring = json_encode($data);
    $post_url = 'https://www.google-analytics.com/mp/collect?api_secret=' . $api_secret '&measurement_id=' . $measurement_id;
    $ch = curl_init($post_url);
    curl_setopt($ch, CURLOPT_POSTFIELDS, $datastring);
    curl_setopt($ch, CURLOPT_HTTP_VERSION,CURL_HTTP_VERSION_1_1);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_URL, $post_url);
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));
    curl_setopt($ch, CURLOPT_POST, TRUE);
    $result = curl_exec($ch);
    
     header('Content-type: image/jpeg');
     imagejpeg(imagecreatefromjpeg($image_link));
    
?>

Now, I’m not really a developer, and this was an experiment I did, so use the code above with caution and ask your developer to vet it beforehand.

Getting the API key and measurement ID

1. Login to your analytics account
2. Go to Admin
3. Click on Data Streams
4. Choose the relevant account (you can use your website one, or create a new one)
5. Grab the measurement ID
6. Click on Measurement Protocol API Secret
7. Read and acknowledge the terms if you’re prompt to
8. Click Create
9. Give the API key a meaningful name
10. Copy the created API key

Using the =IMAGE() formula to trigger the analytics

Now how to use it in a spreadsheet?

Once your developer added the code somewhere, he’ll give you a URL like www.tld.com/amazingpixel.php

The next step will be to just use the following formula:

=IMAGE("https://www.tld.com/amazingpixel.php?v="&NOW())

Or if you wish to customize the event / image from within the spreadsheet:

Encode an image URL using URL Encoder and choose a custom event name:

=IMAGE("https:/www.tld.com/amazingpixel.php?v="&NOW()&"&i=Encoded URL Goes HERE.jpg&e=Custom Event Name Goes Here")

View the results in GA4

You’ll see the results in your GA4 reports under Reports-> Real-time for real time testing and under Reports -> Engagement -> Events after 24 hours or so.

That’s it.

Enjoy your spreadsheet measurements.

What other use cases can you do with this?

You can use it to track how your sales team are using a spreadsheet, how your visitors or customers are using a spreadsheet.

Alternatively you can share the image URL to place in an email newsletter which is not yours so you’ll also track, or even with content syndication if they allow external images (though not sure about this in regards to common terms).

Generally speaking – anywhere you can put an external image link – you can track.

Pro Tip: Always use a dedicated GA4 account if you’re sharing it with external places you might not be able to control.

Did you know that back in the days before 3rd party JavaScript were a thing, this is how they used to track users?

PS

If you’re into GA4 advance analytics of your website, or better yet – into generating insights using Google Looker