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>

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

    <div id="spreadsheet"></div>   

    <script>
      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"
      });    
    </script>
  </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>

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

    <div id="spreadsheet"></div>   

    <script>
      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])>=10, GETNUMBER(R[0]C[0])<=30)',
        type: "reject",
        messageTemplate: "Enter a number between 5 and 15"
      });    
    </script>
  </body>
</html>

Note

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