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

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?

CodePudding user response：

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"

CodePudding user response：

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