6

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

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
  • could this post from Paul help you? Was you looking for this kind of info? – Hitodama Apr 16 '15 at 7:52
  • Certainly looking like it - but will verify it works today – Steph Locke Apr 16 '15 at 7:56
  • @SPArchaeologist Unfortunately life has waylaid me but it looks the right stuff - are you able to make it an answer so I can provide you with the bounty? – Steph Locke Apr 21 '15 at 14:29
  • Can try, but it is pretty long. Will try to make the best of it. – Hitodama Apr 21 '15 at 14:58
  • No need to replicate - I think your answer does it fine – Steph Locke Apr 21 '15 at 15:14
0
+100

Searching for a solution, the only source that seems to provide a full answer is Paul Ryan blog here. 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. I will try to obtain his permission to replicate the info here to avoid link root, for now please refer to his blog.

1

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

0

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("gthatcher@prandur.onmicrosoft.com", $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"]
}
  • 1
    Unfortunately, this isn't an interoperable solution. This would require, currently, the ability to connect to a server/ machine with powershell on. I use R on both Linux & Windows machines so the API is a much more preferable (if not much harder) route. – Steph Locke Apr 20 '15 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.