使用.setValue或.SetFormula解析错误以设置包含具有多个单引号和双引号的查询函数的公式 [英] Parse error from using .setValue or .SetFormula to set a formula containing a query function that has multiple single and double quotes

问题描述

我在表单A中使用新的Google表单，这个表单工作正常;它包含一个查询函数，用于基于表B（'设置'）中的值和表C中的数据（'数据'）过滤日期。
工作公式：

  = QUERY（Data！A1：G，select G，sum（E）where F不为空并且toDate（D）> = date'& text（'Settings'！B5，yyyy-MM-dd）&'和toDate（D）< = date'&文本（'设置'！B6，yyyy-MM-dd）&'组通过G pivot B标签G'Pivot表格获取范围''中的数据''）
  pre> 
 
 
问题是我想用一个脚本使用 .setFormula 或 .setValue 范围方法，但是对公式字符串有问题。
 
 
 
这些方法将采用单引号或双引号括起来公式/价值。因此，如果您使用单引号来包装公式，则可以避免公式中带双引号的大多数问题。 
此外，您可以构建查询字符串，如 
 
 
  formula ='公式的一部分+'公式的另一部分; 
 range.setFormula（公式）; 
  
尝试的解决方案是在我的公式中用单引号AND 
将我的公式包装在set命令中遇到了一个意外结案的单引号，恰好在工作公式中，我之前插入了一个有意结尾的单引号，并带有+号，就像这里的括号（'+）中的内容。 
到目前为止，我还有吗？我希望我清楚。 
所以尝试的变化看起来像这样。 
首先，工作表中的工作表： 
 
 
  = QUERY（Data！A1：G，select G，sum E）其中F不为空AND toDate（D）> = date'& text（'Settings'！B5，yyyy-MM-dd）&'和toDate（D）< = date' & text（'Settings'！B6，yyyy-MM-dd）&'group by G pivot B label G'Pivot table for data in range'）
  
 
 
 
现在，尝试在脚本中设置： 
 
 
  range.setFormula（'= QUERY（Data！A1：G，select G，sum（E）where F is not null AND toDate（D）> = date'+'& text（'+'Settings' +'！B5，yyyy-MM-dd）&'+'andDate（D）<= date'+'& text（'+'Settings'+'！B6，yyyy-MM ''''）'+'group by G pivot B label G'+'数据透视表，用于范围'+'''''中的数据） 
  
结果：
公式写入并显示错误：公式解析错误。我确定必须有一种方法来指示何时需要单引号作为字符串的一部分，而不是将某些单引号作为下一个字符串的标记开始，如果有意义的话。 
帮助表示感谢，我会在发现时发布。
解决方案
太容易了！ （'= QUERY（Data！A1：G，select G，sum（E）where F is not null AND toDate）（而不是+只是使用\来转义我公式中的单引号，因此答案为：range.setFormula D）> = date \'& text（\'Settings\'！B5，yyyy-MM-dd）&\'and toDate（D）<= date \' & text（\'Settings \'！B6，yyyy-MM-dd）&\'group by G pivot B label G\\'Pivot table for data in range \'）\\ \\'）;  
I have a formula in sheet A using new google sheets that works fine; it contains a query function that filters the dates based on values in sheet B('Settings') and data in sheet C('Data').
The working formula:
=QUERY(Data!A1:G,"select G, sum(E) where F is not null AND toDate(D)>= date '" & text('Settings'!B5, "yyyy-MM-dd")&"' and toDate(D)<= date '" & text('Settings'!B6, "yyyy-MM-dd")&"'group by G pivot B label G'Pivot table for data in range'")
The problem is that I would like to set this formula with a script using the .setFormula or .setValue range methods, but am having problems with the formula string.

These methods will take single or double quotes wrapped around the formula/value. So, if you use single quotes to wrap your formula, you can avoid most issues with double quotes in your formulas. 
Further, you can build query strings such as 
formula = 'part of formula'+'another part of formula'; 
range.setFormula(formula);
Attempted solution was to wrap my formula in the set command with single quotes AND
whenever it came across an Unintended Closing Single Quote that happens to be in the working formula, I inserted before that an intentional closing single quote with a + sign like what is in the parenthesis here ('+).
With me so far? I hope I am being clear.
So that attempted change looks like this.
First, working formula in sheets:
=QUERY(Data!A1:G,"select G, sum(E) where F is not null AND toDate(D)>= date '" & text('Settings'!B5, "yyyy-MM-dd")&"' and toDate(D)<= date '" & text('Settings'!B6, "yyyy-MM-dd")&"'group by G pivot B label G'Pivot table for data in range'")
Now, attempt to set in script:
range.setFormula('=QUERY(Data!A1:G,"select G, sum(E) where F is not null AND toDate(D)>= date '+'" & text('+'Settings'+'!B5, "yyyy-MM-dd")&"'+' and toDate(D)<= date '+'" & text('+'Settings'+'!B6, "yyyy-MM-dd")&"'+'group by G pivot B label G'+'Pivot table for data in range'+'")');
Result:
Formula is written and sheet shows error: formula parse error. I'm sure there must be a way to indicate when I need a single quote as part of the string rather than treating some of my single quotes as markers of the next string start if that makes sense.
Help appreciated, i'll post when I figure it out.
解决方案
Too easy! instead of '+ just use \ to escape the single quotes in my formula so answer is: range.setFormula('=QUERY(Data!A1:G,"select G, sum(E) where F is not null AND toDate(D)>= date \'" & text(\'Settings\'!B5, "yyyy-MM-dd")&"\' and toDate(D)<= date \'" & text(\'Settings\'!B6, "yyyy-MM-dd")&"\'group by G pivot B label G\'Pivot table for data in range\'")\'); 

                        
这篇关于使用.setValue或.SetFormula解析错误以设置包含具有多个单引号和双引号的查询函数的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！