How to Create Inventory Tracker Software Using Google Sheets

In this post, I demonstrate How to Create an Inventory Tracker Software Using Google Sheets. This uses parts and locations and does transactions like Receipts, Shipments, Transfers, and Inventory Adjustments.

How to Video:

Video Notes:

Legacy Apps Script Editor Used in Video
Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
Check out Cycle Counting on Google Sheets. Click Here
Check out Inventory Tracker using Bootstrap HTML Sidebar. Click Here

Code in Video:

function myReceiptView() {
  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MENU SHEET          
  var menuSheet = ss.getSheetByName("Menu");
  var partSheet = ss.getSheetByName("Parts");
  var locationSheet = ss.getSheetByName("Locations");
  
  //CLEAR DATA
  menuSheet.getRange("A3:B8").clear();
  menuSheet.getRange("A10:C5000").clear();
  menuSheet.getRange("A3:B8").setDataValidation(null);
  
  //TITLES
  menuSheet.getRange(3,1).setValue(["Transaction Type"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(3,2).setValue(["Receipt"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(4,1).setValue(["Part Number"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(5,1).setValue(["Location"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(6,1).setValue(["Quantity"]).setFontSize(12).setFontWeight("bold");
  
  //PART DROPDOWN
  var partCell = menuSheet.getRange('B4'); 
  var partLastRow = partSheet.getLastRow();
  var partRange = partSheet.getRange('A2:A' + partLastRow);
  var partRule = SpreadsheetApp.newDataValidation().requireValueInRange(partRange).build();
  partCell.setDataValidation(partRule);
  
  //LOCATION DROPDOWN
  var locationCell = menuSheet.getRange('B5'); 
  var locationLastRow = locationSheet.getLastRow();
  var locationRange = locationSheet.getRange('A2:A' + locationLastRow);
  var locationRule = SpreadsheetApp.newDataValidation().requireValueInRange(locationRange).build();
  locationCell.setDataValidation(locationRule);
  
  //QUANTITY
  var quantityCell = menuSheet.getRange('B6');
  var quantityRule = SpreadsheetApp.newDataValidation().requireNumberGreaterThanOrEqualTo(1).setAllowInvalid(false).build();
  quantityCell.setDataValidation(quantityRule);

}

function myShipmentView() {
  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MENU SHEET          
  var menuSheet = ss.getSheetByName("Menu");
  var partSheet = ss.getSheetByName("Parts");
  var locationSheet = ss.getSheetByName("Locations");
  
  //CLEAR DATA
  menuSheet.getRange("A3:B8").clear();
  menuSheet.getRange("A10:C5000").clear();
  menuSheet.getRange("A3:B8").setDataValidation(null);
  
  //TITLES
  menuSheet.getRange(3,1).setValue(["Transaction Type"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(3,2).setValue(["Shipment"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(4,1).setValue(["Part Number"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(5,1).setValue(["Location"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(6,1).setValue(["Quantity"]).setFontSize(12).setFontWeight("bold");
  
  //PART DROPDOWN
  var partCell = menuSheet.getRange('B4'); 
  var partLastRow = partSheet.getLastRow();
  var partRange = partSheet.getRange('A2:A' + partLastRow);
  var partRule = SpreadsheetApp.newDataValidation().requireValueInRange(partRange).build();
  partCell.setDataValidation(partRule);
  
  //LOCATION DROPDOWN
  var locationCell = menuSheet.getRange('B5'); 
  var locationLastRow = locationSheet.getLastRow();
  var locationRange = locationSheet.getRange('A2:A' + locationLastRow);
  var locationRule = SpreadsheetApp.newDataValidation().requireValueInRange(locationRange).build();
  locationCell.setDataValidation(locationRule);
  
  //QUANTITY
  var quantityCell = menuSheet.getRange('B6');
  var quantityRule = SpreadsheetApp.newDataValidation().requireNumberGreaterThanOrEqualTo(1).setAllowInvalid(false).build();
  quantityCell.setDataValidation(quantityRule);
  

}

function myTransferView() {
  
  //Browser.msgBox('Shipment');
  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MENU SHEET          
  var menuSheet = ss.getSheetByName("Menu");
  var partSheet = ss.getSheetByName("Parts");
  var locationSheet = ss.getSheetByName("Locations");
  
  //CLEAR DATA
  menuSheet.getRange("A3:B8").clear();
  menuSheet.getRange("A10:C5000").clear();
  menuSheet.getRange("A3:B8").setDataValidation(null);
  
  //TITLES
  menuSheet.getRange(3,1).setValue(["Transaction Type"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(3,2).setValue(["Transfer"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(4,1).setValue(["Part Number"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(5,1).setValue(["From Location"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(6,1).setValue(["To Location"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(7,1).setValue(["Quantity"]).setFontSize(12).setFontWeight("bold");
  
  //PART DROPDOWN
  var partCell = menuSheet.getRange('B4'); 
  var partLastRow = partSheet.getLastRow();
  var partRange = partSheet.getRange('A2:A' + partLastRow);
  var partRule = SpreadsheetApp.newDataValidation().requireValueInRange(partRange).build();
  partCell.setDataValidation(partRule);
  
  //FROM LOCATION DROPDOWN
  var locationCell = menuSheet.getRange('B5'); 
  var locationLastRow = locationSheet.getLastRow();
  var locationRange = locationSheet.getRange('A2:A' + locationLastRow);
  var locationRule = SpreadsheetApp.newDataValidation().requireValueInRange(locationRange).build();
  locationCell.setDataValidation(locationRule);
  
  //TO LOCATION DROPDOWN
  var locationToCell = menuSheet.getRange('B6');
  locationToCell.setDataValidation(locationRule);
  
  //QUANTITY
  var quantityCell = menuSheet.getRange('B7');
  var quantityRule = SpreadsheetApp.newDataValidation().requireNumberGreaterThanOrEqualTo(1).setAllowInvalid(false).build();
  quantityCell.setDataValidation(quantityRule);

}

function myInvAdjView() {
  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MENU SHEET          
  var menuSheet = ss.getSheetByName("Menu");
  var partSheet = ss.getSheetByName("Parts");
  var locationSheet = ss.getSheetByName("Locations");
  
  //CLEAR DATA
  menuSheet.getRange("A3:B8").clear();
  menuSheet.getRange("A10:C5000").clear();
  menuSheet.getRange("A3:B8").setDataValidation(null);
  
  //TITLES
  menuSheet.getRange(3,1).setValue(["Transaction Type"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(3,2).setValue(["Inventory Adjustment"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(4,1).setValue(["Part Number"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(5,1).setValue(["Location"]).setFontSize(12).setFontWeight("bold");
  menuSheet.getRange(6,1).setValue(["New Quantity"]).setFontSize(12).setFontWeight("bold");
  
  //PART DROPDOWN
  var partCell = menuSheet.getRange('B4'); 
  var partLastRow = partSheet.getLastRow();
  var partRange = partSheet.getRange('A2:A' + partLastRow);
  var partRule = SpreadsheetApp.newDataValidation().requireValueInRange(partRange).build();
  partCell.setDataValidation(partRule);
  
  //LOCATION DROPDOWN
  var locationCell = menuSheet.getRange('B5'); 
  var locationLastRow = locationSheet.getLastRow();
  var locationRange = locationSheet.getRange('A2:A' + locationLastRow);
  var locationRule = SpreadsheetApp.newDataValidation().requireValueInRange(locationRange).build();
  locationCell.setDataValidation(locationRule);
  
  //QUANTITY
  var quantityCell = menuSheet.getRange('B6');
  var quantityRule = SpreadsheetApp.newDataValidation().requireNumberGreaterThanOrEqualTo(0).setAllowInvalid(false).build();
  quantityCell.setDataValidation(quantityRule);
 

}

function submitResults() {
  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MENU SHEET          
  var menuSheet = ss.getSheetByName("Menu");
  var inventorySheet = ss.getSheetByName("Inventory");
  
  //TRANSACTION
  var transaction = menuSheet.getRange(3,2).getValue();
  
  if(transaction == "Receipt")
  {
    //GET VALUES
    var partNumber = menuSheet.getRange(4,2).getValue();
    var location = menuSheet.getRange(5,2).getValue();
    var quantity = menuSheet.getRange(6,2).getValue();
    
    if(partNumber != '' && location != '' && quantity != '')
    { 
      //LAST ROW ON INVENTORY SHEET
      var lastRow = inventorySheet.getLastRow() + 1;
      
      var foundRecord = false;
      for (var j = 2; j < lastRow; j++)
      {
        // UPDATE EXISTING QUANTITY AT LOCATION
        if(inventorySheet.getRange(j,1).getValue() == partNumber  && inventorySheet.getRange(j,2).getValue() == location)
        {
          var newQuantity = Number(inventorySheet.getRange(j,3).getValue()) + quantity;
          inventorySheet.getRange(j,3).setValue([newQuantity]);
          foundRecord = true;
        }
        
      }
      
      if(foundRecord == false)
      {
        inventorySheet.getRange(lastRow,1).setValue([partNumber]);
        inventorySheet.getRange(lastRow,2).setValue([location]);
        inventorySheet.getRange(lastRow,3).setValue([quantity]);
        
      }  
       
    }
    else
    {
      Browser.msgBox('Enter Required Data');
      return;
    }
    
    Browser.msgBox('Complete');
    myReceiptView();
  }
  
  if(transaction == "Shipment")
  {
    //GET VALUES
    var partNumber = menuSheet.getRange(4,2).getValue();
    var location = menuSheet.getRange(5,2).getValue();
    var quantity = menuSheet.getRange(6,2).getValue();
    
    if(partNumber != '' && location != '' && quantity != '')
    { 
      //LAST ROW ON INVENTORY SHEET
      var lastRow = inventorySheet.getLastRow() + 1;
      
      var foundRecord = false;
      for (var j = 2; j < lastRow; j++)
      {
        // UPDATE EXISTING QUANTITY AT LOCATION
        if(inventorySheet.getRange(j,1).getValue() == partNumber  && inventorySheet.getRange(j,2).getValue() == location)
        {
          var newQuantity = Number(inventorySheet.getRange(j,3).getValue()) - quantity;
          
          if(newQuantity < 0)
          {
            Browser.msgBox('Not enough Quantity from location.  Transaction Denied');
            return;
          }
          else if(newQuantity == 0)
          {
            inventorySheet.deleteRow(j);
          }
          else
          {
            inventorySheet.getRange(j,3).setValue([newQuantity]);
          }
          
          foundRecord = true;

        }
        
      }
      
      if(foundRecord == false)
      {
        Browser.msgBox('No quantity to Ship from Location.  Transaction Denied');
        return;
      }
       
    }
    else
    {
      Browser.msgBox('Enter Required Data');
      return;
    }
    
    Browser.msgBox('Complete');
    myShipmentView();
  }
  
  if(transaction == "Transfer")
  {
    //GET VALUES
    var partNumber = menuSheet.getRange(4,2).getValue();
    var fromLocation = menuSheet.getRange(5,2).getValue();
    var toLocation = menuSheet.getRange(6,2).getValue();
    var quantity = menuSheet.getRange(7,2).getValue();
    
    if(partNumber != '' && toLocation != '' && fromLocation != '' && quantity != '')
    { 
      //LAST ROW ON INVENTORY SHEET
      var lastRow = inventorySheet.getLastRow() + 1;
      
      var foundRecord = false;
      for (var j = 2; j < lastRow; j++)
      {
        // UPDATE EXISTING QUANTITY AT FROM LOCATION
        if(inventorySheet.getRange(j,1).getValue() == partNumber  && inventorySheet.getRange(j,2).getValue() == fromLocation)
        {
          var newQuantity = Number(inventorySheet.getRange(j,3).getValue()) - quantity;
          
          if(newQuantity < 0)
          {
            Browser.msgBox('Not enough Quantity from location.  Transaction Denied');
            return;
          }
          else if(newQuantity == 0)
          {
            inventorySheet.deleteRow(j);
          }
          else
          {
            inventorySheet.getRange(j,3).setValue([newQuantity]);
          }
          foundRecord = true;
          
        }
        
      }
      
      if(foundRecord == false)
      {
        Browser.msgBox('No quantity to Transfer from Location.  Transaction Denied');
        return;
      }
      
      var foundRecord = false;
      for (var j = 2; j < lastRow; j++)
      {
        // UPDATE EXISTING QUANTITY AT TO LOCATION
        if(inventorySheet.getRange(j,1).getValue() == partNumber  && inventorySheet.getRange(j,2).getValue() == toLocation)
        {
          var newQuantity = Number(inventorySheet.getRange(j,3).getValue()) + quantity;         
          inventorySheet.getRange(j,3).setValue([newQuantity]);
          foundRecord = true;
        }
        
      }
      
      if(foundRecord == false)
      {
        inventorySheet.getRange(lastRow,1).setValue([partNumber]);
        inventorySheet.getRange(lastRow,2).setValue([toLocation]);
        inventorySheet.getRange(lastRow,3).setValue([quantity]);
        
      } 
       
    }
    else
    {
      Browser.msgBox('Enter Required Data');
      return;
    }
    
    Browser.msgBox('Complete');
    myTransferView();
  }
  
  if(transaction == "Inventory Adjustment")
  {
    //GET VALUES
    var partNumber = menuSheet.getRange(4,2).getValue();
    var Location = menuSheet.getRange(5,2).getValue();
    var quantity = menuSheet.getRange(6,2).getValue();
    
    if(partNumber != ''&& location != '' && quantity !== '')
    { 
      //LAST ROW ON INVENTORY SHEET
      var lastRow = inventorySheet.getLastRow() + 1;
      
      var foundRecord = false;
      for (var j = 2; j < lastRow; j++)
      {
        // UPDATE EXISTING QUANTITY AT FROM LOCATION
        if(inventorySheet.getRange(j,1).getValue() == partNumber  && inventorySheet.getRange(j,2).getValue() == Location)
        {          
          if(quantity < 0)
          {
            Browser.msgBox('Not enough Quantity must be greater than 0.  Transaction Denied');
            return;
          }
          else if(quantity == 0)
          {
            inventorySheet.deleteRow(j);
          }
          else
          {
            inventorySheet.getRange(j,3).setValue([quantity]);
          }
          foundRecord = true;
        }
        
      }
      if(foundRecord == false)
      {
        Browser.msgBox('No Location Found.  Transaction Denied');
        return;
      }
      
    }
    else
    {
      Browser.msgBox('Enter Required Data');
      return;
    }
    
    Browser.msgBox('Complete');
    myInvAdjView();
  }
  
}

function onEdit(e)
{
  var range = e.range;
  var spreadSheet = e.source;
  var spreadSheetName = spreadSheet.getActiveSheet().getName();
  var searchColumn = range.getColumn();
  var searchRow = range.getRow();
  
  //DEFINE ALL ACTIVE SHEETS
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //DEFINE MENU SHEET          
  var menuSheet = ss.getSheetByName("Menu");
  var inventorySheet = ss.getSheetByName("Inventory");
  
  Logger.log('Column: ' + searchColumn + ' Row: ' + searchRow + ' Value: ' + e.value + ' spreadSheetName: ' + spreadSheetName);
  
  if (searchColumn == 2 && searchRow == 4 && e.value != '' && spreadSheetName == 'Menu')
  {
    var partNumber = e.value;
    
    //LAST ROW ON INVENTORY SHEET
    var lastRow = inventorySheet.getLastRow() + 1;
    var foundRecord = false;
    
    for (var j = 2; j < lastRow; j++)
    {
      // UPDATE EXISTING QUANTITY AT FROM LOCATION
      if(inventorySheet.getRange(j,1).getValue() == partNumber)
      {  
        var nextRow = menuSheet.getLastRow() + 1;
        
        menuSheet.getRange(nextRow,1).setValue([inventorySheet.getRange(j,1).getValue()]).setFontSize(12).setFontWeight("bold");
        menuSheet.getRange(nextRow,2).setValue([inventorySheet.getRange(j,2).getValue()]).setFontSize(12).setFontWeight("bold");
        menuSheet.getRange(nextRow,3).setValue([inventorySheet.getRange(j,3).getValue()]).setFontSize(12).setFontWeight("bold");
        foundRecord = true;
      }
      
    }
    
    if(foundRecord == false)
    {
      menuSheet.getRange(10,1).setValue(['(NO RECORDS FOUND)']).setFontSize(12).setFontWeight("bold");
    }
    
    
  }
  
  
}

How to Create Time Log Web App on Google Sheets - In this post, I demonstrate how to create a Time Log Google Web App on Google Sheets.

How to Create an Inventory Tracker on Google Sheets - 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 Create Purchase Order Generator on Google Sheets - In this post, I demonstrate how to create a Purchase Order Generator using the Google Sheets Template and populate the data needed for a Purchase Order using Google Apps Script.

Create Employee Time Clock Web App on Google Sheets - In this post, I demonstrate how to Create an Employee Time Clock Web App on Google Sheets. This is a basic tool for employees to clock in and out during a workday.

How to Create Invoice Generator on Google Sheets - In this post, I demonstrate how to create an Invoice Generator using the Google Sheets Template. In doing this use Google Apps Script to populate the needed Invoice Information to the Template

How to Create Cycle Count Inventory Software on Google Sheets - In this post, I demonstrate how to create a Cycle Count Inventory Software on Google Sheets using Google Apps Script. This can be used to accurately maintain your inventory

How to Create a Time Tracker Using Google Sheets - In this post, I demonstrate How to Put together a Time Tracker to Clock In and Out Multiple Users using Google Apps Script on Google Sheets.

How to Create Inventory Tracker Software Using Google Sheets

How to Video:

Video Notes:

Code in Video:

Related Posts

Published by codewithcurt