Excel Get Rows from file stored on SharePoint

[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

[geotech]

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? 

[v-monli-msft · Accepted solution]

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

[Tanner]

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?

[boydsi]

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. 

[ChadVKealey]

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.

[jake]

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.

[Anonymous]

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.

[ChadVKealey]

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.

[james_hathaway]

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!