Using calculated fields in schema

The formula field property can be used to add calculated fields to your connector's schema.

The full list of functions that are supported in formulas can be found at Function List. Additionally, there is further documentation for:

When referring to another field's ID in a formula, prefix it with a $. For example, if your field ID is t_celsius, it should be referred to as $t_celsius in the formula.

Type for calculated fields

Type is supported for calculated fields, but the output of the formula must match the expected format for that Type.

For example, for the formula:

"HYPERLINK($url, $description)"

The Type should be HYPERLINK.

aggregation for calculated fields

If your formula involves an aggregation, the aggregation should be set to AUTO. Check the type column in the Function List to see if a function returns an aggregation. If your formula does not involve an aggregation, set it to NONE.

Formula aggregation
"SUM($cost) / SUM($units)" AUTO
"$cost + $units" NONE

Examples of calculated fields in schema

function getSchema(request) {
  var cc = DataStudioApp.createCommunityConnector();
  var fields = cc.getFields();
  var types = cc.FieldType;
  var aggregations = cc.AggregationType;

  fields.newDimension()
      .setId('date')
      .setName('Date')
      .setDescription('Date of measurement')
      .setType(types.YEAR_MONTH_DAY);

  fields.newMetric()
      .setId('t_celsius')
      .setName('Temperature (Celsius)')
      .setDescription('Temperature in Celsius')
      .setType(types.NUMBER)
      .setAggregation(aggregations.NONE);

  fields.newMetric()
      .setId('t_fahrenheit')
      .setName('Temperature (Fahrenheit)')
      .setDescription('Temperature in Fahrenheit')
      .setFormula('$t_celsius / 5 * 9 + 32')
      .setType(types.NUMBER)
      .setAggregation(aggregations.NONE);

  fields.newMetric()
      .setId('average_temperature (Celsius)')
      .setName('Average Temperature (Celsius)')
      .setDescription('Temperature in Fahrenheit')
      .setFormula('AVG($t_celsius)')
      .setType(types.NUMBER)
      .setAggregation(aggregations.AUTO);

  fields.newMetric()
      .setId('average_temperature (Celsius)')
      .setName('Average Temperature (Celsius)')
      .setDescription('Temperature in Fahrenheit')
      .setFormula('AVG($t_celsius / 5 * 9 + 32)')
      .setType(types.NUMBER)
      .setAggregation(aggregations.AUTO);

  fields.newDimension()
      .setId('feels_like')
      .setName('Feels Like')
      .setDescription('What it feels like outside.')
      .setFormula('CASE WHEN $t_celsius < 0 THEN "Freezing" WHEN $t_celsius > 40 THEN "Too warm" ELSE "Not bad" END')
      .setType(types.TEXT);

  return { 'schema': fields.build() };
}