How to query an array in Google Sheets?

Question

In Google Sheets i have in a cell an array like [27, https://www.example.com/page1.html, false, false, 1]

How can i access its single parts with a formula?

I know a way through =SPLIT(), like =split(split(A2,"["),",") - but i would very like, if its possible, to access each part directly (each array has always the same amount of parts in my data set).

Maybe something like =QUERY(query(A2,",",1)) - cell, divider, item number...? - Result is 27.

Answer 1

=INDEX(SPLIT(A2,"[,]"),1)

• SPLIT by each of these characters [,]
• INDEX into the resulting array

Answer 2

I would like to take the chance and propose a solution using Google Apps Script. Basically, you can create your own custom function to accomplish this task.

Please follow these steps:

1. Go to Tools => Script editor from your spreadsheet file:

2. Clear the default Code.gs file, copy and paste this function and save the changes:

   function indexArray(arr,pos) {
      return array=arr.slice(1,-1).split(",")[pos-1]
   }
   

3. You are now able to access the indexArray() function from within your spreadsheet file. It accepts two arguments, the desired cell that contains the array and the position of the element you would like to access, starting from 1:

   =indexArray(A2,2)
   

For example, this will give you the second element of your array which is: https://www.example.com/page1.html.

Check these instructions out if you need more information how custom functions work. They are pretty straightforward.