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.
