Excel file created using Flow and Azure function is corrupt

(0) Share

Report

Posted on by LErasmus

I have a Canvas App with a button that calls Flow which in turn

Flow executes SQL stored procedure
Stored procedure 1 returns rows of JSON
Flow appends rows of JSON to JSON array (Data)
Flow executes SQL stored procedure 2
Stored procedure 2 returns rows of JSON containing column formatting returned in Excel
Flow appends rows of JSON to JSON array (Formatting rules)
Flow calls Azure function (Node JS) with Data and Formatting rules
Azure function uses "json2xls" to read JSON (Data) and output to Excel
Azure function format worksheet based on (Formatting Rules)
Azure function returns workbook
Flow creates sharepoint file with body as returned from Azure Function

When trying to open the spreadsheet from SharePoint I get the following error:

We're sorry, we couldn't open your workbook. It's possibly corrupt or using a file format that's not supported.

You'll need to open this in the desktop app.

The problem seems to be the encoding being defaulted to UTF-8 instead of Windows-1252. I have tried all different Content-types, Charsets and converting to Base64 and back option but the resultant .xlsx file always ends up in UTF-8.

When I create a .js file containing essentially the same code as the Azure function it works 100%.

As the JSON schemas are dynamic and the columns in the spreadsheet must be formatted I can not use options such as creating a CSV file to open in Excel or creating the file in Flow with formatting.

Any suggestions will be geatly appreciated.

Lourens

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (3)

Answers (1)

cchannon 4,702 Moderator on at

Like (0)

Report
Copy link

Link copied!

Why not skip 11 altogether? Make the target SharePoint location a parameter of your POST request and have the function drop the file there directly rather than passing its contents back to Flow. That way you cut out any parsing/interpreting Flow is trying to put in the mix and can directly control the encoding from code.

Only downside I can see is that then you need to set up an unattended auth pattern yourself, but that's not difficult with MSAL.

Was this reply helpful? Yes No
LErasmus 11 on at

Like (0)

Report
Copy link

Link copied!

Thank you for this suggestion, I am busy trying itnow and will reply once done. I a not familiar with how to write from Function App to Sharepoint so it is going slowly.

Was this reply helpful? Yes No
Verified answer

LErasmus 11 on at

Like (0)

Report
Copy link

Link copied!

Thanks to the great help of someone I finally managed to get it working without having to write to SharePoint from Function App. It was achived by encoding from binary to base64 in the Function App:
let xlsx = json2xls(json);
let out = {contentBytes: Buffer.from(xlsx, 'binary').toString('base64')}
context.res.json(out);

Then in Flow decode it back to Binary:
base64ToBinary(body('HTTP')['contentBytes'])

This is the written directly to an xlsx file in SharePoint

Was this reply helpful? Yes No