Background

It’s not easy to find out how to query the SharePoint REST API in order to retrieve SharePoint analytics for a site collection or pages within a site. So I have created this summary which shows how to do this if you have a need for this information and don’t have access to the Microsoft Graph API’s reports endpoint for some reason. This is based on the information found in the blog article here, written by Atul Gupta. In addition to the excellent blog post by Atul, I will show you how you can use the API calls within Power Automate to populate a SharePoint list in order to get all the SharePoint stats, for all pages within a site collection for the last 90 days. You could then connect to this with Power BI, or export it to Excel for further analysis.

Retrieve GUID values

To begin with we need to get several values using different calls to the SharePoint REST API. Let’s say the site collection you are interested in is located at https://tenant-name.sharepoint.com/sites/news – we can retrieve the values we need as follows.

SiteGUID – https://tenant-name.sharepoint.com/sites/news/_api/site/id
Example output – 1aaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa2

WebGUID – https://tenant-name.sharepoint.com/sites/news/_api/web/id
Example output – 2bbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbb2

SitePageGUID – https://tenant-name.sharepoint.com/sites/news/_api/web/lists/GetByTitle(%27Site%20Pages%27)/id
Example output – 3ccccccc-cccc-cccc-cccc-cccccccccc3

UniqueID – https://tenant-name.sharepoint.com/sites/news/_api/web/lists/GetByTitle(%27Site%20Pages%27)/items?$select=Title,UniqueId
Example output – 4ddddddd-dddd-dddd-dddd-dddddddddd4

We can then retrieve the analytics information for each page on the site by using the following API call (Replace the values in the square brackets below with the actual values from the above API calls):

https://tenant-name.sharepoint.com/news/_api/v2.1/sites/tenant-name.sharepoint.com/,[SiteGUID],[webGUID]/lists/%7B[SitePageGUID]%7D/items/[UniqueId]/oneDrive.getAggregatedAnalytics?startDateTime={Today-90Days}&endDateTime={Today}&$expand=accessStatsByDay($expand=lastSevenDays,lastThirtyDays,lastNinetyDays,allTime,itemActivityStats)&select=*

For example, if we use the example output from above, we would make the following API call:

https://tenant-name.sharepoint.com/news/_api/v2.1/sites/tenant-name.sharepoint.com/,1aaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaa2,2bbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbb2/lists/%7B3ccccccc-cccc-cccc-cccc-cccccccccc3%7D/items/4ddddddd-dddd-dddd-dddd-dddddddddd4/oneDrive.getAggregatedAnalytics?startDateTime={Today-90Days}&endDateTime={Today}&$expand=accessStatsByDay($expand=lastSevenDays,lastThirtyDays,lastNinetyDays,allTime,itemActivityStats)&select=*