V5- Autofill Google Doc

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs');
  menu.addToUi();
}

function createNewGoogleDocs() {
  // ✅ Define template and destination folder IDs
  const googleDocTemplateId = '1G-qP5xoZAdjp3d1LAW9zk5tbum4vBS-GP9B51MTbDW8'; // replace with your template ID
  const destinationFolderId = '1P_S897kbbqcNPYLCoQ-MOqjK-E7oVudQ'; // replace with your destination folder ID

  // ✅ Get the Google Doc template and destination folder
  const googleDocTemplate = DriveApp.getFileById(googleDocTemplateId);
  const destinationFolder = DriveApp.getFolderById(destinationFolderId);

  // ✅ Get the active spreadsheet and target sheet
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = activeSpreadsheet.getSheetByName('Sheet1'); // replace with your sheet name

  // ✅ Get all data from the sheet
  const rows = sheet.getDataRange().getValues();

  // ✅ Loop through each row in the sheet
  rows.forEach(function(row, index) {
    // Skip the header row (index 0) and rows that already have a document URL (column F = index 5)
    if (index === 0 || row[5]) return;

    // Extract data from the row
    const fullName = row[0];
    const address = row[1];
    const city = row[2];

    // ⚠️ Mobile number column is being formatted as a date — fixing this:
    const mobileNumber = row[3] ? row[3].toString() : '';  // keep original value

    const email = row[4];

    // ✅ Make a copy of the Google Doc template
    const copy = googleDocTemplate.makeCopy(`${fullName}, ${address} Employee Details`, destinationFolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();

    // ✅ Replace placeholders in the copied document
    body.replaceText('{{Full Name}}', fullName || '');
    body.replaceText('{{Your Address}}', address || '');
    body.replaceText('{{Your City}}', city || '');
    body.replaceText('{{Mobile Number}}', mobileNumber || '');
    body.replaceText('{{Your Email}}', email || '');

    // ✅ Save and close the document
    doc.saveAndClose();

    // ✅ Get the URL of the copied document
    const docUrl = doc.getUrl();

    // ✅ Update the corresponding cell in the sheet with the document URL (column F = 6)
    sheet.getRange(index + 1, 6).setValue(docUrl);
  });
}