Import Excel Data into Powershell from SharePoint

Question

I use a lot the ImportExcel module in PowerShell and it works great with local and locally synced files. I have few Teams/SharePoint library syncing with my OneDrive and I use something on these lines to import excel data and then manipulate them directly in PowerShell (paths, file names and links are fictitious):

$File    = "C:\Users\Username\OneDriveFolder\File.xlsm"
$Sheet   = "Sheet1" 
$DataSet = Import-Excel -Path $File -WorkSheetname $Sheet -DataOnly -StartRow 2
$Data    = "Example"
$Query   =  $DataSet.Where({$_.$Data -like $Data})

I was wondering if it is was possible to use a SharePoint URL instead of a local path for the -Path attribute since I would like to import data from an Excel that is located on a SharePoint but it is not synced to my OneDrive:

$File    = "https://companygroup.sharepoint.com/sites/sitefolders/File.xlsm"
$Sheet   = "Sheet1" 
$DataSet = Import-Excel -Path $File -WorkSheetname $Sheet -DataOnly -StartRow 2
$Data    = "Example"
$Query   =  $DataSet.Where({$_.$Data -like $Data})

If I try to do that, I get a file not found error:

'https://companygroup.sharepoint.com/sites/sitefolders/File.xlsm' file not found
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.4.1\Public\Import-Excel.ps1:118 char:21
+                     throw "'$($Path)' file not found"
+                     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: ('https://company... file not found:String) [], RuntimeException
    + FullyQualifiedErrorId : 'https://companygroup.sharepoint.com/sites/sitefolders/File.xlsm' file not found

Is there a way to use the ImportExcel module like that? If it only works with local/locally synced files, is there an alternative to do what I would like to do?

Thanks

My advice would be to look at using the Microsoft Graph API to download the Excel file. If the Excel file is unprotected and open to the public, you might be able to download without the Graph API. — Robin
– Robin, Commented Aug 18, 2022 at 11:51
Thanks for your advice but that won't do since I wrote a script that has to run several actions at once through a foreach clause. Downloading the file locally it is not really efficient. I can already download it locally but I do not want to download it every time there we update something. Also, when syncing it via OneDrive, changes take few minutes to sync. I really need a way to work on the file as it is on the SharePoint. Thanks anyway — Andy Deegee
– Andy Deegee, Commented Aug 19, 2022 at 8:35

Andy Deegee · Accepted Answer · 2022-08-19 14:48:54Z

2

Managed to find the way thanks to a page that talked about something similar: https://learn.microsoft.com/en-us/answers/questions/741691/how-to-load-and-read-excel-file-hosted-in-sharepoi.html

You just need to type the link as if it were a local path, for instance:

$File    = "\\companygroup.sharepoint.com\sites\siteFolders\File.xlsm"
$Sheet   = "Sheet1" 
$DataSet = Import-Excel -Path $File -WorkSheetname $Sheet -DataOnly -StartRow 2
$Data    = "Example"
$Query   =  $DataSet.Where({$_.$Data -like $Data})

answered Aug 19, 2022 at 14:48

Andy Deegee

412 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import Excel Data into Powershell from SharePoint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related