CODEWITHSUNDEEP
google-sheets
CyberAnt
CyberAnt

Reputation: 21

Add a column with a specific value when making google sheet "=QUERY"

I am sorting an array made up of 2 queries. I want to add an additional row per query with a prefilled value.

Full query:

=SORT({
    QUERY(Materials!A4:K,"SELECT A,D,K,J where B='"&Home!C9&"'") ;
    QUERY(Labour!A4:K,"SELECT A,D,H,G where B='"&Home!C9&"'") 
}, 1, 1)

Example of currently returned columns/values:

QUERY(Materials!A4:K,"SELECT A,D,K,J where B='"&Home!C9&"'")

date1, supplier1, notes1, amount1
date2, supplier1, notes2, amount2
date3, supplier2, notes3, amount4

QUERY(Labour!A4:K,"SELECT A,D,H,G where B='"&Home!C9&"'") 
date1, joblocation1, notes1, amount1
date2, joblocation2, notes2, amount2

What I would like the final output to be:

Name1, Materials, supplier1, notes1, amount1
Name2, Materials, supplier1, notes2, amount2
Name3, Materials, supplier2, notes3, amount4

date1, Labour, notes1, amount1
date2, Labour, notes2, amount2

.. thus as per the title, "adding a column with a specific value when making a google sheet query"

Upvotes: 1

Views: 1999

Answers (1)

CyberAnt
CyberAnt

Reputation: 21

To Add an additional untitled column, here is one possible solution:

QUERY(Materials!A4:K,"SELECT A,D,K,J where B='"&Home!C9&"'")

To

QUERY(Materials!A4:K,"SELECT A,'NewColValue',D,K,J where B='"&Home!C9&"' label 'NewColValue' ''")

Upvotes: 1

Related Questions