Google Query aggregation from an on-the-fly new variable

Question

I'm trying to build a Google Query and make a request from Apps Scripts. I'm appending the query to the sheet URI like:

https://docs.google.com/spreadsheets/d/SHEET_ID/gviz/tq?sheet=Sheet1&tq=query;

The query is:

query = "SELECT N, avg(datediff(todate(G),todate(D))) " +
        "GROUP BY N";

But I'm only getting an error like:

{version=0.6, errors=[{detailed_message=Invalid query: PARSE_ERROR: Encountered "(" at line 1, column 23.
Was expecting:
")" ...
, reason=invalid_query, message=INVALID_QUERY}], reqId=0, status=error}

I got this answer for handling that case calling the query from within the spreadsheets: https://productforums.google.com/d/msg/docs/Ksc-w0r8uj0/QFxiXlmzAwAJ But I don't know how to do the same by code, as the response is in a JSONP format which kind of manually edit to only have a JSON string.

Thanks

Answer 1

Answering my own question. Just using the query, I couldn't find a way, but instead reading through the Data Manipulation Methods, specifically "Creating a modifier function" gives me the possibility to modify the data on the fly and then aggregate it.

https://developers.google.com/chart/interactive/docs/reference#google_visualization_data