In this post, I demonstrate how to create an Inventory Tracking system on Googe Sheets that uses transactions of Receipt, Shipment, Transfer, and Adjustment sidebar forms.
How to Video:
Video Notes:
- Bootstrap Library CSS Link. Click Here
- Bootstrap Documentation
- Check out another Inventory Tracker Post. Click Here
Code In Video:
function ReceiptForm()
{
var form = HtmlService.createHtmlOutputFromFile('Receipt').setTitle('Receipt');
SpreadsheetApp.getUi().showSidebar(form);
}
function ShipmentForm()
{
var form = HtmlService.createHtmlOutputFromFile('Shipment').setTitle('Shipment');
SpreadsheetApp.getUi().showSidebar(form);
}
function AdjustmentForm()
{
var form = HtmlService.createHtmlOutputFromFile('Adjustment').setTitle('Adjustment');
SpreadsheetApp.getUi().showSidebar(form);
}
function TransferForm()
{
var form = HtmlService.createHtmlOutputFromFile('Transfer').setTitle('Transfer');
SpreadsheetApp.getUi().showSidebar(form);
}
function addMenu()
{
var menu = SpreadsheetApp.getUi().createMenu('Inventory Tracker');
menu.addItem('Receipt', 'ReceiptForm');
menu.addItem('Shipment', 'ShipmentForm');
menu.addItem('Transfer', 'TransferForm');
menu.addItem('Adjustment', 'AdjustmentForm');
menu.addItem('Delete Zero Inventory', 'DeleteZeroInventory');
menu.addToUi();
}
function onOpen(e)
{
addMenu();
}
function getAllPartNumbers()
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var partsSheet = ss.getSheetByName("Parts");
var [headers, ...data] = partsSheet.getDataRange().getValues();
return data;
}
function getAllInventoryParts()
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var inventorySheet = ss.getSheetByName("Inventory");
var [headers, ...data] = inventorySheet.getDataRange().getValues();
partArray = []
data.forEach(function(item, index)
{
if(!partArray.includes(item[0]) && item[2] !== 0)
{
partArray.push(item[0]);
}
});
partArray.sort();
return partArray;
}
function getAllLocations()
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var locationSheet = ss.getSheetByName("Locations");
var [headers, ...data] = locationSheet.getDataRange().getValues();
return data;
}
function getLocations(partNumber)
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var inventorySheet = ss.getSheetByName("Inventory");
var [headers, ...data] = inventorySheet.getDataRange().getValues();
var returnArray = [];
data.forEach(function(item, index)
{
if(item[0] == partNumber && item[2] !== 0)
{
returnArray.push(item[1]);
}
});
returnArray.sort();
return returnArray;
}
function getQuantity(partNumber, location)
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var inventorySheet = ss.getSheetByName("Inventory");
var [headers, ...data] = inventorySheet.getDataRange().getValues();
var returnArray = [];
// CODE BELOW USED IN VIDEO
//data.forEach(function(item, index)
//{
//(data[index][0] == partNumber && data[index][1] == location) ? returnArray.push(data[index][2]) : null;
//});
// BETTER CODE TO USE
for(var i = 0; i < data.length; i++)
{
if(data[i][0] == partNumber && data[i][1] == location)
{
returnArray.push(data[i][2]);
break;
}
}
return returnArray;
}
function DeleteZeroInventory()
{
var ui = SpreadsheetApp.getUi();
var response = ui.alert('OK to Delete Zero Inventory Parts?', ui.ButtonSet.YES_NO);
if (response == ui.Button.YES)
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var inventorySheet = ss.getSheetByName("Inventory");
var lastRow = inventorySheet.getLastRow();
for (var j = 2; j <= lastRow; j++)
{
if(inventorySheet.getRange(j,3).getValue() === 0)
{
inventorySheet.deleteRow(j);
j--;
}
}
}
}
function submitTransfer(partNumber, location_from, location_to, quantity)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inventorySheet = ss.getSheetByName("Inventory");
var logSheet = ss.getSheetByName("Log");
if(partNumber != '' && location_from != '' && location_to != '' && quantity !== '')
{
var [headers, ...data] = inventorySheet.getDataRange().getValues();
var foundRecord = false;
var errorOutput = '';
data.forEach(function(item, index)
{
if(data[index][0] == partNumber && data[index][1] == location_from)
{
var newQuantity = Number(data[index][2]) - Number(quantity);
if(newQuantity < 0)
{
errorOutput = "<span style=\"color: red; font-weight: bold\" >Not enough Quantity From location. Transaction Denied</span>";
}
else
{
inventorySheet.getRange(index+2,3).setValue(newQuantity);
}
foundRecord = true;
}
});
if(errorOutput != '')
{
return errorOutput;
}
var [headers, ...data] = inventorySheet.getDataRange().getValues();
var foundRecord = false;
data.forEach(function(item, index)
{
if(data[index][0] == partNumber && data[index][1] == location_to)
{
var newQuantity = Number(data[index][2]) + Number(quantity);
inventorySheet.getRange(index+2,3).setValue(newQuantity);
foundRecord = true;
}
});
if(foundRecord == false)
{
inventorySheet.appendRow([partNumber, location_to, quantity]);
}
logSheet.appendRow(['', 'Transfer', partNumber, location_from, location_to, quantity, new Date()]);
SpreadsheetApp.flush();
return "<span style=\"font-weight: bold\" >Record Saved</span>";
}
else
{
return "<span style=\"color: red; font-weight: bold\" >Enter Required Data</span>";
}
}
function submitShipment(orderNumber, partNumber, location, quantity)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inventorySheet = ss.getSheetByName("Inventory");
var logSheet = ss.getSheetByName("Log");
if(partNumber != '' && location != '' && quantity !== '')
{
//LAST ROW ON INVENTORY SHEET
var [headers, ...data] = inventorySheet.getDataRange().getValues();
var foundRecord = false;
var errorOutput = '';
data.forEach(function(item, index)
{
if(data[index][0] == partNumber && data[index][1] == location)
{
var newQuantity = Number(data[index][2]) - Number(quantity);
if(newQuantity < 0)
{
errorOutput = "<span style=\"color: red; font-weight: bold\" >Not enough Quantity from location. Transaction Denied</span>";
}
else
{
inventorySheet.getRange(index+2,3).setValue(newQuantity);
}
foundRecord = true;
}
});
if(errorOutput != '')
{
return errorOutput;
}
logSheet.appendRow([orderNumber, 'Shipment', partNumber, location, '', quantity, new Date()]);
SpreadsheetApp.flush();
return "<span style=\"font-weight: bold\" >Record Saved</span>";
}
else
{
return "<span style=\"color: red; font-weight: bold\" >Enter Required Data</span>";
}
}
function submitAdjustment(partNumber, location, quantity)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inventorySheet = ss.getSheetByName("Inventory");
var logSheet = ss.getSheetByName("Log");
if(partNumber != '' && location != '' && quantity !== '')
{
//LAST ROW ON INVENTORY SHEET
var [headers, ...data] = inventorySheet.getDataRange().getValues();
var foundRecord = false;
var errorOutput = '';
data.forEach(function(item, index)
{
if(data[index][0] == partNumber && data[index][1] == location)
{
if(Number(quantity) < 0)
{
errorOutput = "<span style=\"color: red; font-weight: bold\" >Quantity less then 0 Transaction Denied</span>";
}
else
{
inventorySheet.getRange(index+2,3).setValue(quantity);
}
foundRecord = true;
}
});
if(errorOutput != '')
{
return errorOutput;
}
logSheet.appendRow(['', 'Adjustment', partNumber, location, '', quantity, new Date()]);
SpreadsheetApp.flush();
return "<span style=\"font-weight: bold\" >Record Saved</span>";
}
else
{
return "<span style=\"color: red; font-weight: bold\" >Enter Required Data</span>";
}
}
function submitReceipt(poNumber, partNumber, location, quantity)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inventorySheet = ss.getSheetByName("Inventory");
var logSheet = ss.getSheetByName("Log");
if(partNumber != '' && location != '' && quantity !== '')
{
var [headers, ...data] = inventorySheet.getDataRange().getValues();
var foundRecord = false;
data.forEach(function(item, index)
{
if(data[index][0] == partNumber && data[index][1] == location)
{
var newQuantity = Number(data[index][2]) + Number(quantity);
inventorySheet.getRange(index+2,3).setValue(newQuantity);
foundRecord = true;
}
});
if(foundRecord == false)
{
inventorySheet.appendRow([partNumber, location, quantity]);
}
logSheet.appendRow([poNumber, 'Receipt', partNumber, '', location, quantity, new Date()]);
return "<span style=\"font-weight: bold\" >Record Saved</span>";
}
else
{
return "<span style=\"color: red; font-weight: bold\" >Enter Required Data</span>";
}
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.6.1/css/bootstrap.min.css" />
<script>
function SubmitRecord()
{
document.getElementById("displayReturn").innerHTML = "";
var poNumber = document.getElementById("poNumber").value;
var partNumber = document.getElementById("partNumber").value;
var location = document.getElementById("location").value;
var quantity = document.getElementById("quantity").value;
google.script.run.withSuccessHandler(returnBack).submitReceipt(poNumber, partNumber, location, quantity);
}
function returnBack(stringBack)
{
document.getElementById("displayReturn").innerHTML = stringBack;
document.getElementById("poNumber").value = '';
document.getElementById("partNumber").value = '';
document.getElementById("location").value = '';
document.getElementById("quantity").value = '';
}
function GetPartNumbers()
{
google.script.run.withSuccessHandler(function(ar)
{
var part_number = document.getElementById("partNumber");
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
part_number.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item[0];
option.text = item[0];
part_number.appendChild(option);
});
}).getAllPartNumbers();
};
function GetLocations()
{
google.script.run.withSuccessHandler(function(ar)
{
var location = document.getElementById("location");
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
location.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item[0];
option.text = item[0];
location.appendChild(option);
});
}).getAllLocations()
};
</script>
</head>
<body>
<form>
<div style="padding: 10px" >
<div class="form-row">
<div class="form-group col-md-6">
<label for="poNumber">PO Number</label>
<input type="text" id="poNumber" class="form-control" />
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="partNumber">Part Number</label>
<select id="partNumber" class="form-control" ></select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="location">Location</label>
<select id="location" class="form-control" ></select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="quantity">Quantity</label>
<input type="text" id="quantity" class="form-control" />
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<input type="button" value="Submit" onclick="SubmitRecord()" class="btn btn-primary" />
</div>
</div>
</form>
<div id="displayReturn" ></div>
</div>
<script>GetPartNumbers(); GetLocations(); </script>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.6.1/css/bootstrap.min.css" />
<script>
function SubmitRecord()
{
document.getElementById("displayReturn").innerHTML = "";
var orderNumber = document.getElementById("orderNumber").value;
var partNumber = document.getElementById("partNumber").value;
var location = document.getElementById("location").value;
var quantity = document.getElementById("quantity").value;
google.script.run.withSuccessHandler(returnBack).submitShipment(orderNumber, partNumber, location, quantity);
GetPartNumbers();
}
function returnBack(stringBack)
{
document.getElementById("displayReturn").innerHTML = stringBack;
document.getElementById("orderNumber").value = '';
document.getElementById("partNumber").value = '';
document.getElementById("location").value = '';
document.getElementById("quantity").value = '';
}
function GetPartNumbers()
{
google.script.run.withSuccessHandler(function(ar)
{
var part_number = document.getElementById("partNumber");
part_number.options.length = 0;
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
part_number.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
part_number.appendChild(option);
});
}).getAllInventoryParts();
};
function GetLocations(partNumber)
{
document.getElementById("quantity").value = '';
var location = document.getElementById("location");
location.options.length = 0;
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
location.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
location.appendChild(option);
});
}).getLocations(partNumber)
};
function GetQuantity(location)
{
var partNumber = document.getElementById("partNumber").value;
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
document.getElementById("quantity").value = ar[0];
}).getQuantity(partNumber, location)
};
</script>
</head>
<body>
<form>
<div style="padding: 10px" >
<div class="form-row">
<div class="form-group col-md-6">
<label for="orderNumber">Order Number</label>
<input type="text" id="orderNumber" class="form-control" />
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="partNumber">Part Number</label>
<select id="partNumber" class="form-control" onchange="GetLocations(this.value)" ></select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="location">Location</label>
<select id="location" class="form-control" onchange="GetQuantity(this.value)" ></select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="quantity">Quantity</label>
<input type="text" id="quantity" class="form-control" />
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<input type="button" value="Submit" onclick="SubmitRecord()" class="btn btn-primary" />
</div>
</div>
</form>
<div id="displayReturn" ></div>
</div>
<script>GetPartNumbers(); </script>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.6.1/css/bootstrap.min.css" />
<script>
function SubmitRecord()
{
document.getElementById("displayReturn").innerHTML = "";
var partNumber = document.getElementById("partNumber").value;
var location_from = document.getElementById("location_from").value;
var location_to = document.getElementById("location_to").value;
var quantity = document.getElementById("quantity").value;
google.script.run.withSuccessHandler(returnBack).submitTransfer(partNumber, location_from, location_to, quantity);
GetPartNumbers();
}
function returnBack(stringBack)
{
document.getElementById("displayReturn").innerHTML = stringBack;
document.getElementById("partNumber").value = '';
document.getElementById("location_from").value = '';
document.getElementById("location_to").value = '';
document.getElementById("quantity").value = '';
}
function GetPartNumbers()
{
google.script.run.withSuccessHandler(function(ar)
{
var part_number = document.getElementById("partNumber");
part_number.options.length = 0;
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
part_number.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
part_number.appendChild(option);
});
}).getAllInventoryParts();
};
function GetLocations(partNumber)
{
document.getElementById("quantity").value = '';
var location = document.getElementById("location_from");
location.options.length = 0;
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
location.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
location.appendChild(option);
});
}).getLocations(partNumber)
};
function GetAllLocations()
{
google.script.run.withSuccessHandler(function(ar)
{
var location = document.getElementById("location_to");
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
location.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item[0];
option.text = item[0];
location.appendChild(option);
});
}).getAllLocations()
};
function GetQuantity(location)
{
var partNumber = document.getElementById("partNumber").value;
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
document.getElementById("quantity").value = ar[0];
}).getQuantity(partNumber, location)
};
</script>
</head>
<body>
<form>
<div style="padding: 10px" >
<div class="form-row">
<div class="form-group col-md-6">
<label for="partNumber">Part Number</label>
<select id="partNumber" class="form-control" onchange="GetLocations(this.value)" ></select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="location_from">Location From</label>
<select id="location_from" class="form-control" onchange="GetQuantity(this.value)" ></select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="location_to">Location To</label>
<select id="location_to" class="form-control" ></select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="quantity">Quantity</label>
<input type="text" id="quantity" class="form-control" />
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<input type="button" value="Submit" onclick="SubmitRecord()" class="btn btn-primary" />
</div>
</div>
</form>
<div id="displayReturn" ></div>
</div>
<script>GetPartNumbers(); GetAllLocations(); </script>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.6.1/css/bootstrap.min.css" />
<script>
function SubmitRecord()
{
document.getElementById("displayReturn").innerHTML = "";
var partNumber = document.getElementById("partNumber").value;
var location = document.getElementById("location").value;
var quantity = document.getElementById("quantity").value;
google.script.run.withSuccessHandler(returnBack).submitAdjustment(partNumber, location, quantity);
GetPartNumbers();
}
function returnBack(stringBack)
{
document.getElementById("displayReturn").innerHTML = stringBack;
document.getElementById("partNumber").value = '';
document.getElementById("location").value = '';
document.getElementById("quantity").value = '';
}
function GetPartNumbers()
{
google.script.run.withSuccessHandler(function(ar)
{
var part_number = document.getElementById("partNumber");
part_number.options.length = 0;
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
part_number.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
part_number.appendChild(option);
});
}).getAllInventoryParts();
};
function GetLocations(partNumber)
{
document.getElementById("quantity").value = '';
var location = document.getElementById("location");
location.options.length = 0;
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
let option = document.createElement("option");
option.value = "";
option.text = "";
location.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
location.appendChild(option);
});
}).getLocations(partNumber)
};
function GetQuantity(location)
{
var partNumber = document.getElementById("partNumber").value;
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
document.getElementById("quantity").value = ar[0];
}).getQuantity(partNumber, location)
};
</script>
</head>
<body>
<form>
<div style="padding: 10px" >
<div class="form-row">
<div class="form-group col-md-6">
<label for="partNumber">Part Number</label>
<select id="partNumber" class="form-control" onchange="GetLocations(this.value)" ></select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="location">Location</label>
<select id="location" class="form-control" onchange="GetQuantity(this.value)" ></select>
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="quantity">Quantity</label>
<input type="text" id="quantity" class="form-control" />
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<input type="button" value="Submit" onclick="SubmitRecord()" class="btn btn-primary" />
</div>
</div>
</form>
<div id="displayReturn" ></div>
</div>
<script>GetPartNumbers(); </script>
</body>
</html>
Related Posts






