How to include date range in SQL query for Data Studio BigQuery community connector

Question

I'm trying to make a community connector using the advanced services from Google's Data Studio to connect to my BigQuery data table. The connector is all set up and my getData function returns a query which looks like:

var sqlString = "SELECT * FROM `PROJECT.DATASET.TABLE` WHERE " +
"DATE(timestamp) >= @startDate AND DATE(timestamp) <= @endDate;"

where PROJECT, DATASET, and TABLE are filled in with their respective IDs. The 'timestamp' field is a BigQuery field in my data table of type TIMESTAMP.

In my getConfig function, I'm setting the configuration to add a daterange object to the request passed into getData:

function getConfig() {
  ...
  config.setDateRangeRequired(true);
  ...
}

I'm then returning the community connector object (defined as 'cc' variable in code below) in my getData function, setting the sql string, query parameters for startDate and endDate, and some other necessary info:

function getData(request) {
  ...
  return cc
    .newBigQueryConfig()
    .setAccessToken(accessToken) // defined earlier
    .setBillingProjectId(billingProjectId) // defined earlier
    .setUseStandardSql(true)
    .setQuery(sqlString)
    .addQueryParameter('startDate', bqTypes.STRING, 
  request.dateRange.startDate)
    .addQueryParameter('endDate', bqTypes.STRING, 
  request.dateRange.endDate)
}

When I run this connector in a report, it connects to BigQuery and even queries the table, but it does not return any data. When I replace @startDate and @endDate with string literals of format 'yyyy-mm-dd', it works as expected, so it seems like my only problem is that I can't figure out how to set the date range parameters in the query (which I assume I'm supposed to do to allow date range control in data studio reports). How do I configure this daterange object so that people can control daterange tags in data studio reports?

Edit: For clarification, I know how to add the date range control on a report. The problem is that the query does not return any data even when the date range query parameters are passed in.

Tyler Schon Tyler Schon · Accepted Answer · 2020-08-28T18:29:04

I ended up fixing my SQL query. I made my WHERE condition as

WHERE DATE(requestTimestamp) BETWEEN @startDate AND @endDate

and it actually returned data correctly. I didn't mention another parameter I was using in my query because I thought it was irrelevant, but I had quotes around another conditioned parameter, which may have screwed up the query. The condition before was more like:

WHERE id = '@id' AND DATE(requestTimestamp) BETWEEN @startDate AND @endDate

I think putting quotes around @id was the problem, because changing the query to:

WHERE id = @id AND DATE(requestTimestamp) BETWEEN @startDate AND @endDate

worked perfectly

How to include date range in SQL query for Data Studio BigQuery community connector

2 Answers