Alpaca Portfolio Table for Google Sheets AppScript

code.gs

function updatePortfolioAndPieChart() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Positions");

  // --- CONFIG ---
  var isPaper = sheet.getRange("I1").getValue();
  var apiKey = sheet.getRange("I2").getValue();
  var apiSecret = sheet.getRange("I3").getValue();

  if (!apiKey || !apiSecret) {
    throw new Error("Please input an API key and secret key in I2 & I3.");
  }

  var baseUrl = isPaper ? "https://paper-api.alpaca.markets" : "https://api.alpaca.markets";

  // --- HELPERS ---
  function request(path) {
    var options = {
      method: "GET",
      headers: {
        "APCA-API-KEY-ID": apiKey,
        "APCA-API-SECRET-KEY": apiSecret
      },
      muteHttpExceptions: true
    };
    var res = UrlFetchApp.fetch(baseUrl + path, options);
    return JSON.parse(res.getContentText());
  }

  // --- GET ACCOUNT ---
  var account = request("/v2/account");
  var portfolioValue = parseFloat(account.portfolio_value);
  var cashValue = parseFloat(account.cash);
  if (!portfolioValue || portfolioValue <= 0) {
    throw new Error("Portfolio value is invalid.");
  }

  // --- GET OPEN ORDERS ---
  var orders = request("/v2/orders?status=open");
  var openOrdersValue = 0;
  orders.forEach(function(order) {
    var qty = parseFloat(order.qty);
    var price = 0;

    if (order.notional) {
      price = parseFloat(order.notional);
    } else if (order.limit_price) {
      price = parseFloat(order.limit_price) * qty;
    } else if (order.stop_price) {
      price = parseFloat(order.stop_price) * qty;
    }

    // Adjust for options multiplier
    if (order.asset_class && order.asset_class.toLowerCase() === "us_option") {
      price = price * 100;
    }

    openOrdersValue += price;
  });

  // Adjust cash to exclude open orders
  var adjustedCash = cashValue - openOrdersValue;
  if (adjustedCash < 0) adjustedCash = 0;

  // --- GET POSITIONS ---
  var positions = request("/v2/positions");

  // Sort positions by market value (descending)
  positions.sort(function(a, b) {
    return parseFloat(b.market_value) - parseFloat(a.market_value);
  });

  // --- PREPARE TABLE DATA ---
  var tableData = [];

  // Add positions
  positions.forEach(function(pos) {
    var marketValue = parseFloat(pos.market_value);
    var pct = marketValue / portfolioValue;
    tableData.push([pos.symbol, marketValue, pct]);
  });

  // Add cash row
  tableData.push(["Cash", adjustedCash, adjustedCash / portfolioValue]);

  // Add open orders row
  tableData.push(["Open Orders", openOrdersValue, openOrdersValue / portfolioValue]);

  // --- WRITE DATA ---
  sheet.getRange("H5:J").clearContent();
  sheet.getRange("H5").setValue("Symbol");
  sheet.getRange("I5").setValue("Market Value ($)");
  sheet.getRange("J5").setValue("% of Portfolio");

  sheet.getRange(6, 8, tableData.length, 3).setValues(tableData);

  // Format columns
  sheet.getRange("I6:I" + (tableData.length + 5)).setNumberFormat("$#,##0.00");
  sheet.getRange("J6:J" + (tableData.length + 5)).setNumberFormat("0.00%");
}