DEV Community

loading...
Cover image for How to get all your stock items from Linnworks to Google Spreadsheets via API?

How to get all your stock items from Linnworks to Google Spreadsheets via API?

adeelrahman profile image Adeel ・4 min read

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:

  1. Create a developer account in LW, get Application Id and Application Secret.
  2. 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:

  1. API token which returned as Token
  2. 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

  1. Create a new sheet - you can use a neat little trick I use to create new sheet files by writing sheet.new in your browser.
  2. 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

  1. Make the first API call and get the data 🤓
  2. do - while loop to get all stock items because API returns only 200 objects in 1 API call 😟
  3. Parse the response to get the data fields we are interested in 🦾
  4. 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())
}
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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")
}
Enter fullscreen mode Exit fullscreen mode

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)
}
Enter fullscreen mode Exit fullscreen mode

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.

Discussion (0)

pic
Editor guide