Send WooCommerce orders to Google Sheets

This guide is based on the public woo-google-sheets repository. The original project is available under the MIT License.

What the script does

It reads your store URL, REST API credentials, and start date from the active sheet; requests WooCommerce orders in pages of 100; converts useful billing, shipping, payment, line-item, and order fields into rows; and writes them to the sheet in one batch.

Prepare WooCommerce and the sheet

In WordPress, open WooCommerce → Settings → Advanced → REST API and create a read-only key for a dedicated integration user.
Create a Google Sheet and put the store URL in B3, consumer key in B4, consumer secret in B5, and earliest order date in B6.
Open Extensions → Apps Script, paste the code below, save, and run startSync.
Review the requested Google permissions before authorizing the script.

Credential note: protect access to the spreadsheet. For a shared or production document, move the key and secret into Apps Script Properties instead of visible cells.

Copy-ready Apps Script

function startSync() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const settings = {
    website: String(sheet.getRange("B3").getValue()).replace(/\/$/, ""),
    consumerKey: String(sheet.getRange("B4").getValue()),
    consumerSecret: String(sheet.getRange("B5").getValue()),
    after: new Date(sheet.getRange("B6").getValue()).toISOString(),
  };

  const orders = fetchOrders(settings);
  const rows = orders.map(orderToRow);

  if (rows.length === 0) {
    SpreadsheetApp.getUi().alert("No orders found for this date range.");
    return;
  }

  const firstOutputRow = 8;
  sheet
    .getRange(firstOutputRow, 1, sheet.getMaxRows() - firstOutputRow + 1, rows[0].length)
    .clearContent();
  sheet.getRange(firstOutputRow, 1, rows.length, rows[0].length).setValues(rows);
}

function fetchOrders(settings) {
  const orders = [];
  const authorization = Utilities.base64Encode(
    settings.consumerKey + ":" + settings.consumerSecret
  );

  for (let page = 1; ; page += 1) {
    const query = [
      "after=" + encodeURIComponent(settings.after),
      "per_page=100",
      "page=" + page,
    ].join("&");
    const response = UrlFetchApp.fetch(
      settings.website + "/wp-json/wc/v3/orders?" + query,
      {
        method: "get",
        headers: { Authorization: "Basic " + authorization },
        muteHttpExceptions: true,
      }
    );

    if (response.getResponseCode() !== 200) {
      throw new Error(
        "WooCommerce returned " +
          response.getResponseCode() +
          ": " +
          response.getContentText()
      );
    }

    const pageOrders = JSON.parse(response.getContentText());
    if (pageOrders.length === 0) break;

    orders.push.apply(orders, pageOrders);
    if (pageOrders.length < 100) break;
  }

  return orders;
}

function orderToRow(order) {
  const billing = order.billing || {};
  const shipping = order.shipping || {};
  const items = (order.line_items || [])
    .map(item => item.quantity + " x " + item.name)
    .join(",\n");
  const quantity = (order.line_items || []).reduce(
    (total, item) => total + item.quantity,
    0
  );
  const refunds = (order.refunds || [])
    .map(refund => refund.total + " - " + (refund.reason || "Refund"))
    .join(",\n");

  return [
    billing.first_name || "",
    billing.last_name || "",
    joinAddress(billing),
    billing.phone || "",
    billing.email || "",
    joinAddress(shipping),
    order.customer_note || "",
    order.payment_method_title || "",
    order.total || "",
    order.total_tax || "",
    order.discount_total || "",
    order.refunded_total || "",
    items,
    quantity,
    refunds,
    order.number || "",
    order.date_created || "",
    order.date_modified || "",
    order.status || "",
    order.order_key || "",
    order.currency || "",
    order.cart_tax || "",
    order.shipping_total || "",
    order.shipping_tax || "",
  ];
}

function joinAddress(address) {
  return [
    address.first_name,
    address.last_name,
    address.address_1,
    address.postcode,
    address.city,
    address.country,
  ].filter(Boolean).join(" ");
}

Why pagination matters

WooCommerce limits collection responses. The loop requests 100 records at a time and advances the page parameter until a short or empty page arrives. Without this step, a report may appear correct while silently excluding older orders.

Why this version writes in a batch

Calling appendRow for every order creates many round trips to the Sheets service. Building an array and using one setValues call is faster and makes the output range predictable. The script also fails visibly when WooCommerce returns an error instead of treating a failed request as a completed sync.

Adapt the exported columns

The order of values returned by orderToRow controls the sheet columns. Add headers in row 7, then remove or rearrange values to match the report. The WooCommerce order properties reference lists additional fields.

Source and next steps

Inspect the original Code.gs source, open an issue, or fork the repository on GitHub. For scheduled reporting, add a time-driven Apps Script trigger after validating the output manually.