Need help finding the maximum value in a column in Google Sheets using Google Apps Script

Question

I have a sheet with a couple of thousand rows and 2 columns. I need to write a script that will increment be row and compare the value in one of the columns to the previous 50 values in the same column and see if it is larger than the maximum value of the 50 previous entries.

I've been trying to use Math.max(), but can't find the correct syntax to use to get that to work on a dynamic range.

Solution 1

when reading / writing in spreadsheets always try to do it at once, avoiding looping over the cells

I am sure there is a better javascript approach but this code will get you the max value within a column

function getMaxInColumn(column) {
  var column = 2; // just for testing
  var sheet = SpreadsheetApp.getActiveSheet();
  var colArray = sheet.getRange(2, column, sheet.getLastRow()).getValues();

  var maxInColumn = colArray.sort(function(a,b){return b-a})[0][0];
  return maxInColumn;
}

regards, Fausto

Solution 2

var max = Math.max.apply(null, column.getValues());

But the column can't contain any text, only numbers and empty values. Or else you'll need to exclude text from your array.

Solution 3

You can use the method setFormula(formula) and use a formula like =MAX(Range).

The method getDataRange() might be useful. After you get the range of data you can determine the maximum number of rows getLastRow() and columns getLastColumn().

Comments

[Clayton Hughes]

You advise against looping over the cells, but sort() is going to have to read every cell at least once anyway. Finding the max is O(n). Sorting often isn't. Is there any reasoning behind your advice here?