How to import data from XLSX file hosted on SharePoint Online (not lists)?

1
votes

TL;DR: I'm looking for the equivalent of Excel 2019's Get Data > From File > From Workbook functionality, but where "workbook" is hosted on SharePoint online.

This seems so simple but yet after hours of searching I cannot find anything remotely close. This question does not relate to SharePoint Lists (or file lists). I am literally needing to read data from one online spreadsheet and place into another.

I have three Excel documents hosted on SharePoint Online:

  1. Revenue Tracker.xlsx
  2. Cost Tracker.xlsx
  3. Time Tracker.xlsx

I need to create a fourth file, that analyses the data from the above files, for example to cross-reference revenue against hours.

Based on solutions online, I've tried the following within Excel:

Data > Get Data > From File > From SharePoint Folder

That provided a list of documents located in that SharePoint folder.

Data > Get Data > From Online Services > From SharePoint Online List

Not what I'm looking for.

Create share link in Revenue Tracker.xlsx, then in new file Get Data > From Other Sources > From Web > Paste Url

Error: Access to the resource is forbidden. Even though I am correctly logged into Excel using my work SharePoint credentials.

1
exceloffice365sharepoint-online

1 Answers

0
votes

You can import data from Excel stored in SharePoint to Excel desktop by using Get Data > Web option.

Paste the path to the Excel file in the the URL bar that appears.

EXAMPLE: https://community.powerbi.com/t5/Desktop/Import-Data-from-an-Excel-sheet-in-SharePoint/td-p/47223