How to create charts from SharePoint lists with Power Apps and Plumsail Forms

In this article:

• What is a SharePoint list and why use charts?
• Create chart from SharePoint list in Power Apps
• Create chart from SharePoint list with Plumsail Forms
• Try Plumsail Forms

What is a SharePoint list and why use charts?

SharePoint List is a container that consists of all necessary data in a structured manner. It is a collection of data items called list items. Lists help manage and track information like events, survey results, announcements, etc.

In SharePoint, a list contains columns and rows where columns define the metadata, and rows represent the data. Lists in SharePoint can be easily added, edited, deleted, and viewed.

Charts are an easy way to visualize data. We'll show different approaches you can use within SharePoint forms to visualize data from any lists – using Microsoft's Power Apps platform and Plumsail's alternative.

We'll use the same list in both cases, showcasing example orders that your company might have to fulfill:

Many columns here are simple Single Line text, but we're also using a Choice column for Department, a Person column for Manager, and a Lookup column for Office.

Create chart from SharePoint list in Power Apps

Technically, adding Charts to Power Apps is very easy. When customizing a SharePoint form, first make sure that the list on which you want to base your chart is present in Data Sources. If you want to base your chart on the currently customized list, it should already be available, but if it's a different list, you can add a Data Source, go to the Data tab → click Add data → search for SharePoint:

Then choose SharePoint site, and search for the SharePoint list you want to use, in this case Orders:

Once added, the list will appear in Data sources:

Column chart in Power Apps

Now, you can find a place within the limited confines of the form where you want to place the chart, click Insert → Charts and select the chart type, we'll start with the Column Chart:

Once the chart is added, you can move it and adjust on your form, along with its title and legend, and for all charts you need to select Items → SharePoint list that we've added as Data source:

In the Advanced section of the chart configuration, you can select which columns from the list to use in your chart:

Line Chart in Power Apps

With the Line Chart, we can experiment with the number of series, comparing Quantity and Price difference between orders:

Pie Chart in Power Apps

A Pie Chart can be used to analyze the Price paid by each client, and see who's paying more:

While these are great and easy to setup – there are a few serious drawbacks, some of you might've noticed already:

1. Not all field types are supported – our Choice, Person and Lookup columns cannot be selected for charts.
2. Limited customization options – while there are some things you can change about charts like colors and fonts, a lot of their style is locked from editing.
3. No List View presentation on the form – if you want to include the list view you visualize, that's not possible.
4. Limited options when it comes to chart types, unless you're using a Power BI tile – only Column, Line and Pie.

Power Apps chart from SharePoint list Choice field

While the default charts offer no option to select Choice field as a data source for the Power Apps charts, there are some workarounds with advanced formulas to make Choice fields work as an option:

Stacked bar charts and other types of charts in Power Apps

Feedback from users of Power Apps seems to indicate that other types of charts are highly requested, but they haven't been added yet, only in integration with Power BI. The Stacked Bar chart has been in ideas for over 5 years.

We'll work around all these limitations with the alternative option – Plumsail Forms and Chart.js

Create chart from SharePoint list with Plumsail Forms

Plumsail Forms is a product that allows customization of SharePoint Forms in Modern UI with many new controls, such as Data Table, allowing data input in table format, or List or Library control, which allows you to publish related lists or document libraries directly on the form:

We'll use Chart.js library to visualize data on the forms in a similar way to Power Apps, with many more options:

1. List or Library control makes it easy to show items and retrieve their values on the form.
2. All column types are supported, visualize data however you see fit and dynamically update charts.
3. Full customization of charts with many more options: bubble, donat, radar, scatter and other types.

Once you've configured a List or Library control on the form, you just need to add a canvas element to an HTML control where the chart will appear:

<canvas id='myChart'></canvas>

You will then need to configure the chart with JavaScript, for example, the following customization will allow it to read data from List or Library control and it will build a bar/column chart with total price based on the Department choice field:

requirejs.config({
    paths: {
        chart: 'https://cdn.jsdelivr.net/npm/chart',
    },
});

const containerId = 'myChart';
const listControlName = 'OrdersTable';
const valueField = 'Price.'; // Currency value without currency sign is stored in the '{FieldName}.' field.
const groupField = 'Department';

function getGroups() {
    const groups = fd
        .control(listControlName)
        .widget.dataItems()
        .map((v) => v[groupField]);
    return [...new Set(groups)];
}

function getValues() {
    const groups = getGroups();
    const items = fd.control(listControlName).widget.dataItems();
    const result = [];

    for (let i = 0; i < groups.length; i++) {
        result[i] = items.reduce((sum, v) => (v[groupField] == groups[i] ? sum + parseFloat(v[valueField]) : sum), 0);
    }

    return result;
}

fd.spRendered(function () {
    require(['chart'], function (Chart) {
        fd.control(listControlName).ready(function () {
            const chart = new Chart(document.getElementById(containerId), {
                type: 'bar',
                data: {
                    labels: getGroups(),
                    datasets: [
                        {
                            label: 'Price',
                            data: getValues(),
                            borderWidth: 1,
                            backgroundColor: ['rgba(76, 255, 0, 0.3)', 'rgba(0, 148, 255, 0.3)', 'rgba(0, 255, 255, 0.3)'],
                        },
                    ],
                },
                options: {
                    scales: {
                        y: {
                            beginAtZero: true,
                        },
                    },
                },
            });

            fd.control(listControlName).$on('change', function () {
                setTimeout(function () {
                    chart.data.labels = getGroups();
                    chart.data.datasets[0].data = getValues();
                    chart.update();
                }, 1000);
            });
        });
    });
});

It will even update in real time as the values in the List or Library control change:

This is just a basic example, but a much more in-depth customization is possible with more extensive code, and all fields can be included, even fields such as Lookup or Person to make a chart which will work for your company and your form requirements.

Try Plumsail Forms

With Plumsail Forms for SharePoint, you can add data tables or related lists to any form, and even connect lists from different sites together, and with Chart.js you can visualize this data with graphs. Many other controls are available, and you can add any other resources that you might need.

If you'd like to try it out and see if it works for your needs, get started with 30 days free trial of Plumsail Forms for SharePoint Online by following the installation instruction in our documentation, which has a video walkthrough.

If you're using SharePoint 2019 or Subscription Edition, follow the SharePoint On-Premises installation guide.