Iterate through named range and update values-CodePudding

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.

CodePudding user response：

Issue:

r is a value from a cell, which can be a Number, Boolean, String, or a Date. None of these includes the method setValue. r should be a Range instead.

Code sample:

function onEdit(e) {
  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[counter][0] * FreeCopies[counter][0];
    freeCopiesValues[counter][0] = cost;
  }
  FreeCopiesTarget.setValues(freeCopiesValues);
}

Note:

It's not best practice to use getValues iteratively. It's preferrable to get all values at once and work with the resulting 2D array, as shown in the sample above.

CodePudding user response：

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