Consume SharePoint online lists using REST API (in R)

Question

I would like to be able to query lists that are on my sharepoint online site via R.

I'm having difficulties ascertaining the right method for authenticating my requests. These requests would be primarily ad-hoc, and not some form of dedicated app.

I've seen a number of other SO posts re: authentication but few relate to non-app and non-c# scenarios.

Available documentation

• SharePoint app authentication
• SharePoint 2013 REST Service

Technology I'm going to be working with

• R
• HEADLESS ubuntu 12LTS server
• API package: httr

Task

Get some sort of authentication token, on the fly, for accessing list data via the REST API based on my user's access rights.

What is the authentication method and what are the steps & permissions are needed to authenticate a call to the sharepoint online REST API?

e.g.

1. Go to x and do y
2. Put value z into your app
3. Set up values a, b, z for auth
4. Generate signature c

Answer 1

Searching for a solution, the only source that seems to provide a full answer is Paul Ryan blog (link no longer available, refer to the archive.org version). The process he describes requires three steps:

1. Get the security token
2. Get the access token
3. Get the request digest

for each step he describes the endpoint to call and the info to post.

---

Update - Feb, 2020

I was unable to get a permission to re-post here, so sadly I will have to refrain posting the full info to avoid plagiarism. The original link sadly is no longer available, but an archived copy can be found at this link.

Will try and check if it is appropriate to duplicate the content here now that the original source does no longer exist.

Answer 2

I write this package to connect to both SharePoint Online and SharePoint Server: https://github.com/LukasK13/sharepointr

Answer 3

Instead of using the REST Api, you might try using PowerShell (which calls the REST API for you, and also takes care of Authentication, see below).

Googling "PowerShell and R" will give you lots of ways to do that.

To get PowerShell to query lists from SharePoint Online (much harder than for SharePoint On-Premises), do the following:

1.) Install SharePoint Online Management Shell (https://www.microsoft.com/en-us/download/details.aspx?id=35588) so you can run PowerShell commands This will allow you to run PowerShell commands against SharePoint online

2.) Install the SharePoint Client Components, http://www.microsoft.com/en-us/download/details.aspx?id=42038 (so we can load the SharePoint CSOM libraries; note that SharePoint Online's PowerShell doesn't give you much)

3.) Run the SharePoint Online Management shell, and enter the following:

# got some great ideas from these sites:
#  http://www.hartsteve.com/2013/06/sharepoint-online-powershell/
# https://community.qlik.com/thread/143906
#  https://karinebosch.wordpress.com/2012/02/03/caml-and-the-client-object-model/)

# Load the SharePoint Client Components which you installed
Add-Type –Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll" 
Add-Type –Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" 
# Authenticate - can automate this so you don't have to enter password manually
# Note the .sharepoint.com
$siteUrl = “https://prandur.sharepoint.com” 
$password = Read-Host -Prompt "Enter password" -AsSecureString 
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl) 
# note the .onmicrosoft.com; change this if you've correctly setup a domain for your SharePoint Online
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials("[email protected]", $password) 
$ctx.Credentials = $credentials 

# Get the list you are interested in; change name as appropriate
$list = $ctx.get_web().get_lists().getByTitle("Documents")
$ctx.Load($list)
$ctx.ExecuteQuery()

# Print out the title, so we know we got the list
$list.Title;

# Create a CAML query to get the items and fields you want
# Lots of possibilities here for specifying filters and fields you want
$caml = new-object Microsoft.SharePoint.Client.CamlQuery
$caml.ViewXML = "<View />"

# Now, actually get the items
$items = $list.GetItems($caml)
# May need to do some additional loading tricks if you need some of the non-default fields
$ctx.Load($items)
$ctx.ExecuteQuery()

# now, print some info about each item (or have your R code do something with each item)
foreach($item in $items)
{
    $item["FileLeafRef"]
}