Skip to content
Tutorials Center
My learning

Topics

Lesson 79 of 93 ยท Automation and Collaboration

Office Scripts, Power Automate and Python in Excel

Compare modern Excel automation: Office Scripts in TypeScript, Power Automate flows and Python in Excel with the PY function, and learn which one to pick.

VBA is not the only way to automate Excel. Microsoft now offers three newer tools: Office Scripts (TypeScript that runs in Excel on the web), Power Automate (flows that connect Excel to email, forms, Teams and schedules) and Python in Excel (the PY function, which runs Python inside a cell). They solve different problems, and picking the right one saves a lot of effort.

None of these can run on this site, so every Office Script, flow and PY formula below is read-only and described conservatively. Availability, limits and licensing change often; check Microsoft's current documentation before you plan around a feature. The TryIts are stand-ins written in JavaScript and Python: they show the idea of each tool (read values, transform them, write them back; trigger and steps; a DataFrame in a cell) without calling any Microsoft service.

Three modern tools

Office Scripts TypeScript for the workbook.
Power Automate Flows triggered by events.
Python in Excel Analysis inside cells.

Each has a different home, language and trigger.

1 / 2

Office Scripts

An Office Script is a small TypeScript program that works on a workbook through the ExcelScript object model. You record one with the Automate tab (Excel on the web), edit it in the Code Editor, and save it in your OneDrive so you can run it from a button or from a flow. Unlike VBA, scripts are not stored inside the workbook file, which means the file stays an ordinary .xlsx.

Every script has a main function that receives the workbook:

TypeScript
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Sales");
  const used = sheet.getUsedRange();
  const values = used.getValues();          // 2D array of cell values

  // Row 0 is the header; color the Amount cell green when it is >= 1000
  for (let r = 1; r < values.length; r++) {
    const amount = values[r][2] as number;
    if (amount >= 1000) {
      used.getCell(r, 2).getFormat().getFill().setColor("#C6EFCE");
    }
  }
}

Notice the pattern that makes scripts fast: read a whole range into an array with getValues(), work in memory, write back with setValues(). Calling the workbook cell by cell is slow because each call crosses between your script and Excel. Because it is TypeScript, you get types, autocomplete and the same language skills as the TypeScript tutorial. A script can also take parameters and return a value, which is how flows pass data in and out.

Things to know, stated cautiously:

  • Scripts need a Microsoft 365 work or school account with an eligible license; availability on desktop Excel has been expanding, so check the current status for your version.
  • They use the modern Excel object model, which is similar in spirit to VBA's but not identical (getRange("A1:C5"), getValues(), setFormulas()).
  • They cannot run unattended on your PC the way a VBA macro can; unattended runs go through Power Automate.

Run it: read, transform, write back

This stand-in does exactly what the script does, using a plain 2D array as "the used range". Nothing here talks to Excel.

JS The getValues / setValues pattern

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Power Automate

Power Automate is a separate Microsoft service where you build flows: a trigger (something happens) followed by actions (do this, then that). Excel takes part through the Excel Online (Business) connector, which can read rows from a table, add or update rows, and run an Office Script on a workbook stored in OneDrive or SharePoint.

Typical uses:

  • A form response or an incoming email adds a row to a table.
  • Every Monday at 8:00 a flow runs a script that refreshes a sheet and sends a summary to a Teams channel.
  • When a row in an approvals table changes to "Approved", the flow notifies the requester.
TEXT
Trigger:  Recurrence - every Monday 08:00
Action 1: Run script (Excel Online Business) - script "Weekly summary"
Action 2: Post message in a Teams channel - body = script result

Flows work on data that lives in tables (see Excel Tables), because the connector addresses a table by name, not by cell range. Design your workbook with that in mind: clean headers, one record per row, no merged cells.

Run it: a flow as trigger plus steps

This stand-in represents a flow as data: one trigger and a list of steps, each receiving the previous step's output. Real flows are built visually and run in the cloud; this only shows the shape.

JS A flow as trigger plus steps

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Python in Excel

Python in Excel lets you type Python inside a cell. You enter the PY function (type =PY and press Tab, or use the Formulas > Insert Python command), write code in the cell, and press Ctrl+Enter to run it. The code runs in Microsoft's cloud, not on your computer, in a standard Python environment that includes pandas, Matplotlib and other popular libraries. A function called xl() brings workbook data into Python:

TEXT
=PY(
df = xl("Sales[#All]", headers=True)
df.groupby("Region")["Amount"].sum()
)

The result can come back as a Python object (shown as a DataFrame card you can expand into cells) or as plain Excel values that spill into the grid, where ordinary formulas can use them. Python cells calculate in order, row by row and then column by column, and share variables between cells: a DataFrame defined in one cell is usable in the next ones.

What to expect, kept deliberately general:

  • It needs a supported Microsoft 365 plan and a recent Excel build, and internet access, because the code runs remotely.
  • You cannot install your own packages or access local files or the network from that code.
  • It is best for analysis, statistics and charts that formulas make awkward. For simple sums, regular formulas stay faster and easier to audit.

Run it: the same group-by in real pandas

The cell above is just pandas. This TryIt runs real Python (Pyodide, in your browser), with a DataFrame standing in for what xl("Sales[#All]", headers=True) would return.

Python What PY would compute: a pandas group-by

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Choosing the right tool

Need Good choice Why
Repeat clicks on one workbook, run by a button Office Script or VBA Both act on the workbook directly
Must work offline on desktop, or use a custom =FUNCTION() VBA Runs locally; the only option for user-defined functions of this kind
Run on a schedule or when an email or form arrives Power Automate (with a script) Cloud triggers, no one has to open the file
Share automation without macro warnings Office Script The workbook stays an .xlsx; sharing follows SharePoint and OneDrive permissions
Statistics, grouping, forecasting over large tables Python in Excel or Power Query Better tools than long formula chains
One calculation reused in many cells LAMBDA (see LET and LAMBDA) No code or macro security needed

A useful rule: try a formula first, then Power Query, and only then code. Formulas are visible and auditable by everyone; code is for what they cannot do. Also check your organization's policies, since administrators can disable Office Scripts, Python in Excel or specific Power Automate connectors, and they can limit who may run them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why do Office Scripts read data with getValues() and write with setValues()?

    Each call between the script and Excel has a cost. Moving a 2D array in one call and processing it in memory is the standard fast pattern.
  2. 2. You want a task to run every Monday at 8:00 without anyone opening the workbook. What fits best?

    Power Automate provides schedule and event triggers in the cloud. It can call an Office Script to do the work in the workbook.
  3. 3. Where does Python code in an Excel cell run?

    Python in Excel sends the code to a Microsoft-managed environment and returns the result, which is why it needs internet access and cannot install your own packages.
  4. 4. What is the best first choice for a simple total by region?

    Formulas and PivotTables are visible, quick and understood by every Excel user. Reach for code only when they cannot do the job.

Summary

  • Office Scripts are TypeScript programs for Excel on the web; read ranges with getValues(), process in memory, write back with setValues().
  • Power Automate runs flows (trigger plus actions) and can run Office Scripts on tables stored in OneDrive or SharePoint.
  • Python in Excel uses the PY function and xl() to analyze workbook data with pandas, running in the Microsoft cloud.
  • Pick by need: buttons and local custom functions suit VBA, schedules and events suit Power Automate, heavy analysis suits Python or Power Query.
  • Formulas first, then Power Query, then code. Check licensing, availability and admin policies before you commit.

Next: Collaboration, Comments and Protecting Work.