
π This article includes 1 downloadable practice file β
In this article
Once you are comfortable with Office Scripts basics, the next level is mastering Excel Tables (ListObjects), creating charts programmatically, handling multiple worksheets, and building error-resilient scripts that work reliably in automated Power Automate flows.
Working with Excel Tables (ListObjects)
Excel Tables are the most powerful data structure in Excel. Office Scripts has excellent Table support β you can read, filter, sort, and add rows without worrying about exact cell addresses.
function main(workbook: ExcelScript.Workbook) {
// Get a table by name (Ctrl+T creates a table; name it in the Design tab)
let table = workbook.getTable("SalesData");
if (!table) {
console.log("Table 'SalesData' not found!");
return;
}
console.log("Table has " + table.getRowCount() + " rows");
// Get all data between header and total row
let data = table.getRangeBetweenHeaderAndTotal().getValues();
// Add a new row at the end (index -1)
table.addRow(-1, ["2024-11-01", "Priya Sharma", 85000, "North", "Completed"]);
// Sort by sales column (index 2) descending
table.getSort().apply([{ key: 2, ascending: false }]);
// Filter to show only "Completed" status (column index 4)
table.getAutoFilter().apply(table.getRange(), 4, {
criterion1: "Completed",
filterOn: ExcelScript.FilterOn.values
});
console.log("Table sorted and filtered!");
}
Creating Charts Programmatically
function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
// Remove any existing charts
for (let chart of sheet.getCharts()) {
chart.delete();
}
// Create clustered column chart from data range
let dataRange = sheet.getRange("A1:B13"); // Month | Revenue
let chart = sheet.addChart(ExcelScript.ChartType.columnClustered, dataRange);
// Position and size the chart
chart.setName("Monthly Revenue 2024");
chart.setTop(20);
chart.setLeft(350);
chart.setWidth(550);
chart.setHeight(320);
// Set chart title
let title = chart.getTitle();
title.setText("Monthly Revenue - FY 2024");
title.setVisible(true);
// Format the data series
let series = chart.getSeries()[0];
series.setName("Revenue");
// Show data labels
let labels = series.getDataLabels();
labels.setVisible(true);
labels.setNumberFormat("#,##0");
labels.setPosition(ExcelScript.ChartDataLabelPosition.outsideEnd);
// Style the chart area
chart.getChartArea().getFormat().getFill().setColor("#FAFAFA");
chart.getPlotArea().getFormat().getFill().setColor("#FFFFFF");
console.log("Chart created: " + chart.getName());
}
Multi-Sheet Automation and Error Handling
function main(workbook: ExcelScript.Workbook): ProcessResult {
const errors: string[] = [];
const log: string[] = [];
try {
// Validate required sheets exist
const required = ["RawData", "Summary", "Dashboard"];
for (let sheetName of required) {
if (!workbook.getWorksheet(sheetName)) {
errors.push("Missing sheet: " + sheetName);
}
}
if (errors.length > 0) {
return { success: false, errors, log, rowsProcessed: 0 };
}
let rawSheet = workbook.getWorksheet("RawData");
let summarySheet = workbook.getWorksheet("Summary");
// Get data from raw sheet
let rawData = rawSheet.getUsedRange().getValues();
log.push("Loaded " + (rawData.length - 1) + " records from RawData");
// Aggregate by category (column 1)
let totals: { [key: string]: number } = {};
for (let row = 1; row [cat, totals[cat]]);
summarySheet.getRange("A2").getResizedRange(summaryData.length - 1, 1)
.setValues(summaryData);
log.push("Summary written: " + categories.length + " categories");
return {
success: true,
errors: [],
log,
rowsProcessed: rawData.length - 1
};
} catch (e) {
errors.push("Runtime error: " + e);
return { success: false, errors, log, rowsProcessed: 0 };
}
}
interface ProcessResult {
success: boolean;
errors: string[];
log: string[];
rowsProcessed: number;
}
Best Practices for Production Scripts:
- Always validate that required sheets and tables exist before processing
- Clear the target range before rewriting to avoid stale data:
usedRange?.clear() - Use TypeScript interfaces for all return types used by Power Automate
- Log progress via
console.log()β visible in the Output panel during development - Test scripts on a copy of the workbook before deploying to production
π Practice files for this article
- πLesson 4 Office ScriptPaste into Excel on the web > Automate > New Script.β¬ TS Β· 934 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.