Looker Studio Integration

Create professional oil price dashboards and commodity market reports with Looker Studio (formerly Google Data Studio) and OilPriceAPI. Ideal for enterprise commodities trading analytics, logistics cost reporting, and aviation fuel management.

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")

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

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

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

Frequently Asked Questions

How do I authenticate OilPriceAPI in Looker Studio?

Looker Studio doesn't directly support custom API headers, so authentication depends on your integration method:

Via Google Sheets (Recommended):

1. Set up a Google Sheet with Apps Script integration
2. Store your API key in Script Properties (secure)
3. Connect Looker Studio to the Google Sheet as a data source
4. The API key remains hidden from Looker Studio users

Via BigQuery:

1. Use a Cloud Function with your API key stored in Secret Manager
2. The function loads data into BigQuery
3. Connect Looker Studio to BigQuery

Via Community Connector:

1. Include an API key input field in your connector's config
2. Users enter their own API key when connecting
3. Store it using tableau.password equivalent in the connector

See the Authentication Guide for detailed setup.

What's the rate limit when using Looker Studio with OilPriceAPI?

OilPriceAPI limits depend on your plan: Free (1,000/month), Hobby (10,000/month), Starter (50,000/month), Professional (100,000/month). Configure your data pipeline to stay within limits:

For high-traffic dashboards, use Google Sheets or BigQuery with scheduled data loading to control API usage.

Can I automate price updates in Looker Studio?

Yes, automation depends on your data source:

Google Sheets Source:

1. Set up Apps Script time-driven triggers
2. Looker Studio automatically sees updated data
3. Control refresh with trigger frequency

BigQuery Source:

1. Use Cloud Scheduler to trigger a Cloud Function
2. The function fetches from OilPriceAPI and loads to BigQuery
3. Looker Studio always shows the latest BigQuery data

Community Connector:

• Data refreshes each time the report loads
• No scheduling needed, but uses API calls per viewer

What happens if the API call fails in Looker Studio?

Error handling depends on your integration method:

Google Sheets:

• Apps Script errors logged in Executions
• Sheet shows last successful data
• Looker Studio continues displaying cached values

BigQuery:

• Cloud Function failures logged in Cloud Logging
• BigQuery table retains previous data
• Dashboard shows last successful load timestamp

Community Connector:

• Connector throws an error to Looker Studio
• Dashboard shows error message to users
• Add try/catch in your connector code for graceful handling

Best practice: Add a "Last Updated" field to your data so users know data freshness.

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
• Power BI Integration - Microsoft BI alternative
• Tableau Integration - Enterprise BI alternative
• Commodities Trading API - Trading and analytics data
• Aviation Fuel API - Jet fuel price tracking
• Logistics Fuel API - Supply chain cost analysis
• Fleet Management API - Fleet fuel tracking
• Python Developer Guide - Custom code integration
• Authentication Guide - API key setup and security
• Historical Prices API - Time-series data endpoints
• Commodities List - Available commodity codes