Javascript 谷歌电子表格 : how do I find the max value within a group

Question

在谷歌电子表格中，我有一列重复的年份行和一列值。我想编写一个脚本来查找每年的最大值。我已经使用下面的脚本找到了所有年份的最大值，但是很难找到每年的最大值并将其记录到新列中。

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)

有什么想法吗？

Answer 1

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