Load Spreadsheet data based on conditional criteria

Environment

Product Version	2021.1.224
Product	Progress® Kendo UI® for jQuery

Description

How to populate Spreadsheet remote dataSource conditionally by dynamically changing the URL?

Solution

1. Initialize the DropDown Widget
2. Set up the DropDown's dataSource to match the different remote dataSource options of the Spreadsheet
3. Subscribe to the select event of the DropDown and set 'url' variable to be equal to the name of the selected dataItem, then pass it to the loadData() function
4. Initialize the Speadsheet
5. Define the loadData() function and in it
   1. set the parameter to be equal to a local variable url
      
   2. define a new kendo.data.DataSource
   3. get the activeSheet, then get its _rows._count and _columns._count
   4. use the sheet.range(0,0,rowsCount,columnsCount).clear() to clear any leftover data from the Spreadsheet
   5. pass the local url variable to the Speadsheet's transport.read.url
   6. finally in the loadData() use setDataSource to set the dataSource of the activeSheet to be equal to the kendo.data.DataSource

 <div id="dropdownlist" style="margin-bottom: 5px"></div>
    <div id="spreadsheet" style="width: 100%"></div>
    <script>

      $(document).ready(function(){
        $("#dropdownlist").kendoDropDownList({
          dataTextField: "name",
          dataValueField: "id",
          optionLabel: "Select dataSource...",
          dataSource:[
            { id: "1", name:"Products"},
            { id: "2", name:"Customers"}
          ],
          select:function(e){
            if(e.dataItem){
              var url = `/${e.dataItem.name}`;
              loadData(url);
            }
          }

        });
        $("#spreadsheet").kendoSpreadsheet({
          toolbar:false,
          columns:15,
          sheets: [{
            rows: [{
              height: 40,
              cells: [
                {
                  bold: "true",
                  background: "#9c27b0",
                  textAlign: "center",
                  color: "white"
                },{
                  bold: "true",
                  background: "#9c27b0",
                  textAlign: "center",
                  color: "white"
                },{
                  bold: "true",
                  background: "#9c27b0",
                  textAlign: "center",
                  color: "white"
                },{
                  bold: "true",
                  background: "#9c27b0",
                  textAlign: "center",
                  color: "white"
                },{
                  bold: "true",
                  background: "#9c27b0",
                  textAlign: "center",
                  color: "white"
                }]
            }],
            columns: [
              { width: 100 },
              { width: 415 },
              { width: 145 },
              { width: 145 },
              { width: 145 }
            ]
          }]
        });

        function loadData(path){
          var url = path;
          var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
          var sheet = spreadsheet.activeSheet();
          var rowsCount = sheet._rows._count;
          var columnsCount = sheet._columns._count;
          sheet.range(0,0,rowsCount,columnsCount).clear();

          var dataSource = new kendo.data.DataSource({
            transport: {
              read: function(options) {

                $.ajax({
                  url: "https://demos.telerik.com/kendo-ui/service"+url,
                  dataType: "jsonp", 
                  success: function(result) {
                    options.success(result);
                  },
                  error: function(result) {
                    options.error(result);
                  }
                });
              }
            },
            batch: true
          });
          sheet.setDataSource(dataSource);
        }
      });

</script>

See Also

• JavaScript API Reference of the DropDown
• JavaScript API Reference of the DataSource
• JavaScript API Reference of the Spreadsheet
• JavaScript API Reference of the Spreadsheet's Sheet
• jQuery API Reference of the AJAX method