Excel Lesson 45 – Advanced Scripting | Dataplexa

Lesson 45 · Office Scripts Section Project

Advanced Scripts Project

This project brings together everything from Section VII: Office Scripts basics, recording and cleaning, writing scripts with loops and conditionals, helper functions, the Excel Table API, error handling, and Power Automate integration. You will build a complete automated order processing pipeline — a script that reads a raw orders table, validates each row, flags exceptions, writes a summary, and returns structured data that a Power Automate flow uses to send a conditional alert email. By the end of this lesson you have a production-ready automation template you can adapt for real work.

Project Overview — What We Are Building

End-to-End Pipeline

Source Data — Orders Table

Excel Table "Orders" on Sheet "Data" · columns: OrderID, Date, CustomerID, Product, Qty, Revenue, Status

Script Phase 1 — Validation & Flagging

Loop every row · check for missing values, negative revenue, overdue status · write Flag and Reason columns · colour-code rows

Script Phase 2 — Summary Sheet

Write totals, counts, and flag breakdown to "Summary" sheet · format header · add run timestamp

Script Phase 3 — Return JSON Result

Return structured summary as JSON string: totalRows, flaggedCount, totalRevenue, runDate

Power Automate — Conditional Alert

Scheduled flow calls the script · parses JSON result · if flaggedCount > 0, sends alert email with the summary

The Source Data

The project uses an Excel Table named "Orders" on a sheet named "Data". Seven columns. The script reads this table, validates each row, and adds two new columns — Flag and Reason — if they do not already exist.

Orders Table — Sample Data

OrderID	Date	CustomerID	Product	Revenue	Status	Flag
1001	2025-02-01	C011	Laptop Pro	£1,450	Paid	—
1002	2025-01-15	C034	Desk Chair	£320	Overdue	⚠ REVIEW
1003	2025-02-10	(blank)	Monitor 4K	£890	Paid	⚠ DATA
1004	2025-01-20	C019	Keyboard	-£75	Paid	⚠ VALUE
1005	2025-02-18	C052	Webcam HD	£145	Paid	—

Flag column written by the script · REVIEW = overdue · DATA = missing CustomerID · VALUE = negative revenue

Phase 1 — Validation and Flagging Script

function main(workbook: ExcelScript.Workbook): string {

  // ── Phase 1: Validation & Flagging ─────────────────────────────

  const dataSheet = workbook.getWorksheet("Data");
  if (!dataSheet) {
    return JSON.stringify({ error: "Sheet 'Data' not found." });
  }

  const table = dataSheet.getTable("Orders");
  if (!table) {
    return JSON.stringify({ error: "Table 'Orders' not found." });
  }

  // Ensure Flag and Reason columns exist — add them if not
  let flagCol   = table.getColumnByName("Flag");
  let reasonCol = table.getColumnByName("Reason");
  if (!flagCol)   { table.addColumn(-1, "Flag"); }
  if (!reasonCol) { table.addColumn(-1, "Reason"); }

  // Re-fetch after potential additions
  flagCol   = table.getColumnByName("Flag");
  reasonCol = table.getColumnByName("Reason");

  const body      = table.getRangeBetweenHeaderAndTotal();
  const rowCount  = body.getRowCount();
  const allValues = body.getValues();

  // Column index map (0-based within the table body)
  const COL = {
    orderID:    0,
    date:       1,
    customerID: 2,
    product:    3,
    revenue:    4,
    status:     5
  };

  let flaggedCount = 0;
  let totalRevenue = 0;

  for (let i = 0; i < rowCount; i++) {
    const customerID = allValues[i][COL.customerID] as string;
    const revenue    = allValues[i][COL.revenue]    as number;
    const status     = allValues[i][COL.status]     as string;

    const rowRange  = body.getRow(i);
    const flagCell  = flagCol.getRangeBetweenHeaderAndTotal().getRow(i);
    const reasonCell = reasonCol.getRangeBetweenHeaderAndTotal().getRow(i);

    let flag   = "";
    let reason = "";

    // Validation rule 1: missing CustomerID
    if (!customerID || customerID.toString().trim() === "") {
      flag   = "DATA";
      reason = "Missing CustomerID";
      rowRange.getFormat().getFill().setColor("#fef9c3");
    }
    // Validation rule 2: negative revenue
    else if (revenue < 0) {
      flag   = "VALUE";
      reason = "Negative revenue: £" + revenue;
      rowRange.getFormat().getFill().setColor("#fef2f2");
    }
    // Validation rule 3: overdue status
    else if (status === "Overdue") {
      flag   = "REVIEW";
      reason = "Payment overdue";
      rowRange.getFormat().getFill().setColor("#fef2f2");
    }
    // Clean row — clear any previous flag
    else {
      rowRange.getFormat().getFill().clear();
    }

    flagCell.setValue(flag);
    reasonCell.setValue(reason);

    if (flag !== "") flaggedCount++;
    if (revenue > 0) totalRevenue += revenue;
  }

  console.log("Phase 1 complete: " + flaggedCount + " rows flagged.");

Notice the column index map at the top of the loop — the COL object maps readable names to numeric indexes. This is far better than writing allValues[i][2] throughout the loop. When a column is added or reordered, you update the COL map once rather than hunting through every reference in the code.

Phase 2 — Writing the Summary Sheet

  // ── Phase 2: Summary Sheet ──────────────────────────────────────

  // Get or create the Summary sheet
  let summarySheet = workbook.getWorksheet("Summary");
  if (!summarySheet) {
    summarySheet = workbook.addWorksheet("Summary");
  }

  // Clear previous content
  summarySheet.getUsedRange()?.clear();

  const today = new Date().toISOString().split("T")[0]; // "2025-03-10"

  // Write summary data
  const summaryData: (string | number)[][] = [
    ["Order Processing Summary",  ""],
    ["Run date",                  today],
    ["Total orders",              rowCount],
    ["Flagged orders",            flaggedCount],
    ["Clean orders",              rowCount - flaggedCount],
    ["Total revenue (clean)",     totalRevenue],
    ["Flag rate",                 flaggedCount / rowCount]
  ];

  summarySheet.getRangeByIndexes(0, 0, summaryData.length, 2)
              .setValues(summaryData);

  // Format the header row
  const headerRange = summarySheet.getRangeByIndexes(0, 0, 1, 2);
  headerRange.getFormat().getFill().setColor("#0f172a");
  headerRange.getFormat().getFont().setColor("#ffffff");
  headerRange.getFormat().getFont().setBold(true);
  headerRange.getFormat().setRowHeight(28);
  headerRange.merge(false); // merge A1:B1 for the title

  // Format the flag rate row as percentage
  summarySheet.getRangeByIndexes(6, 1, 1, 1)
              .setNumberFormatLocal("0.0%");

  // Format the revenue row as currency
  summarySheet.getRangeByIndexes(5, 1, 1, 1)
              .setNumberFormatLocal("£#,##0");

  // Auto-fit columns
  summarySheet.getUsedRange().getFormat().autofitColumns();

  console.log("Phase 2 complete: Summary sheet updated.");

Summary Sheet — Written by the Script

Order Processing Summary
Run date	2025-03-10
Total orders	48
Flagged orders	9
Clean orders	39
Total revenue (clean)	£84,320
Flag rate	18.8%

Phase 3 — Return JSON to Power Automate

  // ── Phase 3: Return structured result ───────────────────────────

  const result = {
    totalRows:    rowCount,
    flaggedCount: flaggedCount,
    cleanCount:   rowCount - flaggedCount,
    totalRevenue: totalRevenue,
    flagRate:     Math.round((flaggedCount / rowCount) * 1000) / 10, // e.g. 18.8
    runDate:      today,
    status:       flaggedCount > 0 ? "FLAGGED" : "CLEAN"
  };

  console.log("Phase 3 complete. Status: " + result.status);
  return JSON.stringify(result);

} // end of main function

{"totalRows":48,"flaggedCount":9,"cleanCount":39,"totalRevenue":84320,"flagRate":18.8,"runDate":"2025-03-10","status":"FLAGGED"} — returned to Power Automate as a JSON string

The Power Automate Flow

Flow structure:

TRIGGER
  Recurrence — every weekday at 07:00

STEP 1 — Excel Online (Business): Run script
  File:   /Reports/Orders.xlsx
  Script: Process Orders

STEP 2 — Parse JSON
  Content: outputs('Run_script')?['body/result']
  Schema:  {
    "type": "object",
    "properties": {
      "totalRows":    { "type": "integer" },
      "flaggedCount": { "type": "integer" },
      "cleanCount":   { "type": "integer" },
      "totalRevenue": { "type": "number"  },
      "flagRate":     { "type": "number"  },
      "runDate":      { "type": "string"  },
      "status":       { "type": "string"  }
    }
  }

STEP 3 — Condition
  If: body('Parse_JSON')?['status'] is equal to "FLAGGED"
  YES path — Send an email (V2):
    To:      operations@company.com
    Subject: ⚠️ Order Review Required — [runDate]
    Body:    [flaggedCount] orders flagged out of [totalRows] total.
             Flag rate: [flagRate]%  |  Clean revenue: £[totalRevenue]
             Open the workbook for details: [link to file]
  NO path  — Terminate (status: Succeeded)

Full Flow — Visual Summary

⚡

Recurrence — Weekdays 07:00

📄

Run script — Process Orders

Returns JSON: totalRows, flaggedCount, status…

{ }

Parse JSON — extract fields

flaggedCount, totalRevenue, flagRate, runDate now available as tokens

❓

Condition — status = "FLAGGED"?

YES

Send alert email with flaggedCount, flagRate, totalRevenue

Terminate — no email, no noise

Project QA Checklist

Before going live, verify each of the following:

Script tests:
  ✓ Script runs without errors on the real workbook
  ✓ Flag column correctly identifies DATA, VALUE, and REVIEW cases
  ✓ Clean rows have no flag and cleared fill colour
  ✓ Summary sheet totals match a manual spot check
  ✓ Returned JSON string is valid (paste into jsonlint.com to verify)
  ✓ Script handles empty table gracefully (rowCount = 0, no loop errors)

Power Automate tests:
  ✓ Flow runs manually (Test → Manually) without errors
  ✓ Parse JSON step successfully extracts all fields
  ✓ Condition correctly branches on "FLAGGED" vs "CLEAN"
  ✓ Alert email arrives with correct values in body (test with a flagged row)
  ✓ Silent run (no flagged rows) produces no email — Terminate fires correctly
  ✓ Flow run history shows "Succeeded" for both test cases

💡 Teacher's Note

This project covers the full automation loop that appears in real Microsoft 365 workplaces: a script that processes data, a summary that replaces a manual report, and a flow that escalates only when action is needed. The three-phase pattern — validate, summarise, return — is reusable across almost any repetitive Excel task. Once you have built this once, adapting it for a different dataset is mostly a matter of updating the column index map and the validation rules. The structure stays the same. That is the most valuable thing to take away from this project: a repeatable template you can reach for every time a manual Excel process needs to become an automated one.

🠐 Practice

Q1. In Phase 1, the script checks if the Flag and Reason columns exist in the table and adds them if not. Why is this check important rather than assuming the columns are already there?

Q2. The COL object maps column names to numeric indexes. What is the advantage of this pattern over writing allValues[i][2] directly in the loop?

Q3. In the Power Automate flow, why does the NO path of the Condition step terminate rather than doing nothing?

Next up — Section VIII: Final Projects, where you apply everything learned across the entire course — Power Query, Power Pivot, DAX, dashboards, and Office Scripts — to build a complete, professional-grade Excel solution from scratch.

← Previous Course Index Next →

Excel Course