Refresh data on an excel sheet fetching data from an external data source

[robin_gupta]

Hello,

How can we refresh data on an excel sheet (residing on OneDrive), without opening the sheet?

The data on the excel sheet is coming from a  query against an ODBC source (Snowflake connector).

[yashag2255]

Hi @robin_gupta 

The current excel connector in Power Automate does not support refreshing a worksheet/workbook as a direct action. However, you can check out the graph API (https://docs.microsoft.com/en-us/graph/api/workbook-refreshsession?view=graph-rest-1.0&tabs=http ) and use the HTTP action or build a custom connector for this one. 

If you think this should be added to the existing connector as a feature, you can raise an idea request or upvote similar ones here: https://powerusers.microsoft.com/t5/Power-Automate-Ideas/idb-p/MPAIdeas

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!

[robin_gupta]

So basically there is no low-code solution to updating an excel file in a onedrive location  as of now?

I had read somewhere on the community that one of the two ways below, could help:

1. Setting up "Refresh a Dataset" from POWERBI as a a Step
2. " Update a file" step from Sharepoint
3. Or adding/deleting a row as here on your reply 

@yashag2255  Can you please let me know if the above could help in solving the case?

[yashag2255]

Hi @robin_gupta 

Adding or deleting a row definitely helps but then it interacts with the data. I was under an assumption that you did not want to do that and externally refresh the worksheet. 

Refresh Dataset in Power BI is for the powerbi datasets so if your data from excel is ultimately going there, you can definitely check that out. 

You can try getting the file content first and then try the update file action and replace content so it might refresh it that way. But I believe there will be certain limitations to that as well.

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!

[robin_gupta]

Tried all methods and gave up. There is no way , Power Automate or any of the Sharepoint/PowerBI/OneDrive tools can refresh data from an external source (ODBC) on an excel sheet (without opening it explicitly)

[MafazAnsar]

@robin_gupta I had a somewhat similar problem, my excel gets data submission via MS forms. A table in the excel file is due to get updated based on the data submitted via form submissions. the updated table is to be placed in an email circulated to a team. The table is retrieved from the excel using "get rows present in a table" excel operatior. After failing to get the latest table in the email multiple times, I changed the following on the flow,

Settings on 'List rows present in a table' and set the "secure inputs" to "on" state. ever since my flow gives the latest table in the email body.