Auto-Fetch Shopify Orders into Google Sheets Using Apps Script

Sync orders with pagination, filters & timestamp — no plugins.

What You’ll Need

• Shopify access token (shpat_...)
• Your store name (e.g., yourstore)
• A Google Sheet

Step-by-Step Instructions

1. Open Your Google Sheet

Go to Extensions → Apps Script

2. Paste the Script

Replace credentials:

const SHOPIFY_STORE_NAME = 'yourstore';
const SHOPIFY_ACCESS_TOKEN = 'shpat_xxxxxxxxxxxxxxxx';

Then paste the full script below that handles:

• Sheet setup
• Pagination (page_info)
• Timestamp-based incremental sync
• Field mapping

const SHOPIFY_STORE_NAME = 'your-store-name';  // Replace with your Shopify store name
const SHOPIFY_ACCESS_TOKEN = 'shpat_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';  // Replace with your access token

function fetchShopifyOrders() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  let ordersSheet = spreadsheet.getSheetByName("Orders") || spreadsheet.insertSheet("Orders");
  let lastFetchSheet = spreadsheet.getSheetByName("lastfetchtime") || spreadsheet.insertSheet("lastfetchtime");

  let lastFetchTime = lastFetchSheet.getRange('A1').getValue();
  if (isNaN(new Date(lastFetchTime).getTime())) lastFetchTime = null;

  let url = `https://${SHOPIFY_STORE_NAME}.myshopify.com/admin/api/2023-01/orders.json?limit=50&order=processed_at asc`;
  url += lastFetchTime ? `&created_at_min=${new Date(lastFetchTime).toISOString()}&status=any` : `&status=any`;

  const options = {
    method: 'get',
    headers: {
      "Content-Type": "application/json",
      "X-Shopify-Access-Token": SHOPIFY_ACCESS_TOKEN
    }
  };

  let pageInfo = null;
  let ordersFetched = 0;

  if (!lastFetchTime) {
    const headers = ["Order Name", "Date", "Status", "Invoice Value", "GST", "Shipping Charges", "Shipping State", "Fulfillment Status", "Fulfillment Date", "Refunded", "Delivery Status", "AWB/Tracking Number", "Customer Name", "Customer Address", "Product Details"];
    ordersSheet.clear();
    ordersSheet.appendRow(headers);
  }

  do {
    let fetchUrl = pageInfo ? `https://${SHOPIFY_STORE_NAME}.myshopify.com/admin/api/2023-01/orders.json?limit=50&page_info=${pageInfo}` : url;

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

    if (!orders.length) break;

    orders.forEach(order => {
      const rowData = [
        order.name,
        order.created_at,
        order.financial_status,
        order.total_price,
        order.total_tax,
        order.shipping_lines.length > 0 ? order.shipping_lines[0].price : 0,
        order.shipping_address?.province || 'Unknown',
        order.fulfillment_status || 'Unfulfilled',
        order.fulfillments[0]?.created_at || 'Pending',
        order.refunds.length > 0 ? 'Yes' : 'No',
        order.fulfillments.length > 0 ? 'Shipped' : 'Pending',
        order.fulfillments[0]?.tracking_numbers[0] || 'Pending',
        order.shipping_address?.name || 'Unknown',
        order.shipping_address ? `${order.shipping_address.address1}, ${order.shipping_address.city}, ${order.shipping_address.province} - ${order.shipping_address.zip}, ${order.shipping_address.country}` : 'Unknown',
        order.line_items.map(item => `${item.title} (Qty: ${item.quantity})`).join(', ')
      ];
      ordersSheet.appendRow(rowData);
    });

    Utilities.sleep(1000);

    const linkHeader = response.getHeaders()['Link'];
    pageInfo = linkHeader?.includes('rel="next"') ? (linkHeader.match(/<([^>]+)>;\s*rel="next"/)?.[1].split('page_info=')[1]) : null;
    ordersFetched += orders.length;

  } while (pageInfo);

  lastFetchSheet.getRange('A1').setValue(new Date());
}

3. Run the Script Once

• Select fetchShopifyOrders
• Click ▶️ Run
• Approve OAuth access
• Orders appear in “Orders” sheet
• Timestamp is stored in “lastfetchtime”

4. What It Pulls

Each row includes:

• Order ID & Date
• Payment status & invoice value
• GST & shipping charges
• Fulfillment status + date
• Tracking number
• Customer name & address
• Product summary (Product A (Qty: 2))

5. How It Works

• First run → pulls all orders
• Future runs → pulls only new orders
• Handles Shopify pagination with page_info
• Sleeps between requests to stay within API limits
• Writes last fetch timestamp to avoid duplicates

Optional Upgrades

Set time-driven trigger (daily/weekly)

Add error logging or Slack/email alerts

Link to dashboards or Data Studio

Security Tip

Never share your shpat_ key publicly.
Use private sheets or store secrets in PropertiesService.