How to build a simple Google Data Studio connector ?

2020-04-19

Content

How does data flow in Google Data Studio ?

How Data Flow

Data Set:

  • “Data set” refers to this underlying data, which lives outside of Data Studio.
  • A data set is information you’ve collected, possibly generated by or housed in another platform like WebAPI, MySQL DB, CSV file.

Data Source:

  • A data source is Data Studio’s representation of your data schema.

Report:

  • A report is the visualization you create, built from various components. for example:

How to fetch data from Web APIs ?

Community Connectors enable direct connections from Data Studio to any internet accessible data source.
Connector

To build a basic community connector, you need to define four function:

  • getAuthType()
  • getConfig()
  • getSchema()
  • getData()

Community Connector

The workflow of a community connector

How Connector Work

How to set up a community connector ?

Env Set Up

Requirements

  • npm 5.2.0 or later
  • Some familiarity with the command-line.

Using dscc-gen

To create a new community-connector with dscc-gen, run the follow command:

1
2
npm i @google/dscc-gen
npx @google/dscc-gen connector

What dscc-gen can do ?

1
2
3
4
5
6
7
npm run open              // open your project in Apps Script.
npm run push              // push your local changes to Apps Script.
npm run watch             // watches for local changes & pushes them to Apps Script.
npm run prettier          // formats your code using community standards.
npm run try_latest        // opens the deployment with your latest code.
npm run try_production    //opens your production deployment.
npm run update_production //updates your production deployment to use the latest code.

Implement the connector ?

  1. Define getAuthType() function
    1. Data Studio will call the getAuthType() function when it needs to know the authentication method used by the connector.
    2. This function should return the authentication method required by the connector to authorize the 3rd-party service.
1
2
3
4
5
// auth.js
function getAuthType() {
  var response = { type: 'NONE' };
  return response;
}
  1. Define getConfig() function.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
// Code.js
function getConfig(request) {
  var config = cc.getConfig();
  config
    .newInfo()
    .setId('instructions')
    .setText('Enter npm package names to fetch their download count.');

  config
    .newTextInput()
    .setId('package')
    .setName('Enter a single package name')
    .setHelpText('e.g. googleapis or lighthouse')
    .setPlaceholder('googleapi');

  config.setDateRangeRequired(true);

  return config.build();
}

Result:

Get Config

  1. Define getSchema() function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
// Code.js

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

  fields
    .newDimension()
    .setId('packageName')
    .setType(types.TEXT);

  fields
    .newMetric()
    .setId('downloads')
    .setType(types.NUMBER)
    .setAggregation(aggregations.SUM);

  fields
    .newDimension()
    .setId('day')
    .setType(types.YEAR_MONTH_DAY);

  return fields;
}

function getSchema(request) {
  console.log('getSchema');
  var fields = getFields(request).build();
  return { schema: fields };
}
  1. define getData() function
  • Understanding the request Object

Data Studio passes the request object with each getData() call. The request objest have the following structure:

1
2
3
4
5
6
7
8
9
10
11
12
13
{
  configParams: object,
  scriptParams: object,
  dateRange: {
    startDate: string,
    endDate: string
  },
  fields: [
    {
      name: Field.name
    }
  ]
}

  • The configParams object will contain configuration parameters from getConfig()that are defined by the user.
  • The scriptParams object will contain information relevant to connector execution.
  • dateRange will contain the requested date range if requested in getConfig()response.
  • fields will contain the list of names of fields for which data is requested.

For Example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
{
  configParams: {
    package: 'jquery'
  },
  dateRange: {
    endDate: '2017-07-16',
    startDate: '2017-07-18'
  },
  fields: [
    {
      name: 'day',
    },
    {
      name: 'downloads',
    }
  ]
}

  • Implement the getData() function

In the getData()response, you will need to provide both schema and data for the requested fields. You will divide up the code into three segments:

  1. Create schema for requested fields
  2. Fetch and parse data from API
  3. Transform parsed data and filter for requested fields
1
2
3
4
5
6
7
8
9
10
11
12
13
14
// Code.js
function getData(request) {

  // TODO: Create schema for requested fields
  
  // TODO: Fetch and parse data from API
  
  // TODO: Transform parsed data and filter for requested fields

  return {
    schema: <filtered schema>,
    rows: <transformed and filtered data>
  };
}

  • Create schema for requested fields

    1
    2
    3
    4
    5
    // Code.js
    var requestedFieldIds = request.fields.map(function(field) {
        return field.name;
      });
      var requestedFields = getFields().forIds(requestedFieldIds);

  • Fetch and parse data from API

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    // Code.js
    // https://api.npmjs.org/downloads/point/{start_date}:{end_date}/{package}

    // Fetch and parse data from API
      var url = [
        'https://api.npmjs.org/downloads/range/',
        request.dateRange.startDate,
        ':',
        request.dateRange.endDate,
        '/',
        request.configParams.package
      ];
      var response = UrlFetchApp.fetch(url.join(''));
      var parsedResponse = JSON.parse(response).downloads;

  • Transform parsed data and filter for requested fields

The response from the npm API will be in the following format:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{
  downloads: [
    {
    day: '2014-02-27',
    downloads: 1904088
    },
    ..
    {
    day: '2014-03-04',
    downloads: 7904294
    }
  ],
  start: '2014-02-25',
  end: '2014-03-04',
  package: 'somepackage'
}

We will tranform the data into this form:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
{
  schema: [
    {
      object(Field)
    }
  ],
  rows: [
    {
      values: [string]
    }
  ]
}

// more specific
{
  schema: requestedFields.build(),
  rows: [
    {
      values: [ 38949, '20170716']
    },
    {
      values: [ 165314, '20170717']
    },
    {
      values: [ 180124, '20170718']
    },
  ]
}

Usually we will use a subset of the schema the connector defined. You will use the following function to transform the parsed data and filter it down for requested fields.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// Code.js
function responseToRows(requestedFields, response, packageName) {
  // Transform parsed data and filter for requested fields
  return response.map(function(dailyDownload) {
    var row = [];
    requestedFields.asArray().forEach(function (field) {
      switch (field.getId()) {
        case 'day':
          return row.push(dailyDownload.day.replace(/-/g, ''));
        case 'downloads':
          return row.push(dailyDownload.downloads);
        case 'packageName':
          return row.push(packageName);
        default:
          return row.push('');
      }
    });
    return { values: row };
  });
}

The complete version of getData():

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
function responseToRows(requestedFields, response, packageName) {
  // Transform parsed data and filter for requested fields
  return response.map(function(dailyDownload) {
    var row = [];
    requestedFields.asArray().forEach(function (field) {
      switch (field.getId()) {
        case 'day':
          return row.push(dailyDownload.day.replace(/-/g, ''));
        case 'downloads':
          return row.push(dailyDownload.downloads);
        case 'packageName':
          return row.push(packageName);
        default:
          return row.push('');
      }
    });
    return { values: row };
  });
}

function getData(request) {
  var requestedFieldIds = request.fields.map(function(field) {
    return field.name;
  });
  var requestedFields = getFields().forIds(requestedFieldIds);

  // Fetch and parse data from API
  var url = [
    'https://api.npmjs.org/downloads/range/',
    request.dateRange.startDate,
    ':',
    request.dateRange.endDate,
    '/',
    request.configParams.package
  ];
  var response = UrlFetchApp.fetch(url.join(''));
  var parsedResponse = JSON.parse(response).downloads;
  var rows = responseToRows(requestedFields, parsedResponse, request.configParams.package);

  return {
    schema: requestedFields.build(),
    rows: rows
  };
}

  1. Deployment
    1
    2
    npm run push
    npm run try_latest