Introduction

There are times when you want to track the viewership statistics of a SharePoint site, whether it's for site pages or general files.

SharePoint does track all this but the default interface to show statistics for pages and files is quite limited. In particular, there is no easy way to report on statistics for many pages at once or look at historical trends beyond the past couple weeks.

There is the overall site usage page but even there it is limited to 7 days for the popular pages section and 90 days for overall site viewership.

To get better reporting, we can create a Power Automate flow to query the SharePoint API (which provides better access and options for statistics) and record the result somewhere where it can be picked up by PowerBI or other reporting tools.

The API

What the SharePoint Interface Does

Inspecting the SharePoint interface, when you bring up the default viewership statistics sidebar for a page or file, it performs a call to the following address.

https://yoursite.sharepoint.com/_api/v2.1/drives/{driveId}/items/{itemId}/oneDrive.getActivitiesByInterval?startDateTime=2024-11-30T00.000Z&endDateTime=2025-02-28T00:00:00.000Z&interval=day

It looks like it's calling the Microsoft Graph API, specifically the getActivitiesByInterval endpoint.
https://learn.microsoft.com/en-us/graph/api/itemactivitystat-getactivitybyinterval?view=graph-rest-1.0&tabs=http

This is the format that Microsoft gives for that endpoint:

GET /drives/{drive-id}/items/{item-id}/getActivitiesByInterval(startDateTime={startDateTime},endDateTime={endDateTime},interval={interval})

Accessing the Graph API via SharePoint

The difference you will notice between the above documentation and what the SharePoint interface does, is that rather than calling the Microsoft Graph API directly, the API call is made via SharePoint.
https://learn.microsoft.com/en-us/sharepoint/dev/apis/sharepoint-rest-graph

Specifically, rather than using the https://graph.microsoft.com/v1.0/drives URL, it uses https://yoursite.sharepoint.com/_api/v2.0/drives.

This way you don't need additional authentication, and importantly for us, it means the Send an HTTP request to SharePoint action in Power Automate can reach the endpoint we need.

Referencing the Right Item

There are many ways to referencing the page or file you want to fetch the statistics for.

The above example from the SharePoint interface uses the item ID, but that isn't easy to get directly from a Power Automate Get files (properties only) action. Note that this is the OneDrive ID of the .aspx page or file, not the SharePoint list item ID.

The method I use in this example is to reference the file using the path, as the Get files (properties only) action directly returns a {FullPath} property.
https://learn.microsoft.com/en-us/graph/api/driveitem-get?view=graph-rest-1.0&tabs=http

This is the format from Microsoft for referencing files using the item path:

GET /drives/{drive-id}/root:/{item-path}

The Example API URL

Put the above together and it give this endpoint format. We will use this in the example Power Automate flow.

_api/v2.1/drives/{driveId}/root:{itemPath}:/oneDrive.getActivitiesByInterval?startDateTime={startDateTime}&endDateTime={endDateTime}&interval={interval}

Building the Flow

1. Trigger

This example uses a flow that runs every month to fetch and record the previous months statistics.

2. Calculate the start and end of the time interval

The API call needs the start time and end time of the period for which to fetch statistics.

As this example gets statistics for the previous month, we set the start time to the beginning of the previous month. For the end time, we take the start of the current month and subtract 1 second from it.

• Inputs:
  @startOfMonth(getPastTime(1,'Month'))

• Inputs:
  @subtractFromTime(startOfMonth(utcNow()),1,'Second')

3. Get the contents of the SharePoint library

We use the Get files (properties only) SharePoint action to get the contents of the document library. The default SitePages library may not show up in the dropdown but you can always manually enter the ID of that library.

5. Loop through each returned SharePoint file and filter for the SharePoint files we want to see statistics for

We need to call the API and record the statistics for each SharePoint file so the following actions go in an Apply to each loop.

Within the loop we also add a condition to filter the files. In this example it just filters out any folders (as folders are also returned by the previous SharePoint query), but you can add whatever criteria you want such as specific paths or file types. You can also do this in the SharePoint query itself using OData filters.

• Select an output from previous steps:
  @outputs('GET_SitePages')?['body/value']

• @items('FOREACH_SitePages')?['{IsFolder}'] is equal to @false

6. Get file statistics from the SharePoint API

This is where we actually call the API using the Send an HTTP request to SharePoint action.

As a reminder, the endpoint that we want to use is in the following format, and we just set the required parameters.

_api/v2.1/drives/{driveId}/root:{itemPath}:/oneDrive.getActivitiesByInterval?startDateTime={startDateTime}&endDateTime={endDateTime}&interval={interval}

Put it all together and you get the below to go in the Uri parameter.

_api/v2.1/drives/{driveId}/root:@{replace(items('FOREACH_SitePages')?['{FullPath}'], 'SitePages/', '/')}:/oneDrive.getActivitiesByInterval?startDateTime=@{uriComponent(outputs('COMPOSE_Start_of_Interval'))}&endDateTime=@{uriComponent(outputs('COMPOSE_End_of_Interval'))}&interval=month

7. Handle the response

The API call returns an array of intervals. In this example it only returns one entry as we asked for monthly statistics for one month only.

{
  "value": [
    {
      "aggregationInterval": "None",
      "startDateTime": "2025-01-01T00:00:00Z",
      "endDateTime": "2025-01-31T23:59:59Z",
      "access": {
        "actionCount": 99,
        "actorCount": 99,
        "timeSpentInSeconds": 0
      },
      "incompleteData": {
        "wasThrottled": false,
        "resultsPending": false,
        "notSupported": false
      }
    }
  ]
}

We either need to loop through the array or otherwise extract the first entry. In this example I use the loop in case we want to reuse this flow for different time periods and intervals in the future.

• Select an output from previous steps:
  @{body('HTTP_Get_Stats')['value']}

8. Check the results for any errors

On the off chance that there are errors or incomplete data we include an additional check here to make sure that we're only recording complete data. This just checks that all incompleteData flags in the returned data is false.

• @items('FOREACH_Interval')['incompleteData/wasThrottled'] is equal to @false
• @items('FOREACH_Interval')['incompleteData/resultsPending'] is equal to @false
• @items('FOREACH_Interval')['incompleteData/notSupported'] is equal to @false

9. Record the results

In this example we record the statistics into a SharePoint list to create a history of viewership statistics that can be picked up in a PowerBI report. Of course you can record it in whatever location is suitable.

• Title:
  @items('FOREACH_SitePages')?['{FullPath}']
• StartInterval:
  @formatDateTime(items('FOREACH_Interval')['startDateTime'], 'yyyy-MM-dd')
• ActionCount:
  @items('FOREACH_Interval')['access/actionCount']
• ActorCount:
  @items('FOREACH_Interval')['access/actorCount']

The Final Flow

Here's the overview of the final example flow.

Example PowerBI Report

With the Power Automate flow above built and recording the statistics to a SharePoint list, it is possible to then build out a PowerBI report like the below to show the historical viewership of pages and files.