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.
All data shown is provided and maintained by the COVID-19 Tracking Project.
Steps to follow:
- Create and apply dashboard template
- Create a Combo Box Cell Type
- 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.
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.
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.
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:
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.
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.
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;
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
If you any questions our customer engagement team is here to help: Contact Support
Mackenzie Albitz