How to Get Real-Time Oil Prices in Google Sheets

1Get Your Free API Key

Sign up at oilpriceapi.com to get your API key. The free trial includes 10,000 requests over 7 days—plenty to test and get started.

2Open Apps Script Editor

1. Open your Google Sheets spreadsheet
2. Click Extensions in the menu bar
3. Select Apps Script
4. Delete any existing code in the editor

3Paste This Code

Copy the entire code block below and paste it into the Apps Script editor. Replace YOUR_API_KEY with your actual API key.

/**
 * Gets the current price for a commodity from Oil Price API
 * @param {string} commodityCode - The commodity code (e.g., "WTI_USD", "BRENT_USD")
 * @return {number} The current price
 * @customfunction
 */
function OIL_PRICE(commodityCode) {
  var API_KEY = 'YOUR_API_KEY'; // Replace with your actual API key
  var url = 'https://api.oilpriceapi.com/v1/prices/latest?by_code=' + commodityCode;

  var options = {
    'method': 'GET',
    'headers': {
      'Authorization': 'Token ' + API_KEY,
      'Content-Type': 'application/json'
    },
    'muteHttpExceptions': true
  };

  try {
    var response = UrlFetchApp.fetch(url, options);
    var responseCode = response.getResponseCode();

    if (responseCode !== 200) {
      return 'Error: ' + responseCode;
    }

    var data = JSON.parse(response.getContentText());

    if (data.status === 'success' && data.data && data.data.price) {
      return data.data.price;
    } else {
      return 'Error: Invalid response';
    }
  } catch (error) {
    return 'Error: ' + error.message;
  }
}

/**
 * Gets diesel price for a US state
 * @param {string} stateCode - Two-letter state code (e.g., "TX", "CA")
 * @return {number} The current diesel price per gallon
 * @customfunction
 */
function DIESEL_PRICE(stateCode) {
  var API_KEY = 'YOUR_API_KEY'; // Replace with your actual API key
  var url = 'https://api.oilpriceapi.com/v1/diesel-prices?state=' + stateCode;

  var options = {
    'method': 'GET',
    'headers': {
      'Authorization': 'Token ' + API_KEY,
      'Content-Type': 'application/json'
    },
    'muteHttpExceptions': true
  };

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

    if (data.data && data.data.regional_average) {
      return data.data.regional_average.price;
    } else {
      return 'Error: Invalid response';
    }
  } catch (error) {
    return 'Error: ' + error.message;
  }
}

/**
 * Force refresh all custom functions
 * Run this manually or set up a trigger
 */
function refreshAllPrices() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  range.setValues(values);
}

4Save and Use the Function

1. Press Ctrl+S (or Cmd+S on Mac) to save
2. Go back to your spreadsheet
3. In any cell, type: =OIL_PRICE("WTI_USD")
4. Press Enter—the current WTI price should appear!

Step 1: Open Triggers

1. In Apps Script, click the clock icon (Triggers) in the left sidebar
2. Click + Add Trigger in the bottom right

Step 2: Configure the Trigger

• Choose function: refreshAllPrices
• Event source: Time-driven
• Type: Minutes timer, Hour timer, or Day timer
• Interval: Every 5 minutes, Every hour, etc.

⚠️"Error: Exception: Request failed"

Your API key is invalid or wasn't replaced in the code.

Fix: Open Apps Script and replace YOUR_API_KEY with your actual API key from your dashboard.

⚠️"#ERROR!" appears in cell

The custom function isn't recognized by Google Sheets.

Fix: Make sure you saved the Apps Script code (Ctrl+S). Try closing and reopening the spreadsheet.

⚠️"Loading..." never completes

Google Sheets is having trouble executing the script.

Fix: Check Apps Script's execution log (View → Executions). You may need to re-authorize the script—look for a yellow banner at the top.

⚠️"Error: 429" (Rate Limit)

You've exceeded your API request limit.

Fix: Reduce refresh frequency (use hourly instead of every 5 minutes) orupgrade your plan.

⚠️Prices not updating automatically

Custom functions don't auto-refresh by default.

Fix: Set up a time-based trigger as shown in the Auto-Refresh section.

⚠️"Authorization required" popup

The script needs permission to make external API calls.

Fix: Click "Review permissions" and authorize the script. This is safe—the script only makes calls to oilpriceapi.com.

How do I get oil prices in Google Sheets?

Use our Apps Script custom function. Go to Extensions → Apps Script, paste the OIL_PRICE function code, save, then use =OIL_PRICE("WTI_USD") in any cell to get real-time oil prices.

Is getting oil prices in Google Sheets free?

Yes, we offer a free 7-day trial with 10,000 API requests. This is enough for testing and light usage. Paid plans start at $19/month for 10,000 requests.

What commodities can I get in Google Sheets?

You can get WTI Crude, Brent Crude, Dubai Crude, OPEC Basket, Natural Gas (Henry Hub), Coal, Heating Oil, Gasoline, and diesel prices for all 50 US states—over 40 commodities total.

How often do oil prices update in Google Sheets?

With Apps Script triggers, you can set prices to update every 5 minutes, hourly, or daily. WTI and Brent prices update on our servers every 5 minutes during market hours.

Can I use this on the Google Sheets mobile app?

Yes! Once you set up the custom function on desktop, it works on any device including the Google Sheets mobile app for iOS and Android.

Do I need programming knowledge?

No programming knowledge is required. Just copy and paste our ready-to-use code into Apps Script and replace the API key placeholder with your actual key.

Can I share the spreadsheet with colleagues?

Yes! The custom functions work for anyone who has access to the spreadsheet. They'll see the live prices without needing their own API key—your key is embedded in the script.

What's the difference between Google Sheets and Excel?

Both work great for oil prices. Google Sheets is cloud-based and works on any device. Excel requires our desktop add-in. Choose based on your existing workflow.