Is there a way to insert blank columns in output with google sheets query?

Question

Consider the query run from sheet2

=query(Sheet!A7:A, "Select A,B,C where A='Order'")

but I want to put this in columns A,E,F in Sheet2.

I've found the workaround

=query(Sheet!A7:A, "Select A,X,Y,Z,B,C where A='Order'")

Where XYZ are blank columns from the end of the range. This seems clunky.

So far searches in both the query syntax on Google docs, and google product forums have been unproductive.

Answer 1

I tried a version of ttarchala's response, just giving each blank column I wanted a different "signature", if you will:

=query(Sheet!A7:C, "Select A,' ',' ',' ',B,C where A='Order'")

Note the 1, 2, and 3 spaces in the quotes.

This worked - sort of. It gave me column A, then three blank columns, then B and C. However, it gave me a header row (which in my example I didn't want), and in the blank column headers I got:

| " "() | " "() | " "() |

Which was odd, but understandable. I hid the formula one row up and merged cells across A through C. Which left me with a nice blank cell. I took advantage of the fact that the output from query() can't be shown across merged cells.

This is of course a very specific solution - I had an empty row above my output that I could use and abuse for this purpose. But I thought I would mention anyway, just in case it gives a direction for better ideas.

UPDATE

Adding:

... LABEL ' ' '', ' ' '', ' ' ''

.. to the end of the query zeros out those odd headers that are added and removes the need for that extra row. This tells the query that the label for ' ' (one space) should be nothing, ' ' (two spaces) nothing, etc. This results in no header row.

Answer 2

If truly empty columns are needed then it is necessary to insert null values rather than spaces into those blank columns. This could be very important when creating data for a CSV file to import other systems, for example.

Instead of querying the data cells directly, curly brackets can be used to build a data set from the cells and then query on that. Let's build it up in steps.

You have two ranges that you want to insert three blank columns between. Those ranges can be written like this.

={Sheet!A7:A, Sheet!B7:C}

You can't just insert "" between those ranges because that would only be one row of data and the number of rows must match the number of rows in your source data.

A little trick with the LEFT function can be used to make a blank cell for each row. The LEFT function can take 0 for the number of characters to return, which will return an empty string no matter what data it is given. Any range from the source data can be used. I'll use A7:A. When the whole thing is wrapped in ARRAYFORMULA it will be evaluated for every row. That can be repeated for each blank column needed. The data set with three empty columns looks like this.

=ARRAYFORMULA({Sheet!A7:A, LEFT(Sheet!A7:A, 0), LEFT(Sheet!A7:A, 0), LEFT(Sheet!A7:A, 0), Sheet!B7:C})

There are some ways this could be shortened. One way is to make another data set inside a single LEFT function. The function can deal with arrays and will return multiple columns of empty strings. This is only a little bit shorter.

=ARRAYFORMULA({Sheet!A7:A, LEFT({Sheet!A7:A, Sheet!A7:A, Sheet!A7:A}, 0), Sheet!B7:C})

If a large number of blank columns are needed then adding some character to each cell of the range, repeating it, then splitting it into columns on that character could be shorter. Changing the number of blank columns is as simple as changing the number of repeats. It does depend on choosing a character that would not be in the data, though, or it will break. Here's an an example with nine blank columns, which is no longer than with fewer blank columns.

=ARRAYFORMULA({Sheet!A7:A, LEFT(SPLIT(REPT(Sheet!A7:A&"~",9),"~"), 0), Sheet!B7:C})

Since there are three columns of source data and three blank columns are needed, it can be shortened the most by referencing a larger range in the source. Empty strings will still be output for each column. Although this version is much shorter it depends on having source data with enough columns.

=ARRAYFORMULA({Sheet!A7:A, LEFT(Sheet!A7:C, 0), Sheet!B7:C})

Now query that data set. Instead of referencing data by cell references, they are referenced in order as Col1, Col2, etc. Here's the whole thing together using the shortest version of referencing the data.

=QUERY(ARRAYFORMULA({Sheet!A7:A, LEFT(Sheet!A7:C, 0), Sheet!B7:C}), "Select * where Col1='Order'", 0)

Answer 3

Put the empty columns as literals in the query - cannot really be empty though, must contain a space like this

=query(Sheet!A7:C, "Select A,' ',' ',' ',B,C where A='Order'")

Answer 4

If you need default values such as 0 in my case, you can do:

=query(Sheet!A7:C, "Select A,0,1-1,2-2,B,C where A='Order'")

Answer 5

While the answer I checked is indeed the best answer to my original question, I had a problem with it:

It required that the columns skipped be blank. There was no ready way to use a single query to populate intermittent columns. If I put anything in them, the query would break, and refuse to overwrite that cell.

Add to that query's other weaknesses:

• convoluted syntax.
• different syntax between referencing internal and external spreadsheets
• Inability to use range names within the select statement
• brittle about changes in the source structure -- add a column and the query does NOT adjust.
• being dependent on any of the source fields made for frequent calculation of the entire result.

I ended up converting my query to 4 filter statements using the same criteria.

• For each column I wanted results from, I defined a named range.
• for each filter I defined my criteria and source in terms of named ranges.

E.g.

=filter(COcode,COcount > 0,isNumber(COcount))
=filter(COcount,COcount > 0,isNumber(COcount))

The double criteria on each filter is due to sheets having the idea that "foo" is > than 0. Not something I find intuitively obvious.