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 and the schema 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 getSchema() and/or getData() functions. Instead of fetching and returning a schema and data, these functions can return query configurations. When a query configuration is returned, Data Studio will directly fetch the schema and/or data based on the query configuration provided.

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 getSchema() and/or getData()

Manifest reference

In the manifest, set the following properties to true.

  • For getSchema(), use property dataStudio.advancedServices.schema
  • For getData(), use property dataStudio.advancedServices.data

A value of true indicates that for the respective function, your connector will return a query configuration instead of the standard response.

To define your own schema using the standard getSchema() response, set dataStudio.advancedServices.schema to false in the manifest or omit this property. This is needed when all fields in your schema cannot be represented by a single BigQuery query e.g. when you are creating a multi-schema connector.

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") in the manifest 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.",
    "advancedServices": {
      "schema": true,
      "data": true
    }
  },
  "oauthScopes": ["https://www.googleapis.com/auth/bigquery.readonly"]
}

If you are using a service account to access BigQuery, then the bigquery.readonly OAuth scope is not required in the manifest. However, the scope should be added to the scopes list for the service account.

getData and getSchema reference

Connectors using Data Studio Advanced Services return a query config object for getSchema() and/or 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 getSchema() and 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 getQueryConfig(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();
}

function getSchema(request) {
  return getQueryConfig(request);
}

function getData(request) {
  return getQueryConfig(request)
}