Google Sheets Query Coalesce?

Question

is there any query syntax that woks like coalesce in google sheets?

if i have a source like pict below

the result i want is only getting id and time if status is true, but the time is only exist in one col either in check column or report column

so the result would be like this...

I tired this but doesn't work

=QUERY(A1:D4, "SELECT A, COALESCE(B, C) WHERE D = TRUE")

any ideas or workarounds? Thanks

Answer 1

you can use something like this:

=QUERY(transpose(B1:H1),"Select Col1 where Col1 is not null limit 1",0)

This transposes the row into a column, queries all non-null values from that column, and then set limit 1 to return the first value. So essentially you are selecting the leftmost non-empty value from your row.

I can't take full credit for this, I must have gotten it somewhere else... but it's in one of my sheets.

Answer 2

try:

=ARRAYFORMULA(IFERROR(SPLIT(FLATTEN(QUERY(TRANSPOSE(
 ARRAY_CONSTRAIN(IF(D2:D=TRUE, {A2:A, IF(B2:C="",,"×"&B2:C), D2:D}, ), 9^9, 
 COLUMNS(A:C))),, 9^9)), "×")))