Dynamic output using template with multiple tabs

[sselby6909]

I'm trying to create a dynamic output in excel using a template where different parts of my workflow update different tabs in the template and produce a single dynamically named file that changes path location based on data in the source data on Sharepoint. I'm wondering if the Render, the Blob tools, or a command run would be best in this situation. 

I've tried setting up the Blob with append on a single version of my output, but I'm getting the following error: "There were more than 16 records in the source"  and I've noticed that with Blob, I can't use the "|||" and the tab name to isolate which part of the template file I want to blob.  It just returns an error that the file can't be opened. 

If I'm using the Render tool, I assume that I need to first end my workflow with a Table.

I've already determined how to create the Dynamic file name and path using a series of formulas to create a column with the final output path/filename. I'm mainly trying to determine how to combine my outputs with a template before creating the final output file for use.  Unfortunately the data I have is highly sensitive so I can't provide a copy of my data and workflow. 

[nagakavyasri]

@sselby6909 Overwrite the template using this step for the Output files. Use 'Block Untill then' tool if you are getting error while writing the output file.

[sselby6909]

This doesn't keep the tabs that are not being overriden. It only creates the new tabs. My existing template has additional tabs that point to the data and and create pivots/analytics on the data tabs that are being generated by the workflow. 

This is the new output file

this is the template file

  

[SPetrie]

This is the method I use for my templated excel files.

Copy your template using blob tools and save as a temporary excel file in a temp location.

Do your transformations and then output the the required tabs using a range.

Then use the blob tools again to copy the temp excel file, rename it and save it to your final destination.

This is a very ugly example, but does what it needs to and is a good example of what can be done I think. 

I use conditional formatting rules in my excel templates to do any highlighting or formatting needed. Only downside is that tabs must be created in the template, so any new tabs will not have formatting.

[sselby6909]

Question to elaborate on the idea of creating a copy of the template and then writing to that copy, would it be possible to use a field in a file to create the copy?  If so, what would be better, a run command or the blob function? Both functions are new for me as I've not used them previously. 

So I have one app that currently creates a combined raw data file and creates file path information to be used downstream in a 2nd app workflow to dynamically create the current output files. However, I think I might be able to solve this by creating the file dynamically first when I create the raw output file and then have the 2nd workflow look for that new dynamic file and write to specific tabs/data references. 

Thoughts?

[SPetrie · Accepted solution]

It would be possible to use a field in a file to indicate where the template location is. 

The only part that really matters for the purposes of my version of the flow is that the copy is created and ready for the rest of the workflow to output to it. I prefer to use to use blobs for that step because run commands dont always wait to finish executing before moving on to the next step in the workflow. I prefer to use those as final steps in flows and not in the beginning parts.

Personally, I like to keep everything inside a single workflow if possible. 

If there is no business need to create the combined raw file, I would skip that step. Instead of outputting to an intermediate file and having to pass its name/location to a second flow, take the combined data and output directly to your output template.

I dont see anything particularly wrong with the method you mentioned, its just not the way I tend to go about it.

[sselby6909]

I was able to use the blob function in the first app by creating 2 additional formulas. One for the location of the template and one for the renamed output template. This allowed me to then have a file on the drive for the 2nd app to write to. Because I've already built dynamic file path fields, this resulted in a very easy solution for my situation. 

   

The need for the raw data file is for checks of the data should something go wrong within the 2nd flow on the manipulated data and for possible comparison analytics (i.e. before/after). I also need to create two different apps because I have to ask a 2nd series of questions and browse for a 2nd data source based on that 2nd series of responses. *i.e. I need a file if A is selcted and don't need a file if B is selected.