Google Apps Scripts, Clasp and Data Studio

Update 2018/10/29Google has published a helpful new service that adds some syntactic sugar when writing Data Studio Connector scripts. This blog post has been updated to reflect some of the additional possibilities. 

//SEIBERT/MEDIA has been a Google Cloud Partner for some time now and our project can no longer be described as an experiment: We are actively investing in this area. It’s no wonder that our internal Google product team is being asked more often to provide transparency, metrics, and accountability.

This not only aligns with our company value, but it is important and valuable in order to understand how this business area is developing and how we want to react.

Enter Google Data Studio.

Google’s Data Studio can be used to model, statistically evaluate and graphically display generic data series. In this post, I’m going to limit it to a very simple example, but the possibilities are endless.

Connectors

So our goal is: We want to be able to keep an eye on our reseller status. Of course, I first need to get the data to be displayed. To start with the smallest example, let’s say we are interested in the number of currently active customers and trial instances with their user numbers.

Google Data Studio has many connectors available

Google Data Studio has many connectors available

Data sources are defined in Data Studio using connectors. This can be a simple CSV file, or another Google service, or simply an external MySQL database.

The reseller console is not available as a separate connector, but I can build a community connector easily.

Such a connector is a Google Apps Script (GAS) that must define the following four functions:

  • getConfig() is used by Data Studio to allow the user to configure the connector.
  • getSchema() tells Data Studio what metrics and dimensions to expect in a given connector request.
  • getData(request) gets the data. Data Studio dynamically decides which data it needs depending on the configuration (and visualization). You need to use the right request object so that the function returns only the data that Data Studio needs, or you’ll get an error instead of data.
  • getAuthType() returns the authentication method that Data Studio must use to execute getConfig(). Between most Google services, authentication is not usually required, including for this reseller console.

Clasp

Google’s own tool clasp is very useful when working with Google Apps Scripts.  It lets you manage GAS projects via the command line, just like Git. Here are the installation instructions for clasp. During setup, don’t forget to activate the Google Apps Script API, before running:

$ clasp login

Clasp will complain with meaningful error messages if you have forgotten a step, and it will usually provide a link, either to authorize the required functionality, OAuth2 yourself, or change any necessary settings.

If I hadn’t already created a project listed at https://script.google.com/home, I could now use clasp to create a new project called my-reseller-connector like this:

$ clasp create my-reseller-connector

TypeScript instead of JavaScript

You can use the helpful auto-completion provided by the GAS editor in your browser in your favorite IDE. Although you’ll have to write TypeScript instead of JavaScript, it’s quite easy to get used to this and the productivity boost is great.

Google Apps Script (for example, the Code.gs file that clasp created) is different to ES3 JavaScript primarily due to a number of singleton objects that are available which represent Google services.

Conveniently, clasp manages the build phase before upload all on its own. If it discovers ES6+, clasp automatically transpiles it with ts2gas. That means you don’t even have to install a TypeScript compiler in your project! Simply pull the GAS-typings into the project with:

$ npm install --save @types/google-apps-script

– and the IDE will provide all those sweet, sweet type hints (I tested it with Jetbrains IntelliJ IDEA and Microsofts Visual Studio code). More information about the TypeScript setup.

From here on, I’ll use TypeScript because it makes things so much easier, especially when someone is just starting out.

For the sake of clarity I have renamed Code.gs to config.ts. This shows both the IDE and clasp that it’s a TypeScript file, so I get type help and automatic compilation. The name itself is to make it clear to everyone that this file only deals with the configuration of the connector.

The configuration that is generated and returned is what Data Studio displays when an administrator sets up the connector as a data source. Since I only ever want to return the same kind of integer type quantities, the following is sufficient:

function getConfig(request) {
    return {};
}

The request object received by the function might contain language settings, which I may choose to respect in future. See more about how to expand your configuration.

Next I need to define the schema that we will use to return the data. You can use any of these data types. For this example however, I’ll limit the schema to the four number metrics: active instances and corresponding active users, as well as trial instances and corresponding trial users. The schema.ts might look as follows:

const STATIC_SCHEMA = [
    {
        name: 'totalSubscriptions',
        dataType: 'NUMBER',
        semantics: {
            conceptType: 'METRIC',
        },
    },
    {
        name: 'totalUsers',
        dataType: 'NUMBER',
        semantics: {
            conceptType: 'METRIC',
        },
    },
    {
        name: 'trialSubscriptions',
        dataType: 'NUMBER',
        semantics: {
            conceptType: 'METRIC',
        }
    },
    {
        name: 'trialUsers',
        dataType: 'NUMBER',
        semantics: {
            conceptType: 'METRIC',
        }
    },
];

function getSchema(request) {
    return {
        schema: STATIC_SCHEMA,
    };
}

For now, that’s going to be simple enough. For more complicated schemas, this builder will be useful.

Either way, getSchema also receives a request object. In this case it will contain information about the configuration of the selected connector. Again, I’ll ignore that.

Since I know that additional authorization won’t be required – neither from an administrator nor a user – I can define an auth.ts file as follows:

function getAuthType() {
    return {
        type: 'NONE',
    };
}

So I can debug more easily, I want to write the log statements to both the local log and to Stackdriver’s console.log. To do this, I’ll add a single function to util.ts:

function log(message: any) {
   Logger.log(message);
   console.log(message);
}

Of course, structured logging is available in Stackdriver, but I’ll leave that for another post.

Configure the connector

Finally, I can define which data should be returned when the connector is queried. To do this, I need to create a data.ts. I’ll start by generating a dynamic schema from the static one, because if too much or the wrong kind of data is returned, Data Studio will refuse to process it.

///<reference path="util.ts"/>
///<reference path="schema.ts"/>

function getData(request) {
    let schema = request.fields.map(function(field) {
        for (let i = 0; i < STATIC_SCHEMA.length; i++) {
            if (STATIC_SCHEMA[i].name == field.name) {
                return STATIC_SCHEMA[i];
            }
        }
    });

Next I have to generate data rows that contain only one value – the value that Data Studio queried for earlier.

    let rows = [];
    for (let i = 0; i < schema.length; i++) {
        switch (schema[i].name) {
            case 'totalSubscriptions':
                rows.push({values: [getTotalSubscriptions()]});
                break;
            case 'totalUsers':
                rows.push({values: [getTotalUsers()]});
                break;
            case 'trialSubscriptions':
                rows.push({values: [getTrialSubscriptions()]});
                break;
            case 'trialUsers':
                rows.push({values: [getTrialUsers()]});
                break;
        }
    }

Finally, I need to return these generated rows in the way that Data Studio expects:

    return {
        schema: schema,
        rows: rows,
    };
}

All that remains is to calculate the actual values. For example, in order to calculate the active instances, I’ll have to search for the string “G Suite” in the SKU name, because we don’t want to include (the now discontinued) Postini SKUs:

function getTotalSubscriptions(): number {
    let pageToken;
    let totalSubscriptions = 0;
    do {
        // @ts-ignore
        let result = AdminReseller.Subscriptions.list({pageToken: pageToken});
        const subscriptions = result.subscriptions.filter(function (subscription) {
            return subscription.skuName.indexOf('G Suite') >= 0
        });
        totalSubscriptions += subscriptions.length;
        pageToken = result.nextPageToken;
    } while (pageToken);
    log(`Reporting ${totalSubscriptions} total subscriptions`);
    return totalSubscriptions;
}

If you’ve been paying attention, you’ll have noticed the compiler statement // @ts-ignore. This is necessary because Google is still actively adding to the list of all “advanced” Google services (as explained in this Github issue).

Speaking of advanced services: AdminReseller is one of them! I have to activate it by modifying appsscript.json. The easiest way to do this is either with clasp (clasp apis enable reseller) or in the web interface (Resources > Advanced Google Services). You may need to confirm this via OAuth2.

But I’m not finished yet. I can calculate the trial instances by simply extending the filter to a query for subscription.trialSettings.isInTrial:

function getTrialSubscriptions(): number {
    let pageToken;
    let trialSubscriptions = 0;
    do {
        // @ts-ignore
        let result = AdminReseller.Subscriptions.list({pageToken: pageToken});
        const subscriptions = result.subscriptions.filter(function (subscription) {
            return subscription.skuName.indexOf('G Suite') >= 0 && subscription.trialSettings.isInTrial
        });
        trialSubscriptions += subscriptions.length;
        pageToken = result.nextPageToken;
    } while (pageToken);
    log(`Reporting ${trialSubscriptions} trial subscriptions`);
    return trialSubscriptions;
}

Retrieving user numbers is a little tricky: the name of the field containing the correct number depends on the license model. Also, I want to exclude the “free” plan:

function getTotalUsers(): number {
    let pageToken;
    let totalUsers = 0;
    do {
        // @ts-ignore
        let result = AdminReseller.Subscriptions.list({pageToken: pageToken});
        const subscriptions = result.subscriptions.filter(function (subscription) {
            return subscription.skuName.indexOf('G Suite') >= 0
        });
        for (let subscription of subscriptions) {
            const planName: string = subscription.plan.planName;
            if (planName.indexOf('ANNUAL') >= 0) {
                totalUsers += subscription.seats.numberOfSeats;
            }
            else if (planName == 'FLEXIBLE' || planName == 'TRIAL') {
                totalUsers += subscription.seats.licensedNumberOfSeats;
            }
        }
        pageToken = result.nextPageToken;
    } while (pageToken);
    log(`Reporting ${totalUsers} total users`);
    return totalUsers;
}

Finally, I’ll do the same for the trial users:

function getTrialUsers(): number {
    let pageToken;
    let trialUsers = 0;
    do {
        // @ts-ignore
        let result = AdminReseller.Subscriptions.list({pageToken: pageToken});
        const subscriptions = result.subscriptions.filter(function (subscription) {
            return subscription.skuName.indexOf('G Suite') >= 0 && subscription.trialSettings.isInTrial
        });
        for (let subscription of subscriptions) {
            const planName: string = subscription.plan.planName;
            if (planName.indexOf('ANNUAL') >= 0) {
                trialUsers += subscription.seats.numberOfSeats;
            }
            else if (planName == 'FLEXIBLE' || planName == 'TRIAL') {
                trialUsers += subscription.seats.licensedNumberOfSeats;
            }
        }
        pageToken = result.nextPageToken;
    } while (pageToken);
    log(`Reporting ${trialUsers} trial users`);
    return trialUsers;
}

This should be good enough as a small test. Now, how can we use this connector in Data Studio?

Using the connector in Data Studio

First, make sure to save your changes with clasp push.  Only then can you create a new deployment with clasp deploy (also in your browser under Publish > Publish from manifest).

This deployment now has a unique ID, which you can find with clasp deployments or by clicking on Get ID. You can now enter this Deployment ID in the Data Studio “Add data source” screen (via link Developers). One click on the Validate button and if everything worked, I can now select the connector:

When I do, I’m redirected directly to a page with a blue Connect button, because our connector doesn’t require any configuration:

After clicking on the Connect button, I can now see which Data Studio fields are exposed via the static schema:

Finally, I can create a report and use the newly created data source just like any other. Since I only want to display some numbers for now, a scorecard visualization is appropriate:

Goal achieved! I can now see the status of our G Suite business at any time. And since everything in our organization is shared by default, my colleagues can see it too – I can immediately share the link with all key stakeholders so they can monitor the report.

Your Google Cloud partner

Are you interested in using Google technologies and solutions to collaborate at work? If you have any questions about Google Cloud or products, get in contact with us! We are an official Google Cloud Partner and would be delighted to provide obligation-free advice about the introduction, licensing and productive use of G Suite in your company, including custom design and implementation of applications with Google AppEngine or other environments.

Further information

Large companies and organizations are more frequently choosing Google G Suite
Apps for Google G Suite and the Google App Maker – Scripting
Come along to our monthly Google Cloud breakfast event in Wiesbaden (presentations are typically in German)