This Power Automate video is a step-by-step tutorial on how to track column changes (version history) for SharePoint by using “when an item is modified trigger” in flow. We can track to see the column changes for the modified item, leverage the send an http SharePoint action to get previous values of item based on previous version & also leverage the “Get changes for an item or file” action to get the information of which columns have changed.

The video will showcase some interesting patterns to get the “Version History”. Video will have something for everyone from beginners to advanced users. Trying the CRAWL, WALK, RUN & SPRINT techniques in this video.

Video covers the following:

✅ When an item is modified trigger in flow
✅ Add trigger conditions in Power Automate
✅ Get SharePoint column changes based on Version Number in flow
✅ Get previous column values using versions SharePoint REST API
✅ Create HTML data with column change information
✅ Send email notification of SharePoint column changes tracked

#PowerAutomate #SharePoint #Versions #flow

Table of Contents:

00:00 – Introduction to Track SharePoint column changes in flow
00:41 – Run a flow when SharePoint item is modified
04:18 – Run flow based on trigger conditions when column is modified in SharePoint
10:00 – Track changes & Version History when SharePoint item is modified
28:30 – Send column changes information in HTML Format in Email
31:17 – Subscribe to Reza Dorrani channel

In this step-by-step tutorial video for beginners, I will show how to build Conditional & Dynamic Approvals in Power Automate.

To create an approval workflow, use the Approvals – Start and wait for an approval action in any Power Automate flow.

Power Automate Conditional Approvals: We will build conditional approval workflows for SharePoint based on a value. Scenario would be to trigger a user Manager Approval first if amount exceeds a certain limit and then route the approval to the next set of approvers.

Power Automate Dynamic Approvers: We will use a “For a selected item” SharePoint trigger-based flow, to call a workflow on demand to perform Approvals. The user will select the approvers needed for the workflow action directly within SharePoint itself.

Power Automate Dynamic Approval Type: We will also provide the user an option to select the Approval Type / Pattern – “First to respond” or “Everyone must Approve”.

Topics covered:

• Conditional Approval in Power Automate
• Dynamic approvers selection by user to trigger the approval process
• Dynamic Approval Type selected by user

This video is a step-by-step tutorial on creating custom approval responses using Power Automate flows with SharePoint Document Libraries. We will build a majority wins approval workflow scenario based on the custom approval responses. If a majority vote for Yes, then the document approval process will be marked as “Approved” else “Rejected”. We will set up our approvers dynamically by leveraging a SharePoint Group. The process is dynamic as the number of approvers are based upon the members of the Group.

We will also apply SharePoint View Formatting to enhance the user experience for the Document Approval workflow process & track the status of the Quorum (Majority wins) approval.

Topics covered:

✔️ Power Automate Custom Approval Responses

✔️ Dynamic approvers with SharePoint Group

✔️ Document Library View Formatting

✔️ Approval Status Tracking

✔️ Quorum Approvals (Majority Wins)

This video is a step-by-step tutorial on Microsoft Power Automate flow expressions.

Step 1 – Introduction to expressions for beginners: What are expressions, syntax, expression editors, where can we use expressions, demos etc.

Step 2 – Expression functions walkthrough (Beginner to Intermediate) : Types of functions, working with string, dates, converting time zones, working with collection functions, logical & math functions, working with JSON, demos, etc.

Step 3 – Expressions in Trigger Conditions: Save API Calls, avoid loops, using nested functions, demos, etc.

Step 4 – Advanced Scenarios with Expressions: Email body message parsing, Send email attachments to folders in SharePoint, Calculate business days for a given date range, Get flow run link and handle exceptions, Send Overdue Task Reminders, demos, etc.

Topics covered:

✔️ Flow Expressions Basics

✔️ Classic Vs Modern Expression Editor

✔️ Expression Functions List – String, Date, Arrays, JSON, Logical & Math functions

✔️ Trigger Conditions

✔️ Examples: Calculate working days, send reminder emails, avoid for loops, send email attachments to SharePoint Library Folders, dynamic email body parsing, etc.

✔️ Power Automate Expressions Best Practices

Happy September!

One of the items I always recommend when forming a Governance Committee is to have someone in your organization be granted the Message Reader permission to view the messages in the Office 365 Message Center.  It’s where you’ll get the most detail for the items you want to follow, there is a planner syncing interface, and you can follow and send messages about the products you are interested in.  I find it easier to get the detail I need there, but also refer to the Microsoft 365 Roadmap.  This month, the Microsoft 365 Roadmap will be getting an update to it’s easier to track what you need there.

Microsoft 365 roadmap site updates

Microsoft will be making updates to the Microsoft 365 roadmap on how you view, filter, search for, and sort the information in early September.

UI changes

• See last added will be removed from the header and can be accessed in the filters, under New/updated
• Filters will be moving just below the header image, inline with the search bar, so it’s easier to view your selected filters and if you have search turned on
• The features will be displayed on feature cards, as opposed to a table format, in order to optimize space and make information easier to view
• You will be able to sort the feature cards by either preview date or Generally Available (GA) date
• Status will be represented visually on the feature card to save space and make it easier to perform a quick scan
• Feature cards will be easier to collapse if you want to explore looking at the status, title, and preview and GA dates
• On the feature card;
  • you will be able to see tags organized by category (product, release phase, platform, cloud instance)
  • you will be able to initiate a feedback submission (the feedback experience does not change)
• Mobile view – the same changes will be applicable in mobile view so you can easily view, sort, filter, and search

API changes

• Preview date will be a new data point available to you
• The filters or tags will be prefaced by a category name

Understanding the new Preview data

• Not all features will have public previews, however when a public preview date is available, it will be displayed
• The status of a roadmap item (In Development, Rolling Out or Launched) will be in relation to the GA date and not the preview date

The Preview data will be available, and the roadmap item’s description will be updated to read, “This item is currently available in Public Preview” when the following is true:

• Roadmap item has a Preview Tag, Preview Date and we are in the Preview Month

What you need to do to prepare:

You might want to notify your users about these changes and update any documentation as appropriate.

Pairing naming convention between Teams channels and corresponding SharePoint folders

This new release will update the channel rename experience in Teams to simultaneously rename the corresponding folder in SharePoint sites whenever a channel in Teams is renamed. Microsoft are taking this step to eliminate the difficulty in tracking, across all Microsoft 365 endpoints, that occurs when a channel in Teams is renamed and the corresponding folder’s naming convention is not updated accordingly in SharePoint. This will rollout on Teams desktop and web.

Key points

• Microsoft 365 Roadmap ID: 72211.
• Timing: mid-September through mid-October 2021
• Roll-out: tenant level
• Control type: admin control
• Action: review and assess

This new feature’s pairing of the naming convention between Teams channels and the corresponding SharePoint folders will help to eliminate confusion across all Microsoft 365 endpoints, providing greater clarity for users.

This new feature will be available for all Standard and Private channels that are renamed after the rollout.

• For Standard channels – The corresponding folder in SharePoint will be renamed.
• For Private channels – For Private channels, the corresponding folder in SharePoint will be renamed.
• For legacy Teams channels that were renamed prior to this rollout – There will be no change in experience. When a legacy channel is renamed after this release, it will simultaneously rename the corresponding SharePoint folder.
• Upon renaming a channel, the new folder name will only reflect the same name in the corresponding OneDrive sync client when the whole document library has been synched. When only the channel folder is synched, the new name will not be reflected in the OneDrive sync client.

Teams – Announcing updated Send Feedback experience in mobile app

This new feature will allow users on Teams mobile app to share feedback directly from the Help & Feedback option in Settings, without needing to send an email message. You will also be able to control which elements (like email addresses, and logs) can be shared by users when sending feedback on the mobile app.

This message is associated with Microsoft 365 Roadmap ID: 84004.

When this will happen:

Microsoft will begin rolling this out in late September and be complete by late October.

• This change is only applicable to the standard release.

How this will affect your organization:

In addition to users being able to send feedback by using the mobile app, take note of how you can control mobile feedback elements using existing admin policies for Teams.

• You can disable, enable send feedback; default on
• You can enable, disable sharing email as part of send feedback; default off; if enabled, users will get option to share email – default opt out
• You can enable, disable sharing logs as part of send feedback; default off; if enabled, users will get option to share logs – default opt out; log collection to be supported only on Mobile as of now

Note: Send feedback will not be available for student licenses and the entry point for send feedback will remain same for end users.

All previous admin settings will be respected and left unchanged.

What you need to do to prepare:

Use cmdlet to modify Teams feedback policy. Policy changes apply to Desktop, Web and Mobile clients

• Reference: https://docs.microsoft.com/en-us/powershell/module/skype/set-csteamsfeedbackpolicy?view=skype-ps
• UserInitiatedMode: Set parameter to enabled to allow users who are assigned the policy to give feedback; default ENABLED
• AllowEmailCollection: Set this to TRUE to enable Email collection; default FALSE
• AllowLogCollection: Set this to TRUE to enable log collection; default FALSE; log collection to be supported only on Mobile as of now

Learn more:

• Manage feedback policies in Microsoft Teams
  • Prior to rolling out this release, Microsoft will update this link with greater admin documentation via a MC update.

(Updated) Power Apps can now display images from Microsoft Lists

Updated August 26, 2021: Microsoft has updated the rollout timeline below.

Power Apps image controls can now display images that are stored in image columns in Microsoft Lists. Images can be displayed in four sizes: small, medium, large, or full.

When this will happen:

• Targeted release will begin rolling out in early October (previously mid-July) and will be complete by mid-October (previously end of July).
• Standard release to all other cloud environments will begin mid-October (previously end of July) and be complete by end of October (previously early August).

How this will affect your organization:

New apps that use the SharePoint connector to add a list as a data source may display images from the list if they are present in the list. Existing apps can also be updated to show images.

• Image control in Power Apps
• SharePoint connector documentation

(Updated) PowerApps/PowerAutomate Retirement of Direct Links in the OneDrive for Business Connector

Note: This only applies to Direct links in PowerApps/PowerAutomate. Using the Direct Link option in PowerApps/PowerAutomate does not provide the same experience as other services as the Direct links created expire after a short period of time.

The ‘Direct’ link option is being retired because rather than creating sharing links it creates file download links, which do not reflect the scope chosen by the user and may not be accessible after a short period of time.

If no users in your tenant are using the Direct Link option you can safely disregard this message.

Key points

• Timing: Beginning September 25, 2021 Microsoft will start retiring the Direct Link option while using the ‘Create sharing link’ action in the OneDrive for Business connector in Power Apps and Power Automate
• Control type: user control / admin control
• Action: review, assess and update your apps and flows per the below directions

What you need to do to prepare:

If you are currently using the Direct link option, you have until September 25, 2021 to fix your apps and flows. After that, the action will return an error message. In order to create a long-lived Sharing Link as intended, please select “View” or “Edit” as the Link Type rather than “Direct”.

Learn more about the connector:

• OneDrive for Business

Important updates are available for Microsoft To Do apps

If your organization does not use Microsoft To Do, you can disregard this message. 

Microsoft is moving Microsoft To Do’s backend to a more efficient service. For the best experience across all of your devices, you need to update to the latest version. Make sure your mobile and desktop To Do apps are version 2.49 or higher before end of October 2021 to avoid any issues with cross device syncing. Also since To Do tasks are also linked with Outlook Android and iOS apps. Please make sure that you update your Outlook android and iOS apps as well.

Key points

• Timing: Before October 31, 2021
• Roll-out: tenant level
• Action: Update Microsoft To Do apps (Android, iOS, Windows and Mac) and Outlook apps(Android and iOS) to the latest available version before October 31, 2021.

How this will affect your organization:

After October 2021, To Do app versions 2.48 or below will no longer sync across devices, but you’ll still be able to use To Do in offline mode. After October 2021, you can keep your To Do apps syncing by updating your apps to the latest version. If you log out before updating to version 2.49 or higher, you will lose all un-synced data.

What you need to do to prepare:

Please make sure you update your To Do apps(Android, iOS, Windows and Mac) and Outlook apps(Android and iOS) to the latest available version before 31st October 2021. Update your To Do apps to the latest version in the appropriate app store.

• For desktop versions of To Do, visit Windows Store or Mac App Store.
• For mobile versions of To Do, visit the Google Play Store or iOS Store.

Learn More

Additional information

Changes to Search in Microsoft Stream (Classic)

Starting September 20, 2021, and on an ongoing basis, your Microsoft Stream (Classic) users may be unable to view, search, or edit automatically generated transcripts for newly uploaded videos OR videos that were not uploaded, edited, or viewed during the prior six (6) months (three (3) months if you are a customer in the eastern United States data center). Transcripts will be automatically regenerated when a video gets a new view or when a user with owner rights to the video updates the video details to automatically regenerate the transcript (the video will need to have at least 1 new view).

For your reference, this is an update to prior Message center posts:

• MC234079 and MC248965 for eastern United States data center
• MC241595 for northern Europe data center

Note: this does not apply to

• Automatically generated transcripts that have been edited
• Transcripts that have been manually uploaded to Stream (Classic)
• Videos that have been uploaded, edited, or viewed within the past six months

When this will happen:

Beginning September 20, 2021

How this will affect your organization:

This change affects newly uploaded videos, automatically generated transcripts that are unedited, and videos that were last uploaded, edited, or viewed more than six (6) (or three (3) for eastern United States) months ago, with the six-month (or three-month) period being calculated on a rolling, ongoing basis.

As an example, if a video with auto-generated transcripts goes unwatched in the six (6) months following its upload, the auto-generated transcript will become unavailable. (The video will remain available.) However, a transcript will automatically be regenerated when the video gets a new view, or, when a user with owner rights to the video updates the video details to automatically regenerate the transcript (the video will need to have at least 1 view in this case).

Please note the following points:

• Transcripts that were edited by users, as well as transcripts and subtitle files uploaded by users, will continue to be available.
• Transcripts will be available for download by video owners via the video edit page for all videos.
• Videos will not be removed.
• Users will be able to view closed captions for all videos.
• If a video owner wants others to be able to view, search, or edit transcripts for videos for which the automatically- generated transcript is impacted, the owner may regenerate a transcript by unchecking and rechecking the “Autogenerate captions” option in the update video settings page. Afterwards, the transcript will be displayed again in the player page for viewing, searching, and editing – only if the video has at least 1 view.
• For newly uploaded videos, transcripts won’t be automatically generated. But, transcripts will be automatically generated as soon as a new video gets at least 1 view.

What you need to do to prepare:

Microsoft suggest you notify your users about this change and update your training and documentation as appropriate.

• You may be interested in moving your organization to use the new Microsoft Stream (built on SharePoint), which stores videos in OneDrive for Business.
• Read more about updates to transcript functionality in Microsoft Stream (Classic)

Lightweight Plans in Planner

With the upcoming “Meetings” solution: New hybrid work innovations in Microsoft Teams Rooms, Fluid, and Microsoft Viva, meeting participants can quickly capture tasks and expect them to automatically sync into the Microsoft 365 Task Ecosystem (Planner, To do/Outlook, and Teams Task app). To enable this, meetings create a plan in Planner for the captured tasks.

To support this more ad-hoc form of task tracking, Planner now supports plans that don’t require an M365 Group, which we are calling “Lightweight plans”. Instead of using Groups, access to these plans is determined by a simple list of members stored in Planner that can contain any user with a valid AAD ID in that tenant (more details below).

The basic functionality of Lightweight plans is:

• Lightweight plans can be created by meetings as described above, and through our Graph APIs. There is no current way to create them in Planner UI
• Once created, Lightweight plans appear in Planner alongside existing Microsoft 365 Group plans, and can be interacted with via UI the same way
• All members have equivalent permissions: Any existing member can add / remove other members, and create / edit / delete tasks and the plan itself
• Plan features tied to M365 groups are disabled in these plans, incl. links to OneNote / SP Site, comments on tasks, and uploaded attachments
• The Lightweight plan will be deleted when the last member is removed
• There are no other changes to plan functionality for Lightweight Plans compared to existing Microsoft 365 Group plans

Additional notes:

• There are no changes to Planner licensing or storage for Lightweight Plans compared to existing Microsoft 365 Group plans
• “Lightweight plans” do not result in the creation of any objects in AAD

When this will happen

The APIs for Roster containers (which is how Lightweight plans are enabled, see below) will be enabled on Graph for all tenants starting in mid-September and expect to complete by mid-October.

How this will affect your organization

When this feature is released, plans can be created outside of Microsoft 365 groups. Lightweight plans are not subject to existing Microsoft 365 group controls.

What you need to do to prepare:

If you would like your tenant to be able to utilize “Lightweight plans” (i.e., plans that aren’t contained by a Microsoft 365 Group), you will need to make sure Roster containers are enabled (since this is how we enable Lightweight plans, as described below).

Alternatively, there are tenant admin settings that can disable the creation of Roster containers (and thus Lightweight plans) in a tenant: Disable the creation of Roster containers in your organization’s Planner tenant – Microsoft Planner | Microsoft Docs.

Note that disabling the creation of Roster containers means that your tenant will not be able to take advantage of the Meeting Notes integration with Task Ecosystem, such that tasks captured in a Meeting will not sync to M365 Task Apps.

In addition, see updated data export documentation that covers new Roster-related info.

How Roster containers relate to Lightweight plans

To enable Lightweight plans, we are introducing a new type of container in the Planner service, which we are calling “Roster containers”. Lightweight plans are simply the plans attached to Planner Roster containers.

Note that Roster containers are a Planner-only concept, and do NOT create objects in AAD. They are NOT an equivalent to M365 groups. A single “Roster container” can only contain a single Lightweight plan.

Microsoft Forms: Manage and organize your forms and quizzes now

This new Forms feature release will allow users to manage and group all forms and quizzes as desired and in their own way, to efficiently archive and categorize their forms into collections.

This is rolling out desktop, web and mobile.

This message is associated with Microsoft 365 Roadmap ID 70734.

When this will happen:

Standard: Microsoft will begin rolling this out in late September and expect to complete rollout late November.

Government: Microsoft will begin rolling this out in early November and expect to complete rollout late November.

How this will affect your organization:

End users in your organization will be able to easily archive and manage their own personal forms into different collections which will allow them to work more productively and feel more in control of their personal content. Additionally, this new update will provide a smooth experience for users when they switch between different products in office.com (e.g. PowerPoint, Word, Excel, OneDrive).

In addition, users will also be able to:

• Better archive and manage quick polls/instances that are generated through Microsoft Teams, Outlook, and PowerPoint
• Easily manage their forms/quizzes/polls and quickly locate the certain file they are looking for among user-created, shared and group forms

This is the view that users will see after they create and manage forms /quizzes into collections from their personal forms.

Collection is supported in mobile view as well. Below shows how the collection looks like on small screens.

What you need to do to prepare:

You might want to notify your users about this new capability and update your training and documentation as appropriate.

In this tutorial, I will show how to add data dynamically to an Excel file using the Add a row into a table action from Excel Online connector.

We will dynamically create a Table, add columns by defining the table range & column names and more.
This Power Automate flow pattern can be applied to any data source or service to fetch the data and then do a for each row on the data and add it to the Excel table.

Lastly, we will send an email and attach the generated Excel file.

We will cover the above with examples using SharePoint, Dataverse and Planner as data source.

Topics covered:
✔️ Export to Excel Power Automate flow pattern
✔️ Dynamically Create Table, Add Columns, Add Rows in Excel
✔️ Send email from flow with Excel file attached

Table of Contents:
00:00 – Introduction
00:26 – Export data from SharePoint to Excel (Generate the Columns of Excel file)
03:20 – Create Excel file in OneDrive
05:13 – Create Table in Excel (Table Range A1 Notation)
06:29 – Add a row into a Table (Excel)
08:05 – Power Automate Send Excel file in Email
09:34 – Dynamically Add Columns & Create Table in Excel
14:44 – Reusable flow pattern for exporting to Excel
14:56 – Export data from Microsoft Dataverse to Excel using Power Automate
15:47 – Export Tasks data from Planner to Excel using Power Automate
16:19 – Subscribe to Reza Dorrani channel

This video is a step by step tutorial of how to create HTML table in Microsoft Power Automate and email the table data. We will use Power Automate flows to create the HTML table from SharePoint list items using flow “Create HTML Table” action and add it to the email body. We will cover various filter query scenarios to get the tabular data from SharePoint list, send emails with dynamic content and add HTML table styling to the body of e-mail sent by the flow.

Table of Contents:
00:00 – Intro
00:26 – Asset Manager SharePoint List
01:05 – Build a flow to send an HTML table in Outlook email (SharePoint list to HTML Table in Power Automate)
01:19 – Build SharePoint like Views in PowerApps
09:47 – Add HTML Table Formatting (Styling) in Power Automate send email action
11:59 – Add Link to Item in Create HTML Table action in flow
17:00 – Send HTML table data from SharePoint list based on Date column (date formatting)
20:15 – Send HTML table data based on Date range (due in x days)
21:40 – Format SharePoint Choice column in HTML table in email
25:33 – Run flow with input form – Status column formatting
27:07 – Send Picture / Image of Person column in email using Power Automate’
28:14 – Group SharePoint list items and send formatted HTML Table for each Group
35:22 – Subscribe to Reza Dorrani channel

In this video, you will learn how to build a content approval process workflow for SharePoint document library with Power Automate. This is a step-by-step guide to build your own customized document approval system powered by Power Automate Approvals. The Content Approval feature for SharePoint adds security to the document until the document is approved. Document approval status flow : Draft, Pending, Approved, Rejected. 

Table of Contents:
00:00 – Introduction
00:49 – Leave Request Power App walk through (with flow progress indicator)
02:20 – Why build custom flow approval dashboard in Power Apps?
04:10 – Build a sequential approval flow connected to SharePoint list
18:24 – Build a Flow Approval Dashboard in Power Apps for SharePoint
21:20 – Test approval dashboard
26:43 – Subscribe to Reza Dorrani channel

Its Oscars time again…well, Covid-19 Oscars anyway. If you love movies like my wife and I do, then you are making your own list of movies to watch.  For us, we try and see all of the Best Picture nominations and as many of the other nominations as possible.  One nice thing about this season is that so many of the pictures are already streaming.  The Academy decided to allow streaming movies to be nominated even if they never were in a theater.

The First Pass

Normally this process means either making up a list on paper, or printing a ballot and crossing off movies as we see them.  This year, I decided to use some tech to make this easier.  I created a list in Microsoft To Do list for the Oscars. It didn’t take too long. I got a printable ballot from the Oscars website, and then I manually entered each movie.  Originally I was putting the nominations into the title, but that was ungainly.  Then I hit upon the idea of adding a step in the To Do task for each nomination and To Do would automatically list the number in the title so it was easy to see.

I then shared the To Do list with my wife and we have been using it to track out Oscar viewing.  We favorited the Best Picture nominees and when we watch one, we complete the task.  This was all done on my phone which the only place that my wife uses To Do.

Sharing with Others

I showed this to one of my friends at work. She asked me to share the list with her.  Unfortunately I had created the list in my personal M365 tenant and you can only share a To Do list with someone inside of your tenant.  We then looked at how I could export this and they could import it.  You can export your personal tasks from Outlook, but this is in M365 so we cannot export it, and even if we could…To Do doesn’t have an import.  So, what is a guy to do (pun intended)?

Power Automate to the Rescue?

Power Automate has some To-Do activities, they put in a hyphen for some reason, so I will use that in this section.  I figured that I could:

1. Create a Flow in Power Automate
2. Build out the To-Do list
3. Share that with others

Attempt 1 – The Initial Flow

Creating the Flow is really easy.  I decided to create an instant flow since I only have to run this once to do the work.  <Insert Instant Flow 1 & 2>

There are some To-Do activities in PowerAutomate so I searched for them.

• Add a to-do (v3) – This is the one that I am going to use
• Create a to-do list (v2) – Good to know we can use this if we want to create a list…which reminds me…
• Delete a to-do (v2) list
• Get a to-do list – useful if you want to manipulate a specific list
• Get a to-do (v3) – if we need to manipulate or make decisions on an item
• Update a to-list
• Update a to-do (v2)
• Delete a to-do (v2)
• List all to-do lists (v2) – Could be useful in a lot of ways
• List to-do’s by folder (v2)

This made me realize that people will likely want to add this to new list for the Oscars so we need to add a new list.  Which also means that we need a name for that list.  I could hard code it, but instead, lets add it as an input to the Flow like so:

Open up the Manually trigger a flow action, <Insert Animated Image> add a text input, and prompt the user to give you a name for their list.

Next we use the Create a To-Do list (v2) to create a list using the name they gave us.

Now, when we add our To-Do item in the next step, we will use the list we created. <Insert Image of Movie Being added>

One thing to note, we use the ID of the List as opposed to the name when we add it.  That means select custom value and then you can get the dynamic content to add the id from the prior action.

Next we add the Title of the movie as the Title. The Due Date is set to Noon on April 25th (the date of the Oscars this year). We cannot add steps via flow, so I chose to add the nominations as a bulleted list in the Body Content.

Now we do that for the other 65 movies and…wait a second, I am all for a little cut and paste, but that is a LOT of cut and paste.  Yes we have the Clipboard in PowerAutomate now, but it would still require a lot of configuration.

Attempt 2 – Is there a better way to do this?

I decided that if I built an Excel workbook, I could create a table and add a row for each movie with the Title, and then a column for each nomination.  Yes, I could add them all in one column, but I figured there would be an easy way to get them to all concatenate in PowerAutomate.

So, I added an action to list the rows in an Excel table.

This allowed me to add the movie’s title to the To-Do Task

But, there are multiple rows in the table and that makes the Add Task Action get placed into an Apply to Each control.

NOTE: You might get a warning from PowerAutomate that you need to set the Concurrency Control on in the setting and turn the degree of parallelism down to 1 to ensure it runs properly.

So, the flow is all ready to run.  After saving it, on the main screen you can click the Run button

This will check your permissions on the first run and then prompt you to give us the name of the Oscars List you want to create.

Once you click the Run flow button it will execute the flow and build your list.  It works just fine and we have the Movie Name and the number of nominations for each one as the task name

The task itself has in the notes the list of nominations as an unordered list, but it didn’t quite work.  There are two things wrong here.  One is that the unordered list isn’t formatted correctly.  This is because we are mixing the HTML code and the strings and Flow doesn’t know which is which.  The solution is to build a single string with the body text.  Which brings us to the second issue.

Sound of Metal has six nominations but the list always will have 10 because that was what we created it to handle.  The bottom 4 are blank.  Is there an easy way to only display the right number?

I reached out to a few friends who are big with PowerAutomate and my co-worker Pontus was able to give me some advice.  There isn’t an easy way to do what I want here (yes, we could change the data, but we are often presented with data in a format that isn’t easy to work with and part of this exercise is to take data that isn’t in a normal form).  So two things need to be done.  First I need to create a variable (a string) for the body of the task that builds out the nominations, and then set that variable for the item with the correct number of nominations.

NOTE: The data pulled from the Excel workbook is a string, so in the Case statement we have to wrap the number in ” or you will get the following error. “An unexpected exception encountered when evaluating branching condition.”  Check out this post that talks about it and where I found this solution

Once we have all the cases built, then we can add that variable to the Add a to-do task

Wrap Up

So, that is it.  We built a PowerAutomate Flow to make this work, and now I will share that with you as well.

Here is a link to the Oscars.xlsx file from my OneDrive.  Download that and put it in your OneDrive or SharePoint where PowerAutomate can get it.

Here is the PowerAutomate Package for you to import into your environment.  After you import this. you will need to edit the Flow to point to the Oscars file where you placed it.

Last note, I only built the switch to handle 1-6 and 10 nominations.  IF we wanted to use this again next year we might need to update things.

Note after the last note: You then have to fiddle a bit with the connections and the location of the file in OneDrive.

Unless you want to pay for premium connectors like Plumsail to handle permissions in Power Automate, there’s no easy way to work with permissions in your flows. But there is a way to change permissions and permissions levels using the good old “Send HTTP Request to SharePoint”.

I had a requirement to build a site archival solution that once it was approved, it should change the permission level for the Owners group from “Full Control” to “Read”.

So this was our starting point, standard vanilla SP permissions:

Here’s the full flow that is needed to get the job down, we’ll break down each part.

First step is to figure out what our Owners group object is. The first HTTP call will get all the groups on the site, with a filter on: Group Title contains ‘Owner’

_api/web/sitegroups/?$filter=((substringof(‘Owner’,Title)))

Next step is to parse the results that we get back from that HTTP call. There are several good blog posts out there on how to parse JSON so I won’t go into that. The Schema returns only two properties to save on call size:

“properties”: {
“Id”: {
“type”: “integer”
},
“Title”: {
“type”: “string”
}

Now we should have a nice and clean JSON containing the group details we need. Next step is the trickier one. To work with permissions levels we need to know the magic numeric values of “roledefid”.

“roledefid” for Permission Levels are:

Full Control: 1073741829
Contribute: 1073741827
Edit: 1073741830
Read: 1073741826

So the first call is to grab the ID from the JSON and assign that as “principalid”. This is the SharePoint Group ID. And then pass “roledefid” to tell it what to add.

_api/web/roleassignments/addroleassignment(principalid=@{items(‘For_Each_SP_Owner_Group’)?[‘Id’]},roledefid=1073741827)

Once that is done, it’s almost exactly the same to remove the old permission level. We’ll just make a “remove” instead of “add” call:

_api/web/roleassignments/removeroleassignment(principalid=@{items(‘For_Each_SP_Owner_Group’)?[‘Id’]},roledefid=1073741829)

That should do it! Once it has run the permissions should look like this:

If you want to tweak this to not only target the owners group, you can easily change the first HTTP call to not have the filter query. Then all groups will be included in your logic.