Sync Shopify orders into Sheets

Northwind runs a Shopify store, and the finance team needs that order data in a spreadsheet — not in the Shopify admin. Reconciling payouts, building a weekly revenue chart, joining orders against the support log: all of it happens in Sheets. The usual fix is someone exporting a CSV every Monday, which is dull, easy to forget, and always a few days stale.

This script pulls Northwind’s recent Shopify orders straight into a sheet on a schedule. Each run mirrors the latest orders — id, name, date, total, currency and payment status — so the finance tab is never more than a day behind the store.

What you’ll need

• A Google Sheet to hold the mirrored orders. The script writes to its first tab and creates the header row itself.
• A Shopify private app (or custom app) with the read_orders scope. Copy its Admin API access token.
• That token saved as SHOPIFY_TOKEN in Script Properties — see Store API keys and secrets securely.
• Your store’s *.myshopify.com domain and the sheet ID, set in the config block below.

The script

// Your store's permanent myshopify.com domain (not a custom domain).
const SHOP = 'northwind.myshopify.com';

// The spreadsheet that mirrors the orders.
const ORDERS_SHEET_ID = '1abcShopifyId';

// Shopify Admin API version. Pin it so the response shape never shifts
// under you — see "Watch out for".
const API_VERSION = '2024-07';

// How many orders to pull per run. 100 reflects the recent activity
// the finance team actually reconciles each day.
const ORDER_LIMIT = 100;

// The columns written to the sheet, in order.
const HEADERS = ['id', 'name', 'date', 'total', 'ccy', 'status'];

/**
 * Pulls the most recent Shopify orders and rewrites the orders sheet
 * so it mirrors the store.
 */
function pullShopifyOrders() {
  // 1. Read the Admin API token from Script Properties — never inline it.
  const token = PropertiesService.getScriptProperties()
    .getProperty('SHOPIFY_TOKEN');
  if (!token) {
    Logger.log('No SHOPIFY_TOKEN set — add it in Script Properties.');
    return;
  }

  // 2. Ask Shopify for the latest orders. status=any includes open,
  //    closed and cancelled orders so nothing is silently dropped.
  const url = 'https://' + SHOP + '/admin/api/' + API_VERSION +
    '/orders.json?status=any&limit=' + ORDER_LIMIT;
  const response = UrlFetchApp.fetch(url, {
    headers: { 'X-Shopify-Access-Token': token },
    muteHttpExceptions: true,
  });

  // 3. Bail out clearly if the request failed (bad token, wrong scope).
  if (response.getResponseCode() !== 200) {
    Logger.log('Shopify request failed: ' + response.getContentText());
    return;
  }

  // 4. Flatten each order into a spreadsheet row.
  const orders = JSON.parse(response.getContentText()).orders;
  const rows = orders.map((o) => [
    o.id,
    o.name,
    new Date(o.created_at),
    o.total_price,
    o.currency,
    o.financial_status,
  ]);

  // 5. Rewrite the sheet from scratch so it always reflects the store.
  const sheet = SpreadsheetApp.openById(ORDERS_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, HEADERS.length).setValues(rows);
  }
  Logger.log('Mirrored ' + rows.length + ' Shopify orders.');
}

How it works

1. pullShopifyOrders reads the SHOPIFY_TOKEN from Script Properties. If it is missing the script logs a message and stops, so a misconfigured project fails loudly rather than silently.
2. It builds the Admin API URL from the pinned API_VERSION and asks for the latest ORDER_LIMIT orders. status=any is important — without it Shopify only returns open orders and your totals will quietly miss fulfilled ones.
3. muteHttpExceptions lets the script inspect the response itself. A non-200 code (usually a bad token or a missing scope) is logged and the run stops.
4. Each order object is flattened to a fixed set of six columns. created_at is wrapped in new Date() so the sheet stores a real date, not a string.
5. The first tab is cleared and rewritten: header row, then one row per order. Because it is a full rewrite, the sheet is always an exact mirror of the most recent orders.

Example run

The store has these recent orders in the Shopify admin:

Order	Customer	Total	Status
#1042	Acme Ltd	240.00 GBP	paid
#1043	Bevan & Co	89.50 GBP	pending
#1044	Corsair Design	1,310.00 GBP	paid

After a run, the first tab of the orders sheet holds:

id	name	date	total	ccy	status
5511234567	#1042	2026-05-24	240.00	GBP	paid
5511234568	#1043	2026-05-24	89.50	GBP	pending
5511234569	#1044	2026-05-25	1310.00	GBP	paid

The finance team can now pivot, chart and reconcile against that tab without ever opening Shopify.

Trigger it

This works best as a scheduled job so the sheet stays fresh without anyone thinking about it:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger: choose pullShopifyOrders, event source Time-driven, and a Day timer running in the early morning.
3. Save and approve the authorisation prompt the first time.

A daily run keeps the finance tab no more than a day behind the store. For near-real-time mirroring, switch to an hourly timer.

Watch out for

• This pulls the most recent 100 orders only — it is a mirror of recent activity, not a full historical export. For a busy store, paginate with Shopify’s page_info cursor or filter by created_at_min to walk the whole order history.
• Pin API_VERSION. Shopify ships quarterly versions and eventually retires old ones; an unpinned call can change shape without warning. Bump it deliberately and re-test.
• The token needs the read_orders scope. A token without it returns a 403, which the response-code guard will catch and log.
• Shopify rate-limits the Admin API (roughly two requests per second on the REST API). A single daily call is well within budget, but a paginating version should pause between pages.
• A full clear() then rewrite means any manual notes added to the sheet are wiped each run. Keep annotations on a separate tab.