WooCommerce Orders to Google Sheets: Sync Your WordPress Store Data Automatically

WooCommerce stores every order in a WordPress MySQL database, split across multiple tables in a way that is convenient for WordPress but inconvenient for spreadsheet analysis. Getting that data into Google Sheets in a usable format requires either bridging the table structure or using one of the several abstraction layers WooCommerce and third-party tools provide.

This guide covers all six practical methods, from the simplest (manual CSV) to the most powerful (direct MySQL via Apps Script JDBC), so you can pick the approach that matches your technical comfort level and sync frequency requirements.

How WooCommerce Stores Order Data

WooCommerce is built on WordPress, which means it uses WordPress's data model. That model was designed for blog posts and pages, not for structured e-commerce data.

WordPress stores all content types (posts, pages, orders, products) in a single table: wp_posts. Additional fields that do not fit the standard post schema are stored as key-value pairs in wp_postmeta. This is called the Entity-Attribute-Value (EAV) pattern, and while it gives WordPress flexibility, it makes SQL queries for order data verbose and sometimes slow.

Each WooCommerce order is a row in wp_posts with post_type = 'shop_order'. Order details (customer name, billing address, order total, payment method, line items) live in wp_postmeta, linked to the order via post_id.

Note on WooCommerce HPOS: WooCommerce introduced High-Performance Order Storage (HPOS) in version 8.2, which migrates order data from wp_posts/wp_postmeta to dedicated tables (wc_orders, wc_orders_meta, wc_order_items, etc.). If your store is running WooCommerce 8.2+ and has HPOS enabled, the table structure described below applies to the legacy storage engine. Check WooCommerce > Settings > Advanced > Features to see which storage engine your store uses. The REST API approach (Method 2) works regardless of which storage engine is active.

The wp_posts and wp_postmeta Table Structure

wp_posts (relevant columns for orders)

wp_postmeta (common order meta keys)

The standard pivot query

To get orders as flat rows, you join wp_posts with multiple self-joined instances of wp_postmeta:

SELECT
  p.ID                          AS order_id,
  p.post_date                   AS order_date,
  p.post_status                 AS status,
  MAX(CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END) AS first_name,
  MAX(CASE WHEN pm.meta_key = '_billing_last_name'  THEN pm.meta_value END) AS last_name,
  MAX(CASE WHEN pm.meta_key = '_billing_email'      THEN pm.meta_value END) AS email,
  MAX(CASE WHEN pm.meta_key = '_billing_phone'      THEN pm.meta_value END) AS phone,
  MAX(CASE WHEN pm.meta_key = '_order_total'        THEN pm.meta_value END) AS order_total,
  MAX(CASE WHEN pm.meta_key = '_order_currency'     THEN pm.meta_value END) AS currency,
  MAX(CASE WHEN pm.meta_key = '_payment_method'     THEN pm.meta_value END) AS payment_method,
  MAX(CASE WHEN pm.meta_key = '_transaction_id'     THEN pm.meta_value END) AS transaction_id
FROM wp_posts p
JOIN wp_postmeta pm ON pm.post_id = p.ID
WHERE p.post_type = 'shop_order'
  AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold')
GROUP BY p.ID, p.post_date, p.post_status
ORDER BY p.post_date DESC
LIMIT 5000;

This conditional aggregation pattern (using MAX(CASE WHEN ...)) is the most readable approach to pivoting the EAV structure. It is also more performant on large stores than multiple LEFT JOIN statements on wp_postmeta, because each join creates a separate index lookup per row.

On high-volume stores: If wp_postmeta has millions of rows (common on stores that have been running for several years), this query can be slow. Add an index on (post_id, meta_key) if one does not already exist:

CREATE INDEX idx_postmeta_lookup ON wp_postmeta (post_id, meta_key);

WooCommerce adds this index by default, but it can be missing on older installs or after database migrations.

Method 1: WooCommerce CSV Export (Manual)

Who it is for: Store owners who need a one-time export or a monthly report and do not mind doing it manually.

How to export

1. In your WordPress admin, go to WooCommerce > Orders.
2. Filter by date range, status, or other criteria.
3. Click Export (top right). If you do not see an Export button, you may need the WooCommerce Order Export extension or a plugin like WP All Export.
4. Choose columns and download the CSV.

Import to Google Sheets

1. Open Google Sheets.
2. Go to File > Import > Upload and select the CSV.
3. Choose comma as the separator and click Import Data.

Limitations: Fully manual. No automation. No scheduled updates. Best suited for ad-hoc reporting rather than ongoing monitoring.

Method 2: WooCommerce REST API + Apps Script

WooCommerce ships with a REST API that returns order data in structured JSON. This approach does not require database access (it uses the application layer) which makes it safer and simpler to set up.

Step 1: Create API credentials

In WooCommerce > Settings > Advanced > REST API, click Add key. Give it a description, assign it to your admin user, and set permissions to Read. Copy the Consumer Key and Consumer Secret.

Step 2: The Apps Script

function syncWooCommerceOrders() {
  const WC_STORE_URL = 'https://your-store.com';
  const CONSUMER_KEY = PropertiesService.getScriptProperties().getProperty('WC_KEY');
  const CONSUMER_SECRET = PropertiesService.getScriptProperties().getProperty('WC_SECRET');
  const SHEET_NAME = 'Orders';

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  sheet.clearContents();

  let page = 1;
  const perPage = 100;
  let allOrders = [];

  while (true) {
    const url = `${WC_STORE_URL}/wp-json/wc/v3/orders?per_page=${perPage}&page=${page}&orderby=date&order=desc&status=completed,processing`;
    const options = {
      method: 'get',
      headers: {
        Authorization: 'Basic ' + Utilities.base64Encode(`${CONSUMER_KEY}:${CONSUMER_SECRET}`)
      },
      muteHttpExceptions: true
    };

    const response = UrlFetchApp.fetch(url, options);
    const orders = JSON.parse(response.getContentText());

    if (!Array.isArray(orders) || orders.length === 0) break;

    allOrders = allOrders.concat(orders);
    if (orders.length < perPage) break;
    page++;

    // Respect Apps Script URL Fetch quota
    Utilities.sleep(200);
  }

  if (allOrders.length === 0) {
    Logger.log('No orders returned');
    return;
  }

  // Define the columns you want
  const headers = ['ID', 'Date', 'Status', 'Total', 'Currency', 'Customer Email', 'Billing Name', 'Payment Method'];
  const rows = allOrders.map(order => [
    order.id,
    order.date_created,
    order.status,
    order.total,
    order.currency,
    order.billing ? order.billing.email : '',
    order.billing ? `${order.billing.first_name} ${order.billing.last_name}` : '',
    order.payment_method_title
  ]);

  const data = [headers, ...rows];
  sheet.getRange(1, 1, data.length, headers.length).setValues(data);
  Logger.log(`Synced ${rows.length} orders`);
}

Limitations: The WooCommerce REST API paginates at 100 orders per page. For stores with thousands of orders, paginating through all of them takes time and HTTP requests. For initial full imports, the direct MySQL approach is faster. Use the REST API for incremental updates filtered by after date parameter.

Method 3: Direct MySQL via Apps Script JDBC

Apps Script can connect to MySQL databases directly using its JDBC service. This gives you full SQL power against the WordPress database. The most efficient approach for complex queries or large datasets.

Prerequisites

Your MySQL server must be accessible from the internet (or via a network that Google's JDBC service can reach). Many WordPress hosts restrict external MySQL access by default. Options:

• Enable remote MySQL access: Add Google's IP ranges to your host's allowed connections list. (These ranges change; check Google's documentation for the current list.)
• Use an SSH tunnel: Not directly supported in Apps Script. Use a relay or proxy.
• Use a managed MySQL service (PlanetScale, Aiven, etc.) that allows remote connections natively.

If your host blocks external MySQL access, use Method 2 (REST API) instead.

The Apps Script with JDBC

function syncWooCommerceMySQL() {
  const DB_HOST = 'your-db-host.example.com';
  const DB_PORT = 3306;
  const DB_NAME = 'your_wordpress_db';
  const DB_USER = 'your_db_user';
  const DB_PASS = PropertiesService.getScriptProperties().getProperty('DB_PASS');
  const SHEET_NAME = 'Orders';

  const connString = `jdbc:mysql://${DB_HOST}:${DB_PORT}/${DB_NAME}?useSSL=true`;
  const conn = Jdbc.getConnection(connString, DB_USER, DB_PASS);

  const query = `
    SELECT
      p.ID                          AS order_id,
      p.post_date                   AS order_date,
      p.post_status                 AS status,
      MAX(CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END) AS first_name,
      MAX(CASE WHEN pm.meta_key = '_billing_last_name'  THEN pm.meta_value END) AS last_name,
      MAX(CASE WHEN pm.meta_key = '_billing_email'      THEN pm.meta_value END) AS email,
      MAX(CASE WHEN pm.meta_key = '_order_total'        THEN pm.meta_value END) AS order_total,
      MAX(CASE WHEN pm.meta_key = '_order_currency'     THEN pm.meta_value END) AS currency,
      MAX(CASE WHEN pm.meta_key = '_payment_method'     THEN pm.meta_value END) AS payment_method,
      MAX(CASE WHEN pm.meta_key = '_transaction_id'     THEN pm.meta_value END) AS transaction_id
    FROM wp_posts p
    JOIN wp_postmeta pm ON pm.post_id = p.ID
    WHERE p.post_type = 'shop_order'
      AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-refunded')
      AND p.post_date >= DATE_SUB(NOW(), INTERVAL 90 DAY)
    GROUP BY p.ID, p.post_date, p.post_status
    ORDER BY p.post_date DESC
  `;

  const stmt = conn.createStatement();
  stmt.setMaxRows(10000);
  const results = stmt.executeQuery(query);

  const meta = results.getMetaData();
  const numCols = meta.getColumnCount();

  const headers = [];
  for (let i = 1; i <= numCols; i++) {
    headers.push(meta.getColumnName(i));
  }

  const data = [headers];
  while (results.next()) {
    const row = [];
    for (let i = 1; i <= numCols; i++) {
      row.push(results.getString(i));
    }
    data.push(row);
  }

  results.close();
  stmt.close();
  conn.close();

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  sheet.clearContents();
  sheet.getRange(1, 1, data.length, numCols).setValues(data);
  Logger.log(`Synced ${data.length - 1} orders from MySQL`);
}

For HPOS stores, replace the wp_posts/wp_postmeta query with a query against the new tables:

SELECT
  o.id          AS order_id,
  o.date_created_gmt AS order_date,
  o.status,
  o.total_amount AS order_total,
  o.currency,
  o.billing_email AS email,
  CONCAT(o.billing_first_name, ' ', o.billing_last_name) AS billing_name,
  o.payment_method
FROM wc_orders o
WHERE o.type = 'shop_order'
  AND o.date_created_gmt >= DATE_SUB(NOW(), INTERVAL 90 DAY)
ORDER BY o.date_created_gmt DESC
LIMIT 10000;

Method 4: Zapier or Make.com

Both platforms offer WooCommerce triggers and Google Sheets actions for no-code, event-driven syncs.

Zapier: Trigger on New Order, Action appends a row to Sheets. Setup takes about 15 minutes. Limitation: runs per-order and cannot backfill historical data.

Make.com (formerly Integromat): More powerful for multi-step scenarios: can batch orders and apply transformations before writing. Better value than Zapier at high order volumes.

Both tools use the WooCommerce REST API, so they inherit its limitations: no complex SQL, no direct database table access.

Method 5: WooCommerce Google Sheets Plugins

Several WordPress plugins export WooCommerce data to Google Sheets directly.

WP All Export: A full-featured export plugin with a drag-and-drop layout builder. The Pro version supports scheduled exports and a Google Sheets add-on. Free for manual CSV; Pro license required for scheduling and Sheets integration.

AutomateWoo: Primarily a marketing automation tool, but its webhook features can pipe order data to Sheets via Zapier or Make.

WooCommerce Marketplace plugins: Several vendors offer dedicated Sheets integrations. Evaluate on update frequency, HPOS compatibility, and whether the vendor is actively maintained.

The core limitation of all plugin approaches: you depend on the plugin vendor for compatibility with WooCommerce and WordPress updates. A major version bump can break things. For long-term reliability, prefer the REST API or direct database method.

Method 6: Third-Party Connectors

Dedicated data connectors sit between your WooCommerce store and Google Sheets and handle authentication, scheduling, and data transformation.

• Fivetran: WooCommerce connector via REST API. Full ELT pipeline. Best for stores that also want data in a warehouse.
• Airbyte: Open-source alternative to Fivetran. Self-hostable.
• brooked.io: Connects to MySQL directly (or via REST API), handles the wp_posts/wp_postmeta pivot automatically, and syncs to Google Sheets on a schedule. No WordPress plugin required, no direct database access required for the REST API path.
• Coupler.io: Spreadsheet-focused connector with a WooCommerce integration.

Third-party connectors make the most sense when you want reliable scheduling, incremental syncs (only new or updated orders), and a clean data model without writing SQL.

Comparison Table

Troubleshooting

REST API returns 401 Unauthorized Check that the Consumer Key and Secret are correct and that the API key is assigned to a user with admin or shop manager role. Also verify that WooCommerce > Settings > Advanced > REST API is enabled.

REST API returns empty order list You may be filtering by status incorrectly. The API accepts statuses without the wc- prefix (e.g., status=completed, not status=wc-completed). The wp_posts table uses the wc- prefix; the REST API strips it.

MySQL JDBC connection refused Your hosting provider is blocking external MySQL connections. Check your host's control panel for a "Remote MySQL" or "Remote Database Access" setting. You may need to whitelist Google's IP ranges.

The pivot query is slow on large stores Add the index: CREATE INDEX idx_postmeta_lookup ON wp_postmeta (post_id, meta_key);. Also narrow your date range with a WHERE p.post_date >= ... clause: avoid querying the full order history on every sync. Use incremental syncs after the initial backfill.

**Orders appear with wc- prefix in status column** This is the raw post_status value from wp_posts. To strip the prefix in SQL: REPLACE(p.post_status, 'wc-', '') AS status. The REST API returns statuses without the prefix.

Apps Script hits execution time limit You are probably pulling too many rows or appending row-by-row. Use setValues() with a batch array instead of appendRow() in a loop, and add a LIMIT clause to your query. If you still hit the limit, switch to incremental syncs that only pull orders from the last N days.

Bottom Line

There is no single best method for syncing WooCommerce orders to Google Sheets. It depends on your technical comfort level and what you need from the sync.

For most store owners without database access: use the REST API + Apps Script method. It is straightforward, does not require opening database ports, and can be scheduled with Google's trigger system.

For stores with thousands of orders and complex reporting needs: use the direct MySQL connection with the pivot query against wp_posts and wp_postmeta. It is the fastest approach for bulk data and gives you full SQL expressiveness.

For completely no-code setups: third-party connectors like brooked.io handle the complexity and let you focus on the data, not the plumbing.

Whichever method you choose, build incremental syncs for ongoing updates rather than pulling your full order history on every run. Your database and your sync quota will thank you.

Set Up Your Sync

brooked.io connects WooCommerce stores to Google Sheets via the REST API or direct MySQL, handles the wp_posts/wp_postmeta structure automatically, and runs on a schedule you control.

Related guides on brooked.io:

• Supabase to Google Sheets: Connection Pooler Gotchas and the Right Endpoint to Use
• Neon Postgres to Google Sheets: The Guide Nobody Has Written Yet
• MySQL to Google Sheets: Direct Connection Guide

Troubleshooting quick reference

Frequently asked questions