Google spreadsheets script to create weekly reports in support of order products to providers.

gAppScriptBuildReport.js

// This is meant to be executed periodically, adapt it depending on you sell pace
// or create reports for previous weeks instead of using doing it in realtime.
var moment = Moment.load();
var ss = SpreadsheetApp.getActiveSpreadsheet();

var startOfLastWeek = moment.utc().startOf('week').add(1, 'd');
var endOfLastWeek = moment.utc().endOf('week').add(1, 'd');

Logger.log('Week starts: ' + startOfLastWeek.toString());
Logger.log('Week ends: ' + endOfLastWeek.toString());

function main() {
  // RR
  var aggregatedItems = {};
  
  var sourceSheet = ss.getSheetByName('daily_orders');
  var dateRange = sourceSheet.getRange("A:E")
  var data = dateRange.getValues();
  for (var i = 1; i < data.length; ++i) {
    if(data[i][0]){
      var row = data[i];
      var itemsAmount = row[1]
      var itemName = row[2]
      // Assume the date is in UTC
      var orderDate = moment.utc(row[3])
      var itemEan = row[4];      
      
      
      if(!aggregatedItems.hasOwnProperty(itemEan)){
        aggregatedItems[itemEan] = 0;
      }
      
      var newItemName = itemName.toLowerCase();
      if (newItemName.indexOf("orijen") !== -1 || newItemName.indexOf("acana") !== -1) { 
        // We don't have a recent version of momentjs in spreedsheets so we cannot use isSameOrAfter or similars
        if ((orderDate.isAfter(startOfLastWeek) && orderDate.isBefore(endOfLastWeek)) || orderDate.isSame(startOfLastWeek) || orderDate.isSame(endOfLastWeek)) {
          aggregatedItems[itemEan] += itemsAmount;
        }
      }
    }
  }
  buildWeekReport(aggregatedItems);
}


function getStockData(){
  updateStockData();
  var range = ss.getSheetByName('tp_inventory').getRange('A:D');
  var data = range.getValues();
  var eanQuantity = {};
  var matrix = [];
  for (var i = 1; i < data.length; ++i) {
    if(data[i][0]){
      var row = data[i];
      var itemEan = row[0]; 
      var itemName = row[2].toLowerCase();
      //if (itemName.indexOf("orijen") !== -1 || itemName.indexOf("acana") !== -1) { 
      eanQuantity[itemEan] = row[3];
      //}
    }
  }
  return eanQuantity;
}

function toOrder(sold, stock) {
  // East oriented code will make this easier to modify.
  if (stock == 0) {
    return sold + 2;
  }
  if (sold <= stock) {
    return 0;
  }
  if(stock < 0) {
    return -stock + 2;
  }
  //return sold - stock;
  return sold;
}

function buildWeekReport(aggregatedItems) {
  var sheetName = 'WeeklyReport_' + startOfLastWeek.format('YYYY-MM-DD');
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (sheet == null) {
    var position = ss.getNumSheets();
    ss.insertSheet(sheetName, position);
  }
  sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  sheetInventory = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tp_inventory");
  var matrix = [['ItemEan', 'ItemsSold', 'InStock', 'ToOrder']];
  var stockData = getStockData();
  for (var property in stockData) {
    if (stockData.hasOwnProperty(property)) {
      var itemEan = property;
      var itemsSold = aggregatedItems[itemEan] || 0;
      var itemsInStock = stockData[itemEan];
      var amountToOrder = toOrder(itemsSold, itemsInStock);
      matrix.push([itemEan, itemsSold, itemsInStock, amountToOrder]);
    }
  }
  sheet.getRange(1,1,matrix.length,matrix[0].length).setValues(matrix);
  sheet.setFrozenRows(1);
}

function updateStockData(){
  // Update from url invalidating cache hack
  var queryString = Math.random();
  var cellFunction = '=IMPORTHTML("http://telepienso.com/pages/internal-feed-masale?' + queryString + '","table", 1)';

  ss.getSheetByName("tp_inventory").getRange('A1').setValue(cellFunction);
}