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