Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I worked on a customer issue recently, and I had an opportunity to write the below scripts to export Power BI Reports to PDF/PPT/PBIX and send it as an email attachment.
Reference: https://powerbi.microsoft.com/en-us/blog/export-report-to-pdf-pptx-and-png-files-using-power-bi-rest...
Points to be taken care of while working with PowerShell:
****************Script to Export the PBI Report into PPT/PDF***********************
API Used: https://docs.microsoft.com/en-us/rest/api/power-bi/reports/exporttofileingroup
This Script will help to export the report and later send the same as an attachment. We are using ‘Sendgrid’ here to send the email, we can also use the exchange server.
# Author: Anamika
# Date: 20/06/2020
$authUrl = 'https://login.microsoftonline.com/98--------------43a5ccb4/oauth2/token';
$clientId="682-----------------8a243ec3"
$clientSecret = "hHX-------------9WI20oyI"
#This is the location where the file is finally exported to. You can change this location.
$Folder="c:\myfile\"
$body = @{
'grant_type' = 'client_credentials';
'resource' = 'https://analysis.windows.net/powerbi/api';
'client_id' = $clientId;
'client_secret' = $clientSecret;
};
$authResponse = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body
$pbireportId = "a342-----------2384fd60a1"
$groupID = "f00-------------b5c88f"
$restURL = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/reports/$pbireportId/ExportTo"
$headers = @{
"Content-Type" = "application/json";
"Authorization" = $authResponse.token_type + " " + $authResponse.access_token
}
$body = "{`"format`":`"pdf`"}"
$body
$output= Invoke-RestMethod -Uri $restURL -headers $headers -Method POST -Body $body
$fileid=$output.id
$restURL01 = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/reports/$pbireportId/Exports/$fileid"
$filestatus = "";
Do
{
Write-Output "Waiting for the Export to be available"
$filestatus=Invoke-RestMethod -Uri $restURL01 -headers $headers -Method GET
$filestatus=$filestatus.status
$filestatus
Start-Sleep -Seconds 20
}
While($filestatus -ne "Succeeded")
start-sleep -Seconds 20
$restURL03 = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/reports/$pbireportId/Exports/$fileid/file"
$test=Invoke-RestMethod -Uri $restURL03 -headers $headers -Method GET -OutFile $Folder"new.pdf"
$SMTPServer = "smtp.sendgrid.net"
$SMTPPort = "587"
$Username = "azure_36----------------837960d7@azure.com"
$Password = "p------123"
$to = "xyz@microsoft.com"
$cc = "abc@microsoft.com"
$subject = "Exported a file from PowerBI Report Succesfully"
$body = "Exported a file from PowerBI Report Succesfully"
$attachment =$Folder + "new.pdf"
$message = New-Object System.Net.Mail.MailMessage
$message.subject = $subject
$message.body = $body
$message.to.add($to)
$message.cc.add($cc)
$message.from = $username
$message.attachments.add($attachment)
$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort);
$smtp.EnableSSL = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
$smtp.send($message)
write-host "Mail Sent"
************* script to export the Power BI Report into PBIX format *****************
API Used: https://docs.microsoft.com/en-us/rest/api/power-bi/reports/exportreportingroup
# Author: Anamika
# Date: 20/06/2020
$authUrl = 'https://login.microsoftonline.com/98c45f19--------97d943a5ccb4/oauth2/token';
$clientId="6822cdb7------a370-e9c68a243ec3"
$clientSecret = "hHXz_--------_zg-TH9WI20oyI"
$body = @{
'grant_type' = 'client_credentials';
'resource' = 'https://analysis.windows.net/powerbi/api';
'client_id' = $clientId;
'client_secret' = $clientSecret;
};
$authResponse = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body
$pbireportId = "a342151a-------aaf2-302384fd60a1"
$groupID = "f00cdaa7--------bf50-455bdcb5c88f"
write-output "Initiating Export"
$RestURL = "https://api.powerbi.com/v1.0/myorg/groups/$groupID/reports/$pbireportId/Export"
$headers = @{
"Content-Type" = "application/json";
"Authorization" = $authResponse.token_type + " " + $authResponse.access_token
}
$output= Invoke-RestMethod -Uri $restURL -headers $headers -Method GET -OutFile "C:\myfile\ana.pbix"
***********************script ends************************
The Authentication could be done via App ID and Client Secret as well, instead of using the user account to login. We can use the below code to achieve that:
$authUrl = 'https://login.microsoftonline.com/98c45f19--------97d943a5ccb4/oauth2/token';
$clientId="6822cdb7------a370-e9c68a243ec3"
$clientSecret = "hHXz_--------_zg-TH9WI20oyI"
$body = @{
'grant_type' = 'client_credentials';
'resource' = 'https://analysis.windows.net/powerbi/api';
'client_id' = $clientId;
'client_secret' = $clientSecret;
};
$authResponse = Invoke-RestMethod -Uri $authUrl –Method POST -Body $body
Author: Anamika Goswami
Reviewer: Mounika Narayana Reddy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.