Office Scripts Advanced: Tables, Charts and Automation

Office Scripts Advanced: Tables, Charts and Automation featured illustration

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

⏱ 3 min readUpdated 11 November 2026

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

Advertisement
In this article
  1. Working with Excel Tables (ListObjects)
  2. Creating Charts Programmatically
  3. Multi-Sheet Automation and Error Handling
  4. Continue learning

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.

Advertisement
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.

✨ 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