If you use Zoho Desk to manage customer service and you want to analyze how things are going, I have good news, let’s see step by step how you can easily create a connector and run it in Data Studio to create your own reports and dashboards.

If you use another service, don’t worry, it will be easy for you to adapt the example to your reality. You will have to customize the information query to the architecture of your tools.

In order to make the article lighter, we will not describe the fundamental aspects of google apps scripts. In case you have any doubt, I invite you to review this tutorial that has served as inspiration for this text.

One of the points that I think is great, is that you can do the whole process without installing anything on your local machine. Since we will do all the work on the different platforms.

These are the steps involved in building the connector:

You can check the official documentation of Data Studio and Zoho Desk API

• A google account. (I will assume that you already have it)
• Create a project in Google Apps Scripts (https://script.google.com )
• Enable OAUTH2 library and external calls
  • Id. de Biblioteca:1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
• Develop the basic functions of the connectors in google apps scripts
• A Zoho account (I will assume that you already have it)
• Create an app in Zoho’s console API
• Publish connector
• Authorize access to the different accounts
• Analyze

Develops the basic functions

Main File

The first task we will do within our google apps scrips project is to create a file called main.gs (The name is not relevant).

In this file we will add the following functions:

Function that we will use to select OAUTH2 as an authentication method.

function getAuthType() {
  return { type: "OAUTH2" };
}

Also, although it is outside the scope of this tutorial, we can define specific operation for administrators, although at this point, we will not use it.

function isAdminUser() {
  return true;
}

At the configuration level, we could define different items, for the example we will simply indicate that it is not mandatory to report dates.

function getConfig() {
   return {
    dateRangeRequired: false
  };
}

Let’s get our hands on it, we’ll get the structure of the data, the data and map it to a format that data studio knows how to manage.

function getData(request) {
  var dataSchema = prepareSchema(request);
  var items = fetchFromApi();
  return buildTabularData(items, dataSchema);
}

function prepareSchema(request) {
  var dataSchema = [];
  var fixedSchema = getSchema().schema;
  request.fields.forEach(function(field) {
    for (var i = 0; i < fixedSchema.length; i++) {
      if (fixedSchema[i].name == field.name) {
        dataSchema.push(fixedSchema[i]);
        break;
      }
    }
  });
  return dataSchema;
}

function getSchema() {
  return {
    schema: [
      {
        name: 'id',
        label: 'ID',
        dataType: 'STRING',
        semantics: {
          conceptType: 'DIMENSION'
        }
      },
      {
        name: 'ticketNumber',
        label: 'Ticket',
        dataType: 'STRING',
        semantics: {
          conceptType: 'DIMENSION'
        }
      },
      {
        name: 'email',
        label: 'Email',
        dataType: 'STRING',
        semantics: {
          conceptType: 'DIMENSION'
        }
      },
      {
        name: 'subject',
        label: 'subject',
        dataType: 'STRING',
        semantics: {
          conceptType: 'DIMENSION'
        }
      },
      {
        name: 'createdTime',
        label: 'Created',
        dataType: 'STRING',
        semantics: {
          conceptType: 'DIMENSION'
        }
      },
      {
        name: 'status',
        label: 'Status',
        dataType: 'STRING',
        semantics: {
          conceptType: 'DIMENSION'
        }
      },
      {
        name: 'category',
        label: 'Category',
        dataType: 'STRING',
        semantics: {
          conceptType: 'DIMENSION'
        }
      },
      {
        name: 'threadCount',
        label: 'Threads',
        dataType: 'NUMBER',
        semantics:{
          conceptType: 'METRIC'
        }
      }
    ]
  };
}

Functions for the OAUTH2 management

We will create a second file in the project called oauth.gs, it will take care of the authorization to Zoho’s account.

var oauth = {};
function getOAuthService() {
  
  var scriptProps = PropertiesService.getScriptProperties();
  var clientId = '[CLIENT-ID-OBTENIDO-EN-EL-PROXIMO-PASO]';
  var clientSecret =’ [CLIENT-SECRET-OBTENIDO-EN-EL-PRÓXIMO-PASO]’;
  return OAuth2.createService('ZohoDesk')
    .setAuthorizationBaseUrl('https://accounts.zoho.com/oauth/v2/auth')
    .setTokenUrl('https://accounts.zoho.eu/oauth/v2/token')
    .setGrantType('authorization_code')
    .setClientId(clientId)
    .setClientSecret(clientSecret)
    .setPropertyStore(PropertiesService.getUserProperties())
    .setScope('Desk.tickets.READ')
    .setCallbackFunction('authCallback');
}

function authCallback(request) {
  console.log(request);
  var authorized = getOAuthService().handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Success! You can close this tab.');
  } else {
    return HtmlService.createHtmlOutput('Denied. You can close this tab');
  }
}

function isAuthValid() {
  var service = getOAuthService();
  if (service == null) {
    return false;
  }
  return service.hasAccess();
}

function resetAuth() {
  var service = getOAuthService();
  service.reset();
}

function get3PAuthorizationUrls() {
  var service = getOAuthService();
  if (service == null) {
    return '';
  }
  return service.getAuthorizationUrl();
}

Create an app in Zoho’s console

We will access https://api-console.zoho.eu/ , add a new Served-based Applications type app, give it a name and indicate the Authorized Redirect URI with the following value

https://script.google.com/macros/d/[Google-apps-script-project-key]/usercallback.

Once this is done, we will obtain the Client Id and Secret that we will substitute in the code of the previous step.

If you need more information, you can check the official documentation.

Publish connector

Before publishing the connector, check that your manifest (appscript.json file) is similar to the following:

{
  "dependencies": {
    "libraries": [{
      "userSymbol": "OAuth2",
      "libraryId": "1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF",
      "version": "38"
    }]
  },
  "dataStudio": {
    "name": "Zoho Desk Connector",
    "logoUrl": "https://www.zohowebstatic.com/sites/default/files/styles/product-home-page/public/Desk-logo.png",
    "company": "Acme",
    "addonUrl": "Url to GitHub repository",
    "supportUrl": "Url to Supportr",
    "description": "Retrive basic info from Zoho Desk tickets"
  }
}

After this, we will go to File > Manage Versions and create a new version.

Once this step is done, we are ready to deploy the connector. To do so, go to Publish > Deploy from Manifest.

Once this is done, we will create a new deploy and link it to the latest version.

We will click on install add-on and after that, we will deploy the display created to copy the link to datastudio.

Any user who follows this link will go to their datastudio account to register a data source configured with this connector.

Authorizes access to the different accounts

Once we follow the above link, data studio will ask us for authorization to

• Access to the datastudio account
• Access to the zoho desk account

Analyze

You already have the basic ticket information available in DataStudio to create and share the reports you need.