SpreadJS offers an extremely powerful feature called Range Template Cell–allowing developers to define a template of cell ranges as a single cell type. This enables developers to apply the template to a cell to load specified data to display.
In this blog, we cover how to create, add, and set a data source to a Range Template Cell to display. We will use The COVID Tracking Projects API as our template's data source to retrieve daily, state-reported COVID-19 results.
Steps to Follow:
- Create the Range Template
- Create RangeTemplate Cell Type in the Specified Range
- Configure Data
- Final Design and Formatting
Step 1: Create the Range Template
To create a range template, we will first create a new worksheet called templateSheet. Get the active sheet that will display the range template cells and store it in a variable called renderSheet. Next, use the templateSheet to create a template with values and binding paths. This will also include the Suspend and Resume Paint methods that follow SpreadJS’s Best Practices.
While we create our template, keep in mind our end-goal:
It contains 6 rows and 3 columns with a span to display the state. It also contains data from the API, so need to add binding paths to the respected cells of the template using the SpreadJS setBindingPath method.
To help display the binding paths, here is an example of The COVID Tracking Project’s API Fields:
You can find an in-depth breakdown of the data fields on The COVID Tracking Projects website (under Current values for all states) linked here: https://covidtracking.com/data/api
The code below shows an example of adding binding paths and values:
function templateCells() {
function templateCells() {
// 1- Create a new worksheet as templateSheet
var templateSheet = new GC.Spread.Sheets.Worksheet();
// 1- Get the activesheet as renderSheet
var renderSheet = spread.getActiveSheet();
// 1-This templateSheet is used to create a template for RangeTemplate CellType
templateSheet.suspendPaint();
// 1- Template
templateSheet.addSpan(0, 0, 5, 1, GC.Spread.Sheets.SheetArea.viewport);
templateSheet.setBindingPath(0, 0, "state");
templateSheet.setValue(0, 1, "Cases Reported");
templateSheet.setBindingPath(0, 2, "positive");
templateSheet.setValue(1, 1, "New cases today");
templateSheet.setBindingPath(1, 2, "positiveIncrease");
templateSheet.setValue(2, 1, "Total Cases");
templateSheet.setBindingPath(2, 2, "totalTestResults");
templateSheet.setValue(3, 1, "Recovered");
templateSheet.setBindingPath(3, 2, "recovered");
templateSheet.setValue(4, 1, "Death");
templateSheet.setBindingPath(4, 2, "death");
templateSheet.resumePaint();
}
templateCells();
Step 2: Create RangeTemplate Cell Types in the Specified Range
Now we will create the RangeTemplate Cell Type–specifying the template scope. Add rangeTemplateCellType as a style using SpreadJS’s setStyle method to the active sheets in column B.
function templateCells() {
// 1 - Create a new worksheet as templateSheet
var templateSheet = new GC.Spread.Sheets.Worksheet();
// 1 - Get the active sheet as renderSheet
var renderSheet = spread.getActiveSheet();
// 1 - This templateSheet is used to create a template for RangeTemplate CellType
templateSheet.suspendPaint();
// 1 - Template binding paths
templateSheet.addSpan(0, 0, 5, 1, GC.Spread.Sheets.SheetArea.viewport);
templateSheet.setBindingPath(0, 0, "state");
templateSheet.setValue(0, 1, "Cases Reported");
templateSheet.setBindingPath(0, 2, "positive");
templateSheet.setValue(1, 1, "New cases today");
templateSheet.setBindingPath(1, 2, "positiveIncrease");
templateSheet.setValue(2, 1, "Total Cases");
templateSheet.setBindingPath(2, 2, "totalTestResults");
templateSheet.setValue(3, 1, "Recovered");
templateSheet.setBindingPath(3, 2, "recovered");
templateSheet.setValue(4, 1, "Death");
templateSheet.setBindingPath(4, 2, "death");
templateSheet.resumePaint();
// 2 - Create RangeTemplate Cell Types and specify the template scope
var rangeTemplateCelltype = new GC.Spread.Sheets.CellTypes.RangeTemplate(
templateSheet,
0,
0,
6,
3
);
// 2 - Add the RangeTemplate Cell Type as a style
var style = new GC.Spread.Sheets.Style();
style.cellType = rangeTemplateCelltype;
// 2 - Set the new style to the active sheet's column B
renderSheet.setStyle(-1, 1, style, GC.Spread.Sheets.SheetArea.viewport);
}
templateCells();
}
Step 3: Configure Data
After Step 2, we have completed our Range template! Your template is now set and ready to begin displaying data, based on the binding paths we included in Step 1. Currently, there is no data source set to the template so it will appear like this:
As we have mentioned, we are using The COVID Tracking Project’s data API for the current values for all the states. This is our range templates data source. First, we will create and add a custom function, called WebService, that fetches a given URL and stores the response as JSON.
Add the custom function as a formula, WEBSERVICE, to the active sheet and set the formula with a link to The COVID-19 Tracking Project's data. (https://api.covidtracking.com/v1/states/current.json)
Here is a code snippet for creating the custom function:
// 3- Create a custom function called WebService
function WebService() {}
// 3- Take all the function parameters as key/value pairs and wrap them into an object for template binding
WebService.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction(
"WEBSERVICE",
1,
1
);
WebService.prototype.defaultValue = function () {
return "Loading...";
};
// 3- Fetch the given URL and store the response as JSON
WebService.prototype.evaluateAsync = function (context, arg) {
const url = arg;
fetch(url).then((response) => {
const api_data = response.json();
api_data.then((data) => {
let x = data.map((d) => {
if (Array.isArray(d)) {
return d;
}
return [d];
});
var t = new GC.Spread.CalcEngine.CalcArray(x);
context.setAsyncResult(t);
});
});
};
With the custom function complete, add the custom function and then set the formula:
// 3- Add custom function
sheet.addCustomFunction(new WebService());
// 3- Set formula to B1
var f = '=WEBSERVICE("https://api.covidtracking.com/v1/states/current.json")';
sheet.setFormula(0, 1, f);
Our template now displays the data based on the binding paths we specified in Step 1:
Step 4: Template Design and Formatting
Now that we have the data displayed, we will add some final design aspects and formatting to display more visual data. Using the templateSheet, set the font, font size, and alignment of the span cell and add a left border. Apply a number formatter to the COVID data and remove the gridlines of the templateSheet.
// 4- Set font and font size to the Span
var cell = templateSheet.getCell(0, 0, GC.Spread.Sheets.SheetArea.viewport);
cell.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
cell.vAlign(GC.Spread.Sheets.VerticalAlign.center);
cell.font("36pt Arial");
// 4– add border next to state
templateSheet
.getRange(0, 0, 5, 1, GC.Spread.Sheets.SheetArea.viewport)
.borderRight(
new GC.Spread.Sheets.LineBorder(
"#61afef",
GC.Spread.Sheets.LineStyle.medium
)
);
// 4- Set formatter
templateSheet.setFormatter(
-1,
-1,
"#,###",
GC.Spread.Sheets.SheetArea.viewport
);
// 4- Remove grid lines
templateSheet.options.gridline = {
color: "#FF2235",
showVerticalGridline: false,
showHorizontalGridline: false,
};
With the final design and formatting added to our cell range template, the COVID-19 daily data is now displayed in the range template cell type like so:
We hope you enjoyed learning how to set the RangeTemplate cell type and retrieve data from a URL to serve the template's data source. This shows how easy it is to use SpreadJS's RangeTemplate cell type–giving you the power to display large amounts of data in a well-organized way.
Create your own template with the RangeTemplate Cell Type and have access to all of SpreadJS' powerful features today.
Mackenzie Albitz