# Google Sheets Integration Workflow


**Last Updated:** 2025-11-20

## Overview

The lead capture system syncs all form submissions to Google Sheets for backup storage and easy access. This document explains the authentication, operations, and error handling.

## Authentication

### Service Account

**Service Account Email:** `ordio-webinar-sheets@ordio-472310.iam.gserviceaccount.com`

**Project ID:** `ordio-472310`

**Authentication Method:** JWT (JSON Web Token) via service account credentials

### Credentials Location

1. **File-based (preferred):** `v2/config/google-sheets-credentials.json`
2. **Embedded fallback:** Hardcoded in `lead-capture.php` (if file doesn't exist)

### Credentials Structure

```json
{
    "type": "service_account",
    "project_id": "ordio-472310",
    "private_key_id": "...",
    "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
    "client_email": "ordio-webinar-sheets@ordio-472310.iam.gserviceaccount.com",
    "client_id": "...",
    "auth_uri": "https://accounts.google.com/o/oauth2/auth",
    "token_uri": "https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
    "client_x509_cert_url": "..."
}
```

## Authentication Flow

### Step 1: Generate JWT

```php
function generateJWT($credentials) {
    $header = json_encode(['typ' => 'JWT', 'alg' => 'RS256']);
    $now = time();
    $payload = json_encode([
        'iss' => $credentials['client_email'],
        'scope' => 'https://www.googleapis.com/auth/spreadsheets',
        'aud' => 'https://oauth2.googleapis.com/token',
        'exp' => $now + 3600, // 1 hour expiration
        'iat' => $now
    ]);
    
    // Base64 encode header and payload
    $base64Header = str_replace(['+', '/', '='], ['-', '_', ''], base64_encode($header));
    $base64Payload = str_replace(['+', '/', '='], ['-', '_', ''], base64_encode($payload));
    
    // Sign with private key
    $signature = '';
    $signatureData = $base64Header . '.' . $base64Payload;
    openssl_sign($signatureData, $signature, $credentials['private_key'], OPENSSL_ALGO_SHA256);
    
    $base64Signature = str_replace(['+', '/', '='], ['-', '_', ''], base64_encode($signature));
    return $base64Header . '.' . $base64Payload . '.' . $base64Signature;
}
```

### Step 2: Exchange JWT for Access Token

```php
function getAccessToken($jwtToken) {
    $url = 'https://oauth2.googleapis.com/token';
    $data = [
        'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
        'assertion' => $jwtToken
    ];
    
    $options = [
        'http' => [
            'header' => "Content-type: application/x-www-form-urlencoded\r\n",
            'method' => 'POST',
            'content' => http_build_query($data)
        ]
    ];
    
    $context = stream_context_create($options);
    $result = file_get_contents($url, false, $context);
    $response = json_decode($result, true);
    
    return $response['access_token'];
}
```

### Step 3: Use Access Token

Access token valid for 1 hour. Use for all API requests:

```php
$headers = [
    "Authorization: Bearer $accessToken",
    "Content-Type: application/json"
];
```

## Spreadsheet Configuration

### Spreadsheet ID

```
1gHzf0CcCACPPLo3Xb4aBcO9cguraVa7eeLyYZvqCBAk
```

**Location:** Hardcoded in `lead-capture.php`

**How to find:** Google Sheets → File → Share → Copy link → Extract ID from URL

### Column Structure

| Column | Field | Description |
|--------|-------|-------------|
| A | Timestamp | Berlin timezone (Y-m-d H:i:s) |
| B | Name | User's name |
| C | Phone | Normalized phone (E.164) |
| D | Email | User's email (empty in Step 1) |
| E | Notes | User's notes (empty in Step 1) |
| F | Call Preference | When to call (empty in Step 1) |
| G | Status | "Step 1 Complete" or "Step 2 Complete" |
| H | Source Page | Page URL where form was submitted |
| I | Lead Source | Detected lead source (Paid Search, Organic, etc.) |
| J | Lead ID | Unique lead identifier (LC1234567890) |
| K | Trigger Type | How popup was triggered (time, scroll, exit-intent, manual) |
| L | Source Page Label | Human-readable page name |
| M | Page Context | Context label for tracking |

## Operations

### Step 1: Append Row

```php
function appendToSheets($spreadsheetId, $range, $values, $accessToken) {
    $url = "https://sheets.googleapis.com/v4/spreadsheets/$spreadsheetId/values/$range:append";
    $url .= '?valueInputOption=RAW&insertDataOption=INSERT_ROWS';
    
    $data = ['values' => $values];
    
    $options = [
        'http' => [
            'header' => [
                "Authorization: Bearer $accessToken",
                "Content-Type: application/json"
            ],
            'method' => 'POST',
            'content' => json_encode($data)
        ]
    ];
    
    $context = stream_context_create($options);
    $result = file_get_contents($url, false, $context);
    $response = json_decode($result, true);
    
    if (isset($response['error'])) {
        throw new Exception('Google Sheets API error: ' . json_encode($response['error']));
    }
    
    return $response;
}
```

**Usage:**
```php
$values = [[
    $berlinTime->format('Y-m-d H:i:s'), // A
    $name,                                // B
    $phone,                               // C
    '',                                   // D (email - empty in Step 1)
    '',                                   // E (notes - empty in Step 1)
    '',                                   // F (call preference - empty in Step 1)
    'Step 1 Complete',                    // G
    $sourcePage,                          // H
    $leadSource,                          // I
    $leadId,                              // J
    $triggerType,                         // K
    $sourcePageLabel,                     // L
    $pageContextLabel                     // M
]];

$result = appendToSheets($spreadsheetId, 'A:M', $values, $accessToken);
```

### Step 2: Read Row

```php
function getLeadDataFromSheets($spreadsheetId, $leadId, $accessToken) {
    $url = "https://sheets.googleapis.com/v4/spreadsheets/$spreadsheetId/values/A:M";
    
    $options = [
        'http' => [
            'header' => "Authorization: Bearer $accessToken\r\n",
            'method' => 'GET'
        ]
    ];
    
    $context = stream_context_create($options);
    $result = file_get_contents($url, false, $context);
    $response = json_decode($result, true);
    
    if (isset($response['error'])) {
        throw new Exception('Google Sheets API error: ' . json_encode($response['error']));
    }
    
    $rows = $response['values'] ?? [];
    foreach ($rows as $index => $row) {
        // Check if this row contains our lead ID in column J (index 9)
        if (isset($row[9]) && $row[9] === $leadId) {
            return [
                'found' => true,
                'row' => $index + 1, // +1 because sheets are 1-indexed
                'data' => [
                    'timestamp' => $row[0] ?? '',
                    'name' => $row[1] ?? '',
                    'phone' => $row[2] ?? '',
                    'email' => $row[3] ?? '',
                    'notes' => $row[4] ?? '',
                    'call_preference' => $row[5] ?? '',
                    'status' => $row[6] ?? '',
                    'source_page' => $row[7] ?? '',
                    'lead_source' => $row[8] ?? '',
                    'lead_id' => $row[9] ?? '',
                    'trigger' => $row[10] ?? '',
                    'source_page_label' => $row[11] ?? '',
                    'page_context' => $row[12] ?? ''
                ]
            ];
        }
    }
    
    return ['found' => false, 'row' => null];
}
```

### Step 2: Update Row

```php
function updateLeadInSheets($spreadsheetId, $rowNumber, $email, $notes, $callPreference, $accessToken) {
    $range = "D{$rowNumber}:F{$rowNumber}"; // Update columns D, E, F
    
    $values = [[
        $email,          // D
        $notes,          // E
        $callPreference  // F
    ]];
    
    $url = "https://sheets.googleapis.com/v4/spreadsheets/$spreadsheetId/values/$range";
    $url .= '?valueInputOption=RAW';
    
    $data = ['values' => $values];
    
    $options = [
        'http' => [
            'header' => [
                "Authorization: Bearer $accessToken",
                "Content-Type: application/json"
            ],
            'method' => 'PUT',
            'content' => json_encode($data)
        ]
    ];
    
    $context = stream_context_create($options);
    $result = file_get_contents($url, false, $context);
    $response = json_decode($result, true);
    
    if (isset($response['error'])) {
        throw new Exception('Google Sheets API error: ' . json_encode($response['error']));
    }
    
    return $response;
}
```

## Error Handling

### Authentication Errors

**Symptoms:**
- "Invalid credentials" error
- "Failed to get access token" error

**Solutions:**
1. Verify credentials file exists: `v2/config/google-sheets-credentials.json`
2. Verify credentials are valid (not expired, correct format)
3. Verify service account has access to spreadsheet
4. Check logs for detailed error messages

### API Errors

**Symptoms:**
- "Failed to append to Google Sheets" error
- "Google Sheets API error" in logs

**Solutions:**
1. Check spreadsheet ID is correct
2. Verify service account has edit permissions
3. Check spreadsheet isn't full (row limit)
4. Verify range is correct (A:M for append, D:F for update)

### Network Errors

**Symptoms:**
- Timeout errors
- Connection refused

**Solutions:**
1. Check internet connectivity
2. Verify Google Sheets API is accessible
3. Check firewall rules
4. Retry with exponential backoff

## Best Practices

1. **Always authenticate before operations:** Generate new access token if expired
2. **Handle errors gracefully:** Log errors but don't fail form submission
3. **Use fallback:** If Sheets fails, HubSpot is primary system of record
4. **Normalize data:** Ensure phone numbers, timestamps are consistent
5. **Log operations:** Track all append/update operations in logs

## Permissions

### Service Account Permissions

Service account needs:
- **Viewer** access to spreadsheet (minimum)
- **Editor** access recommended (for append/update operations)

### How to Grant Access

1. Open Google Sheets spreadsheet
2. Click **Share** button
3. Add service account email: `ordio-webinar-sheets@ordio-472310.iam.gserviceaccount.com`
4. Set permission to **Editor**
5. Click **Send**

## Testing

### Test Authentication

```php
// In lead-capture.php, add debug endpoint
if (isset($_GET['debug_sheets']) && $_GET['debug_sheets'] === 'test') {
    $credentials = json_decode(file_get_contents(__DIR__ . '/../config/google-sheets-credentials.json'), true);
    $token = generateJWT($credentials);
    $accessToken = getAccessToken($token);
    
    echo json_encode([
        'debug_test' => true,
        'auth_success' => !empty($accessToken),
        'token_preview' => substr($accessToken, 0, 20) . '...'
    ]);
    exit;
}
```

### Test Append Operation

```bash
# Submit Step 1 form
curl -X POST http://localhost:8003/v2/api/lead-capture.php \
  -H "Content-Type: application/json" \
  -d '{
    "step": "1",
    "name": "Test User",
    "phone": "+49123456789",
    "source_page": "/test",
    "lead_source": "Test"
  }'

# Check spreadsheet for new row
```

### Test Update Operation

```bash
# Submit Step 2 form (use lead ID from Step 1)
curl -X POST http://localhost:8003/v2/api/lead-capture.php \
  -H "Content-Type: application/json" \
  -d '{
    "step": "2",
    "lead_id": "LC1234567890",
    "email": "test@example.com",
    "notes": "Test notes",
    "call_preference": "Sofort (heute noch)"
  }'

# Check spreadsheet for updated row
```

## Troubleshooting

### Credentials Not Found

**Error:** "Invalid credentials - missing private_key or client_email"

**Solution:**
1. Verify `v2/config/google-sheets-credentials.json` exists
2. Verify file is readable (permissions)
3. Verify JSON format is valid
4. Check embedded credentials as fallback

### Spreadsheet Not Accessible

**Error:** "Failed to read from Google Sheets"

**Solution:**
1. Verify spreadsheet ID is correct
2. Verify service account has access
3. Check spreadsheet isn't deleted
4. Verify API is enabled in Google Cloud Console

### Row Not Found

**Error:** "Lead not found for update"

**Solution:**
1. Verify lead ID matches spreadsheet
2. Check if row exists (search in spreadsheet)
3. Verify column J contains lead ID
4. Check if row was deleted

## Related Documentation

- [Architecture Overview](./ARCHITECTURE.md) - System architecture
- [HubSpot Workflow](./HUBSPOT_WORKFLOW.md) - HubSpot integration
- [Troubleshooting Guide](./TROUBLESHOOTING.md) - Common issues