Google Sheets – Import Cells with Specific Conditional Formatting

conditional formattinggoogle sheetsgoogle-formsimportrange

I have created a Google Form which links to a spreadsheet. There is an option which asks for the person's name. For the answers in the sheet, I've created conditional formatting to highlight the correct answers. 

=importrange(" xxxx ", "xxx!xxx" )

I want it to be able to import the names from the name column that have answered all the questions correctly to another sheet if possible. I know the import code, however I need help with importing specific names that have all the cells highlighted with conditional formatting.

Best Answer

As Rubén said, conditional formatting is for human consumption only: neither sheet formulas nor scripts can access it. You should create a column that records the fact of "all answers are correct", and then filter by that column. For example, put in E2 

=arrayformula((C2:C=2)*(D2:D=4))

which will put 1 in every row where both answers are correct. Then execute query command on the result of importrange:

=query(importrange("...", "'Form Responses 1'!B:E"), "select Col1, Col2, Col3 where Col4 = 1", 1)
Related Topic