Google Sheets Integration

/**
 * OilPriceAPI Integration for Google Sheets
 * Fetches real-time oil and commodity prices
 */

// Store your API key in Script Properties for security
// Go to Project Settings > Script Properties > Add Property
// Key: OILPRICE_API_KEY, Value: your_api_key

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

/**
 * Get the latest price for a commodity
 * @param {string} commodityCode - Commodity code (e.g., "WTI_USD", "BRENT_CRUDE_USD")
 * @return {number} Current 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 (e.g., "$72.45")
 * @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!');
  }
}

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
  }
}

/**
 * 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 = '[email protected]';

  // 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}`
      );
    }
  });
}