Office Scripts Basics: Automate Excel in the Browser

Office Scripts Basics: Automate Excel in the Browser featured illustration

πŸ“Ž This article includes 1 downloadable practice file ↓

⏱ 3 min readUpdated 11 November 2026

πŸ“˜ Office Scripts Beginner Course Β· Lesson 3 of 5

Advertisement
In this article
  1. Office Scripts vs VBA: Which Should You Use?
  2. Basic Level: Your First Office Script
  3. Reading Data from a Worksheet
  4. Intermediate Level: Conditional Formatting and Data Manipulation
  5. Expert Level: Power Automate Integration
  6. Continue learning

Office Scripts is Microsoft’s modern automation language for Excel on the web (Microsoft 365). Unlike VBA which only works on the desktop, Office Scripts runs entirely in the browser and integrates natively with Power Automate β€” making it the future of Excel automation for cloud-first teams.

Office Scripts vs VBA: Which Should You Use?

FeatureOffice ScriptsVBA
LanguageTypeScript (typed JavaScript)VBA (Visual Basic)
PlatformExcel Online + Desktop (365)Desktop Excel only
Power AutomateNative integrationNot supported
SharingCloud (OneDrive/SharePoint)Embedded in .xlsm file
Action RecorderYes (Automate tab)Yes (Developer tab)
Learning CurveModerate (need TypeScript basics)Easy for beginners
SpeedSlower (cloud round trips)Faster (runs locally)

Basic Level: Your First Office Script

Open Excel Online (office.com) with a Microsoft 365 Business or Education account. Click the Automate tab, then New Script. Office Scripts uses TypeScript β€” a superset of JavaScript with types.

// Hello World: Write to a cell and format it
function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet();
    
    // Get cell A1 (row 0, column 0)
    let cell = sheet.getCell(0, 0);
    cell.setValue("Hello from Office Scripts!");
    
    // Apply formatting
    cell.getFormat().getFont().setBold(true);
    cell.getFormat().getFont().setSize(14);
    cell.getFormat().getFill().setColor("#FFE57F");  // Yellow background
    
    // Auto-fit column width
    sheet.getRange("A:A").getFormat().autofitColumns();
    
    console.log("Script complete!");  // Check Output panel
}

Reading Data from a Worksheet

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet();
    
    // Read a single cell value
    let value = sheet.getRange("A1").getValue();
    console.log("A1 contains: " + value);
    
    // Read a range as a 2D array
    let range = sheet.getRange("A1:C10");
    let values = range.getValues();
    
    // Loop through rows and columns
    for (let row = 0; row < values.length; row++) {
        for (let col = 0; col < values[row].length; col++) {
            console.log(`Row ${row+1}, Col ${col+1}: ${values[row][col]}`);
        }
    }
    
    // Get the used range (auto-detects data boundaries)
    let usedRange = sheet.getUsedRange();
    console.log("Total rows: " + usedRange.getRowCount());
    console.log("Total cols: " + usedRange.getColumnCount());
}

Intermediate Level: Conditional Formatting and Data Manipulation

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet();
    let data = sheet.getUsedRange().getValues();
    
    // Process each row (skip header row at index 0)
    for (let row = 1; row = 100000) {
            cell.getFormat().getFill().setColor("#C6EFCE");  // Green
            cell.getFormat().getFont().setColor("#276221");
        } else if (sales >= 50000) {
            cell.getFormat().getFill().setColor("#FFEB9C");  // Yellow
            cell.getFormat().getFont().setColor("#9C5700");
        } else {
            cell.getFormat().getFill().setColor("#FFC7CE");  // Red
            cell.getFormat().getFont().setColor("#9C0006");
        }
    }
    
    // Add totals row
    let lastRow = data.length;
    let totalCell = sheet.getCell(lastRow, 2);
    totalCell.setFormula("=SUM(C2:C" + lastRow + ")");
    totalCell.getFormat().getFont().setBold(true);
    sheet.getCell(lastRow, 0).setValue("TOTAL");
    
    console.log("Formatted " + (data.length - 1) + " rows and added totals");
}

Expert Level: Power Automate Integration

Office Scripts can accept parameters from Power Automate flows and return structured data back β€” enabling fully automated, scheduled Excel reporting without anyone opening the file.

Advertisement
// Script called by Power Automate: accepts date range, returns summary
function main(
    workbook: ExcelScript.Workbook,
    startDate: string,
    endDate: string,
    minSales: number
): ReportResult {
    
    let sheet = workbook.getWorksheet("SalesData");
    let data = sheet.getUsedRange().getValues();
    
    let total = 0;
    let count = 0;
    let topPerformers: string[] = [];
    
    for (let row = 1; row = startDate && date = minSales) {
                topPerformers.push(rep + " (" + sales.toLocaleString("en-IN") + ")");
            }
        }
    }
    
    return {
        period: startDate + " to " + endDate,
        totalSales: total,
        transactionCount: count,
        averageSale: count > 0 ? Math.round(total / count) : 0,
        topPerformers: topPerformers
    };
}

// TypeScript interface defines the return structure for Power Automate
interface ReportResult {
    period: string;
    totalSales: number;
    transactionCount: number;
    averageSale: number;
    topPerformers: string[];
}

Getting Started: Use the Action Recorder (Automate tab β†’ Record Actions) to auto-generate Office Scripts code. Perform the actions manually, stop recording, and you get working TypeScript that you can then customize. This is the fastest way to learn.

πŸ“Ž Practice files for this article

  • πŸ“
    Lesson 3 Office ScriptPaste into Excel on the web > Automate > New Script.
    ⬇ TS Β· 710 B

Free to use for learning. Files with macros (.bas) are plain text β€” import them with Alt+F11 β†’ File β†’ Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong

Leave a Comment