How to Count Items in a SharePoint List with Power Automate

Do you need to count the number of rows in your SharePoint list data?

SharePoint lists allow you to create subtotals that are viewable from within your list, but what if you need this information when a flow based on your list runs in Power Automate?

You can use the SharePoint API along with Power Automate to get the count of items in your list.

Count Items with Send an HTTP Request to SharePoint Action

The Send an HTTP request to SharePoint action is extremely versatile and will allow you to perform any tasks available through the SharePoint API.

This means you will be able to use it to count the items in any list.

  1. First, you will need to add the Send an HTTP request to SharePoint action to your flow.
  2. Select the SharePoint site from the Site Address dropdown list which contains the list you want to count.
  3. Choose the GET option in the Method dropdown.
/_api/web/lists/getbytitle('List Name')/ItemCount
  1. Add the above to the Uri input. Just replace List Name with your own list name. This is the endpoint required to count the items in your list with the SharePoint API.
  2. Add Accept and application/json;odata=verbose to the first item of Headers input.
  3. Add Content-Type and application/json;odata=verbose to the Headers of the request.
{
  "Accept": "application/json;odata=verbose",
  "Content-Type": "application/json;odata=verbose"
}

These headers can be input as a single JSON object by clicking on the Switch Headers to text mode button and pasting in the above JSON.

Get the Count from the Output

When the Send an HTTP request to SharePoint action runs, it will return the above output.

Unfortunately, this won’t appear in the dynamic content options. But you can easily get the value from the output.

@{body('Send_an_HTTP_request_to_SharePoint')?['d']?['ItemCount']}

If you want to access the count number in the output then you can use the above expression.

Alternative Method to Count List Items

There is an alternative way that’s worth mentioning to count items in the list.

You can use the Get items action in the SharePoint connector to return all the items in your list and then count the results with the length function.

This isn’t as efficient since it requires the flow to get all the data from the list just to count it. Also, for larger lists, this will not work since the flow will have an upper limit to how many rows can be returned.

Conclusions

There is no specific action for counting items in a SharePoint list.

But thanks to the SharePoint API and the HTTP action, you can create your own action to accomplish this task.

Have you ever needed the total number of list items in your flows? How did you get it? Let me know in the comments below!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Related Articles

Comments

4 Comments

  1. echo

    Hey there, i am trying to replicate your solution. How do i add filter condition on your Uri? like example filter by status (Status eq ‘Complted’)

    • John MacDougall

      I tried to add ?$filter=Status eq 'Completed' on the end of the URI, but it didn’t work. I don’t think you can filter the count.

      I think you need to use something like /_api/web/lists/getbytitle('Your List')/items?$filter=Status eq 'Completed' and then count the results.

      But then you might as well just do this in the Get items action.

Get the Latest Tech Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in tech!