Trying to insert a text function into QUERY - how to escape a string in this situation?

Question

I am setting up some report data in App Script, using QUERY over IMPORTRANGE. All is fine until I try to insert a function to extract the short form string month [ie TEXT(Col4,"MMM")] from a date column into the query.

As far as I can tell, "MMM" must have double quotes, and so must the query itself.

My code builds up the function in sections, like so:

  // get base data
  var dataRange = 'IMPORTRANGE("' + dataShtId + '", "VolunteerActivity")';
  var selQuery = 'select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = ' + reportYear + ' ';
  if (reportVolunteer != "") {
    selQuery = selQuery + "and Col1='" + reportVolunteer + "'"; 
  }
  if (reportTeam != "") {
    selQuery = selQuery + "and Col2='" + reportTeam + "'"; 
  }
  selQuery = selQuery + 'group by Col1,Col2,Col4 order by month(Col4) ';
  selQuery = selQuery + "label Col1 'Volunteer', Col2 'Team', SUM(Col3) 'Count', Col4 'Month'";
  var querytext = "";
  querytext = '=QUERY(' + dataRange + ', "' + selQuery + '")';
  Logger.log(querytext);

This resulting function is fine:

=QUERY(IMPORTRANGE("190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY", "VolunteerActivity")
, "select Col1,Col2,SUM(Col3),Col4 group by Col1,Col2,Col4 order by month(Col4) label Col1 'Volunteer', Col2 'Team', SUM(Col3) 'Count', Col4 'Month'")

If I now change Col4 to return the short month string without any quotes around MMM [TEXT(Col4,MMM)] I get "Unable to parse query string".

If I put single quotes around MMM [TEXT(Col4,'MMM')] I get the same parse error.

If I put double quotes [TEXT(Col4,"MMM")] I get "Formula parse error".

If I put escape characters in front of the quotes [TEXT(Col4,\"MMM\")] I also get "Formula parse error".

If I now try changing the quotes in the code:

  // get base data
  var dataRange = "IMPORTRANGE('" + dataShtId + "', 'VolunteerActivity')";
  var selQuery = "select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = " + reportYear + " ";
  if (reportVolunteer != "") {
    selQuery = selQuery + 'and Col1="' + reportVolunteer + '"'; 
  }
  if (reportTeam != "") {
    selQuery = selQuery + 'and Col2="' + reportTeam + '"'; 
  }
  selQuery = selQuery + 'group by Col1,Col2,Col4 order by month(Col4) ';
  selQuery = selQuery + 'label Col1 "Volunteer", Col2 "Team", SUM(Col3) "Count", Col4 "Month"';
  var querytext = "";
  querytext = "=QUERY(" + dataRange + ", '" + selQuery + "')";
  Logger.log(querytext);

I get this result, which also gives "Formula parse error":

=QUERY(IMPORTRANGE('190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY', 'VolunteerActivity')
, 'select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = 2019 group by Col1,Col2,Col4 order by month(Col4) label Col1 "Volunteer", Col2 "Team", SUM(Col3) "Count", Col4 "Month"')

What am I missing?

Edit

For clarification to the conversations below, and ignoring all scripting, I have attempted to get the function to work directly in the sheet.

This works fine without format:

=QUERY(IMPORTRANGE("190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY", "VolunteerActivity")
, "select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = 2019 group by Col1,Col2,Col4 order by Col4,Col1 ")

Attempt to insert format with single quotes or backquotes gives error "unable to parse query string":

=QUERY(IMPORTRANGE("190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY", "VolunteerActivity")
, "select Col1,Col2,SUM(Col3),format Col4 'MMM' where YEAR(Col4) = 2019 group by Col1,Col2,Col4 order by Col4,Col1 ")

Use single quotes or backquotes for select statement (without format statement) gives a formula parse error:

=QUERY(IMPORTRANGE("190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY", "VolunteerActivity")
, 'select Col1,Col2,SUM(Col3),Col4 where YEAR(Col4) = 2019 group by Col1,Col2,Col4 order by Col4,Col1 ')

Unsurprisingly, inserting format into the above is also an error:

=QUERY(IMPORTRANGE("190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY", "VolunteerActivity")
, `select Col1,Col2,SUM(Col3),format Col4 "MMM" where YEAR(Col4) = 2019 group by Col1,Col2,Col4 order by Col4,Col1 `)

I then tried escaping the internal quote marks:

=QUERY(IMPORTRANGE("190b6pmeTAEPpmRM66lRHpYa3oyo55Wb9nqEiLkoo7bY", "VolunteerActivity")
, "select Col1,Col2,SUM(Col3),format Col4 ""MMM"" where YEAR(Col4) = 2019 group by Col1,Col2,Col4 order by Col4,Col1 ")

This gives: Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "format" "format "" at line 1, column 28. Was expecting one of: "true" ... "false" ...". If I use the backslash as the escape character, as was suggested, I get the formula parse error.

I finally put in a work round by inserting an additional calculated column in the original data to pass the month.

Forget apps script. Your formula won't even work in Google sheets. You can't use `text()` inside query like that. @player0 is right. [Edit] your question with the latest modifications as suggested in the answer below, if you're still having the error. — TheMaster, Apr 20 '19 at 10:28

player0 · Answer 1 · 2019-04-19T14:49:57.547

0

try using format parameter in QUERY formula:

or perhaps backquote ` in your script

edited Apr 19 '19 at 14:49

answered Apr 19 '19 at 14:11

player0

124,011
12
67
124

I don't think that is possible in script - it just throws an error. Or if I change the quotes around in that section, the result gives a parse error. – SM. Apr 19 '19 at 14:43
did you try ` ` ? – player0 Apr 19 '19 at 14:48
Yes. I tried within the original query, and also within the one where I changed each single quote for double quote and each double for single. In the original query, it throws an error. In the one with changed quotes, it gives a parse error. – SM. Apr 19 '19 at 17:27
Is that a legal string character? It is not allowed surrounding the string, and if I use it around `mmm` I get a parse error. – SM. Apr 20 '19 at 05:04
1

I have got round the issue by creating an additional calculated column in the original data to contain the month string, but it is annoying not to be able to do it this way. Any more suggestions? – SM. Apr 20 '19 at 05:23

Trying to insert a text function into QUERY - how to escape a string in this situation?

1 Answers1