# google-sheets-templates Full Instructions

## Google Sheets Template Integration

**Complete system for generating and managing Google Sheets versions of Excel templates.**

## Core Principles

### Dual Format Support

- **Excel templates:** Generated via PhpSpreadsheet, served as .xlsx downloads
- **Google Sheets templates:** Generated via Google Sheets API, served as copy URLs
- **Same source:** Both formats use the same JSON template definitions
- **Platform adaptation:** Templates adapted to each platform's strengths

### Sync Strategy

- **Source of truth:** Excel template definitions (JSON files)
- **Sync trigger:** Template definition changes or manual sync
- **Preserve user data:** Updates don't overwrite user-entered data in copied templates
- **Timestamp tracking:** Compare `last_modified` with `last_synced`

### External Sheet Workflow

When a template uses an **existing Google Sheet** (created manually, not from Excel definition):

- Add entry to `v2/data/google-sheets-template-ids.json` with `source: "external"`
- Sync script skips these templates – returns `needs_sync: false`, reason "External sheet – sync skipped"
- Never run sync for external sheets – it would attempt to overwrite the user's sheet
- Copy URL and spreadsheet_id work normally; API endpoints return the copy URL
- Example: `arbeitszeiterfassung-excel-vorlage` – see `ARBEITSZEITERFASSUNG_EXCEL_VORLAGE.md`

### Use Existing Sheet + Sync (Overwrite)

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

- Add entry to `google-sheets-template-ids.json` with `spreadsheet_id` and `copy_url` – **do not** set `source: "external"`
- Run sync with `--force`: `sync-google-sheets-templates.php --template=TEMPLATE_ID --force`
- Sync overwrites the sheet with definition content
- Example: `kuendigungsschreiben-vorlage` – see `KUENDIGUNGSSCHREIBEN_VORLAGE.md`

## Google Sheets Generator

### Class Usage

```php
require_once 'v2/systems/excel-template-generator/helpers/google-sheets-generator.php';

// Create new template
$generator = new OrdioGoogleSheetsGenerator($templateDefinitionPath);
$spreadsheetId = $generator->generate();

// Update existing template
$generator = new OrdioGoogleSheetsGenerator($templateDefinitionPath);
$generator->generate($existingSpreadsheetId);
```

### Formula Handling

**CRITICAL:** Excel formulas use English function names, which are compatible with Google Sheets:

- ✅ `SUM`, `IF`, `VLOOKUP`, `IFERROR` - Same in both platforms
- ✅ Cell references (A1 notation) - Same in both platforms
- ✅ Named ranges - Converted to Google Sheets named ranges
- ⚠️ Array formulas - Use `ARRAYFORMULA` wrapper in Google Sheets if needed

**Formula Translation:**
- Formulas are preserved as-is (English functions work in both)
- No translation needed for most formulas
- Formula references resolved from formula library if available

### Formatting Conversion

**Style Presets:**
- Excel style presets → Google Sheets formatting API
- Colors: Hex (#4D8EF3) → RGB (0-1 scale)
- Fonts: Inter/Calibri → Arial (Google Sheets default)
- Alignment: Preserved (LEFT, CENTER, RIGHT)

**Color Mapping:**
- Ordio blue (#4D8EF3) → RGB(77, 142, 243)
- Ordio green (#10B981) → RGB(16, 185, 129)
- Ordio red (#EF4444) → RGB(239, 68, 68)

**Limitations:**
- Google Sheets has limited font support (Arial, Times, Courier)
- Some advanced formatting may not translate perfectly
- Borders and conditional formatting supported but may differ

### Data Validation

**Dropdown Lists:**
```json
{
  "type": "list",
  "list_values": ["Aktiv", "Inaktiv", "Urlaub"]
}
```
→ Google Sheets `ONE_OF_LIST` validation

**Number Validation:**
```json
{
  "type": "decimal",
  "operator": "greaterThanOrEqual",
  "formula1": "12.82"
}
```
→ Google Sheets `NUMBER_GTE` validation

**Date Validation:**
```json
{
  "type": "date",
  "operator": "between",
  "formula1": "2026-01-01",
  "formula2": "2026-12-31"
}
```
→ Google Sheets `DATE_BETWEEN` validation

### Sheet Structure

**Multiple Sheets:**
- All sheets from template definition are created
- Sheet order preserved from `order` field
- Sheet names preserved exactly

**Freeze Panes:**
```json
{
  "freeze_pane": {
    "cell": "A2"
  }
}
```
→ Google Sheets freeze rows/columns at specified cell

**Column Widths:**
- Excel points → Google Sheets pixels (approximate conversion: points × 7)
- Auto-width handled automatically in Google Sheets

## Google Drive Integration

### Template Storage

**Drive Folder:**
- Folder ID: `1AgFeV35J44nzi6QXbvs1O1cPEPtFv4uD`
- Templates stored as view-only spreadsheets
- Copy URLs generated for user access

**Template ID Mapping:**
- Stored in: `v2/data/google-sheets-template-ids.json`
- Maps template IDs to spreadsheet IDs and copy URLs
- Updated automatically on generation/sync

### Helper Functions

**Create Template:**
```php
$result = createGoogleSheetsTemplate($templateId);
// Returns: ['spreadsheet_id' => '...', 'copy_url' => '...']
```

**Update Template:**
```php
updateGoogleSheetsTemplate($templateId);
```

**Get Copy URL:**
```php
$copyUrl = getGoogleSheetsCopyUrl($templateId);
```

**Check Existence:**
```php
$exists = googleSheetsTemplateExists($templateId);
```

## Sync System

### Sync Detection

**Automatic Detection:**
1. Compare `last_modified` (registry) with `last_synced` (mapping)
2. Check template definition file modification time
3. Sync if template modified after last sync

**Manual Sync:**
```bash
# Check status (all or single)
php v2/scripts/templates/sync-google-sheets-templates.php --check
php v2/scripts/templates/sync-google-sheets-templates.php --check --template=dienstplan-excel-vorlage

# Sync single template
php v2/scripts/templates/sync-google-sheets-templates.php --template=dienstplan-excel-vorlage

# Sync multiple specific templates (comma-separated)
php v2/scripts/templates/sync-google-sheets-templates.php --templates=dienstplan-excel-vorlage,schichtplan-excel-vorlage

# Sync all templates (only those that need sync – faster)
php v2/scripts/templates/sync-google-sheets-templates.php --all --only-needed

# Sync all templates (including up-to-date)
php v2/scripts/templates/sync-google-sheets-templates.php --all

# Force sync (even if up to date)
php v2/scripts/templates/sync-google-sheets-templates.php --all --force
```

### Sync Logic

**When Template Updated:**
1. Detect change (definition file or registry timestamp)
2. Load updated template definition
3. Update Google Sheets spreadsheet (preserves user data)
4. Update sync timestamp

**User Data Preservation:**
- Google Sheets updates only affect template structure
- User-entered data in copied templates is preserved
- Users' copies remain independent

## API Endpoints

### Get Google Sheets Copy URL

**Endpoint:** `/v2/api/get-google-sheets-template.php`

**Parameters:**
- `template_id` (required) - Template ID

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

**Error Handling:**
- 400: Invalid template ID
- 404: Template not found or not published
- 500: Failed to get/create Google Sheets template

### Get Template Options

**Endpoint:** `/v2/api/template-options.php`

**Parameters:**
- `template_id` (required) - Template ID

**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=...",
        "available": true
      },
      {
        "format": "google_sheets",
        "name": "Google Sheets",
        "description": "Als Google Sheets öffnen und kopieren",
        "action": "copy",
        "url": "https://docs.google.com/spreadsheets/d/.../copy",
        "available": true
      }
    ]
  }
}
```

## Frontend Integration

### Template Page Updates

**Format Selection:**
- Radio buttons for Excel/Google Sheets (if Google Sheets available)
- Defaults to Excel if Google Sheets unavailable
- Button text changes based on selection

**User Flow:**
1. User clicks "Kostenlos downloaden"
2. Modal opens with format selection
3. User selects format and fills form
4. On submit:
   - **Excel:** Downloads .xlsx file
   - **Google Sheets:** Opens copy URL in new tab

**Alpine.js Component:**
```javascript
{
  selectedFormat: 'excel', // or 'google_sheets'
  googleSheetsAvailable: false,
  googleSheetsCopyUrl: null,
  
  async checkGoogleSheetsAvailability() { ... },
  async openGoogleSheetsCopy() { ... }
}
```

### Analytics Tracking

**Excel Downloads:**
- Event: `template_download_click`
- Category: `template_download`
- Label: template ID

**Google Sheets Opens:**
- Event: `template_google_sheets_open`
- Category: `template_download`
- Label: template ID

## Platform Differences

### Formulas

**Compatible:**
- Basic functions: SUM, IF, VLOOKUP, COUNT, AVERAGE
- Cell references: A1 notation
- Named ranges: Supported in both

**Differences:**
- Array formulas: Excel uses CSE, Google Sheets uses ARRAYFORMULA
- Some advanced functions may differ
- Date/time functions mostly compatible

### Formatting

**Compatible:**
- Colors: RGB values
- Alignment: Left, center, right
- Number formats: Mostly compatible

**Differences:**
- Fonts: Limited in Google Sheets (Arial, Times, Courier)
- Borders: Supported but may differ
- Conditional formatting: Supported but syntax differs

### Features

**Excel Only:**
- VBA macros
- Advanced charts
- Power Query
- Advanced pivot tables

**Google Sheets Only:**
- Real-time collaboration
- Google Apps Script
- Built-in version history
- Google Workspace integration

**Both:**
- Basic formulas
- Data validation
- Conditional formatting
- Charts (basic)
- Freeze panes

## Best Practices

### Template Design

1. **Use English Functions:** All formulas must use English function names
2. **Consistent Structure:** Keep sheet names and cell addresses consistent
3. **Example Data:** Include example rows in both formats
4. **Brand Consistency:** Maintain Ordio branding in both formats

### Sync Workflow

1. **Update Excel Template:** Modify template definition JSON
2. **Regenerate Excel:** Run Excel generation script
3. **Sync Google Sheets:** Run sync script to update Google Sheets version
4. **Verify Both:** Test both formats work correctly

### Error Handling

1. **Graceful Degradation:** Excel always available as fallback
2. **User-Friendly Messages:** Clear error messages in German
3. **Logging:** Log all errors with context
4. **Retry Logic:** Implement retry for transient API failures

### Performance

1. **Cache Copy URLs:** Don't regenerate URLs unnecessarily
2. **Batch Operations:** Use batch sync for multiple templates
3. **Rate Limiting:** Add delays between API calls
4. **Error Recovery:** Handle API rate limits gracefully

## Troubleshooting

### Common Issues

**Service Account Permissions:**
- Verify service account has access to Drive folder
- Share folder with service account email (Editor permissions)
- Check API is enabled in GCP console

**API Rate Limits:**
- Implement exponential backoff
- Add delays between API calls
- Use batch operations when possible

**Template Not Found:**
- Verify template was generated successfully
- Check template ID mapping file
- Verify template is published in registry

**Formatting Differences:**
- Some differences are expected (fonts, borders)
- Colors should match exactly
- Test templates in both formats

**Formula Errors:**
- Verify formulas use English function names
- Check cell references are valid
- Test formulas in Google Sheets directly

## Related Documentation

- [Google Sheets Integration Guide](../../docs/systems/google-sheets-integration/GOOGLE_SHEETS_INTEGRATION.md)
- [Excel Template Generator Core](../excel-template-generator-core.mdc)
- [Template Creation Workflow](../../docs/systems/templates/TEMPLATE_CREATION_WORKFLOW.md)