If you want to publish data stored in your Office 365 environment to your WordPress site, wpDataTables combined with Power Automate makes it very easy to do. The data source in Office 365 can be anything accessible to Power Automate (e.g. SharePoint list, Dataverse, Excel, SQL Server).
Check out the example below, which is being generated from a SharePoint list and nicely demonstrates some of the default functionality of wpDataTables.
Employees
wpDataTables can get table data from many different data sources. One possible source is a URL that contains a JSON array. Power Automate makes it simple to publish JSON data to drive the table, using the action When a HTTP request is received.
There are two steps required to generating a table like the example above:
- Generate the source JSON data for the table.
- Create a page or a post in Wordpress that contains a wpDataTable.
Generating the JSON for the Table
There are two ways to generate the source data for the table, both methods have pros and cons.
The First Method uses the trigger action When a HTTP request is received.
- Pros
- Provides always up to date data.
- Very easy to configure the flow to provide the data.
- Cons
- Requires Power Automate Premium
- Request limits in Power Platform could prevent execution on a busy site.
The Second Method uses a recurrence flow to copy data to the WordPress server using the SSH Connector.
- Pros
- Does not require Power Automate Premium
- Does not expose your HTTP action to the client.
- Number of page views will not impact your Power Automate Request limits.
- Faster performance for the client.
- Cons
- Data is refreshed on a schedule, so isn’t live data.
- Requires you to have SSH access to your WordPress host.
Generate data using the HTTP Method
Here is the flow that generates the HTTP Response, it consists of only a few steps:
As you can see, it consists of only a few actions.
- The HTTP Trigger, which must have the method set to GET.
- A Get Items action, to retrieve the data from SharePoint
- A Select action to reshape the data and remove any columns that are not required.
- Finally a response action to return the results to the browser that requested the record set. It is important to set the Content-Type to application/json.
The URL for the HTTP Trigger is generated after the flow is saved. Click this link to see the JSON data generated for the example table in this post. Once the URL has been generated, it can simply be added as the data source in the wpDataTables table.
Generate Data Using the SSH Method
The core of this flow is the same as the HTTP method, but the trigger and method of delivery are different. Here is the flow:
As you can see from the image above, the flow is set to run every 30 minutes, which means it will execute 48 times per day. The HTTP version of the same flow would execute every time someone visits the page.
The create file action at the end of the flow connects to the the WordPress server and saves the output of the select action to a static file called employees.json. There are two advantages to storing a static file like this:
- No need for the browser client to lookup another host.
- The data is static so the response is near instant.
So if your list was a restaurant menu for example, then a lag in the data would be fine. But if it was displaying stock levels of fast moving products, then you’d use the live method.
Create the Data Table
Creating the data table is super easy and you can easily follow the video demo from the wpDataTables website. wpDataTables has loads of different display options and makes it very easy to expose data from your Office 365 environment when combined with Power Automate.
Leave a Reply