Use Sheets as a Calculator or Adding Machine

Changing the value in place based on a leading operator.
Important Notes:
* Since ‘+’ is a legal operator to start a formula, Sheets automatically converts it to ‘=’
* Using .splice() on e.value essentially forces it to be considered a String. Multiply it by 1 to force it to be treated as a number for math.
* Using the ‘switch’ logic is useful here since the operator will be one of a discreet set of options.

Sheet (to copy)
https://docs.google.com/spreadsheets/d/1DcO-9wRETypAen9XwtMEWNLhFOVCOZzDtFqC8DtB6rc/edit#gid=0

Script:
function onEdit(e) {
if (e.range.rowStart != 2 || e.range.columnStart != 1) return;
let operator = e.value[0];
switch (operator){
case ‘=’:
e.range.setValue(1*e.oldValue+1*e.value.slice(1));
break;
case ‘-‘:
e.range.setValue(1*e.oldValue-1*e.value.slice(1));
break;
case ‘*’:
e.range.setValue((1*e.oldValue)*(1*e.value.slice(1)));
break;
case ‘/’:
e.range.setValue((1*e.oldValue)/(1*e.value.slice(1)));
break;
default:
e.range.setValue(e.oldValue);
}
}

Leave a Reply

Your email address will not be published. Required fields are marked *