How to Sync MCommunity Group Members to a SharePoint List

Summary

University of Michigan's MCommunity allow users to self-manage group members, using either internal IDs (UniqNames) or external email addresses. Using MCommunity to drive filters, reporting, and access in other systems is ideal since it keeps U-M's preferred group management system as the source of truth. During the development of TrackMaster (a SharePoint-based membership tracking tool), the Depression Center saw a need to sync MCommunity group members to a contacts list in SharePoint Online, indicating for each contact the groups to which they belong.

This document presents three different sync approaches between MCommunity and SharePoint Lists that work within the security restrictions of Office 365 and Power Automate, including Microsoft's default Data Loss Prevention (DLP) policy which restricts calling 3rd-party APIs directly.
 

Steps

Group sync via PHP and RSS

This approach uses a PHP script, hosted by ITS Shared Web Hosting servers, to act as an API proxy between MCommunity LDAP and Power Automate. Power Automate gets group membership data using the RSS connector, which is allowed by both Michigan Medicine (HITS) and University of Michigan (ITS) environments. The RSS feed is a PHP script that queries LDAP anonymously and returns the data in XML RSS feed format. This approach is approved by ITS Information Assurance but is limited to groups marked as "Public" in MCommunity (anyone with an @umich.edu address can view the list of members).

Setup steps

1. Submit a request to ITS for a group AFS file share
2. Follow the instructions in the ITS knowledge base to setup permissions and folders for shared web hosting
3. Download the PHP script (or entire automation web API) from GitHub, and using FileZilla or Cyberduck, upload it to your group file share under: /afs/umich.edu/group/ (first letter of group name) / (group name) /Public/html/api/GetMCommunityGroupMembersRSS.php
4. Submit a request to ITS for shared web hosting, using the AFS file share setup above
5. Create a Power Automate flow to connect to the public URL for the web server and pass the group name in the URL. For example: https://automation.depressioncenter.org/api/GetMCommunityGroupMembersRSS.php?groupName=efdc-mobiletech@umich.edu
6. The RSS feed will contain the list of members - both internal and external - in CSV format under the Summary field. If the query produced errors, the category will be "ERROR" instead of "SUCCESS" and the Comments field will contain the error details
   
    
7. In Power Automate, grab the CSV from the RSS Summary (or Description) field, convert it to an array, and sync with your SharePoint list accordingly
   
   
   
   
    
8. Note that in the example diagram, we use two flows - one to query RSS and save to a CSV file, and other to read the CSV file and sync to the list. This is done for example purposes only so we can re-use the same flows across different approaches. See Power Automate code here: https://github.com/DepressionCenter/TrackMaster

Group sync via Python and OneDrive

This approach uses a Python script to connect to the MCommunity API and save the a list of group members as a CSV file. OneDrive client is used to map a SharePoint document library locally, so that when Python saves the CSV files, they are automatically synced to the corresponding document library. Power Automate then picks up the CSV files from SharePoint and syncs to the appropriate list.

Pros and cons of Python and OneDrive sync approach

Setup steps

1. Get the Python code from GitHub
2. Get an API key for "MCommunity Groups" from the ITS API team
   1. Create a "team" and an "app" in the API directory
   2. Get an API client ID and secret
3. Modify the JSON configuration file (from GitHub)
   1. Enter the list of groups to sync
   2. Enter the API client ID and secret from the previous steps
4. Request an Windows Server VM (virtual server) from the HITS Server team
   1. Ask for minimal specs, such as 8GB RAM and 50GB hard drive, to reduce costs
   2. Create an MCommunity group for the team members who will need access to the server, and provide the name of the group in the form
   3. Ensure these team members all have access to your SharePoint site and list
5. Once your VM server is ready, login to the server to setup OneDrive, Python, and a Windows Scheduled Task:
   1. Install the OneDrive for Business client app
   2. Install Python and PIP
   3. Login with an account with access to the SharePoint site and list to which group members will be synced
   4. Open your SharePoint document library in Edge browser, and click the "Sync" button in the toolbar
       
   5. Once the library is synced to the server via OneDrive, open Windows Explorer and navigate to the folder in which you will create CSV files
   6. Copy and paste the Python and JSON scripts into this folder
   7. Open a command prompt window, and navigate to the local folder where the script resides
   8. Run the script using Python and ensure there are no error messages. There should now be CSV files in this folder containing group members
       
   9. Setup a daily Windows Scheduled Task to run the Python script (run the Python executable and pass the path to the script as a parameter). Ensure the task runs under your user account
   10. Manually run the task once to ensure there are no errors. There should be new CSV files created
6. When the setup is complete, disconnect from the server but do not log off. This is extremely important, as your account must remain logged in for OneDrive to continue syncing
7. Create a Power Automate flow to loops through the CSV files, decodes the content (from base64), converts the file contents to an array, then syncs the array of group members to your list. See Power Automate code here: https://github.com/DepressionCenter/TrackMaster
   
   
   
   
   
   
   
   
   

Group sync via Office 365 groups

This approach lets the ITS IAM or ITS TDX iPaaS teams take care of syncing MCommunity to an Office 365 group. Power Automate can then query group members from the Office 365 group directly. This approach is approved by ITS IA, but it is limited to internal accounts (only those with a @umich.edu address).

Pros and cons of Office 365 groups sync approach

Setup Steps

1. Submit a ticket to ITS requesting that your specific MCommunity groups get synced to Office 365 groups (ask them to refer to ticket #5150572).
2. ITS will create the corresponding groups in Office 365 with a special prefix (which is required for their sync process to work), such as mcomm- (e.g. mcomm-efdc-mobiletech@umich.edu)
3. Create a Power Automate flow that uses the Office 365 Groups "List groups"connector to query group members
   
    
4. Pass the Group ID from the results to a "List group members" connector
5. Sync the results to your SharePoint list (or to a CSV file in SharePoint for a sub-flow to sync to a list). See GitHub for example flows.

Notes

• Please note neither the Eisenberg Family Depression Center nor the Mobile Technologies Core can provide technical support for either of these solutions. This article aims to explain working sync methods that other teams can implement on a "DIY" basis. However, please reach out to your Trusted Service Provider (TSP), ITS or HITS to request support with specific steps

Resources

• Download architecture and data flow diagrams:   Visio format |  PDF format
• Mobile Technologies Core - Internal Tools and Automation Repository
• Eisenberg Family Depression Center - TrackMaster Docs | TrackMaster Repository
• Eisenberg Family Depression Center - Power Automate + SharePoint Lunch 'n Learn Recordings

About the Author

Gabriel Mongefranco is a Mobile Data Architect at the University of Michigan Eisenberg Family Depression Center. Gabriel has over a decade of experience in data analytics, dashboard design, automation, back end software development, database design, middleware and API architecture, and technical writing.   |   |