cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
geotech
Level: Powered On

Excel Get Rows from file stored on SharePoint

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Excel Get Rows from file stored on SharePoint

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

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
geotech
Level: Powered On

Re: Excel Get Rows from file stored on SharePoint

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? 

Community Support Team
Community Support Team

Re: Excel Get Rows from file stored on SharePoint

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

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Tanner
Level 8

Re: Excel Get Rows from file stored on SharePoint

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
Level: Powered On

Re: Excel Get Rows from file stored on SharePoint

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
Level 10

Re: Excel Get Rows from file stored on SharePoint

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.

 

list-rows-in-excel.png

jake
Level: Powered On

Re: Excel Get Rows from file stored on SharePoint

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.

wbbeasley
Level: Power Up

Re: Excel Get Rows from file stored on SharePoint

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
Level 10

Re: Excel Get Rows from file stored on SharePoint

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.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Users Online
Currently online: 388 members 4,472 guests
Please welcome our newest community members: