Custom function constantly throwing an error

Question

I wrote a custom in-cell function for having a specified data validation list only if a given cell isn't empty. Here's that function:

function CONDITIONALVALIDATION(sheetName,cellToCheckA1,validationCell,validationItems){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(sheetName);
  var cellValue = sheet.getRange(cellToCheckA1).getValue();
  var cellToSet = sheet.getRange(validationCell);
  cellToSet.clearDataValidations();
  if(cellValue!=""){
  var unitsRule = SpreadsheetApp.newDataValidation().requireValueInList(validationItems, true);
    cellToSet.setDataValidation(unitsRule)
  }
}

When I call a test function which fills the parameters for the CONDITIONALVALIDATION function and run it within the editor, I see the desired results. That function is the following:

function testconditional(){
  CONDITIONALVALIDATION("Front End","E12","F12",["x","y"]);
}

However, when I call the function as a custom in-cell function, I constantly get a

"Formula Parse Error."

I have already ruled out syntax as a possible cause; the call to the function in my test function is the exact same as when I call it within a custom in-cell function. Save, of course, the equals sign before the function call to indicate that it is a function to be run. I also know that the function is recognized by sheets; there is no "Function nonexistant", or "#NAME?" error when the function is called within the cell.

How do I solve this problem?

TheMaster TheMaster · Accepted Answer · 2019-04-27T18:22:46

Issue(s):

Javascript Array literals [] are not valid spreadsheet formula syntax. Try {} instead:

=CONDITIONALVALIDATION("Front End","E12","F12",{"x","y"});

Having said that, As written in the documentation, Custom functions can't .setDataValidation(unitsRule)

Spreadsheet

Read only (can use most get*() methods, but not set*()).

Solution:

To use a service other than those listed above, create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.

Custom function constantly throwing an error

2 Answers

Issue(s):

Solution: