Pull Comments From SharePoint List Items Into a Field

This is a blatant copy from Ellis's excellent post here.  He really did a great job of laying this out and documenting a Power Automate flow in a way that can be readily copied/applied.  The only issue being that the new Power Automate experience of course doesn't quite treat JSON blobs the same and you need to do a minor adjustment.

However, it is worth understanding how to get some of the additional details out of a SharePoint list (like Version History) and to include it in a way that can be exported/archived as items age out of a process.  

To pull this off, you'll need to use an API call to SharePoint to access the comments.  Then you will need to process the JSON returned and choose what Comment details you'll want to include (e.g. when, who, what).

The Basic Flow

Essentially your flow will:

• Initialize a basic variable for the merged Comments
• Get Items from SharePoint
• Loop through each ITEM
• Call the SharePoint HTTP API
• Get the Comment(s)
• Parse the JSON returned
• IF there are any Comments then
• Clear the Comments Variable
• Loop through each COMMENT
• Append the COMMENT to the Comment variable
• Update the SharePoint list Item with the value of the Comment variable

Initialize the Comments Variable

Nice when it all starts simple.  Just add an Initialize Variable step to your Flow, name it Comments, and leave the Value blank.  

NOTE: I changed the name of my step here to Init Comments.

Get Values from SharePoint

Again, this is fairly straightforward, just Add/Configure a SharePoint Get Items step to retrieve the appropriate items from the specific list in question.  

Start to Loop Through the ITEMS

Now add an Apply To Each step to your flow and configure it to use the body/value returned from the Get Items step

NOTE: I changed the name on this step to For Each ITEM for clarity

Call the SharePoint API to Get the Comments

Now we get to the hard part, so follow this closely.  You may need to return to this section and go back through it.  It is easy to make mistakes here.

Add the Send an HTTP request to SharePoint step after your step above (inside the loop).

I left the guid for a test list in place so you can see what it looks like

Point this step at your Site where your list exists and use the GET method.  

Don't paste this in yet, but this is essentially what we'll wind up with for the URI field:

_api/web/lists(guid'your_GUID_here')/items(current_Loop_ID_here)/comments

However, to get to this point we're going to start with the current_Loop_ID_here piece first. Take the following code as a starting point:

@items('For_each_ITEM').ID

One of the (many) problems with Power Automate is how to reference things that don't readily appear in the User Interface. For example, our Loop through the Items is essentially just going through each item in the SharePoint list. In effect, each item has the same fields that comes out of our Get Items step originally. However, the UI doesn't show that. So you need to manually populate this out.

That being said, you can't just type the above into a field within Power Automate's current UI and have it recognized. So how do you get Power Automate to recognize this completely valid code (assuming you also renamed your Apply to Each step to For Each ITEM)?

Copy/Paste the above line of code (i.e. @items('For_each_ITEM').ID) into Notepad. Change the blue text to reflect the name of your prior step if you need to (and replace any spaces in the name w/ underscores). Now copy/paste from Notepad into your Power Automate URI field above.

Every Power Automate User

Power Automate should recognize this as a valid reference and change the plain text to instead look similar to a variable.

Now, after your variable, paste in the following from the line above:

)/comments

Then click in front of your variable and paste in the following from the line above:

_api/web/lists(guid'')/items(

Now we need to get your GUID

To get the GUID of your list, scroll back up to your Get Items step from above. Click on the Code View and find the row that starts with "table":

Copy the value between the double quotes as that is the GUID of the Table in SharePoint. Copy/paste that value and replace the your_GUID_here above and leave the single quotes around it.

Finally, you're going to add two Headers to this step. They will be named Accept and Content-Type specifically.  Copy/paste the value below into each field:

application/json;odata=verbose

NOTE: You can use the list name vs. the GUID when querying a SharePoint list via the API.  Some  others online reference that.  However, list names do change and GUIDs do not.  How much pain do you want in your life?

Compose Step

Add a Compose step and use the following as your starting point for the Inputs field:

@outputs('Send_an_HTTP_request_to_SharePoint').body.d.results

Assuming you have not renamed the prior step, this will work as-is.  Otherwise, rename the blue section to reflect the name of your step.

Now stop here, save, and run your app.  Fix any bugs by going back to the steps above and repeating them.

Parse JSON Step

To build a Parse JSON step it is best to have some sample JSON to work with.  That's why I told you to stop/save/run your app above.  If you are still in the viewer where it shows the results from the successful run then great.  Otherwise, exit the editor and open the last successful run of your app.

Click on the Compose step at the bottom (you might have to open your loop step first).  Click inside the outputs field and hit CTRL-A (or whatever your OS uses for select all) and copy everything.

We will use this data to train our Parse JSON step.

Go back into the edit mode for the Power Automate flow and add a Parse JSON step to your app just below the Compose step.  

In the Content field, use the dynamic content selection to add in the Outputs from your Compose step:

Now click on Use sample code to generate schema at the bottom of the new step.  Paste in everything you copied from above into the window that appears and click Done.

You should now see a schema similar to the following:

Note: While you sometimes need to scroll down and remove items in the required section from JSON schemas for things to work when items are not included, for the SharePoint API this should be identical for all SharePoint lists and all items.

Add a Condition Step

Now add a Condition step to your app.  It will have a single check to see if there are any Comments for the current item we're looping through.  You'll use the following code:

length(body('Parse_JSON'))

Assuming you did not change the name of your Parse JSON step, then this will work as-is, otherwise, change the blue text to reflect the correct name.

Set that to see if it is greater than 0.

The result will be that if there is nothing in the list, then we have nothing to do (no Comments).

Add a Set Variable step on the TRUE path

Add a Set Variable step under the True branch of the Condition step.  Set the variable to be Comments that we initialized at the very beginning.  For the Value, use the Equation editor and type in null and hit Update.  

NOTE: You can't just type null or leave the Value blank

Add an Apply To Each step

Add an Apply to Each step to your app.  

Note: I renamed mine to be For each COMMENT

Set the Select an output from previous step field to be the output from the Compose step several steps back:

Add an Append Variable step

Now add an Append Variable step under our Apply To Each loop.  Now this is where you can decide what data you want to save from the comments.  I've included the code to pull the DateTime, Commentor name, and the Comment and put it into a pip-delimited list with a new line character between comments.

Select the Comments variable as the one we're going to update.  In the Value field, click on the expression editor and use this as a baseline for what you might want to include:

concat(items('For_each_COMMENT').createdDate,'|',items('For_each_COMMENT').author.name,'|',items('For_each_COMMENT').text,decodeUriComponent('%0A'))

Again, the blue text should reflect the name of your previous step.

NOTE: If you look at the JSON from one of your test runs that you copied from above, you can decide which value from the Comments bundle you want to retain.

Update SharePoint

Now after your Append Variable, but outside of the Apply To Each loop right before it, add in an Update Item step from the SharePoint choice.

Select your same site/list as above.  For the ID field, select the ID from the first loop (not the second).

Then select whichever column you are wanting to store the Comment details in, ExportedComments in my example, and add the Comments variable as the value to update it with.

Troubleshooting

It is definitely likely you're going to mix up a name somewhere along the way, forget the underscores,  leave out single quotes, or use double instead of single quotes.  Hand typing equations and copy/pasting them into Power Automate can be a PiTA.  

I would point out that if you copy an equation into a blank field and it doesn't immediately turn into something that looks like a variable vs. just the raw text you copied, then Power Automate did not understand your code and there is a problem.

Also, since you're going to be nesting loops, make sure you name them something that makes sense to you.  It is far too easy to pick the wrong one if you don't name them properly.

Final Thoughts

This is one of those more "advanced" processes that comes up when you're doing Archival of items from SharePoint.  Certain data is not automatically copied if we are running an archive routine that you might want to retain (Version History is another example).  

There are a number of SharePoint HTTP REST API methods out there.  So don't limit yourself to simply this one.  You can access many other bits of data within SharePoint indirectly using some of these methods.

However, I do want to point out that Microsoft does seem to want us to stop using SharePoint.  I have limited trust that they will continue support for some of these APIs indefinitely into the future.  Many are far too useful to be allowed to continue.  Meaning, that so long as we can use these, we might not use Dataverse, which might not allow Microsoft to charge us extra, and also not to have an easier way to convince us to click on Co-Pilot, allowing them to brag at their next quarterly board meeting of what % of users are using Microsoft AI...

Just be careful is my point and try to prepare for if (when) they alter our ability to access these APIs openly and for free.