I need to iterate through a named range in Google Sheet and update the value based on other named ranges.
I have this now and I'm getting an error on line 13
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var FreeCopiesTarget = ss.getRangeByName('FreeCopiesTarget');
var CostPerCopy = ss.getRangeByName('CostPerCopy');
var FreeCopies = ss.getRangeByName('FreeCopies');
var values = FreeCopiesTarget.getValues();
for (var counter = 0; counter <= 4; counter++) {
var cost = CostPerCopy.getValues()[counter][0] * FreeCopies.getValues()[counter][0];
var r = FreeCopiesTarget.getValues()[counter][0];
r.setValue(cost);
}
}
Any help is appreciated.
Following Iamblichus answer, I tweaked as follows and it works as expected.
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var FreeCopiesTarget = ss.getRangeByName('FreeCopiesTarget');
freeCopiesValues = FreeCopiesTarget.getValues();
var CostPerCopy = ss.getRangeByName('CostPerCopy').getValues();
var FreeCopies = ss.getRangeByName('FreeCopies').getValues();
for (var counter = 0; counter <= 4; counter++) {
var cost = CostPerCopy[0][counter] * FreeCopies[0][counter];
freeCopiesValues[0][counter] = cost;
}
FreeCopiesTarget.setValues(freeCopiesValues);
}