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.

  • The built-in functions can't read the conditional formatting rules and I think that Google Apps Script can't do this either. You should consider to add your validation rules to an auxiliary column or to a script. May 19, 2016 at 18:29
  • @Rubén if you know how to add validation rules, request to edit to help please
    – jas9531
    May 19, 2016 at 18:44
  • See the answer by sandwich May 19, 2016 at 20:08

1 Answer 1


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


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)
  • the query command does not work it gives a #Value! error. "Error Unable to parse query string for Function QUERY parameter 2: NO_COLUMNB"
    – jas9531
    May 19, 2016 at 21:23
  • Indeed, the columns of an imported range must be referenced as Col1, Col2, and so on. Corrected.
    – user79865
    May 19, 2016 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.