Is it possible to use a standard GoogleSheets function in a script?

Question

I'd like to write something like this in a script.

max(A1:A10)

But when I try it, I get an error message saying: Missing ) after argument list. Is there a right way to do this?

Thanks.

Answer 1

No, its not possible to use sheet functions directly in your scripts.

But for this specific use-case where (I assume) you want to find the maximum number from a given range (A1:A10), you can do the following:

var sheet = SpreadsheetApp.getActive().getSheetByName("[NAME-OF-SHEET]");
var values = sheet.getRange("A1:A10").getValues();

// flatten values array
values = Array.prototype.concat.apply([], values);

// find maximum
var max = Math.max.apply(null, values);

Answer 2

If you want your code to be dynamic, you can write the function to google sheets from a script. Alternatively, you can compute directly.

var ss = SpreadsheetApp.getActiveSheet();
//  Gets full range of all data
//     Useful, but not for the method I'll proceed with...
var ss_Range = ss.getDataRange()
var ss_A1Range = ss_Range.getA1Notation();
var ss_Data = ss_Range.getValues();

// Alternatively, access your last row:
var last_r = ss.getRange(ss.getLastRow() + 1
var last_c = ss.getRange(ss.getLastColumn()+1)

// Write your computation as a function:
//    Say for column A
//    Write your computation beneath the last row (or wherever)
ss.getRange(1,last_r+1).setValue('=Today(A2:A' + last_r + ')') // sorry my string formatting has what to be improved upon

// Compute yourself:
var max_val = Math.max(ss_Range.getValues("A2:A"+last_r).getValues())
ss.getRange(1,last_r+1).setValue(max_val);

Answer 3

You can do that with the functionalities of SpreadsheetApp class [1].

Here is the code you can use in the script linked to your spreadsheet to obtain the max value from A column in "Sheet3" and set it in B1 cell:

function test3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet3"); 
  var values = sheet.getRange("A1:A10").getValues();
  var max = Number(values[0][0]);

  for(var i=1; i<values.length; i++) {
    var val = Number(values[i][0]);

    if(val > max) {
      max = val; 
    }   
  }
   sheet.getRange("B1").setValue(max);  
}

[1] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app