This repository contains files and information required to sync the tasks from Microsoft Planner to an Excel file stored in SharePoint, which can then be read by Power BI; so in simple terms it provides an automatic sync between all Planner plans and Power BI. The flow has been updated a couple of times and so you need to watch both the original video @ https://youtu.be/-U1Nnj95VMo and then follow it up with https://youtu.be/82aYZBKiwoA
The repository contains two versions of the PowerAutomate flow.
The original flow is available in this zip - ExportPlannertaskstoExcel-original.zip and the flow map is shown in Flow map.pdf if you need to build it from scratch.
The latest flow is avaiable in this zip - ExportPlannertaskstoExcel.zip and the flow map is shown in the Flow Map with Office Scripts.pdf. As suggested, this flow also uses the Office Scripts.zip file which contains the office scripts used to delete rows from the tables. Anecdotally the flow with Office Scripts is more stable and runs faster.
The Excel file and .pbit file remain the same regardless of which flow you use.
The following files are included in this github
- An exported Flow package that copies the Planner data into an Excel file.
- An Excel file that holds the Planner data.
- A Power BI template (.pbit) file that visualizes the Planner data.
- The 6 office script files that are used to empty the tables (latest version only).
The following outlines the deployment and configuration process to deploying the solution, and assumes you are using the flow with Office Scripts.
- Associate each Plan with a Team in Microsoft Teams
- Upload the Excel file into a SharePoint document library
- Import the Flow package into Power Automate
- Add the scripts to the automate tab in Excel online, and test that they work
- Update the flow to point to the SharePoint document library.
- Test, test, test, until the flow runs successfully!!
- Open the .pbit file and enter the web URL of the Excel file
Power Automate queries every Team in turn and returns the Planner plans associated with the Team; therefore if no Team exists for a Plan then a Team must be created and associated with the existing Office365 group that was created as part of the Plan creation. If a team already exists, and the Plan is being created, associate the Plan with the relevant team.
Figure 1 - Create a Team and associate it with the existing Group created when the Planner Plan was created
Figure 2 - Create a new Plan against and existing Group
Upload the Excel file into a SharePoint folder. It doesn't matter what the folder or file name is, but make sure you remember where you have saved the file as this will need to be entered multiple times in the Flow. Please leave the table names the same, as Power BI uses hard coded table names as part of the query.
Create 6 new scripts against the Excel file by copying and pasting the code from each script file into the scripts code editor in Excel.
Open Power Automate and import the Flow package. The Flow is a cloud flow that is set to run each night at 11:00pm.
You will need to ensure you configure and select connections for the following resource types.
| Connection Name |
|---|
| Microsoft Teams Connection |
| Excel Online (Business) Connection |
| Planner Connection |
| Office 365 Groups Connection |
If you cannot see a relevant connection, then open the Data > Connections tab, and click the button to add a new connection at the top of the page.
If get the following error when you import the flow then you have more configuration to perform.
Please see this video on YouTub to walk you through the solution - https://www.youtube.com/watch?v=oWyDJmDDFfU
Alternatively please review the relevant flow map.pdf file included in the repository to manually create the flow.
The Flow has 6 major sections to it. They comprise of sections to update the following tables in the Excel file, and each section is broadly similar.
| Table Name | Description |
|---|---|
| Plan_tbl | Holds the Plan Id and Plan Name |
| Bucket_tbl | Holds the Plan Id, Bucket Id and Bucket Name |
| Task_tbl | Holds the Task Id, Plan Id, Bucket Id and many other task fields |
| Assignments_tbl | Holds the Task Id, Plan Id and UserAssignmentId |
| User_tbl | Holds the User Id and User Name |
| Checklist_tbl | Holds the Task Id and the Checklist item(s) |
Each table is updated as part of the Flow. The following steps are applied in each section
- Every row in the current table is deleted (via the office scripts)
- A list of Plans is retrieved for each group associated with each team in Microsoft Teams
- The relevant data for each plan is written into the relevant table, with the exception of the User_tbl which lists the group members for each group, and therefore does not query Planner at all.
Once the Flow has been imported, edit the Flow so that each section points to the location where the Excel file is located in SharePoint. Note that this needs to be done at least three times for each section, one to list all the rows in the table, once to remove the rows from the table, and once to add rows to the table. Specifically the following items need to be set.
| Entity | Description |
|---|---|
| Location | This is the location in SharePoint, eg SharePoint Site – <site name> |
| Document Library | A drop down list. Choose the library where the file is located |
| File | A drop down list. Choose the filename, eg /Planner tasks from flow.xlsx |
| Table | The relevant table for the section. eg Task_tbl, Plan_tbl etc |
The following image gives and example of the UI in Flow for configurating the Excel details.
Figure 3 - Update the details for the Excel file
Note that the specific details for matching fields for each entity in Planner to the relevant columns in each table do not have to be configured, and indeed more columns exist in the tables than are actually configured.
Open the .pbit file and enter the web URL of the Excel file. This can be found by opening the Excel file from the SharePoint site in the desktop app (ie in Excel) and clicking on File and then Info. Click on the button that says Copy Path and then paste in into the ExcelFileWebURL parameter when the Power BI template loads. Remove the ?web=1 from the end of the URL, leaving you with a URL in the following format
https://tenantname/sitename/foldername/filename.xlsx
Publish the Power BI file and then set up a scheduled refresh on the dataset. My recommendation is to set this to run two hours after the schedule for the Power Automate Flow, especially if you have a large number of tasks. The default time for the Flow is 11:00pm, so I recommend setting the Power BI Scheduled Refresh to 1:00am, or of course, you could modify the flow to refresh the Power BI file as the final step.