Office Scripts Introduction

Every Office Script starts with this structure:

function main(workbook: ExcelScript.Workbook) {
  // Your code goes here
}

Breaking it down:
  function main  → the entry point Excel calls when you run the script
  workbook       → the parameter name (convention is always "workbook")
  ExcelScript.Workbook → the TypeScript type — gives you access to the full Excel API
  { }            → the function body — everything between the braces runs in order

The workbook parameter is your gateway to everything:
  workbook.getActiveSheet()         → the currently selected sheet
  workbook.getWorksheet("Sales")    → a sheet by name
  workbook.getSheets()              → an array of all sheets
  workbook.getActiveSheet().getRange("A1") → a specific cell or range

TypeScript basics you need for Office Scripts:
  let x = 5;              → declare a variable (mutable)
  const name = "Alice";   → declare a constant (immutable)
  // comment             → single-line comment
  /* comment */           → multi-line comment
  console.log("hello");   → write to the script output log for debugging

Script 1 — Write a value and format a cell:

function main(workbook: ExcelScript.Workbook) {
  // Get the active sheet
  let sheet = workbook.getActiveSheet();

  // Write a value to cell A1
  sheet.getRange("A1").setValue("Sales Report");

  // Make it bold and set font size
  sheet.getRange("A1").getFormat().getFont().setBold(true);
  sheet.getRange("A1").getFormat().getFont().setSize(16);

  // Set background colour (hex colour string)
  sheet.getRange("A1").getFormat().getFill().setColor("#7c3aed");

  // Set font colour to white
  sheet.getRange("A1").getFormat().getFont().setColor("#ffffff");

  // Auto-fit column A width
  sheet.getRange("A:A").getFormat().autofitColumns();

  console.log("Formatting complete.");
}

Key methods used:
  .getValue() / .setValue()      → read or write a single cell value
  .getValues() / .setValues()    → read or write a 2D array (range)
  .getFormat().getFont()         → access font properties
  .getFormat().getFill()         → access background fill
  .getFormat().autofitColumns()  → auto-fit column width

Reading a single cell:

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

  // Read a single value — returns a string | number | boolean
  let cellValue = sheet.getRange("B2").getValue();
  console.log("Cell B2 contains: " + cellValue);
}

Reading a range of values — returns a 2D array:

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

  // getValues() returns a 2D array: rows × columns
  // e.g. A1:C3 → [[row1col1, row1col2, row1col3], [row2col1, ...], ...]
  let data = sheet.getRange("A1:C5").getValues();

  // Loop through rows
  for (let row of data) {
    // row is a 1D array: [col1value, col2value, col3value]
    console.log(row[0] + " | " + row[1] + " | " + row[2]);
  }
}

Finding the last used row (dynamic ranges):

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

  // getUsedRange() returns the rectangle containing all data
  let usedRange = sheet.getUsedRange();
  let rowCount = usedRange.getRowCount();
  let colCount = usedRange.getColumnCount();

  console.log("Data runs to row " + rowCount + ", column " + colCount);
}

Running a script:
  Click the ▶ Run button in the Code Editor
  Or: Automate tab → All Scripts → click the script name → Run

Saving a script:
  The editor auto-saves to OneDrive as you type
  Scripts are saved as .osts files in OneDrive/Documents/Office Scripts
  They are NOT saved inside the workbook — they are separate files

Naming scripts well:
  Click the script name at the top of the editor to rename
  Use descriptive names: "Format Sales Report Header" not "Script 1"

Sharing a script:
  Open the Scripts panel → right-click the script → Share link
  Anyone with the link and access to your OneDrive can use it
  Or: pin the script to a workbook so others with the workbook can see it
  Workbook tab → right-click script → Add to workbook

Deleting a script:
  All Scripts panel → right-click → Delete
  Or delete the .osts file directly from OneDrive

Keyboard shortcuts in the editor:
  Ctrl+S   → save
  Ctrl+Z   → undo
  Ctrl+/   → toggle comment
  F5       → run (in some environments)