I am attempting to read the XLSX file in Sharepoint that is fed from an Excel Form. I am utilizing python 3.x to write the script for this.
I am attempting to create a solution to capture user comments for some of our processes - which are currently handled via shared excel files on network drives, and is a very messy outcome. I have found a number of articles on SO and other sources that indicate I need to authenticate with Sharepoint and am trying to get this to work in the code below.
I am importing the following libraries ->
from office365.runtime.auth.authentication_context import
AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.file import File
And I have another section that is bringing in my credentials from an encrypted file. I have checked the variables those are populating (username, password) and verified that those are correct.
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("Authentication successful")
response = File.open_binary(ctx, url)
#save data to BytesIO stream
bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0) #set file object to start
#read excel file and each sheet into pandas dataframe
df = pd.read_excel(bytes_file_obj, sheetname = 'Form1')
I am expecting the connection to work and populate my DF from the sheet in excel - but am getting the following error message - which made me initially think my credentials were not coming through properly, but they are. I am also the owner of the folder that I am trying to access, so there are no permissions issues with my credentials access that folder.
An error occurred while retrieving token: AADSTS90023: Invalid STS
request.
An error occurred while retrieving auth cookies
-------------------------------------------------------------------------
--
TypeError Traceback (most recent call
last)
<ipython-input-7-90f58cf903a0> in <module>
32 web = ctx.web
33 ctx.load(web)
---> 34 ctx.execute_query()
35 print("Authentication successful")
36
C:\ProgramData\Anaconda3\lib\site-
packages\office365\runtime\client_runtime_context.py in
execute_query(self)
36
37 def execute_query(self):
---> 38 self.pending_request.execute_query()
39
40 def add_query(self, query, result_object=None):
C:\ProgramData\Anaconda3\lib\site-
packages\office365\runtime\client_request.py in execute_query(self, query,
result_object)
25 if query:
26 return self.execute_single_query(query,
result_object)
---> 27 return self.execute_pending_queries()
28
29 def execute_pending_queries(self):
C:\ProgramData\Anaconda3\lib\site-
packages\office365\runtime\client_request.py in
execute_pending_queries(self)
31 for query in self.__queries:
32 request = self.build_request(query)
---> 33 response = self.execute_request_direct(request)
34 self.process_payload_json(query, response)
35 finally:
C:\ProgramData\Anaconda3\lib\site-
packages\office365\runtime\client_request.py in
execute_request_direct(self, request_options)
102 def execute_request_direct(self, request_options):
103 """Execute client request"""
--> 104 self.context.authenticate_request(request_options)
105 if request_options.method == HttpMethod.Post:
106 from office365.sharepoint.client_context import
ClientContext
C:\ProgramData\Anaconda3\lib\site-
packages\office365\runtime\client_runtime_context.py in
authenticate_request(self, request)
14
15 def authenticate_request(self, request):
---> 16 self.__auth_context.authenticate_request(request)
17
18 @property
C:\ProgramData\Anaconda3\lib\site-
packages\office365\runtime\auth\authentication_context.py in
authenticate_request(self, request_options)
33 """Authenticate request"""
34 if isinstance(self.provider, SamlTokenProvider):
---> 35 request_options.set_header('Cookie',
self.provider.get_authentication_cookie())
36 elif isinstance(self.provider, ACSTokenProvider) or
isinstance(self.provider, OAuthTokenProvider):
37 request_options.set_header('Authorization',
self.provider.get_authorization_header())
C:\ProgramData\Anaconda3\lib\site-
packages\office365\runtime\auth\saml_token_provider.py in
get_authentication_cookie(self)
67
68 logger.debug_secrets("self.FedAuth: %s\nself.rtFa: %s",
self.FedAuth, self.rtFa)
---> 69 return 'FedAuth=' + self.FedAuth + '; rtFa=' + self.rtFa
70
71 def get_last_error(self):
TypeError: can only concatenate str (not "NoneType") to str