Loading...

Populate SharePoint List mulitple choice column with Microsoft 365 Groups-and add some List formatting

Populate SharePoint List mulitple choice column with Microsoft 365 Groups-and add some List formatting

Recently, a customer asked me if I could automagically populate Microsoft 365 Group Names to a multiple choice column in a SharePoint list. Of course I told them that one can create an ootb Person field and allow group selection, but that not quite what they wanted. So I built a simple Power Automate flow that would populate the choice column and and applied some nice list formatting to it.

The Power Automate flow

flow in full

Get your groups

  • We trigger the flow as per our needs (manually, if there is not a lot of change in groups) or on a schedule (if there is a lot of changes in groups)
  • we then initialize a variable (I used string, but you can also do an array) for the groupNames
  • then we use the Azure AD Groups - List Groups action to list all groups. Filter if needed!
  • Then we append the Name (and a comma) in a loop to our groupNames variable

flow first part

Obtain listguid and fieldguid

Now we need to obtain 2 things

  1. the listguid of our SharePoint list: Select the Settings gear ⚙️ on your list, List Settings, you will find it in the URL. The List guid sits right in between %7B and%7D (Of course there are a gazillion of other ways to obtain this, but that is a super easy one)
  2. the fieldguid of the field that we are trying to populate: You can either follow this post here or stick with me and:
  • Use Send an HTTP request to SharePoint
    • Method: Get
    • URI:_api/web/Lists/GetById('@{variables('listGuid')}')/Fields

This will return a massive json object from which you can now find the id of the field you are interested in.

Patch the choice column

As a last step, we just patch our choice column with the groupNames. As we comma-separated the variable, we need to make sure that we remove that comma now:

  • Use Send an HTTP request to SharePoint
    • Method: Patch
    • URI:_api/Web/Lists(guid'listGuid')/Fields(guid'fieldguid')
    • Headers:
      • Accept: application/json;odata=verbose
      • Content-type: application/json;odata=verbose
  • Body:
{
"__metadata": {
"type": "SP.FieldMultiChoice"
},
"FieldTypeKind":15,
"Choices": {'results':[@{substring(variables('groupNames'),0, sub(length(variables('groupNames')),1))}]}
}

send http to SP

If we now check in SharePoint, this already looks good, but I want to improve the experience with some colors.

Some List Formatting

What I want is a refection of which department is selected - remember that this is a multiple choice field? Let’s make that happen! choice column

  • Go tou your list
  • Select the multiple choice field > Column settings > Format this column
  • Select Advanced mode
  • Delete the code in the box
  • Paste in this snippet
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"children": [
{
"elmType": "div",
"txtContent": "=if(indexOf(@currentField, 'Dept-Finance') >= 0, 'Dept-Finance', '')",
"style": {
"display": "inline-block",
"width": "25%",
"background-color": "=if(indexOf(@currentField, 'Dept-Finance') >= 0, '#ffcc99', 'transparent')",
"color": "black",
"text-align": "center",
"padding": "5px"
}
},
{
"elmType": "div",
"txtContent": "=if(indexOf(@currentField, 'Dept-IT') >= 0, 'Dept-IT', '')",
"style": {
"display": "inline-block",
"width": "25%",
"background-color": "=if(indexOf(@currentField, 'Dept-IT') >= 0, '#ccffff', 'transparent')",
"color": "black",
"text-align": "center",
"padding": "5px"
}
},
{
"elmType": "div",
"txtContent": "=if(indexOf(@currentField, 'Dept-HR') >= 0, 'Dept-HR', '')",
"style": {
"display": "inline-block",
"width": "25%",
"background-color": "=if(indexOf(@currentField, 'Dept-HR') >= 0, '#ff9999', 'transparent')",
"color": "black",
"text-align": "center",
"padding": "5px"
}
},
{
"elmType": "div",
"txtContent": "=if(indexOf(@currentField, 'Dept-Marketing') >= 0, 'Dept-Marketing', '')",
"style": {
"display": "inline-block",
"width": "25%",
"background-color": "=if(indexOf(@currentField, 'Dept-Marketing') >= 0, '#b3ffcc', 'transparent')",
"color": "black",
"text-align": "center",
"padding": "5px"
}
}
]
}

How does this list formatting work?

We separate into the number of <div> we need (note this is just an example, there were way more groups to select from) and that set the width to the percentage the <div> may consume. The colors are applied dynamically using indexOf(@currentField, 'Dept-Name') >= 0, which checks if the department is selected in the column. Each part of the cell shows the department name if it is selected. If the department is not selected, the <div> will be empty and have no background color (transparent).

Conclusion

Good old Send and HTTP request to SharePoint saved the day again! No need for app registrations, but a super straight forward flow that populates the multiple choice colum to our needs. Of course you don’t need to do groups, but can apply any array that you fits your use case!

Published on:

Learn more
Luise Freese: Consultant & MVP
Luise Freese: Consultant & MVP

Recent content on Luise Freese: Consultant & MVP

Share post:

Related posts

Office 365 for IT Pros December 2024 Update

The Office 365 for IT Pros writing team is thrilled to announce the availabilility of the December 2024 update (monthly update #114). Current ...

9 hours ago

SharePoint PnP Viva Connections & SPFx JS SIG Call – November 28th, 2024 – Screenshot Summary

Community Call Highlights   SharePoint Quicklinks: Primary Community Websites: https://aka.ms/m365pnp —– PnP Sharing Is Carin...

3 days ago

Viva Engage: Additional permissions added to the corporate communicator role

If you're a corporate communicator on Microsoft Viva, you'll soon have additional communication management tasks at your disposal without the ...

4 days ago

Microsoft 365 Copilot now available to Viva Insights analysts

Microsoft 365 Copilot is now available to Viva Insights analysts to simplify the query-building process for data analysis. Copilot assists ana...

4 days ago

Teams Toolkit for Visual Studio Code update – November 2024

We’re excited to announce Teams Toolkit for Visual Studio Code v5.10.1 and the latest prerelease update. This patch brings impactful new featu...

4 days ago

Teams Toolkit for Visual Studio Code update – November 2024

We’re excited to announce Teams Toolkit for Visual Studio Code v5.10.1 and the latest prerelease update. This patch brings impactful new featu...

4 days ago

Promotional access to Agents in SharePoint

Starting from January 6, 2025, organizations with 50 or more Microsoft 365 Copilot licenses will gain automatic access to 10,000 additional qu...

5 days ago

Microsoft 365 admin center: Business Chat usage in Microsoft 365 Copilot usage report

The Microsoft 365 admin center is set to roll out an update to the Microsoft 365 Copilot usage report, which will track total usage of Busines...

5 days ago

Microsoft Teams Phones: Remotely manage contacts in Teams admin center

Attention Microsoft Teams admins! A new feature will soon be available in the Teams admin center that allows remote management of contacts for...

5 days ago

Microsoft Teams admin center: Activity log for Teams Phone device management

The Teams admin center just got better with the addition of a new feature - Activity log for Teams Phone device management. Teams administrato...

5 days ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy