Excel Lesson 42 – Recording Scripts | Dataplexa

Lesson 42 · Office Scripts Practical

Recording Scripts

The Action Recorder is the fastest way to start writing Office Scripts. You perform actions in Excel — formatting cells, sorting data, applying filters — and the recorder converts every click into TypeScript code automatically. The generated code is not always production-ready, but it gives you a working foundation to read, understand, and edit into something reusable. This lesson covers how to record a script, how to read and interpret the generated TypeScript, how to clean it up, and the common recording pitfalls to watch for.

How the Action Recorder Works

The Action Recorder watches your actions in Excel and translates each one into an API call in TypeScript. Every click, every format change, every value entry becomes a line of code. When you stop recording, the full script appears in the Code Editor, ready to run or edit.

Recording Flow — Action to TypeScript

Automate Tab

● Recording...

You do in Excel

Click cell A1
Type "Monthly Sales"
Press Enter

Generated TypeScript

let selectedSheet = workbook.getActiveSheet();
selectedSheet.getRange("A1").setValue("Monthly Sales");

You do in Excel

Select A1
Click Bold
Set font size 14

Generated TypeScript

selectedSheet.getRange("A1").getFormat().getFont().setBold(true);
selectedSheet.getRange("A1").getFormat().getFont().setSize(14);

You do in Excel

Select A1:D1
Set fill colour
to #1d4ed8

Generated TypeScript

selectedSheet.getRange("A1:D1").getFormat().getFill().setColor("#1D4ED8");

Every action captured as TypeScript in real time · click Stop to end recording and open the code editor

Starting the Action Recorder:
  Automate tab → Record Actions
  The recorder starts immediately — every action is now being captured
  A red recording indicator appears in the Automate tab

Stopping the recorder:
  Automate tab → Stop (the Record Actions button becomes Stop while recording)
  The Code Editor opens with the generated script

What the recorder captures:
  ✓ Cell values (typing, paste)
  ✓ Cell formatting (font, fill, borders, number format)
  ✓ Sorting and filtering
  ✓ Sheet navigation
  ✓ Table operations (add row, resize)
  ✓ Cell selection and range operations

What the recorder does NOT capture:
  ✗ Formula bar edits (formula entry is not reliably recorded)
  ✗ PivotTable interactions
  ✗ Chart creation and formatting
  ✗ Power Query operations
  ✗ Dialog box actions (most)
  ✗ Copy/paste from external sources

The recorder is best used for formatting tasks, data entry patterns, sort and filter sequences, and basic range operations. It is not a complete automation recorder — for complex workflows involving formulas, charts, and data model operations, you write the script manually. But as a starting point for understanding the API and generating boilerplate formatting code, it saves a significant amount of time.

A Real Recording — Header Formatting Script

Here is a typical recording session: formatting a data table header row. You record the actions once, then the script can apply the same formatting to any sheet in seconds.

Actions recorded:
  1. Click cell A1
  2. Select A1:E1 (the header row)
  3. Set fill colour: #0f172a (dark navy)
  4. Set font colour: #ffffff (white)
  5. Set font bold: true
  6. Set font size: 12
  7. Set row height: 28

Generated script (raw recorder output):

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveSheet();
  selectedSheet.getRange("A1:E1").getFormat().getFill().setColor("#0F172A");
  selectedSheet.getRange("A1:E1").getFormat().getFont().setColor("#FFFFFF");
  selectedSheet.getRange("A1:E1").getFormat().getFont().setBold(true);
  selectedSheet.getRange("A1:E1").getFormat().getFont().setSize(12);
  selectedSheet.getRange("A1:E1").getFormat().setRowHeight(28);
}

Row A1:E1 formatted: dark navy background · white bold 12pt text · row height 28 — runs in under one second on any sheet

The generated code is functional but has a problem: the range "A1:E1" is hardcoded. If the table has 8 columns next month, the script only formats the first 5. This is the most common issue with recorded scripts — they capture the exact state at recording time rather than adapting to the data. Cleaning up a recorded script mostly means replacing hardcoded ranges with dynamic calculations.

Cleaning Up a Recorded Script

Recorded scripts work, but they are brittle. Hardcoded cell addresses break when the data changes. Repeated calls to getRange("A1:E1") on every line can be consolidated. And the generated variable name selectedSheet is functional but not descriptive. Cleaning up takes five minutes and produces a script that is genuinely reusable.

Original recorded script (brittle):

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveSheet();
  selectedSheet.getRange("A1:E1").getFormat().getFill().setColor("#0F172A");
  selectedSheet.getRange("A1:E1").getFormat().getFont().setColor("#FFFFFF");
  selectedSheet.getRange("A1:E1").getFormat().getFont().setBold(true);
  selectedSheet.getRange("A1:E1").getFormat().getFont().setSize(12);
  selectedSheet.getRange("A1:E1").getFormat().setRowHeight(28);
}

Cleaned up (dynamic, readable, reusable):

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveSheet();

  // Find the actual last used column dynamically
  const usedRange = sheet.getUsedRange();
  const lastCol   = usedRange.getColumnCount();

  // Build the header range: row 1, from column A to last column
  // getCell(row, col) is zero-indexed: row 0 = row 1, col 0 = col A
  const headerRange = sheet.getRangeByIndexes(0, 0, 1, lastCol);

  // Apply formatting in one place
  const fmt = headerRange.getFormat();
  fmt.getFill().setColor("#0F172A");
  fmt.getFont().setColor("#FFFFFF");
  fmt.getFont().setBold(true);
  fmt.getFont().setSize(12);
  fmt.setRowHeight(28);

  console.log("Header formatted across " + lastCol + " columns.");
}

Key improvements:
  - lastCol is calculated at runtime — works for 5 or 50 columns
  - getRangeByIndexes(row, col, rowCount, colCount) builds range dynamically
  - getFormat() called once and stored — cleaner than repeating the chain
  - console.log confirms what was done — useful for debugging

getRangeByIndexes — Understanding the Parameters

Parameter	Meaning	Example
startRow	First row (0-indexed)	0 = row 1, 1 = row 2
startCol	First column (0-indexed)	0 = col A, 4 = col E
rowCount	Number of rows to include	1 = one row only
colCount	Number of columns to include	lastCol = all used columns

getRangeByIndexes(0, 0, 1, lastCol) = row 1, column A, 1 row tall, as wide as the data

Common Recorder Pitfalls and How to Fix Them

Recorder Pitfalls — Problem and Fix

Pitfall	What recorder generates	What to do instead
Hardcoded range address	getRange("A1:E100")	Use getUsedRange() and getRangeByIndexes() to calculate at runtime
Hardcoded sheet name	getWorksheet("Sheet1")	Use getActiveSheet() or pass the sheet name as a parameter
Repeated method chain	getRange("A1").getFormat()... x5 lines	Store getRange("A1").getFormat() in a variable; call properties on it
Undescriptive variable name	let selectedSheet = ...	Rename to something meaningful: const sheet, const salesSheet
Accidental navigation recorded	getWorksheet("Sheet3").activate()	Delete any sheet navigation lines that are artefacts of recording, not intended actions

A Complete Cleaned Recording — Table Formatter

Putting it all together: a script that records a table formatting action and is then cleaned into a reusable formatter that works on any data table regardless of size.

function main(workbook: ExcelScript.Workbook) {
  const sheet     = workbook.getActiveSheet();
  const usedRange = sheet.getUsedRange();
  const rowCount  = usedRange.getRowCount();
  const colCount  = usedRange.getColumnCount();

  // --- Header row (row 1) ---
  const header = sheet.getRangeByIndexes(0, 0, 1, colCount);
  const hFmt   = header.getFormat();
  hFmt.getFill().setColor("#0F172A");
  hFmt.getFont().setColor("#FFFFFF");
  hFmt.getFont().setBold(true);
  hFmt.getFont().setSize(12);
  hFmt.setRowHeight(28);

  // --- Data rows (rows 2 onwards) ---
  if (rowCount > 1) {
    const dataRows = sheet.getRangeByIndexes(1, 0, rowCount - 1, colCount);
    const dFmt     = dataRows.getFormat();
    dFmt.getFill().setColor("#FFFFFF");
    dFmt.getFont().setColor("#0F172A");
    dFmt.getFont().setSize(11);
    dFmt.setRowHeight(22);
  }

  // --- Alternate row shading (zebra stripes) ---
  for (let i = 1; i < rowCount; i++) {
    // i is 0-indexed, so i=1 is the second row (first data row)
    const rowRange = sheet.getRangeByIndexes(i, 0, 1, colCount);
    // Even data rows (i=2,4,6...) get a light grey background
    if (i % 2 === 0) {
      rowRange.getFormat().getFill().setColor("#F8FAFC");
    }
  }

  // --- Auto-fit all columns ---
  usedRange.getFormat().autofitColumns();

  console.log("Table formatted: " + rowCount + " rows, " + colCount + " columns.");
}

Dark header row · white data rows with light grey zebra stripes on even rows · all columns auto-fitted · Output: "Table formatted: 47 rows, 6 columns."

The loop for zebra striping is something the recorder cannot generate on its own — it requires logic (the if statement and modulo operator). This is the point where cleaning a recording crosses into writing your own code. The recorder gave you the formatting method syntax; you supply the logic. That combination — recorder for API discovery, manual editing for logic — is the most efficient way to work with Office Scripts.

💡 Teacher's Note

Think of the Action Recorder as a way to learn the Office Scripts API rather than a way to write finished scripts. Every time you record an action and see what TypeScript it generates, you learn one more method name and one more pattern. After ten or fifteen recording sessions, you will have seen enough of the API that you can write most simple scripts from scratch without recording at all. Use the recorder deliberately as a learning tool: record one action, study the output, understand what the method does, then try writing the next variant by hand before recording to check your work. This approach builds fluency faster than any other method.

🠐 Practice

Q1. You record a script that formats cell A1 bold. The recorder generates: selectedSheet.getRange("A1").getFormat().getFont().setBold(true); What is the first cleanup change you should make to the variable name?

Q2. A recorded script hardcodes the range "A1:F200". How would you replace this with a dynamic range that always covers exactly the used data?

Q3. In getRangeByIndexes(0, 0, 1, lastCol), what does the first 0 represent and what does the second 0 represent?

Next up — Editing Scripts, where we go beyond recording and write Office Scripts from scratch — working with loops, conditionals, functions, and the full range of ExcelScript API objects to build scripts that make real decisions about your data.

← Previous Course Index Next →

Excel Course

Recording Scripts

How the Action Recorder Works

A Real Recording — Header Formatting Script

Cleaning Up a Recorded Script

Common Recorder Pitfalls and How to Fix Them

A Complete Cleaned Recording — Table Formatter

🠐 Practice

🟣 Quiz