What is Looker Studio?

Looker Studio by Google [Formerly known as data studio] is a business intelligence (BI) and data exploration tool that allows users to create and manage reports, dashboards, and data models. It provides an intuitive interface for exploring and visualizing data as well as collaboration between stakeholders, making it easier for businesses to make data-driven decisions. 

Tools like Looker Studio are used by data analysts, business analysts, and other decision-makers in an organization to:

1. Query and explore data without needing to write SQL code (though the option is usually there for those who need it).
2. Build interactive dashboards that can be easily shared across teams or embedded in other applications.
3. Create reports that can be scheduled for regular delivery to key stakeholders.
4. Analyze data from multiple sources, often by blending them into a single dashboard or report.
5. Develop data models that serve as a single source of truth across the company.

Looker is particularly strong in its ability to create a data model layer that serves as an abstraction over the raw data. This makes it easier for business users to work with data without needing to understand its underlying structure.

‍Why build a connector for Looker Studio?

As a business analyst, one would want to be able to connect a data source into looker studio and bring all of their data into a single report. However, different sources of data have their own way of organizing data. Instead of requesting data directly from these sources, google has allowed developers to use AppScript to request data from any source, and then transform that data into the shape that looker studio recognises.

‍Building our first Looker Studio connector:

For our first connector, we’ll be using PokeApi - an api that gives us information about different Pokemons. Before we get into building the connector for looker studio, let’s look at the data we get from the api. The first endpoint that we’ll look at is `https://pokeapi.co/api/v2/pokemon`If we navigate to the link or make a ‘GET’ request to the given endpoint, we see that the response has a key ‘results’. In it, there are 20 pokemons. Each pokemon has a ‘name’ attribute and a ‘url’ attribute. The data isn’t very useful, is it?

Let’s look at the first pokemon. It’s Bulbasaur, and it has a url - https://pokeapi.co/api/v2/pokemon/1. Let’s navigate to that link.

The data we have in that link is far more detailed than the data we get from all pokemons! Let’s see what data we can use.

We can certainly fetch the parameters base_experience, height and weight directly. And then there’s stats which give us 6 more parameters. Not directly though. Let’s look at the structure of the stats object.

To bring these together, we can build an object. That object will have the name of the pokemon, the base_experience, height and weight directly from the returned object. Then there are the stats on hp, attack, defense, special_attack, special_defense and speed which we get from the stats array. Finally, let’s also extract the type of the pokemon and its abilities. Once we call the api for each pokemon, we must return an object that has all of these properties inside it. 

The question now is, where do we write this code? Answer - Google AppScript.

Let’s create a new AppScript project. Inside that, let’s create a file named ‘PokemonData.gs’

Let’s define a getPokemonData() function in our AppScript file that transforms the data according to our needs

Without getting into the nitty gritties of the code, it reaches out to the URL of each pokemon to get its stats and transforms the data into an object of this form:

[Yes, the dataToPush variable is a little too direct, and I’m open for suggestions!]

With this, let’s proceed to feed this data into our connector!‍

Building our first connector for Looker Studio:

Let’s get down to building our first connector. There are four functions that need to be defined in AppScript for any connector:

1. getAuthType()
2. getConfig()
3. getSchema()
4. getData(request) Yes, the getData function takes in an argument (request)

Let’s do that. We’ll open a new Script file in our project and name it ‘Connector’. In that file, let’s define these functions to begin with. We’ll leave the body empty for now.

Next, outside these functions, we need to initialize an object that we’ll be using inside of all of these functions. That object is the return value from the function createCommunityConnector() from the class DataStudioApp, like this:

That looks good for starting off. Now we need to populate these functions.‍

Function getAuthType() ‍

The purpose of this function is to authorize the user to use our looker studio connector. Google, by default, uses its own Oauth flow to authorize the user from their end, and in addition to Google’s authorization, some connectors will need an additional Oauth flow, like Facebook, GitHub, Shopify, etc. For the Pokemon connector, we’ll not be using any additional authorization, so we’ll set it to none

In order to do that, we need to access the AuthType enums from cc, the global object that we just defined. This enumerated list has all types of authorization Google allows in their connector.

Once we have access to the AuthType enums, we configure the cc object, setting the authorization type to NONE.

For OAuth, please refer to this connector

Here’s the code:

Function getConfig()

The purpose of this function is to set up any configurations that are necessary for our looker studio connector. For instance, let’s assume that the api provides data for pokemons once we pass in the type in the query parameter (Which isn’t actually the case). We would need to choose the pokemon type and pass it off as a parameter in our search query. For that, we would fetch all types of pokemon, and set up an input where the user would then select the pokemon type.

But from this api, we don’t need to do any of that, so let’s just return the config as it is.

Code:

Function getSchema()

This is the first function which we’ll study in detail. Any data we fetch into the connector must have a defined schema - a blueprint. If we’re going to display our data in the form of a table, then what would be the column headers? What type of data would each column contain? Is it a number? Is it text? Date? Also, what would the code identify each column as? We need to define this.Let’s look at how we’re transforming the data once we get it from the api:

Each key in this object is a column header. The keys name, abilities and type are text fields, and the rest of them are numbers. Let’s define an array of fields that we’ll be using:

Code:

Let’s look at the allFields array. Each element in the array is an object that has the keys id, name, description and type. We’ll be using them to build our schema. Before we populate the getSchema() function, we’ll define another function getFields() to populate the fields.

Now, we’ll iterate over each element of the allFields and bring them into the fields variable we just defined. In order to do that, we need to determine whether the field we’re configuring is a metric or a dimension.

A metric is a field which we can get some form of aggregate on. We can calculate the sum, average, maximum, minimum etc in a metric. In our allFields array, each field that has a type ’number’ is a metric. So, in our getFields() function, we’ll iterate over our allFields array, and check the type key in each element. If it’s a ‘number’, we’ll add it as a metric. Else, we’ll add it as a dimension.
Regardless of whether it’s a metric or dimension, we’ll set the id, name and description of each field to the corresponding key in the object. 

And in our getSchema() function, we’ll use the returned object from the getFields() function and build our schema.

Function getData(request)

This function is called by Looker studio to fetch the data from the source we’re connecting to. And here’s where we’re going to use the getPokemonData() that we had defined to transform our pokemon data into a readable object. 

In the getData() function, we get a request parameter. Through the request parameter, we have access to the fields that the connector has requested and the date range. For our connector, we’ll need access to the requested fields.

The request parameter has a ‘fields’ array which contains the requested fields. Each item in that array is an object, and we’re interested in the property ‘name’. So we’ll map that array to get only the ‘name’ of the field. We’ll store that in the requestedFieldIds array

Then we’ll fetch data from the pokemon api and transform it in the form we can read by using the getPokemonData() function. The output we get from this function is JSON data, so we’ll need to parse it using JavaScript’s inbuilt JSON.parse() function. We’ll store this in the pokemonData array.

Once we do that, we need to do one final thing - map that data into an array of arrays. For each item of the array, we need to push in the data relevant to the requested field in that specific order. Here again, we map over the pokemonData array and for each item in the array, we map over the requestedFieldIds array and return the corresponding value in the item object

Finally, we’ll add the rows and the requested fields in the cc object. 

The final code will look something like this:

Almost there! There’s one last thing that we need to do.

Once the connector is used in Looker studio, the getData() function gets called everytime there’s a change in the fields requested. In our code, that means every time a field changes, we make 51 api calls to pokeApi and get the same data again. That’s inefficient. What we can do instead is use userProperties from the PropertiesService class and store the data there. This is how the getData() function would look like with this change:

And there we go! We have successfully built our first connector for Looker Studio! 

‍Publishing the Looker Studio connector on Google’s partner connectors.

Once we’ve built the connector for Looker studio, we need to publish the connector into Google’s “partner connectors” section so that it’s accessible to everyone. Here’s the link to Google’s requirements for publishing a partner connector for Looker studio: https://developers.google.com/looker-studio/connector/pscc-requirements‍

We’ll go through the steps from what we need to do specifically for our connector.

‍App Manifest:

Before we go about publishing our connector, we need to make sure that the appsscript.json file is visible in our script and has the right information. So, we’ll first click “Project Settings” at the left of the AppScript screen. There, we select the Show "appsscript.json" manifest file in editor checkbox.

Here’s how our manifest would look like, when we follow all their guidelines.

AppScript:

1. FIrst, we need to share the apps script file with view access to these emails:some text

a. data-studio-contrib-qa@googlegroups.com
b. data-studio-contrib@google.com

2. Now we need to deploy the AppScript. For that,  we'll need to switch this Apps Script project to use a user-managed Google Cloud Platform (GCP) project. For that we’ll follow these steps:some text

a. Go to Google Cloud Platform console
b. If you don’t have an account, click on ‘start for free’. 
c. Enter your credentials and create an account (You would need your credit card information for this)
d. A new project gets created with the name “My First Project”. In the menu bar on the left, navigate to “APIs and Services”
e. Once you land on the page, click on “OAuth consent screen”
f. Fill up the necessary details in the screen and click on “Save and Continue”
g. In the ‘scopes’ section, add "https://www.googleapis.com/auth/script.external_request". 
h. Click on “Save and Continue” in all subsequent screens, and finally go back to Dashboard
This will enable us to switch to a user-managed GCP project. However, for publishing, that project needs to be verified. For that we follow these steps:
i. In the dashboard, click on “Publish App”
j. Accept the terms in the popup, and then click on “Prepare for Verification”
k. Provide the necessary links and the domain
l. In the “How will the scopes be used?” question, type:
“The scope will be used to connect to an external api to fetch data into our Looker Studio connector”
m. Create a screencast of the connector and publish to YouTube. Enter the screencast link in the “Demo video” input.
n. And that’s it. Wait for the project to be verified
Once the project is verified, 
o. Open the AppScript file
p. Click on “Project settings” in the left NavBar
q. Under “Google Cloud Platform (GCP) Project”, click on “Change project”
r. Find the project number from your Google Cloud Console. Copy it and paste it into the input.

3. Once that’s done, we create a deployment named “Production”. For that, we follow these stepssome text

a. Open the AppScript file
b. On the top right corner, click the “Deploy” button.

c. Name the Deployment ‘Production’

Finally, after these steps have been taken, publish your looker studio connector by filling up this form!