Office Scripts

Excel Office Scripts

Office Scripts is Microsoft's TypeScript-based automation platform for Excel on the web. Scripts automate Excel tasks and can be triggered manually from the Automate tab or programmatically via Power Automate flows.

When to Activate

• User asks to write, review, or fix an Office Script
• User mentions Excel automation, .osts files, or ExcelScript
• User wants TypeScript code that runs inside Excel
• User asks about Power Automate + Excel integration
• User needs to understand Office Script restrictions or best practices
• User asks about the Excel Graph REST API for workbook operations
• User wants to read/write Excel data from an external application

Entry Point — The main Function

Every Office Script must have exactly one entry point:

function main(workbook: ExcelScript.Workbook) {
  // Script logic here
}

For scripts that call external APIs with fetch, use async:

async function main(workbook: ExcelScript.Workbook) {
  let response = await fetch("https://api.example.com/data");
  let data: ExternalData[] = await response.json();
}

For Power Automate integration, add parameters after workbook:

function main(workbook: ExcelScript.Workbook, sheetName: string, startRow: number) {
  let sheet = workbook.getWorksheet(sheetName);
}

Object Model Hierarchy

Workbook
├── Worksheet[]
│   ├── Range (cells, rows, columns)
│   ├── Table[]
│   │   ├── TableColumn[]
│   │   ├── TableRow[]
│   │   ├── TableSort
│   │   └── AutoFilter
│   ├── Chart[]
│   │   ├── ChartSeries[]
│   │   ├── ChartAxes
│   │   ├── ChartLegend
│   │   └── ChartTitle
│   ├── PivotTable[]
│   ├── Shape[]
│   ├── Comment[]
│   ├── ConditionalFormat[]
│   └── NamedItem[]
├── NamedItem[] (workbook-scoped)
└── Table[] (workbook-scoped)

Collections Pattern

Plural get methods return arrays (never undefined):

let sheets: ExcelScript.Worksheet[] = workbook.getWorksheets();
let tables: ExcelScript.Table[] = sheet.getTables();

Singular get methods return object or undefined:

let sheet: ExcelScript.Worksheet | undefined = workbook.getWorksheet("Data");
let table: ExcelScript.Table | undefined = sheet?.getTable("SalesTable");

Verify-Before-Use Pattern (Critical)

Always check that objects returned by singular get methods exist before using them:

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getWorksheet("Data");
  if (!sheet) {
    console.log("Worksheet 'Data' not found");
    return;
  }

  let table = sheet.getTable("SalesTable");
  if (!table) {
    console.log("Table 'SalesTable' not found");
    return;
  }

  // Safe to use sheet and table here
  let range = table.getRangeBetweenHeaderAndTotal();
  let values = range.getValues();
}

Excel Graph REST API

Office Scripts work inside Excel, but applications can also interact with workbooks via the Microsoft Graph REST API.

Base URL

https://graph.microsoft.com/v1.0

Workbook Access Paths

Graph API Endpoints

Session Management

Create session body:

{
  "persistChanges": true
}

Use the returned id in subsequent requests as header: workbook-session-id: {sessionId}.

When to use sessions:

• persistChanges: true — Batch multiple writes atomically; changes saved on close
• persistChanges: false — Temporary calculations without saving; changes discarded
• Sessionless — Simple one-off reads/writes; each request is independent

Session timeout: 5 minutes of inactivity. Refresh to extend.

Worksheet Operations

Add worksheet body:

{
  "name": "NewSheet"
}

Range Operations

Update range values body:

{
  "values": [
    ["Name", "Age", "City"],
    ["Alice", 30, "Seattle"],
    ["Bob", 25, "Portland"]
  ]
}

Update range format body:

{
  "format": {
    "font": {
      "bold": true,
      "color": "#FFFFFF",
      "size": 12
    },
    "fill": {
      "color": "#4472C4"
    },
    "horizontalAlignment": "Center",
    "columnWidth": 120
  },
  "numberFormat": [["$#,##0.00"]]
}

Sort range body:

{
  "fields": [
    {
      "key": 0,
      "sortOn": "Value",
      "ascending": true
    }
  ],
  "matchCase": false,
  "hasHeaders": true
}

Table Operations

Create table body:

{
  "address": "Sheet1!A1:D5",
  "hasHeaders": true
}

Add table rows body:

{
  "index": null,
  "values": [
    ["New Item", 100, "2026-03-01", "Active"],
    ["Another Item", 250, "2026-03-15", "Pending"]
  ]
}

Add table column body:

{
  "index": null,
  "name": "Status",
  "values": [
    ["Active"],
    ["Inactive"],
    ["Pending"]
  ]
}

Chart Operations

Create chart body:

{
  "type": "ColumnClustered",
  "sourceData": "Sheet1!A1:B5",
  "seriesBy": "Auto"
}

Chart types: ColumnClustered, ColumnStacked, BarClustered, BarStacked, Line, LineMarkers, Pie, Doughnut, Area, AreaStacked, XYScatter, XYScatterLines, Radar, Surface, Histogram, Waterfall, Treemap, Sunburst, Funnel.

Named Items (Named Ranges)

Workbook Functions

Example — VLOOKUP:

POST /workbook/functions/vlookup
{
  "lookupValue": "Alice",
  "tableArray": {
    "address": "Sheet1!A1:D10"
  },
  "colIndexNum": 3,
  "rangeLookup": false
}

Supports 300+ Excel functions: SUM, AVERAGE, COUNT, VLOOKUP, IF, CONCATENATE, TEXT, DATE, NOW, etc.

TypeScript 4.0.3 Restrictions (Summary)

Office Scripts uses a restricted TypeScript 4.0.3 environment. Key restrictions:

See references/constraints-and-best-practices.md for the complete list with examples.

Key API Patterns (Quick Reference)

Reading and Writing Data

// Single cell
let value = sheet.getRange("A1").getValue();
sheet.getRange("A1").setValue("Hello");

// Range of cells (2D array)
let values = sheet.getRange("A1:C3").getValues();
sheet.getRange("A1:C3").setValues([
  ["Name", "Age", "City"],
  ["Alice", 30, "NYC"],
  ["Bob", 25, "LA"]
]);

// Dynamic sizing — use getUsedRange()
let usedRange = sheet.getUsedRange();
let allData = usedRange.getValues();

Tables

// Create table from range
let table = sheet.addTable(sheet.getRange("A1:C10"), true /* hasHeaders */);
table.setName("SalesData");

// Add row
table.addRow(-1, ["New Item", 100, "2024-01-15"]);

// Filter
table.getColumnByName("Status").getFilter().applyValuesFilter(["Active"]);

// Sort
table.getSort().apply([{ key: 0, ascending: true }]);

Formatting

let range = sheet.getRange("A1:D1");
range.getFormat().getFill().setColor("#4472C4");
range.getFormat().getFont().setBold(true);
range.getFormat().getFont().setColor("white");
range.setNumberFormat("$#,##0.00");

Conditional Formatting

// Add cell value rule
let cf = sheet.getRange("B2:B100").addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue);
cf.getCellValue().setRule({
  formula1: "=0",
  operator: ExcelScript.ConditionalCellValueOperator.greaterThan
});
cf.getCellValue().getFormat().getFont().setColor("#006100");
cf.getCellValue().getFormat().getFill().setColor("#C6EFCE");

PivotTables

// Create pivot table
let pivotTable = sheet.addPivotTable(
  "SalesPivot",
  sourceSheet.getRange("A1:E100"),
  sheet.getRange("A1")
);

// Add fields
pivotTable.addRowHierarchy(pivotTable.getHierarchy("Region"));
pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Quarter"));
pivotTable.addDataHierarchy(pivotTable.getHierarchy("Revenue"));

Power Automate Integration

• Use the Excel Online (Business) connector's "Run script" action
• Parameters: string, number, boolean, objects, arrays (no any, no Range)
• Return values via return statement with typed result
• fetch is NOT available when called from Power Automate
• throw stops the entire flow; return does not
• JSDoc @param comments appear in the Power Automate UI

Connector Limits

See references/power-automate.md for complete integration guide.

Required Permissions

Office Scripts (In-Browser)

Graph API (External Access)

Error Handling

Office Script Errors

Graph API Errors

Session Error Recovery

// If session expires, create a new one and retry
try {
  await graphClient.api(url).header("workbook-session-id", sessionId).patch(body);
} catch (err) {
  if (err.code === "InvalidSessionAccessConflict" || err.code === "SessionNotFound") {
    const newSession = await graphClient.api(`${workbookUrl}/createSession`)
      .post({ persistChanges: true });
    sessionId = newSession.id;
    await graphClient.api(url).header("workbook-session-id", sessionId).patch(body);
  }
}

Performance Best Practices

1. Minimize read/write calls — read all data at once, process in memory, write once
2. Read outside loops — never call getValues() inside a loop
3. Remove console.log in production — logging impacts performance
4. Pause calculation for bulk writes:

   workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.manual);
   // ... bulk operations ...
   workbook.getApplication().setCalculationMode(ExcelScript.CalculationMode.automatic);
   

5. Batch large datasets — process in chunks of ~5,000 rows
6. Avoid modifying table structure in loops — delete and recreate instead
7. Use sessions for Graph API batch operations — reduces round trips
8. Prefer getUsedRange() over fixed ranges — adapts to data size dynamically

See references/constraints-and-best-practices.md for all performance tips.

Common Patterns

Pattern 1: Data Extraction and Report

1. getWorksheet("RawData") → getUsedRange() → getValues() — extract raw data
2. Process in memory: filter rows, aggregate totals, calculate percentages
3. addWorksheet("Report") — create report sheet
4. getRange("A1:...") → setValues(reportData) — write processed data
5. Apply formatting: headers, number formats, conditional formatting
6. Optional: create chart from report data

Pattern 2: Data Validation and Cleanup

1. Read all data with getUsedRange().getValues()
2. Validate each row: check required fields, validate email/phone patterns
3. Build errors[] array with [row, column, message] for each issue
4. Write errors to a "Validation Results" sheet
5. Highlight invalid cells with red fill in the original data
6. Return summary: { totalRows, validRows, errorRows }

Pattern 3: Multi-Sheet Consolidation

1. workbook.getWorksheets() — get all sheets
2. For each sheet: getUsedRange().getValues() — read data (skip header for 2nd+ sheet)
3. Concatenate all rows into single 2D array
4. Write to a "Consolidated" worksheet
5. Convert consolidated range to a Table for sorting/filtering
6. Add PivotTable for summary analysis

Pattern 4: Scheduled Data Refresh (via Power Automate)

1. Power Automate trigger: Recurrence (daily at 8 AM)
2. HTTP action: fetch data from external API
3. "Run script" action: pass API response as parameter
4. Script: parse response, clear old data, write new rows
5. Script: update "Last Updated" cell with timestamp
6. Script: return row count for flow logging

Pattern 5: Template-Based Report Generation

1. Read template sheet with pre-built formatting and formulas
2. worksheet.copy() — duplicate template for each report instance
3. Populate data cells (leave formulas intact)
4. Update named ranges for dynamic chart data sources
5. Protect sheets to prevent accidental formula deletion
6. Return list of generated sheet names

File Storage

• Scripts saved as .osts files in OneDrive: /Documents/Office Scripts/
• SharePoint-based scripts stored in document library's Office Scripts folder
• Scripts can be shared with a workbook or kept personal

Common Gotchas

1. getRange() vs getRangeByIndexes(): getRange("A1:C3") uses A1 notation; getRangeByIndexes(row, col, rowCount, colCount) uses zero-based indices
2. Values are 2D arrays: Even a single cell returns (string | number | boolean)[][]
3. Dates are serial numbers: Excel stores dates as numbers; use getNumberFormatLocal() to distinguish
4. Tables can't be resized: Delete and recreate to change structure
5. No implicit any: Destructuring and untyped function parameters will fail
6. undefined vs null: Singular get methods return undefined, not null
7. Graph API range address format: Must include sheet name in cross-sheet references (Sheet1!A1:D10)
8. Session concurrency: Only one persistent session per workbook at a time

Number Format Reference

Reference Files