Download Excel workbook from App

Hi App buliders!

in our App we want to implement the functionality that a user can trigger an execute button, which then calls a OD function. This function generates a excel workbook. So far so good. However, we want to return this workbook as a .csv-file to the App/the browser of the user, where it should be downloaded. How can we achieve this?

Some solution approaches we have thought of:

  • Return the .csv-file in the response-body of the function to the App (Question: How to we access the file/trigger the download?)
  • Save the .csv-file to an external storage and only return the download-link to App. (Question: How do we access this link (except showing it in the message?)?)

Has anybody implemented sth. like this before or knows sth. about this?
Thanks in advance!

First of all: Are you aware of the download functionality for tables in Apps? This functionality can be used to store displayed tables as CSVs or Excel files.

If you are aware, and for some reason this does not fit your needs (e.g. the function has to do some transformations to the data):
Is the format of the to-be-produced CSV (i.e. columns and their types) static?

If this is the case, you could introduce an intermediate step:

  1. Function gets triggered with a random ID as variable together with the other input needed.
  2. Function stores content into an existing Data Table with an additional column containing the IDs (as some kind of "session management)
  3. (Intermediate step) App goes to “preview” page containing the generated content (aforementioned data table that is filtered by the random ID)
  4. User can download the previewed data as CSV (or excel) using the download feature for tables in Apps.

Please note, that this approach is not meant to be used for information security aspects. Technically, users will be able to read the whole content of the table if no further measures are taken (e.g. Analysis Authorization and a dedicated user information column)!

If above scenario also does not fit your needs, your options vary with the nature of the data. If it is considered sensitive, external storage imho should not be used. There are options to do this via generated OD Data Tables but you will most likely have to use a workflow instead of a function here. Download endpoints are available for OD DTs as well but require a 2-step protocol that will probably make a function necessary for orchestration.
In any case, for such scenarios, you will probably have to generate a file link and have the user click it. Reason behind: arbitrary externally stored data is not meant to be downloaded via Apps directly. There are security measures in the browser that prevent this kind of downloads. Moreover, Apps has no out-of-the-box functionality to support this.

Thanks for your Input!

The “problem” with our data is, that the .csv-file, which is created by the function, contains multiple worksheets and as far as I know, there is no way to save this kind of format (= .csv with different worksheets) into a data table in OD (but maybe I’am completly wrong with this assumption so feel free to correct me)).

So did I understand it correctly that there is no option to make a file created in the OD function downloadable in the browser (without using a link and have the user click on it)?

Another thought: Could you send the file from the function to your browser and download it there (kind of like a programmatic file download)?

Alright, that sheds some light on the topic.

According to your information above, you’re dealing with XLS(X) or similar formats rather than CSV which does not have the concept of worksheets.
Unfortunatelly, this rules out any ONE DATA based downloads.

Since the response of a Function in ONE DATA has (and to my knowledge can only have) a JSON content type, I fear there is no way of getting this content directly via the output (at least not in a usable manner). I could think of a dedicated custom component that handles this kind of logic, though. If you have time to create one, here’s the rough concept for a programmatic download:

  1. Trigger the Function via your to-be-created CC with all the necessary varaibles for file creation
  2. Create your file via the Function
  3. Load it (or keep it in memory alltogether) in the same Function
  4. Encode the binary file content and return it as part of a predefined JSON response (together with metadata needed to make sense out of the content on Apps end)
  5. The CC interprets the Function’s response and assembles a savable file and opens the browser’s Save dialog (I think similar things are done in other spots in Apps already)

Maybe, there are already similar CCs out there which I’m not aware of, so it’s worth checking with the dev teams if you decide to go this route.

Alright then I’ll have a look into the CC library, maybe there is sth. adequat.

Thanks for your help and your detailed Input :smile: