Checking for Max and Min on different Columns Google Sheets-CodePudding

The following code does work as intented, it is comparing a in B4 and in C4. If B4 is a lower number than C4, it will replace C4 with the new lower number.

The loop is going through all other rows, this works fine.

function recordMinSellPrice() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numberRange = 4;
  
  for(var i= 0; i < 5;i   )
  {
  var test = "B"  numberRange ":" "C"   numberRange;
  var range = sheet.getRange(test);
  var values = range.getValues()[0];
    range.setValues([[values[0], Math.min(values[0], values[1])]]);
   numberRange  ;
  }
}

The code below doesn't work as intented. I want to Compare collum B with collum D instead of C on the code above.

But when i run this i get a Exception.

Exception: The amount of columns in the code is not matching with the amount of columns in the range. The code has 2, but the range has 3.

I think it has to do something with this part; var test = "B" numberRange ":" "D" numberRange;

I think its currently also selecting C numberRange. but i dont want to compare to C numberRange.

The code should compare B4 with D4. if B4 is larger than D4. replace D4 with B4. Start looping.

function recordMaxSellPrice() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var numberRange = 4;
  
  for(var i= 0; i < 5;i   )
  {
  var test = "B"  numberRange ":" "D"   numberRange;
  var range = sheet.getRange(test);
  var values = range.getValues()[0];
    range.setValues([[values[0], Math.max(values[0], values[1])]]);
   numberRange  ;
  }
}

I tried to explain my problem as good as i can. Thanks for helping in advance.

CodePudding user response：

In the bottom part

range.setValues([[values[0], Math.max(values[0], values[1])]]);

You are only passing 2 values... 2 cells to setValues.

However, the range that you are reading has 3 cells, B, C and D

So you have to set 3 values in the setValues command.

range.setValues([[B,C,D]]);

CodePudding user response：

Just in case. Here is the example how it could be done in accordance with the best practices:

function recordMaxSellPrice() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var data = range.getValues(); // get all data from the sheet

  // process the data with no redundant calls to the server
  for (var row in data) {
    data[row][3] = (data[row][3] > data[row][1]) ? data[row][3] : data[row][1]
  }
  
  // set updated data back on the sheet
  range.setValues(data);
}

data[row][1] is cell of column B in the row

data[row][3] is cell of column D in the row