2. ExcelScript: Range, Values, and Formulas

In Office Scripts everything starts with the workbook object that is passed into main. The chain of calls goes workbook → worksheet → range:

• workbook.getWorksheet("Sheet1") gets a sheet by name, and workbook.getActiveWorksheet() gets the active sheet;
• sheet.getRange("A2:D7") is a range, and sheet.getUsedRange() is the whole filled area;
• getValue() / setValue(x) work with one cell; getValues() / setValues(array) work with a two-dimensional array of rows;
• setFormula("=SUM(D2:D7)") sets a formula (in English, with commas); getFormat().getFont().setBold(true) and getFormat().getFill().setColor("yellow") handle formatting;
• range.getCell(0, 0) is a cell inside a range, counted from zero.

The key difference from VBA: array indexes in TypeScript start at 0. For a row taken from A2:D7, row[0] is column A and row[3] is column D. Also, setValues needs an array of exactly the same size as the range.

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Sheet1");
  const range = sheet.getRange("A2:D7");
  const values = range.getValues();           // 6 rows x 4 columns

  for (const row of values) {
    const price = row[1] as number;
    const qty = row[2] as number;
    row[3] = qty >= 10 ? price * qty * 0.9 : price * qty;   // 10% discount from 10 units
  }
  range.setValues(values);                    // one write to the sheet instead of six

  sheet.getRange("D8").setFormula("=SUM(D2:D7)");
  sheet.getRange("A8:D8").getFormat().getFont().setBold(true);
}

condition ? yes : no is the short "if" in TypeScript. as number tells the language that the cell holds a number. Use formulas to get what the script would write.

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.