3. ExcelScript: Tables and Charts

Scripts work more reliably with Excel tables than with cell addresses: a table grows with its data, and columns are found by name.

• sheet.addTable("A1:D20", true) turns a range into a table (true means it has a header row); table.setName("Sales") names it;
• workbook.getTable("Sales") finds a table by name; sheet.getTables() returns all tables on a sheet;
• table.getColumnByName("Amount").getRangeBetweenHeaderAndTotal() is the column's data without the header and totals;
• table.addRow(-1, ["5/12/2026", "Boston", 540]) adds a row at the end; table.getRowCount() is the number of data rows;
• table.setShowTotals(true) turns on the totals row; table.getSort().apply([{ key: 2, ascending: false }]) sorts by the third column (counted from 0);
• table.getColumnByName("City").getFilter().applyValuesFilter(["Austin"]) filters by values.

A chart is created with the worksheet method addChart(type, data). The types are listed in ExcelScript.ChartType: columnClustered, line, pie, barClustered.

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Sales");
  const table = sheet.addTable(sheet.getUsedRange(), true);
  table.setName("Sales");
  table.setShowTotals(true);
  table.getSort().apply([{ key: 2, ascending: false }]);

  const chart = sheet.addChart(ExcelScript.ChartType.columnClustered, table.getRange());
  chart.getTitle().setText("Sales by City");
  chart.setPosition("G2");
  console.log("Rows in the table: " + table.getRowCount());
}

A script runs synchronously: you need neither await nor context.sync() as in Office.js add-ins. console.log prints to the output pane of the Code Editor.

Running it again: a second addTable call on the same cells fails because the table already exists. So scripts that run regularly first look for the table with workbook.getTable("Sales") and create it only if the result is empty (undefined).

The simulator doesn't run scripts. Run the code from the lesson in Excel on the web (Automate tab › Code Editor). Here you learn to read code and check yourself with formulas and a quiz.