Categories

Recent Posts

How to Use Google Sheets as an Amazon FBA Inventory tracker?

Lets create a simple script that will update the FBA Inventory for all the products in a Google Sheet. For the exercise, we will just get the data and update it in the Google Sheet. The script will call the Amazon MWS API every 30 minutes.

Track Amazon FBA Inventory with Google Sheets

Step 1: Add a configuration screen to hold the Amazon MWS Credential

We will hold the Amazon MWS Credential and save it in the User properties class. We will need to enter the information only once. On the Google Sheet loading the onOpen() method will be executed, there will be a menu “Amazon FBA Inventory Tracker” and clicking it sub-menu “Amazon MWS Credential” will open the popup to save the Amazon MWS Credentials.

Code.gs

var amazonMWSConfigProperties = PropertiesService.getUserProperties();

function saveCredentials(formData) {
  var sheet = SpreadsheetApp.getActiveSheet();
  amazonMWSConfigProperties.setProperty('amazonConfig', JSON.stringify(formData));
}

function loadCredentials() {
  var config = amazonMWSConfigProperties.getProperty('amazonConfig');
  return config;
}

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  ss.toast('Loading... Please wait.'); 
  
  var menu = [  
    {name: "Amazon MWS Credential",     functionName: "showDialog"},
    null,
    {name: "Start Tracking FBA Inventory", functionName: "startTracking"},
    {name: "Stop Tracking FBA Inventory",  functionName: "stopTracking"}
  ];  
  ss.addMenu("Amazon FBA Inventory Tracker", menu);
  
}

function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('Page')
      .setWidth(400)
      .setHeight(400);
  SpreadsheetApp.getUi() // Or DocumentApp or SlidesApp or FormApp.
      .showModalDialog(html, 'Amazon MWS Credential');
}

function startTracking() {
  
  try {
    stopTracking(true);
    var ss = SpreadsheetApp.getActiveSpreadsheet();    
    fetchFBAInventory();
    
    ss.toast('The FBA Inventory tracker is now active. You can now close this sheet.', '', -1); 
    
    ScriptApp.newTrigger('fetchFBAInventory')
    .timeBased()
    .everyMinutes(30)
    .create();       
    
    return;
  } catch (e) {
    Browser.msgBox(e.toString());
  }
}

function stopTracking(e) {
  
  var triggers = ScriptApp.getProjectTriggers();
  
  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  
  if (!e) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();    
    ss.toast('The FBA Inventory tracker is no longer active. You can restart the tracker anytime later from the same menu.', '', -1); 
  }  
}

function doGet(e) {
  var queryString = e.queryString;
}

Page.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="//www.google.com/jsapi"></script><script>window.parent.maeExportApis_();</script>
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <script>
      
      function preventFormSubmit() {
        var forms = document.querySelectorAll('form');
        for (var i = 0; i < forms.length; i++) {
          forms[i].addEventListener('submit', function(event) {
            event.preventDefault();
          });
        }
      }
      window.addEventListener('load', preventFormSubmit);

      function handleFormSubmit(formObject) {
        google.script.run.withSuccessHandler(closeModal).saveCredentials(formObject);
      }
      
      function closeModal() {
        google.script.host.close();
      }
      
      function loadMWSConfig(config){
        var configData = JSON.parse(config);
        $("#sellerID").val(configData.sellerID);
        $("#accessKey").val(configData.accessKey);
        $("#secretKey").val(configData.secretKey);
        $("#authToken").val(configData.authToken);
        $("#defaultMarket").val(configData.defaultMarket);
      }
      
    google.script.run.withSuccessHandler(loadMWSConfig).loadCredentials();
    </script>
  </head>
  <body onload="loadMWSConfig()">
    <form id="credentials" onsubmit="handleFormSubmit(this)">
      <div class="form-group">
        <label for="sellerID">Seller ID</label>
        <input type="text" name="sellerID" id="sellerID" value="" style="width: 100%;">
      </div><br>
      <div class="form-group">
        <label for="authToken">Access Key</label>
        <input type="text" name="accessKey" id="accessKey" value="" style="width: 100%;">
      </div><br>
      <div class="form-group">
        <label for="authToken">Secret Key</label>
        <input type="text" name="secretKey" id="secretKey" value="" style="width: 100%;">
      </div><br>
      <div class="form-group">
        <label for="authToken">MWS Auth Token</label>
        <input type="text" name="authToken" id="authToken" value="" style="width: 100%;">
      </div><br>
      <div class="form-group">
        <label for="defaultMarket">Default Marketplace</label>
        <select name="defaultMarket" id="defaultMarket" style="width: 100%;">
          <option value="ATVPDKIKX0DER">United States</option>
          <option value="A39IBJ37TRP1C6">Australia</option>
          <option value="A2Q3Y263D00KWC">Brazil</option>
          <option value="A2EUQ1WTGCTBG2">Canada</option>
          <option value="A13V1IB3VIYZZH">France</option>
          <option value="A1PA6795UKMFR9">Germany</option>
          <option value="A21TJRUUN4KGV">India</option>
          <option value="APJ6JRA9NG5V4">Italy</option>
          <option value="A1VC38T7YXB528">Japan</option>
          <option value="A1AM78C64UM0Y8">Mexico</option>
          <option value="A19VAU5U5O7RUS">Singapore</option>
          <option value="A1RKKUPIHCS9HS">Spain</option>
          <option value="A33AVAJ2PDY3EV">Turkey</option>
          <option value="A2VIGQ35RCS4UG">United Arab Emirates</option>
          <option value="A1F83G8C2ARO7P">United Kingdom</option>
        </select>
      </div><br>
      <button class="share">Save</button>
      <input type="button" value="Close" onclick="closeModal()">
    </form>
</body>
</html>

Step 2: Create a trigger to call the Amazon MWS API every half hour

The “Start Tracking FBA Inventory” menu will programmatically create the trigger for the script to run every half hour. See the method “startTracking()” and “stopTracking()” in the above code snipped in Code.gs file.

We can view the trigger for the script and its detail from
“Tools -> Script Editor -> Edit -> Current project Trigger”

Step 3: Calling the Amazon MWS API to fetch the Inventory Report

We will be using the “GET_FBA_MYI_UNSUPPRESSED_INVENTORY_DATA” report type. Since, this is a report and data is not available immediately so we will make the request for new report using “RequestReport” API and save the ReportID from the response and store it in the Script Properties.
Flowchart:

For this exercise, we are directly saving all the data from the Report without any manipulation or analytics

Product.gs

var amazonMWSConfigProperties1 = PropertiesService.getScriptProperties();

function makeNewRequest() {
  var config = amazonMWSConfigProperties.getProperty('amazonConfig');
  var configData = JSON.parse(config);
  var sellerID=configData.sellerID;
  var accessKey=configData.accessKey;
  var secretKey=configData.secretKey;
  var authToken=configData.authToken;
  var defaultMarket=configData.defaultMarket;
  
  var url = 'https://mws.amazonservices.in/?';
  var today = new Date();
  var unsignedURL = 
  'POST\nmws.amazonservices.in\n/\n'+
  'AWSAccessKeyId=' +accessKey+
  '&Action=RequestReport'+
  '&MWSAuthToken=' +authToken+
  '&Merchant='+sellerID+
  '&ReportType=_GET_FBA_MYI_UNSUPPRESSED_INVENTORY_DATA_'+
  '&SignatureMethod=HmacSHA256'+
  '&SignatureVersion=2'+
  '&Timestamp='+encodeURIComponent(Utilities.formatDate(today, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")) + 
  '&Version=2009-01-01';
  
  var SignedRequest = calculatedSignature(unsignedURL, secretKey);
  var Encoded = Utilities.base64Encode(SignedRequest);
    
  var param = 
  'AWSAccessKeyId=' +accessKey+
  '&Action=RequestReport'+
  '&Merchant='+sellerID+
  '&MWSAuthToken=' +authToken+
  '&SignatureVersion=2'+
  '&Timestamp='+encodeURIComponent(Utilities.formatDate(today, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")) + 
  '&Version=2009-01-01'+
  '&Signature='+encodeURIComponent(SignedRequest) + 
  '&SignatureMethod=HmacSHA256'+
  '&ReportType=_GET_FBA_MYI_UNSUPPRESSED_INVENTORY_DATA_';
  
   var options = {
     "method" : "POST",
     "muteHttpExceptions" : true
   };
  var result = UrlFetchApp.fetch(url+param,options);
  if (result.getResponseCode() == 200) {
     parseXml(result);
  }
}

function fetchFBAInventory() {
  var config = amazonMWSConfigProperties.getProperty('amazonConfig');
  var configData = JSON.parse(config);
  var sellerID=configData.sellerID;
  var accessKey=configData.accessKey;
  var secretKey=configData.secretKey;
  var authToken=configData.authToken;
  var defaultMarket=configData.defaultMarket;
  
  var url = 'https://mws.amazonservices.in/?';
  var today = new Date();
  
  if(null != amazonMWSConfigProperties1.getProperty('_GET_FBA_MYI_UNSUPPRESSED_INVENTORY_DATA_')){
    getReportIdToDownload(amazonMWSConfigProperties1.getProperty('_GET_FBA_MYI_UNSUPPRESSED_INVENTORY_DATA_'));
  }
  makeNewRequest();
}

function getReportIdToDownload(requestId) {
  var config = amazonMWSConfigProperties.getProperty('amazonConfig');
  var configData = JSON.parse(config);
  var sellerID=configData.sellerID;
  var accessKey=configData.accessKey;
  var secretKey=configData.secretKey;
  var authToken=configData.authToken;
  var defaultMarket=configData.defaultMarket;
  
  var url = 'https://mws.amazonservices.in/?';
  var today = new Date();
  var unsignedURL = 
  'POST\nmws.amazonservices.in\n/\n'+
  'AWSAccessKeyId=' +accessKey+
  '&Action=GetReportList'+
  '&MWSAuthToken=' +authToken+
  '&Merchant='+sellerID+
  '&ReportRequestIdList.Id.1='+requestId +
  '&SignatureMethod=HmacSHA256'+
  '&SignatureVersion=2'+
  '&Timestamp='+encodeURIComponent(Utilities.formatDate(today, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")) + 
  '&Version=2009-01-01';
  
  var SignedRequest = calculatedSignature(unsignedURL, secretKey);
 var Encoded = Utilities.base64Encode(SignedRequest);
  
var param = 
  'AWSAccessKeyId=' +accessKey+
  '&Action=GetReportList'+
  '&Merchant='+sellerID+
  '&MWSAuthToken=' +authToken+
  '&SignatureVersion=2'+
  '&Timestamp='+encodeURIComponent(Utilities.formatDate(today, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")) + 
  '&Version=2009-01-01'+
  '&Signature='+encodeURIComponent(SignedRequest) + 
  '&SignatureMethod=HmacSHA256'+
  '&ReportRequestIdList.Id.1='+requestId;
  
   var options = {
     "method" : "POST",
     "muteHttpExceptions" : true
   };

  Logger.log(url+param);
  var result = UrlFetchApp.fetch(url+param,options);
  Logger.log(result);
   if (result.getResponseCode() == 200) {
     var document = XmlService.parse(result);
     var root = document.getRootElement();
     var namespace = document.getRootElement().getNamespace();
  
     var reportId = root.getChild("GetReportListResult", namespace).getChild("ReportInfo", namespace).getChild("ReportId", namespace).getValue();
     downloadReport(reportId) ;
   }
}

function downloadReport(reportId) {
  var config = amazonMWSConfigProperties.getProperty('amazonConfig');
  var configData = JSON.parse(config);
  var sellerID=configData.sellerID;
  var accessKey=configData.accessKey;
  var secretKey=configData.secretKey;
  var authToken=configData.authToken;
  var defaultMarket=configData.defaultMarket;
  
  var url = 'https://mws.amazonservices.in/?';
  var today = new Date();
  var unsignedURL = 
  'POST\nmws.amazonservices.in\n/\n'+
  'AWSAccessKeyId=' +accessKey+
  '&Action=GetReport'+
  '&MWSAuthToken=' +authToken+
  '&Merchant='+sellerID+
  '&ReportId='+reportId+
  '&SignatureMethod=HmacSHA256'+
  '&SignatureVersion=2'+
  '&Timestamp='+encodeURIComponent(Utilities.formatDate(today, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")) + 
  '&Version=2009-01-01';
  
  var SignedRequest = calculatedSignature(unsignedURL, secretKey);
 var Encoded = Utilities.base64Encode(SignedRequest);
  
var param = 
  'AWSAccessKeyId=' +accessKey+
  '&Action=GetReport'+
  '&Merchant='+sellerID+
  '&MWSAuthToken=' +authToken+
  '&SignatureVersion=2'+
  '&Timestamp='+encodeURIComponent(Utilities.formatDate(today, "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'")) + 
  '&Version=2009-01-01'+
  '&Signature='+encodeURIComponent(SignedRequest) + 
  '&SignatureMethod=HmacSHA256'+
  '&ReportId='+reportId;
  
   var options = {
     "method" : "POST",
     "muteHttpExceptions" : true
   };

  var result = UrlFetchApp.fetch(url+param,options);
  writeArrayOfArraysToSheet(result);
}

//Write the array of values to the active sheet
function writeArrayOfArraysToSheet(result) {
  // Target range dimensions are determine
  // from those of the nested array.
  var sheet = SpreadsheetApp.getActiveSheet(),
      arrayOfArrays = getTsvFileAsArrayOfArays(result),
      dimensions = {rowCount: Math.floor(arrayOfArrays.length),
                 colCount: Math.floor(arrayOfArrays[0].length)},
      targetRng;
  sheet.clear();
  targetRng = sheet.getRange(1, 1, 
                             dimensions.rowCount,
                             dimensions.colCount);
  
  targetRng.setValues(arrayOfArrays);
  
}

function getTsvFileAsArrayOfArays(result) {
  // Read the file into a single string.
  // Split on line char.
  // Loop over lines yielding arrays by
  //  splitting on tabs.
  // Return an array of arrays.
  var lines = result.getContentText().split('\n'),
      lines2DArray = [];
  lines.forEach(function (line) {
                  lines2DArray.push(line.split('\t')); 
               });
  return lines2DArray;
}

function highLightProductWithLowQty() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var col = data[0].indexOf('afn-total-quantity');
  var dataRange = sheet.getDataRange();
  for (var i=1; i<=dataRange.getNumRows(); i++) {
    var row = sheet.getRange(i, col+1,1, 10);
    if (row.getValue() < 5) {
      row.setBackground("red");
    }else{
      row.setBackground("white");
    }
  }
}

function calculatedSignature(url,secret) {
var urlToSign = url;

  var byteSignature = Utilities.computeHmacSha256Signature(urlToSign, secret);
// convert byte array to hex string
var signature = byteSignature.reduce(function(str,chr){
  chr = (chr < 0 ? chr + 256 : chr).toString(16);
  return str + (chr.length==1?'0':'') + chr;
},'');
  return Utilities.base64Encode(byteSignature);
}

function parseXml(xml) {
 Logger.log(xml);
var document = XmlService.parse(xml);
var root = document.getRootElement();
var namespace = document.getRootElement().getNamespace();
var Sheet_Orders = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AMZ Orders');

 if (!Sheet_Orders) {
   Sheet_Orders=SpreadsheetApp.getActiveSpreadsheet().insertSheet('AMZ Orders');
}
  
var Sheet_Orders = SpreadsheetApp.getActiveSheet();
var reportType = root.getChild("RequestReportResult", namespace).getChild("ReportRequestInfo", namespace).getChild("ReportType", namespace).getValue();
var reportRequestId = root.getChild("RequestReportResult", namespace).getChild("ReportRequestInfo", namespace).getChild("ReportRequestId", namespace).getValue();
  Logger.log(reportType + ' : ' + reportRequestId)
  amazonMWSConfigProperties1.setProperty(reportType, reportRequestId);
  Logger.log(amazonMWSConfigProperties1.getProperty('_GET_FBA_MYI_UNSUPPRESSED_INVENTORY_DATA_'))
}

This will fetch the Inventory for all our FBA Products and update it in the Google Sheet and we can get the latest data every 30 minutes without any manual intervention.

I have shared all the code for this simple Use Case. If you have some suggestion to add new functionality to this Use Case or you have another Use Case to implement, let me know and i can share that in another blog.

For any queries related to Amazon MWS API or Google App Scripts, contact Tirnav Solutions or drop your queries in the comment box below or reach out to me through email: jain.jayesh@tirnav.com / jain.jayesh@outlook.com or through mobile: +91-9860571681.

Tirnav Solutions is a new-age digital agency from Mumbai with expertise in Mobile App development, Web App development and CRM Software like NetSuite, Salesforce etc.
React out to us for any development requirement on sales@tirnav.com

Thanks

Leave A Comment