I am creating a logic app that would get members from security groups and then writes that to a CSV file, or any file located on a sharepoint folder.
I have gotten so far that I can get the members of multiple groups in a for each loop. I am stuck at getting this in 1 single CSV.
The result should be the Name of the Group in column 1 and the email of the member in column2. I've tried many multiple things already, but cant seem to figure it out.
This is my current set-up that returns bad parsed results for just 1 group.
How should I handle this?
See below a sample of the result of 1 Group:
{
"statusCode": 200,
"headers": {
"Transfer-Encoding": "chunked",
"Vary": "Accept-Encoding",
"Strict-Transport-Security": "max-age=31536000",
"request-id": "76grrfc-3313-4103-8275-026e4e23777e",
"client-request-id": "76fb31fc-3313rrrr5-026e4e23777e",
"x-ms-ags-diagnostic": "{\"ServerInfo\":{\"DataCenter\":\"West Europe\",\"Slice\":\"E\",\"Ring\":\"5\",\"ScaleUnit\":\"000\",\"RoleInstance\":\"AM1PE00105B7\"}}",
"x-ms-resource-unit": "3",
"OData-Version": "4.0",
"Cache-Control": "no-cache",
"Date": "Tue, 29 Mar 2022 15:07:18 GMT",
"Content-Type": "application/json; odata.metadata=minimal; odata.streaming=true; IEEE754Compatible=false; charset=utf-8",
"Content-Length": "1724"
},
"body": {
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#directoryObjects",
"value": [
{
"@odata.type": "#microsoft.graph.user",
"id": "de2ae485-a578-439a-9bd2-991445a",
"businessPhones": [
" 12222"
],
"displayName": "Jaaa",
"givenName": "a",
"jobTitle": "aaa",
"mail": "aaaa",
"mobilePhone": " 0000",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
},
{
"@odata.type": "#microsoft.graph.user",
"id": "de2ae485-a578-439a-9bd2-991445a",
"businessPhones": [
" 12222"
],
"displayName": "Jaaa",
"givenName": "a",
"jobTitle": "aaa",
"mail": "aaaa",
"mobilePhone": " 0000",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
},
{
"@odata.type": "#microsoft.graph.user",
"id": "de2ae485-a578-439a-9bd2-991445a",
"businessPhones": [
" 12222"
],
"displayName": "Jaaa",
"givenName": "a",
"jobTitle": "aaa",
"mail": "aaaa",
"mobilePhone": " 0000",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
},
{
"@odata.type": "#microsoft.graph.user",
"id": "de2ae485-a578-439a-9bd2-991445a",
"businessPhones": [
" 12222"
],
"displayName": "Jaaa",
"givenName": "a",
"jobTitle": "aaa",
"mail": "aaaa",
"mobilePhone": " 0000",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
}
]
}
}
Error on CSV Table :
CodePudding user response：
In the Create CSV table option, you should be able to specify custom headers and then be specific about what you pull back from each item in your array.
I took your JSON and created my own flow to show just that.
Note: The @odata syntax fails to parse so I had to remove the @ sign when testing. See how that affects you with your real life scenario.
The two expressions are in the definition below but for completeness, they are ...
displayName = item()['displayName']
mail = item()['mail']
This is the result ...
This is the JSON definition that you can load into your own tenant for testing ...
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Create_CSV_table": {
"inputs": {
"columns": [
{
"header": "displayName",
"value": "@item()['displayName']"
},
{
"header": "mail",
"value": "@item()['mail']"
}
],
"format": "CSV",
"from": "@variables('JSON Array')"
},
"runAfter": {
"Initialize_Source_Array": [
"Succeeded"
]
},
"type": "Table"
},
"Initialize_Source_Array": {
"inputs": {
"variables": [
{
"name": "JSON Array",
"type": "array",
"value": [
{
"businessPhones": [
" 12222"
],
"displayName": "Jaaa 1",
"givenName": "a",
"id": "de2ae485-a578-439a-9bd2-991445a",
"jobTitle": "aaa",
"mail": "aaaa 1",
"mobilePhone": " 0000",
"odata.type": "#microsoft.graph.user",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
},
{
"businessPhones": [
" 12222"
],
"displayName": "Jaaa 2",
"givenName": "a",
"id": "de2ae485-a578-439a-9bd2-991445a",
"jobTitle": "aaa",
"mail": "aaaa 2",
"mobilePhone": " 0000",
"odata.type": "#microsoft.graph.user",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
},
{
"businessPhones": [
" 12222"
],
"displayName": "Jaaa 3",
"givenName": "a",
"id": "de2ae485-a578-439a-9bd2-991445a",
"jobTitle": "aaa",
"mail": "aaaa 3",
"mobilePhone": " 0000",
"odata.type": "#microsoft.graph.user",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
},
{
"businessPhones": [
" 12222"
],
"displayName": "Jaaa 4",
"givenName": "a",
"id": "de2ae485-a578-439a-9bd2-991445a",
"jobTitle": "aaa",
"mail": "aaaa 4",
"mobilePhone": " 0000",
"odata.type": "#microsoft.graph.user",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
}
]
}
]
},
"runAfter": {},
"type": "InitializeVariable"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {},
"triggers": {
"Recurrence": {
"evaluatedRecurrence": {
"frequency": "Month",
"interval": 12
},
"recurrence": {
"frequency": "Month",
"interval": 12
},
"type": "Recurrence"
}
}
},
"parameters": {}
}
CodePudding user response：
I've taken your array and created a flow using an approach I would use.
Note: The @odata syntax fails to parse so I had to remove the @ sign when testing. See how that affects you with your real life scenario.
The high level steps are:
- Create two arrays, one with the source data and the other with the CSV/destination data.
- Loop through each element in your source array and
Composean object that contains only the two fields you want to include in your CSV data. - Append that new object to the CSV/destination array.
- After the loop is done, write out the CSV/destination array to a CSV structure using the
Create CSV tableaction.
This is the end result ...
If you want to make sure the array is in the desired order, make sure you change the settings on the For each and turn concurrency off ...
This is the JSON definition that you can load into your own tenant for testing ...
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Create_CSV_table": {
"inputs": {
"format": "CSV",
"from": "@variables('CSV Array')"
},
"runAfter": {
"For_each": [
"Succeeded"
]
},
"type": "Table"
},
"For_each": {
"actions": {
"Append_to_array_variable": {
"inputs": {
"name": "CSV Array",
"value": "@outputs('Compose')"
},
"runAfter": {
"Compose": [
"Succeeded"
]
},
"type": "AppendToArrayVariable"
},
"Compose": {
"inputs": {
"displayName": "@{items('For_each')['displayName']}",
"mail": "@{items('For_each')['mail']}"
},
"runAfter": {},
"type": "Compose"
}
},
"foreach": "@variables('JSON Array')",
"runAfter": {
"Initialize_CSV_Array": [
"Succeeded"
]
},
"type": "Foreach"
},
"Initialize_CSV_Array": {
"inputs": {
"variables": [
{
"name": "CSV Array",
"type": "array"
}
]
},
"runAfter": {
"Initialize_Source_Array": [
"Succeeded"
]
},
"type": "InitializeVariable"
},
"Initialize_Source_Array": {
"inputs": {
"variables": [
{
"name": "JSON Array",
"type": "array",
"value": [
{
"businessPhones": [
" 12222"
],
"displayName": "Jaaa 1",
"givenName": "a",
"id": "de2ae485-a578-439a-9bd2-991445a",
"jobTitle": "aaa",
"mail": "aaaa 1",
"mobilePhone": " 0000",
"odata.type": "#microsoft.graph.user",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
},
{
"businessPhones": [
" 12222"
],
"displayName": "Jaaa 2",
"givenName": "a",
"id": "de2ae485-a578-439a-9bd2-991445a",
"jobTitle": "aaa",
"mail": "aaaa 2",
"mobilePhone": " 0000",
"odata.type": "#microsoft.graph.user",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
},
{
"businessPhones": [
" 12222"
],
"displayName": "Jaaa 3",
"givenName": "a",
"id": "de2ae485-a578-439a-9bd2-991445a",
"jobTitle": "aaa",
"mail": "aaaa 3",
"mobilePhone": " 0000",
"odata.type": "#microsoft.graph.user",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
},
{
"businessPhones": [
" 12222"
],
"displayName": "Jaaa 4",
"givenName": "a",
"id": "de2ae485-a578-439a-9bd2-991445a",
"jobTitle": "aaa",
"mail": "aaaa 4",
"mobilePhone": " 0000",
"odata.type": "#microsoft.graph.user",
"officeLocation": "aaa",
"preferredLanguage": null,
"surname": "aaa",
"userPrincipalName": "aaa"
}
]
}
]
},
"runAfter": {},
"type": "InitializeVariable"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {},
"triggers": {
"Recurrence": {
"evaluatedRecurrence": {
"frequency": "Month",
"interval": 12
},
"recurrence": {
"frequency": "Month",
"interval": 12
},
"type": "Recurrence"
}
}
},
"parameters": {}
}