here we'd see how to build a google data studio open-source community connector to visualise sendgrid's global stats using apps script.

context

its been a while that i'd been yearning to create my own open-source community connector but almost every usable data source api that i'd stumbled upon required some kind of auth while the tutorials & explanations available in almost every other blog post typically ended up using "none" 😐

felt like all the more reason for me to share this one.

demo

you could test the connector via the direct link or access the script files to host it yourself.

authentication

authenticate to the sendgrid api by creating an api key in the settings section of the sendgrid ui.


SendGrid recommends API Keys because they are a secure way to talk to the SendGrid API that is separate from your username and password. If your API key gets compromised in any way, it is easy to delete and create a new one and update your environment variables with the new key. An API key permissions can be set to provide access to different functions of your account, without providing full access to your account as a whole.

refer sendGrid > authentication.


you will be prompted to enter this key while setting up the connector for the first time.

enter-api-key

codebase

you can access the entire script on my github repository here.

architecture

perhaps except for 2, all the functions that have been invoked in this endeavour are mandatory -

mandatory connector functions

the auth collective

/**
 * Mandatory function required by Google Data Studio that should
 * return the authentication method required by the connector
 * to authorize the third-party service.
 * @return {Object} AuthType
 */
function getAuthType() {
  var cc = DataStudioApp.createCommunityConnector();
  return cc.newAuthTypeResponse()
  .setAuthType(cc.AuthType.KEY)
  .setHelpUrl('https://sendgrid.com/docs/API_Reference/Web_API_v3/How_To_Use_The_Web_API_v3/authentication.html#-API-key-recommended')
  .build();
}
getAuthType

/**
 * Mandatory function required by Google Data Studio that should
 * clear user credentials for the third-party service.
 * This function does not accept any arguments and
 * the response is empty.
 */
function resetAuth() {
  var userProperties = PropertiesService.getUserProperties();
  userProperties.deleteProperty('dscc.key');
}
resetAuth

/**
 * Mandatory function required by Google Data Studio that should
 * determine if the authentication for the third-party service is valid.
 * @return {Boolean}
 */
function isAuthValid() {
  var userProperties = PropertiesService.getUserProperties();
  var key = userProperties.getProperty('dscc.key');
  return checkForValidKey(key);
}
isAuthValid

/**
 * Mandatory function required by Google Data Studio that should
 * set the credentials after the user enters either their
 * credential information on the community connector configuration page.
 * @param {Object} request The set credentials request.
 * @return {object} An object with an errorCode.
 */
function setCredentials(request) {
  var key = request.key;
  var validKey = checkForValidKey(key);
  if (!validKey) {
    return {
      errorCode: 'INVALID_CREDENTIALS'
    };
  }
  var userProperties = PropertiesService.getUserProperties();
  userProperties.setProperty('dscc.key', key);
  return {
    errorCode: 'NONE'
  };
}
setCredentials

/**
 * Checks if the Key/Token provided by the user is valid
 * @param {String} key
 * @return {Boolean}
 */
function checkForValidKey(key) {
  var token = key;
  var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
  var baseURL = 'https://api.sendgrid.com/v3/stats?start_date=' + today;  
  var options = {
    'method' : 'GET',
    'headers': {
      'Authorization': 'Bearer ' + token,
      'Content-Type': 'application/json'
    },
    'muteHttpExceptions':true
  };
  var response = UrlFetchApp.fetch(baseURL, options);
  if (response.getResponseCode() == 200) {
    return true;
  } else {
    return false;
  }
}
checkForValidKey

gotchas

this app isn't verified

when authorizing the community connector, if you are presented with an "unverified" warning screen see this app isn't verified for details on how to proceed.

start date is too far in the past

sendgrid's stats APIs provide a read-only access to your email statistics that are available only for the last 3 years and so if you stumble upon the following error, please change the date range from the data studio viz.

{
    "errors": [
        {
            "message": "start date is too far in the past",
            "field": "start_date"
        }
    ]
}