ChartJS Integration

Jspreadsheet offers integration with Chart.js that simplifies chart creation within the data grid. To see more working examples of this integration, please refer to the Chart.js documentation. This plugin is used through the =CHART formula, which has two forms explained below.

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/chart.umd.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/[email protected]/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
jspreadsheet.setLicense('ZjdhZTdkZWE5MjdmOWVlMTM3YTczYTU3ZDE2OGFhZjNkODRiYzMxYzQ3Y2RhYjRlMjliYzQzMGYwMzhlOGY4NjY0MjE2YWZhNDhjNzVmYWYwOGRhZGZhMzU3NTEwMThlMDViMGNlNjFmZTdhZGViNzExZGNjYzEyMzRmZGY3MWYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGc0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        {
            data: [
                ['=CHART("pie", A4:A6, B4:B6)'],
                [],
                ['Proposals', 'Votes (%)'],
                ['Proposal 1', 0.36],
                ['Proposal 2', 0.29],
                ['Proposal 3', 0.35],
            ],
            minDimensions: [6, 1],
            mergeCells: {
                'A1': [6, 1],
            },
            defaultColWidth: '60px',
            rows: {0: {height: '380px'}},
        }
    ]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, jspreadsheet } from "@jspreadsheet/react";
import "@jspreadsheet/formula-charts";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjdhZTdkZWE5MjdmOWVlMTM3YTczYTU3ZDE2OGFhZjNkODRiYzMxYzQ3Y2RhYjRlMjliYzQzMGYwMzhlOGY4NjY0MjE2YWZhNDhjNzVmYWYwOGRhZGZhMzU3NTEwMThlMDViMGNlNjFmZTdhZGViNzExZGNjYzEyMzRmZGY3MWYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGc0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();
    // Worksheets
    const worksheets = [
        {
            data: [
                ['=CHART("pie", A4:A6, B4:B6)'],
                [],
                ['Proposals', 'Votes (%)'],
                ['Proposal 1', 0.36],
                ['Proposal 2', 0.29],
                ['Proposal 3', 0.35],
            ],
            minDimensions: [6, 1],
            mergeCells: {
                'A1': [6, 1],
            },
            defaultColWidth: '60px',
            columns: [
                {width: '100px'},
                {width: '100px'},
            ],
            rows: {0: {height: '380px'}},
        }
    ];

    // Render data grid component
    return (<Spreadsheet ref={spreadsheet} worksheets={worksheets}/>);
}

<template>
  <Spreadsheet :worksheets="worksheets" />
</template>

<script>
import { Spreadsheet, jspreadsheet } from "@jspreadsheet/vue";
import "@jspreadsheet/formula-charts";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('ZjdhZTdkZWE5MjdmOWVlMTM3YTczYTU3ZDE2OGFhZjNkODRiYzMxYzQ3Y2RhYjRlMjliYzQzMGYwMzhlOGY4NjY0MjE2YWZhNDhjNzVmYWYwOGRhZGZhMzU3NTEwMThlMDViMGNlNjFmZTdhZGViNzExZGNjYzEyMzRmZGY3MWYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGc0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

export default {
  components: {
    Spreadsheet,
  },
  setup() {
    // Worksheets
    const worksheets = [
        {
            data: [
                ['=CHART("pie", A4:A6, B4:B6)'],
                [],
                ['Proposals', 'Votes (%)'],
                ['Proposal 1', 0.36],
                ['Proposal 2', 0.29],
                ['Proposal 3', 0.35],
            ],
            minDimensions: [6, 1],
            mergeCells: {
                'A1': [6, 1],
            },
            defaultColWidth: '60px',
            columns: [
                {width: '100px'},
                {width: '100px'},
            ],
            rows: {0: {height: '380px'}},
        }
    ];

    return { worksheets };
  },
};
</script>

import { Component, ViewChild, ElementRef, AfterViewInit } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import "@jspreadsheet/formula-charts";
import "jspreadsheet/dist/jspreadsheet.css";
import "jsuites/dist/jsuites.css";

jspreadsheet.setLicense('ZjdhZTdkZWE5MjdmOWVlMTM3YTczYTU3ZDE2OGFhZjNkODRiYzMxYzQ3Y2RhYjRlMjliYzQzMGYwMzhlOGY4NjY0MjE2YWZhNDhjNzVmYWYwOGRhZGZhMzU3NTEwMThlMDViMGNlNjFmZTdhZGViNzExZGNjYzEyMzRmZGY3MWYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNU56azJNRGc0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

@Component({
  standalone: true,
  selector: "app-root",
  template: `<div #spreadsheet></div>`
})

export class AppComponent implements AfterViewInit {
  @ViewChild("spreadsheet") spreadsheet: ElementRef;
  worksheets: jspreadsheet.worksheetInstance[];

  ngAfterViewInit() {
    this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
      worksheets: [
        {
          data: [
            ['=CHART("pie", A4:A6, B4:B6)'],
            [],
            ["Proposals", "Votes (%)"],
            ["Proposal 1", 0.36],
            ["Proposal 2", 0.29],
            ["Proposal 3", 0.35]
          ],
          minDimensions: [6, 1],
          mergeCells: {
            'A1': [6, 1]
          },
          defaultColWidth: "60px",
          columns: [{ width: 100 }, { width: 100 }],
          rows: { 0: { height: 380 } }
        }
      ]
    });
  }
}

See examples: Working Examples Using Chart.js

Main Form

CHART(Type, Labels, Datasets, Options)

Arguments:

Argument	Description
`Type`	A string specifying the chart type, such as "line", "pie", "bar", or any other chart type supported by Chart.js.
`Labels`	The chart labels. Must be a single row or single column range.
`Datasets`	The chart datasets. It can be a range, an object (whose properties are explained later) or a list made up of these two.
`Options`	Chart options. Its properties are explained further below.

Property mapping

Both the dataset configuration object and the options object have property mapping that enables their use without requiring knowledge of Chart.js settings. If this functionality is not useful for your case, use the disableJssSimplification: TRUE setting on the object where you want to disable the mapping.

By disabling property mapping for an object, that object receives minimal treatment before being passed to Chart.js. This object must therefore follow the Chart.js v4.4.0 documentation.

Note: Even with this option enabled, it's still worth reading about the data property of the dataset configuration object, as it provides a good example of how cell coordinates are replaced by their values, which occurs regardless of property mapping.

Dataset properties

Property	Description	Example
`data`	A range enclosed in parentheses or a list of cell names.	`"data": (A1:A4)` `"data": [A1, A2, A3, A4]`
`label`	The label for this dataset	`"label": "Service A"`
`color`	The color(s) used to represent the dataset. It can be a single color or a list of colors. The default value is defined by the PALETTE formula.	`"color": "#D05D5B"` `"color": ["#C1D37F","#95A3B3","#995D81"]`

Options properties

Property	Description	Example
`title`	The title of the chart. If provided, it must be text.	`"title": "My First Chart"`
`titleAxesX`	Chart x-axis title. If provided, it must be text.	`"titleAxesX": "Month"`
`titleAxesY`	Chart y-axis title. If provided, it must be text.	`"titleAxesY": "Units"`
`legend`	Specifies whether dataset labels should be displayed. If provided, it must be boolean.	`"legend": FALSE`
`legendPosition`	The position on the chart where dataset labels should be displayed. Accepted values are: "top", "left", "bottom", or "right".	`"legendPosition": "left"`
`minAxesY`	Suggested minimum y-axis value. If provided, it must be a number.	`"minAxesY": -5`
`maxAxesY`	Suggested maximum y-axis value. If provided, it must be a number.	`"maxAxesY": 50`
`gridLineX`	Specifies whether the chart's x-axis separators should be displayed. If provided, it must be a boolean.	`"gridLineX": FALSE`
`gridLineY`	Specifies whether the chart's y-axis separators should be displayed. If provided, it must be a boolean.	`"gridLineY": FALSE`

Sparkline Form

CHART("sparkline", Datasets, Type)

This form creates smaller charts, omitting most chart information except the drawing itself. This form does not allow the use of the disableJssSimplification property on its objects.

Arguments:

Argument	Description
`Datasets`	The chart datasets. It can be a range, an object (like the main form dataset object, but only accepts the "data" and "color" properties) or a list made up of these two.
`Type`	The chart type. A string specifying the type, such as "line", "pie", "bar", or any other chart type supported by Chart.js. If omitted, the default value is "line".

Edit this page on GitHub