How to automate Shopify reports into Google Sheets

by

TL;DR

You can pull live Shopify order, sales, and product data into Google Sheets automatically using Shopify’s Admin API and a Google Apps Script trigger. setup takes about 45 to 90 minutes the first time. you need a Shopify store on any paid plan, a Google account, and basic comfort pasting and editing code snippets.

What You Need Before You Start

  • a Shopify store on any paid plan (Basic or higher) or a development store
  • a Google account with access to Google Sheets and Google Apps Script (both free)
  • Shopify API credentials: you will create a Custom App inside your Shopify admin, no third-party app installs required
  • the API scopes you need: read_orders, read_products, read_customers — add more depending on what you report on
  • basic comfort copying code snippets and changing a few variable values
  • optional: Coupler.io or Make if you want a fully no-code path (covered in “When To Level Up”)
  • optional: a Slack incoming webhook URL for error notifications

Step 1: Create a Shopify Custom App and Get Your API Token

Go to your Shopify admin panel. click Settings in the bottom-left corner, then Apps and sales channels, then Develop apps. click Create an app, name it something like “Sheets Reporter”, and hit Create app.

once the app is created, click Configure Admin API scopes. check read_orders, read_products, and read_customers. save your selections.

now click Install app, confirm the installation, and copy the Admin API access token that appears on screen. Shopify only shows this token once. paste it into a password manager immediately. also note your store URL, which looks like your-store.myshopify.com.

you should now see a green “Installed” badge on your app and your access token saved securely somewhere you can retrieve it.

Step 2: Set Up Your Google Sheet Structure

open Google Sheets and create a new spreadsheet. name it something unambiguous like “Shopify Sales Automation 2026”.

add three tabs at the bottom of the sheet: Orders, Products, and Config. on the Config tab, set up columns A and B like this:

A B
shopify_store your-store.myshopify.com
api_token paste-your-token-here
orders_limit 250
date_range_days 30

keeping credentials in the sheet rather than hardcoded in the script means you can update them without touching any code.

on the Orders tab, add these headers in row 1: Order ID, Created At, Customer Email, Total Price, Financial Status, Fulfillment Status, Items Count.

you should now see a clean three-tab sheet with headers on the Orders tab and credentials stored in Config.

Step 3: Open the Apps Script Editor

from your Google Sheet, click Extensions in the top menu, then Apps Script. a new browser tab opens with the script editor. delete the default myFunction() stub that appears automatically.

Apps Script runs on Google’s servers. nothing needs to be installed locally. it uses JavaScript, but you will not write this from scratch.

rename the project from “Untitled project” to “Shopify Sheets Sync” using the title field at the very top of the page. this makes it easy to find later in your Google account’s script library under myaccount.google.com/permissions.

you should now see a blank editor with your project renamed and ready to receive code.

Step 4: Add the Config Reader Function

paste the following function into the editor. it reads your credentials from the Config tab each time the script runs, so secrets never live directly in the code.

function getConfig() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Config');
  const data = sheet.getDataRange().getValues();
  const config = {};
  data.forEach(row => {
    config[row[0]] = row[1];
  });
  return config;
}

this loops every row in Config and builds a plain key-value object. anywhere else in the script you can call config['shopify_store'] or config['api_token'] without hardcoding anything.

you should now have one function defined with no red errors showing in the editor gutter.

Step 5: Fetch Orders from the Shopify API

paste the fetch function below the config reader. this is the function that actually calls Shopify’s REST API.

function fetchShopifyOrders() {
  const config = getConfig();
  const store = config['shopify_store'];
  const token = config['api_token'];
  const days = parseInt(config['date_range_days']) || 30;

  const since = new Date();
  since.setDate(since.getDate() - days);
  const sinceISO = since.toISOString();

  const url = `https://${store}/admin/api/2024-04/orders.json?status=any&created_at_min=${sinceISO}&limit=250`;

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

  const response = UrlFetchApp.fetch(url, options);
  const json = JSON.parse(response.getContentText());
  return json.orders || [];
}

the 2024-04 in the URL is the API version. Shopify releases new versions quarterly. 2024-04 is a stable supported version as of writing. check the Shopify changelog every six months if you run this long-term. for more on reading Shopify analytics data, see shopify analytics tools compared.

click Run on fetchShopifyOrders. Apps Script will ask for permission to make external requests. approve it.

you should now see a successful execution with no exceptions in the log output at the bottom of the editor.

Step 6: Write Orders to the Sheet

paste this function to take the raw API response and populate your Orders tab.

function writeOrdersToSheet(orders) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Orders');

  const lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    sheet.getRange(2, 1, lastRow - 1, 7).clearContent();
  }

  const rows = orders.map(order => [
    order.id,
    order.created_at,
    order.email || '',
    parseFloat(order.total_price),
    order.financial_status,
    order.fulfillment_status || 'unfulfilled',
    order.line_items ? order.line_items.length : 0
  ]);

  if (rows.length > 0) {
    sheet.getRange(2, 1, rows.length, 7).setValues(rows);
  }
}

then add the main entry point that wires everything together.

function syncShopifyToSheets() {
  const orders = fetchShopifyOrders();
  writeOrdersToSheet(orders);
  Logger.log(`Synced ${orders.length} orders.`);
}

run syncShopifyToSheets manually from the editor.

you should now see your Orders tab filled with real data from your store, matching the seven column headers you added in Step 2.

Step 7: Handle Pagination for Stores with High Order Volume

Shopify caps each API response at 250 records. if your store has more than 250 orders in your date window, the basic fetch misses everything past the first page. Shopify uses cursor-based pagination via a Link response header.

replace your fetchShopifyOrders function entirely with this version.

function fetchShopifyOrders() {
  const config = getConfig();
  const store = config['shopify_store'];
  const token = config['api_token'];
  const days = parseInt(config['date_range_days']) || 30;

  const since = new Date();
  since.setDate(since.getDate() - days);
  const sinceISO = since.toISOString();

  let allOrders = [];
  let url = `https://${store}/admin/api/2024-04/orders.json?status=any&created_at_min=${sinceISO}&limit=250`;

  const headers = {
    'X-Shopify-Access-Token': token,
    'Content-Type': 'application/json'
  };

  while (url) {
    const response = UrlFetchApp.fetch(url, { method: 'get', headers, muteHttpExceptions: true });
    const json = JSON.parse(response.getContentText());
    allOrders = allOrders.concat(json.orders || []);

    const linkHeader = response.getHeaders()['Link'] || '';
    const nextMatch = linkHeader.match(/<([^>]+)>;\s*rel="next"/);
    url = nextMatch ? nextMatch[1] : null;
  }

  return allOrders;
}

run syncShopifyToSheets again.

you should now see the full order count across all pages, not just the first 250.

Step 8: Schedule the Sync to Run Automatically

in the Apps Script editor, click the clock icon in the left sidebar (Triggers). click Add Trigger at the bottom right.

configure it like this:

  • Function to run: syncShopifyToSheets
  • Deployment: Head
  • Event source: Time-driven
  • Type of time-based trigger: Day timer
  • Time of day: 6am to 7am

this fires the sync every morning before you open your dashboard. if you need fresher data during a sale or product launch, switch to an hourly trigger instead.

click Save. Google will prompt for one more permission approval. approve it.

you should now see one active trigger listed in the Triggers panel with a “Last run” column that populates after the first automated execution fires.

Step 9: Format the Sheet and Build a Summary Dashboard

raw data is not a report. spend five minutes making it readable. select the Financial Status column. go to Format then Conditional formatting. add three rules: text equals paid gets a light green background, refunded gets light red, pending gets light yellow.

repeat for Fulfillment Status: fulfilled in green, unfulfilled in orange.

create a new tab called Dashboard. add these formulas to pull live summary numbers from your Orders tab.

=COUNTIF(Orders!E:E,"paid")
=SUMIF(Orders!E:E,"paid",Orders!D:D)
=COUNTIF(Orders!F:F,"unfulfilled")
=AVERAGEIF(Orders!E:E,"paid",Orders!D:D)

for more advanced dashboard layouts that work with live-refreshing data like this, see how to build a Google Sheets dashboard from live data.

you should now see a color-coded orders tab and a small summary section pulling live counts and revenue figures.

Common Mistakes To Avoid

  • hardcoding your API token inside the script. if you share the Apps Script file or the spreadsheet with edit access, the token is exposed. always read from the Config tab or Apps Script’s PropertiesService.
  • skipping muteHttpExceptions: true. without it, one failed API call crashes the entire script without a useful error message. with it, you can log the failure and investigate.
  • never updating the API version string. Shopify deprecates old API versions on a rolling basis. set a calendar reminder every six months to check whether your version is still supported.
  • pulling too many days of data on a high-volume store. Apps Script has a hard 6-minute execution limit. 90 days of orders from a store doing 500 orders a day will time out every time. keep date_range_days to 30 or less, or batch your date windows.
  • accidentally clearing your headers row. the clearContent call in Step 6 starts from row 2. if you ever adjust the range to start from row 1, you wipe your column headers on every sync.
  • ignoring trigger failure emails. Google sends an email when a trigger fails. most founders filter these out. route them to a dedicated label or set up a Slack webhook alert so broken syncs surface quickly.

When To Level Up

the Apps Script approach works cleanly for stores doing a few hundred orders a day reporting in a single currency. it starts showing cracks in a few specific situations.

if you run multiple Shopify stores, managing separate scripts for each becomes an operational headache fast. Coupler.io handles multi-store Shopify connections out of the box and refreshes on a schedule with no code at all. Supermetrics covers the same ground with more transformation and blending options.

if you need real-time data, say for a live operations dashboard during a flash sale, a script that runs hourly is too slow. you need a webhook-driven pipeline, which is a different architecture entirely.

if other people on your team need to modify the sync logic and they are not comfortable with code, a visual tool like Make gives them a drag-and-drop interface. the same outcome, none of the script editing.

if you hit the 6-minute execution limit regularly, that is your clearest signal to move to a proper ETL pipeline. for a full comparison of tools in this space, browse /category/automation/.

Frequently Asked Questions

does this work on Shopify’s free trial?
no. the Admin API and custom app creation require a paid Shopify plan. Basic is the minimum. if you are testing, Shopify offers free development stores through the partner program that do have API access.

how often can I refresh the data?
free personal Gmail accounts can trigger Apps Script as frequently as every hour. Google Workspace accounts can go down to every minute. for most ecommerce reporting, a daily morning sync is enough for decision-making.

what if I want to pull product inventory data too?
use the same fetch pattern with the /admin/api/2024-04/products.json endpoint and make sure your custom app has the read_products scope enabled. add a writeProductsToSheet function that mirrors the orders version and call it from syncShopifyToSheets.

is it safe to store my API token in a Google Sheet?
reasonably safe as long as the sheet is private and you control edit access carefully. for stricter security, store the token using PropertiesService.getScriptProperties().setProperty('api_token', 'your-token') inside Apps Script. that way the token never appears in the sheet at all.

can I connect this data to Looker Studio instead of using Sheets formulas?
yes. once the data lands in Sheets, Looker Studio can connect to it directly as a data source using the Google Sheets connector. every time your trigger refreshes the sheet data, Looker Studio picks it up on the next dashboard load without any extra configuration.

Bottom Line

automating Shopify reports into Google Sheets does not require a paid tool or a developer on retainer. create a Shopify custom app for API access, store your credentials in a Config tab, write a short Apps Script fetch function with pagination, and set a daily time trigger. the whole pipeline takes under two hours to build and runs every morning without you touching it. you get fresh order data, color-coded statuses, and summary formulas waiting when you open your laptop. when your store volume or your team outgrows what a script can handle, move to a connector built for the job. for tool reviews and more automation workflows that build on this foundation, visit /category/automation/.