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.

finished product

Steps to Follow:

  1. Create the Range Template
  2. Create RangeTemplate Cell Type in the Specified Range
  3. Configure Data
  4. Final Design and Formatting

Try SpreadJS's spreadsheet components

Download the latest version of SpreadJS

Download Now!

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:

template

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:

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:

configure data

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:

data

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:

range template cell type

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.

Try SpreadJS's spreadsheet components

Download the latest version of SpreadJS

Download Now!