# Google Sheets Features

This page explains all the features available in the Google Sheets integration and how to use them.

## Automatic Syncing

Orders sync automatically in the background. Here's how it works:

**When does syncing happen?**
- New order created → syncs within 3-5 seconds
- Order updated → syncs the changes
- Tracking number added → updates the sheet
- Order status changed → reflects in the sheet

**What triggers a sync?**
- Creating a new order (checkout, sales page, manual entry)
- Updating order details
- Adding tracking numbers
- Changing order status
- Adding customer notes

The system uses Laravel's queue system, so syncs happen in the background without slowing down your application.

## Manual Sync Options

Sometimes you need to sync orders manually. The settings page offers two options:

### Sync Last 10 Orders

Use this when:
- You just connected your Google account
- You want to test the connection
- You need to re-sync recent orders

How to use:
1. Go to Settings > Google Sheets
2. Click "Sync 10 Orders"
3. Wait 10-20 seconds
4. Check your Google Sheet

This syncs your 10 most recent orders to the sheet. If an order already exists (matched by Global Order ID), it updates the row instead of creating a duplicate.

### Bulk Re-sync (Admin Only)

This is useful for disaster recovery or when you need to rebuild your sheet from scratch.

Note: Currently, bulk syncing is done through the manual sync button which syncs the last 10 orders. For larger batches, you'll need to run it multiple times or contact support.

## Google Sheet Structure

Your Google Sheet has 20 columns (A through T):

| Column | Field | Description |
|--------|-------|-------------|
| A | Global Order ID | Unique system identifier (e.g., OMS00000001) |
| B | Order Number | Store-specific order number |
| C | Date Created | When the order was placed |
| D | Customer Name | Full name |
| E | Phone | Customer phone number |
| F | Email | Customer email |
| G | Address | Street address |
| H | City | City name |
| I | State | State/Province |
| J | Postcode | Postal code |
| K | Products | Product names (comma-separated) |
| L | SKU | Product SKUs (comma-separated) |
| M | Quantity | Quantities (comma-separated) |
| N | Order Total | Total amount |
| O | Currency | Currency code (usually MYR) |
| P | Payment Method | How customer paid |
| Q | Status | Order status (processing, completed, etc.) |
| R | Tracking Number | Courier tracking number |
| S | Store | Store name (for multi-store setups) |
| T | Notes | Customer notes from checkout |

### Why Global Order ID First?

The Global Order ID (Column A) is the unique identifier across all stores. We use this to detect duplicates and update existing rows. Order numbers can be the same across different stores, but Global Order IDs are always unique.

### Bundle Products

If an order contains product bundles, the system automatically expands them. For example:

**Order contains:**
- 1x Family Bundle (contains 3x Product A + 2x Product B)

**In Google Sheets, you'll see:**
- Products: Product A, Product B
- SKUs: SKU-A, SKU-B
- Quantities: 3, 2

This makes it easier to see what actually needs to be packed and shipped.

## Duplicate Detection

The system is smart about duplicates. Here's how it works:

1. When syncing an order, it checks Column A for the Global Order ID
2. If found → updates that existing row
3. If not found → adds a new row

This means:
- You can safely re-sync orders
- Updates don't create duplicates
- Each order appears only once in your sheet

## Real-Time Updates

When you update an order in the system, the Google Sheet updates automatically:

- Change status → sheet updates
- Add tracking number → appears in Column R
- Edit customer details → sheet reflects the change
- Add notes → shows in Column T

There's usually a 3-5 second delay due to the queue system.

## Header Row Formatting

The first row of your sheet is automatically formatted:

- Background color: Light blue
- Text color: White
- Font weight: Bold
- Frozen row: Yes (stays visible when scrolling)

This makes it easy to identify columns when you have many orders.

## Multi-Seller Isolation

Each seller's data is completely isolated:

**Seller A:**
- Connects their own Gmail account
- Gets their own unique Google Sheet
- Only their orders sync to their sheet
- Cannot see other sellers' data

**Seller B:**
- Connects their own Gmail account
- Gets their own unique Google Sheet
- Only their orders sync to their sheet
- Cannot see Seller A's data

This ensures complete data privacy and security.

## Rate Limits

Google Sheets API has rate limits to prevent abuse. The system handles this automatically:

**Google's Limits:**
- 60 requests per minute per user
- 500 requests per 100 seconds per project

**Our Protection:**
- 1.2 second delay between each sync request
- Automatic retry with exponential backoff
- Maximum 3 retry attempts

This means:
- You can sync ~50 orders per minute
- If you hit the limit, the system waits and retries
- Syncs might slow down during bulk operations

## Sheet Access Control

The Google Sheet is created with these permissions:

- **Owner:** The Gmail account that connected
- **Visibility:** Private (only the owner can access)
- **Location:** The owner's Google Drive

If you want to share the sheet with team members:
1. Open the sheet in Google Drive
2. Click "Share"
3. Add team members' email addresses
4. Set their permission level (Viewer, Commenter, or Editor)

## Disconnecting

When a seller disconnects their Google account:

**What happens:**
- Token is revoked with Google
- All Google-related data cleared from database
- Sync stops immediately

**What doesn't happen:**
- The Google Sheet is NOT deleted
- It remains in the seller's Google Drive
- Past data stays intact

**Reconnecting:**
If the seller reconnects later, a NEW sheet is created. The old sheet remains in Google Drive for reference.

## Data Backup Strategy

Google Sheets integration serves as a real-time backup of your orders. Benefits:

- Orders sync as they're created
- If your database goes down, you still have the data
- Easy to export to CSV or Excel
- Can be shared with accountants or managers
- Accessible from anywhere with internet

However, Google Sheets should be a secondary backup. Always maintain proper database backups as your primary recovery method. See [Backup & Recovery](../../admin/backup-recovery.md) for details.

## Performance Considerations

**Impact on order creation:**
- Zero impact (syncs happen in background queue)
- Orders save immediately to database
- Sync happens asynchronously

**Queue worker resource usage:**
- Very minimal when idle (~80MB RAM)
- Brief spikes during sync (~100-150MB)
- CPU usage: <5% on average

See [Queue Workers Guide](../../deployment/queue-workers.md) for performance details.

## What's NOT Synced

These items don't sync to Google Sheets:

- Order attachments or uploaded files
- Internal admin notes (only customer-facing notes sync)
- Payment transaction details
- Refund information
- Order history/audit trail

Google Sheets is for order data, not complete system replication.

## Tips for Using the Sheet

**Filtering:**
Use Google Sheets' built-in filtering to find orders:
- Filter by status (Column Q)
- Filter by date range (Column C)
- Filter by customer name (Column D)

**Sorting:**
- Sort by newest first: Sort Column C (Date) Z→A
- Sort by status: Sort Column Q A→Z
- Sort by total amount: Sort Column N (high to low)

**Conditional Formatting:**
Highlight orders by status:
1. Select Column Q (Status)
2. Format > Conditional formatting
3. Format cells if text contains "processing" → Yellow
4. Add rule if text contains "completed" → Green
5. Add rule if text contains "cancelled" → Red

**Pivot Tables:**
Analyze your data:
1. Data > Pivot table
2. Analyze sales by product, date, status, etc.
3. Create reports for accounting or management

## Next Steps

- [Setup Guide](setup.md) - Initial configuration
- [Troubleshooting](troubleshooting.md) - Fix common issues
- [Change History](changelog.md) - See what's been updated

---

**Last updated:** 2025-11-21
