CODEWITHSUNDEEP
javascriptgoogle-apps-script
CRB
CRB

Reputation: 71

Inputting an array into Math.Max in Google Apps Scripts

In a Google sheet, I have a custom form based off of some posts/blogs that I have found online to learn more about Google App Scripts. The form works correctly but I am having trouble with the column that should be creating an ID for the row.

From my reading, it seems that my problem is that math.max() takes a list not an array. Furthermore, none of the methods that I found for regular Javascript seem to be working in Google Apps Scripts. Such as, math.max.apply(null,array).

Do I have to iterate the array or is there something to make max() take an array?

Here is the code that I have been playing with.

function itemAdd(form) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var sh = SpreadsheetApp.getActiveSheet();
  var lRow = sh.getLastRow();
  var range = sh.getRange(3,1,lRow,1);
  var maxNum = Math.max(range)
  sheet.appendRow([maxNum,form.category, form.item, form.manupub, 
form.details, form.quantity]);
  return true;
}

Upvotes: 5

Views: 8588

Answers (3)

Antiez
Antiez

Reputation: 957

An neat alternative with .flat()and the spread operator ...:

var vals = range.getValues().flat();
var max = Math.max(...vals)

Upvotes: 2

Tanaike
Tanaike

Reputation: 201553

You can use Math.max.apply() at GAS. And data retrieved by getValues() is a 2 dimensional array. So in order to use the data for Math.max.apply(), the array has to be flattened. By reflecting this, the sample script can be modified as follows.

Script :

function itemAdd(form) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var sh = SpreadsheetApp.getActiveSheet();
  var lRow = sh.getLastRow();
  var range = sh.getRange(3,1,lRow,1);
  var ar = Array.prototype.concat.apply([], range.getValues());
  var maxNum = Math.max.apply(null, ar);
  sheet.appendRow([maxNum,form.category, form.item, form.manupub, form.details, form.quantity]);
  return true;
}

If I misunderstand your question, I'm sorry.

Upvotes: 11

terrywb
terrywb

Reputation: 3956

The following should help. You need to extract the values from the range. This will return a 2 dimensional array.

var vals = range.getValues();
//assuming your values are non negative, set the initial max to 0
var max = 0;
for(var i=0; i<vals.length; i++) {
  max = Math.max(max, vals[i][0]);
}
//max will now have the largest value

Upvotes: 1

Related Questions