The following script subtracts all rows in the sheet when the on change trigger executes. I require the script to only subtract the row on which the change is made.
var sourceSpreadsheetID = '1r4e4BNKwsmdC2Ry93Mq-N49zj3DAZVpHG21TgTe0FWY' ;
var sourceWorksheetName = "CONCRETE";
var docketSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
var docketsheet = docketSpreadsheet.getSheetByName(sourceWorksheetName);
var maxRows = docketsheet.getMaxRows();
var loadRange = docketsheet.getRange(2, 22, maxRows);
//row, column, number of rows
var totalRange = docketsheet.getRange(2, 24, maxRows);
var soldValues = loadRange.getValues();
var totalValues = totalRange.getValues();
for (var row in soldValues) {
var soldCellData = soldValues[row][0];
var totalCellData = totalValues[row][0];
if (soldCellData !== "" && totalCellData !== "" ) {
totalValues[row][0] = totalCellData -
soldCellData;
}
}
loadRange.setValues(soldValues);
totalRange.setValues(totalValues);
}```
Thanks, your assistance is greatly appreciated
function myfunk() {
const dss = SpreadsheetApp.openById('1r4e4BNKwsmdC2Ry93Mq-N49zj3DAZVpHG21TgTe0FWY');
const dsh = dss.getSheetByName("CONCRETE");
const drg = dsh.getRange(2, 22, dsh.getLastRow() - 1, 3);
const vs = drg.getValues();
vs.forEach(r => {
if (r[0] != '' && r[2] != '') {
r[2] -= r[0];
}
});
dsh.getRange(2, 22, vs.length, vs[0].length).setValues(vs);
}
one row at a time:
function onEdit(e) {
const sh = e.range.getSheet();
if (sh.getName() == "CONCRETE" && e.range.rowStart > 1 && e.range.columnStart == 24) {
let vs = sh.getRange(e.range.rowStart, 22, 1, 3).getValues().flat();
sh.getRange(e.range.rowStart, 24).setValue(vs[2] - vs[0]);
}
}