CODEWITHSUNDEEP
javascriptgoogle-apps-script
koem32
koem32

Reputation: 25

Javascript google spreadsheets: how do I find the max value within a group

In google spreadsheets I have a column with repeated rows of years and a column of values. I want to write a script that finds the maximum value for each year. I have found the maximum value of all years with the script below but am having great difficulty finding a maximum for each year and logging it into a new column. 

function findmax(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var column = 9; 
var colArray = sheet.getRange(2, column, sheet.getLastRow()).getValues();
var maxi = Math.max.apply(Math, colArray)
Logger.log(maxi)

Any idea?

Upvotes: 1

Views: 3136

Answers (1)

Konstant
Konstant

Reputation: 2199

function maxGroupBy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var yearCol = 1; // <-assumption that the two column are not adjacent.
  var column = 9;

  var yearArray = sheet.getRange(1, yearCol, sheet.getLastRow()).getValues();
  var colArray = sheet.getRange(1, column, sheet.getLastRow()).getValues();

  var group = {};
  for (var i = 0; i< yearArray.length; i++) {
    group[yearArray[i]] = Math.max((group[yearArray[i]] || colArray[i]), colArray[i]);
  }
  for (key in group) {
    Logger.log(key + "::" + group[key]);  
  }
}

Upvotes: 2

Related Questions