Download a file from API

Hello all,
I have an API endpoint that returns an xlsx file (Can also be a PDF).
That API endpoint returns the file with the headers:
content-disposition: “attachment; filename*=UTF-8’'MyFilename.xlsx”
content-type: “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”
transfer-encoding: “chunked”

When i access that api endpoint from the browser/postman, a browser download popup opens and it downloads the file correctly.
I cant seem to figure out how to download that file through retool once a button is clicked

Retool seems to transform the response into a JSON, where the data contains a message, and the message is a utf-8 of the file (As far as i can see)

Using utils.downloadFile function either on the data or .message downloads something, but it wont open in excel - the file is corrupt.

Suggestions?

Adding my own comment here with new findings on this case.
The issue seems to be caused by the content-type header. If the content-type header is application/octet-stream then the file downloads correctly. But if its application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, then it fails to download.

It seems that not all standard content-type MIME types are supported…?

Hi @slavab!

Are you still blocked here? If so, would you mind sharing a screenshot of your downloadFile query? Specifying the ‘csv’ filetype could do the trick.

https://docs.retool.com/docs/scripting-retool#utilsdownloadfiledata-filename-filetype

In general, there are a few built in CSV management options: filepicker components will have a .parsedValue property that populates if the file is a CSV, buttons have a "download data" option that can output a csv format, and tables have some built in CSV exporting options. If any of the default settings aren't working for your use case, we do have the Papa.parse library built into retool. The full PapaParse docs are here: https://www.papaparse.com/docs and that could be used in a JS query (in combination with the utils.downloadFile function) and they will support some more specific CSV parsing and exporting options. More specifically, Papa.unparse could work for you!

I'm having the same issues here!

@Victoria, I'm trying to download an Excel (not CSV). I have an API which returns an excel file (which downloads fine when queried directly or via Swagger), which I then pass to the utils.downloadFile:

utils.downloadFile(my_query.data , "my_file", 'xlsx')

This does result in an Excel download of the right size and name, but it's corrupted and not openable by Excel.

I also tried tying the download to a button, with the "download data" action, but this resulted in something similar - although actually, the excel was openable, it was just gobbledygook.

Hi archy!

Ack. Would you mind sharing your excel file so I can do some testing? Feel free to either write into Support at support@retool.com or open a chat! One way to do so is through an app > question mark bubble > Chat with support.
Also, when you use a button (which might be our best bet here) to download as Excel, could you expand a bit on 'gobbledygook'? Curious to see how we're messing things up here! :sweat_smile:



Hi @victoria ,
Sorry for my late reply on this.
My issue has been resolved by changing our backend code and returning a different content header as I wrote in the second comment. However, its important to state that this is not really a solution, but more of a workaround to make Retool pick up the file download. Not everyone is able to do this change.
From my earlier testing, this happens with every excel file, so I believe any one you take should cause this.

On MacOS, when opening the file, it simply says an error of "File is damaged and can't be opened". The size of it indeed seems correct, but the content of it (Looking at bytes) seems to be somewhat different from the original.

On our side, we have a button that on click should activate a function with the code line
utils.downloadFile(my_query.data , "my_file", 'xlsx')
where my_query is a GET request to some route.
Again, I believe this happens due to the Content-Type that's returned.
When running the query, with the conent-type of application/vnd.openxmlformats-officedocument.spreadsheetml.sheet it returns a JSON as a response, but the response is formatted as { message: "<Content>" }.
When returning the content type of application/octet-stream it formats the response as { "base64Binary": "<content>", "fileName": "abcd", "fileType": "bin" }

This is helpful, thanks. I tried to just add a Transformer to convert the JSON from the <message: content> form into the b64 form you suggested, but that doesn't seem to have worked- ought it? I could also rewrite the backend but am rather reluctant to do so!

@Victoria actually @slavab's comment helps explain the "gobbledygook" - it's the word "message" followed by a base encoded string, I think:

[splitting response because I don't have permission to embed multiple screenshots]

When I hover over the data download button, I get the following tooltip:

Which implies that the download function is expecting something in a rather structured form, not a binary blob. My excel actually includes multiple sheets and images, so I can't structure it in a object as suggested.

Hi Archy!

You are correct that the Export data action type is expecting some type of structured JSON!

@mark looked in to this, and added in some support for REST GET queries to correctly return an Excel filetype as base64. This is live now, so you should be able to accomplish this with something like:

Then setting the button's event handler to trigger query2 here, instead of the Export data action type

@slavab Can you verify that your workaround of changing the content-type to octet-stream still works? If not, both 'application/vnd.ms-excel' and 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' content types should correctly format the response now! Thank you for the hints!