How to get all your stock items from Linnworks to Google Spreadsheets via API?
Tags: javascript, googlecloud, googlespreadsheet, googleappsscript
Published: 2021-02-05 23:24:15

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](https://help.linnworks.com/support/solutions/articles/7000043829-authorization-api-token). 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](https://apps.linnworks.net/Api/Method/Auth-AuthorizeByApplication) 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
```js
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()`](https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app) 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:
```js
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
```js
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
```js
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](https://github.com/adeelulrahman/writeLinnworksDataToGSpreadsheet).

Thanks for reading.