Accessing Excel file from Sharepoint with R

jim616 picture jim616 · Jan 20, 2015 · Viewed 17.8k times · Source

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.

Answer

Peter Alspach picture Peter Alspach · May 14, 2016

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)