Creating a Custom Data Connector For Google Looker Studio

Are you looking to fetch data from an external API and display it in Google Looker Studio, Don’t know where to start? Look no further than Apps Script, a powerful development platform provided by Google.

Google Apps Script is a rapid application development platform that makes it fast and easy to create business applications that integrate with Google Workspace. You write code in JavaScript and have access to built-in libraries for favorite Google Workspace applications like Gmail, Calendar, Drive, and more.

In this blog post, we’ll explore how to use Apps Script to create a data connector that fetches data from an external API and displays it in Google Looker Studio lets you build live, interactive dashboards with beautiful data visualizations, for free. Fetch your data from a variety of sources and create unlimited reports in Looker Studio, with full editing and sharing capabilities on Google Looker Studio.

Google Loker Studio is a powerful tool for visualizing data and creating informative reports. However, sometimes the data you need to include in your reports are unavailable through the built-in connectors provided by Google. In these cases, you can create a custom data connector to fetch data from external sources and integrate it into your reports.

We’ll walk through the process of creating a custom data connector that fetches data from an external API and includes it in Google Looker Studio Reports.

Break Down the Code:

The code we’ll be using consists of several functions that work together to retrieve data from an external API and return it in a format that can be used in the Goole Looker Studio. Let’s take a closer look at each function.

The code starts by defining by getConfig function. This function returns a configuration object that can be used to specify configuration parameters for your data source. In this case, the configuration object is empty, which means that no configuration parameters are needed.

function getConfig(request) {
var config = {
  configParams: [
  ]
};
return config;
};

Next, the code defines is Schema array that describes the schema of the data that the data source will return. The schema defines the name, datatype, and semantics of each field in the data. In this case, the schema includes fields for id, title, description, price, discount percentage, rating, stock, brand, and category.

var Schema = [
{
  name: 'id',
  label: 'ID',
  dataType: 'NUMBER',
  semantics: {
    conceptType: 'DIMENSION'
  }
},
{
  name: 'title',
  label: 'Title',
  dataType: 'STRING',
  semantics: {
    conceptType: 'DIMENSION'
  }
}// rest all the schema
];

The getSchema function returns the schema object defined in Schema. This function is called the Data Looker Studio when the user sets up the data source.

function getSchema(request) {
return {schema: Schema};
};

The getData function retrieves data from an external API and returns it in a format that can be used by Data Studio. The function starts by using the UrlFetchApp service to fetch data from the API. It then parses the JSON response and extracts the data into an array of rows.

function getData(request) {
var url = "https://dummyjson.com/products";
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json).products;

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

The getData function also uses the request object to determine which fields to include in the returned data. It iterates through the request. fields array, looking for fields with matching names in the Schema array. For each matching field, it adds the corresponding data to the returned row.

var rows = [];
data.forEach(function(item) {
  var row = [];
  request.fields.forEach(function(field) {
    switch (field.name) {
      case 'id':
        row.push(item.id);
        break;
      case 'title':
        row.push(item.title);
        break;
      case 'description':
        row.push(item.description);
        break;
      case 'price':
        row.push(item.price);
        break;
      case 'discountPercentage':
        row.push(item.discountPercentage);
        break;
      case 'rating':
        row.push(item.rating);
        break;
      case 'stock':
        row.push(item.stock);
        break;
      case 'brand':
        row.push(item.brand);
        break;
      case 'category':
        row.push(item.category);
        break;
      default:
        row.push('');
    }
  });
  rows.push({values: row});
});
return {
  schema: dataSchema,
  rows: rows
};
};

Finally, the getAuthType function returns an object that specifies the authentication type for the data source. In this case, the authentication type is set to NONE, which means that no authentication is required.

function getAuthType() {
var response = {
  "type": "NONE"
};
return response;
}

Using this code, you can create a custom data source in Data Studio that retrieves data from an external API and displays it in your reports.

Project Manifest File

The manifest file contains information about your Community Connector that is required to deploy and use your connector in Looker Studio.

The manifest (appsscript.json) is a JSON file in your Apps Script connector project. It contains certain information about your Community Connector that is required to deploy and use your connector in Looker Studio.

{
"timeZone": "Asia/Kolkata",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"dataStudio": {
  "name":"Product_API_CONNECTOR",
  "company":"DataSlush",
  "logoUrl":"https://dataslush.com/wp-content/uploads/2022/09/WhatsApp-Image-2022-09-03-at-6.52.29-PM-768x277.jpeg",
  "companyUrl":"https://dataslush.com/",
  "addonUrl": "https://dataslush.com/",
  "supportUrl":"https://dataslush.com/",
  "description": "Connect Data Studio to web service",
  "sources": ["CUSTOM_JSON"],
  "authType": ["NONE"],
  "feeType": ["FREE"]
}
}

Conclusion

Connecting external APIs to Data Studio can be a powerful way to extend the capabilities of your reports. By using the code snippet provided in this blog post, you can easily connect an external API to Data Studio and start visualizing your data in new and exciting ways.

How DataSlush can help you?

Unleash the Power of Custom Data Connectors

In our quest for seamless data integration and dynamic visualization, we understand that sometimes the built-in connectors just won’t cut it. That’s where DataSlush steps in to elevate your experience with Google Looker Studio.

Tailored Solutions with Google Apps Script

Our team at DataSlush specializes in crafting custom data connectors using the robust Google Apps Script platform. Whether you’re looking to fetch data from a niche external API or streamline your data pipeline, our experts have got you covered.