# Automate a sheet with macros

> Write a short JavaScript macro that reads and changes the sheet you are on, run it in one click, and undo the whole run in one step.

Source: https://plexiidesk.com/help/sheets-macros
Section: Office > PlexiiSheets
Plans: All plans
Updated: 2026-10-15

A macro is a short script that does a job on your sheet for you: fill a column from another, tidy every value in a column, add a row of totals, rename the headings. You write it in JavaScript, run it with one click, and the changes land on the sheet tab you are on.

Use a macro for a change that would take many clicks by hand, or one you make the same way again and again. For a change you can describe in words, a formula or AI fill may be quicker. See [Formulas, functions and named ranges](https://plexiidesk.com/help/sheets-formulas-and-functions) and [Build a sheet with AI fill](https://plexiidesk.com/help/sheets-ai-fill).

## Before you start

- Macros are on every plan.
- Open a spreadsheet in PlexiiSheets, or click inside a Spreadsheet widget on a desk. See [PlexiiSheets overview](https://plexiidesk.com/help/sheets-overview).
- Click the sheet tab you want the macro to work on. A macro works on that tab only.
- You need to be comfortable writing a little JavaScript.

## Run a macro

1. Click the macros button on the toolbar (**Macros — automate the sheet with a script**). The **Macros** window opens with a short example macro already in it.
2. Replace the example with your own macro, or edit it.
3. Click **Run macro**.

If the macro works, the sheet changes straight away and the window shows anything the macro logged, or **Ran — no output logged.** If something goes wrong, the window shows the error in red and the sheet is left exactly as it was.

To close the window, click **Close** or **×**, click outside it, or press `Esc`.

> **Tip:**
> A whole run is one step in the undo history. If a macro did something you did not want, click **Undo** on the toolbar, or press `⌘` `Z` on a Mac or `Ctrl` `Z` on Windows, and the sheet goes back to how it was before the run.

## Write a macro

Write a function called `main` that takes the sheet. PlexiiSheets calls it when you click **Run macro**. This is the example the window starts with:

```js
// Double column A into column B for every row.
function main(sheet) {
  for (let r = 0; r < sheet.rowCount(); r++) {
    const n = Number(sheet.getValue(r, 0))
    if (!Number.isNaN(n)) sheet.setValue(r, 1, n * 2)
  }
  sheet.log('Updated', sheet.rowCount(), 'rows')
}
```

Rows and columns are counted from 0: row 0 is the sheet's row 1, and column 0 is column A.

### What a macro can do with the sheet

| Command | What it does |
| --- | --- |
| `sheet.rowCount()` | How many rows the sheet has. |
| `sheet.colCount()` | How many columns the sheet has. |
| `sheet.getValue(r, c)` | What is in one cell, as text. |
| `sheet.setValue(r, c, value)` | Puts a value in one cell. |
| `sheet.getColumn(c)` | Every value in one column, top to bottom. |
| `sheet.getRange(r0, c0, r1, c1)` | A block of cells, as a list of rows. |
| `sheet.setRange(r0, c0, rows)` | Writes a block of values, starting at one cell. |
| `sheet.addRow(values)` | Adds a row at the bottom, filled with the values you give, if any. |
| `sheet.addColumn(name)` | Adds a column on the right, with the heading you give, if any. |
| `sheet.columnName(c)` | A column's heading. |
| `sheet.setColumnName(c, name)` | Changes a column's heading. |
| `sheet.clear()` | Empties every cell, keeping the rows and columns. |
| `sheet.log(...)` | Shows a line in the window after the run. |

A few things to know:

- `getValue` gives you what is typed in the cell. For a cell with a formula, that is the formula, such as `=SUM(B2:B9)`, not its result.
- To write a formula, set a value that starts with =, such as `sheet.setValue(9, 1, '=SUM(B1:B9)')`.
- Writing to a cell past the edge of the sheet adds the rows and columns needed to reach it.
- A macro can use everyday JavaScript, such as `Math`, `Number`, `String`, `Date`, `JSON` and regular expressions.

### Example: add a row of totals

```js
// Add a total of column B under the last row.
function main(sheet) {
  let total = 0
  for (const v of sheet.getColumn(1)) {
    const n = Number(v)
    if (v !== '' && !Number.isNaN(n)) total += n
  }
  sheet.addRow(['Total', total])
  sheet.log('Total', total)
}
```

## What a macro cannot do

A macro works with the sheet tab it runs on and nothing else. It cannot open files, reach the internet or change anything else in PlexiDesk, and it stops after 3 seconds, so a loop that never ends cannot hold the sheet up.

## Common questions

**The window says the macro references something outside the sheet.** Macros refuse to run when the code contains certain words, including `require`, `import`, `process`, `module`, `global`, `fetch`, `eval` and `Function`, even inside a comment. Remove or reword them and run it again.

**The window says the script timed out.** The macro ran for more than 3 seconds, usually because a loop never ends. Check the loop's condition, such as `r < sheet.rowCount()` when the loop also adds rows.

**Nothing changed and no error showed.** Check that your function is called `main` and that it calls `setValue`, `setRange`, `addRow` or another command that changes the sheet. A `sheet.log` line is a quick way to see what the macro found.

**Is my macro saved?** No. The window starts with the example each time you open it, so keep a copy of a macro you will run again, for example in a note or a document.

**Can a macro work on another tab?** It works on the tab that is open when you click **Run macro**. Switch tabs and run it again for another one.

**Do macros go into an exported Excel file?** No. The .xlsx file holds the cells as the macro left them.

## Related tasks

- [PlexiiSheets overview](https://plexiidesk.com/help/sheets-overview)
- [Shape data with queries](https://plexiidesk.com/help/sheets-queries): repeatable clean-up steps without code.
- [Formulas, functions and named ranges](https://plexiidesk.com/help/sheets-formulas-and-functions)
- [Fill, copy and paste](https://plexiidesk.com/help/sheets-fill-copy-paste)
- [Build a sheet with AI fill](https://plexiidesk.com/help/sheets-ai-fill)