Google Sheets formula to add column to array?

Question

In Google Sheets I have a 2D array, with unpredictable size, returned by a Query:

08-02-2020 | Red
10-02-2020 | Brown
12-02-2020 | Green

Now I'd like to add a third column containing the constant "Golf" to the array. For instance:

08-02-2020 | Red   | Golf
10-02-2020 | Brown | Golf
12-02-2020 | Green | Golf

Is there a way to add a column to the initial array? Could not tame this with arrayformula...

current query:

=query(transpose(query(transpose(query(transpose(query(A1:11)),"select * where Col1 > 0")),"select * where Col1 <> ''",1)),"select * where Col2 > 0")

interesting, I did something similair many moons ago with a dynamic `Total` row using [this guide](https://www.benlcollins.com/spreadsheets/query-total-row/) I'm curious to see what magic player0 comes up with without appScript — Umar.H, Jan 20 '20 at 17:47

score 2 · Accepted Answer · answered Jan 20 '20 at 17:54

2

try:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(QUERY(A1:11)), 
 "where Col1 > 0")), 
 "where Col1 <> ''", 1)), 
 "select "&TEXTJOIN(",", 1, "Col"&TRANSPOSE(ROW(INDIRECT("A1:A"&COLUMNS(
 QUERY(TRANSPOSE(QUERY(TRANSPOSE(QUERY(TRANSPOSE(QUERY(A1:11)), 
 "where Col1 > 0")), "where Col1 <> ''", 1)), 
 "where Col2 > 0"))))), "'Golf'")&" where Col2 > 0"))

answered Jan 20 '20 at 17:54

player0

124,011
12
67
124

1

It took me a little to understand the edits.... What can I say? You're genius! (Sent an email to your address). Many thanks – Riccardo Jan 20 '20 at 18:04

Google Sheets formula to add column to array?

1 Answers1