Google Sheets Integration

Bring the latest available oil and commodity prices, with their source timestamps, into Google Sheets for spreadsheet analysis, automated reports, and team collaboration. Useful for gas station price tracking, logistics cost analysis, and fleet management budgeting.

Overview

Google Sheets is a versatile cloud-based spreadsheet platform for tracking commodity prices, building financial models, and sharing energy market data across teams. There are three ways to connect OilPriceAPI to Google Sheets, in order of recommendation:

1. The OilPriceAPI add-on from the Google Workspace Marketplace — installed formulas, per-spreadsheet key storage, response caching. Start here.
2. IMPORTDATA() — a single formula, no script, key visible in the URL.
3. A self-managed bound Apps Script — copy/paste code you own and maintain. Advanced use only; read the trust note before choosing it.

Key benefits:

• Latest available crude oil prices, each with a source timestamp, in a familiar spreadsheet format
• Automatic refresh with Apps Script triggers
• Share price data with team members
• Build custom formulas combining price data with business logic
• Works with Google Workspace or a personal Google account

Refresh cadence and dataset availability vary by source, market hours, and plan — inspect the source timestamp (OILPRICE_INFO, or the created_at field in API responses) rather than assuming a fixed update interval.

Prerequisites

Before you begin, ensure you have:

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

Recommended: The OilPriceAPI Add-on

The OilPriceAPI for Google Sheets™ add-on on the Google Workspace Marketplace is the maintained integration. It installs the formulas for you, stores your key in Apps Script properties scoped to one spreadsheet, and caches responses so repeated formula recalculation does not burn through your plan's request allowance.

Install and configure

1. Open the Marketplace listing and click Install (or in a sheet: Extensions > Add-ons > Get add-ons, search for "OilPriceAPI").
2. In your spreadsheet, open Extensions > OilPriceAPI > Configure API Key.
3. Paste an API key created for this spreadsheet (Dashboard → API Keys) and save.

Add-on formulas

OILPRICE(code) also works for backward compatibility. Pair any price with OILPRICE_INFO so the sheet shows the value's own source timestamp instead of implying it is live.

Where the add-on keeps your key

• The key is stored in Apps Script properties scoped to that one spreadsheet (document properties, plus a compatibility copy in the configuring user's user properties keyed by spreadsheet ID so Google's custom-function context can retrieve it without exposing it to other spreadsheets).
• The add-on's sidebar never reads the stored key back into the page, and its request diagnostics never contain the key.
• The key is usable by collaborators even though it is not displayed to them. Anyone who can edit the spreadsheet can enter add-on formulas, and those formulas spend requests against the configured key. Configure the key as the spreadsheet owner, use a separate key per sheet, and revoke that key in your Dashboard when you stop sharing the sheet or an editor leaves.

If a spreadsheet also contains a copy/paste bound script defining OILPRICE, remove one of the two — do not run the add-on and a bound-script copy with the same function names in the same spreadsheet.

Quick Formula-Only Option: IMPORTDATA

If you cannot install add-ons, a single formula works using CSV export:

Step 1: Get Your API Key

1. Log in to your OilPriceAPI Dashboard
2. Navigate to API Keys section
3. Create a new key named "Google Sheets"
4. Copy your API key

For detailed setup, see our Authentication Guide.

Step 2: Use IMPORTDATA with CSV Format

OilPriceAPI supports CSV format for easy spreadsheet import:

=IMPORTDATA("https://api.oilpriceapi.com/v1/prices/latest?by_code=WTI_USD&format=csv&api_key=YOUR_API_KEY")

Why the key goes in the URL here: IMPORTDATA() cannot send request headers, so the query parameter is the only authentication a spreadsheet formula can perform.

What the API accepts: the api_key query parameter works on read requests only (GET and HEAD). Endpoints that change something — creating or revoking API keys, managing webhooks, price alerts, or your subscription — accept the Authorization: Token YOUR_API_KEY header and nothing else. If a request carries an Authorization header, the header is used and the query parameter is ignored. That way a URL that escapes can only read what the key could already read.

Anyone who can see the sheet can see the key. A URL carrying a credential is stored in the sheet itself, in your browser history, and in intermediate proxy and CDN logs. Two habits make that safe:

• Create a separate key for each sheet (Dashboard → API Keys) so you can revoke one without breaking anything else. Be aware of what a per-sheet key does not limit: any valid key can also read GET /v1/account and GET /v1/api-keys, which return your account email, plan tier, usage, and the masked list of all your keys. So a leaked sheet URL exposes that sheet's data plus your account identity and key inventory — not that sheet alone. Full key values are never returned by a read request, so a leaked URL cannot harvest your other keys.
• Prefer the add-on above whenever you can. It sends the key in a request header and keeps it in Apps Script properties rather than in a cell, so the key never appears in the sheet's own contents.

Advanced: Self-Managed Bound Apps Script

If you want to own and modify the integration code yourself, you can paste a bound Apps Script into the spreadsheet. This is the self-managed path: you maintain the code, and you own the trust model that comes with it.

Step 1: Open Apps Script Editor

1. Open your Google Sheet
2. Click Extensions > Apps Script
3. Delete any existing code in the editor

Step 2: Add the OilPriceAPI Script

Copy and paste this script:

/**
 * OilPriceAPI Integration for Google Sheets (self-managed)
 * Fetches the latest available oil and commodity prices
 */

// Store your API key in Script Properties.
// Go to Project Settings > Script Properties > Add Property
// Key: OILPRICE_API_KEY, Value: your_api_key
// NOTE: every editor of this sheet can open this script and read that value.

const API_BASE_URL = "https://api.oilpriceapi.com/v1";

/**
 * Get the latest available price for a commodity
 * @param {string} commodityCode - Commodity code (e.g., "WTI_USD", "BRENT_CRUDE_USD")
 * @return {number} Latest available price
 * @customfunction
 */
function OILPRICE(commodityCode) {
  const apiKey =
    PropertiesService.getScriptProperties().getProperty("OILPRICE_API_KEY");

  if (!apiKey) {
    throw new Error(
      "API key not configured. Set OILPRICE_API_KEY in Script Properties.",
    );
  }

  const url = `${API_BASE_URL}/prices/latest?by_code=${encodeURIComponent(commodityCode)}`;

  const options = {
    method: "get",
    headers: {
      Authorization: `Token ${apiKey}`,
    },
    muteHttpExceptions: true,
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());

    if (data.status === "success") {
      return data.data.price;
    } else {
      throw new Error(data.message || "API error");
    }
  } catch (error) {
    return `Error: ${error.message}`;
  }
}

/**
 * Get formatted price with currency symbol
 * @param {string} commodityCode - Commodity code
 * @return {string} Formatted price string
 * @customfunction
 */
function OILPRICE_FORMATTED(commodityCode) {
  const apiKey =
    PropertiesService.getScriptProperties().getProperty("OILPRICE_API_KEY");

  const url = `${API_BASE_URL}/prices/latest?by_code=${encodeURIComponent(commodityCode)}`;

  const options = {
    method: "get",
    headers: {
      Authorization: `Token ${apiKey}`,
    },
    muteHttpExceptions: true,
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());

    if (data.status === "success") {
      return data.data.formatted;
    } else {
      throw new Error(data.message || "API error");
    }
  } catch (error) {
    return `Error: ${error.message}`;
  }
}

/**
 * Get multiple commodity prices at once
 * @param {string} commodityCodes - Comma-separated commodity codes
 * @return {Array} Array of [code, price, formatted, currency]
 * @customfunction
 */
function OILPRICES(commodityCodes) {
  const apiKey =
    PropertiesService.getScriptProperties().getProperty("OILPRICE_API_KEY");

  const url = `${API_BASE_URL}/prices/latest?by_code=${encodeURIComponent(commodityCodes)}`;

  const options = {
    method: "get",
    headers: {
      Authorization: `Token ${apiKey}`,
    },
    muteHttpExceptions: true,
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());

    if (data.status === "success") {
      // Handle single or multiple commodities
      const prices = Array.isArray(data.data) ? data.data : [data.data];
      return prices.map((p) => [p.code, p.price, p.formatted, p.currency]);
    } else {
      throw new Error(data.message || "API error");
    }
  } catch (error) {
    return [[`Error: ${error.message}`]];
  }
}

/**
 * Fetch historical prices and write to a range
 * @param {string} commodityCode - Commodity code
 * @param {string} period - Time period (past_day, past_week, past_month, past_year)
 * @param {string} targetCell - Starting cell reference (e.g., "A1")
 */
function fetchHistoricalPrices(commodityCode, period, targetCell) {
  const apiKey =
    PropertiesService.getScriptProperties().getProperty("OILPRICE_API_KEY");

  const url = `${API_BASE_URL}/prices/${period}?by_code=${encodeURIComponent(commodityCode)}&interval=1d`;

  const options = {
    method: "get",
    headers: {
      Authorization: `Token ${apiKey}`,
    },
    muteHttpExceptions: true,
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());

    if (data.status !== "success") {
      throw new Error(data.message || "API error");
    }

    const prices = data.data.prices;
    const sheet = SpreadsheetApp.getActiveSheet();
    const range = sheet.getRange(targetCell);

    // Prepare data with headers
    const output = [["Date", "Price", "Currency", "Type"]];
    prices.forEach((p) => {
      output.push([new Date(p.created_at), p.price, p.currency, p.type]);
    });

    // Write to sheet
    const targetRange = sheet.getRange(
      range.getRow(),
      range.getColumn(),
      output.length,
      output[0].length,
    );
    targetRange.setValues(output);

    SpreadsheetApp.getActiveSpreadsheet().toast(
      `Loaded ${prices.length} price records`,
      "OilPriceAPI",
    );
  } catch (error) {
    SpreadsheetApp.getActiveSpreadsheet().toast(
      `Error: ${error.message}`,
      "OilPriceAPI Error",
    );
  }
}

/**
 * Create a custom menu for OilPriceAPI functions
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("OilPriceAPI")
    .addItem("Refresh All Prices", "refreshAllPrices")
    .addItem("Load WTI Historical (Past Month)", "loadWTIHistory")
    .addItem("Load Brent Historical (Past Month)", "loadBrentHistory")
    .addSeparator()
    .addItem("Configure API Key", "showApiKeyDialog")
    .addToUi();
}

/**
 * Refresh all OILPRICE formulas in the sheet
 */
function refreshAllPrices() {
  // Force recalculation of custom functions
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getDataRange();

  // Touch the sheet to trigger recalculation
  SpreadsheetApp.flush();

  SpreadsheetApp.getActiveSpreadsheet().toast(
    "Prices refreshed",
    "OilPriceAPI",
  );
}

function loadWTIHistory() {
  fetchHistoricalPrices("WTI_USD", "past_month", "A1");
}

function loadBrentHistory() {
  fetchHistoricalPrices("BRENT_CRUDE_USD", "past_month", "A1");
}

function showApiKeyDialog() {
  const ui = SpreadsheetApp.getUi();
  const result = ui.prompt(
    "Configure API Key",
    "Enter your OilPriceAPI key:",
    ui.ButtonSet.OK_CANCEL,
  );

  if (result.getSelectedButton() === ui.Button.OK) {
    PropertiesService.getScriptProperties().setProperty(
      "OILPRICE_API_KEY",
      result.getResponseText(),
    );
    ui.alert("API key saved successfully!");
  }
}

Step 3: Configure Your API Key

1. In Apps Script, click Project Settings (gear icon)
2. Scroll to Script Properties
3. Click Add script property
4. Set Property: OILPRICE_API_KEY, Value: your_api_key
5. Click Save

Remember: this property is readable by every editor of the sheet. Use a dedicated per-sheet key.

Step 4: Use Custom Functions

Return to your spreadsheet and use the custom functions:

=OILPRICE("WTI_USD")           // latest available WTI value
=OILPRICE("BRENT_CRUDE_USD")   // latest available Brent value
=OILPRICE_FORMATTED("WTI_USD") // same value with currency symbol
=OILPRICES("WTI_USD,BRENT_CRUDE_USD,NATURAL_GAS_USD")

Each value is the latest the API holds for that series at fetch time; the response's created_at field carries the source timestamp if you want to show freshness alongside the number.

Setting Up Automatic Refresh

Time-Driven Triggers

Set up automatic price updates with Apps Script triggers:

1. In Apps Script, click Triggers (clock icon)
2. Click Add Trigger
3. Configure:
   • Function: refreshAllPrices
   • Event source: Time-driven
   • Type: Hour timer or specific time
4. Click Save

Recommended schedules based on your plan:

Many series update far less often than any of these schedules — diesel and gasoline assessments can update on a roughly daily cadence — so a faster trigger returns the same value with the same source timestamp, not fresher data.

On-Edit Trigger for Manual Refresh

Add a refresh button to your sheet:

function onEdit(e) {
  // Check if the edit was in the refresh button cell
  if (e.range.getA1Notation() === "A1" && e.value === "REFRESH") {
    refreshAllPrices();
    e.range.setValue(""); // Clear the cell
  }
}

Building a Complete Oil Price Tracker

Sample Spreadsheet Layout

=NOW() records when the sheet recalculated, not when the source published the value. To show source freshness, add a column carrying the response's created_at timestamp (the add-on's OILPRICE_INFO returns it directly).

Conditional Formatting

Add visual indicators for price changes:

1. Select the Change % column
2. Click Format > Conditional formatting
3. Add rules:
   • Green background for values > 0
   • Red background for values < 0

Price Alerts with Email

/**
 * Check prices and send email alerts
 * Set up as a time-driven trigger
 */
function checkPriceAlerts() {
  const apiKey =
    PropertiesService.getScriptProperties().getProperty("OILPRICE_API_KEY");
  const alertEmail = "your-email@example.com";

  // Define your alert thresholds
  const alerts = [
    { code: "WTI_USD", above: 80, below: 60 },
    { code: "BRENT_CRUDE_USD", above: 85, below: 65 },
  ];

  alerts.forEach((alert) => {
    const price = OILPRICE(alert.code);

    if (price > alert.above) {
      MailApp.sendEmail(
        alertEmail,
        `Oil Price Alert: ${alert.code} above $${alert.above}`,
        `Current ${alert.code} price: $${price}\nThreshold: $${alert.above}`,
      );
    }

    if (price < alert.below) {
      MailApp.sendEmail(
        alertEmail,
        `Oil Price Alert: ${alert.code} below $${alert.below}`,
        `Current ${alert.code} price: $${price}\nThreshold: $${alert.below}`,
      );
    }
  });
}

Rate Limits and Best Practices

Optimizing API Usage:

• Cache prices in cells rather than making repeated API calls
• Fetch multiple commodities in one request — OILPRICE_TABLE() in the add-on (up to 25 codes), OILPRICES() in the bound script. A multi-code request is billed as one request.
• Set appropriate refresh intervals based on your plan
• Use historical endpoints with interval parameter for trend data

Security Best Practices:

• Keep API keys out of cells and formulas wherever possible — the add-on's per-spreadsheet storage is the safest of the three methods
• Know who holds the key: sheet editors can read a bound script's Script Properties, and anyone who can see an IMPORTDATA formula can read the key in its URL
• Use a separate API key per sheet so one can be revoked without breaking the others; revoke keys in the Dashboard when sharing changes
• Monitor usage in your OilPriceAPI Dashboard

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

Troubleshooting

Custom Function Not Working

"Unknown function OILPRICE":

• Add-on: confirm it is installed for this account and enabled in this document
• Bound script: ensure Apps Script is saved, then refresh the spreadsheet (Ctrl+R or Cmd+R)
• Check for script errors in Apps Script execution log
• If both the add-on and a bound-script copy are present, their function names collide — remove one

"API key not configured":

• Add-on: Extensions > OilPriceAPI > Configure API Key
• Bound script: set OILPRICE_API_KEY in Script Properties and verify it saved

Rate Limit Exceeded

"429 Too Many Requests":

• Reduce refresh frequency
• Cache results in cells instead of making repeated calls
• Batch codes into one request (OILPRICE_TABLE / OILPRICES)
• Consider upgrading your plan

Slow Loading

• Custom functions have execution time limits
• Use batched functions for bulk fetches
• Consider using triggers instead of cell formulas for large datasets

Frequently Asked Questions

How do I authenticate OilPriceAPI in Google Sheets?

Three methods, by decreasing security:

Method 1: Marketplace add-on (recommended) — install the add-on, then Extensions > OilPriceAPI > Configure API Key. The key is stored in Apps Script properties scoped to that spreadsheet and is never displayed back, though editors of the sheet can still spend requests with it through formulas.

Method 2: Self-managed bound script — store the key in Script Properties:

const apiKey =
  PropertiesService.getScriptProperties().getProperty("OILPRICE_API_KEY");

This keeps the key out of the sheet's cells, but anyone with edit access can open the bound script and read the property value. Restrict edit access and use a per-sheet key.

Method 3: IMPORTDATA — the key rides in the formula's URL, visible to everyone who can view the sheet:

=IMPORTDATA("https://api.oilpriceapi.com/v1/prices/latest?by_code=WTI_USD&format=csv&api_key=YOUR_API_KEY")

Use only for personal sheets, with a dedicated key you can revoke.

What's the rate limit when using Google Sheets with OilPriceAPI?

OilPriceAPI limits depend on your plan: Free (50/day), Developer (10,000/month), Starter (50,000/month), Professional (100,000/month). Configure Google Sheets refresh frequency to stay within your limits:

Batch multiple commodities into a single request (OILPRICE_TABLE in the add-on, OILPRICES in the bound script) to reduce usage — one request can carry many codes.

Can I automate price updates in Google Sheets?

Yes, use Apps Script time-driven triggers:

1. In Apps Script, click Triggers (clock icon)
2. Click Add Trigger
3. Configure:
   • Function: refreshAllPrices
   • Event source: Time-driven
   • Type: Hour timer or Day timer
4. Click Save

You can also create an onEdit trigger for manual refresh buttons or use onOpen to refresh when the sheet is opened.

What happens if the API call fails in Google Sheets?

When API calls fail in Google Sheets:

Custom Functions (OILPRICE, etc.):

• The cell displays Error: [message]
• Previous values are not retained
• Add error handling in your Apps Script:

try {
  const response = UrlFetchApp.fetch(url, options);
  // process response
} catch (error) {
  return `Error: ${error.message}`;
}

Time-Driven Triggers:

• Failed executions appear in Apps Script > Executions log
• Set up email notifications for failed triggers
• Consider adding a fallback or retry mechanism

Common failures: Rate limit exceeded, invalid API key, network timeout.

Can I use this with Google Sheets on mobile?

Yes, the spreadsheet with oil prices works on Google Sheets mobile apps. However, custom functions execute on Google's servers, so you'll see the results but can't edit the Apps Script from mobile.

How do I share a sheet with live prices safely?

It depends on the method:

• Add-on: the key value is never displayed to collaborators, but anyone who can edit the sheet can trigger formulas that use it. Share with edit access only where that is acceptable.
• Bound script: editors can open the script and read the key in Script Properties; view-only collaborators cannot. Sharing a sheet with edit access shares the key.
• IMPORTDATA: everyone who can view the sheet can read the key in the formula.

In every case, give each shared sheet its own key and revoke it from the Dashboard when sharing changes.

What's the difference between the add-on, IMPORTDATA, and a bound script?

Can I use Google Sheets for historical price charts?

Yes — OILPRICE_HISTORY() in the add-on, or fetchHistoricalPrices() in the bound script, loads dated rows you can chart. See our Historical Prices API for available time ranges.

Why do prices show as cached/stale?

Google Sheets caches custom function results, and the add-on additionally caches API responses briefly to save your request allowance. To force refresh:

• Edit the cell and press Enter
• Use the custom menu: OilPriceAPI > Refresh All Prices
• Set up time-driven triggers for automatic refresh

Also check the value's source timestamp: many series update on a cadence of minutes to a day, so an unchanged number is often the source's latest, not a caching problem.

Related Resources

• Looker Studio Integration - Advanced Google ecosystem analytics
• Power BI Integration - Microsoft BI tool integration
• Airtable Integration - Database-style tracking
• Zapier Integration - Automate price workflows
• Gas Station API - Retail fuel pricing solutions
• Fleet Management API - Fleet fuel cost tracking
• Logistics Fuel API - Supply chain cost analysis
• Fuel Cost Estimator - Trip cost calculations
• Authentication Guide - API key setup and security
• Historical Prices API - Time-series data endpoints
• Commodities List - Available commodity codes