"function filterAndAggregatePnL() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Define sheet names
  const pnlSheetName = "PnL";          // Delivery trades
  const pnlI_SheetName = "PnL_i";      // Intraday trades
  const outputSheetName = "PnL_One";

  // Get sheets
  const pnlSheet = ss.getSheetByName(pnlSheetName);
  const pnlI_Sheet = ss.getSheetByName(pnlI_SheetName);
  const outputSheet = ss.getSheetByName(outputSheetName);

  if (!pnlSheet || !pnlI_Sheet || !outputSheet) {
    SpreadsheetApp.getUi().alert("One or more sheets not found. Please check the sheet names.");
    return;
  }

  // Get filter parameters from "PnL_One" sheet
  const filterSheet = outputSheet;

  const startDate = filterSheet.getRange("B2").getValue(); // Start Date
  const endDate = filterSheet.getRange("C2").getValue();   // End Date
  const assetClassFilter = filterSheet.getRange("A3").getValue().toString().trim();
  const brokerFilter = filterSheet.getRange("B3").getValue().toString().trim();
  const userFilter = filterSheet.getRange("C3").getValue().toString().trim();
  const tradeTypeFilter = filterSheet.getRange("D3").getValue().toString().trim(); // Delivery, Intraday, Both

  // Function to clean and parse numbers, removing commas and currency symbols
  function parseNumber(value) {
    if (typeof value === 'number') return value;
    if (typeof value === 'string') {
      // Remove commas and any non-numeric characters except for the decimal point and minus sign
      let cleaned = value.replace(/[^0-9.\-]/g, '');
      return parseFloat(cleaned) || 0;
    }
    return 0;
  }

  // Function to parse date from string (e.g., "30-Apr-2020") or return as is if already a Date
  function parseDate(value) {
    if (value instanceof Date) {
      return value;
    } else if (typeof value === 'string') {
      // Expected format: "DD-MMM-YYYY"
      const dateParts = value.split("-");
      if (dateParts.length === 3) {
        const day = parseInt(dateParts[0], 10);
        const monthStr = dateParts[1].toLowerCase();
        const year = parseInt(dateParts[2], 10);
        const monthNames = ["jan", "feb", "mar", "apr", "may", "jun",
                           "jul", "aug", "sep", "oct", "nov", "dec"];
        const month = monthNames.indexOf(monthStr.substring(0,3)) + 1; // 1-based
        if (month >= 1 && month <= 12) {
          return new Date(year, month -1, day); // months are 0-based in JavaScript
        }
      }
    }
    return null;
  }

  // Function to retrieve and process data from a sheet
  function getPnLData(sheet, isDelivery) {
    let data = [];
    if (isDelivery) {
      // "PnL" sheet: headers in row 4, data starts from row 5
      const lastRow = sheet.getLastRow();
      const lastCol = sheet.getLastColumn();
      if (lastRow < 5) return data; // No data

      const values = sheet.getRange(5, 1, lastRow - 4, lastCol).getValues();

      // Expected Columns: A-Sl No., B-Buy Date, C-Sell Date, D-Ticker, E-Shares Sold/Bought,
      // F-Buy Price, G-Sell Price, H-Trade Type, I-Asset Class, J-Broker, K-User, O-Fees
      values.forEach(row => {
        const ticker = row[3] ? row[3].toString().trim() : "";
        const shares = parseNumber(row[4]);
        const buyPrice = parseNumber(row[5]);
        const sellPrice = parseNumber(row[6]);
        const assetClass = row[8] ? row[8].toString().trim() : "";
        const broker = row[9] ? row[9].toString().trim() : "";
        const user = row[10] ? row[10].toString().trim() : "";
        const sellDate = parseDate(row[2]);
        const fees = parseNumber(row[14]) || 0; // Fetching fees from column O (index 14)

        if (ticker && sellDate) {
          data.push({
            date: sellDate,
            ticker: ticker,
            shares: shares,
            buyPrice: buyPrice,
            sellPrice: sellPrice,
            assetClass: assetClass,
            broker: broker,
            user: user,
            fees: fees, // Store fees for later calculation
            tradeMode: "Delivery"
          });
        }
      });
    } else {
      // "PnL_i" sheet: headers in row 3, data starts from row 4
      const lastRow = sheet.getLastRow();
      const lastCol = sheet.getLastColumn();
      if (lastRow < 4) return data; // No data

      const values = sheet.getRange(4, 1, lastRow - 3, lastCol).getValues();

      // Expected Columns: A-Sl No., B-Trade Date, C-Ticker, D-Shares Sold/Bought,
      // E-Buy Price, F-Sell Price, G-Trade Type, H-Asset Class, I-Broker, J-User, N-Fees
      values.forEach(row => {
        const ticker = row[2] ? row[2].toString().trim() : "";
        const shares = parseNumber(row[3]);
        const buyPrice = parseNumber(row[4]);
        const sellPrice = parseNumber(row[5]);
        const assetClass = row[7] ? row[7].toString().trim() : "";
        const broker = row[8] ? row[8].toString().trim() : "";
        const user = row[9] ? row[9].toString().trim() : "";
        const tradeDate = parseDate(row[1]);
        const fees = parseNumber(row[13]) || 0; // Fetching fees from column N (index 13)

        if (ticker && tradeDate) {
          data.push({
            date: tradeDate,
            ticker: ticker,
            shares: shares,
            buyPrice: buyPrice,
            sellPrice: sellPrice,
            assetClass: assetClass,
            broker: broker,
            user: user,
            fees: fees, // Store fees for later calculation
            tradeMode: "Intraday"
          });
        }
      });
    }
    return data;
  }

  // Retrieve data from both sheets
  let deliveryData = getPnLData(pnlSheet, true);      // Delivery trades
  let intradayData = getPnLData(pnlI_Sheet, false);  // Intraday trades

  // Combine all data
  let allData = deliveryData.concat(intradayData);

  // Apply filters
  let filteredData = allData.filter(trade => {
    // Date filter: Inclusive
    if (startDate instanceof Date && trade.date < startDate) return false;
    if (endDate instanceof Date && trade.date > endDate) return false;

    // Asset Class filter
    if (assetClassFilter && assetClassFilter.toUpperCase() !== "ALL" && trade.assetClass.toUpperCase() !== assetClassFilter.toUpperCase()) return false;

    // Broker filter
    if (brokerFilter && brokerFilter.toUpperCase() !== "ALL" && trade.broker.toUpperCase() !== brokerFilter.toUpperCase()) return false;

    // User filter
    if (userFilter && userFilter.toUpperCase() !== "ALL" && trade.user.toUpperCase() !== userFilter.toUpperCase()) return false;

    // Trade Type filter: Delivery, Intraday, Both
    if (tradeTypeFilter === "Delivery" && trade.tradeMode !== "Delivery") return false;
    if (tradeTypeFilter === "Intraday" && trade.tradeMode !== "Intraday") return false;
    // If "Both", include all trade modes

    return true;
  });

  // Clear previous output starting from row 5 (to preserve headers and other manual data)
  const lastRowOutput = outputSheet.getLastRow();
  if (lastRowOutput > 4) {
    outputSheet.getRange(5, 1, lastRowOutput - 4, 14).clearContent();
  }

  if (filteredData.length === 0) {
    SpreadsheetApp.getUi().alert("No PnL data matches the specified filters.");
    return;
  }

  // Aggregate data by Ticker, Asset Class, Broker, User
  let aggregationMap = {};

  filteredData.forEach(trade => {
    // Create a unique key for each combination
    const key = `${trade.ticker}|${trade.assetClass}|${trade.broker}|${trade.user}`;

    if (!aggregationMap[key]) {
      aggregationMap[key] = {
        ticker: trade.ticker,
        assetClass: trade.assetClass,
        broker: trade.broker,
        user: trade.user,
        totalShares: 0,
        totalBuyValue: 0,
        totalSellValue: 0,
        totalFees: 0 // Initialize total fees
      };
    }

    aggregationMap[key].totalShares += trade.shares;
    aggregationMap[key].totalBuyValue += trade.shares * trade.buyPrice;
    aggregationMap[key].totalSellValue += trade.shares * trade.sellPrice;
    aggregationMap[key].totalFees += trade.fees; // Accumulate fees
  });

  // Prepare output data
  let outputData = [];

  for (let key in aggregationMap) {
    const agg = aggregationMap[key];
    const totalShares = agg.totalShares;
    const buyAvg = totalShares !== 0 ? (agg.totalBuyValue / totalShares) : 0;
    const sellAvg = totalShares !== 0 ? (agg.totalSellValue / totalShares) : 0;
    const valueAtBuy = agg.totalBuyValue;
    const valueAtSell = agg.totalSellValue;
    const grossPnL = valueAtSell - valueAtBuy;
    const fees = agg.totalFees; // Use accumulated fees
    const netPnL = grossPnL - fees;
    const percentPnL = valueAtBuy !== 0 ? ((netPnL / valueAtBuy)) : 0;

    outputData.push({
      ticker: agg.ticker,
      assetClass: agg.assetClass,
      broker: agg.broker,
      user: agg.user,
      totalShares: totalShares,
      buyAvg: buyAvg,
      sellAvg: sellAvg,
      valueAtBuy: valueAtBuy,
      valueAtSell: valueAtSell,
      grossPnL: grossPnL,
      fees: fees,
      netPnL: netPnL,
      percentPnL: percentPnL
    });
  }

  // Sort outputData by ticker, asset class, broker, and user
  outputData.sort((a, b) => {
    if (a.ticker !== b.ticker) {
      return a.ticker.localeCompare(b.ticker);
    } else if (a.assetClass !== b.assetClass) {
      return a.assetClass.localeCompare(b.assetClass);
    } else if (a.broker !== b.broker) {
      return a.broker.localeCompare(b.broker);
    } else {
      return a.user.localeCompare(b.user);
    }
  });

  // Prepare final output array with headers
  let finalOutput = [];

  outputData.forEach((row, index) => {
    finalOutput.push([
      index + 1,                        // Sl No.
      row.ticker,                       // Ticker
      row.totalShares,                  // #Qty
      row.buyAvg,                       // Buy Avg.
      row.sellAvg,                      // Sell Avg.
      row.valueAtBuy,                   // Value @ Buy
      row.valueAtSell,                  // Value @ Sell
      row.grossPnL,                     // Gross PnL
      row.fees,                         // Fees
      row.netPnL,                       // Net PnL (After Fees)
      row.percentPnL,                   // % P/L (Abs.)
      row.assetClass,                   // Asset Class (Column L)
      row.broker,                       // Broker (Column M)
      row.user                          // User (Column N)
    ]);
  });

  // Define headers
  const headers = [
    "Sl No.",
    "Ticker",
    "#Qty",
    "Buy Avg.",
    "Sell Avg.",
    "Value @ Buy",
    "Value @ Sell",
    "Gross PnL",
    "Fees",
    "Net PnL (After Fees)",
    "% P/L (Abs.)",
    "Asset Class", // Column L
    "Broker",      // Column M
    "User"         // Column N
  ];

  // Set headers in row 4 (A4:N4)
  outputSheet.getRange("A4:N4").setValues([headers]);

  // Write data starting from row 5
  if (finalOutput.length > 0) {
    outputSheet.getRange(5, 1, finalOutput.length, finalOutput[0].length).setValues(finalOutput);
  }

  // Record the last update date and time
  let now = new Date();
  let formattedNow = Utilities.formatDate(now, "Asia/Kolkata", "dd-MMM-yyyy HH:mm:ss");
  outputSheet.getRange('B1').setValue(formattedNow);

  // Inform the user
  SpreadsheetApp.getUi().alert("PnL data has been successfully filtered and aggregated.");
}"

ye script em dm sahi h. aur sahi se work kr rahi h.

isme kuch feature addition krna chahata hu m

1. script kitti der me execure hui wo log ho
2. sheet "PnL_One" ke cell D2 ko bhi consider krna h ab A3 B3 C3 D3 ke jese.  agar D2 me "ALL" ho to script vese hi function kare jese abhi kr rahi h, pr jb "ALL" ki jageh kuch stock naam ho to us stock ka pnl bs filter hoke dispaly ho other filters ke sath. jesi ki tum jante ho "PnL" me stock name / ticker column D me hai aur  "PnL_i" me ticker column C me h.
3. ek aur filter in put h cell O2 me "PnL_One" sheet me. isme agar "ALL" ho to script normal function karegi pr agar "Short Term" ya "Long Term" me se kuch ho to check kategi ki cell D3 me "Both" to nhi h agar both h to message kare ki select delivery for filter short term or long term. (kyuki yar short term aur long term deliver me hi hoga na intraday me thodi). shoert term me wo pnl filter ho jo buy sell date 1 year se km wali h long term me wo jo 1 year se jada wali h.

pehle acche se batao kya smjhe . pehle apan apas me acche se smjh le firr aap code kerna.