google-apps-scriptgoogle-sheetsnamed-rangessetvalue

Iterate through named range and update values


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.


Solution

  • 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);
    }