Skip to content

Instantly share code, notes, and snippets.

@hinklefoxmail hinklefoxmail/inventory.gs Secret
Created Sep 3, 2017

Embed
What would you like to do?
Inventory System for Google Sheets
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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.