Skip to content

Instantly share code, notes, and snippets.

@alistairjcbrown
Last active January 8, 2024 02:38
Show Gist options
  • Save alistairjcbrown/8e358345fd49042c3283b2fecaf15cc3 to your computer and use it in GitHub Desktop.
Save alistairjcbrown/8e358345fd49042c3283b2fecaf15cc3 to your computer and use it in GitHub Desktop.
shopify-routific-conversion
orders_export.csv
routific-destinations-*.csv
# Logs
logs
*.log
npm-debug.log*
yarn-debug.log*
yarn-error.log*
# Runtime data
pids
*.pid
*.seed
*.pid.lock
# Directory for instrumented libs generated by jscoverage/JSCover
lib-cov
# Coverage directory used by tools like istanbul
coverage
# nyc test coverage
.nyc_output
# Grunt intermediate storage (http://gruntjs.com/creating-plugins#storing-task-files)
.grunt
# Bower dependency directory (https://bower.io/)
bower_components
# node-waf configuration
.lock-wscript
# Compiled binary addons (https://nodejs.org/api/addons.html)
build/Release
# Dependency directories
node_modules/
jspm_packages/
# TypeScript v1 declaration files
typings/
# Optional npm cache directory
.npm
# Optional eslint cache
.eslintcache
# Optional REPL history
.node_repl_history
# Output of 'npm pack'
*.tgz
# Yarn Integrity file
.yarn-integrity
# dotenv environment variables file
.env
# next.js build output
.next
.DS_Store

shopify-routific-conversion

Converts from a Shopify orders CSV export to a CSV file which can be uploaded to Routific to provide delivery locations.

Setting up

Clone the gist and install dependencies

git clone git@gist.github.com:8e358345fd49042c3283b2fecaf15cc3.git shopify-routific-conversion
cd shopify-routific-conversion
npm install

Conversion

  1. Go to your Shopify orders - http://shopify.com/admin/orders

  2. Filter the orders as required (e.g. only "Open" orders)

  3. Export the current page as "Plain CSV file"

  4. Copy the file to the shopify-routific-conversion directory

    • Make sure it's called orders_export.csv
    cp ~/Downloads/orders_export.csv ./shopify-routific-conversion
    
  5. Run the conversion

    npm start
    

This will create a CSV file in shopify-routific-conversion with a file name in the format routific-destinations-<date>.csv

Use

When setting up a new project in Routific, use the "Upload a List of Stops" functionality and select the generated routific-destinations CSV file. The correct columns should be automatically selected based on the column titles.

const util = require("util");
const fs = require("fs");
const neatCsv = require("neat-csv");
const createCsvWriter = require("csv-writer").createObjectCsvWriter;
const readFile = util.promisify(fs.readFile);
const capitalize = (text) =>
text.charAt(0).toUpperCase() + text.slice(1).toLowerCase();
const titleize = (text) => text.split(/\s+/).map(capitalize).join(" ");
const padNumber = (value) => `0${value}`.slice(-2);
const formatDate = (date = new Date()) =>
`${date.getFullYear()}-${padNumber(date.getMonth() + 1)}-${padNumber(
date.getDate()
)}`;
(async () => {
const shopfiyOrdersCsv = await readFile("./orders_export.csv");
const shopfiyOrders = await neatCsv(shopfiyOrdersCsv);
const consolidatedOrders = shopfiyOrders.reduce((orders, order) => {
const id = order.Name;
orders[id] = (orders[id] || []).concat(order);
return orders;
}, {});
const items = {};
const orders = Object.keys(consolidatedOrders)
.sort()
.map((id) => {
const orderItems = consolidatedOrders[id];
const itemCount = orderItems.reduce(
(count, orderItem) =>
count + parseInt(orderItem["Lineitem quantity"], 10),
0
);
orderItems.forEach((orderItem) => {
const name = orderItem["Lineitem name"];
items[name] =
(items[name] || 0) + parseInt(orderItem["Lineitem quantity"], 10);
});
const mainItem = orderItems[0];
const shipping = (key) => mainItem[`Shipping ${key}`].trim();
const name = titleize(shipping("Name"));
const address = titleize(shipping("Street")).replace(" , ", ", ");
const city = titleize(shipping("City"));
const state = titleize(shipping("Province"));
const zipCode = shipping("Zip");
const phone = shipping("Phone")
.replace("'", "")
.replace(/\s+/g, "")
.replace(/^44/, "+44")
.replace(/^07/, "+447");
const email = mainItem.Email.trim();
const billingName = mainItem["Billing Name"].trim();
const isApartment =
address.toLowerCase().includes("apartment") ||
address.toLowerCase().includes("flat ") ||
address.toLowerCase().includes("building");
const isPresent = name.toLowerCase() !== billingName.toLowerCase();
if (isApartment) {
console.warn(`Check address: ${id} - ${name}, "${address}"`);
}
const notes = `(${id}) Paid £${mainItem.Total}, ${itemCount} item${
itemCount === 1 ? "" : "s"
} ordered -- ${orderItems
.map(
(orderItem) =>
`${orderItem["Lineitem quantity"]} x ${orderItem["Lineitem name"]}`
)
.join(", ")} -- Link: https://shopify.com/admin/orders/${mainItem.Id}`;
const notes2 = [
isApartment
? "WARNING: This address has been flagged as being an apartment"
: "",
isPresent
? `Note: This may be a present - the shipping name does not match billing name (${billingName})`
: "",
]
.filter((value) => !!value)
.join(" -- ");
return {
id: id.replace("#", "Order "),
name,
address,
city,
state,
zipCode,
phone,
email,
notes,
notes2,
};
});
const csvWriter = createCsvWriter({
path: `./routific-destinations-${formatDate()}.csv`,
header: [
{ id: "id", title: "ID" },
{ id: "name", title: "Name" },
{ id: "address", title: "Address" },
{ id: "city", title: "City" },
{ id: "state", title: "State" },
{ id: "zipCode", title: "Zip Code" },
{ id: "phone", title: "Phone" },
{ id: "email", title: "Email" },
{ id: "notes", title: "Notes" },
{ id: "notes2", title: "Notes 2" },
],
});
await csvWriter.writeRecords(orders);
console.log(`\nOutputted to ./routific-destinations-${formatDate()}.csv`);
console.log(` - Delivering to ${orders.length} locations`);
const largestSize = Math.max(...Object.values(items)).toString().length;
const pad = (value) =>
`${value}`.padStart(largestSize, " ").slice(-largestSize);
console.log(
` - Sold ${Object.values(items).reduce(
(count, item) => count + item,
0
)} products`
);
Object.keys(items).forEach((itemName) => {
console.log(` -- ${pad(items[itemName])} x "${itemName}"`);
});
})();
{
"name": "shopify-routific-conversion",
"version": "1.0.0",
"lockfileVersion": 1,
"requires": true,
"dependencies": {
"@hapi/address": {
"version": "2.1.4",
"resolved": "https://registry.npmjs.org/@hapi/address/-/address-2.1.4.tgz",
"integrity": "sha512-QD1PhQk+s31P1ixsX0H0Suoupp3VMXzIVMSwobR3F3MSUO2YCV0B7xqLcUw/Bh8yuvd3LhpyqLQWTNcRmp6IdQ=="
},
"@hapi/formula": {
"version": "1.2.0",
"resolved": "https://registry.npmjs.org/@hapi/formula/-/formula-1.2.0.tgz",
"integrity": "sha512-UFbtbGPjstz0eWHb+ga/GM3Z9EzqKXFWIbSOFURU0A/Gku0Bky4bCk9/h//K2Xr3IrCfjFNhMm4jyZ5dbCewGA=="
},
"@hapi/hoek": {
"version": "8.5.1",
"resolved": "https://registry.npmjs.org/@hapi/hoek/-/hoek-8.5.1.tgz",
"integrity": "sha512-yN7kbciD87WzLGc5539Tn0sApjyiGHAJgKvG9W8C7O+6c7qmoQMfVs0W4bX17eqz6C78QJqqFrtgdK5EWf6Qow=="
},
"@hapi/joi": {
"version": "16.1.8",
"resolved": "https://registry.npmjs.org/@hapi/joi/-/joi-16.1.8.tgz",
"integrity": "sha512-wAsVvTPe+FwSrsAurNt5vkg3zo+TblvC5Bb1zMVK6SJzZqw9UrJnexxR+76cpePmtUZKHAPxcQ2Bf7oVHyahhg==",
"requires": {
"@hapi/address": "^2.1.2",
"@hapi/formula": "^1.2.0",
"@hapi/hoek": "^8.2.4",
"@hapi/pinpoint": "^1.0.2",
"@hapi/topo": "^3.1.3"
}
},
"@hapi/pinpoint": {
"version": "1.0.2",
"resolved": "https://registry.npmjs.org/@hapi/pinpoint/-/pinpoint-1.0.2.tgz",
"integrity": "sha512-dtXC/WkZBfC5vxscazuiJ6iq4j9oNx1SHknmIr8hofarpKUZKmlUVYVIhNVzIEgK5Wrc4GMHL5lZtt1uS2flmQ=="
},
"@hapi/topo": {
"version": "3.1.6",
"resolved": "https://registry.npmjs.org/@hapi/topo/-/topo-3.1.6.tgz",
"integrity": "sha512-tAag0jEcjwH+P2quUfipd7liWCNX2F8NvYjQp2wtInsZxnMlypdw0FtAOLxtvvkO+GSRRbmNi8m/5y42PQJYCQ==",
"requires": {
"@hapi/hoek": "^8.3.0"
}
},
"buffer-alloc": {
"version": "1.2.0",
"resolved": "https://registry.npmjs.org/buffer-alloc/-/buffer-alloc-1.2.0.tgz",
"integrity": "sha512-CFsHQgjtW1UChdXgbyJGtnm+O/uLQeZdtbDo8mfUgYXCHSM1wgrVxXm6bSyrUuErEb+4sYVGCzASBRot7zyrow==",
"requires": {
"buffer-alloc-unsafe": "^1.1.0",
"buffer-fill": "^1.0.0"
}
},
"buffer-alloc-unsafe": {
"version": "1.1.0",
"resolved": "https://registry.npmjs.org/buffer-alloc-unsafe/-/buffer-alloc-unsafe-1.1.0.tgz",
"integrity": "sha512-TEM2iMIEQdJ2yjPJoSIsldnleVaAk1oW3DBVUykyOLsEsFmEc9kn+SFFPz+gl54KQNxlDnAwCXosOS9Okx2xAg=="
},
"buffer-fill": {
"version": "1.0.0",
"resolved": "https://registry.npmjs.org/buffer-fill/-/buffer-fill-1.0.0.tgz",
"integrity": "sha1-+PeLdniYiO858gXNY39o5wISKyw="
},
"buffer-from": {
"version": "1.1.1",
"resolved": "https://registry.npmjs.org/buffer-from/-/buffer-from-1.1.1.tgz",
"integrity": "sha512-MQcXEUbCKtEo7bhqEs6560Hyd4XaovZlO/k9V3hjVUF/zwW7KBVdSK4gIt/bzwS9MbR5qob+F5jusZsb0YQK2A=="
},
"core-util-is": {
"version": "1.0.2",
"resolved": "https://registry.npmjs.org/core-util-is/-/core-util-is-1.0.2.tgz",
"integrity": "sha1-tf1UIgqivFq1eqtxQMlAdUUDwac="
},
"csv-parser": {
"version": "2.3.2",
"resolved": "https://registry.npmjs.org/csv-parser/-/csv-parser-2.3.2.tgz",
"integrity": "sha512-ggurTYuhhoUJyrPXTAkiO1x6QXkxAoimV+YSz2eSAXF+jQ/Xve/030T34tVStKFmX56pPtY5PiZl3bR4HkZK+Q==",
"requires": {
"@hapi/joi": "^16.1.4",
"buffer-alloc": "^1.1.0",
"buffer-from": "^1.0.0",
"generate-object-property": "^1.0.0",
"minimist": "^1.2.0",
"ndjson": "^1.4.0"
}
},
"csv-writer": {
"version": "1.6.0",
"resolved": "https://registry.npmjs.org/csv-writer/-/csv-writer-1.6.0.tgz",
"integrity": "sha512-NOx7YDFWEsM/fTRAJjRpPp8t+MKRVvniAg9wQlUKx20MFrPs73WLJhFf5iteqrxNYnsy924K3Iroh3yNHeYd2g=="
},
"end-of-stream": {
"version": "1.4.4",
"resolved": "https://registry.npmjs.org/end-of-stream/-/end-of-stream-1.4.4.tgz",
"integrity": "sha512-+uw1inIHVPQoaVuHzRyXd21icM+cnt4CzD5rW+NC1wjOUSTOs+Te7FOv7AhN7vS9x/oIyhLP5PR1H+phQAHu5Q==",
"requires": {
"once": "^1.4.0"
}
},
"generate-object-property": {
"version": "1.2.0",
"resolved": "https://registry.npmjs.org/generate-object-property/-/generate-object-property-1.2.0.tgz",
"integrity": "sha1-nA4cQDCM6AT0eDYYuTf6iPmdUNA=",
"requires": {
"is-property": "^1.0.0"
}
},
"get-stream": {
"version": "5.1.0",
"resolved": "https://registry.npmjs.org/get-stream/-/get-stream-5.1.0.tgz",
"integrity": "sha512-EXr1FOzrzTfGeL0gQdeFEvOMm2mzMOglyiOXSTpPC+iAjAKftbr3jpCMWynogwYnM+eSj9sHGc6wjIcDvYiygw==",
"requires": {
"pump": "^3.0.0"
}
},
"inherits": {
"version": "2.0.4",
"resolved": "https://registry.npmjs.org/inherits/-/inherits-2.0.4.tgz",
"integrity": "sha512-k/vGaX4/Yla3WzyMCvTQOXYeIHvqOKtnqBduzTHpzpQZzAskKMhZ2K+EnBiSM9zGSoIFeMpXKxa4dYeZIQqewQ=="
},
"is-property": {
"version": "1.0.2",
"resolved": "https://registry.npmjs.org/is-property/-/is-property-1.0.2.tgz",
"integrity": "sha1-V/4cTkhHTt1lsJkR8msc1Ald2oQ="
},
"isarray": {
"version": "1.0.0",
"resolved": "https://registry.npmjs.org/isarray/-/isarray-1.0.0.tgz",
"integrity": "sha1-u5NdSFgsuhaMBoNJV6VKPgcSTxE="
},
"json-stringify-safe": {
"version": "5.0.1",
"resolved": "https://registry.npmjs.org/json-stringify-safe/-/json-stringify-safe-5.0.1.tgz",
"integrity": "sha1-Epai1Y/UXxmg9s4B1lcB4sc1tus="
},
"minimist": {
"version": "1.2.5",
"resolved": "https://registry.npmjs.org/minimist/-/minimist-1.2.5.tgz",
"integrity": "sha512-FM9nNUYrRBAELZQT3xeZQ7fmMOBg6nWNmJKTcgsJeaLstP/UODVpGsr5OhXhhXg6f+qtJ8uiZ+PUxkDWcgIXLw=="
},
"ndjson": {
"version": "1.5.0",
"resolved": "https://registry.npmjs.org/ndjson/-/ndjson-1.5.0.tgz",
"integrity": "sha1-rmA7NrE0vOw0e0UkIrC/mNWDLsg=",
"requires": {
"json-stringify-safe": "^5.0.1",
"minimist": "^1.2.0",
"split2": "^2.1.0",
"through2": "^2.0.3"
}
},
"neat-csv": {
"version": "5.2.0",
"resolved": "https://registry.npmjs.org/neat-csv/-/neat-csv-5.2.0.tgz",
"integrity": "sha512-JMbn4RPKo1heWhDihK9kbffxkDdd2uFSk30t3YOz1u3gsbIGhGUNlmGGa5H9yySLie3qU4lsLwrxQyirExD71A==",
"requires": {
"csv-parser": "^2.3.2",
"get-stream": "^5.1.0",
"to-readable-stream": "^2.1.0"
}
},
"once": {
"version": "1.4.0",
"resolved": "https://registry.npmjs.org/once/-/once-1.4.0.tgz",
"integrity": "sha1-WDsap3WWHUsROsF9nFC6753Xa9E=",
"requires": {
"wrappy": "1"
}
},
"process-nextick-args": {
"version": "2.0.1",
"resolved": "https://registry.npmjs.org/process-nextick-args/-/process-nextick-args-2.0.1.tgz",
"integrity": "sha512-3ouUOpQhtgrbOa17J7+uxOTpITYWaGP7/AhoR3+A+/1e9skrzelGi/dXzEYyvbxubEF6Wn2ypscTKiKJFFn1ag=="
},
"pump": {
"version": "3.0.0",
"resolved": "https://registry.npmjs.org/pump/-/pump-3.0.0.tgz",
"integrity": "sha512-LwZy+p3SFs1Pytd/jYct4wpv49HiYCqd9Rlc5ZVdk0V+8Yzv6jR5Blk3TRmPL1ft69TxP0IMZGJ+WPFU2BFhww==",
"requires": {
"end-of-stream": "^1.1.0",
"once": "^1.3.1"
}
},
"readable-stream": {
"version": "2.3.7",
"resolved": "https://registry.npmjs.org/readable-stream/-/readable-stream-2.3.7.tgz",
"integrity": "sha512-Ebho8K4jIbHAxnuxi7o42OrZgF/ZTNcsZj6nRKyUmkhLFq8CHItp/fy6hQZuZmP/n3yZ9VBUbp4zz/mX8hmYPw==",
"requires": {
"core-util-is": "~1.0.0",
"inherits": "~2.0.3",
"isarray": "~1.0.0",
"process-nextick-args": "~2.0.0",
"safe-buffer": "~5.1.1",
"string_decoder": "~1.1.1",
"util-deprecate": "~1.0.1"
}
},
"safe-buffer": {
"version": "5.1.2",
"resolved": "https://registry.npmjs.org/safe-buffer/-/safe-buffer-5.1.2.tgz",
"integrity": "sha512-Gd2UZBJDkXlY7GbJxfsE8/nvKkUEU1G38c1siN6QP6a9PT9MmHB8GnpscSmMJSoF8LOIrt8ud/wPtojys4G6+g=="
},
"split2": {
"version": "2.2.0",
"resolved": "https://registry.npmjs.org/split2/-/split2-2.2.0.tgz",
"integrity": "sha512-RAb22TG39LhI31MbreBgIuKiIKhVsawfTgEGqKHTK87aG+ul/PB8Sqoi3I7kVdRWiCfrKxK3uo4/YUkpNvhPbw==",
"requires": {
"through2": "^2.0.2"
}
},
"string_decoder": {
"version": "1.1.1",
"resolved": "https://registry.npmjs.org/string_decoder/-/string_decoder-1.1.1.tgz",
"integrity": "sha512-n/ShnvDi6FHbbVfviro+WojiFzv+s8MPMHBczVePfUpDJLwoLT0ht1l4YwBCbi8pJAveEEdnkHyPyTP/mzRfwg==",
"requires": {
"safe-buffer": "~5.1.0"
}
},
"through2": {
"version": "2.0.5",
"resolved": "https://registry.npmjs.org/through2/-/through2-2.0.5.tgz",
"integrity": "sha512-/mrRod8xqpA+IHSLyGCQ2s8SPHiCDEeQJSep1jqLYeEUClOFG2Qsh+4FU6G9VeqpZnGW/Su8LQGc4YKni5rYSQ==",
"requires": {
"readable-stream": "~2.3.6",
"xtend": "~4.0.1"
}
},
"to-readable-stream": {
"version": "2.1.0",
"resolved": "https://registry.npmjs.org/to-readable-stream/-/to-readable-stream-2.1.0.tgz",
"integrity": "sha512-o3Qa6DGg1CEXshSdvWNX2sN4QHqg03SPq7U6jPXRahlQdl5dK8oXjkU/2/sGrnOZKeGV1zLSO8qPwyKklPPE7w=="
},
"util-deprecate": {
"version": "1.0.2",
"resolved": "https://registry.npmjs.org/util-deprecate/-/util-deprecate-1.0.2.tgz",
"integrity": "sha1-RQ1Nyfpw3nMnYvvS1KKJgUGaDM8="
},
"wrappy": {
"version": "1.0.2",
"resolved": "https://registry.npmjs.org/wrappy/-/wrappy-1.0.2.tgz",
"integrity": "sha1-tSQ9jz7BqjXxNkYFvA0QNuMKtp8="
},
"xtend": {
"version": "4.0.2",
"resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
"integrity": "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ=="
}
}
}
{
"name": "shopify-routific-conversion",
"version": "1.1.0",
"description": "Script to generate Routific location CSV file from Shopify orders export",
"main": "index.js",
"scripts": {
"start": "node index.js"
},
"keywords": [],
"author": "Alistair Brown <github@alistairjcbrown.com>",
"license": "MIT",
"dependencies": {
"csv-writer": "^1.6.0",
"neat-csv": "^5.2.0"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment