# Google Sheets Integration Setup Verification

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

Quick reference guide for verifying Google Sheets integration setup and troubleshooting common issues.

## Quick Verification

Run the verification script:

```bash
php v2/scripts/templates/verify-google-sheets-setup.php
```

## Common Setup Issues

### 1. Drive Folder Not Found (404 Error)

**Error:**
```
File not found: 1AgFeV35J44nzi6QXbvs1O1cPEPtFv4uD
```

**Solution:**
1. Verify the folder ID is correct in `v2/config/google-sheets-config.php`
2. Check that the folder exists: https://drive.google.com/drive/folders/1AgFeV35J44nzi6QXbvs1O1cPEPtFv4uD
3. Ensure the folder hasn't been deleted or moved
4. If using a different folder, update `GOOGLE_SHEETS_TEMPLATES_FOLDER_ID` constant

### 2. Permission Denied (403 Error)

**Error:**
```
The caller does not have permission
```

**Solution:**
1. **Grant Drive Folder Access:**
   - Open the Drive folder
   - Click "Share"
   - Add service account: `ordio-seo-analytics@ordio-472310.iam.gserviceaccount.com`
   - Set permission to "Editor"
   - Click "Send"

2. **Verify API is Enabled:**
   - Go to [Google Cloud Console](https://console.cloud.google.com/)
   - Navigate to "APIs & Services" > "Enabled APIs"
   - Verify both APIs are enabled:
     - Google Sheets API
     - Google Drive API
   - If not enabled, enable them from the API Library

3. **Check Service Account Permissions:**
   - Go to [IAM & Admin](https://console.cloud.google.com/iam-admin/iam)
   - Find the service account: `ordio-seo-analytics@ordio-472310.iam.gserviceaccount.com`
   - Verify it has necessary roles (usually "Editor" or custom role with Sheets/Drive permissions)

### 3. API Not Enabled

**Error:**
```
Permission denied - Check API is enabled in GCP
```

**Solution:**
1. Enable Google Sheets API:
   - https://console.cloud.google.com/apis/library/sheets.googleapis.com
   - Click "Enable"

2. Enable Google Drive API:
   - https://console.cloud.google.com/apis/library/drive.googleapis.com
   - Click "Enable"

3. Wait 1-2 minutes for API activation to propagate

4. Re-run verification script

### 4. Invalid Credentials

**Error:**
```
Failed to create Google Sheets API client
```

**Solution:**
1. Verify credentials file exists: `v2/config/google-api-credentials.json`
2. Check file permissions (should be readable)
3. Verify JSON structure is valid:
   ```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-seo-analytics@ordio-472310.iam.gserviceaccount.com",
     ...
   }
   ```
4. Ensure `client_email` matches `GOOGLE_SHEETS_SERVICE_ACCOUNT_EMAIL` constant

### 7. Service Account Email Mismatch

**Warning:**
```
Service account email mismatch
```

**Solution:**
1. Check `v2/config/google-sheets-config.php`:
   ```php
   define('GOOGLE_SHEETS_SERVICE_ACCOUNT_EMAIL', 'ordio-seo-analytics@ordio-472310.iam.gserviceaccount.com');
   ```

2. Verify `google-api-credentials.json` has matching email:
   ```json
   {
     "client_email": "ordio-seo-analytics@ordio-472310.iam.gserviceaccount.com"
   }
   ```

3. Update the constant if using a different service account

## Verification Checklist

- [ ] Credentials file exists and is valid
- [ ] Service account email matches configuration
- [ ] Google Sheets API is enabled
- [ ] Google Drive API is enabled
- [ ] Drive folder exists and is accessible
- [ ] Service account has "Editor" access to Drive folder
- [ ] API clients can be created successfully
- [ ] Template IDs mapping file exists (will be created automatically)

## Testing After Setup

1. **Generate a test template:**
   ```bash
   php v2/scripts/templates/generate-google-sheets-template.php --template=dienstplan-excel-vorlage
   ```

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

3. **Verify template in Drive:**
   - Go to Drive folder: https://drive.google.com/drive/folders/1AgFeV35J44nzi6QXbvs1O1cPEPtFv4uD
   - Verify template spreadsheet exists
   - Check that copy URL works: `https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/copy`

4. **Test API endpoint:**
   ```bash
   curl "http://localhost:8003/v2/api/get-google-sheets-template.php?template_id=dienstplan-excel-vorlage"
   ```

## Next Steps

Once setup is verified:

1. Generate all templates:
   ```bash
   php v2/scripts/templates/generate-google-sheets-template.php --all
   ```

2. Set up sync automation (optional):
   - Add cron job to sync templates periodically
   - Or run sync manually when templates are updated

3. Test frontend integration:
   - Visit a template page
   - Verify both Excel and Google Sheets options appear
   - Test Google Sheets copy flow

## Support

If issues persist:

1. Check logs: `logs/` directory for error details
2. Review Google Cloud Console for API errors
3. Verify service account permissions in IAM
4. Check Drive folder sharing settings