cancel
Showing results for 
Search instead for 
Did you mean: 
monaraya

PowerShell Scripts to Export Power BI Reports to PBIX/PDF/PPT

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.

Referencehttps://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:

  • Always use updated Modules for the cmdlets being used in your script.
  • Run the PowerShell as an administrator and execute the script. Also, use the 64-bit Application.
  • The recent changes with TLS could give errors while connecting to Power BI using Connect-PowerBIServiceAccount cmdlet.
  • Please verify the TLS version on the machine we are executing the scripts on: https://powerbi.microsoft.com/en-us/blog/deprecating-tls-1-0-and-1-1-support-in-power-bi/

****************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

Comments

Thank you. Can I know what id should be replaced in the $authURL below?

$authUrl = 'https://login.microsoftonline.com/98--------------43a5ccb4/oauth2/token';

Hey @meharaj ,

In place of  'https://login.microsoftonline.com/98--------------43a5ccb4/oauth2/token'; , you need to mention tenant id.

You can find it from Azure Portal --> Azure Active Directory or from Power BI Service  --> Help (Top Right Corner)  --> About Power BI.


From Tenant URL you can find the tenant ID.

Let me know in case of any queries.

Hello ,

Thx for sharing your script.  I copied and was testing it in our envronment, but I get a 401 Unauthorized error on this part of the code.  Do you have any ideas why?

 

rhalabi_1-1628776101140.png

 

@rhalabi  Always use the API sandbox to troubleshoot your calls.

 

Reports - Export To File In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs

 

Click the "Try it"  button.

Hello @rhalabi  ,

Thanks for using this. 

I see that you are getting unauthorized error. Can you please verify the API permissions applied on the App Registration done for the SPN being used in the script?


We need the below API permissions to be added :

Required scope: (all of the below)

  • Report.ReadWrite.All or Report.Read.All
  • Dataset.ReadWrite.All or Dataset.Read.All

In case error repeats, please do let me know.

 

@monaraya @angoswam 

 

Thanks for the script. Kindly advise whether this will work for Pro License Users??

 

 

 

Polls
What is your favorite Power BI feature release for August 2022?