Quotation marks and apostrophes in the same string in QUERY Google Sheets function

Question

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?

Answer 1

Short answer

• To escape single quote / apostrophe, embrace the string containing an apostrophe between double quotes (").
• To escape double quotes, apply a double substitution, first to remove the double-quotes, then to add them again.

Explanation

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.

Example for single quote / apostrophe

Below table represents and spreadsheet range that contains

• Column A: The data source
• Cell B1: The data value to be used in the criteria expression
• Cell C1: The following formula =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   |     |     |
+---+---------+-----+-----+

Example for single quote / apostrophe and double quotes

=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.

Reference

https://developers.google.com/chart/interactive/docs/querylanguage