Get Microsoft Lists Comments using Flow

[hdonato]

Hi, Everyone
Would like to know if there's a way to get the comments on a list item using power automate? or a way to notify users when new comment is added?

Would greatly appreciate any help.

Thank you.

[Expiscornovus · Accepted solution]

Hi @hdonato,

There is no trigger event for this. But you could collect comments via the SharePoint REST API.

Below is an example.

Use the Send an http request action with a GET request and the following URI:

_api/web/lists/getbytitle('@{variables('ListName')}')/items(@{variables('ListItemId')})/Comments

Replace the ListName and the ListItem id with your values.

[hdonato]

Hi, @Expiscornovus 

This is awesome. Thank you. 
But another question, how could I get the last comment only? 

Thank you in advance. 🙂

[Expiscornovus]

Hi @hdonato,

I believe this query will always return the comments based on id in descending order. So, it should be sufficient to just collect the first item.

You can for instance use this expression from the response:

outputs('Send_an_HTTP_request_to_SharePoint')?['body']['d']['results'][0]

[hdonato]

Hi, @Expiscornovus 
I didn't know that's the way it returns the comments. I should have test it. 😄
Greatly appreciated your help!

[GuyBoswell]

Hi All,

I have followed steps suggested here and I can either get all the comments but each one buried inside a paragraph of code, or I can get the last comment in plain text.  What I want is something like:

Jo Bloggs - 12 Jul 21

My comment is that we should do this

Jane Brown - 13 Jul 21

My comment is I agree

Ruth Wilson - 14 Jul 21

My comment is lets not bother

Obviously users wouldn't really start their comment, "My comment is ...", I just put that in for demonstration purposes.

Can anyone guide me how to achieve this?

Thank you

Guy

[Expiscornovus]

Hi @GuyBoswell,

You could use a select to only retrieve the field from the comments that you want to use. And to get that specific date format (dd MMM yy) you can use a formatdatetime function.

Below is an example of that approach.

The select action uses the results array in the From:

outputs('Send_an_HTTP_request_to_SharePoint_-_AllComments')?['body']['d']['results']

And it uses this for the mapping (switch map to text mode, to copy paste the below):

{
  "Name": @{item()['author']['name']},
  "Date": @{formatdateTime(item()?['createdDate'], 'dd MMM yy')},
  "Text": @{item()?['text']}
}

[GuyBoswell]

Hi @Expiscornovus ,

I already have the, "Send an HTTP request to SharePoint", action and that works, but I have now renamed it to, "Send an HTTP request to SharePoint - AllComments", to match your example.

I have added a Select action

I pasted in to the box, "From", your statement beginning, "Outputs ..."

I pasted in to the box, "Map", your statement in curly brackets

But it will not save because, "Enter a valid JSON"

I copied and pasted Send an HTTP request name from your from statement.  That includes underscores but Power Automate automatically replaced underscore in the name with spaces.  So I tried deleting all underscores, hyphens and spaces from both but that didn't help

Any ideas?

Thank you very much for your help

P.S. when I tried to post this it told me it included invalid HTML which has been removed?! 

[Expiscornovus]

Hi @GuyBoswell,

Can you try and copy paste this instead?

@{outputs('SendanHTTPrequesttoSharePointAllComments')?['body']['d']['results']}

I was copy pasting that expression via the expression editor. I think you pasted it directly as text.

Edit: And that switch to text for the Map fields doesn't work, my bad. Switch to key value mode and copy paste the expressions in the value fields instead.

[GuyBoswell]

No, it doesn't like those.

[Expiscornovus]

Yeah, I noticed that myself 😅. Attempt #3. Just copy paste the individual expression per field in the value column via the expression editor. So, those are the same expressions only copy pasting it without the '@{yourexpression}' format.

Below is an example with the text field.

item()?['text']

[GuyBoswell]

Brilliant!  Making progress.  I can save and run the flow without error.

Now how do I get the information in to an email?

I have an email, that all works, but what do I put in to the email to display the comments there?  There doesn't seem to be an output from Select I can use in the email.

I can use the output from Select as Input to a Compose action and add the output from Compose to the email.  But that just adds this text to the email ...

[{"Name":"item()['author']['name']","Date":"formatdateTime(item()?['createdDate'], 'dd MMM yy'","Text":"item()?['text']"},{"Name":"item()['author']['name']","Date":"formatdateTime(item()?['createdDate'], 'dd MMM yy'","Text":"item()?['text']"},{"Name":"item()['author']['name']","Date":"formatdateTime(item()?['createdDate'], 'dd MMM yy'","Text":"item()?['text']"},{"Name":"item()['author']['name']","Date":"formatdateTime(item()?['createdDate'], 'dd MMM yy'","Text":"item()?['text']"}]

[Expiscornovus]

Hi @GuyBoswell,

Probably the easiest approach could be to use a create html table action which uses the output of the select action. A send an e-mail action could use the output of that create html table action in the body.

Below is an example of that.

[GuyBoswell]

Fantastic!  Got it all working now.

One oddity - if a comment includes an @ it returns something like this for the text of the comment,

@mention{0} Have a look at this

You have been so helpful @Expiscornovus , thank you

[Tudelati]

Hi @GuyBoswell 

Any chance of sending me your flow? I am struggling to do the same. Thank you.

[GuyBoswell]

Hi @Tudelati ,

It was all working fine last week - now it has stopped working.  I suspect something has changed in the schema and this method no longer works.  I don't understand it well enough to do anything other than copy and paste other peoples solutions so I have taken comments out of my emails for now

Guy

[Expiscornovus]

Hi @GuyBoswell,

I have retested the flow I created back in July and the approach is still working for me. In my tenant it doesn't look like the schema has changed. 

This was is still my setup in the Select action btw:

{
    "inputs": {
        "from": "@outputs('Send_an_HTTP_request_to_SharePoint_-_AllComments')?['body']['d']['results']",
        "select": {
            "Name": "@item()['author']['name']",
            "Date": "@formatdateTime(item()?['createdDate'], 'dd MMM yy')",
            "Text": "@item()?['text']"
        }
    }
}

[GuyBoswell]

Hi @Expiscornovus 

In editor I see this ...

Peeking the code view I get

{

    "inputs": {

        "from": "@body('Send_an_HTTP_request_to_SharePoint_-_AllComments')",

        "select": {

            "Name": "@item()['author']['name']",

            "Date": "@formatdateTime(item()?['createdDate'], 'dd MMM yy')",

            "Comment": "@item()?['text']"

        }

    }

}

What do I put where to make it work?

[Expiscornovus]

Hi @GuyBoswell,

The value in your from is different. I am looking at the results array of the body, which is nested in 'd'.

You are directly looking at the body. Can you change the from field to use this expression instead?

outputs('Send_an_HTTP_request_to_SharePoint_-_AllComments')?['body']['d']['results']

[GuyBoswell]

In the Select statement From box, I added a function and pasted your text in to the expression.  When I try to save I get an error,

"The template validation failed: 'The action(s) 'Send_an_HTTP_request_to_SharePoint_-_AllComments' referenced by 'inputs' in action 'Select' are not defined in the template.'."

Thank you so much for all your help 🙂