cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LourensE
New Member

Excel file created using Flow and Azure function is corrupt

Hi

 

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

  1. Flow executes SQL stored procedure
  2. Stored procedure 1 returns rows of JSON
  3. Flow appends rows of JSON to JSON array (Data)
  4. Flow executes SQL stored procedure 2
  5. Stored procedure 2 returns rows of JSON containing column formatting returned in Excel
  6. Flow appends rows of JSON to JSON array (Formatting rules)
  7. Flow calls Azure function (Node JS) with Data and Formatting rules
  8. Azure function uses "json2xls" to read JSON (Data) and output to Excel
  9. Azure function format worksheet based on (Formatting Rules)
  10. Azure function returns workbook
  11. 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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LourensE
New Member

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

View solution in original post

3 REPLIES 3
cchannon
Super User
Super User

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.

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.

LourensE
New Member

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

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,679)