"function filterAndAggregatePnL() {
  const startTime = new Date(); // Start time for execution time logging

  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
  const tickerFilter = filterSheet.getRange("O2").getValue().toString().trim(); // Ticker filter
  const termFilter = filterSheet.getRange("O3").getValue().toString().trim(); // Short Term / Long Term filter

  // 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 tradeType = row[7] ? row[7].toString().trim() : "";
        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 buyDate = parseDate(row[1]);
        const sellDate = parseDate(row[2]);
        const fees = parseNumber(row[14]) || 0; // Fetching fees from column O (index 14)

        if (ticker && sellDate && buyDate) {
          data.push({
            date: sellDate,
            buyDate: buyDate,
            sellDate: sellDate,
            ticker: ticker,
            shares: shares,
            buyPrice: buyPrice,
            sellPrice: sellPrice,
            tradeType: tradeType,
            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 tradeType = row[6] ? row[6].toString().trim() : "";
        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,
            buyDate: tradeDate,
            sellDate: tradeDate,
            ticker: ticker,
            shares: shares,
            buyPrice: buyPrice,
            sellPrice: sellPrice,
            tradeType: tradeType,
            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);

  // Check for Short Term / Long Term filter
  let termFilterUpper = termFilter.toUpperCase();
  let tradeTypeFilterUpper = tradeTypeFilter.toUpperCase();
  let proceed = true;
  if ((termFilterUpper === "SHORT TERM" || termFilterUpper === "LONG TERM") && tradeTypeFilterUpper === "BOTH") {
    SpreadsheetApp.getUi().alert("Select 'Delivery' in cell D3 for Trade Type to filter Short Term or Long Term trades.");
    return;
  }

  // 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;

    // Ticker filter
    if (tickerFilter && tickerFilter.toUpperCase() !== "ALL" && trade.ticker.toUpperCase() !== tickerFilter.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

    // Short Term / Long Term filter
    if ((termFilterUpper === "SHORT TERM" || termFilterUpper === "LONG TERM") && trade.tradeMode !== "Delivery") {
      // Only apply term filter to Delivery trades
      return false;
    }

    if (termFilterUpper === "SHORT TERM" && trade.tradeMode === "Delivery") {
      // Holding period less than 365 days
      let holdingPeriod = Math.floor((trade.sellDate - trade.buyDate) / (1000 * 60 * 60 * 24));
      if (holdingPeriod >= 365) return false;
    }

    if (termFilterUpper === "LONG TERM" && trade.tradeMode === "Delivery") {
      // Holding period 365 days or more
      let holdingPeriod = Math.floor((trade.sellDate - trade.buyDate) / (1000 * 60 * 60 * 24));
      if (holdingPeriod < 365) return false;
    }

    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);

  // Calculate and log execution time
  const endTime = new Date();
  const executionTime = (endTime - startTime) / 1000; // Time in seconds
  outputSheet.getRange('C1').setValue(`Last execution time: ${executionTime} seconds`);

  // Inform the user
  SpreadsheetApp.getUi().alert(`PnL data has been successfully filtered and aggregated.\nExecution time: ${executionTime} seconds.`);
}"

ye script short term aur long term ke liye 1 year kese calculate kr rahi h.

mujhe lgta h direct 365 dekh rahi h script . leap year ho gaya to 366 days hote h tb us case me ky ?

isko consider karo jara.