Looker Studio Integration

Create professional oil price dashboards and commodity market reports with Looker Studio (formerly Google Data Studio) and OilPriceAPI.

Overview

Looker Studio is Google's free business intelligence and data visualization platform that enables you to create interactive, shareable dashboards. By integrating OilPriceAPI with Looker Studio, you can build professional energy market reports, track crude oil trends, and share commodity insights with stakeholders across your organization.

Key Benefits:

• Free, professional-grade oil price dashboards
• Real-time WTI and Brent crude visualizations
• Easy sharing via links or embedding
• Seamless integration with Google ecosystem
• Combine oil data with Google Analytics, BigQuery, and more

Prerequisites

Before you begin, ensure you have:

1. Google Account with access to Looker Studio
2. OilPriceAPI account with an active API key (Sign up)
3. Basic familiarity with Looker Studio interface

Integration Methods

Looker Studio supports multiple ways to connect to external APIs:

Method 1: Google Sheets Connector (Recommended)

The easiest way to get OilPriceAPI data into Looker Studio is through Google Sheets.

Step 1: Set Up Google Sheets Data Source

First, create a Google Sheets integration following our Google Sheets Guide.

Quick setup:

1. Create a new Google Sheet
2. Open Extensions > Apps Script
3. Add the OilPriceAPI script from our Google Sheets guide
4. Configure your API key in Script Properties
5. Use functions like =OILPRICES("WTI_USD,BRENT_CRUDE_USD,NATURAL_GAS_USD")

[Screenshot: Google Sheets populated with commodity price data]

Step 2: Connect Looker Studio to Google Sheets

1. Open Looker Studio
2. Click Create > Data source
3. Select Google Sheets connector
4. Choose your spreadsheet and worksheet
5. Configure field types:
   • price: Number
   • created_at: Date
   • code: Text (Dimension)
   • currency: Text (Dimension)
6. Click Connect

Step 3: Create Your Dashboard

1. Click Create > Report
2. Add your Google Sheets data source
3. Build visualizations:
   • Scorecard: Current WTI price
   • Time Series Chart: Price trends
   • Table: Multi-commodity comparison
   • Combo Chart: Price with volume indicators

[Screenshot: Professional Looker Studio dashboard showing crude oil price analytics]

Method 2: BigQuery Connector

For enterprise-scale oil price analytics, load data into BigQuery:

Step 1: Set Up BigQuery Table

Create a table to store oil price data:

CREATE TABLE `your-project.oil_prices.daily_prices` (
  price FLOAT64,
  formatted STRING,
  currency STRING,
  code STRING,
  created_at TIMESTAMP,
  type STRING,
  source STRING,
  loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

Step 2: Create a Cloud Function to Load Data

Deploy a Cloud Function that fetches from OilPriceAPI:

import functions_framework
from google.cloud import bigquery
import requests
from datetime import datetime

@functions_framework.http
def fetch_oil_prices(request):
    """Cloud Function to fetch oil prices and load to BigQuery"""

    # Get API key from environment or Secret Manager
    api_key = os.environ.get('OILPRICE_API_KEY')

    commodities = [
        'WTI_USD', 'BRENT_CRUDE_USD', 'NATURAL_GAS_USD',
        'HEATING_OIL_USD', 'DIESEL_USD', 'GASOLINE_USD'
    ]

    # Fetch prices from OilPriceAPI
    url = 'https://api.oilpriceapi.com/v1/prices/latest'
    headers = {'Authorization': f'Token {api_key}'}
    params = {'by_code': ','.join(commodities)}

    response = requests.get(url, headers=headers, params=params)
    data = response.json()

    if data['status'] != 'success':
        return f"API Error: {data.get('message', 'Unknown error')}", 500

    # Prepare rows for BigQuery
    client = bigquery.Client()
    table_id = 'your-project.oil_prices.daily_prices'

    prices = data['data'] if isinstance(data['data'], list) else [data['data']]

    rows = []
    for price in prices:
        rows.append({
            'price': price['price'],
            'formatted': price['formatted'],
            'currency': price['currency'],
            'code': price['code'],
            'created_at': price['created_at'],
            'type': price.get('type', 'spot_price'),
            'source': price.get('source', 'oilpriceapi')
        })

    # Insert into BigQuery
    errors = client.insert_rows_json(table_id, rows)

    if errors:
        return f"BigQuery errors: {errors}", 500

    return f"Loaded {len(rows)} price records", 200

Step 3: Schedule with Cloud Scheduler

Create a Cloud Scheduler job to run the function:

gcloud scheduler jobs create http fetch-oil-prices \
  --schedule="0 * * * *" \
  --uri="https://REGION-PROJECT.cloudfunctions.net/fetch_oil_prices" \
  --http-method=GET \
  --time-zone="America/New_York"

Step 4: Connect Looker Studio to BigQuery

1. In Looker Studio, click Create > Data source
2. Select BigQuery connector
3. Navigate to your table: your-project.oil_prices.daily_prices
4. Click Connect

Method 3: Community Connector

For real-time data without intermediary storage, build a custom Community Connector:

Create the Connector

// Code.gs - Looker Studio Community Connector for OilPriceAPI

var cc = DataStudioApp.createCommunityConnector();

function getConfig() {
  var config = cc.getConfig();

  config
    .newTextInput()
    .setId('apiKey')
    .setName('API Key')
    .setHelpText('Your OilPriceAPI key')
    .setPlaceholder('opa_live_xxxxx');

  config
    .newTextInput()
    .setId('commodities')
    .setName('Commodities')
    .setHelpText('Comma-separated commodity codes')
    .setPlaceholder('WTI_USD,BRENT_CRUDE_USD');

  config
    .newSelectSingle()
    .setId('timeRange')
    .setName('Time Range')
    .addOption(config.newOptionBuilder().setLabel('Latest').setValue('latest'))
    .addOption(config.newOptionBuilder().setLabel('Past Day').setValue('past_day'))
    .addOption(config.newOptionBuilder().setLabel('Past Week').setValue('past_week'))
    .addOption(config.newOptionBuilder().setLabel('Past Month').setValue('past_month'))
    .addOption(config.newOptionBuilder().setLabel('Past Year').setValue('past_year'));

  config.setDateRangeRequired(false);

  return config.build();
}

function getSchema() {
  return cc.newGetSchemaResponse()
    .setFields([
      cc.newDimension()
        .setId('code')
        .setName('Commodity Code')
        .setType(cc.FieldType.TEXT),
      cc.newDimension()
        .setId('currency')
        .setName('Currency')
        .setType(cc.FieldType.TEXT),
      cc.newDimension()
        .setId('type')
        .setName('Price Type')
        .setType(cc.FieldType.TEXT),
      cc.newDimension()
        .setId('created_at')
        .setName('Timestamp')
        .setType(cc.FieldType.YEAR_MONTH_DAY_HOUR),
      cc.newMetric()
        .setId('price')
        .setName('Price')
        .setType(cc.FieldType.NUMBER)
        .setAggregation(cc.AggregationType.AVG)
    ])
    .build();
}

function getData(request) {
  var configParams = request.configParams;
  var apiKey = configParams.apiKey;
  var commodities = configParams.commodities;
  var timeRange = configParams.timeRange;

  var baseUrl = 'https://api.oilpriceapi.com/v1/prices/';
  var url = baseUrl + (timeRange === 'latest' ? 'latest' : timeRange);
  url += '?by_code=' + encodeURIComponent(commodities);

  if (timeRange !== 'latest') {
    url += '&interval=1d';
  }

  var options = {
    method: 'get',
    headers: {
      'Authorization': 'Token ' + apiKey
    },
    muteHttpExceptions: true
  };

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  if (data.status !== 'success') {
    throw new Error('API Error: ' + (data.message || 'Unknown error'));
  }

  var prices = timeRange === 'latest'
    ? [data.data]
    : data.data.prices;

  var requestedFields = request.fields.map(function(f) { return f.name; });

  var rows = prices.map(function(price) {
    var row = [];
    requestedFields.forEach(function(field) {
      switch(field) {
        case 'code':
          row.push(price.code);
          break;
        case 'currency':
          row.push(price.currency);
          break;
        case 'type':
          row.push(price.type || 'spot_price');
          break;
        case 'created_at':
          row.push(price.created_at.replace(/[-:TZ]/g, '').substring(0, 10));
          break;
        case 'price':
          row.push(price.price);
          break;
      }
    });
    return { values: row };
  });

  return cc.newGetDataResponse()
    .setFields(request.fields)
    .addAllRows(rows)
    .build();
}

function getAuthType() {
  return cc.newAuthTypeResponse()
    .setAuthType(cc.AuthType.NONE)
    .build();
}

function isAdminUser() {
  return false;
}

Building Effective Dashboards

Recommended Components

Dashboard Layout Best Practices

+----------------------------------+
|  Header: Oil Price Dashboard     |
|  Last Updated: [Date control]    |
+----------------------------------+
|  [WTI]   |  [Brent]  |  [NatGas] |
|  $72.45  |  $76.23   |  $2.89    |
|  +1.2%   |  +0.8%    |  -2.1%    |
+----------------------------------+
|                                  |
|  [Price Trend Chart - Full Width]|
|                                  |
+----------------------------------+
|  [Commodity Table] | [Category]  |
|                    | [Breakdown] |
+----------------------------------+

Calculated Fields

Create calculated fields for advanced analytics:

// Price Change Percentage
(Price - LAG(Price, 1)) / LAG(Price, 1) * 100

// WTI-Brent Spread
CASE
  WHEN code = "WTI_USD" THEN Price
  ELSE NULL
END -
CASE
  WHEN code = "BRENT_CRUDE_USD" THEN Price
  ELSE NULL
END

// Price Status
CASE
  WHEN Price > 80 THEN "High"
  WHEN Price < 60 THEN "Low"
  ELSE "Normal"
END

[Screenshot: Production-ready Looker Studio dashboard with multiple oil price visualizations]

Sharing and Embedding

Share Dashboard

1. Click Share button in Looker Studio
2. Add collaborators by email
3. Set permissions: View, Edit, or Manage
4. Generate shareable link

Embed in Websites

1. Click File > Embed report
2. Copy the embed code
3. Paste into your website HTML:

<iframe
  width="100%"
  height="600"
  src="https://lookerstudio.google.com/embed/reporting/YOUR_REPORT_ID"
  frameborder="0"
  style="border:0"
  allowfullscreen
></iframe>

Rate Limits and Best Practices

Data Refresh Considerations:

Optimization Tips:

• Use Google Sheets with scheduled triggers for predictable API usage
• Cache data in BigQuery for high-traffic dashboards
• Limit Community Connector use to internal dashboards
• Use the interval parameter for historical data to reduce response size

For detailed rate limit information, see our Rate Limiting Guide.

Troubleshooting

Data Not Updating

Google Sheets source:

• Check Apps Script trigger is running
• Verify API key is valid
• Check Google Sheets data range

BigQuery source:

• Verify Cloud Function is executing
• Check Cloud Scheduler job status
• Review BigQuery error logs

Slow Dashboard Loading

• Reduce date range in filters
• Use data extracts instead of live connections
• Aggregate historical data with interval parameter

Authentication Errors

• Verify API key in configuration
• Check for typos in Authorization header
• Confirm key has sufficient request quota

FAQ

Is Looker Studio free to use?

Yes, Looker Studio is free for individual users and small teams. Enterprise features are available through Looker Studio Pro.

How often can I refresh data in Looker Studio?

Data refresh depends on your data source:

• Google Sheets: As often as your Apps Script triggers run
• BigQuery: Based on your data loading schedule
• Community Connector: Each time the report loads

Can I use Looker Studio for real-time oil prices?

Looker Studio is optimized for periodic data refresh rather than real-time streaming. For real-time prices, see our WebSocket documentation.

How do I track WTI-Brent spread in Looker Studio?

1. Include both WTI_USD and BRENT_CRUDE_USD in your data
2. Create a calculated field for the spread difference
3. Use a Combo Chart with both prices and the spread

Can I combine oil prices with other data sources?

Yes, Looker Studio supports data blending. You can combine OilPriceAPI data with:

• Google Analytics for website correlation
• BigQuery for enterprise data
• Other Google Sheets for custom metrics

What's the difference between Looker and Looker Studio?

• Looker Studio (formerly Data Studio): Free, self-service BI tool
• Looker: Enterprise BI platform with advanced modeling (Google Cloud product)

Both can integrate with OilPriceAPI; this guide focuses on Looker Studio.

Related Resources

• Google Sheets Integration - Data source setup for Looker Studio
• Authentication Guide - API key setup and security
• Historical Prices API - Time-series data endpoints
• Commodities List - Available commodity codes
• Power BI Integration - Microsoft BI alternative
• Tableau Integration - Enterprise BI alternative