Use Power Shell To Export After Refresh

[villa1980]

Good morning all,

 I am currently automatically refreshing a report on a daily basis (in service). Exporting it to xlsx and then emailing it on to a group of people.
Being very lazy I am thinking why am I doing this when it could just be emailed after it has been refeshed.
My company won't pay for automate so I guess my only other option is Powershell (which I have no idea how to use this), so my question is does anyone know how this could be created if at all.

As a sidenote I also have access to n8n so could that be a way of doing it?

Thanks

Alex
 

[sroy_16]

Hello @villa1980 

Yes you can use powerscript to achieve the same. I am attaching a sample code. Try using it but making changes to it as per ypur requirement.

# Variables
$clientId = "Your-Client-ID"
$clientSecret = "Your-Client-Secret"
$tenantId = "Your-Tenant-ID"
$groupId = "Your-Workspace-ID"
$reportId = "Your-Report-ID"
$reportExportFormat = "PDF" # You can also use "EXCEL"
$recipientEmail = "recipient@example.com"

# Get OAuth Token
$tokenUri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
$body = @{
client_id = $clientId
scope = "https://analysis.windows.net/powerbi/api/.default"
client_secret = $clientSecret
grant_type = "client_credentials"
}
$response = Invoke-RestMethod -Method Post -Uri $tokenUri -ContentType "application/x-www-form-urlencoded" -Body $body
$accessToken = $response.access_token

# Export Report
$exportUri = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/reports/$reportId/ExportTo"
$headers = @{
Authorization = "Bearer $accessToken"
ContentType = "application/json"
}
$response = Invoke-RestMethod -Method Post -Uri $exportUri -Headers $headers -OutFile "C:\Path\To\ExportedReport.pdf"

# Send Email
$smtpServer = "smtp.yourserver.com"
$smtpFrom = "your-email@example.com"
$smtpTo = $recipientEmail
$messageSubject = "Power BI Report"
$messageBody = "Please find the attached report."
$attachment = "C:\Path\To\ExportedReport.pdf"

Send-MailMessage -From $smtpFrom -To $smtpTo -Subject $messageSubject -Body $messageBody -SmtpServer $smtpServer -Attachments $attachment

For n8n you can try the below steps:

• Use HTTP Request Nodes:

  • Configure HTTP Request nodes in n8n to call the Power BI REST API to export the report.

• Set Up Email Node:

  • Use the Email node in n8n to send the exported report.

• Automate the Workflow:

  • Schedule or trigger this workflow to run after the report refreshes.

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

[ibarrau · Accepted solution]

Hi. You can code scripts to automate some things with PowerShell or the PowerBi Rest API. You kind of require some coding skills in order to make it work and deploy.

There is no capture when the dataset finished the refresh, you could run the refresh with the code  and some minutes later export. Consider that exporting to file by code it's a dedicated capacity feature, so you need Fabric, Premium or embedded in order to make it work.

Also, sending an email by code, might be tricky, you need to purchase a smtp service for coding an email send.

Depending on what you are exporting at the excel, there might be alternatives to generate the file in a different way so you can use other tools for sending that. For example, runing a query against the dataset, store the result at sharepoint and send it by email. Those actions are Power Automate free with a Microsoft 365 account.

I hope that helps,

[villa1980]

Hi ibarrau,

Thank-you for the response, was very informative and has made me look at alternative solutions as you have stated, rather than go down the powershell route.

Many Thanks Again,

Alex