Export Sharepoint list items with comments into CSV file

[Mag1c]

Hi everyone, I've followed this blogpost (https://divyeshkotadia.wordpress.com/2023/04/21/how-to-export-list-items-with-comments-into-excel-cs...) and it has me 95% of the way there but there are two issues.

1. The @mentions in comments when they are linked in o365 do not show the username/email of the individual but the replace function the creator used. (if you are viewing the blogpost this is the step I'm referring to Replace mentions in comment)

For instance it should be @John Smith but it says @mention{0} instead. I'm not quite sure why this is and how to resolve it. We have sometimes 10+ comments on a single Sharepoint list item with multiple people mentioned in the comment so I would love to get this resolved.

2. This issue is less important but the comments are listed newest to oldest, how can I get them to list oldest to newest (first comment to latest comment).

Thank you in advance!

[Expiscornovus]

Hi @Mag1c,

Have a look at the Read ALL comments part of this blog from @DamoBird365, https://www.damobird365.com/microsoft-list-add-comment/

That blog demonstrates how to replace those references with the actual names of the people mentioned by using a couple of Select actions and some expressions with split and replace functions.

[Mag1c]

I appreciate your response. I tried following the blog but I get an error “the action join_objects is nested in a foreach scope of multiple levels. Referencing repetition actions from outside the scope is supported only when there are no multiple levels of nesting"

This is due to the REST API call to fetch all comments being a loop because I want to pull all of the records and its comments from a list, not just a single record and its comments. Any ideas on how to work around this?

[Expiscornovus]

Hi @Mag1c,

It should also be possible to retrieve all comments from all items.

Because you already have a for each loop that one from the blog would be a nested one. That also means that you would have to update the expressions which are being used in the blog to point to the right for each loop action. Make sure that is is using the correct action name in the items function.

Can you share a screenshot of how you added the actions from the blog. This way I can easier point to what you need to change/update to make this work.

[Mag1c]

Sure, I've attached 3 screenshots of my flow. I changed the original quite a bit to add in the parts from the blog you mentioned and haven't cleaned it up yet as I'm testing so there are still some variables listed that won't be needed once it's working.

[Mag1c]

Hello @Expiscornovus @I provided 3 screenshots if you’re able to assist me with the error. Thanks!

[Mag1c]

Bumping this topic in case anyone can assist, thanks!

[Expiscornovus]

Hi @Mag1c,

Apologies for the delayed reply and thanks for sharing those screenshots. 

It looks like the issue might be in your Join Objects action. However, it the screenshot it is collapsed. I cannot see what your are using in that action. Can you share what you used in that action?

[Mag1c]

I've just copied/pasted the expression from the article you posted:

if(equals(length(item()?['mention']),0),items('Apply_to_each')?['text'],concat(join(body('Select_Text_and_Replace_Mention'),''),last(outputs('Split_on_Mention_Compose'))))

[Mag1c]

I think the issue is with my expression in the compose action. The article says this

but if I put exactly that code in as an expression it gives me an error saying it's invalid

@{outputs('join_objects')}

If I try it as the below code, it saves the expression but when I click save on the flow is when I get the error below.

outputs('join_objects')

 Flow save failed with the code 'invalidtemplate' and message 'the template validation failed: 'the inputs of the template action 'compose' at line '1 and column '5290' cannot reference action 'join_objects'. The action 'join_objects' is nested in a foreach scope of multiple levels. Referencing reptition actions from outside the scope is supported only when there are no multiple levels of nesting.

[Mag1c]

Bumping again to see if anyone is able to assist with this?

[Expiscornovus]

Hi @Mag1c,

Sorry, I get a lot of notifications so I missed this one again, my bad 😅

My guess is that the compose is in the wrong location. Is that compose action outside the apply to each loop? If that is the case you will get this error message. Place the compose within the loop.

Btw, if you want to store all text you probably want to use a string variable (initialize variable action at the beginning of your flow) and use an append to string variable action at the end of each loop.

[Mag1c]

@Expiscornovus  My compose action was outside of the apply to each loop but even when I add it inside I still get an error:

Just to refresh as this has been an issue going on a couple of weeks... the initial flow I was using works but has the issue with the @mention in comments showing the code instead of the user's name. - https://divyeshkotadia.wordpress.com/2023/04/21/how-to-export-list-items-with-comments-into-excel-cs... this is the flow I'm referencing. 

You suggested this flow - https://www.damobird365.com/microsoft-list-add-comment/ , to resolve the issue, which it does but only for a single ID in a sharepoint list, not all the of rows/IDs in a sharepoint list which is what I need. I am attemping to export all of the comments out of the list one time.

Essentially, I just need help converting https://www.damobird365.com/microsoft-list-add-comment/ from one single ID to exporting all of the comments in a SP list to a spreadsheet. I tried combining the two referenced flows but am clearly having some issues. 

I hope that helps provide some clarity on this and you can help me resolve, I really appreciate it!

[Expiscornovus]

Hi @Mag1c,

I think you are pretty close. In that case I would suggest to add a Add a new row in table action directly after the Join Objects action (still within the Apply to each loop).

Below is an example

ID field of my table uses

items('Loop_through_items_for_comments')['id']

Comment Text field of my table uses the Outputs of the Join Objects action

outputs('Join_Objects')

Test result is the below

[Mag1c]

@Expiscornovus THANK YOU SO MUCH! I just have a couple of follow up questions, how can I add a column with the creator/user who submitted the comment (submitted by) and the date/time stamp of when they added it? I've tried a few things but I'm missing something.

Lastly, it looks like it's not handling special characters well, specifically the "&" and ":". For the "&" it says "&" and the ":" it says ":". Not really huge issues as I can resolve them with power BI, as that's how I plan to merge the comments to a single row anyways.

THANKS AGAIN SO MUCH, nearly there!

[Expiscornovus · Accepted solution]

Hi @Mag1c,

To add those fields you can include them in the first Select, directly after the Send an HTTP request to SharePoint action. You also need to change the expression for the Text field as well, that it does not include the date time anymore.

Below is an example

1. Changed the Select to use the below

{
  "Created": @{formatdatetime(item()?['createdDate'],'dd-MM-yy HH:mm')},
  "CreatedBy": @{item()?['author/Name']},
  "Text": @{item()?['text']},
  "Mention": @{item()?['mentions']?['results']}
}

 

2. Change the Add a row action to use the below of the two new fields

item()['CreatedBy']

item()['Created']

3. The export should now look like the below

[fireballs]

I have a similar issue- I can generate a correct replacement for the first @mention ID - but each @ mention after the first one returns the @mention{0} instead in the CSV 

[fireballs]

Figured this out by reseting the mentionID integer to -1 within the loop

New problem: multiple @ mention IDs do not get updated within the one comment, only the first one!

[fireballs]

Solved - moved the MentionID integer reset to AFTER the loop.