I'm wondering how one might handle a query like this. Let's suppose I had the following text contained in Cell A2 of a spreadsheet:
Case Bakers' Flats 12" White Flour Tortillas 10/12ct
and needed to put the following formula into B2:
=QUERY(importrange("KEY", "DATA!A1:Z1000"), "select Col24 where (Col1 = '"&A2&"')")
It would produce an error.
My question is: Is there any way to avoid tripping up the query when the string I am using contains any assortment of quotation marks and apostrophes?
"
).Google Sheets QUERY built-in function automatically escape some characters by internally adding \
before single quotes but it's doesn't work when the cell value to be used as the source for the criteria includes double quotes. As a workaround, the the use of double substitution is proposed.
Below table represents and spreadsheet range that contains
=QUERY(A:A,"SELECT * WHERE A = """&B1&""" ")
+---+---------+-----+-----+ | | A | B | C | +---+---------+-----+-----+ | 1 | I'm | I'm | I'm | | 2 | You're | | | | 3 | It's | | | | 4 | I am | | | | 5 | You are | | | | 6 | It is | | | +---+---------+-----+-----+
=SUBSTITUTE( QUERY( SUBSTITUTE(A:A,"""","''"), "SELECT * WHERE Col1 = """&SUBSTITUTE(B1,"""","''")&"""" ), "''","""" )
Note that instead of using a the letter A as identifier of the data source column it's used Col1.
https://developers.google.com/chart/interactive/docs/querylanguage