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();
}
}
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()
.