One of our clients needed to be able to count the number of comments on their SharePoint news pages so they could see which pages were getting the most interaction. Annoyingly, there's not a way to do this through the UI so let's see what we can do with Power Automate!
As you can see below, Daniel has created a news article and mentioned me in it. I have left a new comment and also replied.
First, I need to grab all the pages from the site pages library so I can check them for comments. As always, this is a basic approach, but we could apply some filters to this trigger to maybe only collect specific types of pages if we have tagged them with a bit of metadata for example.
I am triggering this flow manually for testing purposes, but you could set this up to run on a schedule if you wanted to automate it a bit more.
Next create a variable to store the count of comments against a page.
Next, we need to build the counting steps for each page.
Using the HTTP action we can get the comments from the page.
_api/web/lists/getbytitle('Site Pages')/items(@{items('Foreach')?['ID']})/CommentsNext we need to parse the response to use the values. You can use the below Schema and use the body of the HTTP request.
{
"type": "object",
"properties": {
"d": {
"type": "object",
"properties": {
"results": {
"type": "array",
"items": {
"type": "object",
"properties": {
"__metadata": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"uri": {
"type": "string"
},
"type": {
"type": "string"
}
}
},
"likedBy": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"replies": {
"type": "object",
"properties": {
"__deferred": {
"type": "object",
"properties": {
"uri": {
"type": "string"
}
}
}
}
},
"author": {
"type": "object",
"properties": {
"__metadata": {
"type": "object",
"properties": {
"type": {
"type": "string"
}
}
},
"directoryObjectId": {},
"email": {
"type": "string"
},
"expiration": {},
"id": {
"type": "integer"
},
"isActive": {
"type": "boolean"
},
"isExternal": {
"type": "boolean"
},
"jobTitle": {},
"loginName": {
"type": "string"
},
"name": {
"type": "string"
},
"principalType": {
"type": "integer"
},
"userId": {},
"userPrincipalName": {}
}
},
"createdDate": {
"type": "string"
},
"id": {
"type": "string"
},
"isLikedByUser": {
"type": "boolean"
},
"isReply": {
"type": "boolean"
},
"itemId": {
"type": "integer"
},
"likeCount": {
"type": "integer"
},
"listId": {
"type": "string"
},
"mentions": {
"type": "object",
"properties": {
"__metadata": {
"type": "object",
"properties": {
"type": {
"type": "string"
}
}
},
"results": {
"type": "array",
"items": {
"type": "object",
"properties": {
"email": {
"type": "string"
},
"id": {
"type": "integer"
},
"loginName": {
"type": "string"
},
"name": {
"type": "string"
}
},
"required": [
"email",
"id",
"loginName",
"name"
]
}
}
}
},
"modifiedDate": {},
"parentId": {
"type": "string"
},
"replyCount": {
"type": "integer"
},
"text": {
"type": "string"
}
},
"required": [
"__metadata",
"likedBy",
"replies",
"author",
"createdDate",
"id",
"isLikedByUser",
"isReply",
"itemId",
"likeCount",
"listId",
"mentions",
"modifiedDate",
"parentId",
"replyCount",
"text"
]
}
}
}
}
}
}To work out the total number of comments we can use the below to get the total number of comments.
length(body('Parse_JSON')?['d']?['results'])Lastly pass this back into the update action to add the count to the site column.
This workflow captures the main comments, however, we could extend this workflow to get the number of replies, who commented and more! Maybe one for another post.