Email SharePoint Inventory Reports with Power Automate

What this solution does

This implementation preserves the site inventory collection and report fields from the SharePoint inventory report and changes only the delivery approach. It uses the validated report objects to build CSV text in memory, then creates a timestamped CSV file directly in the SharePoint destination folder.

The report includes the existing approved columns: SiteName, SiteUrl, SiteType, Template, WebCreatedDate, Office365Visibility, StorageGB, SiteCollectionAdmins, and SPOwners. The script validates the serialized CSV header before upload.

PowerShell does not send email. Power Automate detects the new SharePoint file and sends it as an attachment using the Microsoft 365 Outlook connector.

Architecture

PowerShell + PnP PowerShell
  |
  | collect site inventory and calculate report data
  v
ConvertTo-Csv (in memory) + validate headers
  |
  | Add-PnPFile -Content
  v
SharePoint document library / Incoming folder
  |
  | When a file is created (properties only)
  v
Condition: filename starts with SharePointSiteInventory_
  |
  v
SharePoint — Get file content using path (Full Path from trigger)
  |
  v
Microsoft 365 Outlook — Send an email (V2)

Download the PowerShell script

Prerequisites

• PowerShell 7 and a compatible version of PnP.PowerShell. This script uses PowerShell 7 parallel-processing syntax and is not intended for Windows PowerShell 5.1 / PowerShell ISE.
• An Entra application registered for certificate-based app-only authentication and the necessary permissions for the inventory operations.
• A password-protected PFX file containing the private key; its matching public certificate must be registered with the Entra application.
• Write permission for the application on the destination SharePoint report site/library, as well as the permissions required to read the tenant/site details in your inventory.
• An approved Power Automate SharePoint connection and Microsoft 365 Outlook connection.
• A DLP policy that allows SharePoint and Office 365 Outlook to be used together in the same flow.

PFX certificate setup

The script authenticates directly from your password-protected PFX file. You do not need to import the certificate into the Windows certificate store or look up a thumbprint.

In the script configuration, set $PfxPath to the full path of your certificate file. When you run the script, it prompts once for the PFX password using a secure input; the password is not stored in the script.

$PfxPath = "C:\Certificates\SharePointAutomationCert.pfx"

The PFX file must contain the private key. Register the matching public certificate (typically the .cer file) with the Entra app identified by $ClientId; keep the private-key PFX file protected on the computer that runs the script. The app must have the required permissions and admin consent for tenant-wide inventory, site reads, and uploading to the destination site.

You do not need to import the PFX into the Windows certificate store for this authentication method. The account running PowerShell must be able to read the PFX file.

Run the script interactively because it asks for the password at the start of each run. For unattended scheduled runs, this prompt cannot be answered automatically; do not hard-code the password. Use an approved secret store or an authentication design intended for unattended execution.

For app registration and certificate authentication steps, see the Entra ID Certificate Authentication guide.

Configure the script

Choose Download script (.ps1) to save a local copy, or select Open in editor above to review the file in GitHub’s browser-based editor. In your editor, update the CONFIGURATION block near the top of the script. Keep the PFX password out of the file; it will be requested securely when you run the script.

Example destination settings — replace the sample site and folder with your own:

$ReportSiteUrl = "https://contoso.sharepoint.com/sites/SharePointReports"
$ReportFolder  = "Shared Documents/Reports/SharePointSiteInventory/Incoming"

How the script avoids a local CSV

1. It collects the SharePoint site details using the existing tenant inventory and site-level queries.
2. It creates the approved CSV text with ConvertTo-Csv in memory.
3. It checks the CSV header and required columns without writing the CSV to disk.
4. It connects to the report site using Connect-PnPOnline -CertificatePath with a securely prompted -CertificatePassword.
5. It calls Add-PnPFile -Folder $ReportFolder -FileName $UploadFileName -Content $CsvContent to create the CSV directly in SharePoint.

No local CSV file is created. The source PowerShell script still runs on the computer or automation host where PowerShell and PnP.PowerShell are installed.

Build the Power Automate flow

Once the script uploads a CSV to SharePoint, this cloud flow detects the new file, reads its contents, and emails the CSV as an attachment.

Fix DLP connector policy errors

If Flow checker says the SharePoint and Microsoft 365 Outlook connectors cannot be used together, review the environment’s Data Loss Prevention (DLP) policies. The issue is with connector governance, not the CSV or PFX authentication. An error may name a policy such as DemoPolicy, but more than one applicable policy can affect the same environment.

1. Open the Power Platform admin center and inspect the data policies that apply to the environment.
2. Check whether the SharePoint connector (shared_sharepointonline) and Microsoft 365 Outlook connector (shared_office365) are allowed and placed in compatible data groups under all applicable policies.
3. If they are in different groups or one is blocked, ask the policy owner to make an approved governance change. Do not assume a particular group is always correct; follow your organization’s classification policy.
4. Save the approved change, allow time for policy propagation, reopen the flow, and run Flow checker again.

Do not delete or disable the policy merely to make this flow pass. For details, see Microsoft’s Data policies documentation and connector classification guidance. If you cannot edit the policy, ask your Power Platform administrator to perform the change.

Run an end-to-end test

1. Save and turn on the Power Automate flow.
2. Run the production PowerShell script and wait for it to finish collecting the inventory and uploading the in-memory CSV.
3. Confirm that a new timestamped CSV appears in the SharePoint destination folder.
4. Open flow run history. Verify the trigger, condition, Get file content using path, and Send an email (V2) succeed.
5. For the first test, send to a test mailbox you control. Confirm the attachment opens and includes the expected report columns before switching the recipient to a production distribution list.

Files created before the flow was enabled do not normally start a new trigger run retroactively. Run the PowerShell script again to create a fresh timestamped file when retesting.

Production and scheduling considerations

• The local report CSV is not created; the content is held in memory and uploaded directly to SharePoint.
• Limit permissions to the destination library because site names, URLs, admins, owners, and storage usage may be sensitive inventory data.
• The PFX file must be readable by the account running the script, and its folder should have restricted access. This script prompts for the PFX password interactively, so it will not run unattended in Task Scheduler as-is. For unattended scheduling, use an approved secret store or redesign authentication for a non-interactive execution context; never put the password in the script.
• Each run creates a new timestamped file. Decide on a retention or archive policy for uploaded reports before adding a move/delete action to the flow.
• Monitor flow runs and configure a failure notification if the report email is operationally important.
• Use the narrowest approved permissions for inventory reading and writing to the destination SharePoint site.

Troubleshooting

PFX file or password error

Check that $PfxPath points to the correct .pfx file, enter the correct PFX password when prompted, and verify the PFX contains the private key. Confirm the matching public certificate is registered with the Entra app and that the certificate has not expired.

Authentication or SharePoint upload denied

Confirm $Tenant and $ClientId match the Entra app, the public certificate corresponding to the PFX is registered, the PFX contains the private key, admin consent has been granted, and the app has the permissions required for tenant-wide inventory, site reads, and writing to the report site. Check that $AdminCenterUrl, $SharePointRootUrl, $ReportSiteUrl, and $ReportFolder are correct.

The flow does not trigger or the condition is false

Confirm the flow is saved and turned on, the SharePoint trigger watches the correct site/library/subfolder, and the new file name begins with SharePointSiteInventory_. If you are retesting, run the script again after enabling the flow to create a new timestamped file.

Get file content returns 404

Use Get file content using path, with Full Path from the trigger. Check that both SharePoint actions point to the same site and that the file exists at that path.

The email arrives without a readable attachment

Set Attachment Name to File name with extension from the trigger and Attachment Content to File Content from the path-based content action.