# Google Sheets Integration

**Last Updated:** 2026-03-21

Comprehensive guide for Google Sheets template integration, including setup, generation, sync, and API usage.

## Overview

The Google Sheets integration allows users to access templates in both Excel (.xlsx) and Google Sheets formats. Templates are automatically generated from the same JSON definitions used for Excel templates, ensuring consistency between formats while adapting to each platform's strengths.

## Architecture

### Components

1. **Google Sheets Generator** (`v2/systems/excel-template-generator/helpers/google-sheets-generator.php`)
   - Converts template JSON definitions to Google Sheets format
   - Handles formulas, formatting, data validation, and sheet structure

2. **Google Drive Helpers** (`v2/helpers/google-drive-helpers.php`)
   - Manages template storage in Google Drive
   - Handles template creation, updates, and copy URL generation

3. **Google Sheets Config** (`v2/config/google-sheets-config.php`)
   - Configuration constants and helper functions
   - Template ID mapping management

4. **Sync System** (`v2/scripts/templates/sync-google-sheets-templates.php`)
   - Detects changes in template definitions
   - Synchronizes Google Sheets versions with Excel templates

5. **API Endpoints**
   - `/v2/api/get-google-sheets-template.php` - Get copy URL for a template
   - `/v2/api/template-options.php` - Get available formats (Excel/Google Sheets)

## Setup

### Prerequisites

1. **Google Cloud Project**
   - Google Sheets API enabled
   - Google Drive API enabled
   - Service account created: `ordio-seo-analytics@ordio-472310.iam.gserviceaccount.com`

2. **Service Account Permissions**
   - Service account must have access to Drive folder: `1AgFeV35J44nzi6QXbvs1O1cPEPtFv4uD`
   - **Important:** The folder should be owned by a user account (not the service account)
   - Share the folder with the service account email with "Editor" permissions
   - Files created in the folder will count against the folder owner's quota, not the service account's quota

3. **Credentials**
   - Service account JSON key file at: `v2/config/google-api-credentials.json`
   - Must include: `type`, `project_id`, `private_key`, `client_email`

### Setup Steps

1. **Enable APIs in Google Cloud Console**
   - Go to [Google Cloud Console](https://console.cloud.google.com/)
   - Navigate to "APIs & Services" > "Library"
   - Enable:
     - [Google Sheets API](https://console.cloud.google.com/apis/library/sheets.googleapis.com)
     - [Google Drive API](https://console.cloud.google.com/apis/library/drive.googleapis.com)

2. **Grant Drive Folder Access**
   - Open the Drive folder: https://drive.google.com/drive/folders/1AgFeV35J44nzi6QXbvs1O1cPEPtFv4uD
   - Click "Share" button
   - Add service account email: `ordio-seo-analytics@ordio-472310.iam.gserviceaccount.com`
   - Set permission to "Editor"
   - Click "Send" (you can uncheck "Notify people" since it's a service account)
   - **Important:** The folder must exist and be accessible. If you get a 404 error, verify the folder ID is correct.

3. **Verify Setup**
   ```bash
   php v2/scripts/templates/verify-google-sheets-setup.php
   ```
   
   This script checks:
   - Credentials file exists and is valid
   - API clients can be created
   - Drive folder is accessible
   - Sheets API is enabled
   - Configuration is valid

4. **Generate Templates**
   ```bash
   # Generate all templates
   php v2/scripts/templates/generate-google-sheets-template.php --all
   
   # Or generate a single template
   php v2/scripts/templates/generate-google-sheets-template.php --template=dienstplan-excel-vorlage
   ```

### Configuration

Configuration is handled in:
- `v2/config/google-api-credentials.php` - API client setup
- `v2/config/google-sheets-config.php` - Google Sheets-specific config

**Constants:**
- `GOOGLE_SHEETS_TEMPLATES_FOLDER_ID` - Drive folder ID for templates
- `GOOGLE_SHEETS_SERVICE_ACCOUNT_EMAIL` - Service account email

### Template Titles

All Google Sheets templates are automatically titled with " - Ordio" suffix:
- Format: `[Template Name] - Ordio`
- Example: "Dienstplan Basis Vorlage - Ordio"
- The suffix is automatically added when creating new spreadsheets
- The title is automatically updated when syncing existing spreadsheets
- Duplicate suffixes are prevented (if title already ends with " - Ordio", it won't be added again)
- `GOOGLE_SHEETS_TEMPLATE_IDS_FILE` - Path to template ID mapping file

## Usage

### Generating Google Sheets Templates

**Single Template:**
```bash
php v2/scripts/templates/generate-google-sheets-template.php --template=dienstplan-excel-vorlage
```

**All Templates:**
```bash
php v2/scripts/templates/generate-google-sheets-template.php --all
```

### Syncing Templates

**Check Sync Status:**
```bash
# All published templates
php v2/scripts/templates/sync-google-sheets-templates.php --check

# Single template (any status)
php v2/scripts/templates/sync-google-sheets-templates.php --check --template=dienstplan-excel-vorlage
```

**Sync Single Template:**
```bash
php v2/scripts/templates/sync-google-sheets-templates.php --template=dienstplan-excel-vorlage
```

**Sync Multiple Specific Templates:**
```bash
php v2/scripts/templates/sync-google-sheets-templates.php --templates=dienstplan-excel-vorlage,schichtplan-excel-vorlage,lohnabrechnung-vorlage
```

**Sync All Templates:**
```bash
php v2/scripts/templates/sync-google-sheets-templates.php --all
```

**Sync Only Templates That Need Sync** (skips up-to-date, faster):
```bash
php v2/scripts/templates/sync-google-sheets-templates.php --all --only-needed
```

**Force Sync (even if not needed):**
```bash
php v2/scripts/templates/sync-google-sheets-templates.php --template=dienstplan-excel-vorlage --force
php v2/scripts/templates/sync-google-sheets-templates.php --all --force
```

### API Usage

**Get Google Sheets Copy URL:**
```
GET /v2/api/get-google-sheets-template.php?template_id=dienstplan-excel-vorlage
```

Response:
```json
{
  "success": true,
  "template_id": "dienstplan-excel-vorlage",
  "copy_url": "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/copy",
  "spreadsheet_id": "SPREADSHEET_ID"
}
```

**Get Template Options:**
```
GET /v2/api/template-options.php?template_id=dienstplan-excel-vorlage
```

Response:
```json
{
  "success": true,
  "options": {
    "template_id": "dienstplan-excel-vorlage",
    "template_name": "Dienstplan Excel Vorlage",
    "formats": [
      {
        "format": "excel",
        "name": "Excel (.xlsx)",
        "description": "Download als Excel-Datei",
        "action": "download",
        "url": "/v2/api/download-template.php?template_id=dienstplan-excel-vorlage",
        "available": true
      },
      {
        "format": "google_sheets",
        "name": "Google Sheets",
        "description": "Als Google Sheets öffnen und kopieren",
        "action": "copy",
        "url": "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/copy",
        "available": true
      }
    ]
  }
}
```

## Template Conversion

### Formula Translation

Excel formulas use English function names (SUM, IF, etc.), which are compatible with Google Sheets. The generator preserves formulas as-is, with minor adjustments:

- Excel `SUM` → Google Sheets `SUM` (same)
- Excel `IF` → Google Sheets `IF` (same)
- Excel `VLOOKUP` → Google Sheets `VLOOKUP` (same)
- Excel array formulas → Google Sheets `ARRAYFORMULA`

### Formatting Conversion

Style presets are converted to Google Sheets formatting:

- **Colors:** Hex colors converted to RGB (0-1 scale)
- **Fonts:** Inter → Arial (Google Sheets default)
- **Alignment:** Preserved (left, center, right)
- **Borders:** Converted to Google Sheets border format

### Data Validation

- **Dropdown lists:** Converted to Google Sheets data validation (ONE_OF_LIST)
- **Number validation:** Converted to NUMBER_* conditions
- **Date validation:** Converted to DATE_* conditions

### Sheet Structure

- **Multiple sheets:** All sheets from template definition are created
- **Sheet order:** Preserved from template definition
- **Freeze panes:** Converted to Google Sheets freeze rows/columns
- **Column widths:** Converted from points to pixels (approximate)
- **Default Sheet1 removal:** The default "Sheet1" tab is automatically removed when templates have custom sheets defined, ensuring clean, professional templates without unnecessary default tabs

## Registering External/Manual Google Sheets

Some templates use **existing Google Sheets** created manually or outside the normal Excel → Google Sheets sync flow. These are registered with `source: "external"` so the sync script never overwrites them.

### When to Use External Sheets

- You have an existing Google Sheet you want to offer as a template
- The sheet was created manually (not from an Excel definition)
- You do not want sync to overwrite the sheet

### Steps to Register an External Sheet

1. **Add entry to** `v2/data/google-sheets-template-ids.json`:

```json
{
  "template_id": "your-template-slug",
  "spreadsheet_id": "YOUR_SPREADSHEET_ID",
  "copy_url": "https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/copy",
  "source": "external",
  "created_at": "YYYY-MM-DD HH:MM:SS",
  "updated_at": "YYYY-MM-DD HH:MM:SS"
}
```

2. **Ensure the template exists in the registry** – Run `create-new-template.php` if needed, or add a registry entry manually.

3. **Verify copy URL** – The sheet must allow "Anyone with link can view" (or equivalent) so users can create a copy when opening the copy URL.

4. **Sync behavior** – The sync script (`sync-google-sheets-templates.php`) checks for `source: "external"` and skips these templates with reason "External sheet – sync skipped". They are never overwritten.

### Example: Arbeitszeiterfassung Vorlage

The `arbeitszeiterfassung-excel-vorlage` template is registered as external. See `docs/systems/templates/template-data/arbeitszeiterfassung-excel-vorlage/ARBEITSZEITERFASSUNG_EXCEL_VORLAGE.md` for full documentation.

### Use Existing Sheet + Sync (Overwrite)

When you have an **existing Google Sheet** and want to push a template definition to it (overwriting its content):

1. Create the template definition JSON and add registry entry
2. Add entry to `google-sheets-template-ids.json` with the sheet's `spreadsheet_id` and `copy_url` – **do not** set `source: "external"`
3. Run sync with `--force`: `php v2/scripts/templates/sync-google-sheets-templates.php --template=TEMPLATE_ID --force`
4. The sync will overwrite the sheet with content from the definition

**Before the first `--force`:** If the target spreadsheet already contained hand-maintained content, export or screenshot it if you still need it—sync clears and rebuilds tabs from the JSON definition.

**Examples:** `kuendigungsschreiben-vorlage`, `urlaubsplaner-vorlage`, `personalbogen-vorlage`, `stundennachweis-vorlage` – see `docs/systems/templates/template-data/kuendigungsschreiben-vorlage/KUENDIGUNGSSCHREIBEN_VORLAGE.md`, `docs/systems/templates/template-data/urlaubsplaner-vorlage/URLAUBSPLANER_VORLAGE.md`, `docs/systems/templates/template-data/personalbogen-vorlage/PERSONALBOGEN_VORLAGE.md`, and `docs/systems/templates/template-data/stundennachweis-vorlage/STUNDENNACHWEIS_VORLAGE.md`

## Template Creation Workflow

When creating **new templates from Excel definitions** (Phase 5 of [TEMPLATE_CREATION_WORKFLOW.md](../templates/TEMPLATE_CREATION_WORKFLOW.md)):

1. **Generate Excel:** `php v2/systems/excel-template-generator/scripts/template-cli.php generate {slug}`
2. **Generate Google Sheets:** `php v2/scripts/templates/generate-google-sheets-template.php --template={slug}`
3. **Sync:** `php v2/scripts/templates/sync-google-sheets-templates.php --template={slug}`

The sync step updates the timestamp and ensures the copy URL is current. For **external sheets**, skip sync – they are never synced.

## Sync System

### How Sync Works

1. **Detection:** Compares `last_modified` from template registry with `last_synced` from mapping file
2. **Definition File Check:** Also checks file modification time of template definition JSON
3. **Update:** Updates Google Sheets template if changes detected
4. **Timestamp:** Updates sync timestamp after successful sync

### Sync Triggers

- Template definition JSON file modified
- Template registry `last_modified` updated
- Manual sync via CLI script
- Force sync with `--force` flag

### Preserving User Data

When updating templates:
- Only updates template structure (sheets, cells, formulas, formatting)
- Does not overwrite user-entered data in copied templates
- Users' copies remain independent

### Sheet Clearing on Update

**Important:** When updating existing Google Sheets templates, the system now clears each sheet completely before rebuilding it. This ensures:

- ✅ **No stale cells** - Old cells that no longer exist in the template definition are removed
- ✅ **No orphaned formatting** - Formatting from removed cells is cleaned up
- ✅ **No duplicate headers** - Prevents duplicate column headers or labels
- ✅ **Clean conditional formatting** - Old conditional formatting rules are removed before new ones are applied
- ✅ **Consistent structure** - Each update results in a clean, fresh sheet structure matching the template definition

**Process:**
1. Delete all conditional formatting rules (in reverse order to avoid index shifting)
2. Clear all cell values via `batchClear`
3. Clear all formatting and data validation via `updateCells` with empty fields
4. Clear filters
5. Rebuild sheet from template definition

This clearing process happens automatically when `updateGoogleSheetsTemplate()` is called for existing templates. New templates are created fresh without clearing.

## Frontend Integration

### Dual Format Selection UI

Template pages now offer both Excel and Google Sheets download options:

- **Format Selection:** Always visible segmented control in the download form modal
- **Excel Option:** Always available, downloads .xlsx file
- **Google Sheets Option:** Enabled when Google Sheets template is available, opens copy URL in new tab
- **Disabled State:** Google Sheets option is visually disabled (grayed out) when unavailable
- **Hero Badge:** Shows "Excel & Google Sheets" when Google Sheets is available, "XLSX Format" when not

**UI Pattern:**
- Segmented control with two options (Excel and Google Sheets)
- Excel option always enabled
- Google Sheets option enabled/disabled based on availability
- Visual feedback on selection (blue highlight, shadow)
- Tooltip on disabled Google Sheets option explaining unavailability

### User Flow

1. User clicks "Kostenlos downloaden" button
2. Modal opens with format selection (always visible)
3. User selects format (Excel or Google Sheets)
4. User fills form (name, email, phone)
5. On submit:
   - **Excel:** Downloads .xlsx file, HubSpot receives `template_type__c: 'Excel'`
   - **Google Sheets:** Opens copy URL in new tab, HubSpot receives `template_type__c: 'Google Sheets'`

### Format Selection UI

- Segmented control pattern for format selection
- Always visible (not conditional on Google Sheets availability)
- Excel option always enabled
- Google Sheets option enabled/disabled based on availability
- Disabled state shows visual indicator (grayed out, tooltip)
- Button text changes based on selected format
- Validation prevents submission if Google Sheets selected but unavailable

### Analytics Tracking

- Excel downloads: `template_download_click` event
- Google Sheets opens: `template_google_sheets_open` event
- Format selection tracked in GTM Form Tracker

## Troubleshooting

### Common Issues

**1. Service Account Permissions**
- **Error:** "Failed to create Google Sheets API client"
- **Solution:** Verify service account has access to Drive folder. Share folder with service account email.

**2. API Rate Limits**
- **Error:** "Quota exceeded" or "Write requests per minute" (429)
- **Solution:** Sync fewer templates at once: `--templates=template1,template2` instead of `--all`. Wait 1 minute between batches. The script applies a 1s delay after each successful sync to reduce rate limit hits.

**3. Template Not Found**
- **Error:** "Template not found" when accessing copy URL
- **Solution:** Verify template was generated successfully. Check template ID mapping file.

**4. Formatting Differences**
- **Issue:** Google Sheets formatting doesn't match Excel
- **Solution:** Some formatting differences are expected. Google Sheets has limited font support. Colors should match.

**5. Formula Errors**
- **Issue:** Formulas don't work in Google Sheets
- **Solution:** Verify formulas use English function names. Check cell references are valid.

### Debugging

**Check Template Status:**
```bash
php v2/scripts/templates/sync-google-sheets-templates.php --check
```

**Validate Configuration:**
```php
require_once 'v2/config/google-sheets-config.php';
$validation = validateGoogleSheetsConfig();
var_dump($validation);
```

**Test API Endpoints:**
```bash
curl "http://localhost:8003/v2/api/template-options.php?template_id=dienstplan-excel-vorlage"
```

## Best Practices

### Template Design

1. **Use English Functions:** All formulas should use English function names
2. **Consistent Cell Addresses:** Use same cell addresses in both Excel and Google Sheets
3. **Preserve Example Data:** Include example rows to guide users
4. **Brand Consistency:** Maintain Ordio branding in both formats

### Performance

1. **Cache Copy URLs:** Copy URLs are cached in mapping file
2. **Batch Operations:** Use batch sync for multiple templates
3. **Rate Limiting:** Add delays between API calls to avoid rate limits
4. **Error Handling:** Graceful degradation - Excel always available

### Maintenance

1. **Regular Syncs:** Run sync script after template updates
2. **Monitor Errors:** Check logs for API errors
3. **Test Templates:** Verify templates work in both formats
4. **Update Documentation:** Keep docs updated with changes

## File Structure

```
v2/
├── config/
│   ├── google-api-credentials.php      # API client setup
│   └── google-sheets-config.php        # Google Sheets config
├── helpers/
│   └── google-drive-helpers.php        # Drive integration helpers
├── systems/excel-template-generator/
│   └── helpers/
│       └── google-sheets-generator.php # Generator class
├── scripts/templates/
│   ├── generate-google-sheets-template.php  # Generation script
│   └── sync-google-sheets-templates.php     # Sync script
├── api/
│   ├── get-google-sheets-template.php  # Copy URL endpoint
│   └── template-options.php            # Options endpoint
└── data/
    └── google-sheets-template-ids.json # Template ID mapping
```

## Related Documentation

- [Excel Template Generator](../../excel-generator/README.md) - Excel template system
- [Template Creation Workflow](../../templates/TEMPLATE_CREATION_WORKFLOW.md) - Template creation process
- [Google API Credentials](../google-api-credentials.md) - API setup guide

## Support

For issues or questions:
- Check logs: `logs/` directory
- Review error messages in browser console
- Verify service account permissions
- Test API endpoints directly