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;
}
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;
}
3 Answers
Reset to default 11You 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.
An neat alternative with .flat()and the spread operator ...:
var vals = range.getValues().flat();
var max = Math.max(...vals)
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