Translate

A Custom Google Spreadsheet Function for Tracking Pageviews

A Custom Google Spreadsheet Function for Tracking Pageviews

Tracking pageviews is an essential aspect of understanding how your website is performing. While platforms like Google Analytics offer detailed insights, sometimes you just need a quick, lightweight way to view your traffic directly inside a tool you already use daily — like Google Sheets. In this detailed guide, we will walk through how to create a custom Google Sheets function that pulls pageview data using Google Analytics API into your spreadsheet.

This guide assumes you are using Google Analytics 4 (GA4) and know the basics of Google Sheets and Google Apps Script.

Why Track Pageviews in Google Sheets?

Before jumping into the code, let’s explore why tracking pageviews directly in Google Sheets is useful:

  • Custom dashboards: Create quick, tailored dashboards without complex tools.

  • Client reports: Automate weekly or monthly traffic reports.

  • Trend analysis: Compare and analyze performance over time using built-in sheet functions.

  • Lightweight usage: For small websites or quick checks, it's faster than opening full analytics dashboards.

What You Need

  1. A Google Analytics 4 property

  2. Access to the GA4 data API

  3. A Google Cloud Platform (GCP) project

  4. Enabled Google Analytics API in GCP

  5. Google Service Account with access to GA4 property

  6. Google Sheets and access to Apps Script Editor

Step-by-Step: Setting Up Pageview Tracking in Google Sheets

Step 1: Create a Google Cloud Project

  1. Visit https://console.cloud.google.com/

  2. Click “New Project” and name it something like GA4 Pageview Tracker.

  3. Once created, go to the “APIs & Services > Library”.

  4. Search for Google Analytics Data API and enable it.

  5. Go to Credentials > Create Credentials > Service Account.

  6. Assign a role like Viewer or Editor.

  7. After creation, go to the service account and create a JSON key. Download this file. You’ll use this key in Apps Script.

Step 2: Add Service Account to Google Analytics

  1. Go to https://analytics.google.com/ and open your GA4 property.

  2. Navigate to Admin > Property Access Management.

  3. Click the blue “+” and add the service account email (from your JSON key) with Viewer access.

Step 3: Upload JSON Key to Google Drive

  1. Upload the downloaded JSON key file to your Google Drive.

  2. Make note of its file ID — you can find this in the file’s share URL:

    https://drive.google.com/file/d/FILE_ID/view

Step 4: Open Google Apps Script

  1. Open your Google Spreadsheet.

  2. Go to Extensions > Apps Script.

  3. Delete any default code and paste the following:

Sample Apps Script Code for Pageview Tracking

const KEY_FILE_ID = 'YOUR_JSON_FILE_ID'; // Replace with actual ID
const PROPERTY_ID = 'YOUR_GA4_PROPERTY_ID'; // Replace with your GA4 property ID

function getPageviewsGA4(startDate, endDate, pagePath) {
  const service = getService();
  if (!service.hasAccess()) {
    Logger.log('Authentication error: ' + service.getLastError());
    return 'Authentication failed';
  }

  const url = `https://analyticsdata.googleapis.com/v1beta/properties/${PROPERTY_ID}:runReport`;

  const payload = {
    dimensions: [{ name: 'pagePath' }],
    metrics: [{ name: 'screenPageViews' }],
    dateRanges: [{ startDate: startDate, endDate: endDate }],
    dimensionFilter: {
      filter: {
        fieldName: 'pagePath',
        stringFilter: {
          value: pagePath
        }
      }
    }
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    headers: {
      Authorization: 'Bearer ' + service.getAccessToken()
    },
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  const result = JSON.parse(response.getContentText());

  try {
    const value = result.rows[0].metricValues[0].value;
    return parseInt(value);
  } catch (e) {
    return 0;
  }
}

function getService() {
  const file = DriveApp.getFileById(KEY_FILE_ID);
  const json = JSON.parse(file.getBlob().getDataAsString());

  return OAuth2.createService('ga4')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(json.private_key)
    .setIssuer(json.client_email)
    .setPropertyStore(PropertiesService.getScriptProperties())
    .setScope('https://www.googleapis.com/auth/analytics.readonly');
}

Step 5: Use the Function in Google Sheets

After saving the script, go back to your spreadsheet. In a cell, type:

=getPageviewsGA4("2025-07-01", "2025-07-04", "/blog/custom-google-spreadsheet-pageviews")

This will return the pageviews for the specified page between July 1 and July 4, 2025.

Troubleshooting Tips

  • If you see “Authentication failed”, double-check your service account permissions and key.

  • If pageviews always return 0, make sure the page path is exactly correct (case-sensitive).

  • If the function hangs or throws errors, check the Execution log in Apps Script editor (View > Logs).

Enhancing the Tracker

You can customize or improve the function by:

  • Making PROPERTY_ID a parameter

  • Returning multiple rows (for top pages)

  • Adding filters like country, deviceCategory, etc.

  • Caching results for better performance

  • Triggering automatic refresh with Google Apps Script triggers

Benefits of This Approach

  • No need for third-party add-ons

  • Data stays in your control

  • Free and highly customizable

  • Integrates easily into dashboards, charts, or reports

Conclusion

Using a custom Google Spreadsheet function to track pageviews provides flexibility and simplicity, especially for users who frequently work in Google Sheets. With Google Analytics 4 and the power of Apps Script, you can create tailored, real-time pageview trackers suited to your exact needs — whether you're a solo blogger or managing client reports.

This method empowers you to turn a spreadsheet into a live traffic monitoring tool — lightweight, cost-free, and endlessly customizable.