Unanswered

Excel Get Rows from file stored on SharePoint

(0) Share

Report

Posted on by geotech

All,

I have a spreadsheet stored on SharePoint that contains a pivot table that I would like emailed periodically to a group of users. When attempting to setup the Flow, I'm having an issue using the Excel > Get rows action when specifying the file location. Apparently, SharePoint is one of the Microsoft Apps that is not a connection type for this action? I see that OneDrive for Business is, as well as other Microsoft and non-Microsoft platforms; however, SharePoint is not listed.

Am I setting up this Flow incorrectly or is there a workaround for accessing spreadsheet data stored on SharePoint for the purposes that I need?

Thank you

Categories:

Using connectors

I have the same question (0)

All responses (9)

Answers (1)

geotech 18 on at

Like (3)

Report

My solution to retrieving the row values from the spreadsheet stored on SharePoint is to have a flow that copies the spreadsheet into my OneDrive for Business folder periodically. Then I can use the Excel: Get Rows action to retrieve the values needed to insert into the email. The issue I'm now facing is that the Table Name property does not recognize PivotTable names. Does anyone have a fix for this that they can share?

Was this reply helpful? Yes No
Verified answer

v-monli-msft on at

Like (0)

Report

Hi @geotech,

To work with Flow/PowerApps, data in your Excel files must be formated as tables. As Flow can only recognize tables in Excel.

Regards,

Mona

Was this reply helpful? Yes No
Tanner 190 on at

Like (4)

Report

I don't see how this solves the OP's problem of needing a SharePoint connection.

Can you extrapolate on how formatting data in an Excel file as a table will somehow make "SharePoint" appear as an option in the connection choice list in Microsoft Flow's "Excel - Get Rows" action?

Was this reply helpful? Yes No
boydsi 23 on at

Like (0)

Report

I'm having the same problem. It's now May and I'm still not seeing any way to get rows of data from an excel file stored in SharePoint without copying the file to OneDrive. That's not an option for a department need. Someone, please help.

Was this reply helpful? Yes No
ChadVKealey 1,393 on at

Like (2)

Report

There is an action (in preview, but existing nonetheless) called "List rows present in a table" (see below) that can do what you want, but, as someone else noted, the data in the Excel file needs to be contained in a table. This seems to work fine, but there appears to be a limit to the number of columns it can read. I have a spreadsheet with about 50 columns and it only reads out to the 32nd column. Maybe that's a limitation in the preview. Or, our users need to pre-trim their data to only those columns they really need.

Was this reply helpful? Yes No
jakeMannion 146 on at

Like (1)

Report

No luck here, either -- I hammered away at it for a while. Here's the culprit, I suspect:

The template language expression 'json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_excelonlinebusiness']['connectionId']' cannot be evaluated because property 'shared_excelonlinebusiness' doesn't exist, available properties are 'shared_sharepointonline, shared_approvals, shared_office365users, shared_sendmail ...

It's surprising to see a Flow method that's so straightforward on G-Drive / Sheets but an absolute crapshoot with SP Online / Excel. Can't complain too much, thought -- it was awfully fun getting off SP Workflow for a change.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (4)

Report

Unsure how this was accepted as solution. The problem was with the lack of source actions for Excel when Excel file is in Sharepoint. When the file is in OneDrive you can pull from rows and many other options. When it is in Sharepoint all you can do is copy the file or some kind of file manipulation. You cannot use the Get Rows and other actions. The workaround in the replies by original author looks to be the best as you can copy the file to OneDrive or Copy to Sharepoint and sync the files. I hate solutions like this as what is the point of the Sharepoint library if I cannot use the other O365 functionality that is available in OneDrive. I was looking into this because Planner is the O365 project management choice it seems. But you cannot extend it to make it more useful for project task management. So I was going to use Excel file in Doc Lib that would be shared by team. Planner would be the summary front end but we could keep details in Excel. Excel is so convenient for editing big lists. Anything else is cumbersome and awkward or not even an option. But alas the file cannot exist in Sharepoint, it must reside in OneDrive. So will come up with klugey workaround so we can use Planner. Allow extensions to Planner and allow linking to Excel or Sharepoint list and problem solved. It looks like PowerApps have more options but I did not go there as they are more work to develop and administer.

Was this reply helpful? Yes No
ChadVKealey 1,393 on at

Like (0)

Report

I believe there are newer actions (possibly still in Preview) that allow similar operations (get rows from table) against Excel files stored in SharePoint. I ran across them on another project, but didn't do any extensive testing.

Was this reply helpful? Yes No
james_hathaway 427 on at

Like (1)

Report

I Agree - the so-called "Solution" DOES NOT ANSWER THE QUESTION.

I also agree that this is simply stupid:
So, you can read data from an Excel file on OneDrive, but not from an Excel file on a SharePoint Document Library?
Isn't OneDrive just a SharePoint Document Library anyway? Just with tighter permissions? What's the difference?

My experience with Microsoft Products just continues to get worse and worse.
It's like they spend all their time on the complicated stuff, and completely ignore the very basics....

Sort it out MS!

Was this reply helpful? Yes No