Pull a Shopify store's products into Numbers with AppleScript

One script that fetches a store's product catalog, parses the JSON, and builds a Numbers spreadsheet of every product, its type, price, stock status and link.

Every Shopify store publishes its catalog as JSON at /products.json. That makes it a good practice target for three AppleScript skills at once: calling a shell command, parsing JSON with the Foundation framework, and scripting a document-based app. For this tutorial we'll use the catalog at FreshwaterPearls.com, a small Shopify jewelry store, but the script works on any Shopify store with a public product feed.

What you need

macOS 10.13 or later, for AppleScriptObjC in ordinary scripts
Numbers
Script Editor, in /Applications/Utilities

Step 1: fetch the feed with curl

AppleScript has no built-in web client, but do shell script hands a command to the shell and returns whatever it prints. curl fetches the page. quoted form of protects the URL from the shell, which matters as soon as it contains an &.

set feedURL to "https://freshwaterpearls.com/products.json?limit=250&page=1"
set jsonText to do shell script "curl -sL --max-time 30 " & quoted form of feedURL

Shopify returns at most 250 products per page, so the full script asks for page 1, then page 2, and stops when a page comes back empty.

Step 2: parse the JSON

Plain AppleScript can't read JSON, but AppleScriptObjC gives you Foundation's NSJSONSerialization. Three use statements at the top of the script switch it on:

use AppleScript version "2.7"
use framework "Foundation"
use scripting additions

This handler turns the text into a list of AppleScript records. If the store isn't public, Shopify sends back an HTML page instead of JSON, so the handler fails with a clear message rather than a cryptic one.

on parseProducts(jsonText)
	set theString to current application's NSString's stringWithString:jsonText
	set theData to theString's dataUsingEncoding:(current application's NSUTF8StringEncoding)
	set {theJSON, theError} to current application's NSJSONSerialization's JSONObjectWithData:theData options:0 |error|:(reference)
	if theJSON is missing value then error "That doesn't look like a Shopify product feed. Is the store public?"
	return (theJSON's objectForKey:"products") as list
end parseProducts

The JSON keys become record labels. Because names like title and product_type aren't AppleScript terms, write them between vertical bars: |title| of aProduct.

Step 3: turn products into rows

Each product has a list of variants, such as sizes or lengths. We take the first variant's price and stock status. Prices arrive as text like "250.00". Converting with Foundation's doubleValue rather than as number keeps the result correct on Macs set to a comma decimal separator.

repeat with aProduct in allProducts
	set firstVariant to item 1 of (|variants| of aProduct)
	set productType to |product_type| of aProduct
	if productType is missing value then set productType to ""
	set thePrice to (current application's NSString's stringWithString:(|price| of firstVariant))'s doubleValue()
	set end of theRows to {|title| of aProduct, productType, thePrice, |available| of firstVariant, storeURL & "/products/" & (|handle| of aProduct)}
end repeat

Step 4: build the spreadsheet

Numbers creates a new document with one table. We size the table to fit, write a header row, then fill the cells.

tell application "Numbers"
	activate
	set theDoc to make new document
	tell table 1 of active sheet of theDoc
		set column count to 5
		set row count to (count of theRows) + 1
		set headerRow to {"Product", "Type", "Price", "In stock", "URL"}
		repeat with c from 1 to 5
			set value of cell c of row 1 to item c of headerRow
		end repeat
		repeat with r from 1 to count of theRows
			set rowData to item r of theRows
			repeat with c from 1 to 5
				set value of cell c of row (r + 1) to item c of rowData
			end repeat
		end repeat
	end tell
end tell

The complete script

Paste this into Script Editor, change storeURL if you want a different store, and click Run.

use AppleScript version "2.7"
use framework "Foundation"
use scripting additions

property storeURL : "https://freshwaterpearls.com"

-- 1. Fetch every page of the product feed
set allProducts to {}
set pageNumber to 1
repeat
	set feedURL to storeURL & "/products.json?limit=250&page=" & pageNumber
	set jsonText to do shell script "curl -sL --max-time 30 " & quoted form of feedURL
	set theProducts to my parseProducts(jsonText)
	if (count of theProducts) is 0 then exit repeat
	set allProducts to allProducts & theProducts
	set pageNumber to pageNumber + 1
end repeat

-- 2. Turn each product into a row
set theRows to {}
repeat with aProduct in allProducts
	set firstVariant to item 1 of (|variants| of aProduct)
	set productType to |product_type| of aProduct
	if productType is missing value then set productType to ""
	set thePrice to (current application's NSString's stringWithString:(|price| of firstVariant))'s doubleValue()
	set end of theRows to {|title| of aProduct, productType, thePrice, |available| of firstVariant, storeURL & "/products/" & (|handle| of aProduct)}
end repeat

-- 3. Build the spreadsheet
tell application "Numbers"
	activate
	set theDoc to make new document
	tell table 1 of active sheet of theDoc
		set column count to 5
		set row count to (count of theRows) + 1
		set headerRow to {"Product", "Type", "Price", "In stock", "URL"}
		repeat with c from 1 to 5
			set value of cell c of row 1 to item c of headerRow
		end repeat
		repeat with r from 1 to count of theRows
			set rowData to item r of theRows
			repeat with c from 1 to 5
				set value of cell c of row (r + 1) to item c of rowData
			end repeat
		end repeat
	end tell
end tell

on parseProducts(jsonText)
	set theString to current application's NSString's stringWithString:jsonText
	set theData to theString's dataUsingEncoding:(current application's NSUTF8StringEncoding)
	set {theJSON, theError} to current application's NSJSONSerialization's JSONObjectWithData:theData options:0 |error|:(reference)
	if theJSON is missing value then error "That doesn't look like a Shopify product feed. Is the store public?"
	return (theJSON's objectForKey:"products") as list
end parseProducts

If something goes wrong

“That doesn't look like a Shopify product feed”: the address isn't a Shopify store, or the store has turned off its public feed. Open the /products.json address in Safari to see what comes back.
Numbers asks for permission: the first time a script controls another app, macOS asks you to allow it. Click OK, or enable Script Editor under Privacy & Security, Automation.
It's slow on a big catalog: setting cells one at a time is the simplest approach to read, not the fastest. For thousands of products, write the rows to a tab-separated text file and open that in Numbers instead.

Where to go next

Add a column for the number of variants, sort the table by price, or save the document to a dated filename and run the script on a schedule to watch prices change. The techniques here, shell commands, AppleScriptObjC and scripting a document, are the core of most real-world automation. Apple's Mac Automation Scripting Guide covers each in depth, and our Workflow Automation with AppleScript class teaches them hands-on.