First off thanks Ben for putting this together. The biggest complaint I get about planner is no top down reporting and this has gotten me off to a good start solving that issue. Secondly, for anyone running into issues with the scripts running after your initial population of the data I recommend making the following 2 adjustments: 1. In your flow, move the Office scripts out of the parallel runs and run them sequentially before hand. This will avoid issues with the scripts all trying to open the same file at the same time. 2. Update the office scripts and replace the final command "delete_tbl.deleteRowsAt(0, numberOfRows);" with "delete_tbl.getRangeBetweenHeaderAndTotal().delete(0);" - This resolved the timeouts I was facing when the tables where longer then a few hundred rows.
Thanks Ben. I recommend to you develop the flow in a sequential way because with branches the flow fails. Sometimes the scripts fail with the error "Conflict".
Is it possible for this to only run to extract the tasks and checklists from only 1 planner from 1 group/team? At the moment mine is pulling all the tasks from all my groups and teams and runs for an unnecessarily long time. I'm very new to PowerBI and PowerAutomate so please excuse my lack of knowledge if this is a simple question.
@@BenHoward_PowerBI Thanks! I can't get the Office Scripts to work, is it simple enough for me to set up the single team flow as per the standard initial flow in your github?
Yes, then you will have to use the process for removing all the rows from the excel sheets as per the standard initial flow. Because you are only synching a single team/planner plan then you shouldn't see any performance issues (assuming you don't have thousands of tasks!)
This series is really amazing and helpful. I used this approach and it works well, but I did run into an issue that I thought I would share in case you redo the series down the road. I have Planners with thousands of tasks included. As a result, the delete rows function doesn't seem to finish the job. I have to run it around six times to delete everything. I thought increasing the pagination to 10,000 might help, but it didn't. Also, a few months ago Microsoft changed the recommended approach for linking to Excel data and unless you do it their way, you can't establish a refresh schedule. I think I shared a way to pull the data from Planner a different way on one of the other videos covering this topic. I tried that, and couldn't seem to locate all of the same data your approach grabs. Maybe, you'll have better luck making that work as I think it might be easier on the flow.
Presumably you are using the scripts to delete the rows - this is Microsoft's recommended approach as per this video. I don't understand the issue for refreshing the data re linking to Excel; I haven't seen any documentation from Microsoft on this.
@@BenHoward_PowerBI script is disabled for most of our products, so I went with the approach through power automate for removing rows. Script would be better, though. It could be a GCC issue, but refresh isn't possible for excel files added a certain way. You need to add them using the SharePoint folder approach and then navigate to the file and table. If you don't use that approach, scheduled refresh isn't possible. Not sure about documentation, but the Microsoft rep we work with told us about the change and now we are all redesigning our excel linked reports.
@@trstrean - thanks for the info re refreshing directly from Excel, I'll check this out with the product group - it would seem a backward step to me having to filter directly for the correct file in the sharepoint folder though... more steps in PowerQuery and more complex - sigh. Re the scripts, it could be a gcc thing. If you cannot use them then may set the Top Count to 1000000 in the advanced section for each of the "list rows present in the xx table" and see if that resolves the timeout issue. Also, it might be worth setting this up to run sequentially rather than in parallel, some users have seen a perf increase that way (though not me).
@@BenHoward_PowerBI I was thinking about sequential also. It might make it possible to increase the number of deletions/additions that way also. The max number of simultaneous actions is 100, which works fine with the default auto setting. I had wanted to optimize it a bit going to 50 for tables of thousands of rows. In parallel, it quickly surpasses that 100 cap early in the process. Sequential should allow me to get past the bottleneck and speed up the overall process. Perhaps a little bit of sequential and parallel is the best approach to optimize things. I agree with your comment on the change. It does make it a little easier adding multiple tables because you can just duplicate it and navigate to different tables.
Hi Ben, thank you very much for this tutorial. I’m currently using this in my work and it works perfect. I have a question… Is it possible to get the priority for each task? How hard would it be? Regards from Chile!!
Hello Ben, first of all thank you very much for this solution, I like it very much. I am exploring powerBI and started from web version, I have some troubles with uploading your .pbit template. Could I maybe ask you to share .pbix file which is suitable for uploading to powerBI service? Thank you in advance!
@@BenHoward_PowerBI , thank you for your quick response, I was able to find a person with access to Power Bi desktop, so we did necessary implementations. But now I have a further question, how do you construct "Task URL" column in your data model? I did not find it to be exported during the flow
@@MaksimBlekhshtein - the task URL is included in the .pbit that you can download from GitHub, but for ref the URL is constructed in Power Query. Here is the PQ code that does it. // Add in the task URL #"Added Custom" = Table.AddColumn(#"Changed Type", "Task URL", each "tasks.office.com/"&Lines.ToText(Tenant)&".onmicrosoft.com/en-us/Home/Task/"&[Task ID]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Task URL", type text}}), #"Cleaned Text" = Table.TransformColumns(#"Changed Type1",{{"Task URL", Text.Clean, type text}})
Hey Ben, thanks for the tutorial and downloadable files, really great tutorial and clear instructions - I am currently testing the 'sequential' version of your flow, but it is currently up to 16hrs+. Does this pull all the data from planner/teams as whole and then run smaller scheduled flows to top up the excel spreadsheet tables (so to speak?), or will this run everyday and pull everything (again, another potential 16hrs+)?
@BenHoward_PowerBI Hi Ben, Thanks again for the awesome serires. When runnning the flow for single team +scripts, it fails with the error: "Action 'Remove_existing_Bucket_rows' failed We were unable to run the script. Please try again. Your file was not saved because we could not merge your changes with changes from someone else." This happens randomly on the different rows (buckets, pPlans, etc.) I changed the delay on controls to 5 and 1 minute, but that didn't help. Any idea what could cause this or how to get around it? P.S. The Excel file is in SharePoint Online.
Hi, are you synching this script offline via onedrive too? If so, worth ensuring it is fully synced with one drive, or even better, don't keep it offline.
I've put the scripts in OneDrive because the Flow editor couldn't detect them whare you need to pick the scrip file in the control if they were in SharePoint Online. Not sure why. Not ideal, but hey! I've shared the OneDrive folder with the appropriate team/group, and I don't have it on my local machine.
@@feniashubby - this is likely to be your issue, multiple synching of one-drive files. No-one needs to be able to access the excel file except the user who is owning and running the flow. Also re the scripts, I created them within the Excel online app when editing the file, that way Excel looks after them. You can simply paste the scripts into Excel when you hit "new script".
Hi Ben, in Apply to each 11 (from branch User), I got an time out error, because the table was populated with a lot of employees (from all the groups I am in. I change the ID to an specific team ID, and I'm getting people from that team, but every user is duplicated several times because they are in different groups, and I get all groups of each user. How could I reduce the list of user to only in the groups ID I typed?
Hi, I am not sure you can reduce the duplication at this stage of the process; however the duplicates are removed by Power Query to just give you a single list of resources.
Thank you so much Ben. In your previous version, we would point out to the Sharepoint path and it would compile multiple planners (from xls files) into the report. With this version, we have to put the path of a single excel file. Is there a way to get multiple planners into one report using this new version? Thanks.
Love this flow and thank you for sharing!! I do have a question though. I am only needing to capture one team and not all of them within my organization. How can I accomplish this? If I run the flow as its built it times out due to the number of teams and data. Suggestions????
Hi Travis, glad you like it. You could modify the script just to pick up a single team, and then list the plans for that team. I've tested it and it works. As I'm 1/2 way through this now I'll publish it to github when it's finished (though this won't be until next week at the earliest). If you just want a single plan and are happy to manually export the file then you can watch this video here - ruclips.net/video/79AFLGoS1yo/видео.html
@@travisharker4536 - I have this working, just not recorded the video yet. The .pdf map for this is in github.com/ben-howard/Temp , and it uses the same Excel file as the previous version.
Hi, I have been progressing this... Can I just ask, why is the delay for scripts ABOVE the delete rows, i.e. after the schedule. Surely (am a newbie) the delay should be after the delete rows, as it take sometime for the rows to be deleted? Therefore, could you explain the rationale/logic behind the delay after the schedule? Trying to make sure I am understanding how delays work as thought I did do, until I saw this.....
Hi Ianf1968, I assume you're using the flow which does not use the excel scripts to remove the rows, rather it uses the "delete row" action in Excel. The delay is because I was seeing issues running the branches in a pure parallel mode, therefore I decided to delay the beginning of each branch. Remember there are four versions of the script in the github repository, each has a slightly different nuance.
Hi am using the scripts with various results. All work in excel ok, all good, but getting random results in corporate environment. I have been increasing delay to see if helps.
Hi Ben, I have managed to get this to work for all team planners but when I try single team I am getting an issue. In the List group members I can’t find the particular team Id I’m after? There’s a list but the one I need isn’t there??
I'm having issues with the scripts, I have the automate feature in Excel but no scripts are appearing, could you share the source code please for all 6 scripts?
Here you are. You need a script for each table, you just need to change the table name for each script. The one below references the "Task_tbl" function main(workbook: ExcelScript.Workbook) { //Delete all rows in Tasks table. let delete_tbl = workbook.getTable("Task_tbl"); // Delete all row(s) at index 0 from table Task_tbl let numberOfRows = delete_tbl.getRowCount(); if (numberOfRows > 0) delete_tbl.deleteRowsAt(0, numberOfRows); }
Hello Ben, great videos thanks a lot! I have a problem. I uploaded the scripts into Excel but when I am in Power Automate it shows me no options to choose in the "Scripts" field. Is that happening because I do not have the rights to run scripts or can you think of another reason? Thanks a lot!
@@BenHoward_PowerBI I found the solution. I only imported the skripts into excel and it did not work. Then I copied the skripts and created new ones where I pasted the code. The "new" skripts with the exact same code as your imported ones then showed up in Power Automate.
@@BenHoward_PowerBI Now that everything is setup, Excel takes out the information of every Planner we have in the company. Do you know what could be the solution to that?
@@BenHoward_PowerBI I did the filtering in Power BI thanks! But the flow only worked well once and then it gives me the error "Bad request" with the status 400 and the following steps have the message "Action failed". Do you might know what could be the problem? Thank you!
Hey Ben. Thank you for this. It has been a wonderful reference as I try to imitate. The error i'm stumped on - when a planner task is assigned to multiple people, you get duplicate task IDs in the data source for the Task_tbl. The way the data model is setup, the error i'm getting is: TaskID contains duplicate value this is not allowed for columns on the one side of a many-to-one relationship or columns that are used as the primary key. Now I can get the report to render though with errors by removing taskID duplicates but I'm doubting that's the correct solution if I want accurate Assignee data.
Hi, It works fine for me, and you should not get duplicate task rows. The tasks branch loops through each plan, and for each plan, writes each task to to the tasks_tbl, once only. The assignments branch handles the assignments, lists each plan, and then for each task in each plan, lists each assignment, and writes those to the assignments table. I have multiple assignments in my planner tasks. Please review the relevant flow map at github.com/ben-howard/Planner-PowerBI-with-PowerAutomate/blob/main/Parallel%20with%20Office%20Scripts/Flow%20Map%20with%20Office%20Scripts.pdf to verify your flow is set up correctly.
@@BenHoward_PowerBI Ben, first I'd like to thank you for teh fantastic series and content. This could prrove to be really useflul in our environment. I am having the same error where duplicates remain in the workbook after running the Flow. I am using a modified version of the "Single Team" without Excel scripts, but rather the Flow actioin to remove dupes. When I try to get the data in Power BI, the error pops up, and the subsequent table do not populate. Everything else seems to be running fine. Here's the error that BI throws: Task_tbl Column 'Task ID' in Table 'Task_tbl' contains a duplicate value 'vY-u42HzmkaD8YHcA8JbhWUAJQ7i' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
@@feniashubby - If you are running this without scripts then you seriously need to consider the scripts option, it is a much faster and reliable way to remove the existing rows. Without being able to consistently remove the rows, you will get the error you are seeing in Power BI. To troubleshoot this, I would manually remove all the rows from the table, and then run the flow, and watch the table populate in Excel. Once it has finished successfully, run it again, watch the table depopulate and then populate. Then link it into Power BI.
@@BenHoward_PowerBI I discovered what my problem was. I had uploaded the script files to the SPO library then within Excel I connected to them. That is the issue. Once I "Created" the scripts within Excel Online and saved them, they started appearing in Flow. That only works from my OneDrive. As soon as I move the scripits to SHarePoint Online, they stop wroking. 😞 *********** The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'BadRequest'. This may indicate invalid input parameters. Error response: { "status": 400, "message": "Unable to parse script reference. clientRequestId: 97c5f3bc-ef66-45cf-bde3-51cd8d1a0c8d", "error": { "message": "Unable to parse script reference." }, "source": "excelonline-scus.azconn-scus-001.p.azurewebsites.net" }
Good Day Ben, Thank you for this updated version of the first video. Please, I wanted to ask, When I tried out this solution the dynamic content for each branch didn't automatically fill up as it did in the video with "Remove_Tasks", and "Remove_Assignments. I don't know why this is happening. I would really appreciate it if you could assist with explaining why this is happening.
Hi, it's probably because you've not configured the branch to use the right table? Presumably the content is being removed correctly when you run the scripts manually in Excel?
Hi I am getting this error please help The dynamic operation request to API 'excelonlinebusiness' operation 'GetTable' failed with status code '429'. This may indicate invalid input parameters. Error response: { "statusCode": 429, "message": "Rate limit is exceeded. Try again in 1 seconds." }
Hi, when I download this from the Repo there is no Automate Tab in the excel, the scripts dont populate and doesnt allow me to enter it in manually. The Tables dont populate and cant type them in either, but it is populating the Documents folder drop down etc when adding in connections etc. i think its failing at connecting to the table in the excel for some reason. Could not retrieve values. The dynamic invocation request failed with error: { "status": 404, "message": "The resource could not be found. clientRequestId: 81ebfc6b-995f-4393-b270-b0be9ac0a09e serviceRequestId: 8c3fb2a4-d194-4f44-a619-70cf8f151948", "error": { "message": "The resource could not be found." } Thanks!
Okay, if there is no Automate Tab then the scripts feature is not available to you, worth checking with your M365 admin to see if it is part of your subscription, or whether it needs turning on. If you want a copy of the old version please contact me directly via email and I will send you one.
Solution: i created the 6 scripts in the excel file and ran them to test before saving. I then checked the connection info in each of the Excel steps, since my company makes us change our passwords often the default selection was the wrong one, so changed that and the tbl names and script names came up instantly. saved and it worked. woohoo!
Hello Thank you for your videos. This is very helpful in building out the process. I downloaded the new files and followed along with this video and see the Scripts are not in the file under scripts. Is there a way I can get the details to add these new scripts to the excel file.
Hi - you need to ensure Office Scripts are available. Assuming they are then here is the basis for the script. You just need to amend the "Table_tbl" value for each of the tables. function main(workbook: ExcelScript.Workbook) { //Delete all rows in Tasks table. let delete_tbl = workbook.getTable("Task_tbl"); // Delete all row(s) at index 0 from table Task_tbl let numberOfRows = delete_tbl.getRowCount(); if (numberOfRows > 0) delete_tbl.deleteRowsAt(0, numberOfRows); } Here's the details on how to turn on Office Scripts - docs.microsoft.com/en-US/microsoft-365/admin/manage/manage-office-scripts-settings?view=o365-worldwide
Thank you very much, this is very helpful! Can you help me with this error?, this happened when I try to run the flow again and occurs when the script is run to remove rows . "We were unable to run the script. Please try again. Your file was not saved because we could not merge your changes with changes from someone else." It only happens sometimes, but I don't know what the problem is. I have two users adding task in the planner I don't know if that's the problem.
Had this error. I had the planner task excel open in my desktop excel app. I closed it and it was resolved. If you want to see the real-time updates of the excel, you need to open it from sharepoint.
Hi Ben, the public view doesn't appear to be available via the link in the description? Is there a new link to view this? I'd like to have a play before suggesting this to my team
Hi, please save the Excel file to SharePoint, open it in the browser, and check under the Automate Tab. The scripts are there for me (I have just tested it), but in case you need the code, it is as follows. function main(workbook: ExcelScript.Workbook) { //Delete all rows in Tasks table. let delete_tbl = workbook.getTable("Task_tbl"); // Delete all row(s) at index 0 from table Task_tbl let numberOfRows = delete_tbl.getRowCount(); if (numberOfRows > 0) delete_tbl.deleteRowsAt(0, numberOfRows); }
Hi Ben, any suggestions where we have 27+ plans so the flow is taking a long time. I also removed the parallel flows and made it concurrent as it constantly errored out..
@BenHoward_PowerBI thanks for your quick reply. Usually 22 hours, I've seen it running for 1 day 22 hrs when I turned off the daily refresh to see what happens
@@alannahsteevens5102 - wow, that is soooo long... I'm trying to work on soln that uses the graph API but don't have much spare time at the moment - it *might* be quicker but who knows.
@@BenHoward_PowerBI I have used the graph API and it still times out. I think you're correct that it's the call limits per license, I turned off all other flows in our organization that weren't sensitive and it ran successfully taking 3 hrs again
I guess you have a lot of data, approx how many planner plans do you have and how many tasks? If you let it run 7 times or so you'll have enough info in the process advisor.
@@BenHoward_PowerBI I tried to do the one for Single site but it asked for Script location so was a bit stumped at this one as was unsure where or how to get the script running
@@highlandwhite5401 - the dealing with the scripts occurs around 6 mins in the video, and adding the scripts to the Excel sheet @ around 7:38. Both these steps apply to the single team flow.
First off thanks Ben for putting this together. The biggest complaint I get about planner is no top down reporting and this has gotten me off to a good start solving that issue.
Secondly, for anyone running into issues with the scripts running after your initial population of the data I recommend making the following 2 adjustments:
1. In your flow, move the Office scripts out of the parallel runs and run them sequentially before hand. This will avoid issues with the scripts all trying to open the same file at the same time.
2. Update the office scripts and replace the final command "delete_tbl.deleteRowsAt(0, numberOfRows);" with "delete_tbl.getRangeBetweenHeaderAndTotal().delete(0);" - This resolved the timeouts I was facing when the tables where longer then a few hundred rows.
These are great comments, thanks Charles :) - I have updated the scripts in Git to reflect these changes.
@@BenHoward_PowerBI I've done the changes but still facing the flow timeout error
Many thanks Ben. Just a hint for other people, in our environment we found that the excel scripts perform better if run in sequence
Thanks Rob, that's a great tip which hopefully people will pick up through comments.
We're having the same problem - How did you get this to run in sequence did you set concurrency or did you drag the tasks around or something else?
Thanks Ben. I recommend to you develop the flow in a sequential way because with branches the flow fails. Sometimes the scripts fail with the error "Conflict".
Hi Victor, thanks for the feedback.
Is it possible for this to only run to extract the tasks and checklists from only 1 planner from 1 group/team? At the moment mine is pulling all the tasks from all my groups and teams and runs for an unnecessarily long time. I'm very new to PowerBI and PowerAutomate so please excuse my lack of knowledge if this is a simple question.
Hi, yes, check out this flow - github.com/ben-howard/Planner-PowerBI-with-PowerAutomate/tree/main/Single%20Team
@@BenHoward_PowerBI Thanks! I can't get the Office Scripts to work, is it simple enough for me to set up the single team flow as per the standard initial flow in your github?
@@jackscott4521 - is it that you cannot get the scripts to work, or you do not have access to scripts at all?
@@BenHoward_PowerBI I don't have access to scripts in my org
Yes, then you will have to use the process for removing all the rows from the excel sheets as per the standard initial flow. Because you are only synching a single team/planner plan then you shouldn't see any performance issues (assuming you don't have thousands of tasks!)
This series is really amazing and helpful. I used this approach and it works well, but I did run into an issue that I thought I would share in case you redo the series down the road. I have Planners with thousands of tasks included. As a result, the delete rows function doesn't seem to finish the job. I have to run it around six times to delete everything. I thought increasing the pagination to 10,000 might help, but it didn't. Also, a few months ago Microsoft changed the recommended approach for linking to Excel data and unless you do it their way, you can't establish a refresh schedule. I think I shared a way to pull the data from Planner a different way on one of the other videos covering this topic. I tried that, and couldn't seem to locate all of the same data your approach grabs. Maybe, you'll have better luck making that work as I think it might be easier on the flow.
Presumably you are using the scripts to delete the rows - this is Microsoft's recommended approach as per this video. I don't understand the issue for refreshing the data re linking to Excel; I haven't seen any documentation from Microsoft on this.
@@BenHoward_PowerBI script is disabled for most of our products, so I went with the approach through power automate for removing rows. Script would be better, though. It could be a GCC issue, but refresh isn't possible for excel files added a certain way. You need to add them using the SharePoint folder approach and then navigate to the file and table. If you don't use that approach, scheduled refresh isn't possible. Not sure about documentation, but the Microsoft rep we work with told us about the change and now we are all redesigning our excel linked reports.
@@trstrean - thanks for the info re refreshing directly from Excel, I'll check this out with the product group - it would seem a backward step to me having to filter directly for the correct file in the sharepoint folder though... more steps in PowerQuery and more complex - sigh. Re the scripts, it could be a gcc thing. If you cannot use them then may set the Top Count to 1000000 in the advanced section for each of the "list rows present in the xx table" and see if that resolves the timeout issue. Also, it might be worth setting this up to run sequentially rather than in parallel, some users have seen a perf increase that way (though not me).
@@BenHoward_PowerBI I was thinking about sequential also. It might make it possible to increase the number of deletions/additions that way also. The max number of simultaneous actions is 100, which works fine with the default auto setting. I had wanted to optimize it a bit going to 50 for tables of thousands of rows. In parallel, it quickly surpasses that 100 cap early in the process. Sequential should allow me to get past the bottleneck and speed up the overall process. Perhaps a little bit of sequential and parallel is the best approach to optimize things. I agree with your comment on the change. It does make it a little easier adding multiple tables because you can just duplicate it and navigate to different tables.
Hi Ben, thank you very much for this tutorial. I’m currently using this in my work and it works perfect. I have a question… Is it possible to get the priority for each task? How hard would it be? Regards from Chile!!
Unfortunately Microsoft do not expose the priority field in the relevant connector.
@@BenHoward_PowerBI Thank you Ben. I really appreciate your time :)
Hello Ben, first of all thank you very much for this solution, I like it very much.
I am exploring powerBI and started from web version, I have some troubles with uploading your .pbit template. Could I maybe ask you to share .pbix file which is suitable for uploading to powerBI service?
Thank you in advance!
Hi, for sure, can you send me a mail, you'll find my address in the .pbit file, or contact me via my website (applepark.co.uk)
@@BenHoward_PowerBI , thank you for your quick response, I was able to find a person with access to Power Bi desktop, so we did necessary implementations. But now I have a further question, how do you construct "Task URL" column in your data model? I did not find it to be exported during the flow
@@MaksimBlekhshtein - the task URL is included in the .pbit that you can download from GitHub, but for ref the URL is constructed in Power Query. Here is the PQ code that does it.
// Add in the task URL
#"Added Custom" = Table.AddColumn(#"Changed Type", "Task URL", each "tasks.office.com/"&Lines.ToText(Tenant)&".onmicrosoft.com/en-us/Home/Task/"&[Task ID]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Task URL", type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Changed Type1",{{"Task URL", Text.Clean, type text}})
Thank you @@BenHoward_PowerBI! I will try to find it in my environment.
Thanks for the update Ben :)
Hey Ben, thanks for the tutorial and downloadable files, really great tutorial and clear instructions - I am currently testing the 'sequential' version of your flow, but it is currently up to 16hrs+. Does this pull all the data from planner/teams as whole and then run smaller scheduled flows to top up the excel spreadsheet tables (so to speak?), or will this run everyday and pull everything (again, another potential 16hrs+)?
Hi, it re-runs entirely. The reason for this is that there is no way for me to find out which tasks are new or have changed in anyway.
@BenHoward_PowerBI
Hi Ben,
Thanks again for the awesome serires.
When runnning the flow for single team +scripts, it fails with the error:
"Action 'Remove_existing_Bucket_rows' failed
We were unable to run the script. Please try again.
Your file was not saved because we could not merge your changes with changes from someone else."
This happens randomly on the different rows (buckets, pPlans, etc.)
I changed the delay on controls to 5 and 1 minute, but that didn't help. Any idea what could cause this or how to get around it?
P.S.
The Excel file is in SharePoint Online.
Hi, are you synching this script offline via onedrive too? If so, worth ensuring it is fully synced with one drive, or even better, don't keep it offline.
I've put the scripts in OneDrive because the Flow editor couldn't detect them whare you need to pick the scrip file in the control if they were in SharePoint Online. Not sure why. Not ideal, but hey!
I've shared the OneDrive folder with the appropriate team/group, and I don't have it on my local machine.
@@feniashubby - this is likely to be your issue, multiple synching of one-drive files. No-one needs to be able to access the excel file except the user who is owning and running the flow. Also re the scripts, I created them within the Excel online app when editing the file, that way Excel looks after them. You can simply paste the scripts into Excel when you hit "new script".
Hi Ben, in Apply to each 11 (from branch User), I got an time out error, because the table was populated with a lot of employees (from all the groups I am in. I change the ID to an specific team ID, and I'm getting people from that team, but every user is duplicated several times because they are in different groups, and I get all groups of each user. How could I reduce the list of user to only in the groups ID I typed?
Hi, I am not sure you can reduce the duplication at this stage of the process; however the duplicates are removed by Power Query to just give you a single list of resources.
Thank you so much Ben. In your previous version, we would point out to the Sharepoint path and it would compile multiple planners (from xls files) into the report. With this version, we have to put the path of a single excel file. Is there a way to get multiple planners into one report using this new version? Thanks.
Hi, did you watch the video?
Love this flow and thank you for sharing!! I do have a question though. I am only needing to capture one team and not all of them within my organization. How can I accomplish this? If I run the flow as its built it times out due to the number of teams and data. Suggestions????
Hi Travis, glad you like it. You could modify the script just to pick up a single team, and then list the plans for that team. I've tested it and it works. As I'm 1/2 way through this now I'll publish it to github when it's finished (though this won't be until next week at the earliest). If you just want a single plan and are happy to manually export the file then you can watch this video here - ruclips.net/video/79AFLGoS1yo/видео.html
@@BenHoward_PowerBI Any update on this question or the updates being published?
@@travisharker4536 - I have this working, just not recorded the video yet. The .pdf map for this is in github.com/ben-howard/Temp , and it uses the same Excel file as the previous version.
Hi, I have been progressing this... Can I just ask, why is the delay for scripts ABOVE the delete rows, i.e. after the schedule. Surely (am a newbie) the delay should be after the delete rows, as it take sometime for the rows to be deleted? Therefore, could you explain the rationale/logic behind the delay after the schedule? Trying to make sure I am understanding how delays work as thought I did do, until I saw this.....
Hi Ianf1968, I assume you're using the flow which does not use the excel scripts to remove the rows, rather it uses the "delete row" action in Excel. The delay is because I was seeing issues running the branches in a pure parallel mode, therefore I decided to delay the beginning of each branch. Remember there are four versions of the script in the github repository, each has a slightly different nuance.
Hi am using the scripts with various results. All work in excel ok, all good, but getting random results in corporate environment. I have been increasing delay to see if helps.
Hi Ben, I have managed to get this to work for all team planners but when I try single team I am getting an issue. In the List group members I can’t find the particular team Id I’m after? There’s a list but the one I need isn’t there??
I'm having issues with the scripts, I have the automate feature in Excel but no scripts are appearing, could you share the source code please for all 6 scripts?
Here you are. You need a script for each table, you just need to change the table name for each script. The one below references the "Task_tbl"
function main(workbook: ExcelScript.Workbook) {
//Delete all rows in Tasks table.
let delete_tbl = workbook.getTable("Task_tbl");
// Delete all row(s) at index 0 from table Task_tbl
let numberOfRows = delete_tbl.getRowCount();
if (numberOfRows > 0) delete_tbl.deleteRowsAt(0, numberOfRows);
}
Hello Ben, great videos thanks a lot!
I have a problem. I uploaded the scripts into Excel but when I am in Power Automate it shows me no options to choose in the "Scripts" field. Is that happening because I do not have the rights to run scripts or can you think of another reason?
Thanks a lot!
Can you execute the scripts in Excel, that will prove whether you have the rights to run them?
@@BenHoward_PowerBI I found the solution. I only imported the skripts into excel and it did not work. Then I copied the skripts and created new ones where I pasted the code. The "new" skripts with the exact same code as your imported ones then showed up in Power Automate.
@@BenHoward_PowerBI Now that everything is setup, Excel takes out the information of every Planner we have in the company. Do you know what could be the solution to that?
@@alexklaus2778 - the easiest way would be to create some filters in Power BI.
@@BenHoward_PowerBI I did the filtering in Power BI thanks! But the flow only worked well once and then it gives me the error "Bad request" with the status 400 and the following steps have the message "Action failed". Do you might know what could be the problem? Thank you!
Hey Ben. Thank you for this. It has been a wonderful reference as I try to imitate.
The error i'm stumped on - when a planner task is assigned to multiple people, you get duplicate task IDs in the data source for the Task_tbl. The way the data model is setup, the error i'm getting is: TaskID contains duplicate value this is not allowed for columns on the one side of a many-to-one relationship or columns that are used as the primary key. Now I can get the report to render though with errors by removing taskID duplicates but I'm doubting that's the correct solution if I want accurate Assignee data.
Hi, It works fine for me, and you should not get duplicate task rows. The tasks branch loops through each plan, and for each plan, writes each task to to the tasks_tbl, once only. The assignments branch handles the assignments, lists each plan, and then for each task in each plan, lists each assignment, and writes those to the assignments table. I have multiple assignments in my planner tasks. Please review the relevant flow map at github.com/ben-howard/Planner-PowerBI-with-PowerAutomate/blob/main/Parallel%20with%20Office%20Scripts/Flow%20Map%20with%20Office%20Scripts.pdf to verify your flow is set up correctly.
@@BenHoward_PowerBI Ben, first I'd like to thank you for teh fantastic series and content. This could prrove to be really useflul in our environment.
I am having the same error where duplicates remain in the workbook after running the Flow. I am using a modified version of the "Single Team" without Excel scripts, but rather the Flow actioin to remove dupes. When I try to get the data in Power BI, the error pops up, and the subsequent table do not populate.
Everything else seems to be running fine. Here's the error that BI throws:
Task_tbl
Column 'Task ID' in Table 'Task_tbl' contains a duplicate value 'vY-u42HzmkaD8YHcA8JbhWUAJQ7i' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
@@feniashubby - If you are running this without scripts then you seriously need to consider the scripts option, it is a much faster and reliable way to remove the existing rows. Without being able to consistently remove the rows, you will get the error you are seeing in Power BI. To troubleshoot this, I would manually remove all the rows from the table, and then run the flow, and watch the table populate in Excel. Once it has finished successfully, run it again, watch the table depopulate and then populate. Then link it into Power BI.
@@BenHoward_PowerBI
I discovered what my problem was. I had uploaded the script files to the SPO library then within Excel I connected to them. That is the issue. Once I "Created" the scripts within Excel Online and saved them, they started appearing in Flow. That only works from my OneDrive. As soon as I move the scripits to SHarePoint Online, they stop wroking. 😞
***********
The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'BadRequest'. This may indicate invalid input parameters. Error response: { "status": 400, "message": "Unable to parse script reference. clientRequestId: 97c5f3bc-ef66-45cf-bde3-51cd8d1a0c8d", "error": { "message": "Unable to parse script reference." }, "source": "excelonline-scus.azconn-scus-001.p.azurewebsites.net" }
Good Day Ben, Thank you for this updated version of the first video.
Please, I wanted to ask, When I tried out this solution the dynamic content for each branch didn't automatically fill up as it did in the video with "Remove_Tasks", and "Remove_Assignments. I don't know why this is happening. I would really appreciate it if you could assist with explaining why this is happening.
Hi, it's probably because you've not configured the branch to use the right table? Presumably the content is being removed correctly when you run the scripts manually in Excel?
Hi I am getting this error please help
The dynamic operation request to API 'excelonlinebusiness' operation 'GetTable' failed with status code '429'. This may indicate invalid input parameters. Error response: { "statusCode": 429, "message": "Rate limit is exceeded. Try again in 1 seconds." }
I'm getting the same error
same error. Hi Shahab, did you solve this? If so, pls share
ActionFailed. An action failed. No dependent actions succeeded. I am getting this error
Hi, when I download this from the Repo there is no Automate Tab in the excel, the scripts dont populate and doesnt allow me to enter it in manually. The Tables dont populate and cant type them in either, but it is populating the Documents folder drop down etc when adding in connections etc. i think its failing at connecting to the table in the excel for some reason.
Could not retrieve values. The dynamic invocation request failed with error: { "status": 404, "message": "The resource could not be found.
clientRequestId: 81ebfc6b-995f-4393-b270-b0be9ac0a09e
serviceRequestId: 8c3fb2a4-d194-4f44-a619-70cf8f151948", "error": { "message": "The resource could not be found." }
Thanks!
Okay, if there is no Automate Tab then the scripts feature is not available to you, worth checking with your M365 admin to see if it is part of your subscription, or whether it needs turning on. If you want a copy of the old version please contact me directly via email and I will send you one.
Solution: i created the 6 scripts in the excel file and ran them to test before saving. I then checked the connection info in each of the Excel steps, since my company makes us change our passwords often the default selection was the wrong one, so changed that and the tbl names and script names came up instantly. saved and it worked. woohoo!
@@ForzaJonesy - thanks so much for the feedback, I am very glad it worked for you.
Hello Thank you for your videos. This is very helpful in building out the process. I downloaded the new files and followed along with this video and see the Scripts are not in the file under scripts. Is there a way I can get the details to add these new scripts to the excel file.
Hi - you need to ensure Office Scripts are available. Assuming they are then here is the basis for the script. You just need to amend the "Table_tbl" value for each of the tables.
function main(workbook: ExcelScript.Workbook) {
//Delete all rows in Tasks table.
let delete_tbl = workbook.getTable("Task_tbl");
// Delete all row(s) at index 0 from table Task_tbl
let numberOfRows = delete_tbl.getRowCount();
if (numberOfRows > 0) delete_tbl.deleteRowsAt(0, numberOfRows);
}
Here's the details on how to turn on Office Scripts - docs.microsoft.com/en-US/microsoft-365/admin/manage/manage-office-scripts-settings?view=o365-worldwide
Thank you very much, this is very helpful!
Can you help me with this error?, this happened when I try to run the flow again and occurs when the script is run to remove rows .
"We were unable to run the script. Please try again. Your file was not saved because we could not merge your changes with changes from someone else."
It only happens sometimes, but I don't know what the problem is. I have two users adding task in the planner I don't know if that's the problem.
Had this error. I had the planner task excel open in my desktop excel app. I closed it and it was resolved. If you want to see the real-time updates of the excel, you need to open it from sharepoint.
@@beefy2390 I had this issue and had the excel file closed. I have no idea what's causing the issue.
Hi Ben, the public view doesn't appear to be available via the link in the description? Is there a new link to view this? I'd like to have a play before suggesting this to my team
app.powerbi.com/view?r=eyJrIjoiMjc5MmU1N2ItZWQ5Ny00ODVlLTg4ZGUtNGE2NDMwYTJkYmZmIiwidCI6ImI0MWI2YWQ5LTViZDAtNGNkYS05NWI3LWI0NDhkMGM2NTZiYyJ9
@@BenHoward_PowerBI thanks! this looks great
when I downloaded the excel file it doesn't contain your scripts it's just empty, any idea what could be the cause of this?
Hi, please save the Excel file to SharePoint, open it in the browser, and check under the Automate Tab. The scripts are there for me (I have just tested it), but in case you need the code, it is as follows.
function main(workbook: ExcelScript.Workbook) {
//Delete all rows in Tasks table.
let delete_tbl = workbook.getTable("Task_tbl");
// Delete all row(s) at index 0 from table Task_tbl
let numberOfRows = delete_tbl.getRowCount();
if (numberOfRows > 0) delete_tbl.deleteRowsAt(0, numberOfRows);
}
Hi Ben, any suggestions where we have 27+ plans so the flow is taking a long time. I also removed the parallel flows and made it concurrent as it constantly errored out..
If it is timing out it is because you are hitting limits put in place by Microsoft. How long is a long time?
@BenHoward_PowerBI thanks for your quick reply. Usually 22 hours, I've seen it running for 1 day 22 hrs when I turned off the daily refresh to see what happens
@@alannahsteevens5102 - wow, that is soooo long... I'm trying to work on soln that uses the graph API but don't have much spare time at the moment - it *might* be quicker but who knows.
@@BenHoward_PowerBI I have used the graph API and it still times out. I think you're correct that it's the call limits per license, I turned off all other flows in our organization that weren't sensitive and it ran successfully taking 3 hrs again
Good day, this takes days to complete.
I guess you have a lot of data, approx how many planner plans do you have and how many tasks? If you let it run 7 times or so you'll have enough info in the process advisor.
Hi Ben - Do you have the excel template for the Single Team is this different that the multiple teams
Hi, the Excel template (and the power bi template) is the same across all of flows.
@@BenHoward_PowerBI I tried to do the one for Single site but it asked for Script location so was a bit stumped at this one as was unsure where or how to get the script running
The dropdown menu was missing on the script as in your video
@@highlandwhite5401 - the dealing with the scripts occurs around 6 mins in the video, and adding the scripts to the Excel sheet @ around 7:38. Both these steps apply to the single team flow.