How to export list items with comments into excel/csv file using power automate?

In this blog I will take you step by step to export list item with comments into excel (csv) file using power automate.

1. We will begin with creating a cloud flow with trigger of your choice. I will be using manual trigger for this post.
2. After trigger let’s initialize few variables that will be used later in the flow.
   
3. Will use “Get items” action to get all list items for export.
4. Let’s loop through each item to get comments through REST API because “Get Items” action will not provide comments, hopefully in future MSFT adds some support for this 😀
   • REST API call to get a comment for each item
     _api/web/lists/getbyid(‘ListId’)/items(ItemId)/comments
   • 
   • Compose comments to get the comment text
   • Check if comments are empty and set ItemComments variable to “No Comments” value If there are no comments for current list item
     
   • If comments exist then we will loop through each comment to check user mentions (e.g. @divyesh) and format comments string accordingly. Note: Each actions mentioned in an image are explained in following steps.
     
   • First let’s reset ItemComments variable to null in “Yes” action block and start looping through comments to check for user mentions.
     
   • For comments with no mentions set ItemComments variable to
     ” Comment Text – Commented by <UserEmail> on <CreatedDate>\n” as shown below.
     Note: You can format string as per your requirement.
     
   • For comments with mentions we need to loop through it to find and replace each mentions with user email.
     
     Note: Comments string will have numeric reference to mentions like @mention{0} so we have to replace them with respective username or email. For this instance I am replacing it with email and converting ASCII value to string character e.g. @mention{divyesh@test.com}. In simple terms 0 has been replaced by user email and ASCII values decoded back to it’s original string characters
   • After Apply to each loop action let’s append the newly formatted string to ItemComments variable
     
   • Compose all comments
     
   • We will use Select action to build rows for CSV file with formatted comments
     
   • Loop through each rows and append that to array variable ExcelTableData
     
5. Now use “Create CSV table” action to generate the CSV formatted output for “Create file” action in next step
   
6. Create and store excel file on document library of your choice
   
7. Open the excel file and you will comments in CSV file as shown below
   

Well exporting only list items in excel is simple and few steps, however with comments it little complex and long. Hopefully this article will be helpful.

Sharing is caring 😀