New to Kendo UI for jQuery? Download free 30-day trial

Spreadsheet retrieve range of formula cells

Environment

Product Version 2021.1.330
Product Progress® Kendo UI® Spreadsheet for jQuery

Description

I want to identify all the formula cells so that I can perform actions on them after initialization such as change background color or disable them. How do I get all the formula values and return their respective ranges?

Solution

  1. Get the Spreadsheet's [activeSheet](https://docs.telerik.com/kendo-ui/api/javascript/ui/spreadsheet/methods/activesheet)
  2. Use its private _properties.get("formula").values() method to get an Array of the cells values that have formulas
  3. Utilize the JS .map() method, to map the Array
  4. In the callback of the map method use the [sheet.range](https://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/sheet/methods/range) method to get the [Range](https://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/range) of the formula cells currently being mapped and return it to enable later manipulation
<div id="example">
    <div id="spreadsheet" style="width: 100%;"></div>
    <script>
        var sp = $("#spreadsheet").kendoSpreadsheet({
          sheets: [{
            name: "Test",
            rows: [{
              cells: [
                {"format":"#","index":0,"value":1},
                {"format":"#","index":1,"value":2},
                {"formula":"SUM(A1, B1)","index":2,"value":3},
                {"formula":"SUM(C1, C2)","index":3,"value":10}
              ]
            },{
              cells: [
                {"format":"#","index":0,"value":3},
                {"format":"#","index":1,"value":4},
                {"formula":"SUM(A2, B2)", "index": 2, "value":7}
              ]
            }]
          }]
        }).getKendoSpreadsheet();

      var sheet = sp.activeSheet();
      var formulas = sheet._properties.get("formula").values().map(function (f) {
        var formulaRange = sheet.range(f.value.row, f.value.col);
        formulaRange.background("#92a8d1");
        return formulaRange;
      });
      console.log(formulas);
    </script>
</div>
In this article