
π This article includes 1 downloadable practice file β
In this article
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?
| Feature | Office Scripts | VBA |
|---|---|---|
| Language | TypeScript (typed JavaScript) | VBA (Visual Basic) |
| Platform | Excel Online + Desktop (365) | Desktop Excel only |
| Power Automate | Native integration | Not supported |
| Sharing | Cloud (OneDrive/SharePoint) | Embedded in .xlsm file |
| Action Recorder | Yes (Automate tab) | Yes (Developer tab) |
| Learning Curve | Moderate (need TypeScript basics) | Easy for beginners |
| Speed | Slower (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.
// 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.
Stuck on a step? Ask a question and the AI answers using this article.