Inventory System for Google Sheets

inventory.gs

function createSS() {
  //get Variables
  var user = Session.getActiveUser();
  var thisSheet = SpreadsheetApp.getActiveSheet();
  var data = thisSheet.getDataRange().getValues();
  var template = DriveApp.getFileById('1QuKvHEt85MivyCLHqMWz02kCTterqM-ed81PyiKD404');
  var myDate = getDate();
  var dateAndTime = myDate.dateAndTime;
  var folder = DriveApp.getFolderById('0B3QaE26f2r25M0lUUjQ3V29HdG8');
  var addSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Added Log");
  //search through document for no id
  Logger.log('Begin search for Id');
  for (var i = 0 ; i < data.length; i++){
    var documentId = data[i][8];
    var row = i+1;
    if (documentId){
      continue;
    }
    
    var item = getInfoByIndex(i, data);
    Logger.log(item);
  
  if (!item.documentID){
    Logger.log("Need to Create Sheet");
  }
    //creates a new sheet in the folder and updates the old sheet with the info
    var newFile = template.makeCopy(item.brand+' '+item.type, folder)
    thisSheet.getRange(row, 9).setValue(newFile.getId());
    thisSheet.getRange(row, 10).setValue(newFile.getUrl());
    thisSheet.getRange(row, 12).setValue(item.startingQuantity);
    thisSheet.getRange(row, 6).setValue(item.startingQuantity);
    
    var newSS = SpreadsheetApp.open(newFile);
    var newSheet = newSS.getSheets()[0];
    newSheet.appendRow([dateAndTime, user, user, item.startingQuantity*-1, item.startingQuantity]);
    
    
    //Log to added Sheet
    var rowData = [myDate.dateAndTime,user,item.brand+' '+item.type,item.startingQuantity,item.uSD,item.startingQuantity*item.uSD]
    addSheet.appendRow(rowData);
    
    }
  
}



function getInfoById(id,type){
  Logger.log(id);
  Logger.log(type);
  //change vars depending on searched thing type
 if (type == 'item'){
 var dataId = '10ZExgJ1SkUu1_eOPf1js2so3KOGZXrl3HA0Q_KIkS30';
 var dataSheetName = 'overview';
 var barRow = 0;
  } else if (type == 'person'){
    var dataId = '1N5YoTJCcIWDS5Sg7AVoGfVJAlobZcgCV4N5rNkQeg8A';
    var dataSheetName = 'Staff';
    var id = '*'+id+'*';
    var barRow = 3;
  }
    
  
  //get ss
 var data = SpreadsheetApp.openById(dataId).getSheetByName(dataSheetName).getDataRange().getValues();
  for (var i = 0; i < data.length; i++){
    
    //insert barcode check field here
    var tableId = data[i][barRow];
    //If match, do___
    if (id == tableId){
      var myObject = getInfoByIndex(i, data)
      myObject.id = id;
      myObject.kind = type;
      return myObject;
  }
  
 
  }
   //Return Object with all values false
  var myObject = {index:-1, id:id, kind:type};
  for (var j = 0; j < data[0].length; j++){
        var propertyName = camelize(data[0][j]);
        myObject[propertyName] = false;
    } 
    return myObject;
}


function getInfoByIndex(index, data){
  Logger.log('Getting Info by Index');
  var myObject = {index:index}
  for (var j = 0; j < data[0].length; j++){
    Logger.log(data[0][j])
        var propertyName = camelize(data[0][j]);
        myObject[propertyName] = data[index][j];
    }   
      return myObject;
}


function camelize(str) {
  return str.replace(/(?:^\w|[A-Z]|\b\w|\s+)/g, function(match, index) {
    if (+match === 0) return ""; // or if (/\s+/.test(match)) for white spaces
    return index == 0 ? match.toLowerCase() : match.toUpperCase();
  });
}


function checkout(){
  //get vars
  var user = Session.getActiveUser();
  
  var thisSheet = SpreadsheetApp.getActive().getSheetByName('check out');
  var rawValues = thisSheet.getDataRange().getValues();
  Logger.log(rawValues);
  var taker = rawValues[1][1];
  Logger.log(taker);
  
  var department = rawValues[1][2];
  var deptName = 'Other'
  for (var i = 0; i < rawValues.length; i++){
    if (department == rawValues[i][5]){
      var deptName = rawValues[i][6];
      Logger.log(deptName);
      break;
    }
    
  }

  
  //get the person
  if (!taker || !department){
    SpreadsheetApp.getUi().alert('Incomplete info, checkout not run');
    return;
  } else if (isNaN(taker)){
    var person = {first: taker, last: '(No ID)',dept: deptName};
    
  } else {
  var person = getInfoById(taker,'person');
  person.dept = deptName;
  Logger.log(person);
    if (!person.first){
      SpreadsheetApp.getUi().alert('Unknown ID, checkout not run. If no id is present, just type the name in for person');
      return;
    }
  }
  var items = [];
  for (var i = 2; i < rawValues.length; i++){
    
   var barcode = rawValues[i][1];
    
    //if there is a item
    Logger.log(barcode);
    if (barcode){
      var amountTaken = rawValues[i][2];
      
      // if amount is missing, it is now 1
      if (!amountTaken){
        amountTaken = 1;
      }
      //update the sheets
      items.push(updateSheets(barcode, person, amountTaken));
      
      
      
      }
        
    }
   
  
  //give prompt that action has been successful
  var prompt = person.first+' '+person.last+' has taken:'
  for (var i = 0; i < items.length; i++){
    prompt = prompt +'\n'+items[i];
  }
 
  var values = [['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['','']]
  thisSheet.getRange(2, 2, 20, 2).setValues(values);
 SpreadsheetApp.getUi().alert(prompt);
  
  
}

function addStock(){
   //get vars
  var user = Session.getActiveUser();
  var thisSheet = SpreadsheetApp.getActive().getSheetByName('add stock');
  var rawValues = thisSheet.getDataRange().getValues();
  var department = 'Added Items';
  var person = {first: user, last:' ', dept: department}
  
  //get item data from barcode
  var items = [];
  for (var i = 1; i < rawValues.length; i++){
    
   var barcode = rawValues[i][1];
    
    //if there is a item
    Logger.log(barcode);
    if (barcode){
      var amountTaken = 0-rawValues[i][2];
      
      // if amount is missing, it is now 1
      if (!amountTaken){
        amountTaken = -1;
      }
      //update the sheets && find info
      items.push(updateSheets(barcode, person, amountTaken,i));
    }
  }
  
  //create prompt if action has been succesful
  var prompt = 'The following items have been added to the inventory:'
  for (var i = 0; i < items.length; i++){
    prompt = prompt +'\n'+items[i];
  }
 
  var values = [['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['',''],['','']]
  thisSheet.getRange(2, 2, 30, 2).setValues(values);
 SpreadsheetApp.getUi().alert(prompt);
 
}

function onOpen(){
  SpreadsheetApp.getUi().createMenu('Add Items').addItem('Add New Items', 'createSS').addItem('Add Stock', 'addStock').addToUi();
  SpreadsheetApp.getUi().createMenu('Checkout').addItem('Checkout', 'checkout').addToUi();
}

function updateSheets(barcode, person, amountTaken){
  //get vars
  var myDate = getDate();
  var user = Session.getActiveUser();
  
  //find item
  var item = getInfoById(barcode, 'item');
  if (!item.type){
    return (barcode+' is unknown. Please update the inventory');
  }
    item.taken = amountTaken;
    Logger.log(item);
  try{
    var itemSheet = SpreadsheetApp.openById(item.documentID);
  }
  catch(e){
     return (barcode+' is unknown. Please update the inventory');
  }
    var itemName = itemSheet.getName();
      
    item.remaining = item.currentQuantity - item.taken;
    itemSheet.appendRow([myDate.dateAndTime,person.first+' '+person.last,user,item.taken,item.remaining]);
      
    //update info on overview sheet
    SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 6).setValue(item.remaining);
   
  //if items were added...
  if (amountTaken <0){
    SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 4).setValue(item.startingQuantity - item.taken);
    if (!item.totalAdded){
      item.totalAdded = 0;
    }
    SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 12).setValue(item.totalAdded - item.taken);
    var rowData = [myDate.dateAndTime,person.first+' '+person.last,item.brand+' '+item.type,(item.taken*-1),item.uSD,item.taken*item.uSD*-1];
    SpreadsheetApp.getActive().getSheetByName('Added Log').appendRow(rowData);
  } else {
    //if items were removed...
    if (!item.totalRemoved){
      item.totalRemoved = 0;
    }
    SpreadsheetApp.getActive().getSheetByName('overview').getRange(item.index+1, 11).setValue(item.totalRemoved + item.taken);
    
    //get school year
    if (myDate.monthNumber < 7){
      var year = myDate.year-1+'-'+myDate.year;
    } else {
      var year = myDate.year+'-'+myDate.year+1;
    }
    
    //if dept log exists, get it
    var fileName = person.dept+' Inventory Checkout for '+year;
    if (DriveApp.getFilesByName(fileName).hasNext()){
      var deptSS = SpreadsheetApp.open(DriveApp.getFilesByName(fileName).next());
    } else {
      
      //otherwise make it
      var folder = DriveApp.getFolderById('0B3QaE26f2r25aE1QSXVSY1Z6VHM');
      var template = DriveApp.getFileById('1vvZi_AJq0kH7FN9xCIHskAUkeZrRCmQdWqnyjfEwRP0')
      var newFile = template.makeCopy(fileName, folder);
      deptSS = SpreadsheetApp.open(newFile);
    }
    
    var deptSheet = deptSS.getSheetByName('Total Used');
    var dataArray = [myDate.dateAndTime,person.first+' '+person.last,item.brand+' '+item.type,item.taken,item.uSD,item.taken*item.uSD]
    //and update
    deptSheet.appendRow(dataArray);
    
    var monthSheet = deptSS.getSheetByName(myDate.month);
    monthSheet.appendRow(dataArray);
    SpreadsheetApp.getActive().getSheetByName('Master Log').appendRow(dataArray);
  }
    
    
    Logger.log(item.remaining);
    SpreadsheetApp.flush();
    var abs = Math.abs(item.taken)
    var prompt = abs+' '+itemName;
    Logger.log(prompt);
    return (prompt);
  
}

function getDate(){  
  //get date objects and variables
  var myDate = {};
  var date = new Date();
  myDate.year = date.getFullYear();
  myDate.monthNumber = date.getMonth();
  var monthArray = new Array();
    monthArray[0] = "Jan";
    monthArray[1] = "Feb";
    monthArray[2] = "Mar";
    monthArray[3] = "Apr";
    monthArray[4] = "May";
    monthArray[5] = "June";
    monthArray[6] = "July";
    monthArray[7] = "Aug";
    monthArray[8] = "Sep";
    monthArray[9] = "Oct";
    monthArray[10] = "Nov";
    monthArray[11] = "Dec";
  
  var dayArray = ['Sun', 'Mon', 'Tue', 'Wed', 'Th', 'Fri', 'Sat']
  myDate.weekday = dayArray[date.getDay()];
  myDate.month = monthArray[myDate.monthNumber];
  myDate.day = dayArray[date.getDay()]+' '+date.getDate()
  myDate.time = Utilities.formatDate(date, 'GMT+1:00', "HH:mm:ss");
  myDate.now = Date.now();
  myDate.dateAndTime = Utilities.formatDate(date, 'GMT+1:00', "MM/dd/yyyy HH:mm");
  myDate.origin = date;
  return myDate;
}