Select subrange of an array-returning formula like GOOGLEFINANCE()

Question

In Google Sheets, GOOGLEFINANCE returns an array of values, like so:

The formula is entered in A1 but the result extends into other rows and columns. I would like to "select" (not sure what the exact term is) a subset of this data. For example, I would like to select only the prices, without header (so B2:B22 in the image above). And I would like to do that in one formula, without ever printing the full table.

I tried OFFSET, but it doesn't work since it doesn't recognize the GOOGLEFINANCE formula as a range. Conversely, INDEX works, but only returns one cell at a time, so

 INDEX(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"),2,2)

returns "345.66" for example.

Answer 1

You could use INDEX without a row parameter to return a column

For example,

=INDEX(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"),,2)

will return the second column fully. However, To fully manipulate the array, you can use QUERY:

 =QUERY(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"), "Select Col1,Col2 where Col2>355 limit 6 offset 1",0)

Here I've made the following conditions:

• 🄿🅁🄸🄲🄴 more than 『355』(Remove where Col2>355 if you don't need it )
• Total 🄻🄸🄼🄸🅃 of rows =5
• 🄾🄵🄵🅂🄴🅃 the first row

Answer 2

Rather than invoking the sledgehammer that is the QUERY function, you can slice an array using this named function:

SLICE(array, offset_rows, offset_cols, num_rows, num_cols)

Returns a specified slice of the given array.

• array: The input array.
• offset_rows: The number of rows from the top of the input array to omit from the returned slice. If negative, the first row of the returned slice is taken from the row of the input array that is this many rows upward from its bottom.
• offset_cols: The number of columns from the left of the input array to omit from the returned slice. If negative, the first column of the returned slice is taken from the column of the input array that is this many columns to the left of its right side.
• num_rows: The number of rows to return. If zero or negative, the magnitude specifies how many rows from the bottom of the input array to omit from the returned slice.
• num_cols: The number of columns to return. If zero or negative, the magnitude specifies how many columns from the right of the input array to omit from the returned slice.

=LET(
  offset_rows, IF(offset_rows<0, ROWS(array)+offset_rows, offset_rows),
  offset_cols, IF(offset_cols<0, COLUMNS(array)+offset_cols, offset_cols),
  MAKEARRAY(
    IF(num_rows>0, num_rows, ROWS(array)+num_rows-offset_rows),
    IF(num_cols>0, num_cols, COLUMNS(array)+num_cols-offset_cols),
    LAMBDA(row, col, INDEX(array, offset_rows+row, offset_cols+col))
  )
)

Examples

Letting array be {1,2,3; 4,5,6; 7,8,9}, then:

• SLICE(array,0,0,0,0) ⇒ {1,2,3; 4,5,6; 7,8,9} (identity)
• SLICE(array,1,0,0,0) ⇒ {4,5,6; 7,8,9} (drop first row)
• SLICE(array,0,1,0,0) ⇒ {2,3; 5,6; 8,9} (drop first column)
• SLICE(array,0,0,1,0) ⇒ {1,2,3} (take only first row)
• SLICE(array,0,0,0,1) ⇒ {1; 4; 7} (take only first column)
• SLICE(array,-1,0,0,0) ⇒ {7,8,9} (take only last row)
• SLICE(array,0,-1,0,0) ⇒ {3; 6; 9} (take only last column)
• SLICE(array,0,0,-1,0) ⇒ {1,2,3; 4,5,6} (drop last row)
• SLICE(array,0,0,0,-1) ⇒ {1,2; 4,5; 7,8} (drop last column)
• SLICE(array,1,1,0,0) ⇒ {5,6; 8,9} (drop first row and column)
• SLICE(array,0,0,-1,-1) ⇒ {1,2; 4,5} (drop last row and column)
• SLICE(array,1,1,-1,-1) ⇒ 5 (drop first and last row and column)

---

The OP's use case could be written as:

SLICE(GOOGLEFINANCE("TSLA","price",TODAY()-30,TODAY(),"DAILY"),1,1,0,0)

These parameters drop the first row and the first column from the array returned by GOOGLEFINANCE.