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

[monaraya]

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:

• 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

[lbendlin]

In the loop to check rendering progress you probably want to put the sleep at the very top of the loop.  There's no point to request the export and then immediately check for the rendering progress.

In the request itself you could maybe add the code for selecting a subset of pages from the report for extracting?

[meharaj]

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

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

[meharaj]

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

iNSTEAD OF 98-------------43a5ccb4

[angoswam]

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.

[rhalabi]

Hello monaraya,

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?

[lbendlin]

@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.

[angoswam]

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.

[Anonymous]

@monaraya @angoswam 

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

[EricShahi]

@monaraya  Hey,

1. Created an SA account on Azure > App Registration 

2. Added API permisson > PBI Service , then Delegated Permission as follows:

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

I was able to invoke the authResponse , so I get the Authorization value and content type.

The next part was to Invoke ExportToAPI and I get following error message: 

Invoke-RestMethod : The remote server returned an error: (401) Unauthorized.
At line:37 char:15
+ ... ileExport = Invoke-RestMethod -Method Post -uri $url1 -Headers $authh ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequ
est) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Comma
nds.InvokeRestMethodCommand

[lbendlin]

Examine the returned access_token to make sure it has the required scope.

Note:  Exporting files is a multi step process.

- API call to request an export.  Returns the request ID

- API calls to poll the status of the request

- once status is returned as completed, API call to retrieve the rendered result  and put it into a local file.

[EricShahi]

@lbendlin  The returned access token is valid. 

The output of the $authheaders is :

Name                                       Value
----                                            -----
Authorization                            Bearer xxxxxxxxxxxxxxxxxxxxxxxxxxx..
Content-Type                             application/json

Thanks for the Note, I'm aware of three REST API we need to call  to get the final export file in required format. 

[lbendlin]

There's a difference between "valid"  and "has the required scope". Use jwt.io to check.

[EricShahi]

@lbendlin 

I copied the access token over to JWT and see the attached screenshot below :

[EricShahi]

@lbendlin 

Copied access_token and see the valid message below : 

[lbendlin]

What does it say in the scope section?