Skip to content

Instantly share code, notes, and snippets.

@coreymartella
Last active August 16, 2023 14:32
Show Gist options
  • Save coreymartella/d24c8eab2802206744304dd111cc4546 to your computer and use it in GitHub Desktop.
Save coreymartella/d24c8eab2802206744304dd111cc4546 to your computer and use it in GitHub Desktop.
Shopify Order Exporter
*.csv*
*.zip*

CSV Order Exporter

This script produces a CSV export mimicing the Shopify Admin export. It paginates orders and writes to a CSV file.

Prerequisites

  • Ruby
  • Gems: active_support, shopify_api, retriable (gem install active_support shopify_api retriable)

Running the script

The script requires several variables:

  • SHOP (i.e myshop if https://myshop.myshopify.com is your shop url)
  • API_KEY private app api key
  • PASSWORD private app password
  • DATE the date to export orders in YYYY-MM-DD format

Example invocation:

SHOP=myshop API_KEY=key PASSWORD=secret DATE=2018-10-17 ruby export_orders.rb

the CSV file will be saved in the directory as myshop_orders_2018-10-17_<timestamp>.csv where <timestamp> is the time the export started.

#!/usr/bin/env ruby
# invoke this script with ENV vars:
# SHOP=myshop API_KEY=private_app_api_key PASSWORD=private_app_password [DATE=YYYY-MM-DD] ruby export_orders.rb
require 'shopify_api'
require 'active_support'
require 'active_support/core_ext'
require 'csv'
require 'retriable'
class CSVOrderExporter
attr_accessor :date, :since_id
def initialize
connect
end
def perform
total = [ShopifyAPI::Order.count(params), max_records].compact.min
puts "Exporting #{total} orders on #{date} for #{ENV["SHOP"]} to #{filename}"
processed = 0
skipped = 0
page = 1
more_records = true
while more_records
orders = Retriable.retriable{ShopifyAPI::Order.all(params: params.merge(page: page, limit: page_size))}
orders.each_with_index do |o,i|
if seen_ids.include?(o.id)
skipped += 1
next
end
printf("\rOrder %14s", o.id);STDOUT.flush
write_order(o)
seen_ids << o.id
processed += 1
per_iteration = (Time.now - start_time)/processed
est = (start_time + (per_iteration*total))
printf("%6d/%d ETA: %-20s (%.2f hours)", processed+skipped, total, est, (est-Time.now)/1.hour);STDOUT.flush
break if max_records && max_records <= processed
end
break if max_records && max_records <= processed
page += 1
more_records = orders.size == page_size
end
csv.close
puts "#{Time.now} Wrote #{processed} in #{Time.now-start_time} to #{filename}"
end
protected
def date
@date ||= begin
d = (Date.parse(ENV["DATE"]) rescue nil)
raise("Unable to parse date, pass DATE=YYYY-MM-DD") if ENV["DATE"].present? && !d
d
end
end
def params
#TODO is created_at_max <= or < ? do we need a +1 on it?
@params ||= begin
h = {order: "created_at asc", status: "any"}
if ENV["PARAMS"] && (JSON.parse(ENV["PARAMS"]) rescue nil)
h.merge!(JSON.parse(ENV["PARAMS"]).with_indifferent_access)
end
h.merge!(created_at_min: date.in_time_zone.iso8601, created_at_max: date.at_end_of_day.iso8601) if date
h
end
end
def seen_ids
@seen_ids ||= Set.new
end
def start_time
@start_time ||= Time.now
end
def max_records
ENV["MAX_RECORDS"].presence&.to_i
end
def connect
raise "SHOP must be specified" unless ENV["SHOP"]
start = Time.now
if ENV["TOKEN"]
session = ShopifyAPI::Session.new("#{ENV["SHOP"]}.myshopify.com", ENV["TOKEN"])
ShopifyAPI::Base.activate_session(session)
elsif ENV["API_KEY"] && ENV["PASSWORD"]
ShopifyAPI::Base.site = "https://#{ENV["API_KEY"]}:#{ENV["PASSWORD"]}@#{ENV["SHOP"]}.myshopify.com/admin"
else
raise "TOKEN or API_KEY and PASSWORD must be provided"
end
shop = ShopifyAPI::Shop.current
Time.zone = shop.iana_timezone
shop
end
def filename
"#{ENV["SHOP"]}_orders_#{date}_#{Time.now.strftime("%Y%m%d%H%M%S")}.csv"
end
def csv
@csv ||= begin
csv = CSV.open(filename, "wb")
csv << headers
csv
end
end
def page_size
page_size = ENV["PAGE_SIZE"].to_i
page_size = 50 if page_size < 1 || page_size > 50
page_size
end
def headers
@headers ||= ATTRIBUTES.map do |field,extras|
if field == :tax_line_details
1.upto(5).map{|i| ["Tax #{i} Name","Tax #{i} Value"]}
else
extras && extras[:label_name] ? extras[:label_name] : field.to_s.humanize
end
end.flatten
end
def write_order(o)
transactions = Retriable.retriable{ShopifyAPI::Transaction.find(:all, :params => {order_id: o.id, fields: [:id, :kind, :status, :amount, :created_at, :gateway, :receipt, :source] })}
payment_reference_transaction = transactions.select { |t| %w(authorization sale).include?(t.kind) && t.status = "success" }.last
payment_reference_receipt = payment_reference_transaction.try(:receipt)
total_received = transactions.select{|t| %w(capture sale).include?(t.kind) && t.status == "success"}.map(&:amount).map(&:to_d).sum
total_received -= transactions.select{|t| %w(change).include?(t.kind) && t.status == "success"}.map(&:amount).map(&:to_d).sum
paid_at = transactions.detect{|t| t.kind == "capture" && t.status == "success"}.try(:created_at)
billing_address = o.try(:billing_address) || o.try(:customer).try(:default_address)
row = [
o.name,
o.contact_email,
o.financial_status || 'pending',
paid_at ? Time.parse(paid_at).to_s : nil,
o.fulfillment_status || "unfulfilled",
o.fulfillment_status == "fulfilled" ? o.fulfillments.map{|f| Time.parse(f.created_at)}.max.to_s : "", #GAP fulfilled_at
(o.try(:customer)&.accepts_marketing ? "yes" : "no"),
o.currency,
o.subtotal_price,
o.shipping_lines.map(&:price).map(&:to_d).sum,
o.total_tax,
o.total_price,
o.discount_applications{|d| d.try(:code)}.compact.first,
o.total_discounts,
o.shipping_lines.first&.title,
(o.created_at ? Time.parse(o.created_at).to_s : nil),
o.line_items.first&.quantity,
o.line_items.first&.name,
o.line_items.first&.price,
"", #GAP: missing compare_at_price from line item w/o product data
o.line_items.first&.sku,
o.line_items.first&.requires_shipping,
o.line_items.first&.taxable,
o.line_items.first&.fulfillment_status,
billing_address&.name.presence || [o.try(:customer).try(:first_name), o.try(:customer).try(:last_name)].compact.join(" "),
[billing_address&.address1, billing_address&.address2].reject(&:blank?).join(", ").presence,
billing_address&.address1, # {:label_name=>"Billing Address1"}
billing_address&.address2, # {:label_name=>"Billing Address2"}
billing_address&.company, # {:label_name=>"Billing Company"}
billing_address&.city, # {:label_name=>"Billing City"}
billing_address&.zip, # {:label_name=>"Billing Zip", :force_string_in_excel=>true}
billing_address&.province_code, # {:label_name=>"Billing Province"}
billing_address&.country_code, # {:label_name=>"Billing Country"}
billing_address&.phone,
o.try(:shipping_address)&.name,
[o.try(:shipping_address)&.address1, o.try(:shipping_address)&.address2].reject(&:blank?).join(", ").presence,
o.try(:shipping_address)&.address1,
o.try(:shipping_address)&.address2,
o.try(:shipping_address)&.company,
o.try(:shipping_address)&.city,
o.try(:shipping_address)&.zip,
o.try(:shipping_address)&.province_code,
o.try(:shipping_address)&.country_code,
o.try(:shipping_address)&.phone,
o.note,
o.note_attributes.map{ |na| "#{na.name}:, #{na.value}" }.join("\n"),
(o.cancelled_at ? Time.parse(o.cancelled_at).to_s : nil),
# GAP: provider name mismatches gateway for Bambora vs beanstream..
transactions&.first&.gateway,
# GAP: payment_reference varies by provider, not sure of the consistent way to get OrderTransaction#name
payment_reference_receipt.try(:trnOrderNumber) || payment_reference_receipt.try(:receipt_id) || payment_reference_transaction.try(:authorization) || payment_reference_transaction&.id,
(total_refunded = transactions.select{|t| t.kind == "refund" && t.status == "success"}.map(&:amount).map(&:to_d).sum),
o.line_items.first.vendor,
o.id,
o.tags,
"", # GAP: need to get OrderRisk for risk_level_for
o.source_name,
o.line_items.first.total_discount,
] +
(1..5).map do |tl_i|
tl = o.tax_lines[tl_i-1]
#HACK rounded tax rate
[
(tl ? "#{tl.title} #{(tl.rate.to_d*100).round}%" : ""),
tl&.price
]
end.flatten + [o.phone] #
csv << row
line_item_data = empty_line_item_data.merge!(
"Created at" => Time.parse(o.created_at).to_s,
"Email" => o.contact_email,
"Name" => o.name,
"Phone" => o.phone
)
o.line_items[1..-1].each do |li|
LINE_ITEM_FIELDS.each do |field,header|
line_item_data[header] = li.try(field)
line_item_data[header] ||= "pending" if field == :fulfillment_status
end
csv << line_item_data.values_at(*headers)
end
end
def empty_line_item_data
(@empty_line_item_data ||= Hash[headers.map {|h| [h,nil]}]).dup
end
ATTRIBUTES = [
[:name],
[:contact_email, { label_name: 'Email' }],
[:financial_status],
[:paid_at, { label_name: 'Paid at' }],
[:fulfillment_status, { default: 'pending' }],
[:fulfilled_at, { label_name: 'Fulfilled at' }],
[:marketing_preference, { label_name: 'Acce pts Marketing' }],
[:currency],
[:subtotal_price, { label_name: 'Subtotal' }],
[:shipping_price, { label_name: 'Shipping' }],
[:total_tax, { label_name: 'Taxes' }],
[:total_price, { label_name: 'Total' }],
[:discount_code],
[:total_discounts, { label_name: 'Discount Amount' }],
[:shipping_title, { label_name: 'Shipping Method' }],
[:created_at, { label_name: 'Created at' }],
# line items
['line_item_quantity', { label_name: 'Lineitem quantity' }],
['line_item_name', { label_name: 'Lineitem name' }],
['line_item_price', { label_name: 'Lineitem price' }],
['line_item_compare_at_price', { label_name: 'Lineitem compare at price' }],
['line_item_sku', { label_name: 'Lineitem sku' }],
['line_item_requires_shipping', { label_name: 'Lineitem requires shipping' }],
['line_item_taxable', { label_name: 'Lineitem taxable' }],
['line_item_fulfillment_status', { label_name: "Lineitem fulfillment status" }],
# billing address
[:billing_or_customer_name, { label_name: 'Billing Name' }],
['billing_address.street', { label_name: 'Billing Street' }],
['billing_address.address1', { label_name: 'Billing Address1' }],
['billing_address.address2', { label_name: 'Billing Address2' }],
['billing_address.company', { label_name: 'Billing Company' }],
['billing_address.city', { label_name: 'Billing City' }],
['billing_address.zip', { label_name: 'Billing Zip', force_string_in_excel: true }],
['billing_address.province_code', { label_name: 'Billing Province' }],
['billing_address.country_code', { label_name: 'Billing Country' }],
['billing_address.phone', { label_name: 'Billing Phone' }],
# shipping address
['shipping_address.name', { label_name: 'Shipping Name' }],
['shipping_address.street', { label_name: 'Shipping Street' }],
['shipping_address.address1', { label_name: 'Shipping Address1' }],
['shipping_address.address2', { label_name: 'Shipping Address2' }],
['shipping_address.company', { label_name: 'Shipping Company' }],
['shipping_address.city', { label_name: 'Shipping City' }],
['shipping_address.zip', { label_name: 'Shipping Zip', force_string_in_excel: true }],
['shipping_address.province_code', { label_name: 'Shipping Province' }],
['shipping_address.country_code', { label_name: 'Shipping Country' }],
['shipping_address.phone', { label_name: 'Shipping Phone' }],
[:note, { label_name: 'Notes' }],
[:extract_note_attributes, { label_name: 'Note Attributes' }],
[:cancelled_at, { label_name: 'Cancelled at' }],
[:payment_gateway_for, { label_name: 'Payment Method' }],
[:payment_reference, { label_name: 'Payment Reference' }],
[:total_refunded, { label_name: 'Refunded Amount' }],
# lineitem - vendor
['line_items.first.vendor', { label_name: 'Vendor' }],
[:id],
[:tags],
[:risk_level_for, { label_name: 'Risk Level' }],
[:serialized_source_name, { label_name: 'Source' }],
# line item discount
['line_items.first.total_discount', { label_name: 'Lineitem discount' }],
# tax types GST/PST etc...
[:tax_line_details, { calculated_labels: :tax_line_fields }],
# Additional columns are added to the end to prevent breaking CSV readers that don't rely on column names.
[:phone]
]
LINE_ITEM_FIELDS = {
quantity: "Lineitem quantity",
name: "Lineitem name",
price: "Lineitem price",
compare_at_price: "Lineitem compare at price",
sku: "Lineitem sku",
requires_shipping: "Lineitem requires shipping",
taxable: "Lineitem taxable",
fulfillment_status: "Lineitem fulfillment status",
vendor: "Vendor",
total_discount: "Lineitem discount"
}
end
require './csv_order_exporter.rb'
CSVOrderExporter.new.perform
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment