Quoting in a query formula

[Terri C]

Hi,

I have an apps script that pulls data from a database and builds out some reports.  The process has been working great then today there is a new name Mickey D'Mouse with an apostrophe note I changed the FirstName and LastName but the D'  is original. I made the text bigger as it makes it easier to see what the punctuation is. I've tried surrounding various parts with different quotes, but I've not been able to figure out the combo to get this to display.

=Query(MasterList!$C:$E,"SELECT C, D, E WHERE C=`Mickey D'Mouse`",1)

It gives this error:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " <ID> "LastName "" at line 1, column 36. Was expecting one of: <EOF> "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label" ... "format" ... "options" ... "and" ... "or" ...

My script dynamically builds the sheets so this is the line that inserts this particular formula onto the sheet. This is how this particular formula gets onto the sheet.

ws.getRange("A13").setFormula(`=Query(MasterList!$C:$E,"SELECT C, D, E WHERE C='${teacherName}'",1)`); 

Thanks for your help.

Terri

[Keith Andersen]

Try...

=Query(MasterList!$C:$E,"SELECT C, D, E WHERE C=`Mickey D\'Mouse`",1)

If that doesn't work try using single quotes instead of the back tick. The apostrophe in front of the D simply has to be escaped by a backslash.

NOTICE: This message is from the Greece Central School District. This message and any attachments may be confidential and/or privileged and are intended only for the individual(s) or group(s) identified as the addressee. If the message addressee is in error, or you are not authorized to read, copy, or distribute this message or attachments; please delete this message and attachments and notify the sender by return email at the address listed above.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/88fe3807-b168-47c6-b7ea-3fe0358a5922n%40googlegroups.com.

[Terri Cvetan]

I played around with it some more and figured out the FILTER() function was not as fussy about assorted non-alpha characters

This is what my code inserts into the cell:

=FILTER(MasterList!C2:E,MasterList!C2:C="Mickey D'Mouse")

What I modified in my apps script

    ws.getRange("A13").setFormula(`=FILTER('MasterList'!C2:E,'MasterList'!C2:C="${teacherName}")`); //Works with the D name

    //ws.getRange("A13").setFormula(`=Query(MasterList!$C:$E,"SELECT C, D, E WHERE C='${teacherName}'",1)`); //Works with simple names

Thanks for your help!

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/FKwteNgnAMg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4z_YTkZ9wsxMsoMdKsP3%3DUc3J2e7jWhE0sKMdbUWC_fjg%40mail.gmail.com.

[Keith Andersen]

You're welcome. Thanks for letting me know how you solved it.

Cheers

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADi%2BwK0K3aQGqKO8L2EmAbUC9nNz9SgRxF4Z8aZtqgXXTOCQnQ%40mail.gmail.com.

[Keith Andersen]

https://excelmee.com/excel-formulas/lookup/get-title-row-with-filter-formula-result-in-excel/

This works to get the headers with filter function.

Cheers

Keith

On Wed, Aug 3, 2022, 11:49 AM 'Terri Cvetan' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

Follow-up, I see why I was using the QUERY() the FILTER() did not bring over the column headings. So I added one line to grab the headers in my apps script. 

     ws.getRange("A13").setFormula(`Query(MasterList!$C:$E,"SELECT C, D, E WHERE C='Teacher Name'")`); //get the headers

     ws.getRange("A14").setFormula(`=FILTER('MasterList'!C2:E,'MasterList'!C2:C="${teacherName}")`); //gets data

Terri L. Cvetan

Programmer Analyst I

Greece Central School District

Technology Support Services

Phone: 585-966-2951

Terri....@greececsd.org

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4x29KkCKt05%3D0iJ9fChiofqfiXLwinrW_8yS9_u4pJhMQ%40mail.gmail.com.

NOTICE: This message is from the Greece Central School District. This message and any attachments may be confidential and/or privileged and are intended only for the individual(s) or group(s) identified as the addressee. If the message addressee is in error, or you are not authorized to read, copy, or distribute this message or attachments; please delete this message and attachments and notify the sender by return email at the address listed above.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADi%2BwK2QYfnkvdGsqjhwODKSgdKaH7hf8OJLah1qtPVF4rK7OQ%40mail.gmail.com.

[Keith Andersen]

Experimenting I have found..

https://docs.google.com/spreadsheets/d/1Ovt4RHtvi7kxQvM41JUwqqCqu8i_cQ-i-VfEqCLFRsk/edit?usp=drivesdk

Hope this helps

Cheers

Keith

[Terri C]

You rock my friend! I see what this is doing and this works perfectly!   Thank you!

[Keith Andersen]

It's not without it's own limitations...but it's a cool concept.

Glad to help. Cheers.

Keith 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/c699fb4f-9883-4d86-bb59-1433532d2fd9n%40googlegroups.com.