Hello! This is a great use case. What happens here is that, by default, the Planner connector requires the Bucket ID (a long alphanumeric code, like a GUID) rather than the display name ("To do", "Done"). When you select a bucket from the dropdown menu, Power Automate hardcodes that specific ID.
To make this dynamic, we need to apply some Solution Architecture practices to ensure the flow is scalable and maintainable.
1. Governance and Architecture (Best Practices)
Avoid Hardcoding: Do not use multiple nested conditions (If or Switch statements) with manually pasted Bucket IDs. If someone creates a new bucket in Planner tomorrow, your flow will break or require rewriting. The best approach is to fetch the IDs dynamically.
Connector Governance (DLP): Ensure that both the Excel Online (Business) and Planner connectors are housed within the same Business Data Group in your tenant's DLP (Data Loss Prevention) policies. Otherwise, the automation will be blocked when trying to pass data between them.
Service Account: Run this flow under a dedicated Service Account that has permissions to both the Excel file and the Planner plan. This prevents the automation from failing if the original flow creator leaves the organization or changes roles.
2. Solution Design in Power Automate
Instead of picking the bucket from the dropdown, you will pass the ID dynamically. Here is the architectural structure:
Trigger and Read: Your flow reads the Excel row. Ensure you have a dedicated column for the target bucket (e.g., Nome_Bucket).
List buckets: Add the Planner action called List buckets. Select your Plan. This retrieves all existing buckets and their hidden backend IDs.
Filter array: Add the Filter array data operation action.
In the From field, use the value array from the previous action.
In the condition, set it to: Name (from Planner) is equal to Nome_Bucket (the dynamic content from your Excel column).
Update/Create Task: In the action where you create or update the Planner task, go to the Bucket ID field, scroll to the bottom, and click on Enter custom value.
Insert the following expression to grab the exact ID of the dynamically matched bucket:
first(body('Filter_array'))?['id']
3. Data Standardization at the Source
For this architecture to work flawlessly, the data in Excel cannot have typos. I highly recommend creating a data validation rule or a helper column in Excel to ensure data integrity before the flow even reads it, using regional syntax with semicolons.
For example, an Excel formula to categorize the status could look like this:
=IF(Status_Tarefa="Cancelado"; "Cancelled"; "To Do")
Hope this helps steer your project in the right direction!
