cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bwieland86
Level: Powered On

PowerApps -> OneDrive -> SQL Stored Procedure -> Excel table

I'm using PowerApps to trigger a Flow to copy an Excel template in a OneDrive "Templates" folder into another "Reports" folder, then calling a parameterized Azure SQL stored procedure and using the Response connector to retrieve the data from the query. In the Excel template, I've created a named table on a hidden worksheet to receive the output of the stored procedure, with various VLOOKUPS and SUMIFS on a visible sheet to display the data to the end user on the formatted template. The named table has the static headers from the output of the stored procedure, but is otherwise blank. The issue I'm having is that I'm not seeing a way to take the response data from the Azure SQL stored procedure and update the named table (ie, clear any existing data in the table and push the headers + data from SQL) in the copied template in the Reports folder.

Within the Excel for Business connectors, I only see Add a Row into Table, Update Row, or Create Table as potentially useful. The particular query does not result in any primary keys and the table in the template only has headers, so updating a row won't work. I tried to use "Add a Row into Table," but the file is created on runtime and it won't accept me manually entering the anticipated named table.

Is there a way to do this directly in Flow, or do I have to store the data in a PowerApps collection and then patch it to the Excel table in the created file?

 

Excel_output_from_SQL.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

Re: PowerApps -> OneDrive -> SQL Stored Procedure -> Excel table

Hi @bwieland86 

 

So there is a limitation on the current excel connector that whenever you try to add/ update or delete a row from an excel table, you actually have to select the file by navigating to it by clicking the folder icon on the file input in the action and then select the table from the drop down that appears with the choice of tables that are present in that particular file. Passing dynamic values for these is not supported and hence the scenario cannot be done in flow using the excel connector directly. The create collection with the data and then patch to excel actually seems the better and feasible option for this one. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

2 REPLIES 2
Dual Super User
Dual Super User

Re: PowerApps -> OneDrive -> SQL Stored Procedure -> Excel table

Hi @bwieland86 

 

So there is a limitation on the current excel connector that whenever you try to add/ update or delete a row from an excel table, you actually have to select the file by navigating to it by clicking the folder icon on the file input in the action and then select the table from the drop down that appears with the choice of tables that are present in that particular file. Passing dynamic values for these is not supported and hence the scenario cannot be done in flow using the excel connector directly. The create collection with the data and then patch to excel actually seems the better and feasible option for this one. 

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
bwieland86
Level: Powered On

Re: PowerApps -> OneDrive -> SQL Stored Procedure -> Excel table

Thanks @yashag2255! I was able to get it to work by changing around the order:

 

1. Create template Excel file with named table in a hidden worksheet and stored the file in a /Templates folder in OneDrive. Utilized normal Excel formulas (mostly VLOOKUP and SUMIF, in this case) to display the data in a formatted report format in a visible worksheet.

2. Created a Flow to call SQL Azure stored procedure and used Response to return the data back to PowerApps

3. Collected data from Flow into a collection

4. Created OneDrive connector to the named table in the Excel template

5. Used the Remove function to clear the table in the Excel template

6. Used ForAll .... Patch to write the data in the collection to the table in the Excel template

7. Created a second Flow to copy the file from path from the /Templates folder into a /Reports folder. Passed the original and target destination file path / name as parameters from PowerApps when calling the Flow.

8. Used the Remove function to clear the table in the Excel template

Helpful resources

Announcements
firstImage

New Ranks and Rank Icons in April

Read the announcement for more information!

firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (4,441)