3

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. – Rubén May 19 '16 at 18:29
  • @Rubén if you know how to add validation rules, request to edit to help please – jas9531 May 19 '16 at 18:44
  • See the answer by sandwich – Rubén May 19 '16 at 20:08
3

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)
  • 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 '16 at 21:23
  • Indeed, the columns of an imported range must be referenced as Col1, Col2, and so on. Corrected. – user79865 May 19 '16 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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