Results 1 to 3 of 3

Thread: Google Apps Script - BackOrder Info Code

  1. #1
    Join Date
    Apr 2012
    Posts
    440

    Default Google Apps Script - BackOrder Info Code

    Morning All,

    I had asked BP to see if there were any chances for a few modifications to help us with some pinch points. The earliest point they can review these is early next year, so in the meantime I have tried to create some bridges to allow us to get through the next couple of months.

    My coding skills are not great, but enough to at least kickstart looking at it. Accordingly I wanted to share some code that can be used in Google Sheets using Google Apps Script. The code at this moment may not be the best or most efficient, but it works and pulls in the data we need to understand what orders are on backorder and the value of these. I am hoping that BP will be able to add the qty picked/qty shipped fields to their order exports in the near future, or even access to the purchase order section fields via the API.

    Please use this as a starting to modify to your requirements, hope it is of use:

    Code:
    //****BackOrder Spreadsheet - BluePark*****
    // Collects data from BP API and places it in Google Sheets
    // To run, open a new Google Sheets> Tools> Macros. Clear the existing code and replace with this code
    // Add your own API Key, API User, and Root link to point it to your website
    // You will have to accept permissions to run this code
    // Once run the first time, you can add headers into the Spreadsheet to tidy it up
    // Code can then be refreshed by clicking on the BackOrder Menu tab on the sheet interface > Refresh Data
    // **This code is provided free of charge as an example of a working code for our system. We take no responsibility for any errors in the code or its processing efficiency
    
    
    //custom menu on spreadsheet
    function onOpen() {
      var ui = SpreadsheetApp.getUi();
      ui.createMenu('Backorder Menu')
          .addItem('Refresh Data','bpimport')
          .addToUi();
    }
    
    
    // API data, please fill with your own info from your admin manager (we created a separate admin name for the API credentials)
    var API_KEY = '**YOUR API KEY**';
    var API_USER = '**YOUR API USER**';
    var authHeader = 'Basic ' + Utilities.base64Encode(API_USER + ':' + API_KEY);      
    
    
    //calls the BP url to collect the data, the endpoint can be changed to get the specific data you need. This currently calls for any order that are pending, order number,
    //product data, billing address and order date from BP to reduce the amount of data flow. Calls only 200 records but this can be increased easily
    function bpimport() {
      var root = 'https://www.**YOUR WEBSITE**/api/v1/orders';
      var endpoint = '?status_id=1&fields=id%2Corder_number%2Cproducts%2Cbilling_address%2Corder_date&sort=order_date&order=desc&limit=200&page=1';
      
      var params = {
        'method' : 'GET' ,
        'muteHttpExceptions': true,
        'headers': {Authorization: authHeader}
      }
    
    
    //brings data in and parses the JSON, spits out in output array  
      var response = UrlFetchApp.fetch(root+endpoint, params);
      var json = response.getContentText();
      var data = JSON.parse(json);
      
      var output = []
    
    
    //the logger functions below allow you to check any immediate data. Numbers in brackets refer to row index numbers. Just remove the // comments and then look at the logs (View>Logs) 
    //Logger.log(data);
    //Logger.log(data[0]);
    //Logger.log(data[0].products[0]["sku"]);
    //Logger.log(data[0].order_number);
    //Logger.log(data[0].billing_address["company_name"]);
    //Logger.log(data[0].products[0]["title"]);
    //Logger.log(data[0].products[0]["variant_title"]);
    //Logger.log(data[0].products[0]["quantity"]);
    //Logger.log(data[0].products[0]["quantity_picked"]); 
    //Logger.log(data[0].products[0]["quantity_shipped"]);
      
    //loops through each line of the array to pull out specific sets of data as per their title in the output.push header  
        data.forEach(function(elem,i) {
    //this loops through each product as there maybe be multiple products within each line of data    
        for (var j = 0; j < data[i].products.length; j++) {  
          output.push([elem["order_number"],[elem["order_date"]],[elem["billing_address"]["company_name"]],elem["products"][j]["sku"],elem["products"][j]["title"],elem["products"][j]["variant_title"],elem["products"][j]["price_paid_exc_vat"],
                       elem["products"][j]["quantity"],elem["products"][j]["quantity_picked"],elem["products"][j]["quantity_shipped"]]);
        }           
        });
      
    //  Logger.log(output)
     
    // select the sheet name and plate in var sheet, or rename the sheet name to BackOrderInfo
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getSheetByName('BackOrderInfo');
        
    // calculates the number of rows and columns needed
        var numRows = output.length;
        var numCols = output[0].length;
    
    
    // clears the contents of the previous data dump so the data is refreshed. This only clears 2000 lines and 15 columns  
        sheet.getRange(3,1,2000,15).clearContent();
        
    // output the numbers to the sheet starting from row 3, column 1
        sheet.getRange(3,1,numRows,numCols).setValues(output);
      
     
    // small formulae to calculate the remaining items to be picked (orderqty  - qty picked) and remaining balance value (products sale value * backorder qty)
        for (var k = 0; k < numRows; k++) {
        sheet.getRange(3+k,11).setFormulaR1C1('=iferror(R[0]C[-3]-R[0]C[-2],"N/a")');
        sheet.getRange(3+k,12).setFormulaR1C1('=iferror(R[0]C[-1]*R[0]C[-5],"N/a")');
        }
      
    // small formula to sum the BackOrder value and apply to L1 cell
      var cell = sheet.getRange("L1");
      var r = 1 + numRows;
      cell.setFormulaR1C1('=SUM(R[2]C[0]:R[' + r + ']C[0])');
      
    //small formula to add datestamp to cell H1
      var date = new Date();
      var celldate = sheet.getRange("H1");
      celldate.setValue(date);
      
    }
    Shaun

    Loxta Hardware
    www.loxta.co.uk

  2. #2
    Join Date
    Jun 2012
    Location
    South West England
    Posts
    224

    Default

    Shaun, Well documented mate - the sign of a good programmer! Also logging helps you debug. Well done for sharing.
    Rob

  3. #3
    Join Date
    Apr 2012
    Posts
    440

    Default

    Thanks Rob

    Slowly slowly catchy monkey
    Shaun

    Loxta Hardware
    www.loxta.co.uk

Similar Threads

  1. Feature Request - Backorder info on packing list, or separate doc
    By LabelKing in forum Bluepark Technical Discussion
    Replies: 34
    Last Post: 30-05-2017, 15:17
  2. (Not Provided) - Google kills keyword info
    By StokedSEO in forum Online Marketing and Promotion
    Replies: 15
    Last Post: 27-09-2013, 10:52
  3. Google Apps - opinions please (follow on from 2 computers thread)
    By Bather in forum General Discussion: Any Other Business
    Replies: 3
    Last Post: 28-01-2013, 12:18
  4. Google apps verification, cloud based small business systems.
    By HIKPY in forum Online Marketing and Promotion
    Replies: 3
    Last Post: 18-11-2010, 17:16

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us

Bluepark's ecommerce software is developed, hosted and supported exclusively by ourselves, here in the UK, and has been so since the company was initially formed in 2004. Your brand new online shop will be hosted securely on our fast and reliable server network, using a domain name of your choice, providing the ability to build and maintain your ecommerce website, and manage your orders, from any location via your own secure online Administration Console.

A Bluepark ecommerce site is the perfect tool for selling online, whether you're selling physical products, digital downloads or services. You'll be in good company with over 1,000 UK customers who also form a friendly and helpful online community, further strengthening our highly acclaimed support network.

Find us on...