Free Core Web Vitals Monitoring Script: Track Your Performance vs Competitors Daily

				
					/**
 * ============================================================================
 * CHROME UX REPORT AUTOMATION SCRIPT
 * ============================================================================
 * 
 * This Google Apps Script automatically fetches Chrome UX Report metrics for
 * your domains and organizes them in a Google Sheet with historical tracking.
 * 
 * SETUP REQUIRED:
 * 1. Replace YOUR_API_KEY_HERE with your Chrome UX Report API key
 * 2. Ensure you have a sheet named "A" with your domains listed in column A
 * 3. Run the createSheetsAndAddData() function to start monitoring
 * 
 * Author: Panos Kondylis, COO of Fussion
 * Email: panos@fussion.agency
 * Linkedin: https://www.linkedin.com/in/kondilis/
 * ============================================================================
 */

// ============================================================================
// CONFIGURATION SECTION - UPDATE THIS BEFORE RUNNING
// ============================================================================

/**
 * API KEY SETUP:
 * 1. Go to https://console.cloud.google.com/
 * 2. Create a new project
 * 3. Enable the "Chrome UX Report API"
 * 4. Go to Credentials > Create API Key
 * 5. Replace 'YOUR_API_KEY_HERE' below with your actual API key
 * 
 * SECURITY NOTE: Consider using PropertiesService to store this securely
 * instead of hardcoding it here. Uncomment the line below if you prefer:
 * var apiKey = PropertiesService.getScriptProperties().getProperty('CUX_API_KEY');
 */
var apiKey = 'YOUR_API_KEY_HERE';

// ============================================================================
// MAIN EXECUTION FUNCTION
// ============================================================================

/**
 * MAIN FUNCTION: createSheetsAndAddData()
 * 
 * This is the primary function you'll run. It orchestrates the entire workflow:
 * - Reads domains from sheet "A"
 * - Creates a separate sheet for each web vital metric
 * - Fetches Chrome UX Report data for each domain
 * - Organizes data with historical date columns
 * 
 * HOW TO RUN:
 * 1. Click the dropdown menu in Apps Script (currently shows a function name)
 * 2. Select 'createSheetsAndAddData'
 * 3. Click the Play (▶) button to execute
 * 4. Grant permissions when prompted
 * 
 * EXPECTED RUNTIME: 2-5 minutes depending on number of domains
 */
function createSheetsAndAddData() {
    // ========================================================================
    // STEP 1: Define the Web Vitals to Monitor
    // ========================================================================
    
    /**
     * Map of Chrome UX Report metric names to display names
     * 
     * These are the six core web vitals that the Chrome UX Report tracks:
     * - cumulative_layout_shift (CLS): Visual stability (lower is better)
     * - first_contentful_paint (FCP): Time until first content appears
     * - largest_contentful_paint (LCP): Time until largest element loads
     * - first_input_delay (FID): Time until response to user click
     * - experimental_time_to_first_byte (TTFB): Server response time
     * - interaction_to_next_paint (INP): Latest responsiveness metric
     * 
     * Each display name becomes a sheet name in your spreadsheet.
     * To add/remove metrics, modify this object and the corresponding API field names.
     */
    var webVitals = {
        "cumulative_layout_shift": "CLS",
        "first_contentful_paint": "FCP",
        "largest_contentful_paint": "LCP",
        "first_input_delay": "FID",
        "experimental_time_to_first_byte": "TTFB",
        "interaction_to_next_paint": "INP"
    };

    // ========================================================================
    // STEP 2: Get References to Your Spreadsheet and Domain Sheet
    // ========================================================================
    
    /**
     * Get the currently active spreadsheet
     * This is the sheet you have open in Google Sheets when you run the script
     */
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    
    /**
     * Get reference to sheet "A" which contains your list of domains
     * This sheet must exist and be named exactly "A" (case-sensitive)
     * 
     * SHEET "A" FORMAT:
     * Row 1: Header (e.g., "Domains")
     * Rows 2+: Your domains to monitor (one per row)
     * 
     * Accepted domain formats (script handles all):
     * - https://www.example.com
     * - example.com
     * - www.example.com
     * - https://example.com
     */
    var domainSheet = ss.getSheetByName("A");

    /**
     * Safety Check: Verify that sheet "A" exists
     * If the sheet doesn't exist, log an error and exit
     * This prevents the script from crashing on missing sheets
     */
    if (domainSheet === null) {
        Logger.log("❌ ERROR: No sheet named 'A' found. Please create a sheet named 'A' with your domains.");
        return;
    }

    // ========================================================================
    // STEP 3: Read the Domain List from Sheet "A"
    // ========================================================================
    
    /**
     * Get the last row with data in sheet "A"
     * This tells us how many rows contain domain data
     */
    var lastRow = domainSheet.getLastRow();
    
    /**
     * Safety Check: Verify we have data beyond just the header row
     * If lastRow is 1 or less, only the header exists and there are no domains
     */
    if (lastRow < 2) {
        Logger.log("❌ ERROR: Sheet 'A' only has a header row. Please add domains starting in row 2.");
        return;
    }
    
    /**
     * Calculate how many rows of domain data exist
     * Example: If lastRow is 5, and we skip row 1 (header), we have 4 domain rows
     */
    var domainRowCount = lastRow - 1;
    
    /**
     * Extract all domain values from column A
     * - Start at row 2 (skip header in row 1)
     * - Get domainRowCount rows
     * - Get 1 column (column A)
     * - .getValues() returns a 2D array, .flat() converts it to 1D
     * 
     * After this line, 'domains' is a simple array like:
     * ['example.com', 'test.com', 'mysite.org']
     */
    var domains = domainSheet.getRange(2, 1, domainRowCount, 1).getValues().flat();
    
    /**
     * Log the domains for verification in the execution logs
     * Helps you see exactly what the script read from your sheet
     */
    Logger.log("📋 Domains found: " + JSON.stringify(domains));
    
    /**
     * Filter out empty or invalid domains to prevent errors
     * This removes any cells that are:
     * - Empty/null
     * - Contain only whitespace (spaces, tabs, etc.)
     * 
     * After filtering, you'll only have valid domains in the array
     */
    domains = domains.filter(function(domain) {
        return domain && domain.toString().trim().length > 0;
    });
    
    /**
     * Log filtered domains to confirm valid domains remain
     * This shows which domains will actually be processed
     */
    Logger.log("✅ Valid domains after filtering: " + JSON.stringify(domains));
    
    /**
     * Safety Check: Exit if no valid domains found
     * If all domains were empty, there's nothing to process
     */
    if (domains.length === 0) {
        Logger.log("❌ ERROR: No valid domains found in sheet A. Please add at least one domain.");
        return;
    }

    // ========================================================================
    // STEP 4: Generate Today's Date for Column Headers
    // ========================================================================

    /**
     * Create a formatted date string to use as today's column header
     * This helps you track when each data collection occurred
     * 
     * Format: "Saturday, 25 October" (human-readable)
     * This format makes it easy to see what day data was collected
     */
    var currentDate = new Date();
    var dayOfWeek = currentDate.toLocaleString('default', { weekday: 'long' });
    var dayOfMonth = currentDate.getDate();
    var month = currentDate.toLocaleString('default', { month: 'long' });
    var formattedDate = dayOfWeek + ", " + dayOfMonth + " " + month;

    // ========================================================================
    // STEP 5: Process Each Web Vital Metric
    // ========================================================================

    /**
     * Loop through each web vital metric defined above
     * For each one, either create a new sheet or update an existing one
     * 
     * Example: In first iteration, webVital = "cumulative_layout_shift" and webVitals[webVital] = "CLS"
     */
    for (var webVital in webVitals) {
        /**
         * Get the short display name for this metric (e.g., "CLS" instead of "cumulative_layout_shift")
         * This makes sheet names concise and readable
         */
        var metricDisplayName = webVitals[webVital];
        
        Logger.log("🔄 Processing metric: " + metricDisplayName);
        
        /**
         * Try to get a reference to an existing sheet with this metric's name
         * If the sheet doesn't exist yet, getSheetByName returns null
         */
        var sheet = ss.getSheetByName(metricDisplayName);

        // ====================================================================
        // CASE A: Sheet doesn't exist yet - Create a new sheet
        // ====================================================================
        
        /**
         * Check if this is the first time we're running the script
         * If sheet is null, this metric doesn't have a sheet yet
         */
        if (sheet === null) {
            Logger.log("  ✨ Creating new sheet for: " + metricDisplayName);
            
            /**
             * Insert a new sheet with the metric's display name
             * After this line, a new sheet named (e.g., "CLS") is created
             * The new sheet is initially blank
             */
            sheet = ss.insertSheet(metricDisplayName);
            
            /**
             * Add the header row to the new sheet
             * Column A: "Domains" label
             * Column B: Today's date in formatted format
             * This creates the structure: [Domains | Saturday, 25 October]
             */
            sheet.appendRow(["Domains", formattedDate]);
            
            /**
             * Loop through each domain and fetch its metric data
             */
            for (var i = 0; i < domains.length; i++) {
                var currentDomain = domains[i];
                
                Logger.log("  ⬇️  Fetching data for domain: " + currentDomain);
                
                /**
                 * Call the API to get this specific web vital metric for this domain
                 * getWebVitalData returns the 75th percentile value for that metric
                 * 
                 * Returns examples:
                 * - "2500" (milliseconds for time-based metrics)
                 * - "0.05" (decimal for CLS)
                 * - "No Data" (if insufficient Chrome UX data exists)
                 * - "API Error" (if API call failed)
                 */
                var metricValue = getWebVitalData(currentDomain, webVital);
                
                Logger.log("  ✓ Received value: " + metricValue);
                
                /**
                 * Add a new row to the sheet with:
                 * Column A: Domain name
                 * Column B: The metric value just fetched
                 * 
                 * This adds rows like: [example.com | 2500]
                 */
                sheet.appendRow([currentDomain, metricValue]);
            }
            
        } else {
            // ================================================================
            // CASE B: Sheet already exists - Add today's data as new column
            // ================================================================
            
            Logger.log("  📝 Updating existing sheet for: " + metricDisplayName);
            
            /**
             * Get the last column number in the sheet
             * This helps us add today's date in a new column to the right
             * 
             * Example: If sheet has 2 columns (A, B), getLastColumn() returns 2
             * We add 1 to get column 3, which becomes the new data column
             */
            var nextColumnNumber = sheet.getLastColumn() + 1;
            
            /**
             * Add today's date in the header row of the new column
             * This creates a time series: [Domains | Sat 18 Oct | Sun 19 Oct | Mon 20 Oct]
             * 
             * The header row is always row 1, so we specify:
             * - Row: 1
             * - Column: nextColumnNumber (calculated above)
             */
            sheet.getRange(1, nextColumnNumber).setValue(formattedDate);
            
            /**
             * Loop through each domain and add its current metric value
             * This updates the existing sheet with today's data
             */
            for (var i = 0; i < domains.length; i++) {
                var currentDomain = domains[i];
                
                Logger.log("  ⬆️  Updating domain: " + currentDomain);
                
                /**
                 * Call the API to get this specific web vital metric for this domain
                 * Same as above, returns the 75th percentile value
                 */
                var metricValue = getWebVitalData(currentDomain, webVital);
                
                Logger.log("  ✓ Received value: " + metricValue);
                
                /**
                 * Insert the data in the appropriate row and the new column
                 * 
                 * Row calculation:
                 * - Row 1 is the header
                 * - Row 2 is the first domain (i=0, so i+2 = 2)
                 * - Row 3 is the second domain (i=1, so i+2 = 3)
                 * 
                 * Column: nextColumnNumber (calculated above)
                 */
                sheet.getRange(i + 2, nextColumnNumber).setValue(metricValue);
            }
        }
    }
    
    /**
     * Log completion message
     * Lets you know in the execution logs that everything finished successfully
     */
    Logger.log("✅ Data collection completed successfully!");
}

// ============================================================================
// API COMMUNICATION FUNCTION
// ============================================================================

/**
 * FUNCTION: getWebVitalData(domain, webVital)
 * 
 * Makes the actual API call to Chrome UX Report and extracts the metric value.
 * 
 * PARAMETERS:
 * @param {string} domain - The domain to fetch metrics for (e.g., "example.com")
 * @param {string} webVital - The metric type from the webVitals map keys (e.g., "largest_contentful_paint")
 * 
 * RETURNS:
 * @return {string|number} - The 75th percentile metric value, or error message string
 *                           Examples: "2500", "0.05", "No Data", "API Error"
 * 
 * WHAT IT DOES:
 * 1. Validates inputs (prevents crashing on bad data)
 * 2. Constructs the API URL with your API key
 * 3. Formats the domain correctly for the API
 * 4. Makes an HTTP POST request to Chrome UX Report API
 * 5. Parses the JSON response
 * 6. Extracts the 75th percentile value for the requested metric
 * 7. Returns the value or an error message
 */
function getWebVitalData(domain, webVital) {
    
    // ========================================================================
    // VALIDATION: Check Inputs
    // ========================================================================
    
    /**
     * Safety Check: If domain is undefined or null, return error immediately
     * This prevents the script from crashing on undefined domains
     * 
     * Why this matters:
     * - Empty cells in your sheet might pass as undefined
     * - Prevents wasting API calls on invalid data
     */
    if (!domain) {
        Logger.log("⚠️  WARNING: Domain is undefined/null for metric: " + webVital);
        return "Invalid Domain";
    }

    // ========================================================================
    // STEP 1: Build the API URL
    // ========================================================================
    
    /**
     * Base URL for the Chrome UX Report API endpoint
     * 
     * This is Google's official endpoint for fetching Chrome UX Report data
     * The API key is appended to authenticate your request
     * 
     * RATE LIMITS:
     * - 150 requests per minute (per API key)
     * - 10,000 requests per day (per API key)
     */
    var apiEndpoint = 'https://chromeuxreport.googleapis.com/v1/records:queryRecord?key=' + apiKey;

    // ========================================================================
    // STEP 2: Format the Domain
    // ========================================================================
    
    /**
     * Convert domain to string and remove any leading/trailing whitespace
     * 
     * Why this matters:
     * - Excel/Sheets might add extra spaces
     * - Spaces cause the API to reject requests
     * - This ensures the domain is clean
     */
    domain = domain.toString().trim();
    
    /**
     * Ensure the domain has the https:// protocol prefix
     * The Chrome UX Report API requires the full protocol
     * 
     * Examples of what this does:
     * - "example.com" becomes "https://example.com"
     * - "www.example.com" becomes "https://www.example.com"
     * - "https://example.com" stays as "https://example.com"
     * 
     * IMPORTANT FORMATTING NOTE:
     * - Use www or no-www consistently with your actual site
     * - Different formats might have different data availability
     * - If no data appears, try with/without www
     */
    if (!domain.startsWith('https://')) {
        domain = 'https://' + domain;
    }

    // ========================================================================
    // STEP 3: Build the API Request Payload
    // ========================================================================
    
    /**
     * Create the request object that will be sent to the API
     * 
     * The "origin" parameter tells the Chrome UX Report API which website's
     * metrics to fetch. It must include the protocol (https://).
     * 
     * WHAT IS AN ORIGIN?
     * - Protocol + domain (no path, no query string)
     * - "https://example.com" is an origin
     * - "https://example.com/page1" is NOT an origin
     * - The API automatically aggregates data across all pages of this origin
     */
    var requestPayload = {
        "origin": domain
    };

    // ========================================================================
    // STEP 4: Configure the HTTP Request
    // ========================================================================
    
    /**
     * Set up the options for the HTTP request
     * These control how the request is sent to the API
     */
    var requestOptions = {
        /**
         * HTTP Method: POST
         * Chrome UX Report API requires POST requests (not GET)
         */
        "method": "post",
        
        /**
         * Payload: Convert the request object to JSON string
         * JSON format is required by the API
         * 
         * Example conversion:
         * {"origin": "https://example.com"} becomes a JSON string
         */
        "payload": JSON.stringify(requestPayload),
        
        /**
         * Headers: Tell the API we're sending JSON data
         * This is required for proper API communication
         */
        "headers": {
            "Content-Type": "application/json"
        },
        
        /**
         * muteHttpExceptions: Capture errors instead of throwing exceptions
         * 
         * Normally, a 404 error would crash the script
         * Setting this to true captures all responses (including errors)
         * So we can handle them gracefully
         */
        "muteHttpExceptions": true
    };

    // ========================================================================
    // STEP 5: Make the API Call and Handle Response
    // ========================================================================
    
    try {
        /**
         * Send the HTTP POST request to the Chrome UX Report API
         * This actually fetches the data
         */
        var response = UrlFetchApp.fetch(apiEndpoint, requestOptions);
        
        /**
         * Get the HTTP response code
         * 200 = success
         * 404 = not found (no data for this domain)
         * Other codes = various errors
         */
        var responseCode = response.getResponseCode();
        
        /**
         * Parse the JSON response into a JavaScript object
         * This converts the JSON string response into usable data
         */
        var apiResponse = JSON.parse(response.getContentText());

        /**
         * Log the full API response for debugging purposes
         * Useful for understanding what data came back
         * Can be very long, but helpful when troubleshooting
         */
        Logger.log("API Response for " + domain + " (" + webVital + "): " + JSON.stringify(apiResponse));

        // ====================================================================
        // Check for Success vs Error
        // ====================================================================
        
        /**
         * Check if response code indicates an error (not 200-299 range)
         * Successful responses are in the 200-299 range (200, 201, 204, etc.)
         */
        if (responseCode < 200 || responseCode >= 300) {
            
            /**
             * Handle "Not Found" error (404 or "NOT_FOUND" in response)
             * This means Chrome UX Report has no data for this domain
             * 
             * Common reasons for "No Data":
             * - Domain has low traffic
             * - Brand new domain (takes time for data to accumulate)
             * - Wrong domain format (try with/without www)
             * - Domain doesn't use Chrome (all Chrome extensions disabled, etc.)
             */
            if (responseCode === 404 || (apiResponse["error"] && apiResponse["error"]["status"] === "NOT_FOUND")) {
                Logger.log("⚠️  No Chrome UX Report data for: " + domain);
                return "No Data";
            }
            
            /**
             * Handle other API errors (500s, 403 permission denied, etc.)
             * Log the error for debugging
             */
            Logger.log("❌ API Error for " + domain + ": HTTP " + responseCode + " - " + JSON.stringify(apiResponse));
            return "API Error";
        }

        // ====================================================================
        // Extract the Metric Value from Response
        // ====================================================================
        
        /**
         * Navigate through the API response to find the metric value
         * 
         * RESPONSE STRUCTURE:
         * {
         *   "record": {
         *     "metrics": {
         *       "largest_contentful_paint": {
         *         "percentiles": {
         *           "p75": 2500      <-- This is what we want!
         *         }
         *       }
         *     }
         *   }
         * }
         * 
         * PATH TO VALUE:
         * apiResponse["record"]["metrics"][webVital]["percentiles"]["p75"]
         * 
         * WHAT IS P75 (75th PERCENTILE)?
         * - 75% of real page loads perform better than this value
         * - Filters out the top 25% of fast page loads (outliers)
         * - More realistic for average user experience than median
         * - This is Google's official metric for Core Web Vitals
         */
        var metricValue = (apiResponse["record"] && 
                          apiResponse["record"]["metrics"] && 
                          apiResponse["record"]["metrics"][webVital] && 
                          apiResponse["record"]["metrics"][webVital]["percentiles"] &&
                          apiResponse["record"]["metrics"][webVital]["percentiles"]["p75"]) 
                          || "No Data";
        
        /**
         * Log the extracted value for verification
         * Confirms that the value was successfully extracted
         */
        Logger.log("✓ Metric value for " + domain + " (" + webVital + "): " + metricValue);
        
        /**
         * Return the metric value to be inserted into the sheet
         * This value will become a cell value in your spreadsheet
         */
        return metricValue;

    } catch (error) {
        /**
         * Catch any errors that occur during API communication or JSON parsing
         * 
         * Possible errors:
         * - Network errors (server unavailable)
         * - JSON parsing errors (malformed response)
         * - Timeout errors (request took too long)
         */
        Logger.log("❌ ERROR for " + domain + " (" + webVital + "): " + error);
        
        /**
         * Return "Error" string so the sheet reflects that data couldn't be fetched
         * This prevents the script from crashing and allows it to continue
         */
        return "Error";
    }
}

// ============================================================================
// DEBUGGING HELPER FUNCTIONS
// ============================================================================

/**
 * FUNCTION: debugDomainReading()
 * 
 * USE THIS FIRST if you suspect domains aren't being read correctly.
 * 
 * WHAT IT DOES:
 * - Reads every cell in column A of sheet "A"
 * - Shows the exact cell value, data type, and character count
 * - Helps identify hidden spaces, formatting issues, or missing data
 * 
 * HOW TO RUN:
 * 1. Select 'debugDomainReading' from the function dropdown
 * 2. Click the Play (▶) button
 * 3. View logs: Click View → Logs
 * 
 * WHAT TO LOOK FOR:
 * - Cells with unexpected whitespace before/after values
 * - Empty cells where you thought you had data
 * - Data type mismatches (should all be strings)
 * - Character count anomalies
 */
function debugDomainReading() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var domainSheet = ss.getSheetByName("A");

    if (domainSheet === null) {
        Logger.log("❌ ERROR: No sheet named 'A'");
        return;
    }

    var lastRow = domainSheet.getLastRow();
    Logger.log("🔍 DEBUG: Last row in sheet 'A': " + lastRow);
    
    /**
     * Get all data from column A (both header and domains)
     * This includes every cell, even empty ones
     */
    var allData = domainSheet.getRange(1, 1, lastRow, 1).getValues();
    
    /**
     * Loop through each cell and display details
     * This helps you see exactly what's stored in each cell
     */
    for (var i = 0; i < allData.length; i++) {
        var cellValue = allData[i][0];
        var cellType = typeof cellValue;
        var cellLength = cellValue ? cellValue.toString().length : 0;
        
        Logger.log("Row " + (i + 1) + ": [" + cellValue + "] (type: " + cellType + ", length: " + cellLength + ")");
    }
}

/**
 * FUNCTION: testSingleDomain()
 * 
 * USE THIS before running the main script to verify API setup and domain formatting.
 * 
 * WHAT IT DOES:
 * - Tests one domain with "https://www" prefix
 * - Tests the same domain without "www"
 * - Fetches the LCP metric for both
 * - Shows the raw API responses
 * 
 * WHY THIS HELPS:
 * - Confirms your API key is valid
 * - Tests domain formatting
 * - Shows if data is available for your domains
 * 
 * HOW TO RUN:
 * 1. Update testDomain1 and testDomain2 with YOUR actual domains
 * 2. Select 'testSingleDomain' from the function dropdown
 * 3. Click the Play (▶) button
 * 4. View logs: Click View → Logs
 * 
 * WHAT TO LOOK FOR:
 * - Successful: Logs show numeric values (e.g., 2500)
 * - No data: Logs show "No Data" message
 * - API error: Check if API key is correct
 */
function testSingleDomain() {
    /**
     * CUSTOMIZE THESE: Replace with your actual domains to test
     * Try one with www and one without to see if data availability differs
     */
    var testDomain1 = "https://www.example.com";  // ← REPLACE WITH YOUR DOMAIN
    var testDomain2 = "https://example.com";      // ← REPLACE WITH YOUR DOMAIN
    
    Logger.log("🧪 TEST 1: Testing domain WITH www");
    var result1 = getWebVitalData(testDomain1, "largest_contentful_paint");
    Logger.log("Result: " + result1);
    
    Logger.log("🧪 TEST 2: Testing domain WITHOUT www");
    var result2 = getWebVitalData(testDomain2, "largest_contentful_paint");
    Logger.log("Result: " + result2);
    
    Logger.log("✅ Test complete. If you see numeric values, your API setup is working!");
}