Modify existing Excel file with a Pivot Table and make it downloadable in Gallery

Question

Hi, everyone!

I want to make an app in our company Gallery that updates a sheet in an existing Excel file, and then make that file downloadable in the Gallery as its output.

It goes something like:

* We have an existing Excel file with a Pivot Table sheet and a Raw Data sheet. The Pivot Table is the report that they use, and it uses data from the Raw Data sheet. I want to keep this file untouched in a specific location.
* My workflow in the Gallery makes calculations and creates a table that will go into the Raw Data sheet.
* I now want to load the existing Excel file from (1) into the workflow, and update/overwrite its Raw Data sheet with the table from (2).
* Finally, I want to end the workflow by outputting the resulting Excel file from (3), making it downloadable in the Gallery.

Please advise on how the above (particularly Steps 3 & 4) can be accomplished, or if it's at all possible.

Just for additional context: I already have a similar workflow, but it updates the Raw Data sheet of the existing Excel file, and moves it into a different folder. My intention now is to make it easier for the user by making the output file downloadable at the end of the workflow in the browser itself, instead of forcing the user to go to that folder location to get the output.

Thanks a lot for any help. 😊

apathetichell · Answer

Yeah - if the template file isn't static - you'd want it in a share drive location the server has access to. Basically it's working the same there and could either read in the blob and update (ie the template method) - if you want each run to end up with a new output with a new filename or write the data directly to the rawdata sheet in the existing location.

The blob method is key to me if you want to make a copy and keep "the excelness" (ie the pivots/formats/etc) of the original file. I've also had to run Server without a share drive in some cases and this REALLY helps.

Peachyco · Answer

Thanks for the inputs. 😊

@JagdeeshN : I tried that approach, but it doesn't quite work. From the starting point, when I load the Pivot Table into the workflow, it loses all of its "Pivot-Tableness" and becomes a flat table. When I output it, it's no longer a Pivot Table.

@apathetichell : Interesting approach. I tested that with a dummy file, and it seems to work (at least for my account - I can't say yet how effective it will be on other users' accounts). I'll certainly keep this in mind for similar requirements in the future.

Unfortunately, your Step 1 would be problematic for our particular use-case because we want to keep the template file in a space where it might "evolve" (i.e. some authorized user - who is not necessarily an Alteryx designer - might tinker with the Pivot Table). If we include the template file as an asset in the workflow, we would need the additional step of having our Alteryx admin/expert update the workflow each time there's a change in the Pivot Table.

For now, I guess we'll have to stick to our method of keeping the template file in one folder, update that template file in the workflow, then use Run Command to copy-paste it into the target folder for the user.

apathetichell · Answer

Let's refer to your two sheet - pivot table/raw data sheet as the template. Here is how I do what you want to do:

1) include your template as an asset when you upload workflow to the server.

2) use blob input in your workflow to load in the template. Use Blob output to create a new version with a specific name and location (ie working directory/date-filename.xlsx)

3) use block until done to do step 2 before step 5

4) have your Alteryx workflow do what your alteryx workflow wants to do to prep the raw data.

5) use output to range (keep formatting) to output into the raw data sheet and range of the file you created in step 2. Remember when you write to server you will write to  location\filename.xlsx|rawadatasheet$A2:X5000 - or whatever. note. 1 | - not 3.

DONE.