"/**
 * 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 with formatting
  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 = "Trading Report";

      // **Updated**: Generate current date and time for the email body in desired format
      const reportGenerationTime = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd-MMM-yyyy hh:mm:ss a").toLowerCase();
      const body = `Please find attached the latest trading report generated on ${reportGenerationTime}.\n\nRegards,\nNeeraj`;

      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, including formatting.
 * Inserts a page break between sections by adding blank rows.
 * @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 { data: pnlData, formats: pnlFormats } = getDataWithDateLimitAndFormats(pnlSheet, 5, 3); // Data starts at row 5, date in column C (3)
    Logger.log(`PnL Sheet non-empty row count: ${pnlData.length}`);

    if (pnlData.length > 0) {
      // Append title with sheet name
      const startRow = reportSheet.getLastRow() + 1;
      reportSheet.appendRow([`Delivery Trades - ${pnlSheetName}`]);

      // Apply title formatting
      const titleRange = reportSheet.getRange(startRow, 1, 1, pnlData[0].length);
      titleRange.setFontWeight("bold").setFontSize(14);

      // Append headers from row 4 of PnL sheet
      const pnlHeaders = pnlSheet.getRange(4, 1, 1, pnlSheet.getLastColumn()).getValues();
      reportSheet.appendRow(pnlHeaders[0]);

      // Apply header formatting
      const headerRange = reportSheet.getRange(startRow + 1, 1, 1, pnlHeaders[0].length);
      headerRange.setFontWeight("bold").setBackground("#d3d3d3"); // Light gray background for headers

      // Append the data
      const dataStartRow = reportSheet.getLastRow() + 1;
      reportSheet.getRange(dataStartRow, 1, pnlData.length, pnlData[0].length).setValues(pnlData);
      
      // Apply data formatting
      const dataRange = reportSheet.getRange(dataStartRow, 1, pnlData.length, pnlData[0].length);
      dataRange.setBackgrounds(pnlFormats.backgrounds);
      dataRange.setFontStyles(pnlFormats.fontStyles);
      dataRange.setFontSizes(pnlFormats.fontSizes);
      dataRange.setNumberFormats(pnlFormats.numberFormats);
      dataRange.setFontWeights(pnlFormats.fontWeights);
      dataRange.setFontColors(pnlFormats.fontColors);
      // Removed dataRange.setBorders(...)

      // Add a significant number of blank rows to simulate a page break
      const blankRows = 40; // Adjust this number based on your page size and content
      const blankRowData = Array(pnlData[0].length).fill('');
      for (let i = 0; i < blankRows; i++) {
        reportSheet.appendRow(blankRowData);
      }
    }
  } else {
    Logger.log(`Sheet "${pnlSheetName}" not found.`);
  }

  // Properly reference the "PnL_i" sheet
  const pnlISheet = ss.getSheetByName(pnlISheetName);
  if (pnlISheet) {
    const { data: pnlIData, formats: pnlIFormats } = getDataWithDateLimitAndFormats(pnlISheet, 5, 2); // Data starts at row 5, date in column B (2)
    Logger.log(`PnL_i Sheet non-empty row count: ${pnlIData.length}`);

    if (pnlIData.length > 0) {
      // Append title with sheet name
      const startRow = reportSheet.getLastRow() + 1;
      reportSheet.appendRow([`Intraday Trades - ${pnlISheetName}`]);

      // Apply title formatting
      const titleRange = reportSheet.getRange(startRow, 1, 1, pnlIData[0].length);
      titleRange.setFontWeight("bold").setFontSize(14);

      // Append headers from row 4 of PnL_i sheet
      const pnlIHeaders = pnlISheet.getRange(4, 1, 1, pnlISheet.getLastColumn()).getValues();
      reportSheet.appendRow(pnlIHeaders[0]);

      // Apply header formatting
      const headerRange = reportSheet.getRange(startRow + 1, 1, 1, pnlIHeaders[0].length);
      headerRange.setFontWeight("bold").setBackground("#d3d3d3"); // Light gray background for headers

      // Append the data
      const dataStartRow = reportSheet.getLastRow() + 1;
      reportSheet.getRange(dataStartRow, 1, pnlIData.length, pnlIData[0].length).setValues(pnlIData);
      
      // Apply data formatting
      const dataRange = reportSheet.getRange(dataStartRow, 1, pnlIData.length, pnlIData[0].length);
      dataRange.setBackgrounds(pnlIFormats.backgrounds);
      dataRange.setFontStyles(pnlIFormats.fontStyles);
      dataRange.setFontSizes(pnlIFormats.fontSizes);
      dataRange.setNumberFormats(pnlIFormats.numberFormats);
      dataRange.setFontWeights(pnlIFormats.fontWeights);
      dataRange.setFontColors(pnlIFormats.fontColors);
      // Removed dataRange.setBorders(...)

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

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

/**
 * Retrieves data and associated formatting from a sheet up to the last row with a date in the specified column.
 * @param {Sheet} sheet - The sheet to retrieve data from.
 * @param {number} startRow - The row number to start retrieving data.
 * @param {number} dateColumn - The column number where the date is located.
 * @return {Object} - An object containing data and formatting arrays.
 */
function getDataWithDateLimitAndFormats(sheet, startRow, dateColumn) {
  const lastRow = sheet.getLastRow();
  if (lastRow < startRow) {
    return { data: [], formats: {} };
  }

  // Get all values in the date column starting from startRow
  const dateDataRange = sheet.getRange(startRow, dateColumn, lastRow - startRow + 1, 1);
  const dateData = dateDataRange.getValues();

  // Find the last row that has a date
  let lastValidRow = startRow - 1; // Initialize to one before startRow
  for (let i = dateData.length - 1; i >= 0; i--) {
    const cell = dateData[i][0];
    if (cell instanceof Date && !isNaN(cell)) { // Check if cell is a valid date
      lastValidRow = startRow + i;
      break;
    }
  }

  if (lastValidRow < startRow) {
    // No valid dates found
    return { data: [], formats: {} };
  }

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

  // Retrieve formatting properties
  const backgrounds = dataRange.getBackgrounds();
  const fontStyles = dataRange.getFontStyles();
  const fontSizes = dataRange.getFontSizes();
  const numberFormats = dataRange.getNumberFormats();
  const fontWeights = dataRange.getFontWeights();
  const fontColors = dataRange.getFontColors();

  return {
    data: data,
    formats: {
      backgrounds: backgrounds,
      fontStyles: fontStyles,
      fontSizes: fontSizes,
      numberFormats: numberFormats,
      fontWeights: fontWeights,
      fontColors: fontColors
    }
  };
}

/**
 * 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) {
  // Append Summary Title
  const startRow = reportSheet.getLastRow() + 1;
  reportSheet.appendRow(["Summary of Fees per Broker"]);

  // Apply title formatting
  const titleRange = reportSheet.getRange(startRow, 1, 1, 4);
  titleRange.setFontWeight("bold").setFontSize(14);

  // Append Summary Headers
  reportSheet.appendRow(["Broker", "Total Brokerage", "Total Charges", "Total Fees"]);

  // Apply header formatting
  const headerRange = reportSheet.getRange(startRow + 1, 1, 1, 4);
  headerRange.setFontWeight("bold").setBackground("#d3d3d3"); // Light gray background for headers

  const brokerData = {};

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

    const lastRow = sheet.getLastRow();
    if (lastRow < 5) return; // 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 = ss.getSheetByName(pnlISheetName); // Correct reference
  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) {
    const dataStartRow = reportSheet.getLastRow() + 1;
    reportSheet.getRange(dataStartRow, 1, summaryRows.length, summaryRows[0].length).setValues(summaryRows);

    // Apply data formatting
    const dataRange = reportSheet.getRange(dataStartRow, 1, summaryRows.length, summaryRows[0].length);
    dataRange.setBackgrounds(summaryRows.map(() => Array(4).fill("#ffffff"))); // White background
    dataRange.setFontWeights(summaryRows.map(() => Array(4).fill("normal")));
    dataRange.setNumberFormats(["@", "#,##0.00", "#,##0.00", "#,##0.00"]);
    dataRange.setFontColors(summaryRows.map(() => Array(4).fill("#000000"))); // Black font
    // Removed dataRange.setBorders(...)
  }

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

    // Bold the headers
    reportSheet.getRange("A" + (startRow + 1) + ":D" + (startRow + 1)).setFontWeight("bold");
  }
}

/**
 * Applies formatting to the Report sheet to make it systematic and beautiful.
 * Sets column widths, borders, text alignment, frozen rows, and alternating row colors.
 * @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();
  // Removed range.setBorder(...)

  // 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: Enhance specific sections if needed
  // For example, bold the "Summary of Fees per Broker" title
  const values = reportSheet.getDataRange().getValues();
  for (let i = 0; i < values.length; i++) {
    if (values[i][0] === "Summary of Fees per Broker") {
      // Bold the title row
      reportSheet.getRange(i + 1, 1, 1, reportSheet.getLastColumn()).setFontWeight("bold").setFontSize(14);
      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|null} - The PDF blob of the sheet or null if export fails.
 */
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=true&' + // repeat frozen rows on each page
    'gid=' + sheetId + '&' + // sheet ID
    'horizontal_alignment=LEFT&' + // alignment
    'vertical_alignment=MIDDLE&' + // vertical alignment
    'top_margin=0.5&' + // margins in inches
    'bottom_margin=0.5&' +
    'left_margin=0.5&' +
    'right_margin=0.5&' +
    'sheetnames=false&' +
    'printtitle=false&' +
    'pagenum=UNDEFINED';

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

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

gives this trade log with error

"1:05:10 PM	Notice	Execution started
1:05:16 PM	Info	PnL Sheet non-empty row count: 601
1:06:16 PM	Info	PnL_i Sheet non-empty row count: 588
1:06:28 PM	Error	
ReferenceError: ss is not defined
appendSummary	@ 8. PDF Report Beta.gs:310
appendReportContent	@ 8. PDF Report Beta.gs:190
generateFreePDFReport	@ 8. PDF Report Beta.gs:33"