Excel functions example

[koroner1]

I working on wraper to this library and I have a problem to .patch information to DriveItem

There is no problem with getting values from DriveItem to Pandas.DataFrame:

import pandas as pd
from urllib.parse import unquote
from msgraph.generated.models.drive_item import DriveItem

async def _get_df_async(self, worksheet: DriveItem, headers: bool = True) -> pd.DataFrame:
	odata_id = worksheet.additional_data.get("@odata.id")
	if not odata_id:
		raise ValueError("Worksheet does not contain @odata.id")
	# Extract IDs using regex
	drive_id = unquote(re.search(r"drives\('([^']+)'\)", odata_id).group(1))
	item_id = unquote(re.search(r"items\('([^']+)'\)", odata_id).group(1))
	sheet_id = unquote(re.search(r"worksheets\(([^)]+)\)", odata_id).group(1).replace("%27", ""))
	response = await self.client.drives \
		.by_drive_id(drive_id) \
		.items.by_drive_item_id(item_id) \
		.workbook \
		.worksheets.by_workbook_worksheet_id(sheet_id) \
		.used_range \
		.get()
	values = response.additional_data.get("values", [])

	if not values:
		return pd.DataFrame()

	if headers:
		return pd.DataFrame(values[1:], columns=values[0])
	else:
		return pd.DataFrame(values)

but with current version of msgraph-sdk lib, 1.55 - there is no .patch() in .used_range
so I m looking for an example of: _set_df_async

Thank you.

[de-lazurenko]

Hey! Had the same issue. The problem is that used_range is read-only in the SDK — no .patch() on it by design.

The workaround is to use range_with_address() instead, which does have .patch():

from msgraph.generated.models.workbook_range import WorkbookRange

async def _set_df_async(self, df: pd.DataFrame, worksheet: DriveItem, include_headers: bool = True):
    odata_id = worksheet.additional_data.get("@odata.id")
    drive_id = unquote(re.search(r"drives\('([^']+)'\)", odata_id).group(1))
    item_id = unquote(re.search(r"items\('([^']+)'\)", odata_id).group(1))
    sheet_id = unquote(re.search(r"worksheets\(([^)]+)\)", odata_id).group(1).replace("%27", ""))

    if include_headers:
        values = [df.columns.tolist()] + df.values.tolist()
    else:
        values = df.values.tolist()

    # Build address range, e.g. "A1:C5"
    n_rows = len(values)
    n_cols = len(values[0])
    end_col = chr(ord('A') + n_cols - 1)  # works up to 26 cols
    address = f"A1:{end_col}{n_rows}"

    request_body = WorkbookRange()
    request_body.values = values

    await self.client.drives \
        .by_drive_id(drive_id) \
        .items.by_drive_item_id(item_id) \
        .workbook \
        .worksheets.by_workbook_worksheet_id(sheet_id) \
        .range_with_address(address) \
        .patch(request_body)

Two things to keep in mind: the column letter hack (chr(ord('A') + n_cols - 1)) breaks past column Z — if you need wider sheets, you'll need a proper column index to letter converter. Also make sure values contains only JSON-serialisable types (strings, ints, floats) — pandas NaN will throw.

[koroner1]

Thank you for replay and your code, but I have again problem with .patch:

        await self.client.drives \
            .by_drive_id(drive_id) \
            .items.by_drive_item_id(item_id) \
            .workbook \
            .worksheets.by_workbook_worksheet_id(sheet_id) \
            .range_with_address(address) \
>           .patch(request_body)
             ^^^^^
E       AttributeError: 'RangeWithAddressRequestBuilder' object has no attribute 'patch'

range_with_address_request_builder.py

which msgraph-sdk version do you use ?