Menu
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}&file=MyFile.xlsx&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.
659
The Microsoft365R package makes it easy to connect R with Outlook, Teams, OneDrive, and SharePoint.
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)
151
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.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
