Looker Studio Advanced Services

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

How it works

Community Connectors that use Looker Studio Advanced Services work similarly 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, Looker Studio will directly fetch the schema and/or data based on the query configuration provided.

Developing your connector

Community Connectors that use Looker 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": "Looker DevRel",
    "companyUrl": "https://developers.google.com/looker-studio/",
    "addOnUrl": "https://developers.google.com/looker-studio/",
    "supportUrl": "https://developers.google.com/looker-studio/",
    "description": "Use BigQuery public dataset with Looker 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.

Caution: useQueryConfig has been deprecated in the manifest. If your existing connector manifest uses useQueryConfig, consider using advancedServices in the manifest instead.

getData and getSchema reference

Connectors using Looker Studio Advanced Services return a query config object for getSchema() and/or getData(). Depending on the data source, this config may need additional properties.

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();

Note: Query parameter names should not include the leading @ required for the query.

Note: parameterValue.value must be a JS string, even if the parameterType is a number or boolean.

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)
}