I want to use Excel file on OneDrive for Business which include Formula in cells.
Now, "Excel - Get row" action does not support any formula in cell.
"message": "Excel files containing formula are currently not supported. Please use a file that has no formula.",
The new Excel (business) or Excel (onedrive) connectors support this: https://flow.microsoft.com/blog/approval-history-accessibility/
Typically it would require the user to hit a refresh button under "Data" Tab. To automtate that, one would need to write a macro and save it as .xlsm file. This file extension is probably not detected by Flow at present. So, this is probably the one solution that contains no macros in the file and formulas within the table.
Alternatively,i think we could use a PowerBI service report to read this table and export to another file as table. The flow would then read from this new table. We can only schedule the Power BI file to refresh at a predetermined frequency, but not a trigger like when new data is added, as far as I remember.
I have not tested this solution. I also believe that we will have to redefine data-types in the new table using this process, if that is something important.
1.Is your source data for the pivot auto populated?
2. What is the trigger for the flow?
Excellent, Thank you for complete 'Excel - Get Row' feature
Correct - we don't have any triggers for Excel today. If you add your own timestamps into the rows you can add filters on the queries to only get the data after a certain timestamp (which is basically what triggers do). However, you do have to put the timestamp into the rows to do this.
Great, for COMPLETE status, Fomula support in Excel (business), Excel (OneDrive) connectors