Formula support in "Excel - Get row" action

[yoshihirok]

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.

Body
{
  "status": 400,
  "message": "Excel files containing formula are currently not supported. Please use a file that has no formula.",
  "source": "127.0.0.1"
}

Regards,

Yoshihiro Kawabata

[Unte]

Same issue for me. Flow will not open excel if tagle contains formulas. Even if i will not use them and take only text values from different cells

[vmoskalenko]

Well, I have found a workaround.

I just put the formulas out of the table. I.e., skip one column from your table and create new functions here. 

Sure you have to use A1 or B2 links instead of table column name, but it works.

[mryckman]

We get some data from Jet Professional that we'd love to have flow use, but all of the tables appear to contain formulas. It seems like Flow should be able to get the read-only values from them. Maybe the problem is that it would have to execute the sheet or something.

[ashokpershad]

This is very much required. How can MS expect to work in excel without formulas? This is basic requirement for every user working in excel to use formulas as much as possible. I support the IDEA.

[vmoskalenko]

I have received a private message to provide more details about the workaround.

I think it would be nice to publish the answer in public.

So, Create a simple Excel file.

Create a Table area. In my case, it's A1:E8.

Add formulas near the table area. In my case, it's F and G columns and K1 and K2.

Do not extend table area. Table area should be the same as in step 2. Do not mix table area and formula area - they should be separated.

I think this workaround is not Cento per cento workaround that works every time. But in my case, it works. And looks like will works in other scenarios.

I hope it helps.

Please find Excel sample here 

[Anonymous]

This "workaround" posted user vmoskalenko is useful ONLY if you want to be able to pull static information from the "Get rows from excel" command. The whole purpose of Flow, for me, is to pull information that is always changing. My tables in excel will need to contain formulas- because they are references to ever-growing data sets. 

Flow needs to support tables with formulas.

[eraiewski]

This is unacceptable. Zapier has figured out how to do it just fine. This means it is not impossible, but that Microsoft needs to get their heads in the game. 

[lflorencio]

Very very very sad this situation...

It's hard to imagine other application with more users in a company than the Excel.

I'm developing an aplications with Forms, Excel and (I would like) Flow and Outlook.

After fill an form, with a formula in Excel I put an identifier in a cell to filter and send these rows in a e-mail body.

But... I can't do this because Excel with formula is nos suported by MS Flow!!!

Come on Microsoft ... Excel brought us here, let's not forget it now, right?

[mvrao26]

Hi,

Unitl we have solution from Microsoft, here is the workaround I used and it seems to be working. Let me know if it works for you...

1. Install Power Query.

2. Use powerquery to get the data from the source table with formulas and simply load it to a new worksheet with a new table name called Upload table. (For example)

3. Run the flow with Excel Get Rows action on the Upload Table. This new Upload table only contains the values and Get Rows action worked for me.

Thanks,

[Anonymous]

mvrao26, your workaround sounds promising. I've never used Power Query before, but from a cursory look, seems like I could make this work.

Before I dig too deep into this- that second step "Use powerquery to get the data from the source table with formulas"- is there a way to automate that process so that's it's always up to date, or would my "Upload table" have to be manually refreshed to remain up-to-date with the original source table, since my source table is a pivot table report based on an ever-growing original data set?

Thanks for your insightful response!!