Decoding binary version of excel from REST api call response

[Anonymous]

Hi I have folowing powershell script where I sent REST API calls: 

$groupId = ''
$reportId = ''
$exportFormat = 'XLSX'

$body = '{ format: "' + $exportFormat + '" }'

## Reports - Export To File In Group
$Export = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$groupId/reports/$reportId/ExportTo" -Method Post -Body $body
$vExport = $Export | ConvertFrom-Json
$exportId = $vExport.id

## Pooling information
$Status = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$groupId/reports/$reportId/exports/$exportId" -Method Get
$vStatus = $Status | ConvertFrom-Json
$vStatus

## Reports - Get File Of Export To File In Group
$File = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$groupId/reports/$reportId/exports/$exportId/file" -Method Get

$BodyBytes = [System.Text.Encoding]::UTF8.GetBytes($File)

[System.IO.MemoryStream]$memoryStream = New-Object System.IO.MemoryStream(, $BodyBytes)
$memoryStream.Position = 0
[System.Net.Mime.ContentType]$contentType = New-Object System.Net.Mime.ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")

[System.Net.Mail.Attachment]$attachment = New-Object Mail.Attachment($memoryStream, $contentType)

 And then I send email with attachment. Problem is, that attach file is damaged after email reciving. I guess I'm not working correctly with converting $File to Bytes[]. Do you know how to work with binary version of excel?

[Anonymous]

Hi @Anonymous,

I'd like to suggest you trace the exported file to confirm the file format and encode before you do transform operations.

In addition, this API also includes the limit of exported file format, I'd like to suggest you check the original report and exported file format at the first.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous,

Of course I have checked the original file. It exports correctly. Even when I pipe file output via -OutFile excel works fine. Problem is I guess with the object $File. It may not be the correct object type. Rest API documentation says, that response is in format Media Type. So for xlsx files it could be:

application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

 but powershell returns type error when I try to save it to that object.

I'm doing this to create subscriptions on top of paginated reports so we could decommission our SQL Server Reporting Services. I don't want to add another step to save file, wanna do it all in memory.