google-apps-scriptgoogle-workspace

Inventory System in sheets that will add and subtract then clear


i have inventory list in sheets and to save confusion i would like a cell that will add and one to remove the value entered into the stock and clear rather than leave the math to the person entering it.

B1 = Item Name, B2 = Stock, B3 = Add Stock, B4 Remove Stock

So once a value is entered into B3 & B4 the cell will clear.

I can get one or the other to work but not both. Even pointing me in the right direction would be appreciated as all the research i am doing is leading me down a rabbit hole. I am using cells directly to simplify for learning.

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSheet();
var AddST = ss.getRange("C2").getValue();
var Stock = ss.getRange("B2").getValue();
var RemST = ss.getRange("D2").getValue();

function addStockLevel() {

    ss.getRange("B2").setValue(Stock + AddST);
    ss.getRange("C2").clear();

}

function removeStockLevel() {

    ss.getRange("B2").setValue(Stock - RemST);
    ss.getRange("D2").clear();

}
}

Solution

  • To do that in sheets named Sheet1, Sheet2 and Sheet3, but not on any other sheets, use this:

    'use strict';
    
    /**
    * Simple trigger that runs each time the user manually edits the spreadsheet.
    *
    * @param {Object} e The onEdit() event object.
    */
    function onEdit(e) {
      if (!e) {
        throw new Error(
          'Please do not run the onEdit(e) function in the script editor window. '
          + 'It runs automatically when you hand edit the spreadsheet. '
          + 'See https://stackoverflow.com/a/63851123/13045193.'
        );
      }
      adjustStockLevel_(e);
    }
    
    
    /**
    * Adjusts stock level.
    *
    * @param {Object} e The onEdit() event object.
    */
    function adjustStockLevel_(e) {
      if (!e.value || e.range.rowStart !== 2 || e.range.columnStart < 3 || e.range.columnStart > 4) return;
      const sheet = e.range.getSheet();
      if (!sheet.getName().match(/^(Sheet1|Sheet2|Sheet3)$/i)) return;
      const _get = (rangeA1) => Number(sheet.getRange(rangeA1).getValue()) || 0;
      const [stock, add, sub] = [_get('B2'), _get('C2'), _get('D2')];
      sheet.getRange('B2').setValue(stock + add - sub);
      sheet.getRange('C2:D2').clearContent();
    }
    

    To make it work on other sheets, adjust the regular expression in match().