Automate a sheet with macros

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 and Build a sheet with 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.
• 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.

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:

// 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

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

// 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
• Shape data with queries: repeatable clean-up steps without code.
• Formulas, functions and named ranges
• Fill, copy and paste
• Build a sheet with AI fill