Microsoft Graph Excel API Skill

Overview

This skill documents elegant patterns for working with Excel files in SharePoint using the Microsoft Graph API with ky HTTP client. It covers session management, reading/writing data, applying cell formatting (colors, fonts, alignment), handling file locks, and performance optimization for batch operations.

Key Topics:

• Session management with persistent changes
• Reading and writing cell values
• Applying cell formatting (fill colors, fonts, alignment, borders)
• Performance optimization for bulk operations
• Error handling and file lock detection

Setup

Required Packages

{
  "dependencies": {
    "@azure/identity": "^4.13.0",
    "ky": "^1.14.3"
  }
}

Create MS Graph Client with ky

import { ClientSecretCredential } from "@azure/identity";
import ky from "ky";

/**
 * Credencial de Azure AD para autenticación
 */
export const credential = new ClientSecretCredential(
  Bun.env.AZURE_TENANT_ID!,
  Bun.env.AZURE_CLIENT_ID!,
  Bun.env.AZURE_CLIENT_SECRET!
);

/**
 * Crea un cliente HTTP (ky) autenticado para Microsoft Graph REST API
 * Útil para operaciones de Excel que no están en el SDK
 */
export async function createGraphHttpClient() {
  const token = await credential.getToken("https://graph.microsoft.com/.default");

  return ky.create({
    prefixUrl: "https://graph.microsoft.com/v1.0",
    headers: { Authorization: `Bearer ${token.token}` },
  });
}

Why ky instead of SDK?

• Excel workbook sessions API is not available in the Kiota-based SDK
• REST API provides full access to Excel formatting endpoints
• ky offers cleaner syntax than fetch with better error handling

Note: Use Bun.env instead of process.env when working with Bun runtime.

Type Definitions

Before working with Excel data, define proper types to avoid using unknown:

// src/types/excel-data.ts

/**
 * Valor de una celda de Excel
 * Puede ser: string, number, boolean, o null (celda vacía)
 */
export type ExcelCellValue = string | number | boolean | null;

/**
 * Fila de Excel (array de valores de celdas)
 */
export type ExcelRow = ExcelCellValue[];

/**
 * Rango de Excel (array bidimensional de valores)
 * Representa múltiples filas y columnas
 */
export type ExcelRange = ExcelRow[];

/**
 * Respuesta de createSession de Microsoft Graph
 */
export interface ExcelSessionResponse {
  id: string;
  persistChanges: boolean;
}

// src/types/excel-format.ts

export interface ExcelColor {
  argb: string; // Format: "#RRGGBB"
}

export interface ExcelFill {
  type: "pattern";
  pattern: "solid" | "gray50" | "gray75" | "gray25";
  fgColor: ExcelColor;
  bgColor?: ExcelColor;
}

export interface ExcelFont {
  name?: string;
  size?: number;
  bold?: boolean;
  italic?: boolean;
  underline?: "none" | "single" | "double";
  color?: ExcelColor;
}

export interface ExcelAlignment {
  horizontal?: "general" | "left" | "center" | "right" | "fill" | "justify";
  vertical?: "top" | "center" | "bottom" | "justify" | "distributed";
  wrapText?: boolean;
}

export interface ExcelBorderStyle {
  style: "none" | "continuous" | "thin" | "medium" | "thick";
  color: ExcelColor;
}

export interface ExcelBorder {
  top?: ExcelBorderStyle;
  bottom?: ExcelBorderStyle;
  left?: ExcelBorderStyle;
  right?: ExcelBorderStyle;
}

export interface ExcelCellFormat {
  fill?: ExcelFill;
  font?: ExcelFont;
  alignment?: ExcelAlignment;
  border?: ExcelBorder;
}

export interface ExcelErrorResponse {
  error: {
    code: string;
    message: string;
  };
}

Why define these types?

• ✅ Type safety: Catch errors at compile time
• ✅ IntelliSense: Better autocomplete in your IDE
• ✅ Documentation: Types serve as inline documentation
• ✅ Refactoring: Easier to refactor with confidence
• ❌ Avoid unknown[][]: Loses all type information

Key Concepts

Excel Sessions (Optional but Recommended)

Important: Sessions are NOT required for Excel API to work, but are highly recommended for performance.

Excel APIs can be called in three modes:

1. Persistent session (Recommended): All changes are persisted (saved). Most efficient way to use Excel API.
2. Non-persistent session: Changes not saved to source. Useful for analysis/calculations without affecting document.
3. Sessionless: No session ID passed. Excel servers locate workbook for each operation. Not efficient but suitable for isolated requests.

When to use sessions:

• Making more than one or two API calls
• Need best performance
• Want to batch multiple operations

When sessionless is acceptable:

• Single isolated request
• One-time read operation
• Quick data retrieval

Note from Microsoft docs: "If you don't use a session header, changes made during the API call are persisted to the file."

Session Management Pattern

import ky, { HTTPError } from "ky";
import { createGraphHttpClient } from "../api/msgraph";

export interface ExcelSession {
  id: string;
  driveId: string;
  itemId: string;
  client: typeof ky; // ky instance with session header
}

export async function createExcelSession(
  driveId: string,
  itemId: string
): Promise<ExcelSession> {
  const client = await createGraphHttpClient();

  try {
    const result = await client
      .post(`drives/${driveId}/items/${itemId}/workbook/createSession`, {
        json: { persistChanges: true },
      })
      .json<{ id: string }>();

    return {
      id: result.id,
      driveId,
      itemId,
      client: client.extend({
        headers: { "workbook-session-id": result.id },
      }),
    };
  } catch (error) {
    // Handle 409 (file locked) specially
    if (error instanceof HTTPError && error.response.status === 409) {
      const errorData = await error.response.json();
      const userName = errorData?.error?.message?.match(/(.+?) is editing/)?.[1] || "Otro usuario";
      throw new Error(`LOCKED:${userName} está editando el archivo.`);
    }
    throw error;
  }
}

Key Points:

• Use client.extend() to add session header to all subsequent requests
• Handle 409 errors to detect file locks
• Extract username from error message for better UX

Closing Sessions

export async function closeExcelSession(session: ExcelSession): Promise<void> {
  try {
    await session.client.post(
      `drives/${session.driveId}/items/${session.itemId}/workbook/closeSession`
    );
  } catch {
    // Ignore errors when closing session - sessions auto-expire
    console.log("ℹ️  Sesión cerrada");
  }
}

Key Points:

• Always close sessions in a finally block
• Ignore errors on close - sessions auto-expire anyway
• Sessions timeout after ~7 minutes of inactivity

Type Definitions

Excel Cell Value Types

/**
 * Valor de una celda de Excel
 * Puede ser: string, number, boolean, o null (celda vacía)
 */
export type ExcelCellValue = string | number | boolean | null;

/**
 * Fila de Excel (array de valores de celdas)
 */
export type ExcelRow = ExcelCellValue[];

/**
 * Rango de Excel (array bidimensional de valores)
 * Representa múltiples filas y columnas
 */
export type ExcelRange = ExcelRow[];

Why these types?

• ExcelCellValue: Represents all possible cell values in Excel
• ExcelRow: A single row of cells
• ExcelRange: Multiple rows (2D array)
• Avoids using unknown[][] which loses type safety

Reading Data

Read Specific Range (Sessionless)

import type { ExcelRange } from "../types/excel-data";

// Simple read without session - suitable for isolated requests
async function readExcelRangeSessionless(
  driveId: string,
  itemId: string,
  worksheetName: string,
  rangeAddress: string
): Promise<ExcelRange> {
  const client = await createGraphHttpClient();
  
  const result = await client
    .get(`drives/${driveId}/items/${itemId}/workbook/worksheets/${worksheetName}/range(address='${rangeAddress}')`)
    .json<{ values: ExcelRange }>();

  return result.values || [];
}

Read Specific Range (With Session)

import type { ExcelRange } from "../types/excel-data";

// With session - better for multiple operations
async function readExcelRange(
  session: ExcelSession,
  worksheetName: string,
  rangeAddress: string
): Promise<ExcelRange> {
  const result = await session.client
    .get(`drives/${session.driveId}/items/${session.itemId}/workbook/worksheets/${worksheetName}/range(address='${rangeAddress}')`)
    .json<{ values: ExcelRange }>();

  return result.values || [];
}

Read Used Range (All Data)

import type { ExcelRange } from "../types/excel-data";

export async function readUsedRange(
  session: ExcelSession,
  worksheetName: string
): Promise<ExcelRange> {
  const result = await session.client
    .get(`drives/${session.driveId}/items/${session.itemId}/workbook/worksheets/${worksheetName}/usedRange`)
    .json<{ values: ExcelRange }>();
  
  return result.values || [];
}

Key Points:

• Returns all data in the worksheet (used range only)
• Useful for reading existing data before updates
• Returns empty array if worksheet is empty
• Type-safe with ExcelRange instead of unknown[][]

Writing Data

Update Range with Values Only (Sessionless)

import type { ExcelRange } from "../types/excel-data";

// Simple update without session - changes are persisted automatically
async function updateExcelRangeSessionless(
  driveId: string,
  itemId: string,
  worksheetName: string,
  rangeAddress: string,
  values: ExcelRange,
  numberFormat?: string[][]
): Promise<void> {
  const client = await createGraphHttpClient();
  
  const body: { values: ExcelRange; numberFormat?: string[][] } = { values };
  if (numberFormat) {
    body.numberFormat = numberFormat;
  }

  await client.patch(
    `drives/${driveId}/items/${itemId}/workbook/worksheets/${worksheetName}/range(address='${rangeAddress}')`,
    { json: body }
  );
}

Update Range with Values and Formatting (With Session)

import type { ExcelRange } from "../types/excel-data";
import type { ExcelCellFormat } from "../types/excel-format";

export async function updateExcelRange(
  session: ExcelSession,
  worksheetName: string,
  rangeAddress: string,
  values: ExcelRange,
  options?: {
    numberFormat?: string[][];
    format?: ExcelCellFormat;
  }
): Promise<void> {
  const baseUrl = `drives/${session.driveId}/items/${session.itemId}/workbook/worksheets/${worksheetName}/range(address='${rangeAddress}')`;

  // 1. Update values and numberFormat (can be in same request)
  if (values.length > 0 && values[0] && values[0].length > 0) {
    const valuesBody: { values: ExcelRange; numberFormat?: string[][] } = { values };
    if (options?.numberFormat) {
      valuesBody.numberFormat = options.numberFormat;
    }
    await session.client.patch(baseUrl, { json: valuesBody });
  }

  // 2. Apply formatting (requires separate requests to /format endpoints)
  if (options?.format) {
    // 2a. Update alignment and wrapText
    if (options.format.alignment) {
      await session.client.patch(`${baseUrl}/format`, {
        json: {
          horizontalAlignment: options.format.alignment.horizontal,
          verticalAlignment: options.format.alignment.vertical,
          wrapText: options.format.alignment.wrapText,
        },
      });
    }

    // 2b. Update fill color
    if (options.format.fill) {
      await session.client.patch(`${baseUrl}/format/fill`, {
        json: { color: options.format.fill.fgColor.argb }, // Must be "#RRGGBB" format
      });
    }

    // 2c. Update font
    if (options.format.font) {
      const fontPayload: Record<string, string | number | boolean> = {};
      if (options.format.font.name) fontPayload.name = options.format.font.name;
      if (options.format.font.size) fontPayload.size = options.format.font.size;
      if (options.format.font.bold !== undefined) fontPayload.bold = options.format.font.bold;
      if (options.format.font.color?.argb) fontPayload.color = options.format.font.color.argb;

      await session.client.patch(`${baseUrl}/format/font`, { json: fontPayload });
    }

    // 2d. Update borders (optional - can be unreliable on large ranges)
    if (options.format.border) {
      const borderUpdates = [];
      if (options.format.border.top) {
        borderUpdates.push(
          session.client.patch(`${baseUrl}/format/borders/EdgeTop`, {
            json: {
              style: options.format.border.top.style,
              color: options.format.border.top.color.argb,
            },
          })
        );
      }
      if (options.format.border.bottom) {
        borderUpdates.push(
          session.client.patch(`${baseUrl}/format/borders/EdgeBottom`, {
            json: {
              style: options.format.border.bottom.style,
              color: options.format.border.bottom.color.argb,
            },
          })
        );
      }
      if (options.format.border.left) {
        borderUpdates.push(
          session.client.patch(`${baseUrl}/format/borders/EdgeLeft`, {
            json: {
              style: options.format.border.left.style,
              color: options.format.border.left.color.argb,
            },
          })
        );
      }
      if (options.format.border.right) {
        borderUpdates.push(
          session.client.patch(`${baseUrl}/format/borders/EdgeRight`, {
            json: {
              style: options.format.border.right.style,
              color: options.format.border.right.color.argb,
            },
          })
        );
      }
      // Execute all borders in parallel
      await Promise.all(borderUpdates);
    }
  }
}

Key Points:

• Values and numberFormat can be sent in same request
• Formatting requires separate requests to /format endpoints
• Pass empty array [[]] to apply format without changing values
• Border formatting can be unreliable on large ranges
• Use ExcelRange type instead of unknown[][] for type safety

Color Format Requirements

CRITICAL: Microsoft Graph Excel API requires specific color formats:

• Fill colors: "#RRGGBB" format (e.g., "#1E3A6E")
• Font colors: "#RRGGBB" format (e.g., "#FFFFFF")
• Border colors: "#RRGGBB" format (e.g., "#CCCCCC")

DO NOT USE:

• ❌ ARGB format: "FF1E3A6E" (will cause 400 Bad Request)
• ❌ Without hash: "1E3A6E" (may work but not recommended)
• ❌ Named colors: "blue" (not supported in all endpoints)

Example:

// ✅ Correct
const colors = {
  PRIMARY_BLUE: "#1E3A6E",
  WHITE: "#FFFFFF",
  LIGHT_BLUE: "#E8EEF5",
};

// ❌ Wrong - will fail with 400 Bad Request
const colors = {
  PRIMARY_BLUE: "FF1E3A6E", // ARGB format not accepted
  WHITE: "FFFFFFFF",
};

Common Number Formats

• "General" - Default format
• "0.00" - Number with 2 decimals
• "d/m/yyyy" - Date without leading zeros
• "@" - Text format (preserves leading zeros)

Performance Optimization

Problem: Slow Performance with Row-by-Row Updates

When writing many rows with formatting, doing it row-by-row is VERY slow because each row requires multiple HTTP requests:

• 1 request for values
• 1 request for alignment
• 1 request for fill color
• 1 request for font

Example of SLOW approach (100 rows = ~400 requests):

// ❌ SLOW - Don't do this
for (let i = 0; i < 100; i++) {
  const row = data[i];
  const rangeAddress = `A${i + 2}:Z${i + 2}`;
  
  // This makes 4 HTTP requests per row!
  await updateExcelRangeWithFormat(session, worksheetName, rangeAddress, [row], {
    format: {
      fill: { color: i % 2 === 0 ? "#E8EEF5" : "#FFFFFF" },
      font: { name: "Calibri", size: 11, color: "#000000" },
      alignment: { horizontal: "left", vertical: "center" },
    },
  });
}
// Total: 100 rows × 4 requests = 400 HTTP requests 😱

Solution: Batch Write All Data, Then Apply Formatting

Optimized approach (100 rows = ~60 requests):

// ✅ FAST - Do this instead
const allRows = data.map(item => convertToExcelRow(item));
const lastRow = allRows.length + 1;
const dataRange = `A2:Z${lastRow}`;

// 1. Write ALL data at once (1 request)
console.log("📝 Escribiendo datos...");
await updateExcelRangeWithFormat(session, worksheetName, dataRange, allRows);

// 2. Apply base formatting to entire range (2 requests: font+alignment)
console.log("🎨 Aplicando formato base...");
await updateExcelRangeWithFormat(session, worksheetName, dataRange, [[]], {
  format: {
    font: { name: "Calibri", size: 11, color: "#000000" },
    alignment: { horizontal: "left", vertical: "center" },
  },
});

// 3. Apply alternating colors in batches (50 requests for 100 rows)
console.log("🎨 Aplicando colores alternados...");
const evenRowRanges = [];
for (let i = 0; i < allRows.length; i++) {
  const targetRow = i + 2;
  if (targetRow % 2 === 0) {
    evenRowRanges.push(`A${targetRow}:Z${targetRow}`);
  }
}

// Apply colors in parallel batches of 10
const batchSize = 10;
for (let i = 0; i < evenRowRanges.length; i += batchSize) {
  const batch = evenRowRanges.slice(i, i + batchSize);
  await Promise.all(
    batch.map((range) =>
      updateExcelRangeWithFormat(session, worksheetName, range, [[]], {
        format: { fill: { color: "#E8EEF5" } },
      })
    )
  );
}

console.log("✅ Formato aplicado");
// Total: 1 + 2 + ~50 = ~60 HTTP requests ⚡

Performance Comparison

Approach	Requests for 100 rows	Time
Row-by-row with format	~400 requests	Very slow 🐌
Batch write + batch format	~60 requests	Fast ⚡

Key Optimization Principles:

1. Write all values in one request - Use a large range like A2:Z101
2. Apply base formatting to entire range - Font and alignment for all rows at once
3. Apply variable formatting in batches - Alternating colors in parallel batches
4. Use parallel requests for independent operations - Promise.all() for same-level formatting
5. Pass empty array [[]] when only applying format - No need to resend values

Batch Size Recommendations

• Values: Write all at once (no limit needed for 100-1000 rows)
• Formatting batches: 10-20 ranges per batch (balance between speed and API limits)
• Parallel requests: Max 10 concurrent requests to avoid rate limiting

Cell Formatting Examples

Example 1: Header Row with Corporate Colors

const headerRange = "A1:Z1";
const headerValues = [["Name", "Date", "Amount", /* ... */]];

await updateExcelRangeWithFormat(session, worksheetName, headerRange, headerValues, {
  format: {
    fill: { color: "#1E3A6E" }, // Dark blue background
    font: { name: "Calibri", size: 11, bold: true, color: "#FFFFFF" }, // White text
    alignment: { horizontal: "center", vertical: "center", wrapText: true },
  },
});

Example 2: Alternating Row Colors

// Even rows - light blue
const evenRowRange = "A2:Z2";
await updateExcelRangeWithFormat(session, worksheetName, evenRowRange, [[]], {
  format: {
    fill: { color: "#E8EEF5" }, // Light blue
    font: { name: "Calibri", size: 11, color: "#000000" },
    alignment: { horizontal: "left", vertical: "center" },
  },
});

// Odd rows - white (default, no fill needed)
const oddRowRange = "A3:Z3";
await updateExcelRangeWithFormat(session, worksheetName, oddRowRange, [[]], {
  format: {
    font: { name: "Calibri", size: 11, color: "#000000" },
    alignment: { horizontal: "left", vertical: "center" },
  },
});

Example 3: Applying Format Without Changing Values

// Apply formatting to existing data without overwriting values
// Pass empty array [[]] to skip value update
await updateExcelRangeWithFormat(session, worksheetName, "A1:Z100", [[]], {
  format: {
    font: { name: "Arial", size: 10 },
    alignment: { horizontal: "left", vertical: "top" },
  },
});

Border Formatting (Advanced)

WARNING: Border formatting on large ranges can be unreliable with Microsoft Graph API. The /format/borders/{sideIndex} endpoint sometimes returns 400 Bad Request errors for ranges with many columns.

Recommendation: Avoid borders on large ranges, or apply them manually in Excel after data is written.

If you must use borders programmatically:

// Apply border to a single cell or small range
const borderRange = "A1:C1";
await session.client.patch(
  `drives/${session.driveId}/items/${session.itemId}/workbook/worksheets/${worksheetName}/range(address='${borderRange}')/format/borders/EdgeTop`,
  {
    json: {
      style: "thin",
      color: "#2C5282", // Hex format
    },
  }
);

Border endpoints:

• /format/borders/EdgeTop
• /format/borders/EdgeBottom
• /format/borders/EdgeLeft
• /format/borders/EdgeRight

Preserving Formulas

When updating data, skip columns that contain formulas to avoid overwriting them:

// Insert columns A, B, C
const rangeABC = `A${rowNum}:C${rowNum}`;
const valuesABC = [[value1, value2, value3]];
const formatABC = [["d/m/yyyy", "General", "0.00"]];
await updateExcelRangeWithFormat(session, worksheetName, rangeABC, valuesABC, {
  numberFormat: formatABC,
});

// Skip columns D and E (they have formulas)

// Insert columns F, G, H
const rangeFGH = `F${rowNum}:H${rowNum}`;
const valuesFGH = [[value6, value7, value8]];
const formatFGH = [["@", "General", "General"]];
await updateExcelRangeWithFormat(session, worksheetName, rangeFGH, valuesFGH, {
  numberFormat: formatFGH,
});

Error Handling

File Lock Detection (409 Errors)

if (response.status === 409) {
  const errorData = await response.json();
  const userName = errorData?.error?.message?.match(/(.+?) is editing this workbook/)?.[1] || "Otro usuario";
  throw new Error(`LOCKED:${userName} está editando el archivo. Cierra el archivo e intenta nuevamente.`);
}

Handling LOCKED Errors

try {
  const session = await createExcelSession(driveId, itemId);
  // ... work with session
} catch (error) {
  if (error instanceof Error && error.message.startsWith("LOCKED:")) {
    const cleanMessage = error.message.substring(7);
    console.log(`⚠️  Archivo bloqueado: ${cleanMessage}`);
    // Handle gracefully - maybe skip or retry later
  } else {
    throw error;
  }
}

Best Practices

1. Use ky for cleaner HTTP requests - Simpler than fetch with better error handling
2. Create MS Graph client once - Reuse the configured ky instance
3. Use sessions for multiple operations - If making more than 1-2 calls, create a session
4. Sessionless is OK for isolated requests - Single reads/writes don't need sessions
5. Sessions with persistChanges: true - Changes are auto-saved
6. Extend ky client with session ID - Use client.extend() to add session header
7. Handle 409 errors gracefully - Extract user name from error message using ky.HTTPError
8. Close sessions when done - Use try/finally to ensure cleanup
9. Preserve formulas - Update non-formula columns separately
10. Use appropriate number formats - Dates, numbers, and text need different formats
11. Use text format for IDs - "@" format preserves leading zeros
12. Batch write for performance - Write all data at once, then apply formatting
13. Use hex color format - Always use "#RRGGBB" format, never ARGB
14. Parallel formatting in batches - Use Promise.all() with batch size of 10-20
15. Pass empty array for format-only updates - Use [[]] when not changing values

Common Pitfalls

1. Using sessions for single operations - Sessionless is fine for 1-2 isolated calls
2. Not using sessions for batch operations - Multiple calls should use sessions for performance
3. Row-by-row updates with formatting - Always batch write data first, then apply formatting
4. Wrong color format - Use "#RRGGBB" not "FFRRGGBB" (ARGB)
5. Overwriting formulas - Always skip columns with formulas when updating
6. Wrong number format - Use "@" for text, "d/m/yyyy" for dates, "0.00" for numbers
7. Not handling file locks - Check for ky.HTTPError with status 409
8. Forgetting to close sessions - Sessions auto-expire but explicit closing is cleaner
9. Not using ky error handling - Use try/catch with ky.HTTPError for better error messages
10. Applying borders to large ranges - Borders are unreliable on ranges with many columns

API Endpoints Reference

• Create Session: POST /drives/{driveId}/items/{itemId}/workbook/createSession
• Close Session: POST /drives/{driveId}/items/{itemId}/workbook/closeSession
• Read Range: GET /drives/{driveId}/items/{itemId}/workbook/worksheets/{name}/range(address='{range}')
• Read Used Range: GET /drives/{driveId}/items/{itemId}/workbook/worksheets/{name}/usedRange
• Update Range Values: PATCH /drives/{driveId}/items/{itemId}/workbook/worksheets/{name}/range(address='{range}')
• Update Range Format: PATCH /drives/{driveId}/items/{itemId}/workbook/worksheets/{name}/range(address='{range}')/format
• Update Fill Color: PATCH /drives/{driveId}/items/{itemId}/workbook/worksheets/{name}/range(address='{range}')/format/fill
• Update Font: PATCH /drives/{driveId}/items/{itemId}/workbook/worksheets/{name}/range(address='{range}')/format/font
• Update Border: PATCH /drives/{driveId}/items/{itemId}/workbook/worksheets/{name}/range(address='{range}')/format/borders/{sideIndex}

When to Use This Skill

Use this skill when you need to:

• Work with Excel files in SharePoint/OneDrive using ky HTTP client
• Read or write data to Excel workbooks online with cleaner syntax
• Apply cell formatting (colors, fonts, alignment) programmatically
• Optimize performance for bulk data operations
• Handle concurrent editing scenarios with proper error handling
• Preserve formulas while updating data
• Implement corporate color schemes and styling
• Simplify Microsoft Graph API calls with ky's elegant interface