Sheets query SQL "in" -like function

Question

I hava a table with some data in a google sheet:

[Name] [Product] [Price] [ID]

I need to collect and sum selected row based on an other range, say A2:A on an other sheet, where A2:A contains names from [Name].

I am trying the following solution:

=query('dataSheet'!A3:D,"select * " & myFunction() ,-1)

where myFunction() is a script to assamble the "where ... or where ... " part of the SQL query:

function myFucntion(){
  var payList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ügyfél Fizetés').getRange('A2:A').getValues();
  var payLength = payList.filter(String).length;
  var payRow = payLength + 2;
  if (payLength > 0){
    var sqlString = "";
    sqlString = "where A = '""" & " & """ & "A " & payRow & """ & """ & "'";
    for (i = 0; i < payLength; i++){

    }
  }
  Logger.log(sqlString);
  return sqlString;
}

The sqlString should look something like this:

where A = '" & A2 & "' or A = '" & A3 & "'"

I will add the or segments in the for loop, but the sqlString = ... part throws an error:

Missing ; before statement.

Help me with the syntax, or if you have more efficient solution for my problem, I'm open to all suggestions.

Answer 1

When using Google Apps Script to write Google Sheets custom function we should have in mind two syntaxes, the used by Google Sheets and the used by Google Apps Script, and if the formula will include QUERY built-in function we also show have in mind the Google Query Language syntax.

Google Sheets formulas and Google Apps Script sentences have different syntax.

Concatenation

• On Google Sheets & is used as the concatenate operator
• Google Apps Script uses JavaScript which use + to concatenate strings when at least one of the operands is a string.

Including a " inside a text / escaping quotes in strings

• On Google Sheets to put a " inside at text value we write """
• On JavaScript to include a " inside a string we write \"

Including a ' inside a QUERY argument when it's wrote on Google Apps Script

• On Google Query Language ' is required to enclose string literals. When we will write a the SQL statement in Google Apps Script for a QUERY function it should be wrote inside the string as \'.