How to fix the error 'API call to bigquery.jobs.query failed with error: Encountered " "FROM" "from "" at .... Was expecting: ")"'

Question

I am trying to run a bigquery api call using the query string(attached in the code) from appscript; The query runs perfectly fine in bigquery UI

I have tried the following with no success:
1. include parentheses in orders.creation_date in the query string
2. replace orders with the actual table table i.e [my-project:theservices.theservice_order_item]

/**
 * Runs a BigQuery query and logs the results in a spreadsheet.
 */
function runQuery() {

  var projectId = 'my-project';

  var request = {
    query:  
    "SELECT  extract(date from orders.creation_date) as the_date \n FROM [my-project:theservices.theservice_order_item] AS orders LIMIT 10;"   
  }; 

};

Following is the error I get:
API call to bigquery.jobs.query failed with error: Encountered " "FROM" "from "" at line 1, column 22. Was expecting: ")" ... (line 23, file "Code")

Answer 1

Quoting App Script BigQuery Standard SQL insert or update statement:

You need to set the useLegacySql flag/parameter to false, to indicate that you want to use standard SQL, like so:

var job = {
configuration: {
  query: {
    query: 'SELECT ....',
    useLegacySql: false
    }

Additionally, when a table looks like this - that's #legacySQL:

FROM [my-project:theservices.theservice_order_item]

In #standardSQL the table should be enclosed in tilde '`' and has a '.' between the project and dataset name:

FROM `my-project.theservices.theservice_order_item`