New to Kendo UI for jQueryStart a free 30-day trial

Spreadsheet retrieve range of formula cells

Environment

Product Version2021.1.330
ProductProgress® 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
EnvironmentDescriptionSolution
Not finding the help you need?
Contact Support