Google Looker Studio is a powerful platform for creating interactive dashboards. However, sometimes you need to fetch data from external APIs or other data sources that are not supported by default. This is where custom connectors come in. In this guide, we’ll walk you through building a connector to fetch external data, using Bitcoin prices as an example.
All the code for this project will be written and edited in the ) and makes it available in Looker Studio. The process involves defining configuration options, schema, data fetching logic, and minimal authentication.
1. Defining Configuration Parameters
To allow users to customize the behavior of the connector, such as providing an API key or setting the number of days of data to fetch, we define a configuration using getConfig.
function getConfig(request) {
var config = cc.getConfig();
config.newTextInput()
.setId('apiKeyInput')
.setName('API Key:')
.setHelpText('cryptocompare.com API key')
.setPlaceholder('YOUR-API-KEY');
config.newTextInput()
.setId('dayLimit')
.setName('Number of Days')
.setHelpText('Number of days to fetch Bitcoin price data')
.setPlaceholder('30');
return config.build();
}
Here, two fields are defined:
- apiKeyInput: To enter the API key for authenticating with the external API.
- dayLimit: To specify the number of days of historical data to fetch.
2. Defining the Data Schema
The schema determines the fields (columns) in the data returned by the connector.
function getSchema(request) {
var schema = [
{ name: 'time', label: 'Time', dataType: 'NUMBER' },
{ name: 'close', label: 'Close Price', dataType: 'NUMBER' }
];
return { schema: schema };
}
- time: A numeric field representing timestamps.
- close: A numeric field representing the closing price of Bitcoin.
This schema structure can be modified to fit the data you need from your API.
3. Fetching Data from an External API
The getData function is where the data is fetched from the API, transformed, and returned to Looker Studio.
function getData(request) {
var apiKey = request.configParams.apiKeyInput;
var dayLimit = request.configParams.dayLimit || '30';
if (!apiKey) {
throw new Error("API Key is missing. Please provide a valid API key.");
}
var url = "https://min-api.cryptocompare.com/data/v2/histoday?fsym=BTC&tsym=USD&limit=" + dayLimit + "&api_key=" + apiKey;
var response = UrlFetchApp.fetch(url);
var json = JSON.parse(response.getContentText());
var data = json.Data.Data;
var schema = [
{ name: 'time', label: 'Time', dataType: 'NUMBER' },
{ name: 'close', label: 'Close Price', dataType: 'NUMBER' }
];
var rows = data.map(function (item) {
return { values: [item.time, item.close] };
});
return { schema: schema, rows: rows };
}
The API URL dynamically incorporates the user-provided API key and the number of days (dayLimit).
The API response is parsed, and the relevant fields are extracted into rows.
4. Authentication and Admin Settings
This connector does not require authentication, but you must specify it explicitly. Additionally, the isAdminUser function is required by Looker Studio.
function getAuthType() {
return cc.newAuthTypeResponse()
.setAuthType(cc.AuthType.NONE)
.build();
}
function isAdminUser() {
return false;
}
- getAuthType specifies that no authentication is required.
- isAdminUser simply returns false since this connector does not need admin-specific features.
Full Code
Here is the complete code for the connector:
var cc = DataStudioApp.createCommunityConnector();
function getConfig(request) {
var config = cc.getConfig();
config.newTextInput()
.setId('apiKeyInput')
.setName('API Key:')
.setHelpText('cryptocompare.com API key')
.setPlaceholder('YOUR-API-KEY');
config.newTextInput()
.setId('dayLimit')
.setName('Number of Days')
.setHelpText('Number of days to fetch Bitcoin price data')
.setPlaceholder('30');
return config.build();
}
function getSchema(request) {
var schema = [
{ name: 'time', label: 'Time', dataType: 'NUMBER' },
{ name: 'close', label: 'Close Price', dataType: 'NUMBER' }
];
return { schema: schema };
}
function getData(request) {
var apiKey = request.configParams.apiKeyInput;
var dayLimit = request.configParams.dayLimit || '30';
if (!apiKey) {
throw new Error("API Key is missing. Please provide a valid API key.");
}
var url = "https://min-api.cryptocompare.com/data/v2/histoday?fsym=BTC&tsym=USD&limit=" + dayLimit + "&api_key=" + apiKey;
var response = UrlFetchApp.fetch(url);
var json = JSON.parse(response.getContentText());
var data = json.Data.Data;
var schema = [
{ name: 'time', label: 'Time', dataType: 'NUMBER' },
{ name: 'close', label: 'Close Price', dataType: 'NUMBER' }
];
var rows = data.map(function (item) {
return { values: [item.time, item.close] };
});
return { schema: schema, rows: rows };
}
function getAuthType() {
return cc.newAuthTypeResponse()
.setAuthType(cc.AuthType.NONE)
.build();
}
function isAdminUser() {
return false;
}
appsscript.json sample:
{
"timeZone": "",
"dependencies": {
"libraries": []
},
"dataStudio": {
"name": "Bitcoin Historical Price",
"logoUrl": "https://cdn-icons-png.flaticon.com/512/825/825540.png",
"company": "Test Company",
"companyUrl": "https://example.com/",
"addonUrl": "https://example.com/",
"supportUrl": "https://example.com/",
"description": "Get bitcoin price using JSON and cryptocompare.com API"
},
"oauthScopes": [
"https://www.googleapis.com/auth/script.external_request"
],
"webapp": {
"executeAs": "USER_DEPLOYING",
"access": "MYSELF"
}
}
Deployment
- Save the above code in Google Apps Script.
- Deploy the connector by selecting Deploy > New Deployment.
- Select Web app Add-on, Click Deploy.
- Click on Looker Studio generated link.
- Test the connector in Looker Studio by configuring the API key and number of days.
SOCIAL SHARE CARD GENERATOR