Editing Scripts

Writing Office Scripts from scratch — rather than just cleaning up recordings — is where automation becomes genuinely powerful. You can write logic that makes decisions based on cell values, loops that process every row in a dataset, and helper functions that keep your code organised and reusable. This lesson covers the TypeScript fundamentals you need for real scripting work: variables and types, conditionals, loops, functions, working with Excel Tables, and handling errors gracefully.

TypeScript Fundamentals for Office Scripts

TypeScript is JavaScript with types added. For Office Scripts purposes, you do not need to be a TypeScript expert — you need to know a small, practical subset. The key concepts are: how to declare variables, how to work with the basic types (string, number, boolean), how to write if/else logic, and how to write for loops. Everything else is the ExcelScript API on top of those basics.

Variables:
  let x = 10;           // mutable — can be reassigned
  const name = "Sales"; // constant — cannot be reassigned
  let flag = true;      // boolean: true or false

Types (TypeScript enforces these):
  let revenue: number  = 5000;
  let region:  string  = "North";
  let active:  boolean = true;

  // Type annotation after the variable name with a colon
  // TypeScript will warn you if you assign the wrong type

String operations:
  let first = "John";
  let last  = "Smith";
  let full  = first + " " + last;       // "John Smith"
  let upper = region.toUpperCase();     // "NORTH"
  let len   = region.length;            // 5
  let has   = region.includes("orth");  // true

Number operations:
  let total = 1000;
  let tax   = total * 0.2;    // 200
  let round = Math.round(tax); // 200
  let floor = Math.floor(1.9); // 1
  let str   = total.toString(); // "1000" (convert to string)

Boolean / comparison:
  5 > 3      // true
  5 === 5    // true (strict equality)
  5 !== 3    // true (not equal)
  "a" === "A" // false (case sensitive)
  true && false // false (AND)
  true || false // true  (OR)
  !true         // false (NOT)

One TypeScript detail that catches beginners: use triple-equals (===) for comparisons, not double-equals (==). Double-equals performs type coercion — it might consider "5" == 5 to be true because it converts the string to a number before comparing. Triple-equals checks both value and type, so "5" === 5 is false. In Office Scripts, cell values read with getValue() come back as string | number | boolean — always use === to avoid type coercion surprises when comparing them.

Conditionals — Making Decisions

If/else statements let a script make decisions based on cell values, row counts, or any other condition. Combined with the ExcelScript API, they let you write scripts that behave differently for different data — flagging overdue items, colour-coding rows by status, or skipping blank rows automatically.

Basic if/else:

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveSheet();
  const value = sheet.getRange("B2").getValue() as number;

  if (value >= 10000) {
    sheet.getRange("A2").setValue("High");
    sheet.getRange("A2").getFormat().getFill().setColor("#15803d");
  } else if (value >= 5000) {
    sheet.getRange("A2").setValue("Medium");
    sheet.getRange("A2").getFormat().getFill().setColor("#d97706");
  } else {
    sheet.getRange("A2").setValue("Low");
    sheet.getRange("A2").getFormat().getFill().setColor("#dc2626");
  }
}

Note: getValue() returns string | number | boolean
  The "as number" casts the value to a number type so TypeScript
  lets you use >= on it. Use "as string" for text comparisons.

Checking for blank cells:
  const val = sheet.getRange("A1").getValue();
  if (val === "" || val === null) {
    console.log("Cell is blank — skipping");
  }

B2 = 12500 → A2 labelled "High" with green fill · B2 = 6800 → "Medium" amber · B2 = 2300 → "Low" red

The as number and as string type casts are necessary because getValue() has a union return type — TypeScript does not know at compile time whether the cell contains text or a number. Casting tells TypeScript what type you expect, and if you are wrong, the script will either produce unexpected results or throw a runtime error. Always check that the cell actually contains the type you are casting to — add a console.log(typeof value) line during development to confirm.

Loops — Processing Every Row

Loops let a script process every row in a dataset — flagging rows that meet a condition, writing a calculated value for each row, or applying conditional formatting row by row. The two loop types you will use most are the for loop (when you need the index) and the for...of loop (when you just need the values).

Getting all data as a 2D array, then looping:

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

  // Read all values at once — much faster than reading row by row
  const allValues = usedRange.getValues();

  // Loop from row index 1 (skipping header row at index 0)
  for (let i = 1; i < rowCount; i++) {
    const revenue = allValues[i][2] as number; // column C = index 2
    const status  = allValues[i][4] as string; // column E = index 4

    // Write a flag in column F (index 5) based on conditions
    const flagCell = sheet.getRangeByIndexes(i, 5, 1, 1);

    if (status === "Overdue" && revenue > 5000) {
      flagCell.setValue("URGENT");
      flagCell.getFormat().getFill().setColor("#dc2626");
      flagCell.getFormat().getFont().setColor("#ffffff");
      flagCell.getFormat().getFont().setBold(true);
    } else if (status === "Overdue") {
      flagCell.setValue("Review");
      flagCell.getFormat().getFill().setColor("#fef9c3");
    } else {
      flagCell.setValue("");
      flagCell.getFormat().getFill().clear();
    }
  }

  console.log("Flagged " + (rowCount - 1) + " data rows.");
}

Key pattern: read all data with getValues() first, then loop
  Reading the entire range at once is far more efficient than
  calling getValue() on each cell inside the loop.
  Write back to the sheet with getRangeByIndexes() inside the loop.

Loop Result — Flag Column Written Row by Row

OrderID	Revenue	Status	Flag (written by script)
1001	£12,500	Overdue	URGENT
1002	£3,200	Overdue	Review
1003	£8,900	Paid	(blank)
1004	£15,600	Overdue	URGENT

Flag column written by the loop · URGENT = overdue AND revenue above £5K · Review = overdue only · blank = paid

Helper Functions — Organising Reusable Logic

When a script grows beyond a dozen lines, pulling repeated logic into named helper functions keeps the main function readable and makes the script easier to maintain. TypeScript functions outside of main are fully supported in Office Scripts.

Extracting logic into helper functions:

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

  for (let i = 1; i < rowCount; i++) {
    const revenue = allValues[i][2] as number;
    const status  = allValues[i][4] as string;
    const flag    = getFlag(revenue, status);  // call helper function
    const colour  = getFlagColour(flag);       // call helper function

    const cell = sheet.getRangeByIndexes(i, 5, 1, 1);
    cell.setValue(flag);
    if (colour) {
      cell.getFormat().getFill().setColor(colour);
      cell.getFormat().getFont().setBold(flag === "URGENT");
    }
  }
}

// Helper function 1 — determine the flag label
function getFlag(revenue: number, status: string): string {
  if (status === "Overdue" && revenue > 5000) return "URGENT";
  if (status === "Overdue") return "Review";
  return "";
}

// Helper function 2 — map flag label to fill colour
function getFlagColour(flag: string): string {
  if (flag === "URGENT") return "#dc2626";
  if (flag === "Review") return "#fef9c3";
  return "";
}

Benefits:
  - main() reads like a clear description of what the script does
  - Logic is in one place — change the threshold once, all rows update
  - Functions can be tested individually by calling them with test values
  - Functions can be shared across multiple scripts

Notice the return type annotations: function getFlag(...): string tells TypeScript this function always returns a string. TypeScript uses this information to check that you are not accidentally using the return value as a number elsewhere. For Office Scripts, annotating return types on helper functions is good practice — it makes the code self-documenting and catches bugs before you run the script.

Working With Excel Tables

Excel Tables (Insert → Table) have their own API in Office Scripts — separate from range operations. When your data is structured as an Excel Table, the Table API gives you named access to columns, easy row addition, and sorted/filtered access that is more reliable than range indexing.

Getting a Table and its data:

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

  // Get a named Table
  const table = sheet.getTable("SalesData");
  // Or: workbook.getTable("SalesData") searches all sheets

  // Get the data body range (excludes header row)
  const body     = table.getRangeBetweenHeaderAndTotal();
  const rowCount = body.getRowCount();
  const values   = body.getValues();

  console.log("Table has " + rowCount + " data rows.");

  // Access a specific column by name
  const revenueCol = table.getColumnByName("Revenue");
  const revenueValues = revenueCol.getRangeBetweenHeaderAndTotal().getValues();

  // Add a new row to the table
  table.addRow(-1, ["1099", "2025-03-01", "C045", "P012", 3, 4500, 2800]);
  // -1 means append to the end

  // Get the header row
  const headers = table.getHeaderRowRange().getValues();
  console.log("Columns: " + headers[0].join(", "));
}

Table has 46 data rows. · Columns: OrderID, OrderDate, CustomerID, ProductID, Quantity, Revenue, Cost · new row appended at bottom

Using getColumnByName("Revenue") instead of getValues()[i][5] is far more robust. If the column order in the table ever changes, a numeric index breaks silently — the script reads the wrong column and produces wrong results with no error. A named column reference always points to the correct data regardless of where the column sits.

Error Handling — Try/Catch

Office Scripts stop execution and show an error message if something goes wrong — a range that does not exist, a table name that is misspelled, a value that cannot be converted to a number. A try/catch block lets you handle errors gracefully: log what went wrong, write a fallback value, and let the rest of the script continue.

Basic try/catch pattern:

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

  try {
    // Code that might fail
    const table = sheet.getTable("SalesData");
    if (!table) {
      throw new Error("Table 'SalesData' not found on this sheet.");
    }

    const body   = table.getRangeBetweenHeaderAndTotal();
    const values = body.getValues();
    console.log("Loaded " + values.length + " rows.");

  } catch (error) {
    // error is the Error object thrown above or an API error
    console.log("Script failed: " + error);
    // Optionally write the error to a cell for visibility
    sheet.getRange("A1").setValue("ERROR: " + error);
  }
}

Checking for null before using a result:

function main(workbook: ExcelScript.Workbook) {
  const table = workbook.getTable("SalesData");

  // getTable() returns null if the table does not exist
  // instead of throwing an error
  if (table === null) {
    console.log("SalesData table not found — check sheet name.");
    return; // exit the script early
  }

  // Safe to use table here
  console.log("Table found: " + table.getName());
}

The null check pattern — if (table === null) { return; } — is the cleaner approach when an API method might legitimately return null. It avoids the overhead of try/catch for an expected condition. Use try/catch for genuinely unexpected errors (API failures, malformed data, unexpected types). Use null checks for conditions that can be anticipated and handled directly — a missing table, an empty sheet, a missing column name.

💡 Teacher's Note

The single biggest performance mistake in Office Scripts is calling getValue() or setValue() on individual cells inside a loop. Every API call to Excel takes time — and in a loop over 500 rows, that becomes 500 individual round trips. The fix is to read everything with a single getValues() call before the loop starts (one round trip to get all data as a 2D array), do all your processing in memory using the array, and then write results back in batches using setValues() after the loop ends. This pattern can make a slow script twenty to fifty times faster. Read once, process in memory, write once. That is the performance mantra for Office Scripts loops.

🠐 Practice

Q1. Write a short script that reads the value from cell C5, and if it is greater than 1000, sets cell D5 to "Over budget" with a red background. Otherwise sets D5 to "OK" with a green background.

Q2. Why should you call getValues() once before a loop rather than calling getValue() on each cell inside the loop?

Q3. You want to access the Revenue column in an Excel Table named "SalesData" by column name rather than by index. Write the TypeScript line that gets the Revenue column's data range.

Next up — Automating Tasks, where we connect Office Scripts to Power Automate to run scripts on a schedule, trigger them from form submissions, and build end-to-end automated workflows that require no manual intervention.

← Previous Course Index Next →

Excel Course