Hey there! Let’s work through this frustrating quote conflict issue you’re hitting when setting a QUERY function via Google Apps Script—this is such a common pitfall, so you’re definitely not alone in struggling with it.

Why This Error Happens #

The root problem is a clash between JavaScript string syntax and Google Sheets formula syntax. When you write a QUERY formula, you need quotes around the select/where logic, but if you don’t properly handle those quotes in your Apps Script code, JavaScript misinterprets the string structure, throwing that "Missing ; before statement" error.

Proven Fixes with Examples #

Let’s walk through the most reliable ways to format your formula correctly:

1. Use Escaped Double Quotes

If you wrap your entire formula in single quotes in JavaScript, you can escape the double quotes inside the QUERY string with a backslash (\"). For example:

function setBasicQuery() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Escape double quotes for the QUERY's query parameter
  const queryFormula = '=QUERY(A:C, "SELECT A WHERE B=\'Banana\'", 1)';
  activeSheet.getRange("D1").setFormula(queryFormula);
}

Or if you prefer wrapping the formula in double quotes, escape the inner double quotes:

const queryFormula = "=QUERY(A:C, \"SELECT A WHERE B='Banana'\", 1)";

2. Use ES6 Template Literals (Recommended!)

Template literals (wrapped in backticks `) let you write the formula almost exactly as you would in Google Sheets, no need to escape double quotes. This is the cleanest approach:

function setQueryWithTemplate() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Template literals handle inner double quotes automatically
  const queryFormula = `=QUERY(A:C, "SELECT A WHERE B='Banana'", 1)`;
  activeSheet.getRange("D1").setFormula(queryFormula);
}

3. Handling Text with Single Quotes (e.g., "O'Neil")

If your QUERY needs to match text that includes a single quote, Google Sheets requires you to use two single quotes to escape it. Here’s how to write that in Apps Script with template literals:

function queryWithApostrophe() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const queryFormula = `=QUERY(A:C, "SELECT A WHERE B='O''Neil'", 1)`;
  activeSheet.getRange("D1").setFormula(queryFormula);
}

With single-quoted strings, you’d escape each single quote like this:

const queryFormula = '=QUERY(A:C, "SELECT A WHERE B=\'O\'\'Neil\'", 1)';

Common Mistake to Avoid #

The error usually pops up when you forget to escape quotes properly, like this invalid code:

// ❌ THIS WILL FAIL
const badFormula = "=QUERY(A:C, "SELECT A WHERE B='Banana'", 1)";

Here, JavaScript sees the second " right after A:C, as the end of the string, so it tries to parse SELECT as a variable—hence the syntax error.

If you’re still stuck, feel free to share your specific formula code, and we can tweak it further!

内容的提问来源于stack exchange，提问作者Sharat Ramamani