Google Data Studio Connector: Toronto City Council Attendance

appsscript.json

{
  "timeZone": "America/Toronto",
  "dependencies": {},
  "dataStudio": {
    "name": "Toronto City Council Attendance",
    "logoUrl": "https://i.imgur.com/e9wE7Md.png",
    "company": "Petro Podrezo",
    "companyUrl": "https://toronto.ca/",
    "addonUrl": "https://toronto.ca/",
    "supportUrl": "https://toronto.ca/",
    "shortDescription": "Toronto City Council Attendance",
    "description": "Attendance statistics for Toronto city councilors.",
    "authType": ["NONE"],
    "feeType": ["FREE"]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

index.js

const cc = DataStudioApp.createCommunityConnector();
const dsTypes = cc.FieldType;
const dsAggregationTypes = cc.AggregationType;

function isAdminUser() {
  return true;
}

function getAuthType() {
  const AuthTypes = cc.AuthType;
  return cc
    .newAuthTypeResponse()
    .setAuthType(AuthTypes.NONE)
    .build();
}

function getConfig(request) {
  const config = cc.getConfig();
  config
    .newSelectSingle()
    .setId('dataSetUrl')
    .setName('Period')
    .addOption(config.newOptionBuilder().setLabel('2006 - 2010').setValue('https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/035afc79-7a6b-462a-85a4-ef7b7b14c403?format=json'))
    .addOption(config.newOptionBuilder().setLabel('2010 - 2014').setValue('https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/191a6bf8-038e-4269-8672-d236bc13318b?format=json'))
    .addOption(config.newOptionBuilder().setLabel('2014 - 2018').setValue('https://ckan0.cf.opendata.inter.prod-toronto.ca/download_resource/129dffc6-f820-422d-b23c-6469c4325dff?format=json'));
  
  config.setDateRangeRequired(false);
  config.setIsSteppedConfig(false);

  return config.build();
}

// https://developers.google.com/datastudio/connector/reference#datatype
function _getField(fields, fieldId) {
  switch (fieldId) {
    case 'fullName':
      fields
        .newDimension()
        .setId('fullName')
        .setName('Full Name')
        .setType(dsTypes.TEXT);
      break;
    case 'sessionDatetime':
      fields
        .newDimension()
        .setId('sessionDatetime')
        .setName('Time')
        .setType(dsTypes.YEAR_MONTH_DAY_SECOND);
      break;
    case 'committeeName':
      fields
        .newDimension()
        .setId('committeeName')
        .setName('Committee Name')
        .setType(dsTypes.TEXT);
      break;
    case 'meetingNumber':
      fields
        .newDimension()
        .setId('meetingNumber')
        .setName('Meeting Number')
        .setType(dsTypes.NUMBER);
      break;
    case 'present':
      fields
        .newMetric()
        .setId('present')
        .setName('Present?')
        .setType(dsTypes.BOOLEAN)
        .setAggregation(dsAggregationTypes.COUNT);
      break;
    default:
      throw new Error(`Invalid fieldId: ${fieldId}`)
  }
  return fields;
}



function getSchema(request) {
  let fields = cc.getFields();
  ['fullName', 'sessionDatetime', 'committeeName', 'meetingNumber', 'present'].forEach(fieldId => {
    fields = _getField(fields, fieldId);
  });
  fields.setDefaultMetric('present');
  fields.setDefaultDimension('fullName');
  return { 'schema': fields.build() };
}

/* {
    "Session Start-End Time": "09:44AM - 12:30PM",
    "MTG #": 123,
    "Last Name": "Smith",
    "First Name": "John",
    "Committee": "City Council",
    "Session Type": "Morning",
    "_id": 9999,
    "Session Date": "2009-12-08T00:00:00",
    "Present": "Y"
  } */
function _getDataField(entity, fieldId) {
  switch (fieldId) {
    case 'fullName':
      return `${entity['First Name']} ${entity['Last Name']}`;
    case 'sessionDatetime':
      let year, month, day, hour, hour24, ampm, minute;
      [_, year, month, day] = /^(\d{4})-(\d{2})-(\d{2})/.exec(entity['Session Date']);
      [_, hour, minute, ampm ] = /^(\d{2}):(\d{2})(\w{2})/.exec(entity['Session Start-End Time']);
      hour24 = ampm === 'PM' ? (parseInt(hour) + 12).toString() : hour;
      // YYYYMMDDHHMMSS
      return `${year}${month}${day}${hour24}${minute}00`;
    case 'committeeName':
      return entity['Committee'];
    case 'meetingNumber':
      return entity['MTG #'];
    case 'present':
      return entity['Present'] === 'Y';
    default:
      throw new Error(`Invalid fieldId: ${fieldId}`)
  }
}

function getData(request) {
  let fields = cc.getFields();
  // Get the IDs of all requested fields and map it to the schema for that field
  // Note that we really only need the data type here, but returning extra data
  // such as aggregation methods won't break anything.
  const fieldIds = request.fields.map(field => field.name);
  fieldIds.forEach(fieldId => {
    fields = _getField(fields, fieldId);
  });

  const requestOptions = {
    muteHttpExceptions: true,
    method: 'get'
  };
  const url = request.configParams.dataSetUrl;
  const httpResponse = UrlFetchApp.fetch(url, requestOptions);
  // handle errors from the API
  if(httpResponse.getResponseCode() !== 200) {
    Logger.log('An exception occurred accessing the API:');
    Logger.log(httpResponse.getResponseCode());
    Logger.log(httpResponse.getAllHeaders());
    Logger.log(httpResponse.getContentText());
    // TODO: Return an error to the user
    sendUserError(`The API replied with an unsuccessful status code of ${httpResponse.getResponseCode()}`);
    return;
  }
  const data = JSON.parse(httpResponse.getContentText());

  const rows = data.map(dataPoint => {
    return {
      values: fieldIds.map(fieldId => _getDataField(dataPoint, fieldId))
    };
  });

  const result = {
    schema: fields.build(),
    rows: rows
  };
  return result;
}

function sendUserError(message) {
  cc.newUserError()
    .setText(message)
    .throwException();
}