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

[EricShahi]

@lbendlin  

scope section ? can you give me example where it would be on the access_token.

[lbendlin]

it's the "scp"  section.

[EricShahi]

 Hi @lbendlin 

Thanks for clarfying regarding scope "sc" section.

I dont see aything with scp, which is strange as i have delegated permisson below : 

just on the other note, do i need to create a secuirty group and assinged SA account.

 see doc form MS Embed Power BI content in an embedded analytics application with service principal and an applicatio...

[lbendlin]

in my understanding it doesn't matter what you identify as - as long as you have an auth token with the right scope you will be permitted to consume the API call.

[EricShahi]

@lbendlin 

Thanks what I thought, just needed to confirm. 

 based on JWT  it concludes that the access token doesnot contain scope roles are missing . I was expecting to see something similar to the example you have provided.

[EricShahi]

The access_Token Scope "sc" is missing all the delegated permission I have assiged i.e. scope doesn't even exisit. 

I have copied the access_token into JWT.io to decode.

I was expecting to see something like below as an example : 

as you can see below, I have given enough access however its not included on the scope (Access_Token), does anybody know the reason why ?

[lamb]

Can someone help, i get the below error maybe on this statement

**** 'resource' = 'https://analysis.windows.net/powerbi/api';

Invoke-PowerBIRestMethod : One or more errors occurred.
At line:18 char:17
+ ... hResponse = Invoke-PowerBIRestMethod -Url $authUrl –Method POST -Body ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (Microsoft.Power...werBIRestMethod:InvokePowerBIRestMethod) [Invoke-PowerBIRestMethod], AggregateException
+ FullyQualifiedErrorId : One or more errors occurred.,Microsoft.PowerBI.Commands.Profile.InvokePowerBIRestMethod

[lbendlin]

@lamb not enough information.  Please post the relevant Powershell code.

[lamb]

When i run all the steps at once in windows powershell I get this error. If you want me to provide more screenshots you can reach out to me directly @ leo.ambrose@owens-minor.com