Practice and Answer Key: An Office Script that Summarises Orders by Status

function main(workbook: ExcelScript.Workbook): number {
  const table = workbook.getTable("tblOrders");
  const values = table.getRangeBetweenHeaderAndTotal().getValues();
  const statusCol = table.getColumnByName("Status").getIndex();
  const amountCol = table.getColumnByName("Amount").getIndex();

  // Count orders and add up the amount for each status
  const counts: { [status: string]: number } = {};
  const amounts: { [status: string]: number } = {};
  for (const row of values) {
    const s = row[statusCol] as string;
    counts[s] = (counts[s] || 0) + 1;
    amounts[s] = (amounts[s] || 0) + (row[amountCol] as number);
  }

  // Start a fresh Summary sheet every time
  let sheet = workbook.getWorksheet("Summary");
  if (sheet) {
    sheet.delete();
  }
  sheet = workbook.addWorksheet("Summary");

  // Build the output rows, A to Z by status
  const output: (string | number)[][] = [["Status", "Orders", "Amount"]];
  for (const s of Object.keys(counts).sort()) {
    output.push([s, counts[s], amounts[s]]);
  }
  sheet.getRange("A1:C" + output.length).setValues(output);
  return output.length - 1;
}