arrayThis

Copy Sheets formulas across ranges with ease.

ongoing since google sheets tagJavaScript tag Google Workspace Marketplace

Description

Copies the formula and (optional) format of the top left-most cell across a range, respecting absolute and relative cell references. This is useful for applying functions across arrays that cannot normally be applied with ARRAYFORMULA(), such as RAND(), INDEX() & GOOGLEFINANCE().

For reference, Google’s list of functions can be found here.

How To Use

To get started, highlight the range you want to apply the formula across. Remember that the formula will be taken from the top-left cell and that absolute and relative cell references are maintained. Go to ‘Add-ons > arrayThis > Active Range​’ and you will see the formula copied across the range.

If you wish to apply arrayThis across several ranges at once, then you do not need to select them. Go to ‘Add-ons > arrayThis > Specify Range(s)​’ and enter the ranges separating by commas.

Output

Examples

A1: =RAND() Select column A. Go to ‘Add-ons > arrayThis > Active Range’. The output will be ‘=RAND()’ in each cell, producing different random numbers, instead of the same random number that would be produced by ARRAYFORMULA().

A1: =“http://www.example.com/?n=“&RAND()

​B1: =IMPORTHTML(A1,“table”,1)

Incorrect: Go to ‘Add-ons > arrayThis > Specify Range(s)’ and enter “A:B” in the prompt. This will make every cell in A:B equal to “http://www.example.com/?n=“&RAND().

Correct: Go to ‘Add-ons > arrayThis > Specify Range(s)’ and enter “A:A,B:B” in the prompt. This will make every cell in column A ’=“http://www.example.com/?=“&RAND()’ and every cell in column B ‘=IMPORTHTML(A1,“table”,1)‘.

Permissions

Here’s what arrayThis does with each of the permissions it requires:

Further details can be found in the GSuite Add-on Privacy Policy.

Uh-oh, Something Broke!

Contact me, giving as much detail as you can.

Changelog​