SpreadJS, our JavaScript spreadsheet, offers and supports a powerful calculation engine, an easy to use template builder, and an easy way to connect to data via URL. This tutorial discusses how to utilize the URL's imported data by visually displaying it in a dashboard template created using SpreadJS' Designer.

This is accomplished by using the URL data as the cell bound data source for the template created in the Designer. This tutorial will be using the COVID-19 Tracking Project's data API to provide the data endpoint for display and use in the SpreadJS instance.

Explore the SpreadJS Designer Features

Download SpreadJS

Download Now!

All data shown is provided and maintained by the COVID-19 Tracking Project.

runtime designer

Steps to follow:

  1. Create and apply dashboard template
  2. Create a Combo Box Cell Type
  3. Set URL data as the cell-binding data source

Step 1: Create and apply dashboard template

In this section, we will create a template using SpreadJS' and then populate that template using the URL data by setting it as the template's data source when the page loads.

SpreadJS offers a SpreadJS’s Runtime Designer and online Designer. The Designers offer a Template Builder option that can later use cell binding to populate the data. The Template Builder is located under the Data tab.

template builder

Using the AutoGenerateLabel option to create the binding path label automatically.
For this demo, add the following:

  • state
  • dataQualityGrade
  • positiveIncrease & negativeIncrease
  • pending
  • hospitalizedCurrently
  • inIcuCurrently
  • onVentilatorCurrently
  • death
  • recovered
  • totalTestResults

You can select a cell and double click a generated node or drag the node to the desired area. You can then design the cell as you would like to see the data displayed when bonded.

bonded cells

When using the designer, you can also utilize charts, sparklines, shapes, and all the styling formats. This is how we designed the template for this demo's purposes.

charts sparklines shapes

Another feature of SpreadJS that can be applied to a template using the Designer is Hyperlinks. For our example, we will be adding a hyperlink to the bottom right cell to the COVID Tracking Project’s main site. The URL we will be using for this example is https://covidtracking.com/.

To add this hyperlink, select the cell, right-click and select Link and then paste the link like so:

hyperlinks

Once finished creating the dashboard template, export the instances as a JavaScript file. Below is a gif depicting how to do this with the Designer, notice we name the JavaScript file template.

export template

Here is a link to download the dashboard template created for this example: Download template.js

Import the template from the JSON dashboard and refresh the instance

The final step: Include the template.js file in our project and then, using SpreadJS' fromJSON and refresh method, load SpreadJS from the dashboard template and then refresh the instance.

In the HTML tags include the template JSON file, template.js:

1.1 - Include the template javascript file exported from the runtime designer   
    <script src="template.js" type="text/javascript"></script></td>

Then load the SpreadJS instance from the template using the fromJSON and refresh method. Here is a code snippet showing this with the 1.2 markers:

          var spread = new GC.Spread.Sheets.Workbook(  
            document.getElementById("ss")  
          );  
          var spreadNS = GC.Spread.Sheets;  
        // 1.2 - Load SpreadJS from the template created with runtime designer  
            spread.fromJSON(template);  
            spread.options.showHorizontalScrollbar = false;  
            spread.options.showVerticalScrollbar = false;  
            spread.options.tabStripVisible = false;

        // 1.2 - Refresh the spread instance  
          spread.refresh();

          var sheet = spread.getActiveSheet();  
              sheet.options.colHeaderVisible = false;  
              sheet.options.rowHeaderVisible = false;

Step 2: Create a ComboBox cell type

Create a drop-down ComboBox cell type to select which state the end-user wants to see information on. After creating the ComboBox, we will set the cell type and default value to cell B1 (0,1):

 // 2.1 - Create combo box cell type for state selection  
         var combo = new spreadNS.CellTypes.ComboBox();  
              combo  
              .items([  
              { text: "Select a State", value: "" },  
              { text: "AL", value: "AL" },  
              { text: "AK", value: "AK" },  
              { text: "AZ", value: "AZ" },  
              { text: "AR", value: "AR" },  
              { text: "CA", value: "CA" },  
              { text: "CO", value: "CO" },  
              { text: "CT", value: "CT" },  
              { text: "DE", value: "DE" },  
              { text: "DC", value: "DC" },  
              { text: "FL", value: "FL" },  
              { text: "GA", value: "GA" },  
              { text: "HI", value: "HI" },  
              { text: "ID", value: "ID" },  
              { text: "IL", value: "IL" },  
              { text: "IN", value: "IN" },  
              { text: "IA", value: "IA" },  
              { text: "KS", value: "KS" },  
              { text: "KY", value: "KY" },  
              { text: "LA", value: "LA" },  
              { text: "ME", value: "ME" },  
              { text: "MD", value: "MD" },  
              { text: "MA", value: "MA" },  
              { text: "MI", value: "MI" },  
              { text: "MN", value: "MN" },  
              { text: "MS", value: "MS" },  
              { text: "MO", value: "MO" },  
              { text: "MT", value: "MT" },  
              { text: "NE", value: "NE" },  
              { text: "NV", value: "NV" },  
              { text: "NH", value: "NH" },  
              { text: "NJ", value: "NJ" },  
              { text: "NM", value: "NM" },  
              { text: "NY", value: "NY" },  
              { text: "NC", value: "NC" },  
              { text: "ND", value: "ND" },  
              { text: "OH", value: "OH" },  
              { text: "OK", value: "OK" },  
              { text: "OR", value: "OR" },  
              { text: "PA", value: "PA" },  
              { text: "RI", value: "RI" },  
              { text: "SC", value: "SC" },  
              { text: "SD", value: "SD" },  
              { text: "TN", value: "TN" },  
              { text: "TX", value: "TX" },  
              { text: "UT", value: "UT" },  
              { text: "VT", value: "VT" },  
              { text: "VA", value: "VA" },  
              { text: "WA", value: "WA" },  
              { text: "WV", value: "WV" },  
              { text: "WI", value: "WI" },  
              { text: "WY", value: "WY" },  
            ])  
              .editorValueType(spreadNS.CellTypes.EditorValueType.text);  
          // 2.1 - Set the default value of select a state  
             sheet  
             .getCell(0, 1, spreadNS.SheetArea.viewport)  
             .cellType(combo)  
             .value("Select a State");</td>

Step 3: Set URL data as the cell-binding data source

We will now be able to see the comboBox cell type applied to cell B1. Next, we will create a custom function when a user selects an item from the ComboBox.

3.1 Bind the instance to the CellChanged event

First, we must determine the state the end-user has selected using the value returned from the ComboBox cell type when it is changed. To do this, we must set first bind the CellChanged event to the SpreadJS instance like so:

 // 3.1 - Bind the CellChanged event to trigger when a new item is selected from the from comboBox  
          spread.bind(GC.Spread.Sheets.Events.CellChanged, (e, args) => {  
            if (args.propertyName === "value") {  
              var cellType = args.sheet.getCellType(args.row, args.col);  
              if (  
                !cellType ||  
                !(cellType instanceof GC.Spread.Sheets.CellTypes.ComboBox)  
              ) {  
                return;  
              }  
              var state = args.newValue.toLowerCase();  
              // Test: the state selected should alert when selected  
              console.log(state);  
            }  
          });</td>

3.2 Create the API's URL with selected state

After applying the above code and the test's success with the state abbreviation being alerted after being selected, we will use the state variable to get the wanted URL to get the state data from the COVID-19 Tracking Projects API. This is depicted below as 3.2:

// 3.1 - Bind the CellChanged event to trigger when a new item is selected from the from comboBox  
          spread.bind(GC.Spread.Sheets.Events.CellChanged, (e, args) => {  
            if (args.propertyName === "value") {  
              var cellType = args.sheet.getCellType(args.row, args.col);  
              if (  
                !cellType ||  
                !(cellType instanceof GC.Spread.Sheets.CellTypes.ComboBox)  
              ) {  
                return;  
              }  
              var state = args.newValue.toLowerCase();  
              // Test: The state selected should alert when selected  
              alert(state);  
              // 3.2 - use the selected state for API's url to get the data  
              var apiUrl =  
                "https://covidtracking.com/api/v1/states/" +  
                state +  
                "/current.json";  
              // Test: The Covid Tracking Projects state API's URL should appear in console  
                console.log(apiUrl);  
            }  
          });</td>

3.3 Get and set the URLs data as the cell-binding source

If both the tests result as expected, you can now remove the alert and console included in parts 3.1 and 3.2. Next, use the getJSON method with the created URL from step 3.2 to get the JSON data from the API from the URL, then set the returned data as the sheets cell-binding data source as shown below marked as 3.3:

// 3.1 - Bind the CellChanged event to trigger when a new item is selected from the from comboBox  
          spread.bind(GC.Spread.Sheets.Events.CellChanged, (e, args) => {  
            if (args.propertyName === "value") {  
              var cellType = args.sheet.getCellType(args.row, args.col);  
              if (  
                !cellType ||  
                !(cellType instanceof GC.Spread.Sheets.CellTypes.ComboBox)  
              ) {  
                return;  
              }  
              var state = args.newValue.toLowerCase();  
              // 3.2 - use the selected state for API's url to get the data  
              var apiUrl =  
                "https://covidtracking.com/api/v1/states/" +  
                state +  
                "/current.json";  
                // 3.3 - getJSON data from URL  
                $.getJSON(apiUrl, function (data) {  
                spread.suspendPaint();  
                spread.suspendCalcService();  
                // 3.3 - Set the URL's data as the sheets data source  
                sheet.setDataSource(  
                  new GC.Spread.Sheets.Bindings.CellBindingSource(data)  
                );  
                spread.refresh();  
                spread.resumeCalcService();  
                spread.resumePaint();  
              });  
            }  
          });</td>

After applying the code logic from all the steps and creating and applying the Runtime Designer template, your SpreadJS application will display data using the template's cell-binding paths added with the Designer.

cell binding paths

3.4 Modify SpreadJS viewport

We will then modify the SpreadJS viewport to remove the column and row headers, the vertical and horizontal scrollbar, and tab strip. We can accomplish this by setting the sheet options colHeaderVisible and rowHeaderVisible to false, then setting the workbook options showHorizontalScrollbar, showVerticalScrollbar, and tabStripVisible.

          // 3.4) Modify the viewport  
          var sheet = spread.getActiveSheet();  
            sheet.options.colHeaderVisible = false;  
            sheet.options.rowHeaderVisible = false;  
            spread.options.showHorizontalScrollbar = false;  
            spread.options.showVerticalScrollbar = false;  
            spread.options.tabStripVisible = false;

cell binding data

You have completed this tutorial and, using SpreadJS, have been able to crate a template using the Runtime Designer, created a cell type, set a data source from a URL, used events to load data, and modified the SpreadJS view port.

To work more with SpreadJS and see our many other features, download a free 30-day trial here: Grape City's SpreadJS

Explore the SpreadJS Designer Features

Download SpreadJS

Download Now!

If you any questions our customer engagement team is here to help: Contact Support