I'm new to google sheet script and I'm trying to make a function that outputs the max value of possible opions from a DropDown list.
The feature of the function work but only when I call the following function in a cell
=MAXDROPDOWN(CELL("address";C13))
and it outputs an Error when doing
=MAXDROPDOWN(C13)
Where C13 is a dropdown list
I'm sure there is a better way to do this but I'm really unable to I tried a lot of options but it never works.
=MAXDROPDOWN("C13") works but it disables all smart features associated with functions and cell reference since its a string provided so it loses its advantage of using it over my proposition above with =MAXDROPDOWN(CELL("address";C13))
I tried with getA1notation before to but I only saw it functioning on current active cells the function is called from and not on cells as parameters to the function
Thanks in advance for your help
function MAXDROPDOWN(cell1){
try {
var cell = SpreadsheetApp.getActive().getRange(cell1);
var rule = cell.getDataValidation();
if (rule != null) {
var args = rule.getCriteriaValues();
return Math.max(...args[0])
}
}
catch( err ) {
return "#ERROR!"
}
}
Thank to TheWizEd I was able to do what I wanted. Below is what I did.
function MAXDROPDOWN(cell){
try {
var cell1 = SpreadsheetApp.getActive().getRange(CELL_ADDRESS(cell));
var rule = cell1.getDataValidation();
if (rule != null) {
var args = rule.getCriteriaValues();
return Math.max(...args[0])
}
}
catch( err ) {
return "#ERROR!"
}
}
function CELL_ADDRESS(cell) {
let test = SpreadsheetApp.getActiveSheet().getActiveCell().getFormula();
let address = test.match(/\(.+\)/)[0];
return(address.slice(1,-1));
}
Note that =MAXDROPDOWN(C13)
will pass the value of cell C13, not the address. If you want the address you can parse the formula as shown in this example.
If I place this formula in any cell as =MAXDROPDOWN(A1)
it will give me as address the string "A1"
function MAXDROPDOWN(cell) {
let test = SpreadsheetApp.getActiveSheet().getActiveCell().getFormula();
let address = test.match(/\(.+\)/)[0];
console.log(address.slice(1,-1));
}