Microsoft Excel Online

The Microsoft Excel Online connector is a Palantir-provided driver for Microsoft Excel Online.

To create a new Microsoft Excel Online source, follow the standard setup flow for Palantir-provided drivers, then use the sections below for Microsoft Excel Online-specific configuration and networking. For the complete property reference, see the official Microsoft Excel Online driver documentation ↗.

Supported capabilities

CapabilityStatus
Exploration🟢 Generally available
Batch syncs🟢 Generally available
Incremental🟢 Generally available
OAuth 2.0 authentication🟢 Generally available
Table exports🟢 Generally available

Introduction

The Microsoft Excel Online connector reads live workbooks in OneDrive for Business or SharePoint Online. It exposes each worksheet as a relational table through Microsoft Graph.

Use batch syncs to read worksheet tables. Use table exports to append dataset rows to a workbook. To download a workbook or insert, update, and delete keyed rows, use a delegated Function with Microsoft Graph.

Choose an Excel connector

The connector to use depends on where the workbook is stored and how you need to access it.

ConnectorUse when
Microsoft ExcelThe workbook is stored as a file in Amazon S3, Azure Blob Storage, Azure Data Lake Storage, OneDrive, SharePoint, Google Drive, FTP, SFTP, or another supported backend.
Microsoft Excel Online (this connector)The workbook is stored in Microsoft 365 through OneDrive for Business or SharePoint Online. This connector uses Microsoft Graph and supports Entra ID authentication.
Microsoft SharePoint ExcelThe workbook is stored on an on-premises SharePoint Server 2010 or 2013 deployment. For SharePoint Online, use Microsoft Excel Online.

Use Microsoft Excel Online when your workbooks have a consistent tabular structure and you need worksheets or cell ranges exposed directly as source tables.

For greater flexibility over worksheet selection, cell ranges, data types, and workbook-specific parsing, ingest the workbook as a file with Microsoft OneDrive or Microsoft SharePoint. Then parse it in a transform using the Excel parser for Transforms, the parseExcel function in Pipeline Builder, or a library such as pandas.read_excel or openpyxl.

Authentication

Microsoft Excel Online authenticates to Microsoft 365 through Microsoft Entra ID. Set AuthScheme to AzureAD for delegated access to workbooks available to the signed-in user.

Delegated user sign-in (AzureAD)

With delegated authentication, a Microsoft 365 user signs in interactively. The source can access only files available to that user.

  1. Register an application in Microsoft Entra ID. Add a Web platform redirect URI and set it to the CallbackURL value obtained in step 3.

  2. Grant delegated Microsoft Graph permissions: Files.Read.All, Sites.Read.All, and offline_access. If your tenant restricts user consent for these scopes, an administrator who can grant tenant-wide consent must grant consent for the application.

  3. Create the Foundry source and set the JDBC properties:

    • AuthScheme = AzureAD
    • InitiateOAuth = REFRESH
    • AzureTenant = your tenant name or ID (for example contoso.onmicrosoft.com)
    • OAuthClientId / OAuthClientSecret = the registered application's client ID and secret
    • Scope = https://graph.microsoft.com/Files.Read.All https://graph.microsoft.com/Sites.Read.All offline_access

    Then copy the source's generated CallbackURL and paste it back as the application's Web redirect URI.

  4. Save, then select Authorize and complete the interactive sign-in as the user who can access the target workbook.

The Microsoft Excel Online source settings show the properties for delegated Azure AD authentication.

Configuration

The properties below are mandatory or recommended.

PropertyRequired?DescriptionDefault
AuthScheme ↗RecommendedSpecifies the type of authentication to use when connecting to Microsoft Excel Online. If this property is left blank, the default authentication is used.AzureServicePrincipal
AzureTenant ↗RecommendedIdentifies the Microsoft Excel Online tenant being used to access data, either by name (for example, contoso.onmicrosoft.com) or ID. (Conditional).—
Drive ↗RecommendedThe Id of the drive.—
InitiateOAuth ↗RecommendedSpecifies the process for obtaining or refreshing the OAuth access token, which maintains user access while an authenticated, authorized user is working.GETANDREFRESH
OAuthClientId ↗RecommendedSpecifies the client Id that was assigned when the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.—
OAuthClientSecret ↗RecommendedSpecifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret). This secret registers the custom application with the OAuth authorization server.—
Workbook ↗RecommendedThe name or Id of the workbook.—

Networking

The table below lists the domains that the source must be able to access to run.

For each domain, add a corresponding egress policy. If the source is hosted on-premises and not directly reachable from Foundry, use an agent proxy egress policy instead. The agent host itself must also be able to reach the listed domains. See using an agent as a proxy for details.

DomainRequired
graph.microsoft.comIf AzureEnvironment=GLOBAL (default)
login.microsoftonline.comIf AuthScheme=AzureAD (default), AzureServicePrincipal, AzureServicePrincipalCert AND AzureEnvironment=GLOBAL
microsoftgraph.chinacloudapi.cnIf AzureEnvironment=CHINA
login.chinacloudapi.cnIf AuthScheme=AzureAD (default), AzureServicePrincipal, AzureServicePrincipalCert AND AzureEnvironment=CHINA
graph.microsoft.usIf AzureEnvironment=USGOVT
login.microsoftonline.usIf AuthScheme=AzureAD (default), AzureServicePrincipal, AzureServicePrincipalCert AND AzureEnvironment=USGOVT or USGOVTDOD
dod-graph.microsoft.usIf AzureEnvironment=USGOVTDOD
<SharepointURL>If setting the SharepointURL property

The Foundry Network connectivity panel for a Microsoft Excel Online source, showing approved egress policies for graph.microsoft.com and login.microsoftonline.com.

OAuth 2.0 authentication

This connector supports OAuth 2.0 authentication. Follow the OAuth 2.0 guidance for Palantir-provided drivers to configure and authorize the connection.

Table exports

This connector supports table exports. Learn how to set up a table export.

Read and write data

Read worksheet tables

Each worksheet in an accessible workbook is exposed as a table named {workbook}_{worksheet}. For example, a People sheet in People.xlsx becomes People_People. In the source connection properties, set Drive or SharepointURL to select a location, and set Workbook to select a workbook by name or ID. Use a batch sync to read the resulting tables.

The Foundry source explorer previews an Excel Online worksheet as a relational table.

Set ValueRenderOption to FormattedValue, UnformattedValue, or Formula to control whether the connector returns displayed values, raw values, or formula text.

Append rows with table exports

Use a table export to insert rows from a Foundry dataset into a workbook. Table exports do not update or delete individual rows. Use the delegated Function examples when you need keyed row operations.

Use Microsoft Excel Online in code

The following examples call Microsoft Graph with the signed-in user's delegated authorization. They cover four operations in Python and TypeScript v2: download a workbook, insert a row, update a row, and delete a row.

Before using an example:

  1. Configure a Microsoft Graph OAuth 2.0 authorization-code outbound application and REST source. Grant the delegated Files.ReadWrite, offline_access, openid, and profile scopes.
  2. Allow the source to be used in code and exported to Functions. Configure source-aware egress for Microsoft Graph, Microsoft identity, and the SharePoint host used for workbook downloads.
  3. Import the source into the Function repository. Replace the source, drive, workbook item, worksheet, and source alias placeholders.
  4. Authorize the source as the user who can access the target workbook.

The writer examples use a worksheet containing only the five columns Id, Name, Role, City, and PersonKey, with headers in A1:E1 and data starting in row 2. The insert and delete operations shift cells within columns A:E. Adapt the columns and ranges to your workbook.

Download a workbook

The download endpoint ↗ returns the complete .xlsx file.

Copied!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 import base64 from urllib.parse import quote import requests from functions.api import function from functions.sources import get_source SOURCE_RID = "ri.magritte..source.REPLACE_WITH_YOUR_SOURCE_RID" DRIVE_ID = "REPLACE_WITH_YOUR_DRIVE_ID" WORKBOOK_ITEM_ID = "REPLACE_WITH_YOUR_WORKBOOK_ITEM_ID" GRAPH_BASE_URL = "https://graph.microsoft.com/v1.0" @function(api_name="downloadExcelOnlineWorkbook", sources=[SOURCE_RID]) def download_excel_online_workbook() -> str: source = get_source(rid=SOURCE_RID) access_token = source.get_session_credentials().get().access_token url = ( f"{GRAPH_BASE_URL}/drives/{quote(DRIVE_ID, safe='')}" f"/items/{quote(WORKBOOK_ITEM_ID, safe='')}/content" ) with requests.Session() as session: proxy_uri = source.get_https_proxy_uri() if proxy_uri: session.proxies.update({"http": proxy_uri, "https": proxy_uri}) response = session.get( url, headers={"Authorization": f"Bearer {access_token}"}, timeout=60, ) response.raise_for_status() return base64.b64encode(response.content).decode("ascii")
Copied!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 import { Buffer } from "node:buffer"; import { UserFacingError } from "@osdk/functions"; import { getFetch, getSource } from "@palantir/functions-sources"; const SOURCE_RID = "ri.magritte..source.REPLACE_WITH_YOUR_SOURCE_RID"; const SOURCE_ALIAS = "MicrosoftGraphExcelDelegated"; const DRIVE_ID = "REPLACE_WITH_YOUR_DRIVE_ID"; const WORKBOOK_ITEM_ID = "REPLACE_WITH_YOUR_WORKBOOK_ITEM_ID"; const GRAPH_BASE_URL = "https://graph.microsoft.com/v1.0"; export const config = { apiName: "downloadExcelOnlineWorkbook", sources: [SOURCE_ALIAS], }; export default async function downloadExcelOnlineWorkbook(): Promise<string> { const source = await getSource({ rid: SOURCE_RID }); const { accessToken } = (await source.sessionCredentials?.get()) as { accessToken: string; }; const sourceFetch = (await getFetch(source)) as typeof fetch; const url = `${GRAPH_BASE_URL}/drives/${encodeURIComponent(DRIVE_ID)}` + `/items/${encodeURIComponent(WORKBOOK_ITEM_ID)}/content`; const response = await sourceFetch(url, { method: "GET", headers: { Authorization: `Bearer ${accessToken}` }, signal: AbortSignal.timeout(60_000), }); if (!response.ok) { throw new UserFacingError(`Microsoft Graph returned HTTP ${response.status}.`); } return Buffer.from(await response.arrayBuffer()).toString("base64"); }

Insert a row

The range insert endpoint ↗ shifts existing cells down, and the range update populates the new row.

INSERT changes workbook data

Each invocation inserts a row. The example does not detect duplicate keys or recover a partially populated row. Use a non-production workbook, and inspect the workbook before retrying an uncertain result.

Copied!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 from urllib.parse import quote import requests from functions.api import function from functions.sources import get_source SOURCE_RID = "ri.magritte..source.REPLACE_WITH_YOUR_SOURCE_RID" DRIVE_ID = "REPLACE_WITH_YOUR_DRIVE_ID" WORKBOOK_ITEM_ID = "REPLACE_WITH_YOUR_WORKBOOK_ITEM_ID" WORKSHEET = "People" GRAPH_BASE_URL = "https://graph.microsoft.com/v1.0" def _delegated_session() -> requests.Session: source = get_source(rid=SOURCE_RID) session = requests.Session() access_token = source.get_session_credentials().get().access_token session.headers["Authorization"] = f"Bearer {access_token}" proxy_uri = source.get_https_proxy_uri() if proxy_uri: session.proxies.update({"http": proxy_uri, "https": proxy_uri}) return session @function(api_name="insertExcelOnlineRow", sources=[SOURCE_RID]) def insert_excel_online_row( business_key: str, name: str, role: str, city: str, ) -> str: workbook_url = ( f"{GRAPH_BASE_URL}/drives/{quote(DRIVE_ID, safe='')}" f"/items/{quote(WORKBOOK_ITEM_ID, safe='')}/workbook" ) row_url = ( f"{workbook_url}/worksheets/{quote(WORKSHEET, safe='')}" f"/range(address='{quote('A2:E2', safe='')}')" ) with _delegated_session() as session: inserted = session.post( f"{row_url}/insert", json={"shift": "Down"}, timeout=60, ) inserted.raise_for_status() populated = session.patch( row_url, json={"values": [["", name, role, city, business_key]]}, timeout=60, ) populated.raise_for_status() return f"Inserted row for {business_key}."
Copied!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 import { UserFacingError } from "@osdk/functions"; import { getFetch, getSource } from "@palantir/functions-sources"; const SOURCE_RID = "ri.magritte..source.REPLACE_WITH_YOUR_SOURCE_RID"; const SOURCE_ALIAS = "MicrosoftGraphExcelDelegated"; const DRIVE_ID = "REPLACE_WITH_YOUR_DRIVE_ID"; const WORKBOOK_ITEM_ID = "REPLACE_WITH_YOUR_WORKBOOK_ITEM_ID"; const WORKSHEET = "People"; const GRAPH_BASE_URL = "https://graph.microsoft.com/v1.0"; export const config = { apiName: "insertExcelOnlineRow", sources: [SOURCE_ALIAS], }; export default async function insertExcelOnlineRow( businessKey: string, name: string, role: string, city: string, ): Promise<string> { const source = await getSource({ rid: SOURCE_RID }); const { accessToken } = (await source.sessionCredentials?.get()) as { accessToken: string; }; const sourceFetch = (await getFetch(source)) as typeof fetch; const workbookUrl = `${GRAPH_BASE_URL}/drives/${encodeURIComponent(DRIVE_ID)}` + `/items/${encodeURIComponent(WORKBOOK_ITEM_ID)}/workbook`; const rowUrl = `${workbookUrl}/worksheets/${encodeURIComponent(WORKSHEET)}` + `/range(address='${encodeURIComponent("A2:E2")}')`; const headers = { Authorization: `Bearer ${accessToken}`, "Content-Type": "application/json", }; const inserted = await sourceFetch(`${rowUrl}/insert`, { method: "POST", headers, body: JSON.stringify({ shift: "Down" }), }); if (!inserted.ok) { throw new UserFacingError(`Microsoft Graph returned HTTP ${inserted.status}.`); } const populated = await sourceFetch(rowUrl, { method: "PATCH", headers, body: JSON.stringify({ values: [["", name, role, city, businessKey]], }), }); if (!populated.ok) { throw new UserFacingError(`Microsoft Graph returned HTTP ${populated.status}.`); } return `Inserted row for ${businessKey}.`; }

Update a row

The range update endpoint ↗ replaces the values in the matched row.

UPDATE changes workbook data

The example matches one row by PersonKey, changes its Name, Role, and City values, and writes the entire A:E range back. This replaces any formulas in those cells with values. It does not recover an ambiguous write. Inspect the workbook before retrying after a timeout or server error.

Copied!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 from urllib.parse import quote import requests from functions.api import UserFacingError, function from functions.sources import get_source SOURCE_RID = "ri.magritte..source.REPLACE_WITH_YOUR_SOURCE_RID" DRIVE_ID = "REPLACE_WITH_YOUR_DRIVE_ID" WORKBOOK_ITEM_ID = "REPLACE_WITH_YOUR_WORKBOOK_ITEM_ID" WORKSHEET = "People" GRAPH_BASE_URL = "https://graph.microsoft.com/v1.0" def _delegated_session() -> requests.Session: source = get_source(rid=SOURCE_RID) session = requests.Session() access_token = source.get_session_credentials().get().access_token session.headers["Authorization"] = f"Bearer {access_token}" proxy_uri = source.get_https_proxy_uri() if proxy_uri: session.proxies.update({"http": proxy_uri, "https": proxy_uri}) return session @function(api_name="updateExcelOnlineRow", sources=[SOURCE_RID]) def update_excel_online_row( business_key: str, name: str, role: str, city: str, ) -> str: workbook_url = ( f"{GRAPH_BASE_URL}/drives/{quote(DRIVE_ID, safe='')}" f"/items/{quote(WORKBOOK_ITEM_ID, safe='')}/workbook" ) worksheet_url = f"{workbook_url}/worksheets/{quote(WORKSHEET, safe='')}" with _delegated_session() as session: used_range = session.get( f"{worksheet_url}/usedRange(valuesOnly=true)", timeout=60, ) used_range.raise_for_status() rows = used_range.json()["values"] key_index = rows[0].index("PersonKey") matches = [ (number, row) for number, row in enumerate(rows[1:], start=2) if len(row) > key_index and str(row[key_index]) == business_key ] if len(matches) != 1: raise UserFacingError("Expected exactly one row with that PersonKey.") row_number, row = matches[0] replacement = list(row) replacement[1:4] = [name, role, city] address = quote(f"A{row_number}:E{row_number}", safe="") updated = session.patch( f"{worksheet_url}/range(address='{address}')", json={"values": [replacement]}, timeout=60, ) updated.raise_for_status() return f"Updated row for {business_key}."
Copied!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 import { UserFacingError } from "@osdk/functions"; import { getFetch, getSource } from "@palantir/functions-sources"; const SOURCE_RID = "ri.magritte..source.REPLACE_WITH_YOUR_SOURCE_RID"; const SOURCE_ALIAS = "MicrosoftGraphExcelDelegated"; const DRIVE_ID = "REPLACE_WITH_YOUR_DRIVE_ID"; const WORKBOOK_ITEM_ID = "REPLACE_WITH_YOUR_WORKBOOK_ITEM_ID"; const WORKSHEET = "People"; const GRAPH_BASE_URL = "https://graph.microsoft.com/v1.0"; export const config = { apiName: "updateExcelOnlineRow", sources: [SOURCE_ALIAS], }; export default async function updateExcelOnlineRow( businessKey: string, name: string, role: string, city: string, ): Promise<string> { const source = await getSource({ rid: SOURCE_RID }); const { accessToken } = (await source.sessionCredentials?.get()) as { accessToken: string; }; const sourceFetch = (await getFetch(source)) as typeof fetch; const workbookUrl = `${GRAPH_BASE_URL}/drives/${encodeURIComponent(DRIVE_ID)}` + `/items/${encodeURIComponent(WORKBOOK_ITEM_ID)}/workbook`; const worksheetUrl = `${workbookUrl}/worksheets/${encodeURIComponent(WORKSHEET)}`; const authorization = { Authorization: `Bearer ${accessToken}` }; const usedRange = await sourceFetch( `${worksheetUrl}/usedRange(valuesOnly=true)`, { headers: authorization }, ); if (!usedRange.ok) { throw new UserFacingError(`Microsoft Graph returned HTTP ${usedRange.status}.`); } const rows = ((await usedRange.json()) as { values: unknown[][] }).values; const keyIndex = rows[0].indexOf("PersonKey"); const matches = rows .slice(1) .map((row, index) => ({ row, rowNumber: index + 2 })) .filter(({ row }) => String(row[keyIndex]) === businessKey); if (matches.length !== 1) { throw new UserFacingError("Expected exactly one row with that PersonKey."); } const replacement = [...matches[0].row]; replacement.splice(1, 3, name, role, city); const address = encodeURIComponent( `A${matches[0].rowNumber}:E${matches[0].rowNumber}`, ); const updated = await sourceFetch( `${worksheetUrl}/range(address='${address}')`, { method: "PATCH", headers: { ...authorization, "Content-Type": "application/json", }, body: JSON.stringify({ values: [replacement] }), }, ); if (!updated.ok) { throw new UserFacingError(`Microsoft Graph returned HTTP ${updated.status}.`); } return `Updated row for ${businessKey}.`; }

Delete a row

The range delete endpoint ↗ removes the matched cells and shifts subsequent rows up.

DELETE permanently removes a row

The example deletes the row that matches one PersonKey. It does not recover an ambiguous delete. Use a non-production workbook, and inspect the workbook before retrying after a timeout or server error.

Copied!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 from urllib.parse import quote import requests from functions.api import UserFacingError, function from functions.sources import get_source SOURCE_RID = "ri.magritte..source.REPLACE_WITH_YOUR_SOURCE_RID" DRIVE_ID = "REPLACE_WITH_YOUR_DRIVE_ID" WORKBOOK_ITEM_ID = "REPLACE_WITH_YOUR_WORKBOOK_ITEM_ID" WORKSHEET = "People" GRAPH_BASE_URL = "https://graph.microsoft.com/v1.0" def _delegated_session() -> requests.Session: source = get_source(rid=SOURCE_RID) session = requests.Session() access_token = source.get_session_credentials().get().access_token session.headers["Authorization"] = f"Bearer {access_token}" proxy_uri = source.get_https_proxy_uri() if proxy_uri: session.proxies.update({"http": proxy_uri, "https": proxy_uri}) return session @function(api_name="deleteExcelOnlineRow", sources=[SOURCE_RID]) def delete_excel_online_row( business_key: str, ) -> str: workbook_url = ( f"{GRAPH_BASE_URL}/drives/{quote(DRIVE_ID, safe='')}" f"/items/{quote(WORKBOOK_ITEM_ID, safe='')}/workbook" ) worksheet_url = f"{workbook_url}/worksheets/{quote(WORKSHEET, safe='')}" with _delegated_session() as session: used_range = session.get( f"{worksheet_url}/usedRange(valuesOnly=true)", timeout=60, ) used_range.raise_for_status() rows = used_range.json()["values"] key_index = rows[0].index("PersonKey") row_numbers = [ number for number, row in enumerate(rows[1:], start=2) if len(row) > key_index and str(row[key_index]) == business_key ] if len(row_numbers) != 1: raise UserFacingError("Expected exactly one row with that PersonKey.") address = quote(f"A{row_numbers[0]}:E{row_numbers[0]}", safe="") deleted = session.post( f"{worksheet_url}/range(address='{address}')/delete", json={"shift": "Up"}, timeout=60, ) deleted.raise_for_status() return f"Deleted row for {business_key}."
Copied!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 import { UserFacingError } from "@osdk/functions"; import { getFetch, getSource } from "@palantir/functions-sources"; const SOURCE_RID = "ri.magritte..source.REPLACE_WITH_YOUR_SOURCE_RID"; const SOURCE_ALIAS = "MicrosoftGraphExcelDelegated"; const DRIVE_ID = "REPLACE_WITH_YOUR_DRIVE_ID"; const WORKBOOK_ITEM_ID = "REPLACE_WITH_YOUR_WORKBOOK_ITEM_ID"; const WORKSHEET = "People"; const GRAPH_BASE_URL = "https://graph.microsoft.com/v1.0"; export const config = { apiName: "deleteExcelOnlineRow", sources: [SOURCE_ALIAS], }; export default async function deleteExcelOnlineRow( businessKey: string, ): Promise<string> { const source = await getSource({ rid: SOURCE_RID }); const { accessToken } = (await source.sessionCredentials?.get()) as { accessToken: string; }; const sourceFetch = (await getFetch(source)) as typeof fetch; const workbookUrl = `${GRAPH_BASE_URL}/drives/${encodeURIComponent(DRIVE_ID)}` + `/items/${encodeURIComponent(WORKBOOK_ITEM_ID)}/workbook`; const worksheetUrl = `${workbookUrl}/worksheets/${encodeURIComponent(WORKSHEET)}`; const authorization = { Authorization: `Bearer ${accessToken}` }; const usedRange = await sourceFetch( `${worksheetUrl}/usedRange(valuesOnly=true)`, { headers: authorization }, ); if (!usedRange.ok) { throw new UserFacingError(`Microsoft Graph returned HTTP ${usedRange.status}.`); } const rows = ((await usedRange.json()) as { values: unknown[][] }).values; const keyIndex = rows[0].indexOf("PersonKey"); const rowNumbers = rows .slice(1) .map((row, index) => ({ row, rowNumber: index + 2 })) .filter(({ row }) => String(row[keyIndex]) === businessKey) .map(({ rowNumber }) => rowNumber); if (rowNumbers.length !== 1) { throw new UserFacingError("Expected exactly one row with that PersonKey."); } const address = encodeURIComponent( `A${rowNumbers[0]}:E${rowNumbers[0]}`, ); const deleted = await sourceFetch( `${worksheetUrl}/range(address='${address}')/delete`, { method: "POST", headers: { ...authorization, "Content-Type": "application/json", }, body: JSON.stringify({ shift: "Up" }), }, ); if (!deleted.ok) { throw new UserFacingError(`Microsoft Graph returned HTTP ${deleted.status}.`); } return `Deleted row for ${businessKey}.`; }