Pulling data from a sharepoint list via rest API


#1

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.


#2

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.


#3

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.