How to Conditional Format based off a Google Sheets Query function?

user_16769802 0
Viewed 295

I have a google sheet that is doing to two queries and would like to highlight all cells if data was pulled in off a certain sheet.

 {QUERY(IMPORTRANGE("url1", "Sheet1!$A:$Z"), 
   "SELECT Col1,Col2,Col3,Col4,Col5,Col15,Col17,Col18,Col7,Col11,Col12,Col13,Col14 WHERE Col15 IS NOT NULL, 1);
 QUERY(IMPORTRANGE("url2", "Sheet2!$A3:$Z"),
   "SELECT Col1,Col2,Col3,Col4,Col5,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15 WHERE Col17 = '1',1)}

I would like to have "Sheet2" to be highlighted all the way through while keeping Sheet1 generic. Is this possible to do?

conditional-formatting google-query-language spreadsheet google-sheets import
2 Answers

could be if you add helper column (which you can hide) based of you can setup conditional formatting. fx would go like this:

={QUERY(IMPORTRANGE("url1", "Sheet1!$A:$Z"), 
"SELECT Col1,Col2,Col3,Col4,Col5,Col15,Col17,Col18,Col7,Col11,Col12,Col13,Col14,'Sheet1' 
 WHERE Col15 IS NOT NULL, 1);
 QUERY(IMPORTRANGE("url2", "Sheet2!$A3:$Z"),
 "SELECT Col1,Col2,Col3,Col4,Col5,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,'Sheet2' 
 WHERE Col17 = '1',1)}

and then a simple cf:

=$N1="Sheet1"
user_56326290

or you could paint it directly with custom fx:

=ROW(A1)<=ROWS(QUERY(IMPORTRANGE("url1", "Sheet1!$A:$Z"), 
 "SELECT Col1 WHERE Col15 IS NOT NULL, 1))

ofc all spreadsheets (importranges) needs to be connected by authorizing the access for each and every importrange

user_56326290
Related