"/**
 * Generates a formatted PDF report from "PnL" and "PnL_i" sheets without using any paid services.
 */
function generateFreePDFReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Sheet names
  const PNL_SHEET_NAME = "PnL";
  const PNL_I_SHEET_NAME = "PnL_i";
  const REPORT_SHEET_NAME = "Report";

  // PDF file name
  const pdfName = `Trading_Report_${Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyyMMdd_HHmm")}.pdf`;

  // Report Drive folder (ensure this folder exists)
  const folderName = "Trading Reports"; // Make sure this folder exists in your Drive
  const folder = getExistingFolder(folderName);

  if (!folder) {
    Logger.log(`Folder "${folderName}" not found. Please create it in Google Drive.`);
    return;
  }

  // Create or clear the Report sheet
  let reportSheet = ss.getSheetByName(REPORT_SHEET_NAME);
  if (reportSheet) {
    reportSheet.clear();
  } else {
    reportSheet = ss.insertSheet(REPORT_SHEET_NAME);
  }

  // Append content from PnL and PnL_i
  appendReportContent(ss, reportSheet, PNL_SHEET_NAME, PNL_I_SHEET_NAME);

  // Apply formatting to the Report sheet
  formatReportSheet(reportSheet);

  // Export to PDF
  const pdfBlob = exportSheetAsPDF(ss, REPORT_SHEET_NAME, pdfName);

  if (pdfBlob) {
    // Save PDF to Drive
    const pdfFile = folder.createFile(pdfBlob);
    Logger.log(`PDF report generated: ${pdfFile.getUrl()}`);

    // Optional: Email the PDF
    const emailReport = true; // Set to false to disable email
    if (emailReport) {
      const recipients = ["dryrun.tryout@gmail.com"]; // Replace with your email
      const subject = "Daily Trading Report";
      const body = "Please find attached the latest trading report.";

      MailApp.sendEmail({
        to: recipients.join(","),
        subject: subject,
        body: body,
        attachments: [pdfFile]
      });

      Logger.log("PDF report emailed to recipients.");
    }

    // Delete the Report sheet
    ss.deleteSheet(reportSheet);
  } else {
    Logger.log("PDF blob was not created.");
  }
}

/**
 * Retrieves an existing folder by name. If it doesn't exist, returns null.
 * @param {string} folderName - The name of the folder.
 * @return {Folder|null} - The Google Drive folder or null if not found.
 */
function getExistingFolder(folderName) {
  const folders = DriveApp.getFoldersByName(folderName);
  if (folders.hasNext()) {
    return folders.next();
  } else {
    return null;
  }
}

/**
 * Appends content from "PnL" and "PnL_i" sheets to the Report sheet.
 * @param {Spreadsheet} ss - The active spreadsheet.
 * @param {Sheet} reportSheet - The report sheet.
 * @param {string} pnlSheetName - The name of the "PnL" sheet.
 * @param {string} pnlISheetName - The name of the "PnL_i" sheet.
 */
function appendReportContent(ss, reportSheet, pnlSheetName, pnlISheetName) {
  // Process PnL sheet
  const pnlSheet = ss.getSheetByName(pnlSheetName);
  if (pnlSheet) {
    const pnlData = getNonEmptyData(pnlSheet, 5); // Assuming data starts at row 5
    Logger.log(`PnL Sheet non-empty row count: ${pnlData.length}`);

    if (pnlData.length > 0) {
      reportSheet.appendRow(["Delivery Trades - " + pnlSheetName]);

      reportSheet.getRange(reportSheet.getLastRow() + 1, 1, pnlData.length, pnlData[0].length).setValues(pnlData);

      // Add a blank row for separation
      const blankRow = Array(pnlData[0].length).fill('');
      reportSheet.appendRow(blankRow);
    }
  } else {
    Logger.log(`Sheet "${pnlSheetName}" not found.`);
  }

  // Process PnL_i sheet
  const pnlISheet = ss.getSheetByName(pnlISheetName);
  if (pnlISheet) {
    const pnlIData = getNonEmptyData(pnlISheet, 5); // Assuming data starts at row 5
    Logger.log(`PnL_i Sheet non-empty row count: ${pnlIData.length}`);

    if (pnlIData.length > 0) {
      reportSheet.appendRow(["Intraday Trades - " + pnlISheetName]);

      reportSheet.getRange(reportSheet.getLastRow() + 1, 1, pnlIData.length, pnlIData[0].length).setValues(pnlIData);

      // Add a blank row for separation
      const blankRow = Array(pnlIData[0].length).fill('');
      reportSheet.appendRow(blankRow);
    }
  } else {
    Logger.log(`Sheet "${pnlISheetName}" not found.`);
  }

  // Add Summary
  appendSummary(reportSheet, pnlSheet, pnlISheetName);
}

/**
 * Retrieves non-empty data from a sheet starting from a specific row.
 * @param {Sheet} sheet - The sheet to retrieve data from.
 * @param {number} startRow - The row number to start retrieving data.
 * @return {Array} - A 2D array of non-empty rows.
 */
function getNonEmptyData(sheet, startRow) {
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();

  if (lastRow < startRow) {
    return [];
  }

  const dataRange = sheet.getRange(startRow, 1, lastRow - startRow + 1, lastColumn);
  const data = dataRange.getValues();

  // Filter out completely empty rows
  const nonEmptyData = data.filter(row => row.some(cell => cell !== ""));

  return nonEmptyData;
}

/**
 * Appends a summary of total fees per broker to the Report sheet.
 * @param {Sheet} reportSheet - The report sheet.
 * @param {Sheet} pnlSheet - The "PnL" sheet.
 * @param {string} pnlISheetName - The name of the "PnL_i" sheet.
 */
function appendSummary(reportSheet, pnlSheet, pnlISheetName) {
  reportSheet.appendRow(["Summary of Fees per Broker"]);
  reportSheet.appendRow(["Broker", "Total Brokerage", "Total Charges", "Total Fees"]);

  const brokerData = {};

  // Function to process a sheet
  function processSheet(sheet, brokerCol, brokerageCol, chargesCol) {
    if (!sheet) return;

    const lastRow = sheet.getLastRow();
    if (lastRow < 5) return; // Assuming data starts at row 5

    const brokers = sheet.getRange(5, brokerCol, lastRow - 4, 1).getValues();
    const brokerages = sheet.getRange(5, brokerageCol, lastRow - 4, 1).getValues();
    const charges = sheet.getRange(5, chargesCol, lastRow - 4, 1).getValues();

    for (let i = 0; i < brokers.length; i++) {
      const broker = brokers[i][0].toString().trim();
      const brokerage = parseFloat(brokerages[i][0]) || 0;
      const charge = parseFloat(charges[i][0]) || 0;
      const feeTotal = brokerage + charge;

      if (broker) {
        if (!brokerData[broker]) {
          brokerData[broker] = { brokerage: 0, charges: 0, total: 0 };
        }
        brokerData[broker].brokerage += brokerage;
        brokerData[broker].charges += charge;
        brokerData[broker].total += feeTotal;
      }
    }
  }

  // Assuming in "PnL" sheet:
  // Broker is in Column J (10), Brokerage in O (15), Charges in P (16)
  if (pnlSheet) {
    processSheet(pnlSheet, 10, 15, 16);
  }

  // Assuming in "PnL_i" sheet:
  // Broker is in Column I (9), Brokerage in N (14), Charges in O (15)
  const pnlISheet = pnlSheet.getSheetId() ? pnlSheet : null;
  if (pnlISheet) {
    processSheet(pnlISheet, 9, 14, 15);
  }

  // Write summary to Report sheet
  const summaryRows = [];
  for (const broker in brokerData) {
    summaryRows.push([
      broker,
      brokerData[broker].brokerage,
      brokerData[broker].charges,
      brokerData[broker].total
    ]);
  }

  if (summaryRows.length > 0) {
    reportSheet.getRange(reportSheet.getLastRow() + 1, 1, summaryRows.length, summaryRows[0].length).setValues(summaryRows);
  }

  // Formatting
  const lastRow = reportSheet.getLastRow();
  if (lastRow >= 2) {
    // Format numbers
    reportSheet.getRange("B2:D" + lastRow).setNumberFormat("#,##0.00");

    // Bold headers
    reportSheet.getRange("A2:D2").setFontWeight("bold");
  }
}

/**
 * Applies formatting to the Report sheet to make it systematic and beautiful.
 * @param {Sheet} reportSheet - The report sheet.
 */
function formatReportSheet(reportSheet) {
  // Adjust column widths to fit content
  reportSheet.autoResizeColumns(1, reportSheet.getLastColumn());

  // Apply borders to all data cells
  const range = reportSheet.getDataRange();
  range.setBorder(true, true, true, true, true, true);

  // Set text alignment to left for all cells
  range.setHorizontalAlignment("left");

  // Freeze the top two rows (headers)
  reportSheet.setFrozenRows(2);

  // Apply alternating row colors (Zebra Striping) efficiently
  const numRows = reportSheet.getLastRow();
  const numColumns = reportSheet.getLastColumn();

  if (numRows > 2) { // Assuming first two rows are headers
    // Define the range excluding headers
    const dataRange = reportSheet.getRange(3, 1, numRows - 2, numColumns);

    // Create a 2D array for background colors
    const backgrounds = [];
    for (let i = 0; i < numRows - 2; i++) {
      const isEven = (i + 1) % 2 === 0; // Start from the first data row as odd
      const rowColor = isEven ? "#f9f9f9" : "#ffffff"; // Light gray for even rows
      const rowBackground = Array(numColumns).fill(rowColor);
      backgrounds.push(rowBackground);
    }

    // Apply all background colors at once
    dataRange.setBackgrounds(backgrounds);
  }

  // Optional: Set font size and style for headers
  // Example: Bold and larger font for summary headers
  // Modify based on your Report sheet's structure

  // Example: Bold the "Summary of Fees per Broker" line
  // Find the starting row of Summary
  const values = reportSheet.getDataRange().getValues();
  for (let i = 0; i < values.length; i++) {
    if (values[i][0] === "Summary of Fees per Broker") {
      // Bold the header row right after this row
      reportSheet.getRange(i + 2, 1, 1, reportSheet.getLastColumn()).setFontWeight("bold");
      break;
    }
  }
}

/**
 * Exports a specific sheet as a PDF blob.
 * @param {Spreadsheet} ss - The active spreadsheet.
 * @param {string} sheetName - The name of the sheet to export.
 * @param {string} pdfName - The desired PDF file name.
 * @return {Blob} - The PDF blob of the sheet.
 */
function exportSheetAsPDF(ss, sheetName, pdfName) {
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    Logger.log(`Sheet "${sheetName}" not found.`);
    return null;
  }

  const sheetId = sheet.getSheetId();
  const url_base = ss.getUrl().replace(/edit$/, '');
  const exportUrl = url_base + 'export?' +
    'format=pdf&' + // export format
    'size=letter&' + // paper size
    'portrait=true&' + // orientation
    'fitw=true&' + // fit to width
    'sheetnames=false&' + // hide sheet names
    'printtitle=false&' + // hide title
    'pagenumbers=true&' + // show page numbers
    'gridlines=false&' + // hide gridlines
    'fzr=false&' + // do not repeat frozen rows on each page
    'gid=' + sheetId; // sheet ID

  const options = {
    headers: {
      'Authorization': `Bearer ${ScriptApp.getOAuthToken()}`
    },
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(exportUrl, options);
    const blob = response.getBlob().setName(pdfName);
    return blob;
  } catch (error) {
    Logger.log(`Error exporting sheet "${sheetName}" as PDF: ${error}`);
    return null;
  }
}"

isme addition krna h

1. "PnL" aur "PnL_i" se date wahi tk lena h jaha tk dates ho , date column C me h "PnL" ke aur B me hai "PnL_i" ke

2. "Please find attached the latest trading report." body of email me ye add krna h date and time of report generation.
regards Neeraj .... esa poora krna h
3. dono sheet kw row 4 me header h. 5 se data entry h ye dhyan rakhna peport banate time.