First: You can always check out the Open-Source implementations that others did for custom Google Data Studio connectors. They are a great source if information. Fore more information checkout the documentation on Open Source Community Connectors.
Second: My implementation is for a time tracking system thus having confidential GDPR relevant data. That's why I can not just give you response messages. But I assembled this code. It contains authentifiction, HTTP GET data fetch and data conversions. Explanation is below the code. Again, checkout the open-source connectors if you need further assistance.
var cc = DataStudioApp.createCommunityConnector();
const URL_DATA = 'https://www.myverysecretdomain.com/api';
const URL_PING = 'https://www.myverysecretdomain.com/ping';
const AUTH_USER = 'auth.user'
const AUTH_KEY = 'auth.key';
const JSON_TAG = 'user';
String.prototype.format = function() {
// https://coderwall.com/p/flonoa/simple-string-format-in-javascript
a = this;
for (k in arguments) {
a = a.replace("{" + k + "}", arguments[k])
}
return a
}
function httpGet(user, token, url, params) {
try {
// this depends on the URL you are connecting to
var headers = {
'ApiUser': user,
'ApiToken': token,
'User-Agent': 'my super freaky Google Data Studio connector'
};
var options = {
headers: headers
};
if (params && Object.keys(params).length > 0) {
var params_ = [];
for (const [key, value] of Object.entries(params)) {
var value_ = value;
if (Array.isArray(value))
value_ = value.join(',');
params_.push('{0}={1}'.format(key, encodeURIComponent(value_)))
}
var query = params_.join('&');
url = '{0}?{1}'.format(url, query);
}
var response = UrlFetchApp.fetch(url, options);
return {
code: response.getResponseCode(),
json: JSON.parse(response.getContentText())
}
} catch (e) {
throwConnectorError(e);
}
}
function getCredentials() {
var userProperties = PropertiesService.getUserProperties();
return {
username: userProperties.getProperty(AUTH_USER),
token: userProperties.getProperty(AUTH_KEY)
}
}
function validateCredentials(user, token) {
if (!user || !token)
return false;
var response = httpGet(user, token, URL_PING);
if (response.code == 200)
console.log('API key for the user %s successfully validated', user);
else
console.error('API key for the user %s is invalid. Code: %s', user, response.code);
return response;
}
function getAuthType() {
var cc = DataStudioApp.createCommunityConnector();
return cc.newAuthTypeResponse()
.setAuthType(cc.AuthType.USER_TOKEN)
.setHelpUrl('https://www.myverysecretdomain.com/index.html#authentication')
.build();
}
function resetAuth() {
var userProperties = PropertiesService.getUserProperties();
userProperties.deleteProperty(AUTH_USER);
userProperties.deleteProperty(AUTH_KEY);
console.info('Credentials have been reset.');
}
function isAuthValid() {
var credentials = getCredentials()
if (credentials == null) {
console.info('No credentials found.');
return false;
}
var response = validateCredentials(credentials.username, credentials.token);
return (response != null && response.code == 200);
}
function setCredentials(request) {
var credentials = request.userToken;
var response = validateCredentials(credentials.username, credentials.token);
if (response == null || response.code != 200) return { errorCode: 'INVALID_CREDENTIALS' };
var userProperties = PropertiesService.getUserProperties();
userProperties.setProperty(AUTH_USER, credentials.username);
userProperties.setProperty(AUTH_KEY, credentials.token);
console.info('Credentials have been stored');
return {
errorCode: 'NONE'
};
}
function throwConnectorError(text) {
DataStudioApp.createCommunityConnector()
.newUserError()
.setDebugText(text)
.setText(text)
.throwException();
}
function getConfig(request) {
// ToDo: handle request.languageCode for different languages being displayed
console.log(request)
var params = request.configParams;
var config = cc.getConfig();
// ToDo: add your config if necessary
config.setDateRangeRequired(true);
return config.build();
}
function getDimensions() {
var types = cc.FieldType;
return [
{
id:'id',
name:'ID',
type:types.NUMBER
},
{
id:'name',
name:'Name',
isDefault:true,
type:types.TEXT
},
{
id:'email',
name:'Email',
type:types.TEXT
}
];
}
function getMetrics() {
return [];
}
function getFields(request) {
Logger.log(request)
var fields = cc.getFields();
var dimensions = this.getDimensions();
var metrics = this.getMetrics();
dimensions.forEach(dimension => fields.newDimension().setId(dimension.id).setName(dimension.name).setType(dimension.type));
metrics.forEach(metric => fields.newMetric().setId(metric.id).setName(metric.name).setType(metric.type).setAggregation(metric.aggregations));
var defaultDimension = dimensions.find(field => field.hasOwnProperty('isDefault') && field.isDefault == true);
var defaultMetric = metrics.find(field => field.hasOwnProperty('isDefault') && field.isDefault == true);
if (defaultDimension)
fields.setDefaultDimension(defaultDimension.id);
if (defaultMetric)
fields.setDefaultMetric(defaultMetric.id);
return fields;
}
function getSchema(request) {
var fields = getFields(request).build();
return { schema: fields };
}
function convertValue(value, id) {
// ToDo: add special conversion if necessary
switch(id) {
default:
// value will be converted automatically
return value[id];
}
}
function entriesToDicts(schema, data, converter, tag) {
return data.map(function(element) {
var entry = element[tag];
var row = {};
schema.forEach(function(field) {
// field has same name in connector and original data source
var id = field.id;
var value = converter(entry, id);
// use UI field ID
row[field.id] = value;
});
return row;
});
}
function dictsToRows(requestedFields, rows) {
return rows.reduce((result, row) => ([...result, {'values': requestedFields.reduce((values, field) => ([...values, row[field]]), [])}]), []);
}
function getParams (request) {
var schema = this.getSchema();
var params;
if (request) {
params = {};
// ToDo: handle pagination={startRow=1.0, rowCount=100.0}
} else {
// preview only
params = {
limit: 20
}
}
return params;
}
function getData(request) {
Logger.log(request)
var credentials = getCredentials()
var schema = getSchema();
var params = getParams(request);
var requestedFields; // fields structured as I want them (see above)
var requestedSchema; // fields structured as Google expects them
if (request) {
// make sure the ordering of the requested fields is kept correct in the resulting data
requestedFields = request.fields.filter(field => !field.forFilterOnly).map(field => field.name);
requestedSchema = getFields(request).forIds(requestedFields);
} else {
// use all fields from schema
requestedFields = schema.map(field => field.id);
requestedSchema = api.getFields(request);
}
var filterPresent = request && request.dimensionsFilters;
//var filter = ...
if (filterPresent) {
// ToDo: apply request filters on API level (before the API call) to minimize data retrieval from API (number of rows) and increase speed
// see https://developers.google.com/datastudio/connector/filters
// filter = ... // initialize filter
// filter.preFilter(params); // low-level API filtering if possible
}
// get HTTP response; e.g. check for HTTT RETURN CODE on response.code if necessary
var response = httpGet(credentials.username, credentials.token, URL_DATA, params);
// get JSON data from HTTP response
var data = response.json;
// convert the full dataset including all fields (the full schema). non-requested fields will be filtered later on
var rows = entriesToDicts(schema, data, convertValue, JSON_TAG);
// match rows against filter (high-level filtering)
//if (filter)
// rows = rows.filter(row => filter.match(row) == true);
// remove non-requested fields
var result = dictsToRows(requestedFields, rows);
console.log('{0} rows received'.format(result.length));
//console.log(result);
return {
schema: requestedSchema.build(),
rows: result,
filtersApplied: filter ? true : false
};
}
A sample request that filters for all users with names starting with J.
{
configParams={},
dateRange={
endDate=2020-05-14,
startDate=2020-04-17
},
fields=[
{name=name}
],
scriptParams={
lastRefresh=1589543208040
},
dimensionsFilters=[
[
{
values=[^J.*],
operator=REGEXP_EXACT_MATCH,
type=INCLUDE,
fieldName=name
}
]
]
}
The JSON data returned by the HTTP GET contains all fields (full schema).
[ { user:
{ id: 1,
name: 'Jane Doe',
email: '[email protected]' } },
{ user:
{ id: 2,
name: 'John Doe',
email: '[email protected]' } }
]
Once the data is filtered and converted/transformed, you'll get this result, which is perfectly displayed by Google Data Studio:
{
filtersApplied=true,
schema=[
{
isDefault=true,
semantics={
semanticType=TEXT,
conceptType=DIMENSION
},
label=Name,
name=name,
dataType=STRING
}
],
rows=[
{values=[Jane Doe]},
{values=[John Doe]}
]
}
