You want to use two cells to track the balance owing (Cell A1) and the total amounts paid (Cell2) by clients.
It should be noted this approach is fraught with danger since any mis-type may have the effect of rendering the cumulative values of Cells A1 and A2 incomprehensible.
Why your script doesn't work.
Two reasons:
first, there can only be one onEdit()
trigger, whereas you have two functions named onEdit
. This is not a serious hurdle since the two functions can be easily combined. In fact they must be combined, since the a customer receipt not only updates cell A2, but also updates Cell A1.
second, the scenario dictates that the same cell must be used for data entry as well as the cumulative balance. The value of data entry is easily determined - it is the value of the cell.
The cumulative balance is the sum of the amount entered PLUS/MINUS the previous balance. However, depending on the transaction, it is not possible to use getValue()
for the previous balance since getValue()
will return only the current balance.
Fortunately, Event Objects come to the rescue - an object named oldValue
is available when a cell has a value prior to data entry. So the cumulative balance can be calculated as the sum of the value
plus the oldValue
The following answer uses a simple onEdit
trigger and Event Objects
. At the outset, there is no value available for oldValue
, so this is tested to determine if it is undefined; if yes, then a value of 0 (zero) is assigned.
Since the script relies on values in Cells A1 and A2, there is no way to halt the script; even deleting a value does not help.
I took the view that this was not a positive outcome, and added a test on Cell A3 - any text entered in this cell will stop the script.
function onEdit(e)
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
//Logger.log(JSON.stringify(e));
// variables
var sheetname = "Sheet1";
// A1 = Income
// A2 = Payments
// A3 = enter a word here to reset the script
var income = "A1";
var payments = "A2";
var endcode = "A3";
// get Event Objects
var editRange = e.range;
var sheet = editRange.getSheet();
// define ranges
var incomeRange = sheet.getRange(income);
var paymentsRange = sheet.getRange(payments);
var endcodeRange = sheet.getRange(endcode);
// trigger to End the code cycle
if (sheet.getName() === sheetname && editRange.getA1Notation() === endcode){
incomeRange.setValue("");
paymentsRange.setValue("");
endcodeRange.setValue("");
//Logger.log("end the routine");
return false;
}
// test for income
if (sheet.getName() === sheetname && editRange.getA1Notation() === income)
{
//Logger.log("the edited range is "+income)
// test for oldvalue = undefined
if (typeof e.oldValue === 'undefined') {
var OV = 0;
//Logger.log("old value = undefined so adjusted old value = "+OV);
}
else
{
var OV = Number(e.oldValue);
//Logger.log("old value = "+OV)
}
// test for new value = undefined
if (typeof e.value === 'undefined'){
var NV = 0;
//Logger.log("new value = undefined so adjusted new value = "+NV)
}
else
{
var NV = Number(e.value);
//Logger.log("new value = "+NV)
}
var newValue = OV+NV;
//Logger.log("Summary: OV:"+OV+", plus NV:"+NV+" equals newValue:"+newValue)
incomeRange.setValue(newValue);
//Logger.log("updated A2");
return false;
} // end update income
// test for payments
if (sheet.getName() === sheetname && editRange.getA1Notation() === payments)
{
Logger.log("the edited range is "+payments)
// test for oldvalue = undefined
if (typeof e.oldValue === 'undefined') {
var OV = 0;
//Logger.log("old value = undefined so adjusted old value = "+OV);
}
else
{
var OV = Number(e.oldValue);
//Logger.log("old value = "+OV)
}
// test for new value = undefined
if (typeof e.value === 'undefined'){
var NV = 0;
//Logger.log("new value = undefined so adjusted new value = "+NV)
}
else
{
var NV = Number(e.value);
//Logger.log("new value = "+NV)
}
var newValue = OV+NV;
//Logger.log("Summary Payments: OV:"+OV+", plus NV:"+NV+" equals newValue:"+newValue)
paymentsRange.setValue(newValue);
//Logger.log("updated B2")
// update Income outstanding
var incomeValue = incomeRange.getValue();
var adjIncome = incomeValue - NV;
//Logger.log("Summary: income value before adjustment:"+incomeValue+", less payment value:"+NV+" equals adjIncome:"+adjIncome)
incomeRange.setValue(adjIncome);
//Logger.log("updated B2")
return false;
}
return false;
}