📋 Measure Core Web Vitals of Your Websites with Google Sheets

1. Overview

\[ \begin{array}{l} \textbf{Google Sheets can be used to track and analyze Core Web Vitals --} \\ \text{such as Largest Contentful Paint (LCP), First Input Delay (FID), and Cumulative Layout Shift (CLS).} \\ \textbf{By using the PageSpeed Insights API with Apps Script --} \\ \text{you can automate the collection of performance metrics for multiple websites.} \end{array} \]

2. Core Steps

\[ \begin{array}{ll} \textbf{Step 1:} & \text{Create a Google Sheet with a list of website URLs in Column A.} \\ \textbf{Step 2:} & \text{Get a free API key from Google Cloud for PageSpeed Insights.} \\ \textbf{Step 3:} & \text{Open the Script Editor (Extensions $\rightarrow$ Apps Script).} \\ \textbf{Step 4:} & \text{Write a script to call the PageSpeed Insights API and parse metrics.} \\ \textbf{Step 5:} & \text{Run the script, store results in the sheet, and update regularly.} \end{array} \]

3. Sample Google Apps Script

// Replace with your API key
var API_KEY = 'YOUR_API_KEY_HERE';

function measureCoreWebVitals() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var urls = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();

  for (var i = 0; i < urls.length; i++) {
    var url = urls[i][0];
    if (url) {
      var apiUrl = "https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" + encodeURIComponent(url) + "&key=" + API_KEY;
      var response = UrlFetchApp.fetch(apiUrl);
      var data = JSON.parse(response.getContentText());
      
      try {
        var lcp = data.loadingExperience.metrics.LARGEST_CONTENTFUL_PAINT_MS.percentile;
        var fid = data.loadingExperience.metrics.FIRST_INPUT_DELAY_MS.percentile;
        var cls = data.loadingExperience.metrics.CUMULATIVE_LAYOUT_SHIFT_SCORE.percentile;
        
        sheet.getRange(i + 2, 2).setValue(lcp);
        sheet.getRange(i + 2, 3).setValue(fid);
        sheet.getRange(i + 2, 4).setValue(cls);
      } catch (e) {
        sheet.getRange(i + 2, 2).setValue("Error");
        sheet.getRange(i + 2, 3).setValue("Error");
        sheet.getRange(i + 2, 4).setValue("Error");
      }
    }
  }
}

4. Important Notes

\[ \begin{array}{l} \text{• Core Web Vitals include LCP, FID, and CLS, measured in milliseconds or score units.} \\ \text{• You need a Google Cloud API key with PageSpeed Insights enabled.} \\ \text{• API quotas apply; avoid sending too many requests at once.} \\ \text{• Ensure URLs include https:// for correct measurement.} \end{array} \]

5. Conceptual Flow (in LaTeX)

The process can be described as:

\[ \text{Website URLs in Sheet} \xrightarrow{\text{Apps Script + PageSpeed API}} \text{Core Web Vitals Data} \]

Where: \[ \text{Core Web Vitals} = \{ \text{LCP}, \text{FID}, \text{CLS} \} \]