How to automatically download files from SharePoint

Here is an example of how to automatically download files from SharePoint Online by using PowerShell. By knowing how to run PowerShell script from the Windows command line (CMD) you can also build a workaround to download files during R or Python script execution.

SharePoint App Principal

In the beginning, you have to register the App Principal and configure it properly. App Principal is used to authenticating the app with App Only Policy instead of real user credentials. It won’t ask the user to input the user name and password. It is a reliable solution if the account Multi-Factor Authentication is enabled.

Click here to read more about that: How to automatically upload files to SharePoint.

Download files from the SharePoint site with the PowerShell script

First of all, you will need to install the SharePointPnPPowerShellOnline module.

In this script, I call the PowerShell module from a custom location. Do this if necessary.

Import-Module "C:\CustomLocation\SharePointPnPPowerShellOnline" -Force

#Site collection URL
$SiteURL = "https://YourCompany.sharepoint.com/sites/Your-SiteName"

#Connect to SharePoint Online with AppId and AppSecret
Connect-PnPOnline -Url $SiteURL -AppId "HereIsYourClientId" -AppSecret "HereIsYourClientSecret"

$FileRelativeURL = "/Shared Documents/YourLocation/SKEY.csv" #Relative Path of the Library

$DownloadPath ="C:\YourLocation"

#Download File from SharePoint Online Library
Get-PnPFile -Url $FileRelativeURL -Path $DownloadPath -AsFile -Force

Use PowerShell script within the R script

If you are interested to download files to SharePoint that you generated with R, that is easy. Prepare your PowerShell script as in the example above and execute it during R script.

system("cmd.exe", input = paste("powershell C:\\My\\location\\update_files.ps1"))

Click here to read more about it: Execute Windows CMD command (PowerShell) from an R script.