Help Center · Reference

76 builtins, and a sandbox that stops.

A script column computes each row’s value from a few lines of code, for the cases a formula cannot express. Add one from Insert → Script and write it in the column editor with a live preview. Scripts run with no network access, no DOM and no eval, under a step budget — a runaway loop ends rather than hangs.

The language.

Return a value

return cell("Price") * cell("Qty");

A script column's value is whatever you return. Every script should end by returning something.

Read other columns

let name = cell("First Name") + " " + cell("Last Name");
return name;

cell("Column Name") reads another column in the same row. Names are case-insensitive.

Variables

let subtotal = 100;
let tax = subtotal * 0.07;
return subtotal + tax;

Declare a variable with let, then reassign it with =.

Conditionals

let score = cell("Score");
if (score >= 90) { return "A"; }
else if (score >= 80) { return "B"; }
return "C";

if / else if / else branch on a true/false condition.

Loops

let total = 0;
for (let i = 1; i <= 10; i = i + 1) {
  total = total + i;
}
return total;

for and while repeat a block. A step limit stops runaway loops automatically.

Operators

return "Total: " & (cell("A") + cell("B")) ^ 2;

Arithmetic + - * / % and ^ (power), comparisons == != <> < > <= >= , and logic && || ! . Text joins with & (always) or + (when a side is already text). ^ is worked out before * / , which are worked out before + - , which come before & ; a comparison is last. -2 ^ 2 is 4, and 2 ^ 3 ^ 2 is 512. Text comparisons ignore capitals, so "Done" == "done" is true — the same rule sheets, forms and automations use. Note that = on its own means ASSIGNMENT here, not equality: write == to compare.

Comments

// Margin as a percentage
return (cell("Price") - cell("Cost")) / cell("Price") * 100;

Anything after // on a line is ignored.

Complete examples.

Whole scripts you can paste and run. Each one shows the small table it was run on and the exact result it gives there. A script column runs once per row and shows what it returns in that row. The script console (Tools → Script console… in a table) can also read the whole table, print lines, and propose changes you confirm before anything is saved. Scripts read text, number, date and checkbox columns. A dropdown column reads as the option you see in the cell, so cell("Status") == "Done" works; a multi-select reads as its options joined with a comma and a space.

Build a full name from two columnsScript column

Joins First Name and Last Name into one tidy name, fixing stray spaces and capitals, and still works when a last name is missing.

How to run it: In a table, open the Insert tab and press Script. Click any cell in the new column to open its script editor, replace the starter script with this one, and click away (or press Ctrl+Enter) to save. Every row then shows its own answer.

The script

// Tidy each part: TRIM removes spaces at the ends,
// PROPER capitalises the first letter of each word.
let first = PROPER(TRIM(cell("First Name")));
let last = PROPER(TRIM(cell("Last Name")));

// JOIN puts a space between the parts and skips any blank one,
// so a missing last name does not leave a trailing space.
return JOIN(" ", first, last);

Sample table: Contacts — with the script column added

First Name · text	Last Name · text	Full Name · script
ada	LOVELACE	Ada Lovelace
Grace	hopper	Grace Hopper
Linus		Linus

Flag overdue tasksScript column

Shows Done, Overdue, On time or No due date for each task by comparing its Due date with today.

The script

// Finished tasks are never overdue.
if (cell("Status") == "Done") {
  return "Done";
}

// Check for a blank date first: a date function cannot read a blank.
if (ISBLANK(cell("Due"))) {
  return "No due date";
}

// DAYSBETWEEN counts the days from today to the due date.
// Below zero means the due date has already passed.
let daysLeft = DAYSBETWEEN(TODAY(), cell("Due"));
if (daysLeft < 0) {
  return "Overdue";
}
return "On time";

Sample table: Tasks — with the script column added

Task · text	Status · text	Due · date	Hours · number	Overdue? · script
Write brief	Done	2020-03-02	3	Done
Book venue	In progress	2020-04-15	5	Overdue
Send invites		2099-06-01	2.5	On time
Order food				No due date

Total a columnScript console

Adds up the Hours column across the whole table and prints the total and the average.

How to run it: In a table, open Tools → Script console…, paste this over the starter script and press Run. It reads the row your cursor is on. Use your own table's name where this says "Tasks".

The script

// "Tasks" is the name of the table; "Hours" is the column to add up.
// Blank cells are skipped.
let total = sum("Tasks", "Hours");
let average = avg("Tasks", "Hours");

print("Total hours: " + total);
print("Average per task: " + ROUND(average, 1));
return total;

Sample table: Tasks

Task · text	Status · text	Due · date	Hours · number
Write brief	Done	2020-03-02	3
Book venue	In progress	2020-04-15	5
Send invites		2099-06-01	2.5
Order food

What the console shows

Total hours: 10.5
Average per task: 3.5
→ 10.5

Count rows by statusScript console

Walks every row of the table once and prints how many tasks are done and how many are not.

The script

let done = 0;
let notDone = 0;

// count("Tasks") is the number of rows. cellAt reads one cell
// from row number i — the first row is 0.
for (let i = 0; i < count("Tasks"); i = i + 1) {
  if (cellAt("Tasks", i, "Status") == "Done") {
    done = done + 1;
  } else {
    notDone = notDone + 1;
  }
}

print("Done: " + done);
print("Not done: " + notDone);
return done;

Sample table: Tasks

Task · text	Status · text	Due · date	Hours · number
Write brief	Done	2020-03-02	3
Book venue	In progress	2020-04-15	5
Send invites		2099-06-01	2.5
Order food

What the console shows

Done: 1
Not done: 3
→ 1

Fill in blank statusesScript console

Gives every task that has no status the status "To do", and leaves the others alone.

How to run it: In a table, open Tools → Script console… and paste this over the starter script. Set Run over to "All … rows in this view", then press Run. You see the proposed changes first; nothing is saved until you press Apply.

The script

// This runs once for each row.
// Only rows with a blank Status are changed.
if (ISBLANK(cell("Status"))) {
  setCell("Status", "To do");
}

Sample table: Tasks — proposed changes highlighted

Task · text	Status · text	Due · date	Hours · number
Write brief	Done	2020-03-02	3
Book venue	In progress	2020-04-15	5
Send invites	To do (was blank)	2099-06-01	2.5
Order food	To do (was blank)

What the console shows

Proposed changes — nothing has been saved
Ran over 4 rows. Would create 0, update 2.
[Apply 2 changes]  [Cancel]

Built-in functions.

Row

cell(name)

The value of another column in the current row. cell("Price")

ROWID()

This row's stable, immutable backend id — unchanged when rows are inserted, sorted, or moved. Useful for reference codes, relation keys, and audit links. return "TASK-" + ROWID();

Math

ROUND(number, [digits])

Round to a number of decimal places. ROUND(3.14159, 2)

ABS(number)

Absolute value. ABS(-5)

FLOOR(number)

Round down to a whole number. FLOOR(4.9)

CEIL(number)

Round up to a whole number. CEIL(4.1)

CEILING(number)

Round up to a whole number — identical to CEIL. This is the spelling tables, forms and automations use, so a formula moved from any of them works unchanged. CEILING(4.1)

SQRT(number)

Square root. SQRT(144)

POW(base, exp)

Raise to a power. POW(2, 8)

POWER(base, exp)

Raise to a power — identical to POW, and the spelling tables, forms and automations use. The `^` operator is the shorter way to write the same thing: 2 ^ 10 and POWER(2, 10) are both 1024 here, as they are in a sheet. (This entry used to say `^` was not part of this language, which was true until it was added.) POWER(2, 10)

MOD(a, b)

Remainder of a divided by b. MOD(10, 3)

MIN(a, b, …)

Smallest of the numbers. Note: inside the in-grid Script console a cross-sheet aggregate of the same name claims every call, so this numeric form returns nothing there — AVERAGE is never shadowed. MIN(4, 9, 2)

MAX(a, b, …)

Largest of the numbers. Note: inside the in-grid Script console a cross-sheet aggregate of the same name claims every call, so this numeric form returns nothing there — AVERAGE is never shadowed. MAX(4, 9, 2)

SUM(a, b, …)

Adds the numbers together. Note: inside the in-grid Script console a cross-sheet aggregate of the same name claims every call, so this numeric form returns nothing there — AVERAGE is never shadowed. SUM(10, 20, 5)

AVERAGE(a, b, …)

The mean of the numbers. AVERAGE(2, 4, 6)

AVG(a, b, …)

The mean of the numbers — identical to AVERAGE, and the spelling automations use. Note: inside the in-grid Script console a cross-sheet aggregate of the same name claims every call, so this numeric form returns nothing there — AVERAGE is never shadowed. AVG(2, 4, 6)

PRODUCT(a, b, …)

Multiplies the numbers together. PRODUCT(2, 3, 4)

COUNT(a, b, …)

How many of the arguments are numbers. Note: inside the in-grid Script console a cross-sheet aggregate of the same name claims every call, so this numeric form returns nothing there — AVERAGE is never shadowed. COUNT(1, "x", 3)

COUNTA(a, b, …)

How many of the arguments are not blank. COUNTA(1, "", "x")

NUMBER(value)

Convert a value to a number. NUMBER("42")

SIGN(number)

-1, 0, or 1 for the sign of a number. SIGN(-4)

TRUNC(number)

Drop the decimal part (toward zero). TRUNC(4.9)

YEAR(date)

The 4-digit year of a date. YEAR(cell("Due"))

MONTH(date)

The month (1–12) of a date. MONTH(cell("Due"))

DAY(date)

The day of month (1–31) of a date. DAY(cell("Due"))

WEEKDAY(date)

Day of week, 1 = Sunday … 7 = Saturday. WEEKDAY(cell("Due"))

DAYSBETWEEN(start, end)

Whole days from start to end (negative if end is earlier). DAYSBETWEEN(cell("Start"), TODAY())

DATEADD(date, days)

The date a number of days later (negative = earlier). DATEADD(cell("Start"), 30)

ADDMONTHS(date, months)

The date a number of months later, clamped to the month end. ADDMONTHS(cell("Start"), 12)

DATE(year, month, day)

Build a date (YYYY-MM-DD) from parts. DATE(2026, 7, 21)

TODAY()

Today's date as YYYY-MM-DD. TODAY()

ROUNDUP(number, [digits])

Round away from zero to the given decimals. ROUNDUP(2.1, 0)

ROUNDDOWN(number, [digits])

Round toward zero to the given decimals. ROUNDDOWN(2.9, 0)

LN(number)

Natural logarithm (base e). For a base-10 logarithm use LOG(x) or LOG10(x). LN(2.718)

LOG10(number)

Base-10 logarithm. Exact for powers of ten, where LOG(x, 10) can land a hair under. LOG10(1000)

EXP(number)

e raised to a power. EXP(1)

PI()

The constant π. PI()

LOG(number, [base])

Logarithm of a number, base 10 unless you give another base — the same meaning Excel and Google Sheets both give it. Refuses a number ≤ 0, and a base ≤ 0 or equal to 1, rather than returning an infinity or a NaN that would spread through the rest of the script. (It used to be a lower-case alias of PRINT, which made the logarithm unreachable; printing is now spelled PRINT only.) LOG(1000)

Text

LEN(text)

Number of characters. LEN("hello")

UPPER(text)

Upper case. UPPER("hi")

LOWER(text)

Lower case. LOWER("HI")

TRIM(text)

Remove surrounding spaces. TRIM(" hi ")

LEFT(text, n)

First n characters. LEFT("widget", 3)

RIGHT(text, n)

Last n characters. RIGHT("widget", 3)

MID(text, start, len)

Characters from a position. MID("widget", 2, 3)

CONCAT(a, b, …)

Join values into text. CONCAT("A", "-", 1)

TEXT(value)

Convert a value to text. TEXT(42)

SUBSTITUTE(text, find, replace)

Replace every occurrence of find with replace. SUBSTITUTE("a-b-c", "-", "/")

REPLACE(text, find, replace)

Replace the first occurrence of find. REPLACE("a-b-c", "-", "/")

INDEXOF(text, part)

1-based position of part in text (0 if not found). INDEXOF("widget", "dg")

PROPER(text)

Capitalize the first letter of each word. PROPER("john doe")

REPEAT(text, count)

Repeat text a number of times. REPEAT("=", 10)

MASK(text, [keep_last], [mask_char])

Hide all but the last few characters — an account number as ************1234. Keeps 4 by default. Asking to keep more than the value holds, or a negative count, never reveals it. MASK(Account)

PADSTART(text, length, [pad])

Pad the start of text to a length. PADSTART("7", 3, "0")

PADEND(text, length, [pad])

Pad the end of text to a length. PADEND("7", 3, "0")

REGEXMATCH(text, pattern)

True if the text matches the regular expression. REGEXMATCH(cell("Email"), "@")

REGEXEXTRACT(text, pattern)

The first match (or first capture group) of a regular expression. REGEXEXTRACT(cell("SKU"), "\\d+")

REGEXREPLACE(text, pattern, replacement)

Replace every regex match with the replacement. REGEXREPLACE(cell("Phone"), "\\D", "")

SPLITPART(text, delimiter, index)

The 1-based part of a delimited string (empty if out of range). SPLITPART(cell("Email"), "@", 2)

JOIN(delimiter, part1, …)

Join the non-empty parts with a delimiter (skips blanks). JOIN(" ", cell("First"), cell("Middle"), cell("Last"))

Logic

IF(condition, then, [else])

Returns `then` when the condition is true, otherwise `else` (blank if omitted). Only the chosen branch is evaluated. IF(cell("Qty") > 100, "bulk", "standard")

IFS(cond1, val1, cond2, val2, …)

Returns the value for the first true condition. IFS(cell("Score") >= 90, "A", cell("Score") >= 80, "B", true, "C")

SWITCH(value, case1, result1, …, [default])

Matches value against each case and returns its result; the last lone argument is the default. SWITCH(cell("Tier"), "gold", 0.2, "silver", 0.1, 0)

AND(a, b, …)

True only if every argument is true (short-circuits). AND(cell("Paid"), cell("Shipped"))

OR(a, b, …)

True if any argument is true (short-circuits). OR(cell("Urgent"), cell("Overdue"))

NOT(value)

Inverts a true/false value. NOT(ISBLANK(cell("Email")))

CONTAINS(text, part)

True if text contains part (case-insensitive). CONTAINS("widget", "idg")

ISBLANK(value)

True if the value is empty. ISBLANK(cell("Notes"))

ISNUMBER(value)

True if the value is a number (or a text value that reads as one). IF(ISNUMBER(cell("Qty")), cell("Qty"), 0)

ISTEXT(value)

True if the value is non-blank text that isn't a number. ISTEXT(cell("Code"))

STARTSWITH(text, part)

True if text starts with part (case-insensitive). STARTSWITH("widget", "wid")

ENDSWITH(text, part)

True if text ends with part (case-insensitive). ENDSWITH("widget", "get")

COALESCE(a, b, …)

The first value that isn't blank. COALESCE(cell("Nick"), cell("Name"))

ISEMAIL(value)

True if the value looks like a valid email address. ISEMAIL(cell("Email"))

ISURL(value)

True if the value looks like a valid http(s) URL. ISURL(cell("Website"))

Debug

PRINT(value)

Write a value to the Script console and return it unchanged — so it can be dropped into the middle of an expression to see what a step produced. Where no console is listening (an app expression, a script column) it simply returns its argument. return PRINT(cell("Price")) * 2;

For most calculations a formula is quicker — scripts are for multi-step logic. The formula reference is here →

Write one against real rows.

Start free and add a script column to a sheet.

Start free Talk to a person