pythonexcelazuremicrosoft-graph-apimicrosoft-graph-files

How to format Graph API call to update Excel cell?


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 ids 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:


Solution

  • I see several issues not relevant to the exception:

    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.