Chapter 13: Google Sheets API (MVP Feed Delivery)

Google Sheets is AdPriority’s MVP delivery mechanism for getting custom labels into Google Merchant Center. Instead of authenticating against the Content API for each merchant’s GMC account, AdPriority writes priority data to a Google Sheet, which GMC fetches automatically as a supplemental feed. This chapter covers the full Sheets API integration: authentication, sheet structure, write operations, quota management, and the tenant-per-sheet architecture.

11.1 Why Sheets for MVP

The supplemental feed pipeline must get priority labels from AdPriority’s database into Google Merchant Center. There are two paths: the Content API (direct GMC writes) and Google Sheets (indirect, GMC pulls from the Sheet). For the MVP, Sheets wins decisively.

Decision Matrix

The Key Insight

The merchant must manually add the supplemental feed URL in GMC regardless of approach. With Sheets, that one-time setup is the only GMC configuration needed. With the Content API, the merchant must also complete a Google OAuth flow and grant API access to their GMC account – a significant friction point during onboarding.

11.2 End-to-End Data Flow

+------------------+     +------------------+     +------------------+
|                  |     |                  |     |                  |
|   AdPriority     |     |  Google Sheets   |     |  Google Merchant |
|   Backend        |     |  API v4          |     |  Center          |
|                  |     |                  |     |                  |
+--------+---------+     +--------+---------+     +--------+---------+
         |                        |                        |
   1. Calculate priority          |                        |
      scores for all              |                        |
      active variants             |                        |
         |                        |                        |
   2. Build row data:             |                        |
      [gmcId, label0..4]          |                        |
         |                        |                        |
   3. POST to Sheets API          |                        |
      values.update               |                        |
      (batch write)               |                        |
         +----------------------->|                        |
         |                        |                        |
         |               4. Sheet updated                  |
         |                  with all rows                   |
         |                        |                        |
         |                        |  5. GMC fetches Sheet  |
         |                        |     on schedule (daily)|
         |                        |<-----------------------+
         |                        |                        |
         |                        |  6. GMC matches IDs    |
         |                        |     and applies labels |
         |                        +----------------------->|
         |                        |                        |
         |                        |               7. Labels visible
         |                        |                  in GMC and
         |                        |                  Google Ads

11.3 Google Sheets API v4

Authentication

AdPriority uses a service account for Sheets API access. This avoids per-merchant OAuth for the Sheets side – the service account owns and manages all tenant sheets.

Authentication Setup
=====================

1. Create service account in Google Cloud Console
   - Project: adpriority-production
   - Role: Editor (for Sheets the account creates)

2. Download JSON key file
   - Store encrypted, never commit to repository
   - Load via GOOGLE_SERVICE_ACCOUNT_KEY env variable

3. Enable Google Sheets API
   - APIs & Services -> Enable Google Sheets API

4. Service account email (for sharing)
   - e.g., adpriority@adpriority-prod.iam.gserviceaccount.com

Required OAuth scope:

https://www.googleapis.com/auth/spreadsheets

This grants read/write access to Google Sheets owned by or shared with the service account.

Client Initialization

// backend/src/integrations/google/sheets.ts
import { google } from 'googleapis';
import { JWT } from 'google-auth-library';

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];

function getAuthClient(): JWT {
  const credentials = JSON.parse(process.env.GOOGLE_SERVICE_ACCOUNT_KEY!);

  return new JWT({
    email: credentials.client_email,
    key: credentials.private_key,
    scopes: SCOPES,
  });
}

export function getSheetsClient() {
  const auth = getAuthClient();
  return google.sheets({ version: 'v4', auth });
}

11.4 Sheet Structure

Each tenant (Shopify store) gets its own Google Sheet. The sheet follows a strict structure that GMC expects for supplemental feeds.

Header Row

+----+----------------+----------------+----------------+----------------+----------------+
| A  | B              | C              | D              | E              | F              |
+----+----------------+----------------+----------------+----------------+----------------+
| id | custom_label_0 | custom_label_1 | custom_label_2 | custom_label_3 | custom_label_4 |
+----+----------------+----------------+----------------+----------------+----------------+

Column requirements:

• Column A (id) must be the first column
• Column names must exactly match GMC attribute names
• Header row is row 1 (required by GMC)

Data Rows

One row per variant. For Nexus Clothing with ~20,000 active variants:

Row 1 (header):
id                                          | custom_label_0 | custom_label_1 | custom_label_2      | custom_label_3 | custom_label_4

Row 2:
shopify_US_8779355160808_46050142748904     | priority-4     | winter         | jeans-pants         | in-stock       | name-brand

Row 3:
shopify_US_9128994570472_47260097118440     | priority-5     | winter         | jeans-pants         | new-arrival    | name-brand

Row 4:
shopify_US_9057367064808_47004004712680     | priority-5     | winter         | outerwear-heavy     | low-inventory  | off-brand

Row 5:
shopify_US_9238797418728_47750439567592     | priority-3     | winter         | headwear-caps       | in-stock       | name-brand

Row 6:
shopify_US_7609551716584_42582395650280     | priority-0     | winter         | hoodies-sweatshirts | dead-stock     | off-brand

...

Row 20,001:
shopify_US_8361353412840_44841122955496     | priority-2     | winter         | underwear-socks     | in-stock       | name-brand

Sheet Size Calculations

Even the largest Shopify stores (100,000+ variants) would use only 600,000 cells (6% of the limit). The Google Sheets approach is viable for the vast majority of merchants.

11.5 Write Operations

Full Sheet Write (Sync Operation)

The primary write pattern is a full replacement: clear the sheet and write all current data. This ensures the sheet always reflects the current state of priorities without complex diff logic.

// backend/src/services/sync/sheets-writer.ts
import { getSheetsClient } from '../../integrations/google/sheets';

const HEADER_ROW = [
  'id',
  'custom_label_0',
  'custom_label_1',
  'custom_label_2',
  'custom_label_3',
  'custom_label_4',
];

interface ProductLabelRow {
  gmcProductId: string;   // shopify_US_{productId}_{variantId}
  priority: number;       // 0-5
  season: string;         // winter, spring, summer, fall
  category: string;       // normalized category group
  status: string;         // in-stock, low-inventory, etc.
  brandTier: string;      // name-brand, store-brand, off-brand
}

export async function writePrioritySheet(
  spreadsheetId: string,
  products: ProductLabelRow[]
): Promise<{ rowsWritten: number }> {
  const sheets = getSheetsClient();

  // Build all rows: header + data
  const dataRows = products.map(p => [
    p.gmcProductId,
    `priority-${p.priority}`,
    p.season,
    p.category,
    p.status,
    p.brandTier,
  ]);

  const allRows = [HEADER_ROW, ...dataRows];

  // Step 1: Clear existing data
  await sheets.spreadsheets.values.clear({
    spreadsheetId,
    range: 'Sheet1',
  });

  // Step 2: Write all rows
  await sheets.spreadsheets.values.update({
    spreadsheetId,
    range: 'Sheet1!A1',
    valueInputOption: 'RAW',
    requestBody: {
      values: allRows,
    },
  });

  return { rowsWritten: dataRows.length };
}

Chunked Write for Large Catalogs

For stores with more than 50,000 variants, a single values.update call may exceed payload limits. Use batch updates:

export async function writePrioritySheetChunked(
  spreadsheetId: string,
  products: ProductLabelRow[],
  chunkSize: number = 10000
): Promise<{ rowsWritten: number }> {
  const sheets = getSheetsClient();

  // Clear sheet first
  await sheets.spreadsheets.values.clear({
    spreadsheetId,
    range: 'Sheet1',
  });

  // Write header
  await sheets.spreadsheets.values.update({
    spreadsheetId,
    range: 'Sheet1!A1',
    valueInputOption: 'RAW',
    requestBody: {
      values: [HEADER_ROW],
    },
  });

  // Write data in chunks
  let rowsWritten = 0;

  for (let i = 0; i < products.length; i += chunkSize) {
    const chunk = products.slice(i, i + chunkSize);
    const startRow = i + 2; // +1 for header, +1 for 1-indexed

    const rows = chunk.map(p => [
      p.gmcProductId,
      `priority-${p.priority}`,
      p.season,
      p.category,
      p.status,
      p.brandTier,
    ]);

    await sheets.spreadsheets.values.update({
      spreadsheetId,
      range: `Sheet1!A${startRow}`,
      valueInputOption: 'RAW',
      requestBody: { values: rows },
    });

    rowsWritten += rows.length;

    // Respect rate limits between chunks
    await sleep(1000);
  }

  return { rowsWritten };
}

11.6 Sheet Lifecycle Management

Creating a Sheet for a New Tenant

When a merchant installs AdPriority and completes setup, the backend creates a new Google Sheet:

// backend/src/services/sync/sheets-manager.ts
import { getSheetsClient } from '../../integrations/google/sheets';

export async function createTenantSheet(
  storeName: string
): Promise<{ spreadsheetId: string; spreadsheetUrl: string }> {
  const sheets = getSheetsClient();

  // Create new spreadsheet
  const response = await sheets.spreadsheets.create({
    requestBody: {
      properties: {
        title: `AdPriority - ${storeName} - Custom Labels`,
      },
      sheets: [
        {
          properties: {
            title: 'Sheet1',
            gridProperties: {
              frozenRowCount: 1, // Freeze header row
            },
          },
        },
      ],
    },
  });

  const spreadsheetId = response.data.spreadsheetId!;
  const spreadsheetUrl = response.data.spreadsheetUrl!;

  // Share publicly (Viewer) so GMC can fetch it
  const drive = google.drive({ version: 'v3', auth: getAuthClient() });
  await drive.permissions.create({
    fileId: spreadsheetId,
    requestBody: {
      role: 'reader',
      type: 'anyone',
    },
  });

  // Write header row
  await sheets.spreadsheets.values.update({
    spreadsheetId,
    range: 'Sheet1!A1',
    valueInputOption: 'RAW',
    requestBody: {
      values: [HEADER_ROW],
    },
  });

  return { spreadsheetId, spreadsheetUrl };
}

Storing the Sheet Reference

The spreadsheet ID and URL are stored in the stores database table:

-- In the stores table
ALTER TABLE stores ADD COLUMN gmc_sheet_id VARCHAR(255);
ALTER TABLE stores ADD COLUMN gmc_sheet_url TEXT;

-- Example record for Nexus
UPDATE stores
SET gmc_sheet_id = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms',
    gmc_sheet_url = 'https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit'
WHERE shop_domain = 'nexus-clothes.myshopify.com';

Sheet Lifecycle

Tenant Sheet Lifecycle
=======================

1. INSTALL
   Merchant installs app
   -> Create new Google Sheet
   -> Write header row
   -> Share publicly (Viewer)
   -> Store spreadsheet ID in database
   -> Show URL to merchant for GMC setup

2. INITIAL SYNC
   Full product catalog imported from Shopify
   -> Calculate all priorities
   -> Write all rows to Sheet (~20,000 for Nexus)
   -> Merchant adds Sheet URL as supplemental feed in GMC

3. ONGOING SYNC (daily at 2:00 AM per tenant timezone)
   -> Recalculate priorities (seasonal changes, new products)
   -> Full Sheet rewrite with current data
   -> GMC fetches updated Sheet on its schedule

4. ON-DEMAND SYNC (triggered by merchant or webhook)
   -> Recalculate affected products only
   -> Full Sheet rewrite (simpler than partial updates)

5. UNINSTALL
   Merchant removes app
   -> Delete Sheet via Drive API
   -> Remove spreadsheet ID from database
   -> Merchant must manually remove supplemental feed from GMC

11.7 Merchant Onboarding: GMC Setup Instructions

After AdPriority creates the Sheet, the merchant must perform a one-time setup in Google Merchant Center. The app displays these instructions in the Settings page:

+-----------------------------------------------------------------------+
|  AdPriority Settings > Google Merchant Center Setup                     |
+-----------------------------------------------------------------------+
|                                                                        |
|  Your supplemental feed is ready!                                      |
|                                                                        |
|  Sheet URL:                                                            |
|  https://docs.google.com/spreadsheets/d/1BxiMVs.../edit               |
|  [Copy URL]                                                            |
|                                                                        |
|  Follow these steps to connect it to Google Merchant Center:           |
|                                                                        |
|  1. Open Google Merchant Center (merchants.google.com)                 |
|  2. Go to Products > Feeds                                             |
|  3. Click "Add supplemental feed"                                      |
|  4. Choose "Google Sheets" as the source                               |
|  5. Paste the Sheet URL above                                          |
|  6. Select "Sheet1" as the tab                                         |
|  7. Set fetch schedule to "Daily"                                      |
|  8. Click "Create feed"                                                |
|  9. Link to your primary data source(s)                                |
|                                                                        |
|  That is it! GMC will fetch your priority labels daily.                |
|                                                                        |
|  [Verify Connection]  [View Sheet]  [Need Help?]                       |
|                                                                        |
+-----------------------------------------------------------------------+

11.8 Quota and Rate Limits

Google Sheets API Quotas

Rate Limit Strategy

Rate Limit Budget (per 100-second window)
==========================================

100 requests available per 100 seconds

Sync operations per tenant:
  - 1 clear request
  - 1 write request (small stores, < 50K rows)
  - OR 1 clear + N chunk writes (large stores)

Maximum concurrent tenant syncs:
  - Small stores (1 clear + 1 write = 2 requests): 50 tenants per window
  - Large stores (1 clear + 5 chunks = 6 requests): 16 tenants per window

Strategy:
  - Queue syncs with 2-second spacing between tenants
  - Small stores: ~50 tenants per 100-second window
  - Stagger sync times across timezones
  - Peak: 2:00 AM in each timezone

Retry with Backoff

// backend/src/integrations/google/sheets-retry.ts

const SHEETS_RETRY_CONFIG = {
  maxRetries: 5,
  initialDelayMs: 2000,     // Start at 2 seconds
  backoffMultiplier: 2,     // Double each retry
  maxDelayMs: 60000,        // Cap at 60 seconds
  retryableCodes: [429, 500, 503],
};

export async function sheetsApiWithRetry<T>(
  operation: () => Promise<T>
): Promise<T> {
  let delay = SHEETS_RETRY_CONFIG.initialDelayMs;

  for (let attempt = 0; attempt <= SHEETS_RETRY_CONFIG.maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error: any) {
      const statusCode = error.response?.status || error.code;

      if (
        attempt === SHEETS_RETRY_CONFIG.maxRetries ||
        !SHEETS_RETRY_CONFIG.retryableCodes.includes(statusCode)
      ) {
        throw error;
      }

      console.warn(
        `Sheets API attempt ${attempt + 1} failed (${statusCode}). ` +
        `Retrying in ${delay}ms...`
      );

      await new Promise(resolve => setTimeout(resolve, delay));
      delay = Math.min(
        delay * SHEETS_RETRY_CONFIG.backoffMultiplier,
        SHEETS_RETRY_CONFIG.maxDelayMs
      );
    }
  }

  throw new Error('Unreachable');
}

11.9 Sync Scheduling

Scheduled Sync (Daily)

Each tenant’s Sheet is updated daily. The sync job runs as a Bull queue worker:

// backend/src/scheduler/sync.ts
import { Queue, Worker } from 'bullmq';

const syncQueue = new Queue('sheet-sync', { connection: redisConfig });

// Schedule daily sync for each active tenant
export async function scheduleDailySyncs(): Promise<void> {
  const stores = await db.store.findMany({
    where: { status: 'active', gmc_sheet_id: { not: null } },
  });

  for (const store of stores) {
    await syncQueue.add(
      'daily-sync',
      { storeId: store.id },
      {
        repeat: {
          pattern: '0 2 * * *', // 2:00 AM daily
        },
        jobId: `daily-sync-${store.id}`,
      }
    );
  }
}

// Worker processes sync jobs
const syncWorker = new Worker('sheet-sync', async (job) => {
  const { storeId } = job.data;

  // 1. Fetch current priorities from database
  const products = await db.productMapping.findMany({
    where: { storeId, shopifyStatus: 'active' },
  });

  // 2. Build label rows
  const rows: ProductLabelRow[] = products.map(p => ({
    gmcProductId: p.gmcProductId,
    priority: p.priorityScore,
    season: p.customLabel1,
    category: p.customLabel2,
    status: p.customLabel3,
    brandTier: p.customLabel4,
  }));

  // 3. Write to Sheet
  const store = await db.store.findUnique({ where: { id: storeId } });
  const result = await writePrioritySheet(store.gmcSheetId, rows);

  // 4. Log sync result
  await db.syncLog.create({
    data: {
      storeId,
      type: 'sheet_write',
      status: 'success',
      rowsWritten: result.rowsWritten,
      completedAt: new Date(),
    },
  });

  return result;
}, { connection: redisConfig });

Event-Triggered Sync

Certain events trigger an immediate Sheet update rather than waiting for the daily schedule:

Sync Status Tracking

The dashboard displays sync status for the merchant:

+-----------------------------------------------------------------------+
|  Last Sync: 2026-02-10 02:00:14 AM                                    |
|  Status: Success                                                       |
|  Rows Written: 18,472                                                  |
|  Duration: 4.2 seconds                                                 |
|  Next Sync: 2026-02-11 02:00 AM                                       |
|                                                                        |
|  [Sync Now]  [View Sheet]  [Sync History]                              |
+-----------------------------------------------------------------------+

11.10 Error Handling

Common Failure Modes

Sheet Health Check

A periodic job verifies that each tenant’s Sheet is accessible and correctly structured:

export async function verifySheetHealth(
  spreadsheetId: string
): Promise<{ healthy: boolean; issue?: string }> {
  try {
    const sheets = getSheetsClient();

    // Read first row to verify header
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId,
      range: 'Sheet1!A1:F1',
    });

    const header = response.data.values?.[0];

    if (!header || header[0] !== 'id') {
      return { healthy: false, issue: 'Missing or incorrect header row' };
    }

    if (header.length !== 6) {
      return { healthy: false, issue: `Expected 6 columns, found ${header.length}` };
    }

    return { healthy: true };
  } catch (error: any) {
    return { healthy: false, issue: error.message };
  }
}

11.11 Transition to Content API (Growth Path)

As AdPriority scales, some merchants will outgrow the Sheets approach. The transition path is:

Tier Progression
=================

Starter ($29/mo)     -->  Google Sheets (daily sync)
                          - One Sheet per tenant
                          - Full rewrite daily
                          - ~24-48h label propagation

Growth ($79/mo)      -->  Google Sheets (daily sync + on-demand)
                          - Same Sheet approach
                          - Manual "Sync Now" button
                          - Seasonal auto-transitions

Pro ($199/mo)        -->  Content API (near real-time)
                          - Direct GMC API writes
                          - 1-6h label propagation
                          - Google Ads performance data
                          - Falls back to Sheets if API fails

The Sheet remains as a fallback and debugging tool even for Pro tier merchants. If the Content API encounters quota issues, the system degrades gracefully to Sheet-based sync.

11.12 Security Considerations

11.13 Chapter Summary

Google Sheets is the ideal MVP delivery mechanism for AdPriority’s custom labels. The Sheets API v4 provides a simple, reliable, and free way to maintain supplemental feeds that GMC fetches daily. Each tenant gets a dedicated Sheet created by AdPriority’s service account, with the spreadsheet ID stored in the database and the URL provided to the merchant for one-time GMC setup.

Key specifications:

• Sheet structure: 6 columns (id + 5 custom labels), one row per variant
• Nexus store: ~20,000 rows, 120,000 cells (1.2% of Sheet limit)
• Write pattern: Full clear-and-rewrite on each sync
• Schedule: Daily at 2:00 AM + event-triggered syncs
• Rate limit: 100 requests per 100 seconds (supports ~50 small-store syncs per window)
• Retry: Exponential backoff starting at 2 seconds, up to 60 seconds, 5 max retries
• One Sheet per tenant, created automatically on app setup
• Service account owns all Sheets (no per-merchant Google OAuth needed)
• Fallback mechanism for Pro tier when Content API hits quota limits