Interacting with SharePoint Online documents using Python

My organisation has been using SharePoint Online for a few years now as a way to collaborate both across the organisation and with external partners. One thing that was missing however, was the automation of the data contained within documents on SharePoint to enable analysis further down the line. Various services had set up manual processes to use this data but that is obviously not a good use of anyone’s time so we needed a solution.

My first port of call was always going to be python so I set about looking at the possibilities. There is a lot of information online about how to interact with documents on an internal installation of SharePoint Server but not ‘beginner level’ guidance when it comes to SharePoint Online using SAML.

Luckily there is the Office365-REST-Python-Client module that can be used (https://pypi.org/project/Office365-REST-Python-Client/), however, again the documentation can be a little confusing. On the guidance it gives the following code snippet example of how to interact:

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
  
ctx_auth = AuthenticationContext(url)
if ctx_auth.acquire_token_for_user(username, password):
	ctx = ClientContext(url, ctx_auth)
	web = ctx.web
	ctx.load(web)
	ctx.execute_query()
	print("Web title: {0}".format(web.properties['Title']))
 
else:
	print(ctx_auth.get_last_error())
 

Now, if you’re like me, the first thing you think to try when it calls for username and password variable, are the username and password credentials you use to access SharePoint Online, however, if you try this you will be met with various authentication errors.

It turns out that the solution is actually to use an App-Only principle – the details of which can be found here:

https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azureacs

You can engage with your SharePoint tenant administrator and they should be able to set this up using the guidance above. Once you have this set up, instead of using the ‘acquire_token_for_user’ function, this can be swapped for the ‘acquire_token_for_app’ function, and instead of passing in the username and password as arguments, you pass in the client_id and client_secret that is created when the App-Only principle is set up, so it instead looks something like:

runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
  
ctx_auth = AuthenticationContext(url)
if ctx_auth.acquire_token_for_app(client_id, client_secret):
	ctx = ClientContext(url, ctx_auth)
	web = ctx.web
	ctx.load(web)
	ctx.execute_query()
	print("Web title: {0}".format(web.properties['Title']))
  
else:
	print(ctx_auth.get_last_error())
 

The above only gets the title of the web page but you can simply swap out this code to achieve other functions. To download a (for example) Excel .xlsx file and save it to a local drive you can simply use something like:

with open(filename,’wb’) as output_file:
	response = File.open_binary(ctx, relative_url)
	output_file.write(response.content)
 

This may seem straightforward (and I’m sure some of you are thinking ‘well, duh’ right now), but I can’t tell you how long it took me to find this little snippet and work out who the right people I needed to speak to to get this set up were, and so I’m hoping this will save someone else the time of going through the search as I did.