I want to update an Excel cell value (hosted in SharePoint) using the Microsoft Graph API. Documentation is here.
I have had success with other API calls (format updates, clearing cells) using a similar format as below, please help me understand what I'm doing wrong.
In the code example I want to write "41373" to cell B:17 in sheet 'Plant_3'. The code example returns a 400
code error:
"code": "InvalidAuthenticationToken",
"message": "Access token is empty.",
This message can be generic and mean the formatting of an API call is wrong rather than an authentication problem. Other Graph API calls work with the same id
s and keys
so I doubt it's an authentication issue.
def graph_config_params():
"""
read authentication parameters from config file, return dict with results
:return: (dict) configuration parameters
"""
try:
# Read the config file
config = configparser.ConfigParser()
config.read('config.ini')
# assign authorization tokens from config file to variables
return_dict = {'client_id': config['entra_auth']['client_id'],
'client_secret': config['entra_auth']['client_secret'],
'tenant_id': config['entra_auth']['tenant_id'],
# Get necessary configuration data for the SharePoint file
'site_id': config['site']['site_id'],
'document_library_id': config['site']['document_library_id'],
'doc_id': config['site']['doc_id'],
'drive_id': config['site']['drive_id']}
return return_dict
except Exception as e:
log.exception(e)
def set_up_ms_graph_authentication(graph_auth_args=graph_config_params()):
"""
Create headers with access token for Microsoft Graph API to authenticate
:param graph_authentication_params:
:return: (dict) headers with access token
"""
try:
# Set up Microsoft Graph authentication
msal_scope = ['https://graph.microsoft.com/.default']
msal_app = ConfidentialClientApplication(
client_id=graph_auth_args['client_id'],
authority=f"https://login.microsoftonline.com/{graph_auth_args['tenant_id']}",
client_credential=graph_auth_args['client_secret'])
result = msal_app.acquire_token_silent(scopes=msal_scope,
account=None)
if not result:
result = msal_app.acquire_token_for_client(scopes=msal_scope)
if 'access_token' in result:
access_token = result['access_token']
else:
raise Exception("Failed to acquire token")
# Prepare request headers with the acquired access token
headers = {'Authorization': f'Bearer {access_token}'}
# log.info(f'headers: {headers}')
return headers
except Exception as e:
log.exception(e)
sheet = 'Plant_3'
# get variables that work in other Graph API calls
config_args=graph_config_params()
# get Authorization Bearer token, again works in other Graph API calls.
my_headers = set_up_ms_graph_authentication()
url= f"https://graph.microsoft.com/v1.0/sites/{config_args['site_id']}/drives/{config_args['drive_id']}/items/{config_args['doc_id']}/workbook/worksheets/{sheet}/range/(address='B:17')"
response = requests.patch(url,
headers=my_headers,
json={'values': [['41373']] })
Things I've tried:
json
to data
.json
and data
.Files.ReadWrite.All
and got a 403
error.post
and tried all configurations mentioned above.valueType
argument: [['String']]I see several issues not relevant to the exception:
B17
not B:17
/range(address='B17')
, not /range/(address='B17')
.With json
parameter the request should be
response = requests.patch(url,headers=my_headers,json={'values': [['41373']]})
With data parameter the request body must be a string and the request must contain the header content-type:application/json
response = requests.patch(url,headers=my_headers,data='{"values": [["41373"]]}')
Based on the exception the issue is with missing authorization
header or missing scope.