# Measuring Google Spreadsheet Visits Using Google Analytics 4

_Last updated: 2024-06-18_

Track how many people open a shared Google Spreadsheet by embedding a pixel that fires a Google Analytics 4 event each time the spreadsheet loads. This method uses a server-side image request to log visits without requiring users to click external links or accept app permissions.

## Why measure spreadsheet visits?

A client created an ROI calculator using Google Spreadsheets as a brand utility. Before investing in converting it to a custom website form, they wanted to measure actual usage. Standard tracking methods fail: bit.ly links are bypassed when the spreadsheet is shared directly or via Google's native share feature; Google Apps Script counters display warnings on clone and require manual inspection of the spreadsheet itself (which increments the counter).

## The image pixel method

Instead of Apps Script, use the old-school image pixel technique: embed an `=IMAGE()` formula that fetches an image from a server script. The server script fires a Google Analytics 4 event and returns a 1×1 pixel, triggering GA4 tracking each time the spreadsheet is opened.

### How it works

1. Create a PHP script that fires a GA4 event and returns an image.
2. Obtain the measurement ID and API secret from Google Analytics 4.
3. Use the spreadsheet formula `=IMAGE()` to request the image from your script.
4. View results in GA4 Real-time reports and Engagement > Events.

## Writing the PHP script

Place this script on a PHP-enabled website (or use a no-code alternative like Integromat for Webflow or static HTML generators). A developer should vet the code before deployment.

### Script components

**Measurement ID and API secret:**

```
$measurement_id = "G-XXXXXX";
$api_secret = "XXXXXXXXXX";
```

Replace with your GA4 credentials (see steps below).

**Event name:**

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

Defaults to `ImagePixelEvent`; customize via query string parameter `e`.

**Image URL:**

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

Defaults to a logo; customize via URL-encoded query string parameter `i`.

**Client identification:**

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

Note: Due to Google's image proxy, the source IP will typically appear as Google's proxy, not the user's actual IP.

### GA4 API call

```
$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);
```

### Return the image

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

### Complete script

```
<?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));

?>
```

Use with caution and have your developer review it before deploying to production.

## Obtaining the measurement ID and API secret

1. Log in to your Google Analytics account.
2. Go to Admin.
3. Click Data Streams.
4. Select the relevant account (use your website account or create a new one).
5. Copy the measurement ID.
6. Click Measurement Protocol API Secret.
7. Read and accept the terms if prompted.
8. Click Create.
9. Enter a meaningful name for the API secret.
10. Copy the generated API secret value.

## Using the =IMAGE() formula in your spreadsheet

Once your developer deploys the script (e.g., at `www.tld.com/amazingpixel.php`), use this formula in a cell:

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

The `v` parameter with `NOW()` appended ensures the URL changes on each recalculation, triggering a fresh request.

### Custom event name and image

To customize the event name or image URL from within the spreadsheet:

1. URL-encode your image URL using [URL Encoder](https://www.urlencoder.org/).
2. Choose a custom event name.
3. Use this formula:

```
=IMAGE("https://www.tld.com/amazingpixel.php?v="&NOW()&"&i=ENCODED_IMAGE_URL&e=CustomEventName")
```

Replace `ENCODED_IMAGE_URL` with your encoded URL and `CustomEventName` with your chosen event name.

## Viewing results in GA4

- **Real-time testing**: Reports > Real-time. Events appear immediately.
- **Historical data**: Reports > Engagement > Events. Data populates after ~24 hours.

## Other use cases

The image pixel method works anywhere an external image URL can be embedded:

- Track spreadsheet usage by sales teams or customers.
- Track opens of shared spreadsheets outside your organization.
- Embed the image pixel in email newsletters to track opens (outside your own platform).
- Embed in content syndication platforms that allow external images (verify terms of service).
- Track any external image embed as a GA4 event.

**Pro Tip**: Use a dedicated GA4 account for shared or external tracking where you cannot control who accesses it.

## Historical note

Before third-party JavaScript became standard, image pixels were the primary method for tracking users across websites.