CODEWITHSUNDEEP
google-sheets
Noam
Noam

Reputation: 983

Storing a google spreadsheet array in one cell

Is it possible to store a google spreadsheet array in one cell, and then to refer to it?

I have a table, from which I have used FILTER to gather an array of relevant lines. I need to do several things with the filtered array: count the number of elements and sum the elements. Currently I have to repeat the FILTER function several times, because I can't host its output in one cell. It would be much nicer to store the FILTER output in a cell, and then have another cell with, say, "=SUM(B1)", and "=COUNT(B1)".

I have found that NOEXPAND disables adding CONTINUE cells, but then if I write "=COUNT(B1)" the result is 1 - I'm counting only the first item of the array.

Thanks!

edit: For some reason I couldn't answer my own question, but I found out that you can write your own functions in javascript. You can even serialize an array with JSON, and keep the result as a string. So, as a basic example, you can write:

function serialize(a) {
  return JSON.stringify(a);
}

function deserialize(a) {
  return JSON.parse(a);
}

Upvotes: 8

Views: 16771

Answers (1)

00500005
00500005

Reputation: 4057

You can store an array as text in a single cell using JOIN.

It's less than ideal, as you still have to unmarshal it every time using SPLIT, but it's useful for storing complex filters.

For instance:

A1: =TRANSPOSE({1,2,3,4,5,6,7,8,9,10})
B1: =TRANSPOSE({3,1,1,2,1,1,1,1,1,0})
C1: =JOIN(",",FILTER(A1:A10, B1:B10 > 0, B1:B10 < 3))

COUNT(SPLIT(C1,",")) == 8

compared to

COUNT(FILTER(A1:A10, B1:B10 > 0, B1:B10 < 3)) == 8

Upvotes: 6

Related Questions