Google sheets - QUERY, IMPORTRANGE and append text to results

1
votes

I have a google sheet (sheetA) that contains master data. I am importing this data into another google sheet (Sheet B) using "IMPORTRANGE" function along with "QUERY"

=SORT(QUERY(IMPORTRANGE("url for sheet A","Crown DB!A2:E"),"SELECT Col1 WHERE not(Col5='SS')"))

Suppose, following is an example output I get after runnning the above formula

item1
item2
item3
item4

I want to append text to these returned values so that I can obtain two new values for each returned value:

item1 - var1
item1 - var2
item2 - var1
item2 - var2
item3 - var1
item3 - var2
item4 - var1
item4 - var2

If it were a single variant, I can just append "- Var1" to the above formula:

SORT(QUERY(IMPORTRANGE("url for sheetA","Crown DB!A2:E"),"SELECT Col1 WHERE not(Col5='SS')")) & "- Var1"

How can I modify the formula to get append multiple variants (>=2) for each item returned using IMPORTRANGE? The number of variants for each item is the same.

1
google-sheets

1 Answers

2
votes

Simplest approach is by appending the values inside an array formula and flatten it. Then use SORT afterwards.

Formula:

=ARRAYFORMULA(SORT(FLATTEN(
QUERY({A2:E},"SELECT Col1 WHERE not(Col5='SS') and not(Col1='')")
 & {" - Val1", " - Val2"})))

Output (2 variants):

output1

Output (3 variants):

output2

Note:

  • Used {A2:E} to show the full formula easier in testing. Change {A2:E} into the IMPORTRANGE in your case.
  • not(Col1='') is important to skip rows with blank Col1's.
  • Appending an n-array with an m-array in the ARRAYFORMULA will result into n x m dimension of array. Using FLATTEN on that will combine them all in one column. Then we use SORT afterwards.

Final formula should be:

=ARRAYFORMULA(SORT(FLATTEN(QUERY(
IMPORTRANGE("url for sheetA","Crown DB!A2:E"),
"SELECT Col1 WHERE not(Col5='SS') and not(Col1='')"
) & {" - Val1", " - Val2"})))