cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 II
Dual Super User II

Hi @Anonymous 

 

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 II
Dual Super User II

Hi @Anonymous 

 

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

Anonymous
Not applicable

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
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Users online (7,052)