# Manual Google Sheet Creation Workflow

**Last Updated:** 2026-03-08

Alternative approach to avoid service account storage quota issues: create Google Sheets manually, then register them for automatic updates.

## Overview

Instead of creating Google Sheets programmatically (which requires domain-wide delegation due to service account quota), you can:

1. Create Google Sheets manually in your Drive folder
2. Share them with the service account
3. Register them with the system
4. Let the API update them automatically

## Benefits

- ✅ **No storage quota issues** - Uses your quota, not service account's
- ✅ **Works immediately** - No domain-wide delegation setup needed
- ✅ **Full API access** - Can update content, formatting, formulas, etc.
- ✅ **Automatic sync** - Sync system keeps them updated

## Workflow

### Step 1: Create Google Sheet Manually

1. Go to: https://drive.google.com/drive/u/0/folders/1kFCqL3MaXEtqRLVj1eGqUML0UH3qa1uU
2. Click "+ New" → "Google Sheets"
3. Name it appropriately (e.g., "Dienstplan Excel Vorlage")
4. **Note:** The sheet will have a default "Sheet1" tab - this will be automatically removed when the template is populated, so you can ignore it.
5. Right-click → "Share"
6. Add: `ordio-seo-analytics@ordio-472310.iam.gserviceaccount.com`
7. Set permission: "Editor"
8. Click "Send" (uncheck "Notify people")
9. Copy the spreadsheet ID from the URL:
   - URL format: `https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit`
   - Copy the `SPREADSHEET_ID` part

### Step 2: Register the Sheet

```bash
php v2/scripts/templates/register-manual-sheet.php \
  --template=dienstplan-excel-vorlage \
  --spreadsheet-id=YOUR_SPREADSHEET_ID
```

### Step 3: Populate with Template Content

```bash
php v2/scripts/templates/generate-google-sheets-template.php \
  --template=dienstplan-excel-vorlage
```

This will:
- Read the template JSON definition
- Populate the spreadsheet with all data, formatting, formulas, etc.
- Set up data validation, conditional formatting, etc.

### Step 4: Verify

```bash
php v2/scripts/templates/sync-google-sheets-templates.php --check
```

## What Can Be Updated

Once registered, the API can update:

- ✅ **Cell values** - All data content
- ✅ **Formatting** - Colors, fonts, borders, alignment
- ✅ **Formulas** - All formulas and calculations
- ✅ **Data validation** - Dropdown lists, number ranges, etc.
- ✅ **Conditional formatting** - Color rules based on values
- ✅ **Sheet structure** - Add/remove sheets, rename sheets
- ✅ **Column widths** - Adjust column sizes
- ✅ **Frozen panes** - Freeze rows/columns

## Sync System

The sync system will automatically detect changes in Excel template definitions and update the Google Sheets versions:

```bash
# Check what needs syncing
php v2/scripts/templates/sync-google-sheets-templates.php --check

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

# Sync all templates
php v2/scripts/templates/sync-google-sheets-templates.php --all
```

## Example: Complete Workflow

```bash
# 1. You create "Dienstplan Excel Vorlage" manually in Drive
#    Spreadsheet ID: 1abc123def456ghi789

# 2. Register it
php v2/scripts/templates/register-manual-sheet.php \
  --template=dienstplan-excel-vorlage \
  --spreadsheet-id=1abc123def456ghi789

# 3. Populate with template content
php v2/scripts/templates/generate-google-sheets-template.php \
  --template=dienstplan-excel-vorlage

# 4. Verify it worked
php v2/scripts/templates/sync-google-sheets-templates.php --check
```

## Troubleshooting

### "Spreadsheet not accessible" Error

**Solution:** Make sure the spreadsheet is shared with:
- Email: `ordio-seo-analytics@ordio-472310.iam.gserviceaccount.com`
- Permission: Editor

### "Template not found" Error

**Solution:** Verify the template ID exists in the registry:
```bash
php -r "require 'v2/config/template-page-config.php'; \$meta = get_template_metadata('TEMPLATE_ID'); var_dump(\$meta);"
```

### Updates Not Working

**Solution:** Check service account has Editor access and verify API is enabled:
```bash
php v2/scripts/templates/verify-google-sheets-setup.php
```

## Advantages Over Programmatic Creation

| Aspect | Manual Creation | Programmatic Creation |
|--------|----------------|----------------------|
| Storage Quota | Uses your quota ✅ | Requires domain-wide delegation |
| Setup Time | Immediate ✅ | Requires admin console setup |
| API Access | Full access ✅ | Full access ✅ |
| Sync | Automatic ✅ | Automatic ✅ |
| Initial Setup | Manual (one-time) | Automatic (but needs delegation) |

## When to Use Each Approach

**Use Manual Creation When:**
- You want to start immediately
- Domain-wide delegation isn't set up yet
- You prefer to control initial creation
- You have a few templates to set up

**Use Programmatic Creation When:**
- Domain-wide delegation is configured
- You have many templates to create
- You want fully automated workflow
- You're creating templates frequently