This is something I needed to come up with the other day, but the documentation is a bit too all over the place. Basically all I wanted to do was to take a cell and make sure it's value existed in an array I populated from a remote source. In a nutshell, call a custom javascript function for the cell...

So here's a small example, just checks a local array, stripped clean for simplicity

It's important to note the syntax in the validation definition, it's R1C1 notation apparently. Relative cell notation, so R[0]C[0] is the current cell, which is why that's passed into the function.

arrayCheck.htmlView on GitHub
<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8"/>
    <title>Kendo UI Snippet</title>

  

  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2021.3.1207/styles/kendo.common.min.css">
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2021.3.1207/styles/kendo.rtl.min.css">
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2021.3.1207/styles/kendo.default.min.css">
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2021.3.1207/styles/kendo.mobile.all.min.css">
<script src="https://code.jquery.com/jquery-1.12.3.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2021.3.1207/js/angular.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2021.3.1207/js/jszip.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2021.3.1207/js/kendo.all.min.js"></script>
</head>
  <body>


&lt;script&gt;
  var items = ['apple', 'banana', 'pear']
  
  kendo.spreadsheet.defineFunction("ISINARRAY", function(fruit){ 
    console.log("OKay");
    return items.indexOf(fruit.toLowerCase()) &gt; -1;
  }).args([
    [ "fruit", "string"] 
  ]);
&lt;/script&gt;

&lt;div id="spreadsheet"&gt;&lt;/div&gt;   

&lt;script&gt;
  var spreadsheet = $("#spreadsheet").kendoSpreadsheet().getKendoSpreadsheet();

  var sheet = spreadsheet.activeSheet();
 
  sheet.range("A1:A20").validation({
    comparerType: "custom",
    dataType: "custom",
    from: 'AND(ISINARRAY(R[0]C[0]) = true)',
    type: "reject",
    allowNulls: true,
    messageTemplate: "Pick apple, banana, pear"
  });    
&lt;/script&gt;



  </body>
</html>

Here's another example that shows how to validate for a number

numCheck.htmlView on GitHub
<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8"/>
    <title>Kendo UI Snippet</title>

  

  <link rel="stylesheet" href="https://kendo.cdn.telerik.com/2021.3.1207/styles/kendo.common.min.css">
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2021.3.1207/styles/kendo.rtl.min.css">
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2021.3.1207/styles/kendo.default.min.css">
<link rel="stylesheet" href="https://kendo.cdn.telerik.com/2021.3.1207/styles/kendo.mobile.all.min.css">
<script src="https://code.jquery.com/jquery-1.12.3.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2021.3.1207/js/angular.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2021.3.1207/js/jszip.min.js"></script>
<script src="https://kendo.cdn.telerik.com/2021.3.1207/js/kendo.all.min.js"></script>
</head>
  <body>


&lt;script&gt;
  kendo.spreadsheet.defineFunction("GETNUMBER", function(num){ 
    return num * 2;
  }).args([
    [ "num", "number"]
  ]);
&lt;/script&gt;

&lt;div id="spreadsheet"&gt;&lt;/div&gt;   

&lt;script&gt;
  var spreadsheet = $("#spreadsheet").kendoSpreadsheet().getKendoSpreadsheet();

  var sheet = spreadsheet.activeSheet();
 
  sheet.range("A1:B2").validation({
    comparerType: "custom",
    dataType: "custom",
    from: 'AND(GETNUMBER(R[0]C[0])&gt;=10, GETNUMBER(R[0]C[0])&lt;=30)',
    type: "reject",
    messageTemplate: "Enter a number between 5 and 15"
  });    
&lt;/script&gt;



  </body>
</html>

Note

  • Note how the args are the variable AND the data type
  • defineFunction has to exist BEFORE you initialize the spreadsheet element.