Data Studio Advanced Services

If your connector fetches data from BigQuery, then you can use Data Studio Advanced Services to optimize your connector. Instead of calling the BigQuery API directly, you provide a query configuration and Data Studio will fetch the data for you.

How it works

Community Connectors that use Data Studio Advanced Services work similiarly to a normal connector.

The notable difference is the response expected from the execution of the getData() function. Instead of fetching and returning data, getData() should return a query configuration that Data Studio uses to fetch the data.

Developing your connector

Community Connectors that use Data Studio Advanced Services are the same as a normal connector with two exceptions.

  1. An additional manifest key
  2. A different response from getData()

Manifest reference

In the manifest, set the dataStudio.useQueryConfig property to true. This tells Data Studio that your connector's getData() function will return a query configuration instead of data.

Since the BigQuery query will be executed on behalf of the authorized user, you also need to include the BigQuery OAuth scope ("https://www.googleapis.com/auth/bigquery.readonly") to request authorization.

The following is a connector manifest using the authorized user's credentials to connect to BigQuery:

{
  "dataStudio": {
    "name": "BigQuery Public Dataset Demo",
    "logoUrl": "https://www.gstatic.com/images/branding/product/1x/data_connector_48dp.png",
    "company": "Data Studio DevRel",
    "companyUrl": "https://developers.google.com/datastudio/",
    "addOnUrl": "https://developers.google.com/datastudio/",
    "supportUrl": "https://developers.google.com/datastudio/",
    "description": "Use BigQuery public dataset with Data Studio's Advanced Services.",
    "useQueryConfig": true
  },
  "oauthScopes": ["https://www.googleapis.com/auth/bigquery.readonly"]
}

getData reference

Connectors using Data Studio Advanced Services return a query config object for getData. Depending on the data source, this config may need additional properties. BigQuery, for example, requires the bigQueryConnectorConfig property.

The following is an example of using the DataStudioApp service to create this configuration object:

var bqTypes = DataStudioApp.createCommunityConnector().BigQueryParameterType;
var configuration = DataStudioApp.createCommunityConnector().newBigQueryConfig()
    // BigQuery billing project's Id.
    .setBillingProjectId('billingProjectId')
    // The query that will be executed.
    .setQuery('myQueryString')
    // Set to `true` to use StandardSQL.
    .setUseStandardSql(true)
    // The accessToken used for service execution.
    .setAccessToken('myAccessToken')
    // Adding a `STRING` query parameter. Other supported types are `BOOL`,
    // `FLOAT64`, and `INT64`.
    .addQueryParameter('myUrlParameterName', bqTypes.STRING, 'myUrlParameterValue')
    .build();

Example getData function

var sqlString = "" +
    "SELECT " +
    "  _TABLE_SUFFIX AS yyyymm, " +
    "  ROUND(SUM(IF(fcp.start < @fast_fcp, fcp.density, 0)), 4) AS fast_fcp, " +
    "  ROUND(SUM(IF(fcp.start >= 1000 AND fcp.start < 3000, fcp.density, 0)), 4) AS avg_fcp, " +
    "  ROUND(SUM(IF(fcp.start >= 3000, fcp.density, 0)), 4) AS slow_fcp " +
    "FROM " +
    "  `chrome-ux-report.all.*`, " +
    "  UNNEST(first_contentful_paint.histogram.bin) AS fcp " +
    "WHERE " +
    "  origin = @url " +
    "GROUP BY " +
    "  yyyymm " +
    "ORDER BY " +
    "  yyyymm ";

function getData(request) {
  var url = (request.configParams && request.configParams.url);
  var projectId = (request.configParams && request.configParams.projectId);
  var authToken = ScriptApp.getOAuthToken();
  return DataStudioApp.createCommunityConnector().newBigQueryConfig()
      .setAccessToken(authToken)
      .setUseStandardSql(true)
      .setBillingProjectId(projectId)
      .setQuery(sqlString)
      .addQueryParameter('url', bqTypes.STRING, url)
      .addQueryParameter('fast_fcp', bqTypes.INT64, '' + 1000)
      .build();
}