Reading Excel file returns 'Invalid Request' error

My goal is to use Python to read and write to an Excel file in SharePoint that other users will accessing. I developed a solution to a problem on my local machine using Pandas to read and xlWings to write. Now I'm trying to move it to SharePoint for others to use.

The solution is a non-computational application of Excel, it's essentialy to track positions of items so there will be many instances of many empty cells when the file is read. I will need to read the range A1:V20, here's what the sheet looks like:

I tried various url changes based off Microsoft's guide here, but still can't get results.

Here are the permissions my application has:

I've read some tutorials/answers to questions (i.e. here and here) and can authenticate. I've updated my url based off this answer, but no luck.

Here's the error message I am getting:

{
    "error": {
        "code": "invalidRequest",
        "message": "Invalid request"
    }
}
'value'

Previously I had the url ending in /workbook/tables('Plant_3') based off this answer but I was getting the following error:

"error": {
    "code": "BadRequest",
    "message": "Open navigation properties are not supported on OpenTypes. Property name: 'tables'.",

I beleive that error was because my file has no tables and the url was trying to link to that object/collection.

Here's my code (updated per comments):

from msal import ConfidentialClientApplication
import requests
import json
import pandas as pd
import configparser

config = configparser.ConfigParser()
config.read('config.ini')

client_id = config['entra_auth']['client_id']
client_secret = config['entra_auth']['client_secret']
tenant_id = config['entra_auth']['tenant_id']

msal_scope = ['https://graph.microsoft.com/.default']
msal_app = ConfidentialClientApplication(client_id=client_id,
                                         authority=f"https://login.microsoftonline.com/{tenant_id}",
                                         client_credential=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")

headers = {'Authorization': f'Bearer {access_token}'}
site_id = config['site']['site_id']  # https://www.powertechtips.com/check-site-id-sharepoint/
# https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-find-the-library-id-on-our-sharepoint/701e68f3-954f-490c-b3cb-ceb8bd5601d1
document_library_id = config['site']['document_library_id']
doc_id = config['site']['doc_id']  # from document details

# create url
url = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{document_library_id}/items/{doc_id}:/workbook/worksheets('Plant_3')/range(address='A1:V20')"

# Make a GET request to the Microsoft Graph API to read the Excel file as a pandas dataframe
response = requests.get(url, headers=headers)
try:  # how I want it to go
    data = response.json().get("value", [])
    # if data found, convert to dataframe.
    if data:
        df = pd.DataFrame(data)
        print(df)
    else:
        print("No data")
except Exception as e:
    print(f"Error: {e}")

I created one app registration and added same API permissions as you like this:

In SharePoint, I have one excel file named test.xlsx with below data:

Initially, I too got same error when I ran your code to retrieve above excel file data:

To resolve the error, I ran below modified python code and got the response with excel data successfully as below:

from msal import ConfidentialClientApplication
import requests
import pandas as pd
import configparser

# Read the config file for credentials and details
config = configparser.ConfigParser()
config.read('config.ini')

client_id = config['entra_auth']['client_id']
client_secret = config['entra_auth']['client_secret']
tenant_id = config['entra_auth']['tenant_id']

# Set up Microsoft Graph authentication
msal_scope = ['https://graph.microsoft.com/.default']
msal_app = ConfidentialClientApplication(client_id=client_id,
                                         authority=f"https://login.microsoftonline.com/{tenant_id}",
                                         client_credential=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}'}

# 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']

# Use 'usedRange' to automatically detect the range with data
url = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{document_library_id}/items/{doc_id}/workbook/worksheets('Sheet1')/usedRange"

# Make a GET request to Microsoft Graph API to read the worksheet range
response = requests.get(url, headers=headers)

# Extract only the 'values' part of the response
try:
    data = response.json().get('values', [])
    
    # If data is found, convert it to a pandas DataFrame and print it
    if data:
        df = pd.DataFrame(data[1:], columns=data[0])  # First row as column headers
        print(df)
    else:
        print("No data found.")
except Exception as e:
    print(f"Error: {e}")

Response:

UPDATE:

For writing new data into excel file using Graph API in Python, I used below modified code and got response like this:

from msal import ConfidentialClientApplication
import requests
import pandas as pd
import configparser
import json

config = configparser.ConfigParser()
config.read('config.ini')

client_id = config['entra_auth']['client_id']
client_secret = config['entra_auth']['client_secret']
tenant_id = config['entra_auth']['tenant_id']

msal_scope = ['https://graph.microsoft.com/.default']
msal_app = ConfidentialClientApplication(client_id=client_id,
                                         authority=f"https://login.microsoftonline.com/{tenant_id}",
                                         client_credential=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")

headers = {'Authorization': f'Bearer {access_token}', 'Content-Type': 'application/json'}

site_id = config['site']['site_id'] 
document_library_id = config['site']['document_library_id']
doc_id = config['site']['doc_id']

# Define the new data you want to write (e.g., add new rows of data)
new_data = [
    ["Venkat", "8k"],
    ["Arko", "4k"]
]

body = {
    "values": new_data
}

# Here writing to A6:B7 -> 2 columns and 2 rows (Venkat and Arko)
url_write = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{document_library_id}/items/{doc_id}/workbook/worksheets('Sheet1')/range(address='A6:B7')"

response_write = requests.patch(url_write, headers=headers, data=json.dumps(body))

if response_write.status_code == 200:
    print("Data successfully written to Excel.")
else:
    print(f"Error writing data: {response_write.text}")
    exit()

# Use 'usedRange' to automatically detect the range with data (including the newly added data)
url_read = f"https://graph.microsoft.com/v1.0/sites/{site_id}/drives/{document_library_id}/items/{doc_id}/workbook/worksheets('Sheet1')/usedRange"

response = requests.get(url_read, headers=headers)

try:
    data = response.json().get('values', [])
    
    # If data is found, convert it to a pandas DataFrame and print it
    if data:
        df = pd.DataFrame(data[1:], columns=data[0])  # First row as column headers
        print("Updated Excel Data:")
        print(df)
    else:
        print("No data found.")
except Exception as e:
    print(f"Error while reading: {e}")

Response: