GCLID Decoder

Extract timestamps and decode Google Click IDs

Result

Custom Google Sheets Formula

Use our custom Google Apps Script to decode GCLIDs directly in Google Sheets with simple formulas like =GCLID_TIMESTAMP(A1)

1. Open Your Google Sheet & Add Script

In your Google Sheet, go to Extensions → Apps Script (or Tools → Script editor in older versions)

Delete the default function myFunction() {} and replace with:

/**
 * GCLID Decoder for Google Sheets by Martin Aberastegue
 * https://www.martinaberastegue.com/
 * Custom function to extract timestamps from Google Click IDs
 * 
 * Usage in Google Sheets:
 * =GCLID_TIMESTAMP("your_gclid_here")
 * =GCLID_TIMESTAMP("your_gclid_here", "gmt") - for GMT format
 * =GCLID_TIMESTAMP("your_gclid_here", "local") - for local format
 * =GCLID_TIMESTAMP("your_gclid_here", "unix") - for Unix timestamp
 */

/**
 * Extract timestamp from GCLID
 * @param {string} gclid The Google Click ID to decode
 * @param {string} format Optional format: "unix", "gmt", "local" (default: "local")
 * @return {string|number} The extracted timestamp in requested format
 * @customfunction
 */
function GCLID_TIMESTAMP(gclid, format = "local") {
  try {
    if (!gclid || typeof gclid !== 'string') {
      return "ERROR: Invalid GCLID";
    }
    
    // Clean the GCLID
    gclid = gclid.trim();
    
    // Decode from base64url
    const binary = base64urlDecode(gclid);
    if (!binary) {
      return "ERROR: Invalid base64url encoding";
    }
    
    // Parse as protobuf
    const data = parseProtobuf(binary);
    
    // Extract timestamps
    const timestamps = extractTimestamps(data);
    
    if (timestamps.length === 0) {
      return "ERROR: No timestamp found";
    }
    
    const timestamp = timestamps[0];
    
    // Return in requested format
    switch (format.toLowerCase()) {
      case "unix":
        return timestamp.unix_timestamp;
      case "gmt":
        return timestamp.gmt_date;
      case "local":
      default:
        return timestamp.local_date;
    }
    
  } catch (error) {
    return "ERROR: " + error.message;
  }
}

/**
 * Get all GCLID information as JSON
 * @param {string} gclid The Google Click ID to decode
 * @return {string} JSON string with all extracted information
 * @customfunction
 */
function GCLID_INFO(gclid) {
  try {
    if (!gclid || typeof gclid !== 'string') {
      return "ERROR: Invalid GCLID";
    }
    
    gclid = gclid.trim();
    const binary = base64urlDecode(gclid);
    if (!binary) {
      return "ERROR: Invalid base64url encoding";
    }
    
    const data = parseProtobuf(binary);
    const timestamps = extractTimestamps(data);
    
    const result = {
      gclid: gclid,
      char_length: gclid.length,
      timestamps: timestamps,
      raw_data: data
    };
    
    return JSON.stringify(result, null, 2);
    
  } catch (error) {
    return "ERROR: " + error.message;
  }
}

/**
 * Decode base64url string to Uint8Array
 */
function base64urlDecode(data) {
  try {
    // Add padding if needed
    const remainder = data.length % 4;
    if (remainder) {
      data += '='.repeat(4 - remainder);
    }
    
    // Convert base64url to base64
    data = data.replace(/-/g, '+').replace(/_/g, '/');
    
    // Decode base64 to blob, then get bytes
    const blob = Utilities.base64Decode(data);
    
    // Convert blob to Uint8Array
    const bytes = new Uint8Array(blob.length);
    for (let i = 0; i < blob.length; i++) {
      bytes[i] = blob[i] & 0xFF; // Ensure byte value
    }
    
    return bytes;
  } catch (e) {
    // Fallback method using manual base64 decoding
    return manualBase64Decode(data);
  }
}

/**
 * Manual base64 decoding fallback
 */
function manualBase64Decode(data) {
  try {
    // Add padding if needed
    const remainder = data.length % 4;
    if (remainder) {
      data += '='.repeat(4 - remainder);
    }
    
    // Convert base64url to base64
    data = data.replace(/-/g, '+').replace(/_/g, '/');
    
    // Base64 decode table
    const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
    const lookup = {};
    for (let i = 0; i < chars.length; i++) {
      lookup[chars[i]] = i;
    }
    
    const bytes = [];
    for (let i = 0; i < data.length; i += 4) {
      const a = lookup[data[i]] || 0;
      const b = lookup[data[i + 1]] || 0;
      const c = lookup[data[i + 2]] || 0;
      const d = lookup[data[i + 3]] || 0;
      
      const bitmap = (a << 18) | (b << 12) | (c << 6) | d;
      
      bytes.push((bitmap >> 16) & 255);
      if (data[i + 2] !== '=') bytes.push((bitmap >> 8) & 255);
      if (data[i + 3] !== '=') bytes.push(bitmap & 255);
    }
    
    return new Uint8Array(bytes);
  } catch (e) {
    return null;
  }
}

/**
 * Parse protobuf binary data
 */
function parseProtobuf(data) {
  const result = {};
  let offset = 0;
  const length = data.length;
  
  while (offset < length) {
    // Read field header (tag + wire type)
    const varintResult = decodeVarint(data, offset);
    if (!varintResult) break;
    
    const header = varintResult.value;
    offset = varintResult.offset;
    
    const fieldNumber = header >> 3;
    const wireType = header & 0x7;
    
    switch (wireType) {
      case 0: // varint
        const varintFieldResult = decodeVarint(data, offset);
        if (varintFieldResult) {
          result[fieldNumber] = varintFieldResult.value;
          offset = varintFieldResult.offset;
        }
        break;
        
      case 1: // 64-bit
        if (offset + 8 <= length) {
          result[fieldNumber] = data.slice(offset, offset + 8);
          offset += 8;
        }
        break;
        
      case 2: // length-delimited
        const lengthResult = decodeVarint(data, offset);
        if (lengthResult) {
          const fieldLength = lengthResult.value;
          offset = lengthResult.offset;
          
          if (offset + fieldLength <= length) {
            const fieldData = data.slice(offset, offset + fieldLength);
            
            // Try to parse as nested protobuf
            try {
              const nested = parseProtobuf(fieldData);
              if (Object.keys(nested).length > 0) {
                result[fieldNumber] = nested;
              } else {
                result[fieldNumber] = fieldData;
              }
            } catch (e) {
              result[fieldNumber] = fieldData;
            }
            offset += fieldLength;
          }
        }
        break;
        
      case 5: // 32-bit
        if (offset + 4 <= length) {
          result[fieldNumber] = data.slice(offset, offset + 4);
          offset += 4;
        }
        break;
        
      default:
        // Unknown wire type, skip
        offset++;
        break;
    }
  }
  
  return result;
}

/**
 * Decode varint from binary data
 */
function decodeVarint(data, offset) {
  const length = data.length;
  let value = 0;
  let shift = 0;
  
  while (offset < length) {
    const byte = data[offset];
    offset++;
    
    // Extract the 7 data bits
    const dataBits = byte & 0x7F;
    
    // Add to result
    value += dataBits * Math.pow(2, shift);
    
    // If MSB is not set, we're done
    if ((byte & 0x80) === 0) {
      return { value: value, offset: offset };
    }
    
    shift += 7;
    
    // Prevent infinite loops
    if (shift >= 64) {
      return null;
    }
  }
  
  return null;
}

/**
 * Extract timestamps from parsed protobuf data
 */
function extractTimestamps(data) {
  const timestamps = [];
  extractTimestampsRecursive(data, timestamps);
  
  // Convert to readable format
  const readable = [];
  for (const ts of timestamps) {
    let unixTimestamp = null;
    
    if (ts > 1000000000 && ts < 2000000000) {
      // Unix timestamp in seconds
      unixTimestamp = ts;
    } else if (ts > 1000000000000 && ts < 2000000000000) {
      // Unix timestamp in milliseconds
      unixTimestamp = ts / 1000;
    } else if (ts > 1000000000000000) {
      // Unix timestamp in microseconds
      unixTimestamp = ts / 1000000;
    }
    
    if (unixTimestamp) {
      const date = new Date(unixTimestamp * 1000);
      const timestampInfo = {
        original: ts,
        unix_timestamp: unixTimestamp,
        gmt_date: date.toISOString().replace('T', ' ').replace('.000Z', ' GMT'),
        local_date: date.toLocaleString()
      };
      
      // Avoid duplicates
      if (!readable.some(r => r.original === ts)) {
        readable.push(timestampInfo);
      }
    }
  }
  
  return readable;
}

/**
 * Recursively extract numeric values that could be timestamps
 */
function extractTimestampsRecursive(data, timestamps) {
  if (Array.isArray(data)) {
    for (const value of data) {
      extractTimestampsRecursive(value, timestamps);
    }
  } else if (typeof data === 'object' && data !== null) {
    for (const key in data) {
      extractTimestampsRecursive(data[key], timestamps);
    }
  } else if (typeof data === 'number' && data > 1000000000) {
    timestamps.push(data);
  }
}

2. Save the Script

Click the Save button (💾) or press Ctrl+S (Cmd+S on Mac)

Give it a name like "GCLID Decoder" when prompted

3. Use the Formulas

Go back to your Google Sheet and use these formulas in any cell:

Basic timestamp:
=GCLID_TIMESTAMP(A1)
Unix timestamp:
=GCLID_TIMESTAMP(A1, "unix")
GMT format:
=GCLID_TIMESTAMP(A1, "gmt")
Complete info:
=GCLID_INFO(A1)

4. Bulk Processing

Put your GCLIDs in column A, use =GCLID_TIMESTAMP(A1) in column B, then drag the formula down to process multiple GCLIDs at once!

💡 Pro tip: The custom functions are now available in this sheet and any new sheets you create in this Google Sheets file.

How GCLID Decoding Works

What is GCLID?

GCLID (Google Click Identifier) is a unique tracking parameter that Google Ads appends to URLs when someone clicks on your ad. It contains encoded information including timestamps, campaign data, and other tracking metadata encoded in Protocol Buffers (protobuf) format.

Decoding Process

This decoder is based on my original PHP implementation, which I later adapted to JavaScript for browser compatibility and Google Apps Script for Google Sheets integration. The core decoding logic remains the same across all three implementations. For this explanation, I'll show you the PHP code since it's the most readable and well-documented version.

The decoding happens in 4 main steps:

1. Base64URL Decoding

GCLID uses base64url encoding (URL-safe base64). We first convert it to binary data:

function base64urlDecode(data) {
    // Add padding if needed
    const remainder = data.length % 4;
    if (remainder) {
        data += '='.repeat(4 - remainder);
    }
    
    // Convert base64url to base64
    data = data.replace(/-/g, '+').replace(/_/g, '/');
    
    // Decode base64 to binary
    const binaryString = atob(data);
    const bytes = new Uint8Array(binaryString.length);
    for (let i = 0; i < binaryString.length; i++) {
        bytes[i] = binaryString.charCodeAt(i);
    }
    
    return bytes;
}

2. Protocol Buffers Parsing

The binary data is structured as protobuf messages. We parse each field using wire types:

function parseProtobuf(data) {
    const result = {};
    let offset = 0;
    const length = data.length;
    
    while (offset < length) {
        // Read field header (tag + wire type)
        const varintResult = decodeVarint(data, offset);
        const header = varintResult.value;
        offset = varintResult.offset;
        
        const fieldNumber = header >> 3;
        const wireType = header & 0x7;
        
        switch (wireType) {
            case 0: // varint (timestamps often here)
            case 1: // 64-bit
            case 2: // length-delimited (nested data)
            case 5: // 32-bit
        }
    }
    
    return result;
}

3. Varint Decoding

Protobuf uses variable-length integers (varints) for efficient encoding. Each byte uses 7 bits for data and 1 bit as continuation flag:

function decodeVarint(data, offset) {
    let value = 0;
    let shift = 0;
    
    while (offset < data.length) {
        const byte = data[offset];
        offset++;
        
        // Extract the 7 data bits
        const dataBits = byte & 0x7F;
        
        // Add to result
        value += dataBits * Math.pow(2, shift);
        
        // If MSB is not set, we're done
        if ((byte & 0x80) === 0) {
            return { value: value, offset: offset };
        }
        
        shift += 7;
    }
}

4. Timestamp Extraction

We recursively search for numeric values that look like Unix timestamps and convert them to readable dates:

function extractTimestamps(data) {
    const timestamps = [];
    extractTimestampsRecursive(data, timestamps);
    
    const readable = [];
    for (const ts of timestamps) {
        let unixTimestamp = null;
        
        if (ts > 1000000000 && ts < 2000000000) {
            // Unix timestamp in seconds
            unixTimestamp = ts;
        } else if (ts > 1000000000000 && ts < 2000000000000) {
            // Unix timestamp in milliseconds  
            unixTimestamp = ts / 1000;
        } else if (ts > 1000000000000000) {
            // Unix timestamp in microseconds
            unixTimestamp = ts / 1000000;
        }
        
        if (unixTimestamp) {
            const date = new Date(unixTimestamp * 1000);
            const timestampInfo = {
                original: ts,
                unix_timestamp: unixTimestamp,
                gmt_date: date.toISOString().replace('T', ' ').replace('.000Z', ' GMT'),
                local_date: date.toLocaleString()
            };
            readable.push(timestampInfo);
        }
    }
    
    return readable;
}

What Information Can Be Extracted?

  • Click Timestamp: When the ad was clicked (in various precisions)
  • Campaign Data: Internal Google Ads campaign identifiers
  • Attribution Data: Information for conversion tracking
  • Session Data: Browser session and user interaction data

Technical Details

  • Encoding: Base64URL → Binary → Protocol Buffers
  • Wire Types: 0=varint, 1=64bit, 2=length-delimited, 5=32bit
  • Timestamp Formats: Unix seconds, milliseconds, or microseconds
  • Nested Messages: Recursive parsing for complex data structures