Tachytelic.net

In this post I am going to demonstrate how to collect data from a SharePoint list using Power Automate and publish it in a jQuery grid on any website. Many businesses store data in data sources that are easily accessible by Power Automate that might be useful to share externally. For example, stock levels, upcoming events, price lists etc.

This is easy to do with a small amount of code and saves you having to create guest access to your Office 365 environment. Power Automate can compile a JSON array as a data source to a jQuery grid.

Take a look at the following SharePoint list:

Using the above data, the parts list is going to be exposed to the public web, but the cost and margin columns will be excluded, and the list will be filtered so that only items where “Show on Web” is set to yes.

This data source could be anything that is accessible to Flow:

• Excel Data
• SQL Server
• CDS
• Oracle
• MySQL
• etc

The table below is a live working example of what this flow will demonstrate, as you can see:

• It’s very fast and easy to browse.
• Searching is virtually instant.
• It’s possible to group items easily.
• Filtering is simple.

How to build the flow

The flow is really easy to build and requires only a few steps, the trigger action is “When a HTTP request is received”.

Filter the data

The first action in the flow is the “Get Items” from SharePoint. This has an oData filter query so that it only retrieves items where “Show on Web?” is set to yes:

Reduce the JSON data from Get Items

The JSON returned by SharePoint is quite heavy, some of the the columns are not required and none of the metadata is needed for the grid. So a select action is used to reshape the data and create new column names:

You can really see the value of this when you compare one record from the “Get Items” action to one record from the select.

A single record from the select action looks like this:

  {
    "Part": "716/27600",
    "Description": "FUEL SENDER UNIT - P12",
    "Price": 67.75,
    "Stock": 25,
    "Product Group": "Standard Product"
  }

As you can see, it contains only the information required to build a grid in the browser.

Send the JSON response to the browser

The final step is just a HTTP response configured in the most basic way. It simply sends the output of the select action.

The entire flow consists of four steps and looks like this (Click to Zoom):

Build the Browser page for the grid

The table above was built using the DevExpress DataGrid, but the method is very similar for many jQuery based datagrid components. The amount of code is minimal.

The html for the grid consists only of a couple of DIVs:

<div class="demo-container">
  <div id="gridContainer"></div>
</div>

Everything else is done from jQuery. Three lines in the head section:

<link rel="stylesheet" href="https://cdn3.devexpress.com/jslib/20.2.3/css/dx.common.css">
<link rel="stylesheet" href="https://cdn3.devexpress.com/jslib/20.2.3/css/dx.light.css">
<script type="text/javascript" src="https://cdn3.devexpress.com/jslib/20.2.3/js/dx.all.js"></script>

And one script on the page that pulls the data from Power Automate:

<script>
jQuery(function(){
    jQuery("#gridContainer").dxDataGrid({
        dataSource: "https://prod-03.westeurope.logic.azure.com:443/workflows/...",
        columns: ["Part", "Description", "Price", "Stock", "Product Group"],
        showBorders: true,
		paging: {
            pageSize: 10
        },
        pager: {
            showPageSizeSelector: true,
            allowedPageSizes: [10, 25, 50, 100, 200]
        },
        remoteOperations: false,
        searchPanel: {
            visible: true,
            highlightCaseSensitive: true
        },
        groupPanel: { visible: true },
        grouping: {
            autoExpandAll: false
        },
		filterRow: { visible: true },
        allowColumnReordering: true,
        rowAlternationEnabled: true
    });
});
</script>

The datagrid component has many more options that I did not configure, and could have been setup with even less code than I used.

This is an easy to implement, but quite powerful use for Power Automate. If you only want to provide a small subset of data to users outside of your organisation then you don’t need to setup guest access to your environment, so it really is an easy solution.

Something to consider is that if you are going to do this on a webpage that gets many visitors per day then you are going to go through flow runs quite quickly. An alternative would be to run the flow on a schedule and output the JSON to a file which is publicly reachable. This will produce a faster response to the browser and save your flow usage.

If you would like to copy the source code, go to this demo page and view source in your browser.