Use MS Lists and Power Automate to copy files to MS Teams

This blog post is a continuation of the work we did in:

Drawing inspiration from a PMO (project management office) team managing multiple projects from a centralized List in mind, we will copy template files to the project team as part of the provisioning process.

Part 1 – Update “Projects” list

I’ve added a new Hyperlink column, called “SharePoint Link” to the “Projects” list.

Column name	Column type
Title	Title used to store the project name.
Description	Single line of text used to store the project description.
Visibility	Choice with the following options: “Private” and “Public”. Indicates if a team should visible to non-team members.
Owners	Person or Group with Allow multiple selections enabled.
Members	Person or Group with Allow multiple selections enabled.
Link	URL used to store the web link to the newly created Teams team.
Private Channel Owner	Person. Important: do not allow multiple selections. We can only add one owner to the private channel using Graph.
SharePoint Link	Hyperlink used to store the SharePoint site associated with the newly created Teams team.

Part 2 – Update Flow Get SharePoint URL

I’m updating the Flow created in Use MS Lists and Power Automate to provision Private Channels in Teams to include the steps required to get the underlying SharePoint site URL of the newly created Teams team. My starting point looks like the image below.

Add a Azure AD – Get group step and set Group Id to “Team ID” from the previous Get a team step (contained in the Condition action).

Insert a new HTTP action and set:

Method to “GET”
URI to https://graph.microsoft.com/v1.0/groups/<Id>/drive where <Id> is from the previous Azure AD – Get group step
Authentication to “Active Directory OAuth”
Tenant to “variables(‘varConnectInfo’)?[‘varDirectoryID’]”
Audience to https://graph.microsoft.com
Client ID to “variables(‘varConnectInfo’)?[‘varApplicationID’]”
Credential Type to “Secret”
Secret to “variables(‘varConnectInfo’)?[‘varSecret’]”

Save and test the Flow. Copy the output from the HTTP step.

Add a Parse JSON step, set:

Content to “Body” from the previous HTTP step
Click “Generate from sample” and past in the copied output from the previous HTTP step

Insert a new Initialize variable and set Name to “varWebURL”, Type to “String” and Value to following expression:
substring(body('Parse_JSON_-_Get_Group_Drive')?['webUrl'], 0, lastIndexOf(body('Parse_JSON_-_Get_Group_Drive')?['webUrl'], '/'))

This expression trims the Document Library path from the site URL.

Insert a new SharePoint Update item action and set:

Site Address and List Name to your target site and list
Id to “ID” from the When an item is created step
Title to “Title” from the When an item is created step
Visibility Value to “Visibility Value” from the When an item is created step
SharePoint Link to the “varWebURL” variable

At this point in the Flow we create a new Teams team, add a private channel, get the SharePoint URL and save it back to our List. In the next section we will check for the General folder in the Groups default drive. The General folder maps back to the General channel in the team. As I understand it, the default Files tab does not provision until you visit the team. To work around the delayed provisioning we will check for the folder and if it does not exist create it.

Part 3 – Update Flow Copy Files

Insert a new Scope action. This will be our “Try” for a Try-Catch-Finally pattern. Add a SharePoint Get folder metadata using path action and set:

Site address to “varWebURL”
Folder Path to “/Shared Documents/General”

Insert another Scope action. This will be our “Catch” for the Try-Catch-Finally pattern. Add a SharePoint Create new folder action and set:

Site address to the “varWebURL” variable
List or Library to “Documents”
Folder Path to “/General”

Click the ellipse on the Scope action, select “Configure run after”. Set Scope – Try to “has failed” and click “Done”.

This means that Flow will create the General folder if check for the General folder fails.

Insert another Scope action. This will be our “Finally” for the Try-Catch-Finally pattern. Add a SharePoint Copy folder action and set:

Current Site Address to your source site that is hosting the project files
Folder to Copy to your source folder that is storing the project files
Destination Site Address to “varWebURL”
Destination Folder to “/Shared Documents/General”
If another folder is already there to “Replace”.

My completed and albeit lengthy, Flow is shown in the image below.

Test by creating a new row in the Projects list.

If all goes well your Flow will execute without errors, a new Teams team (1) with a Private Channel (2) will be created and the project files (3) will copied to the team!

I find it amazing that with a List and Power Automate you can piece together a Teams provisioning solution without too much fuss. I wouldn’t use this solution for provisioning at large scale (think thousands) but I would for small-to-mid scale (think hundreds) use cases where consistency, control and eliminating manual efforts are the goal. I reference a PMO throughout these posts as a source of inspiration but this solution could easily be tailored for uses like committee management or mass departmental team creation.

Thanks for reading.

14 thoughts on “Use MS Lists and Power Automate to copy files to MS Teams”

Amardip says:

January 11, 2021 at 9:58 pm

Hi Norm another great and useful blog.
Keep up the great work!
Question on Microsoft Lists – could it be used to track department risks?
In my department a risk can have 3 separate actions to help mitigate the risk. Also each action can have a separate team member owner.
Risk Action Action owner
Risk 1 Action A Peter B
Action B Sam T
Action C Craig S

Kind regards
Amardip

LikeLike

Reply
1. Norm Young says:
  
  January 12, 2021 at 1:01 pm
  
  For sure you can use Lists and Power Automate to track department risks.
  
  I would have two lists. A List to store the Risk level and owner and another List to store the Department risks. Power Automate would evaluate the risk level, pull the owner, and then execute the correct action.
  
  What do the risk actions look like?
  
  NY
  
  LikeLike
  
  Reply
Amardip says:

January 13, 2021 at 3:37 pm

Hi Norm
So for example the department risk could be “there is a risk project A will not be delivered on time and within agreed budget”.

The actions to mitigate this risk could be:
Acton 1 – understand if project deadline can be pushed back.

Action 2 – clarify if all functional requirements need to be implemented.

So in this example risk A has 2 actions.
Hope this helps.
Amardip

LikeLike

Reply
1. Norm Young says:
  
  January 13, 2021 at 6:50 pm
  
  Hi Amardip,
  
  Each evaluation (condition) would store the respective actions. Unless I am missing something, this is doable with Lists and Power Automate.
  
  That sounds like a good future blog post as well!
  
  NY
  
  LikeLike
  
  Reply
Amardip says:

January 19, 2021 at 12:55 pm

Thanks Norm! Would make a great blog!

LikeLike

Reply
Kevopoly says:

April 28, 2022 at 8:43 pm

Hey Norm, any idea why I would receive a “The response is not a JSON format.” error message during the scoped try,catch,finally actions?

LikeLike

Reply
1. Norm Young says:
  
  May 2, 2022 at 4:55 pm
  
  Hi Kevopoly,
  
  Where in the Flow is the error and what is the error msg?
  
  Thanks for reading!
  NY
  
  LikeLike
  
  Reply
lolthisshitisridic says:

April 28, 2022 at 8:45 pm

Hey Norm,

Do you have any idea why I would receive an error message during the Try/Catch/Finally scoped actions? I get the error message using a variety of different URL/URI (encodingURIComponent, JSON, etc.) and nothing seems to work. I thought it was a race condition or timing issue, but adding a 5 minute delay didn’t work.

LikeLike

Reply
Jan says:

July 20, 2022 at 9:11 pm

Hello, thank you for this interesting series! I am struggling to save my 1:1 reproduction of your flow and cannot figure out what goes wrong. Error Message (it is in German…): Fehler beim Speichern des Flows. Code: InvalidTemplate, Meldung: “Fehler bei der Vorlagenüberprüfung: Die in “inputs” in Aktion “Variable_initialisieren_-_varWebURL” referenzierten Aktionen “Parse_JSON_-_Get_Group_Drive” sind nicht in der Vorlage definiert..”.

So, could it be that the “Parse_JSON_-_Get_Group_Drive” thing in the Initialize variable “varWebURL” value does not work anymore, or is it a problem with the template? Thank you for any ideas 🙂

LikeLike

Reply
1. Norm Young says:
  
  August 3, 2022 at 3:58 pm
  
  Hi Jan,
  
  Sorry about the delayed response. I’ve been busy working hard and enjoying the summer!
  
  There could be some localization issues that I would not have considered in this Flow pattern. Be sure to check the German equivalents of anything in the Compose output and action names that Power Automate may not understand if you are basing your Flow exactly on my steps.
  
  Thanks for reading!
  
  NY
  
  LikeLike
  
  Reply
Jan says:

July 23, 2022 at 8:08 pm

Hello Norm, I left a comment some days ago and asked about the “substring(body(‘Parse_JSON_-_Get_Group_Drive’)?[‘webUrl’], 0, lastIndexOf(body(‘Parse_JSON_-_Get_Group_Drive’)?[‘webUrl’], ‘/’))”-expression not working. Actually it turned out, that when using another environment locale (like German in my case), also the Methods need to be (partially) called by their German name (which is very very confusing!). So in my case the expression in step “Initialize variable – varWebURL” needed to be as follows: substring(body(‘JSON_analysieren_-_Get_Group_Drive’)?[‘webUrl’], 0, lastIndexOf(body(‘JSON_analysieren_-_Get_Group_Drive’)?[‘webUrl’], ‘/’)). The same applies to the path names for the sharepoint folders – I needed to put “Freigegebene Dokumente” instead of “Shared Documents” and “Dokumente” instead of the general “Documents” folder in the General-tab of a team. Actually, the flow is now working flawlessly and I want to thank again for the inspiration given in this series of posts!

LikeLike

Reply
1. Norm Young says:
  
  August 3, 2022 at 4:00 pm
  
  Hello again Jan!
  
  I’m glad you got it working. Debugging Flows is a great way of learning Power Automate but it isn’t always the most enjoyable experience 😉
  
  Thank you for you kind feedback. I appreciate it!
  
  NY
  
  LikeLike
  
  Reply
Olga Melo says:

October 29, 2022 at 6:39 pm

Hi Norm,
This content is very helpful. Thank you for posting.
I’m trying to provision a couple of folders in a newly created MS TEAM.
Team and channel are created and url update to the SP Project list.
But I can’t get the folders created. I get either that the response is not a JSON format or 404 Json. Not sure if I need to create a file first or a delay. I’ve tried many different things, and nothing seems to fix the issue.

LikeLike

Reply
1. Norm Young says:
  
  October 31, 2022 at 7:47 pm
  
  Hi Olga,
  
  It is hard for me to know without taking a closer look at your Flow. I would check the output and ensure that the varWebURL looks correct.
  
  Thanks for reading!
  
  NY
  
  LikeLike
  
  Reply