expose an SPO list for Metrics reporting

We have an existing SPO metrics list, populated automatically by user activity, and we want to produce a straightforward report from that data.

No new data collection, no migration, no unnecessary infrastructure. And no unnecessary charges!

Reporting options

What each option actually means

1. SharePoint views

We can create views of the existing metrics list, including grouping, filtering and totals for numeric columns.

No additional platform or data duplication.

The limitation is presentation. It's still a SharePoint list view, not a particularly attractive report.

2. Excel

Excel can connect to a SharePoint Online list using Power Query.

Once connected, you can create pivot tables, charts and summaries. Refresh the connection when you need updated metrics.

This is probably the simplest route to a conventional management report.

3. Power BI

Power BI Desktop can connect directly to your SPO list.

You can create reports showing, for example, searches by day, most frequently accessed pages and activity trends.

You don't need Dataverse or Power Automate.

Power BI Desktop is free for authoring. Publishing and sharing reports through the Power BI service may require additional licensing.

4. Power Apps

A canvas app can connect directly to the existing SPO list.

You could create an admin screen displaying totals, filtered records and basic charts.

However, Power Apps is primarily an application-building platform. For reporting alone, it's probably more work than necessary.

5. Python and HTML

This is close to what you're already doing.

Python reads the metrics from SharePoint, processes them and generates HTML.

Advantages include complete control over formatting, no additional reporting platform, and the ability to run the report manually.

The disadvantage is maintaining your own reporting code.

6. Dataverse

Dataverse is worth understanding, but it isn't necessary to produce this report.

A SharePoint virtual table can expose list data through Dataverse without copying it. However, virtual tables have limitations around aggregation and native Dataverse reporting features.

Alternatively, you could replicate the metrics into regular Dataverse tables, but that introduces synchronization, storage and security considerations.

For a simple report, neither approach offers an obvious advantage.

One important distinction: reporting versus collection

                   USER ACTIVITY
                         |
                         v
                  SPO Metrics List
                         |
            +------------+------------+
            |            |            |
            v            v            v
       Excel / BI    SharePoint     Python
                       Views         HTML

All three reporting approaches can read the same existing list.

The metrics continue accumulating regardless of which reporting tool you choose or how often you run a report.

Fair criticism. SharePoint's REST API has a rather impressive ability to make modern technology feel like 2007. :)

But there's an important distinction: SharePoint REST can return JSON instead of Atom/XML. You don't need to parse XML in Python.

For example:

GET /_api/web/lists/GetByTitle('Metrics')/items
Accept: application/json;odata=nometadata

That header requests a simplified JSON response.

Your Python code could then work with ordinary dictionaries:

items = response.json()["value"]

Microsoft documents JSON responses for SharePoint list items, so this is supported functionality rather than a workaround.

 Microsoft Learn

The real obstacle is authentication, not data format.

Before we write any code, I'd investigate whether your existing authenticated SharePoint session can be used for a read-only proof of concept.

If that works, we can concentrate on automating the export without changing anything else in your reporting system.

Authentication

Look at Microsoft Entra ID delegated authentication using my existing Microsoft 365 work account.

There are two distinct possibilities:

Method	How it works	Suitable for
Interactive OAuth 2.0 (MSAL)	I sign in through Microsoft using my work account and MFA	Running Python reports on demand
Application authentication (certificate)	A registered application authenticates without a person signing in	Fully unattended scheduled reports

Both use Microsoft's existing identity infrastructure. Neither inherently requires a premium Power Platform licence.

Microsoft Learn

+1

 

My preference for your situation

Start with interactive delegated authentication:

1. Python initiates a Microsoft sign-in.

2. You authenticate with your existing work account.

3. Microsoft issues an access token.

4. Python uses that token to read the metrics list.

5. Your existing reporting code generates the HTML.

There is one catch: Python needs an approved application identity (client ID), and the tenant must permit the necessary delegated permissions. We can't assume your browser's existing SharePoint session is directly reusable.

But there's an important distinction

You originally asked about automating the manual export.

If you want to run the reports yourself with one command, interactive authentication is appropriate.

If you want the reports to generate automatically every night, we need unattended authentication, which generally means an Entra application registration and certificate.

Which level of automation are we pursuing: one-command execution or completely unattended scheduling?

That determines whether we can keep authentication interactive.

---

Hi Kaiyan,

I'm investigating automating the retrieval of metrics from an existing SharePoint Online list using Python, without additional licensing or services.

Could you confirm three things?

1. Interactive authentication: Can we use an existing or new Entra ID app registration with MSAL and delegated read permissions to access SPO list data using our Microsoft 365 credentials?
2. Unattended authentication: Would certificate-based application authentication be permitted if we later want to schedule the reports automatically?
3. Licensing: Can we accomplish this entirely within our existing Microsoft 365 licensing, without additional costs?

The objective is simply to replace a manual list export:

SPO metrics list → Python → HTML reports

No additional platforms or infrastructure.

Dataverse

Current architecture

User clicks / searches
       |
       v
SPO Metrics List
       |
       v
Existing admin report

Possible Dataverse experiment
SPO Metrics List
       |
       v
Dataverse Table
       |
       v
Power Apps / Power BI / Dataverse views