Google Sheets Integration

// === TickAtlas for Google Sheets ===
// Paste this into Extensions > Apps Script

var API_KEY = PropertiesService.getScriptProperties().getProperty("CLAW_API_KEY");
var BASE_URL = "https://tickatlas.com/v1";

/**
 * Get a single indicator value for a symbol.
 * @param {string} symbol - e.g. "EURUSD"
 * @param {string} indicator - e.g. "RSI_14"
 * @param {string} timeframe - e.g. "H1" (default)
 * @return {number} The indicator value
 * @customfunction
 */
function CLAW_INDICATOR(symbol, indicator, timeframe) {
  timeframe = timeframe || "H1";
  var url = BASE_URL + "/indicator"
    + "?symbol=" + encodeURIComponent(symbol)
    + "&indicator=" + encodeURIComponent(indicator)
    + "&timeframe=" + encodeURIComponent(timeframe);

  var options = {
    "method": "get",
    "headers": { "X-API-Key": API_KEY },
    "muteHttpExceptions": true
  };

  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() !== 200) {
    return "Error: " + response.getResponseCode();
  }

  var data = JSON.parse(response.getContentText());
  return data.value;
}

/**
 * Get the RSI value for a symbol.
 * @customfunction
 */
function CLAW_RSI(symbol, timeframe) {
  return CLAW_INDICATOR(symbol, "RSI_14", timeframe || "H1");
}

/**
 * Get the MACD histogram value for a symbol.
 * @customfunction
 */
function CLAW_MACD(symbol, timeframe) {
  return CLAW_INDICATOR(symbol, "MACD_hist", timeframe || "H1");
}

/**
 * Get a live price quote (bid, ask, or spread).
 * @customfunction
 */
function CLAW_QUOTE(symbol, field) {
  field = field || "bid";
  var url = BASE_URL + "/quote?symbol=" + encodeURIComponent(symbol);

  var options = {
    "method": "get",
    "headers": { "X-API-Key": API_KEY },
    "muteHttpExceptions": true
  };

  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() !== 200) return "Error";

  var data = JSON.parse(response.getContentText());
  return data[field];
}

/**
 * Get the AI market summary for a symbol.
 * @customfunction
 */
function CLAW_SUMMARY(symbol, timeframe) {
  timeframe = timeframe || "H1";
  var url = BASE_URL + "/summary?symbol=" + encodeURIComponent(symbol)
    + "&timeframe=" + encodeURIComponent(timeframe);

  var options = {
    "method": "get",
    "headers": { "X-API-Key": API_KEY },
    "muteHttpExceptions": true
  };

  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() !== 200) return "Error";

  var data = JSON.parse(response.getContentText());
  return data.bias + " (" + data.confidence + "%)";
}

// === Auto-Refresh Trigger ===
// Run this function once to set up automatic refresh every 5 minutes

function setupAutoRefresh() {
  // Remove any existing triggers first
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === "refreshData") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }

  // Create a new time-based trigger
  ScriptApp.newTrigger("refreshData")
    .timeBased()
    .everyMinutes(5)
    .create();

  Logger.log("Auto-refresh trigger created: every 5 minutes");
}

function refreshData() {
  // Force recalculation by writing a timestamp
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange("A1").setNote("Last refresh: " + new Date().toISOString());

  // Force all custom functions to re-evaluate
  SpreadsheetApp.flush();
}

function removeAutoRefresh() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === "refreshData") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
  Logger.log("Auto-refresh trigger removed");
}

// === Market Dashboard Builder ===
// Creates a formatted dashboard with data for multiple symbols

function buildDashboard() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var symbols = ["EURUSD", "GBPUSD", "USDJPY", "XAUUSD", "BTCUSD"];
  var timeframe = "H1";

  // Headers
  sheet.getRange("A1").setValue("Symbol");
  sheet.getRange("B1").setValue("Bid");
  sheet.getRange("C1").setValue("Ask");
  sheet.getRange("D1").setValue("RSI");
  sheet.getRange("E1").setValue("MACD");
  sheet.getRange("F1").setValue("AI Bias");
  sheet.getRange("G1").setValue("Updated");
  sheet.getRange("A1:G1").setFontWeight("bold");

  for (var i = 0; i < symbols.length; i++) {
    var row = i + 2;
    sheet.getRange("A" + row).setValue(symbols[i]);
    sheet.getRange("B" + row).setFormula('=CLAW_QUOTE("' + symbols[i] + '","bid")');
    sheet.getRange("C" + row).setFormula('=CLAW_QUOTE("' + symbols[i] + '","ask")');
    sheet.getRange("D" + row).setFormula('=CLAW_RSI("' + symbols[i] + '","' + timeframe + '")');
    sheet.getRange("E" + row).setFormula('=CLAW_MACD("' + symbols[i] + '","' + timeframe + '")');
    sheet.getRange("F" + row).setFormula('=CLAW_SUMMARY("' + symbols[i] + '","' + timeframe + '")');
    sheet.getRange("G" + row).setValue(new Date().toLocaleTimeString());
  }
}