Python built-ins in Google Sheets

Some notes:

I use “vector” for a one-dimensional array, and “array” for the general case.
Custom functions don’t have optional arguments in the Python sense, but you can just leave an argument blank.
I’m gonna implicitly convert between Google Sheets’s one-indexing to Python’s zero-indexing. Python functions take zero-indices, and Google Sheets functions take one-indices, unless otherwise stated. This means I’ll write list[i] as the Python equivalent of INDEX(list, i), even though the Google Sheets i is one larger than the Python i.
If I made a mistake, please tell me. If you have better solutions than mine, I’d love to hear them. Get in the comments!

Built-in functions

abs(). Use ABS.

all(). The native equivalent doesn’t short-circuit. To get short-circuiting behavior, use:

ALL(vector)
= REDUCE(TRUE, vector, LAMBDA(x, y, IF(x, y, FALSE)))

If you need to coerce to booleans, see bool().

any(). The native equivalent doesn’t short-circuit. To get short-circuiting behavior, use:

ANY(vector)
= REDUCE(FALSE, vector, LAMBDA(x, y, IF(x, TRUE, y)))

If you need to coerce to booleans, see bool().

bin(). Use DEC2BIN.

bool(). You can read the native coercion logic on sheets.wiki. To get Python-style coercion, use:

BOOL(val)
= SWITCH(TYPE(val),
  1, val <> 0,
  2, val <> "",
  4, val,
  64, AND(ROWS(val) > 0, COLUMNS(val) > 0),
  TRUE
)

chr(). Use CHAR.

complex(). Use COMPLEX.

dict(). See Dictionaries for information about dictionaries.

divmod(). You want FLOOR.MATH for proper floor division:

DIVMOD(a, b)
= LET(q, FLOOR.MATH(a / b), {q, a - q*b})

enumerate(). Assuming you have a column vector, this returns an array, which you can treat as a list of its rows:

ENUMERATE(vector)
= HSTACK(SEQUENCE(ROWS(vector)), vector)

eval(). You probably want to use INDIRECT instead.

filter(). The native equivalent FILTER doesn’t work with lambdas. Use:

FILTER_(vector, fn)
= FILTER(vector, MAP(vector, fn))

If you want to filter an array, treated as a list of its rows, use:

FILTER_(array, fn)
= FILTER(
  array,
  BYROW(array, LAMBDA(row, fn(TRANSPOSE(row))))
)

The TRANSPOSE is needed to flip the rows to columns, so we can treat them in the same way our other built-in equivalents do.

float(). Use TO_PURE_NUMBER or N. See Numeric types for information about floats.

hex(). Use DEC2HEX.

int(). Use INT, or DECIMAL if you need to convert from a base. See Numeric types for information about integers.

len(). Use ROWS or COLUMNS, depending on the shape. If you don’t know, you can FLATTEN.

list(). See Lists and tuples for information about lists.

map(). For vectors, use MAP. To treat an array as a list of its rows, use:

MAP_(array, fn)
= BYROW(array, LAMBDA(row, fn(TRANSPOSE(row))))

The TRANSPOSE is needed to flip the rows to columns, so we can treat them in the same way our other built-in equivalents do.

max(). Use MAX. You can use a key function, on an array treated as a list of its rows, with:

MAX_(array, key)
= LET(
  mapped,
    BYROW(array, LAMBDA(row, key(TRANSPOSE(row)))),
  i,
    MATCH(MAX(mapped), mapped, 0),
  INDEX(array, i)
)

The TRANSPOSE is needed to flip the rows to columns, so we can treat them in the same way our other built-in equivalents do.

min(). Use MIN. You can use a key function, on an array treated as a list of its rows, with:

MIN_(array, key)
= LET(
  mapped,
    BYROW(array, LAMBDA(row, key(TRANSPOSE(row)))),
  i,
    MATCH(MIN(mapped), mapped, 0),
  INDEX(array, i)
)

The TRANSPOSE is needed to flip the rows to columns, so we can treat them in the same way our other built-in equivalents do.

oct(). Use DEC2OCT.

ord(). Use CODE.

pow(). Use POW. For the modulo, use, as a named function:

POW_MOD(base_, exp_, modulo)
= IF(exp_ = 0, 1,
  LET(
    half,
      POW_MOD(base_, FLOOR(exp_ / 2), modulo),
    double,
      MOD(half * half, modulo),
    IF(ISEVEN(exp_), double,
      MOD(double * base_, modulo)
    )
  )
)

It needs to be a named function for the recursion to work.

range(). You might not need this: if you’re going to map immediately afterward, you can use MAKEARRAY. The native equivalent is SEQUENCE. If you want Python behavior and zero-indexing:

RANGE_(start_or_stop, stop, step)
= LET(
  start, IF(ISBLANK(stop), 0, start_or_stop),
  stop_, IF(ISBLANK(stop), start_or_stop, stop),
  step_, IF(ISBLANK(step), 1, step),
  len, FLOOR.MATH((stop_ - start) / step_),
  IF(len <= 0, TOCOL(, 1),
    SEQUENCE(len, 1, start, step_)
  )
)

reversed(). To reverse the rows of an array:

REVERSED(array)
= SORT(array, SEQUENCE(ROWS(array)), FALSE)

round(). Use ROUND.

set(). To deduplicate a vector, use UNIQUE. See Sets for information about sets.

slice(). To slice the rows of an array, with a given start and stop that are zero-indexed, use:

SLICE(array, start, stop, step)
= LET(
  step_, IF(ISBLANK(step), 1, step),
  coerce, LAMBDA(idx, default,
    IFS(
      ISBLANK(idx), default,
      idx < 0, ROWS(array) + idx,
      AND(step_ > 0, idx >= ROWS(array)), ROWS(array),
      AND(step_ < 0, idx >= ROWS(array) - 1), ROWS(array) - 1,
      TRUE, idx
    )
  ),
  start_, coerce(start, IF(step_ > 0, 0, ROWS(array) - 1)),
  stop_, coerce(stop, IF(step_ > 0, ROWS(array), -1)),
  len, FLOOR.MATH((stop_ - start_) / step_),
  IF(len <= 0, TOCOL(, COLUMNS(array))
    MAKEARRAY(len, COLUMNS(array), LAMBDA(i, j,
      INDEX(array, start_ + 1 + (i - 1) * step_, j)
    ))
  )
)

To slice columns, use with TRANSPOSE. Writing a performant version for slicing rows and columns simultaneously is an exercise left to the reader.

sorted(). See list.sort().

str(). Use TO_TEXT or T. See Strings for information about strings.

sum(). Use SUM for numbers and CONCATENATE for strings.

tuple(). See Lists and tuples for information about tuples.

type(). Use TYPE, which returns a number that you have to convert. If you want Python-style names:

TYPE_(val)
= SWITCH(TYPE(val),
  1, IF(val = INT(val), "int", "float"),
  2, "str",
  4, "bool",
  16, "Exception",
  64, "list",
)

zip(). To zip two column vectors to an array treated as a list of its rows, use HSTACK. It has exact behavior by default, placing error cells if the vectors are of different lengths.

To get shortest behavior, use:

ZIP(a, b)
= ARRAY_CONSTRAIN(HSTACK(a, b), MIN(ROWS(a), ROWS(b)), 2)

To get longest behavior, use IFERROR(HSTACK(a, b)).

Built-in types

Boolean operators

The built-ins AND and OR do not short-circuit, but IF does.

To get the Python behavior of AND and OR, where we return one of the arguments, short-circuit as needed, and do Python-style coercion to booleans, use:

AND_(x, y)
= LET(
  x_bool, SWITCH(TYPE(x),
    1, x <> 0,
    2, x <> "",
    4, x,
    64, AND(ROWS(x) > 0, COLUMNS(x) > 0),
    TRUE
  ),
  IF(x_bool, x, y)
)

and

OR_(x, y)
= LET(
  x_bool, SWITCH(TYPE(x),
    1, x <> 0,
    2, x <> "",
    4, x,
    64, AND(ROWS(x) > 0, COLUMNS(x) > 0),
    TRUE
  ),
  IF(x_bool, y, x)
)

Comparisons

The comparison operators <, <=, >, >= carry over.

x == y. Use x = y.

x != y. Use x <> y.

Numeric types

The arithmetic operators +, -, *, / carry over.

x // y. Use FLOOR.MATH(x / y).

x % y. Use MOD, or x - FLOOR.MATH(x / y) * y if you want more similar Python behavior. I don’t actually know what the difference is.

c.conjugate(). Use IMCONJUGATE.

x ** y. Use POW.

x | y. Use BITOR.

x ^ y. Use BITXOR.

x & y. Use BITAND.

x << n. Use BITLSHIFT.

x >> n. Use BITRSHIFT.

~x. Use -x - 1.

int.bit_length(). Use LEN(DEC2BIN(int)).

int.bit_count(). Use LEN(REGEXREPLACE(DEC2BIN(int), "0", "")).

float.is_integer(). Use INT(float) = float.

Lists and tuples

In general you can treat a single-column array as a list or a tuple. (Inputs are always immutable, so they’re kinda the same.)

val in list. Use NOT(ISNA(MATCH(val, list, 0))).

list + other. Use VSTACK(list, other).

list * n. Use:

LIST_TIMES(list, n)
= REDUCE(
  TOCOL(, COLUMNS(list)),
  SEQUENCE(n),
  LAMBDA(acc, _, VSTACK(acc, list))
)

list[i]. Use INDEX(list, i) if the index is guaranteed positive. Otherwise, see slice().

list[i:j], list[i:j:k]. See slice().

list.index(val). Use MATCH(val, list, 0).

list.count(val). Use COUNTIF(list, val).

list.sort(). Use SORT(list). You can use a key function, on an array treated as a list of its rows, with:

LIST_SORT(array, key)
= SORT(array, MAP(array, LAMBDA(row, key(TRANSPOSE(row)))))

The TRANSPOSE is needed to flip the rows to columns, so we can treat them in the same way our other built-in equivalents do.

Strings

Strings have so many built-in methods that I’m not going to bother. Just check the Google Sheets function list. Here’s the common sequence methods though:

sub in str. Use IFERROR(FIND(sub, str) <> 0, FALSE).

str + other. Use str & other.

str * n. Use REPT(str, n).

str[i]. Use MID(list, i, 1) if the index is positive. Otherwise, see str[i:j:k].

str[i:j]. Use MID(list, i, j - i) if indices are positive. Otherwise, see str[i:j:k].

str[i:j:k]. With zero-indexing:

STR_SLICE(str, start, stop, step)
= LET(
  step_, IF(ISBLANK(step), 1, step),
  coerce, LAMBDA(index, default,
    IFS(
      ISBLANK(index), default,
      index < 0, LEN(str) + index,
      AND(step_ > 0, index >= LEN(str)), LEN(str),
      AND(step_ < 0, index >= LEN(str) - 1), LEN(str) - 1,
      TRUE, index
    )
  ),
  start_, coerce(start, IF(step_ > 0, 0, LEN(str) - 1)),
  stop_, coerce(stop, IF(step_ > 0, LEN(str), -1)),
  len, FLOOR.MATH((stop_ - start_) / step_),
  IF(len <= 0, "", CONCATENATE(
    MAKEARRAY(len, 1, LAMBDA(i, _,
      MID(str, start_ + 1 + (i - 1) * step_, 1)
    ))
  ))
)

str.index(sub). Use FIND(sub, str).

str.count(sub). With zero-indexing:

STR_COUNT(str, sub, start, end)
= LET(
  start_, IF(ISBLANK(start), 0, start),
  end_, IF(ISBLANK(end), LEN(str), end),
  IF(end_ <= start_, 0,
  LET(
    sub_start, IFERROR(FIND(sub, str, start_ + 1) - 1, -1),
    IF(sub_start = -1, 0, LET(
      sub_end, sub_start + LEN(sub),
      1 + STR_COUNT(str, sub, sub_end, end_)
    ))
  ))
)

Sets

In general you can treat a single-column array without repeated elements as an (immutable) set.

val in set. Use NOT(ISNA(MATCH(val, set, 0))).

set.isdisjoint(other). Use:

SET_ISDISJOINT(set, other)
= ISNA(FILTER(set, MATCH(set, other, 0)))

set <= other. Use:

SET_ISSUBSET(set, other)
= IF(
  ROWS(set) <= ROWS(other),
  IFNA(
    ROWS(FILTER(set, MATCH(set, other, 0))) = ROWS(set),
    FALSE
  ),
  FALSE
)

For set < other, use the above with ROWS(set) < ROWS(other).

set | other. Use UNIQUE(VSTACK(set, other)).

set & other. Use:

SET_AND(set, other)
= FILTER(set, MATCH(set, other, 0))

set - other. Use:

SET_MINUS(set, other)
= FILTER(set, ISNA(MATCH(set, other, 0)))

set ^ other. Use:

SET_SYMMETRIC_DIFFERENCE(set, other)
= VSTACK(
  FILTER(set, ISNA(MATCH(set, other, 0))),
  FILTER(other, ISNA(MATCH(other, set, 0)))
)

Dictionaries

In general you can treat an array with key-value rows, and unique keys, as a dictionary.

dict[key]. Use VLOOKUP(key, dict, 2, FALSE).

key in dict. Use:

IN_DICT(key, dict)
= NOT(ISNA(VLOOKUP(key, dict, 2, FALSE)))

dict.get(key, default). Use:

DICT_GET(dict, key, default)
= IFNA(VLOOKUP(key, dict, 2, FALSE), default)

dict.keys(). Use INDEX(dict, 0, 1).

dict.values(). Use INDEX(dict, 0, 2).

dict | other. Use:

DICT_OR(dict, other)
= VSTACK(other,
  FILTER(dict,
    BYROW(dict, LAMBDA(row,
      ISNA(VLOOKUP(INDEX(row, 0, 1), other, 2, FALSE))
    ))
  )
)

Built-in functions

Built-in types

Boolean operators

Comparisons

Numeric types

Lists and tuples

Strings

Sets

Dictionaries

Comments