[Question] Another way to calculate the number of items in a SharePoint list?

(0) Share

Report

Posted on by Community Power Pla...

Hello all,

I have been doing automated daily dashboards that extract data from SharePoint lists, what I'm currently doing is:

'Get Items' from a SharePoint List
Use 'Filter' function to filter the data I want
Then use the Length function to find out the number
Send emails to the designated parties

'Get Items' with Query FIlter
Use Length function to get the numbers of items
Then use 'Get Items' for another filtration
Use the Length function
Send emails to the designated parties

But, all the data that SharePoint is pulling is dumped out because I don't use them, so the time it took to 'Get Items' was because it is retrieving data that I don't use. Even when I use another view with viewer Columns, but it pulls some predefined data.

Do you think guys there is a faster way to pull number of items from SharePoint lists without the need to pull the whole data?

Categories:

General topics

I have the same question (0)

All responses (14)

Answers (1)

Verified answer

VictorIvanidze 13,073 on at

Like (1)

Report

Hi @Anonymous ,

yes I think requesting via GRAPH will be much faster. Note that you need a premium connector "HTTP with Azure AD" ro run GRAPH API.
See how MS GRAPH works with SharePoint here: https://docs.microsoft.com/en-us/graph/api/resources/sharepoint?view=graph-rest-1.0

Was this reply helpful? Yes No
Christopher S on at

Like (1)

Report

Hi @Anonymous

If you can idenfity by any columns which records you don't need, you can indicate an oData query to filter the results:
https://flow.microsoft.com/en-us/blog/advanced-flow-of-the-week-filtering-with-odata/

Best Regards

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hello @VictorIvanidze ,

Thats amazing! I will explore it right away, thanks!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hello @chsanche ,

The 'Filter Query' section you mean right?

I use it, but this will need to do a hit to SharePoint list on each different type/filteration, and I have too many data 10k+, I only want the total length of items, none of the data.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hello, I was able to create an API that retrieves the list items, but it only retreived 221 items. The list is 71k 😁

And do you know how to return just a number of count? Because I'm not able to do it.

Can you please guide me?

Was this reply helpful? Yes No
VictorIvanidze 13,073 on at

Like (0)

Report

Did you use $count parameter in your query as shown here:
https://docs.microsoft.com/en-us/graph/query-parameters ?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Yes I tried it, but it gives me that it is not supported:
https://graph.microsoft.com/v1.0/sites/[Site]/lists/[SubSite]/items?$top=2&$count=true

The response is:
{
  "error": {
    "code": "invalidRequest",
    "message": "$count is not supported on this API. Only URLs returned by the API can be used to page.",
    "innerError": {
      "request-id": "a367e809-cbad-4734-834b-28c46ea00106",
      "date": "2020-05-10T10:36:16"
    }
  }
}

Was this reply helpful? Yes No
VictorIvanidze 13,073 on at

Like (0)

Report

Well it looks like $count is not supported there.Try to use
https://graph.microsoft.com/v1.0/sites/[Site]/lists/[SubSite]/items?$TOP=3000&SELECT=ID

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hello,

Thanks! It worked for up to 4k items, it showed the IDs, but if I try more, lets say 5k items, it says:
{
  "error": {
    "code": "UnknownError",
    "message": "",
    "innerError": {
      "request-id": "71e0a97a-632d-4673-a75c-f6ed4ce2b76c",
      "date": "2020-05-10T13:34:40"
    }
  }
}
Do you know how to overcome this? And how do I count them? Should I use the Length?

Was this reply helpful? Yes No
VictorIvanidze 13,073 on at

Like (0)

Report

Do you see an
@odata.nextLink": "https://graph.microsoft.com/v1.0/sites/<dddddddddd>/lists/<zzzzzzzzzzzzzzzzzz>/items?$TOP=3000&SELECT=ID&$skiptoken=<UUUUUUUUUUUUUUUUUUUUU>"
in the response? You should just use the value of the nextlink, to get the next page of results.

See details here: https://docs.microsoft.com/en-us/graph/paging

Was this reply helpful? Yes No