A few days ago, I wrote a simple script that connects Linnworks (LW) and Google Spreadsheet. It's using LW's API to get all the stock items from LW to Google Spreadsheets. The script is written in Google Apps Script, which is a language developed by Google to automate tasks within Google Workspace and connect Google products to external APIs. The language is essentially modern JavaScript but with the benefit of having access to major Google APIs (Spreadsheet, Documents, etc.) at its service. What I love the most about Google Apps Script is that it's embedded in the Spreadsheet, hosted for free and can be put on a trigger to run on a schedule.
Things needed for this project
The only thing you need from LW is the API token. You can generate an API token using the instructions given at their help page. In summary, to get an API token, you have to follow 2 steps:
- Create a developer account in LW, get
Application IdandApplication Secret. - Authorize those using an API endpoint in order to get an API token.
After authorizing your LW application, you must have received a response object that contains two important keys:
- API token which returned as
Token -
Server, which is important to create a URL string. It should look something like this:https://eu-ext.linnworks.net
Copy these two pieces of information somewhere, we will need them in the next steps.
Create a Google Spreadsheet file
- Create a new sheet - you can use a neat little trick I use to create new sheet files by writing
sheet.newin your browser. - In your spreadsheet, navigate to Tools > Script Editor.
This is an editor where we will be writing all our code.
Breaking the project into steps
- Make the first API call and get the data 🤓
-
do - whileloop to get all stock items because API returns only 200 objects in 1 API call 😟 - Parse the response to get the data fields we are interested in 🦾
- Paste the parsed objects in the sheet 📝
1. Making the first API call
function getLWStockItems(){
const URL = 'PASTE_SERVER_HERE/api/Stock/GetStockItemsFull'
const payload = {
'keyword': '',
'loadCompositeParents': 'True',
'loadVariationParents': 'False',
'entriesPerPage': '200',
'pageNumber': 1,
'dataRequirements': '[0,1,2,3,7,8]',
'searchTypes': '[0,1,2]'
}
var response = UrlFetchApp.fetch(URL, {
method: 'post',
headers: {
'Authorization': 'YOUR_LW_TOKEN'
},
'contentType': 'application/json',
'payload': JSON.stringify(payload),
muteHttpExceptions: true
})
console.log(response.getResponseCode())
console.log(response.getContentText())
}
The above function should return 200 status code and a huge array of 200 stock items. payload object is what API accepts in the body of a request. UrlFetchAPP.fetch() is how you make an HTTP request in Google Apps Script.
2. do - while loop
One of the parameters in the payload object is the pageNumber. We can use this page number field to increment it till we get all the pages and all the stock item objects. The moment API returns 400, we break the loop. The code looks something like this:
function getLWStockItems() {
let allProducts = []
const URL = 'PASTE_SERVER_HERE/api/Stock/GetStockItemsFull'
let startingPageNo = 1
do {
const payload = {
'keyword': '',
'loadCompositeParents': 'True',
'loadVariationParents': 'False',
'entriesPerPage': '200',
'pageNumber': startingPageNo,
'dataRequirements': '[0,1,2,3,7,8]',
'searchTypes': '[0,1,2]'
}
console.log(payload.pageNumber)
var response = UrlFetchApp.fetch(URL, {
method: 'post',
headers: {
'Authorization': 'YOUR_LW_TOKEN'
},
'contentType': 'application/json',
'payload': JSON.stringify(payload),
muteHttpExceptions: true
})
console.log(response.getResponseCode())
if (response.getResponseCode() === 200){
const jsonRes = JSON.parse(response.getContentText())
allProducts = allProducts.concat(jsonRes)
startingPageNo++
}
} while (response.getResponseCode() !== 400)
console.log('Total stock objects: ', allProducts.length)
return allProducts
}
The above function makes API calls 1 by 1 while incrementing the startingPageNo variable. If API returns 200 status code then allProducts array is concatenated. As soon as API returns 400, while loop breaks and we return allProducts array. At this point, we have all the stock items from LW to parse and paste in the sheet.
3. Parse the response
function parseResponse(){
const stocksData = getLWStockItems()
const stockItemsToWrite = []
try{
for (let item of stocksData){
const suppliersData = item.Suppliers
const itemProperties = item.ItemExtendedProperties
const images = item.Images
const stockLev = item.StockLevels
let imageLink = ""
let itemsObj = {}
let supplierObj = {}
for (let supplier of suppliersData){
if(supplier.IsDefault){
supplierObj["supplierName"] = supplier.Supplier
supplierObj["supplierCode"] = supplier.Code
supplierObj["supplierBarcode"] = supplier.SupplierBarcode
supplierObj["supplierPP"] = supplier.PurchasePrice
}
}
for (let property of itemProperties){
if(property.ProperyName === "export-two-tone"
|| property.ProperyName === "export-postage"
|| property.ProperyName === "HSTariffCode"
|| property.ProperyName === "CountryOfOrigin"
|| property.ProperyName === "ShippingDescription"
|| property.ProperyName === "CountryOfOriginISO"
){
itemsObj[property.ProperyName] = property.PropertyValue
}
}
for (let image of images){
if(image.IsMain){
imageLink = image.FullSource
}
}
for (let stock of stockLev){
if(stock.Location.LocationName === "Default"){
var availableStock = stock.Available
}
}
stockItemsToWrite.push([
item.ItemNumber,
item.ItemTitle,
item.BarcodeNumber,
item.CategoryName,
item.PurchasePrice,
item.RetailPrice,
supplierObj["supplierName"],
supplierObj["supplierCode"],
supplierObj["supplierBarcode"],
supplierObj["supplierPP"],
itemsObj['CountryOfOrigin'],
itemsObj['CountryOfOriginISO'],
itemsObj['export-postage'],
itemsObj['export-two-tone'],
itemsObj['HSTariffCode'],
itemsObj['ShippingDescription'],
item.PackageGroupName,
imageLink,
availableStock
])
itemsObj = {}
supplierObj = {}
imageLink = ""
}
}catch(erorr){
console.log("some error while parsing: ", erorr)
}
// writing to the sheet
writeToSheet(stockItemsToWrite, "Sheet1")
}
In this function, I am interested in getting the fields related to default supplier, extended item properties, images and stock levels. After parsing the fields, I am pushing them into stockItemsToWrite which is a 2D array. A 2D array is created because Spreadsheet API accepts data to be pasted in 2D array format.
Paste the data in Sheet
function writeToSheet(data, sheetName) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
try {
// removes the previously exisiting data
sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clear({ formatOnly: true, contentsOnly: true })
} catch (error) {
console.log(error)
}
sheet.getRange(2, 1, data.length, data[0].length).setValues(data)
}
The above function pastes the 2D array into sheet of your choice.
Full script
You can get the full script at my github.
Thanks for reading.
Top comments (0)