Skip to main content

Notifications

Community site session details

Community site session details

Session Id : 5e1c260be2sEO3hyKAP+oI
Power Automate - Using Connectors
Answered

Excel Get Rows from file stored on SharePoint

Like (0) ShareShare
ReportReport
Posted on 19 Jul 2017 18:36:25 by 18

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

  • james_hathaway Profile Picture
    427 on 16 Jul 2020 at 12:08:48
    Re: Excel Get Rows from file stored on SharePoint

    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!

  • ChadVKealey Profile Picture
    1,393 on 23 Jan 2019 at 21:40:31
    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.

  • Community Power Platform Member Profile Picture
    on 23 Jan 2019 at 19:34:12
    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.

  • jakeMannion Profile Picture
    146 on 22 Jul 2018 at 11:20:54
    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.

  • ChadVKealey Profile Picture
    1,393 on 04 May 2018 at 19:31:59
    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

  • boydsi Profile Picture
    23 on 02 May 2018 at 15:45:33
    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. 

  • Tanner Profile Picture
    190 on 09 Jan 2018 at 19:52:09
    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?

  • Verified answer
    v-monli-msft Profile Picture
    on 21 Jul 2017 at 09:38:28
    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

  • geotech Profile Picture
    18 on 20 Jul 2017 at 16:41:44
    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? 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,765 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard

Featured topics

Loading started