Google-sheets – Use array as function parameters

formulasgoogle sheetsgoogle-sheets-arrays

Given a function that returns an array, is it possible to "spread" the return values as the arguments to another function?

For example, I'd like to pass the result of SPLIT("example.com,My Site", ",") to the HYPERLINK(url, [link_label]) function and have it treat "My Site" as the link_label parameter.

I've tried

=HYPERLINK(SPLIT("example.com,My Site", ","))

but it ignores the second argument.

(This is a simplified example. In reality, the function that's returning the URL and label is a QUERY which I'd like to avoid repeating in the second argument.)

Best Answer

Your split string is SPLIT("example.com,My Site", ","). The url is the first element, and the label is the second element.

Use Index to retrieve the separate elements of the split value. The paramaters returns the content of a cell, specified by row and column offset.

First element: =index(SPLIT("example.com,My Site", ","), 0, 1) (row zero, column 1)
Second element: =index(SPLIT("example.com,My Site", ","), 0, 2) (row zero, column 2)

Combine them in the hyperlink:

=HYPERLINK(INDEX(SPLIT("example.com,My Site", ","), 0, 1), INDEX(SPLIT("example.com,My Site", ","), 0, 2))

Related Solutions

Google Sheets – Non-Deterministic Parameters to Custom Function

I've made some research and it seems like an old problem, even from old MS Excel versions.

I think the best/easiest way is to create your own randomization function. This way random value won't be regenerated. You use it the same way as before =plusOne(myRandom())

function plusOne(i) {
  return i + 1;
}

function myRandom() {
  # Some function
  return Math.random();
}

You could also shorten it =myRandom()

function myRandom() {
  # Some function
  return Math.random() + 1;
}

Google Sheets – Function to Copy Array

Short answer

Instead of =TRANSPOSE(TRANSPOSE(A1:A30)) consider to use ={A1:A30} as it's shorter.

Long answer

An alternative to ARRAYFORMULA is the use of embedded arrays also named

inline arrays
literal arrays
array parameters
"your own arrays"

Example of embedded array of constants

={"A","B";1,2}

={A1:B2;D1:E2;G1:H2}

Example of embedded array of non-adjacent ranges.

It's worth to say that for spreadsheets with regional settings that use:

. as the decimal separator should use , as the column delimiter
, as the decimal separator should use \ as the column delimiter

In both cases use ; as the row delimiter.

Reference

Using arrays in Google Sheets - Docs editors Help