Generate a table entry from an HTTP request

[jleystral]

Hi guys,

In a SharePoint list we have multiple site addresses and SharePoint group names. With an automated workflow we would like to have a regular report about the members of each group.

I am not getting anywhere with the point where I play the Title object from the JSON for all listed members into an Excel table. Can anyone help me? I feel I need to work with Apply for each here, but I don't know what to do.

JSON: 

HTTP:

Thanks in advance for your kind support.

[Expiscornovus]

Hi @jleystral,

Would a csv file also be ok? Or does it have to be an Excel?

Below is an example with a csv

If you want to create an Excel you could potentially replace the Create CSV table by a Select and loop through the results of that Select to add rows to a Excel file instead.

1. The Create CSV uses the expression below in the From

outputs('HTTP-Anforderung_an_SharePoint_senden')?['body']['d']['results']

2. In the Columns select custom and map/use the columns you want. Use the item()?['nameoffield'] format

 

[jleystral]

Thats great. CSV is fine.

But I am not sure about doing this with multiple website adresses and multiple groups per site adresse. Because my HTTP request is in a "apply for each" and therefore i would get one csv file per sp group. I would like to have one overview with column 1 is website adresse, column 2 is group name and in column 3 there are all the group member titles. So I get one file and can search for a specific user and see, in which groups he/she is.

[Expiscornovus]

Hi @jleystral,

Instead of creating a csv from the response of each group result you could append it to an array variable.

You could use the array variable as input for the create csv table action after you finished looping through all the groups.

[jleystral]

I've already tried that. But I dont know how to append the titles and how to create the csv in the end.

[jleystral]

[jleystral]

So no one knows how to deal with it?

[Expiscornovus]

Hi @jleystral,

Apologies that I missed your replies. I get a lot of notifications on these forums 😅

Below is an example of using a variable of type array to store the results of each group and use it in a csv.

1. Per Group the members are retrieved

2. A Select uses the same outputs function as before

3. A Compose combines the results from a previous loop

4. A Set variable updates the varArray variable with the outputs of the Compose action

5. Outside the loop you should be able to use the varArray variable in the Create CSV table action

[jleystral]

Thank you so much for your effort to help me.

I did what you wrote and got this error message:
 

I am a little bit confused because both should be an array, right? The Variable and the  results from the http request. 

[Expiscornovus]

Hi @jleystral,

That action with the name Select? Is that an actual Select action?

It looks a lot like it is a Create CSV Table action which you renamed to Select? 😁 

The reason I am asking this is because I see a Format field (with a CSV value) in the Inputs (Eingaben) section of that action with the name Select.

Which is not a configuration which is available in the standard Select action 😉

[jleystral]

Haha you are so right. 😄 That was an create csv action indeed.

I changed that and now I am getting this error message. Is the output of the compose a string or an array?

[Expiscornovus]

Hi @jleystral,

The output of the union function within the compose should be an array.

What expression did you use in the compose?

[jleystral]

Hi Expiscornovus,

I used this: 

Auswählen = Select 

[jleystral · Accepted solution]

I did a quick workaround that suits for me:

Not beautiful but it works

Thanks again so much for your help Expiscornovus . This forum is great.