Pulling data from a sharepoint list via rest API

I have found scattered articles about this topic but still can't find the exact resolution. I am trying to pull results from a Sharepoint list into R. In my browser I can hit "https://company.sharepoint.com/sites/SITE/site/_vti_bin/client.svc/Web/Lists/getbytitle('listName')/Items" and I can see the results I want. When I try to pull this via R I get a 403 error one way and a 200 the other way but I can't seem to get results. Unfortunately I can't provide a reprex because it's a private company SP site but here's what I've tried.

library(httr)
username <- "username@company.com"
password <- "password"

call <- "https://company.sharepoint.com/sites/SITE/site/_vti_bin/client.svc/Web/Lists/getbytitle('listName')/Item"

rest_call <- GET(call, authenticate(username,password,type="any")) #or type="basic"

This is what rest_call evaluates to:
Response [https://company.sharepoint.com/sites/SITE/site/_vti_bin/client.svc/Web/Lists/getbytitle('listName')/Item]
Date: 2018-09-04 21:01
Status: 403
Content-Type: application/xml;charset=utf-8
Size: 323 B

I've also tried this route:

library(RCurl)
library(XML)
handle <- handle("https://login.microsoftonline.com") 
path   <- "extSTS.srf"

# fields found in the login form.
login <- list(
  amember_login = username
 ,amember_pass  = password
 ,amember_redirect_url = 
   "https://company.sharepoint.com/sites/SITE/site/_vti_bin/client.svc/Web/Lists/getbytitle('listName')/Item"
)

response <- POST(handle = handle, path = path, body = login)
#tried both of these
test <- read_xml(response)
test <- readHTMLTable(content(response, "text"))

Response evaluates to the following, which makes it seem as though it was successful, but the redirect URL doesn't seem to work and I don't actually get the list content:
Show in New WindowClear OutputExpand/Collapse Output
Response [https://login.microsoftonline.com/extSTS.srf]
Date: 2018-09-04 20:17
Status: 200
Content-Type: application/soap+xml; charset=utf-8
Size: 1.37 kB

I've found a few articles that seem to have worked for people to authenticate to SP and pull data but those code bits haven't worked (and I've tried a lot of combos of that code). Do I need to do something with OAuth or have something set on the SP site itself. I feel like I'm close and I am also approaching the giving up point, any insights or thoughts would be appreciated.

I know in my company we use kerberos for authentification on windows. Sharepoint use that and I needed to precise it when making a request

  • The environment that make the request must have the kerberos ticket
  • The authentification information is then know on windows
  • I used httr::authenticate("", "", type = "ntlm") to pass through

It really is just hint because it could be so different in your case, but i thought anything could help... I struggled a lot at the time until I find that it used ntlm.

Obviously, I also needed to check my proxy setting and SSL settings to make it work.

Hope it is of any help.

1 Like

Thanks! Really at this point any thoughts are helpful. I'm not sure we use NTLM auth, I tried what you have and that didn't work but I'm curious what proxy and ssl settings. I don't have a proxy setup and I don't have Use SSL 3.0 checked.
I'll also try to validate for sure what type of auth we're using for SP.

I wrote this package for connection to both SharePoint Server and SharePoint online:

6 Likes

This is awesome, I plan to test this out at some point soon, appreciate you sharing this months after my post!

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.