1---2title: "Macro scripting API"3sidebar_position: 54---56# Macro scripting API78What a [macro](../sheets/macros.md) script can do. Recorded macros use9the same functions, so recording one is a quick way to see them.1011A script runs from top to bottom. It's Starlark: Python's syntax12(`for`, `if`, `def`, lists, dicts, string formatting, `while`) without13imports, classes or exceptions. Statements may be at the top level.1415```python16# Total every numeric column of the selection, in the row below it.17sel = selection() # e.g. "B2:D9"18rows = get(sel)19if type(rows) != "list":20 rows = [[rows]] # a single cell21below = offset(sel.split(":")[-1], 0, 1)22first = offset(sel.split(":")[0], 0, len(rows))23for c in range(len(rows[0])):24 col = [r[c] for r in rows]25 if all([type(v) in ("int", "float") for v in col]):26 cell = offset(first, c, 0)27 set_formula(cell, "SUM(%s:%s)" % (offset(sel.split(":")[0], c, 0), offset(cell, 0, -1)))28select(first)29run("format.bold")30```3132References are strings in A1 notation: a cell (`"B3"`), a range33(`"B3:D9"`), whole columns (`"B:D"`) or rows (`"3:5"`), a named range34(`"Sales"`), any of them after a sheet name (`"Sheet2!A1"`,35`"'Q3 plan'!A1:B2"`). Values come back as Starlark values: whole numbers36as `int`, others as `float`, text as `str`, `True` and `False`, `None`37for a blank cell, and error values as their code (`"#DIV/0!"`).3839## Cells4041| Function | Does |42|---|---|43| `get(ref)` | The value of a cell, or a list of rows of values for a range |44| `get_formula(ref)` | A cell's formula as typed (`"=SUM(B2:B8)"`), `""` if it holds none; a list of rows for a range |45| `set(ref, value)` | Enter a value as if typed: numbers, `True`/`False`, `None` (clears), or text, which is read as typed (`"=A1*2"` is a formula, `"$5"` currency, `"'=x"` text). A range gets `value` in every cell, or a list of rows (`[[1, 2], [3, 4]]`) from its first cell |46| `set_formula(ref, formula)` | Enter a formula (the `=` is optional) in the first cell and fill it over the range, relative references adjusting as in a copy |47| `clear(ref=None)` | Clear contents, keeping formatting; the selection by default |48| `number_format(ref, kind, decimals=None, pattern="")` | Format numbers: kind is `auto`, `text`, `number`, `percent`, `scientific`, `accounting`, `financial`, `currency`, `date`, `time`, `datetime`, `duration`, `size`, or `custom` with a `pattern` such as `"0.0%"` |49| `get_number_format(ref)` | The kind of a cell's number format |50| `offset(ref, cols=0, rows=0)` | `ref` moved, keeping its size and sheet: `offset("A1", 2, 3)` is `"C4"` |5152Formulas read inside a script are up to date: a value set a line earlier53is already in the formulas that use it.5455## The selection5657| Function | Does |58|---|---|59| `selection()` | The selected range, e.g. `"B2:D9"`, `"B:D"` |60| `active_cell()` | The active cell, e.g. `"B2"` |61| `select(ref, active=None)` | Select a cell or range, on another sheet if it names one; `active` puts the active cell elsewhere in it |62| `move(cols=0, rows=0)` | Move the active cell, dropping the selection |63| `extend(cols=0, rows=0, whole=None)` | Select from the active cell to `cols`, `rows` away; `whole="columns"` or `"rows"` selects whole ones |64| `jump(to, extend=False)` | Move as keys do: `"up"`, `"down"`, `"left"`, `"right"` (Ctrl+arrows, to the edge of the data), `"home"` (column A), `"start"` (A1), `"end"` (the last used cell) |65| `enter(text, fill=False, origin=None)` | Type into the active cell and accept, as Enter does without moving; `fill=True` fills every selected cell (Ctrl+Enter). With `origin`, a formula is taken as typed at that cell and its relative references move with the distance to the active cell. `text` is in en-US's form (`1.5`, `9/26/2026`, `=ROUND(A1, 2)`) whatever the file's [locale](../sheets/locale.md), as a recording writes it, so a macro does the same everywhere |66| `paste_text(text)` | Paste tab-separated text from the active cell, as pasting from another program: read in the file's [locale](../sheets/locale.md) |67| `fill(to=None, rows=0, cols=0)` | Drag the fill handle: continue the selection's series or copy it over `to`, or `rows` down (up if negative), or `cols` right (left if negative) |6869## Sheets7071| Function | Does |72|---|---|73| `sheets()` | The sheet names, in tab order, hidden ones included |74| `active_sheet()` | The name of the sheet shown |75| `activate_sheet(name)` | Show a sheet (not a hidden one: `run("sheet.unhide", answer=name)` shows it again) |76| `add_sheet(name=None)` | Add a sheet after the one shown and show it; returns its name |77| `move_sheet(position)` | Move the sheet shown to a position, counting from 1 |78| `set_width(cols, width)` | Set column widths, 1 to 240: `set_width("B", 14)`, `set_width("B:D", 8)` |79| `set_height(rows, height)` | Set row heights in lines, 1 to 50, or 0 to fit their contents: `set_height("3", 2)`, `set_height("3:5", 0)` |8081## Commands8283`run(id, answer=None)` runs any command from the menus or palette by its84id, on the selection, as if picked. A command that asks something (a85width, a name, a confirmation) needs `answer`: the text you'd type, a range86for a range question, the key of a choice (`"enter"`, `"d"`) or its87label, or the item picked from a list (View > Hidden sheets takes the88sheet's name). A command that opens a dialog takes what you'd choose in89it as a dict (see [Dialogs](#dialogs)). Other pickers (Named ranges, Tables) can't90run in a script; nor can files, menus, help, undo or macros. The ids are91those in the recorded scripts; a few:9293| Id | Command |94|---|---|95| `format.bold`, `format.italic`, `format.underline`, `format.strikethrough` | Text styles (toggles, as the keys) |96| `format.currency`, `format.percent`, `format.date`, `format.decimals_more`, ... | Number formats |97| `format.align_left`, `format.align_center`, `format.align_right`, `format.valign_top`, `format.valign_middle`, `format.valign_bottom`, `format.clear` | Alignment, clear formatting |98| `format.borders_all`, `format.borders_outer`, `format.border_top`, ..., `format.border_thick`, `format.border_color` (answer: the color, `"Red"` or `"Automatic"`) | Borders, their line and color |99| `edit.copy`, `edit.cut`, `edit.paste`, `edit.paste_values` | Clipboard (within 012; copying also sets the system clipboard) |100| `edit.fill_down`, `edit.fill_right`, `clear` | Fill, clear the selection |101| `insert.row_above`, `insert.row_below`, `insert.col_left`, `insert.col_right`, `delete.row`, `delete.col` | Rows and columns |102| `column.width` (answer: the width), `column.reset` | Column widths |103| `data.sort_sheet_az`, `data.sort_range_az`, `data.sort_range_za`, `data.filter`, `data.filter_remove` | Sorting and filters |104| `data.define_name` (answer: the name) | Name the selection |105| `table.create` (answer: the name), `table.rename` (answer: the name), `table.resize` (answer: the range), `table.banded`, `table.header`, `table.remove` | [Tables](../sheets/tables.md): make the selection one, or change the one the active cell is in |106| `format.conditional_add`, `data.validation_add` (answer: the rule as a line of the file, see [The .012 format](../files/format.md#conditional-formats-and-data-validation)), `insert.checkbox`, `format.conditional_clear`, `data.validation_clear`, `data.checkbox_toggle` | Conditional formats, data validation and checkboxes |107| `format.conditional_set`, `data.validation_set` (answer: `{"rule": 2, ...}`, the rule's number and its new line), `format.conditional_remove`, `data.validation_remove` (answer: the rule's number), `format.conditional_move` (answer: `{"rule": 2, "to": 1}`) | Change the rules of the sheet shown, numbered from 1 as the panel lists them |108| `sheet.new`, `sheet.duplicate`, `sheet.rename` (answer: the name), `sheet.delete` (answer: `"enter"` when it asks), `sheet.hide`, `sheet.unhide` (answer: the sheet's name) | Sheets |109| `view.freeze_rows1`, `view.freeze_cols1`, `view.freeze_rows0`, ... | Frozen panes |110111Record a macro that uses a command to see its id.112113## Dialogs114115A command that opens a dialog runs in a script with `answer` set to what116you'd choose in it, as a dict: the dialog doesn't open, and what it would117do is done. A recording writes dialogs this way, so recording one is the118quickest way to get its answer right. Fields left out keep what the119dialog would start with.120121```python122run("data.sort_range", answer={"by": [{"column": "B", "order": "desc"}, {"column": "A"}], "header": True})123run("data.filter_column", answer={"column": "A", "hidden": ["North"]})124run("chart.edit", answer={"chart": 1, "at": "F3", "width": 30})125```126127| Id | Answer |128|---|---|129| `data.sort_range` | `by`: the columns to sort by, in order, each `{"column": "B"}`, with `"order": "desc"` for Z to A; `header`: whether the range's first row is a header that stays in place (left out: as the bar guesses). The range is the selection, or the data around the active cell |130| `data.filter_column` | `column`: the column of the filter (left out: the active one); `hidden`: the values it hides; `condition` and `value`: the condition rows must meet, as a filter column is written in [the .012 format](../files/format.md) (`gt`, `contains`, ...), the value in en-US's form |131| `edit.replace` | `find` and `replace`; `matchCase`, `wholeCell`, `regex`, `inFormulas` set to `True` to turn them on; `within`: `"all"` for every sheet or a range of the sheet shown (left out: the sheet shown); `cell`: replace only the match in that cell (left out: replace all) |132| `insert.chart` | The chart as a line of the file: `type`, `data`, `at`, `width`, `height`, `header`, `labels`, `title` and the options, see [The .012 format](../files/format.md); what's left out is the chart Insert > Chart makes of the selection |133| `chart.edit` | `chart`: the chart's number on the sheet shown, counting from 1 (left out: the chart the command would edit), and the fields of its line to change, as for `insert.chart`; `None` puts a field back to its default |134| `data.pivot`, `data.pivot_edit` | The pivot table as a line of the file: `source`, `rows`, `columns`, `values`, `filters`, `rowTotals`, `columnTotals`, see [The .012 format](../files/format.md). `data.pivot` makes it on a new sheet, as Data > Pivot table does; `data.pivot_edit` changes the pivot of the sheet shown |135136A dict's keys are strings, and its values `None`, `True`, `False`,137numbers, strings, lists and dicts; `run` hands it on as JSON with the138keys in the order written.139140## Output and errors141142`print(...)` shows its last line on the context line when the macro ends.143A failure stops the macro and shows where it happened; there's no144`try`. Calling a function wrongly (`move(1, 2, 3)`) or on a reference145that doesn't exist fails the same way. Only what the functions above do is146possible: no `load`, no files, no time, no randomness, so a script does147the same thing every time it runs on the same sheet.148149## In the file150151Macros are saved in the `.012` file, one line each, after the named152ranges:153154```json155"macroOrigin": "3f9c0b1e5a7d42c8b61f0e9a2d4c7b18",156"macros": [157 {"name": "Totals", "key": "1", "api": 1, "source": "# Recorded with relative references...\nenter(\"Total\")\n"}158],159```160161`api` is the version of the functions above the script was written for.162The file's version doesn't change: older builds of 012 open files with163macros and ignore them (and drop them if they save).