Accessing Excel file from Sharepoint with R

Asked 20/1, 2015 at 15:21 Answered 11/5, 2023 at 18:4

am trying to write an R script that will access an Excel file that is stored on my company's Sharepoint page so that I can make a few calculations and plot the results. I've tried various ways to do this (download.file, RCurl getURL(), gdata), but I can't seem to figure out how to do this. The url is HTTPS and there should be a username and password required. I've gotten the closest with this code:

require(RCurl)
URL<-"https://companyname.sharepoint.com/sites/folder/_layouts/15/WopiFrame.aspx?sourcedoc={2DCC2ED7-1C13-4910-AFAD-4A9ACFF1C797}&file=myfile.xlsx&action=default'  
f<-getURL(URL,verbose=T,ssl.verifyhost=F,ssl.verifypeer=F,userpwd="mylogin:mypw")

This seems to connect (although the username and password don't seem to matter) and returns

> f  
[1] "<html><head><title>Object moved</title></head><body>\r\n<h2>Object moved to <a href=\"https://companyname.sharepoint.com/sites/_layouts/15/WopiFrame2.aspx?sourcedoc={2DCC2ED7-1C13-4910-AFAD-4A9ACFF1C797}&amp;file=MyFile.xlsx&amp;action=default\">here</a>.</h2>\r\n</body></html>\r\n"`

However, I'm not sure what to do at this point, or even if I'm on the right track. Any help will be greatly appreciated.

Moazami answered 20/1, 2015 at 15:21 Comment(2)

Have you had any luck accessing it, yet? I have a similar question. – Lardner 29/2, 2016 at 17:10

No, I wasn't able to figure it out. – Moazami 1/3, 2016 at 17:22

I use

library(readxl)
read_excel('//companySharepointSite/project/.../ExcelFilename.xlsx', 'Sheet1', skip=1)

Note, no https:, and sometimes I have to open the file first (i.e., cut and paste //companySharepointSite/project/.../ExcelFilename.xlsx into my browser's address bar)

Alesiaalessandra answered 14/5, 2016 at 4:28 Comment(1)

This does not work for me, it simply returns an html stating that I need to login. Thoughts? – Lossa 19/6, 2018 at 16:7

I found that other answers did not work for me, perhaps because I am on a Mac, which obviously does not play as well with Microsoft products such as Sharepoint.

Ended up having to split it into two pieces: first download the Excel file to disk and then separately read that Excel file.

library(httr)
library(readxl)

# the URL of your sharepoint file
file_url <- "https://yoursharepointsite/Documents/yourfile.xlsx"

# save the excel file to disk
GET(file_url, 
    authenticate(active_directory_username, active_directory_password, "ntlm"),
    write_disk("tempfile.xlsx", overwrite = TRUE))

# save to dataframe
df <- read_excel("tempfile.xlsx")
df

# remove excel file from disk
file.remove("tempfile.xlsx")

This gets the job done, though would be interested if anyone knows how to avoid the interim step of writing to disk.

N.B. Depending on your specific machine/network/Sharepoint configuration, you may also be able to just use authenticate(":",":","ntlm") per this answer.

Mattison answered 22/7, 2020 at 14:47 Comment(1)

Did not work for me. – Begrudge 27/10, 2023 at 3:7

I was unable to accomplish this using hints from answers above in R (I tried many approaches found on this site). However, just to highlight the response by @RyanBradley above and especially the response by @ZS27:

I instead had to use the OneDrive Desktop client (Windows) to allow me to sync the folder to my computer. Newer versions of SharePoint (like that found in MS Teams) have a sync button or feature in the document libraries / folders that interfaces with OneDrive.

This is the functional equivalent of mounting the folder as a network drive, so R interfaces with the file as if it was a part of your file system. Works for me.

Thrive answered 18/8, 2020 at 17:56 Comment(1)

Yes - this is easy and quick. I just synced from within Teams actually. Now I can "see" the file on my computer and can open it just like any other file. Thanks! – Kabyle 11/9, 2020 at 17:2

I had a situation exactly like you. I want to access an excel file, available on an sharepoint site using R programming language.

I have also surfed many stuff in Internet and I didn't find anything relevant to my requirement.

Then, I have attempted the following thing: I have made the sharepoint folder as a network drive folder, in my local system.

Then, I have accessed that excel file(in sharepoint site) from my machine without accessing web browser.

Hence, I have copied the network path, present in my system (it will be same as your sharepoint site, however it will not have https/http. The site will start with "\" like the following: "\sharepoint.test.com\folder\path").

Launch RStudio and select Import Dataset option, under Environment section.

Choose 'From Excel'. 'Import Excel Data' form will be opened.

Under File/URL field: Paste the network path of sharepoint (copied from your machine).

Click Import, the excel file in Sharepoint will be imported in R successfully.

Ensure that the file should not have html language as input (lie %20 and all) and Backslash should be used as separator in the URL. While importing the file, provide the input of the folder name exactly, as you see.

For example: Sharepoint.microsoft.com - Sharepoint's Domain Department name - name of the Folder Project name - name of the folder Sample.xlsx - name of the file So, your URL to import dataset should be:

"\Sharepoint.microsoft.com\Department name\Project name\Sample.xlsx".

Thank you!

Jeth answered 6/4, 2020 at 6:16 Comment(0)

I recently downloaded a file from my company sharepoint by using the Microsoft365R package which facilitates authentication through the browser.

The first line of code, get_business_onedrive() opened a new tab in my browser. I briefly saw the windows login screen, then it reused my authentication from earlier in the day. Then the website read, "Authenticated with Azure Active Directory. Please close this page and return to R."

I returned to R to downloaded my file to my working directly, read the relevant data into my environment as an object, then deleted the file from the working directly.

Ideally there would be a way to skip the download part, as mentioned by Nick Kastango in a separte post. I used my company's usual browser facilitated authentication. I specifically did not want to map my Team drive to my local onedrive because I want my colleagues with access to the file to be able to use the script and not have map files to a local drive. Rather, they'll use the script and login with their own credentials.

# load relevant packages
library(Microsoft365R) #for accessing onedrive
library(readxl) #for reading excel files

get_business_onedrive() #I ran this first and the browser logged me in

list_sharepoint_sites() #list my teams in the Rstudio console

site <- get_sharepoint_site("MyTeamName") #written exactly as it was listed in the console
  
site$list_drives() #list the various drive libraries, like "documents" and "wiki"

drv <- site$get_drive()# default is the document library, so I didn't need to specify anything

#downloads the file to the project working directory
drv$download_file("foldername/filename.xlsx")

# Review the sheet names in order to select the correct one.  
excel_sheets("filename.xlsx")

#read the dataframe to my Rstudio environment
df <-read_excel("filename.xlsx", 
                       sheet = "sheetname")

#Removes the file from my working directory. I don't want the downloaded excel file to stay. I always want to be working with the current version of the spreadsheet.
file.remove("filename.xlsx")

Cerate answered 11/5, 2023 at 18:4 Comment(0)

You may need to map a network drive to the SharePoint library so that you can connect to it directly. Or if you don't want to map a network drive you could also place a shortcut to the folder in your startup folder.

Example file path: \company_sharepoint_site\ssp\site_name\sub_site_name\library_name

Example start up folder location (Windows 10): C:\Users\USER_NAME\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup

Note direction of the slashes ("\" rather than "/") is important so that your file path is interpreted as a file location, not an internet browser location. By placing such a path in a network drive or as a shortcut in your startup folder your PC should connect to it when it boots.

 # Load or install readxl
if(require(readxl) == FALSE){
  install.packages("readxl")
  if(require(readxl)== FALSE){stop("Unable to install and load readxl")}
}

# Define path to data 
data_path <- "\\\\company_sharepoint_site\\ssp\\site_name\\sub_site_name\\library_name\\Example.xlsx"

# Pull data
df_employees <- read_xlsx(data_path)

Reede answered 31/10, 2019 at 17:4 Comment(1)

You can sync the SharePoint folder to your computer via OneDrive by clicking on the "Sync" button located on the SharePoint documents toolbar. My SharePoint is synced to my C drive and enables me to connect to the document locally (C:/Users/OneDrive/my_data.xlsx). – Anthropomorphic 30/12, 2019 at 21:38

Try using the link in this format: http://site/_layouts/download.aspx?SourceUrl=url-of-document-in-library

Kosak answered 16/5, 2015 at 17:57 Comment(3)

It is considered good practice to not just past a link, but also summarise the essential points from the link. This prevents your answer from becoming useless if the link dies. – Pakistan 15/12, 2015 at 14:45

I think that the link is the format. Not a link to a document that has the format. – Apgar 10/11, 2017 at 1:27

What does the "url of document in library" part mean? where can we get that? – Gleda 23/2, 2022 at 18:22

If above doesn't work try this syntax [note slash directions]:

"\\gov.sharepoint.com@SSL/DavWWWRoot/sites/SomePath/SomePath/SomePath/SomeFile"

See this for more info about syntax and what's going on:

Connect to a site via SSL/DavWWWRoot not usual URL? Why does this make a difference?

Cellophane answered 5/1, 2021 at 15:13 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags