Use ONLYOFFICE macro to track orders on Shopify
In this day and age, E-commerce has become a valuable tool to break down geographical barriers and facilitate the constant stream of income. Therefore managing and tracking order information is a vital part of a successful business strategy. In this article, we will build a macro that retrieves order data from Shopify and inserts it into a spreadsheet.
Note! The macro is only operational within desktop versions of ONLYOFFICE editors.
About Shopify
Shopify is a leading e-commerce platform that allows individuals and businesses to create and manage their online stores. It provides a comprehensive set of tools and features to help merchants sell products, manage inventory, process orders, and customize their storefronts.
The Shopify API serves as a powerful interface that allows developers to interact with and extend the functionality of Shopify. It provides a wide range of methods and endpoints that enable developers to seamlessly integrate their own applications, systems, or services with Shopify stores.
Building the macro
First we declare the following variables:
const TOKEN = 'YOUR_API_TOKEN'
const SHOP_ID = 'YOUR_SHOP_ID'
const URL = `https://${ SHOP_ID }.myshopify.com/admin/api/2022-10/orders.json`
const LABELS = [
'order id',
'total price',
'tax',
'shipping address'
]
The TOKEN and the SHOP_ID variables contain your actual Shopify API token and shop ID respectively. The URL variable holds the complete URL for the API request to retrieve orders from the Shopify store. The LABEL variable is an array containing labels for the table columns. It specifies the order in which data will be displayed in the spreadsheet. Each label corresponds to a column in the table.
Then we we incorporate the makeRequest function to initiate a GET request to the designated Shopify API endpoint:
function makeRequest() {
$.ajax({
method: 'GET',
headers: {
// 'Access-Control-Allow-Origin': '*',
'Content-Type' : 'application/json',
'X-Shopify-Access-Token': TOKEN
},
url: URL,
dataType: 'json',
}).done(successFunction)
}
In this $.ajax request, we set the necessary headers, including the content type and the Shopify API token. We also specify the data type and the URL as the Shopify API endpoint for fetching orders.
After that we handle the response with the successFuntion:
function successFunction(data) {
if (data.orders === undefined) return
let table = LABELS.map(label => [label])
const oWorksheet = Api.GetActiveSheet()
const oRange = oWorksheet.GetRange(`A1:D${data.orders.length + 1}`)
data.orders.forEach(order => {
table[0].push(order.id.toString())
table[1].push(order.current_total_price)
table[2].push(order.current_total_tax)
// if missing field
if (order.shipping_address === undefined) {
table[3].push(order.billing_address.address1.toString())
} else {
table[3].push(order.shipping_address.address1.toString())
}
})
oRange.SetValue(table)
It takes data as a parameter, which represents the response containing the order information. The first line of the function checks if the data.orders property is undefined. If it is, it means there are no orders in the response, so the function returns early. Then we use the LABELS array and the map method to create a new array with each label as a separate row in the table. Each label is encapsulated in an array to represent a single cell in the table:
function successFunction(data) {
if (data.orders === undefined) return
let table = LABELS.map(label => [label])
})
After that we retrieve the active sheet in the ONLYOFFICE document and define a range where the order data will be inserted. The range is set to span from cells A1 to D, followed by the number of orders plus one. The data.orders.length + 1 is used to account for the labels row in the table:
const oWorksheet = Api.GetActiveSheet()
const oRange = oWorksheet.GetRange(`A1:D${data.orders.length + 1}`)
In this block, a loop iterates over each order object in the data.orders array. For each order, the relevant information is pushed into the corresponding column of the table array:
data.orders.forEach(order => {
table[0].push(order.id.toString())
table[1].push(order.current_total_price)
table[2].push(order.current_total_tax)
// if missing field
if (order.shipping_address === undefined) {
table[3].push(order.billing_address.address1.toString())
} else {
table[3].push(order.shipping_address.address1.toString())
}
})
Let’s take a closer look:
- order.id.toString() retrieves the order ID as a string and pushes it to the first column (table[0]).
- order.current_total_price and order.current_total_tax are pushed to the second and third columns (table[1] and table[2]) respectively.
- The shipping address (order.shipping_address.address1) is checked. If it exists, it is pushed to the fourth column (table[3]). Otherwise, the billing address (order.billing_address.address1) is used.
Finally, the table array is inserted into the specified range in the ONLYOFFICE document. The SetValue method sets the values of the cells in the range, populating the table with the order data:
oRange.SetValue(table)
reload()
The entire macro code is the following:
(function()
{
const TOKEN = 'YOUR_API_TOKEN'
const SHOP_ID = 'YOUR_SHOP_ID'
const URL = `https://${ SHOP_ID }.myshopify.com/admin/api/2022-10/orders.json`
const LABELS = [
'order id',
'total price',
'tax',
'shipping address'
]
makeRequest()
function makeRequest() {
$.ajax({
method: 'GET',
headers: {
// 'Access-Control-Allow-Origin': '*',
'Content-Type' : 'application/json',
'X-Shopify-Access-Token': TOKEN
},
url: URL,
dataType: 'json',
}).done(successFunction)
}
function successFunction(data) {
if (data.orders === undefined) return
let table = LABELS.map(label => [label])
const oWorksheet = Api.GetActiveSheet()
const oRange = oWorksheet.GetRange(`A1:D${data.orders.length + 1}`)
data.orders.forEach(order => {
table[0].push(order.id.toString())
table[1].push(order.current_total_price)
table[2].push(order.current_total_tax)
// if missing field
if (order.shipping_address === undefined) {
table[3].push(order.billing_address.address1.toString())
} else {
table[3].push(order.shipping_address.address1.toString())
}
})
oRange.SetValue(table)
reload()
}
})();
We hope you will find this macro a useful tool for accessing and analyzing Shopify order information.
However, please note that this macro is just one of many ways you can use our API methods. ONLYOFFICE macros are extremely versatile, and we welcome you to experiment and create your own scripts. Feel free to share your ideas or your macros with us. We are always open to discussion and cooperation. Best of luck in your exploratory endeavors!
Useful links
