Formulas Collection

precision

Generates UID

ids,report_id,page_id,params

Get the list of links for Looker Studio for individual reports

timestamps

Get human-like definition of how much time passed since given datetime

range,row_offset

Create columns offsets representation of range, including sheet name reference

texts

Takes text out of double quotes

data

Creates group by column 1 of text in column 2. The result is pivot table made of text.

range

Creates cell-formula map of selected range

dates

Converts dates to month short name in english

range,only_initials,text_color,background_color,font,width,height

Generates images from the text strings contained in the provided range. Use ";" to skip optional parameters and set them to their default values.

keys,values

Creates a flat JSON string based 2 on columns: keys and values. Useful to transfer data from Sheets to Script.

values,chars_to_trim

Replace chars of a list to space and trim the result

rowdata,columndata,firstdatacell

Unpivots "wide" table

data,regular_axpressions,values_return

Search the first match using regular expressions

data

The formula aims to precisely rectify poorly formatted phone numbers.

range

Extract URLs from range with smart chips, or URLs

range

Get the column address by cell or range

header,data,delimiter

Join values in table with the same header

range

get the A1-Address of datarange. The function will crop empty rows below the range

range,conditions

check array values are in sub-array

data

Index of each group in sorted range

range

Determine data type of a value

range

Convert Weekly Schedule to labels

text,regex_to_extract

Extract all text parts from text. Extraction goes by RegEx pattern

data

Counts the number of current entry in the array. Works as ArrayFormula

data

Creates group by column 1 of text in column 2. The result is pivot table made of text.

how_many,start_from

Create sequence of individual sequences, started from individual numbers

a,b

Joins 2 ranges A and B by keys in the first columns of 2 ranges. All keys of range A must be present in B

data

Deletes repeating values by columns

text,regex,func

Does your Lambda with text if text mathces RegEx

urls

ImportHtml ArrayFormula

func

get `func` execution time in seconds

data

Cumulative count by groups

data,sort_column,unique_column

find max values by groups and delete duplicates

data,sort_column,unique_column

find min values by groups and delete duplicates

data,delimiter

Get last part of text after delimiter

data

Pack data into 1 cell with delimiters

packed

Unpacks packed data back into array. This function is used in pair with PACK functions. Use it to operate complex calculations.

data,row_offset,column_offset,height,width

Like Normal OFFSET, but for ARRAYS

date_,type,date_format,special_date_formats

Create calendar of month by date with custom dates formats

dates,days,weekends

Finds the date after or before the given date will give us N workdays

date_,type

Create a month Calendar

how_many,n

Create sequence of repeated indexes N times each

data,num_spaces

Adds N empty rows below each row of data

colors,dates_from,dates_to,total_periods

Create Gantt chart colored bars

scale

get country flags

items,quantities,prices

Get FIFO sell prices

items,quantities,prices

Get LIFO sell prices

get 90 AI generated face images

data,column_indexes

generate array of selected column indexes

data,data_headers,columns_return

Extract columns from data by column names

sheet_names,header_row

Find common headers from multiple sheets

sheet_names,row_num,col_num

gets data range addresses from multiple sheets from row R and starting from column C up to the end of sheets

sheet_names,header_row

Combines data from multiple sheets by common headers

search_keys,data

Match multiple items

data,rows

Generate array by cells row indexes of same columns

data

fills down values if blank from the top filled cells in each column

sheet_names,range_a1

combine data from multiple sheets

numbers

multiple counters from 1 to N each

elements,n

Creates sequence from 1 to n repeatedly

numbers

sequence of repeated numbers of N elements each: 1,1,1,2,2,2,2,3,3,4,...

data,indexes

Generates an array of given row indexes

data

calculates cumulative sum over range

data_column,num_cols

Splits single column of data to separate columns

data,row_start,col_start,num_rows,num_cols

creates a submatrix of data with given dimentions

data

Find the number last non empty INDEX in data

data

filter out blank rows

data

cut blank rows in the end of array

how_many,mean,deviation

Creates a fake normal distibuted data on how_many elements

how_many,dates_approx

Get the range of normally distributed dates

how_many,show_headers

Get random data, looking like normal data: * date range distributed normally * emails and colors = 20 random persons * random true/false * "real" products + prices * growing quantities

how_many,max_num

Create random list of numbers, that are distributed by Quadratic Equation

how_many,data

Selects how_many random elements from data.

how_many,data,distribution

Pick random how_many random elements from list. The selection is are distributed.

how_many

Creates sequence of numbers made with emojis

text

Split text by chars

text,delimiter,value_pre,value_after

append and/or prepend text to parts of delimited texts

list,delimiter

Number of parts in elements of delimited list

data,col

Puts 1 in place of a group start. Select data and col with group names.

data,col

Create counter of items of group. Select data and col with group names.

data,col

Adds space between each group. Select data and col with group names.

items,operations,quantities,buy_key

Use to dynamically get the remaining inventory balance of each item

num_rows,num_cols,value

Fill number of rows and cols with value

list0,list1_re,list2_with

Regex Replace each element in list1 with the element of list2 in each text of list0

list0,list1,list3_with

Substitute each element in list1 with the element of list2 in each text of list0

list0,list1,list2,delimiter

Regex Replace values from delimited list1 with delimited list2 in texts from list0

list0,list1,list2,delimiter

Substitutes values from delimited list1 with delimited list2 in texts from list0

how_many

Generate how_many randomly generated emais.

how_many

Generate how_many random true/false values

data

Convert HEX code to colors

how_many

Generate how_many random valid colors.

how_many

Generate how_many random valid color codes.

list1,list2

Multiply 2 lists. Join 2 lists: list1 & list2. This operation is called Cartesian product: set of all ordered pairs (a, b) where a is in A and b is in B.

data,querytext

Enriches functionality of QUERY. Use shortcut in group by clause: =QUERY_(C2:E,"select Col1, Col2, sum(Col3) where Col3 > 0 group by 1,2")

sources,ranges

ImportRange ArrayFormula. Has 2 parameters: sources, ranges.

elements,from,to,mode

Create a list of random integers on N elements, each of them is between from & to bounds. Select mode=1 to prevent random array from recalculation.

text,delimiter

Split text as "01-02-03" by delimiter "-" and get the textual output without auto-convrsion of the result to number. Usual split will produce numners: 1,2,3. SplitAsText will give texts: "01", "02", "03"

cell1,cell2

Give it cell1 A1 and cell2 D4 to get the string range notation: "A1:D4"

a1cell

Extract sheet name from a a1cell, placed in another sheet of the current file.