0

I need to save a sharepoint list as an excel/ csv file. To do so, I have followed following two SO links,

Get SharePoint List with Python

SharePlum error : "Can't get User Info List"

My code look like this, 

import pandas as pd
from shareplum import Site 
from requests_ntlm import HttpNtlmAuth

cred = HttpNtlmAuth("email_id", "password")
site = Site('https://companyname.sharepoint.com/sites/analyticsandml', auth=cred)

sp_list = site.List('Client Office List') # this creates SharePlum object
data = sp_list.GetListItems('All Items') # this will retrieve all items from list

data_df = pd.DataFrame(data[0:])
data_df.to_excel("data.xlsx")

print("Content of share point list is saved in a file.")

I'm getting the error below:

shareplum.errors.ShareplumRequestError: Shareplum HTTP Post Failed : 403 Client Error: Forbidden for url: https://companyname.sharepoint.com/sites/analyticsandml/_vti_bin/lists.asmx

Please help me with this.

Note: @Lee_MSFT on running your version of code, I get the error snapshot of which is below, enter image description here

Traceback: After using the shareplum based code, I get the following error, enter image description here

My views: Shareplum based solution works when you use office365 with no other added layer of security like SecureAuth. If MFA is enabled, like in my case, it will give you invalid credentials error.

Sarvesh Pandey
  • 322
  • 7
  • 17
  • `Shareplum HTTP Post Failed : 403 Client Error: Forbidden for url` <<-- This is a permission issue wherein the company server is preventing you from accessing it's resources. Try adding a `user-agent` as shown in this other [SO thread](https://stackoverflow.com/questions/38489386/python-requests-403-forbidden) and see if that helps. – AndrewL64 May 24 '20 at 14:49
  • I tried that but it didn't work. The error is, TypeError: Site() got an unexpected keyword argument 'headers'. Also tried it with GetListItems() with no success. – Sarvesh Pandey May 24 '20 at 15:07

1 Answers1

2

Sample demo to use Office365-REST-Python-Client to get SharePoint list data and output as excel.

import json
import pandas
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.runtime.client_request import ClientRequest
from office365.runtime.utilities.request_options import RequestOptions

url="https://xxx.sharepoint.com/"
ctx_auth = AuthenticationContext(url)
if ctx_auth.acquire_token_for_user("user@xxx.onmicrosoft.com", "password"):
  request = ClientRequest(ctx_auth)
  options = RequestOptions("{0}/sites/lee/_api/web/lists/getByTitle('ListA')/items?$select=ID,Title,ProjectType".format(url))
  options.set_header('Accept', 'application/json; odata=minimalmetadata')
  options.set_header('Content-Type', 'application/json')
  data = request.execute_request_direct(options)
  s = json.loads(data.content)
  data=s['value']
  pandas.read_json(json.dumps(data)).to_excel("output.xlsx")
  print("output")

else:
  print (ctx_auth.get_last_error())

Update:

Tried SharePlum in python 3.8.

import json
import pandas
from shareplum import Site
from shareplum import Office365

authcookie = Office365('https://xxx.sharepoint.com', username='user@xxx.onmicrosoft.com', password='password').GetCookies()
site = Site('https://xxx.sharepoint.com/sites/lee/', authcookie=authcookie)
sp_list = site.List('ListA')
data = sp_list.GetListItems('All Items')
pandas.read_json(json.dumps(data)).to_excel("output.xlsx")

Debug screenshot:

enter image description here

Lee
  • 5,305
  • 1
  • 6
  • 12
  • thank you for your answer. On running your version of code, I get an error snapshot of which is included in the question itself. Please let me know that how I can approach now. – Sarvesh Pandey May 26 '20 at 08:37
  • What's your python version? "Python 2.7 & 3.4–3.6 are supported" based on the project description. – Lee May 26 '20 at 08:48
  • Python 3.8.1 64-bit (VS Code). Oh, so is it like I need to downgrade to Python 3.6? – Sarvesh Pandey May 26 '20 at 08:50
  • Again, there's a problem, wanted to know that employee's user credentials should work fine or not? Traceback is included in the question. Eagerly waiting for your opinion. – Sarvesh Pandey May 26 '20 at 10:35
  • Attached my debug screenshot, confirm whether you enabled MFA for your account. – Lee May 27 '20 at 01:05
  • Exception has occurred: Exception ('Error authenticating against Office 365. Error from Office 365:', 'AADSTS50126: Error validating credentials due to invalid username or password.') That's the error I am getting during debug, My company uses SecureAuth Authenticate. Whether this thing is creating a problem. I don't think that MFA is enabled (I have checked it from azure portal) – Sarvesh Pandey May 27 '20 at 07:29
  • 1
    It should be related to SecureAuth, my test talent didn't integrate it. – Lee May 27 '20 at 08:26
  • I have also gone through some Microsoft docs, you are right! It is related to SecureAuth. Really interesting. So, is that mean, I cann't access file from sharepoint because of that, or do you have some workaround? – Sarvesh Pandey May 27 '20 at 08:33