Autofill Document & Invoice from Google sheet data Updated Version – 4

function createDocumentsFromSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); // 👈 Change to your sheet name
  var dataRange = sheet.getDataRange();
  var dataValues = dataRange.getValues();

  // 📄 Template Google Doc ID
  var templateFileId = "1gwMxkJiRR4efxMGu9KDM96wvdWCN2md8AnEWdJY2GoA"; // 👈 Replace with your template file ID
  var templateDoc = DriveApp.getFileById(templateFileId);

  // 📁 Create a folder for the generated documents (with timestamp to avoid conflicts)
  var folderName = "Generated Documents - " + new Date().toISOString().replace(/[:.]/g, "-");
  var folder = DriveApp.createFolder(folderName);

  // 📝 Placeholder tags (must exactly match those in your Google Doc template)
  var placeholders = [
    "{{Full Name}}",
    "{{Address Of Event}}",
    "{{Todays Date}}",
    "{{Event Date}}",
    "{{Email}}",
    "{{Type Of Event}}",
    "{{Menu Selection}}",
    "{{Number Of Guests}}",
    "{{Price}}",
    "{{Catering Service Choice Percentage}}",
    "{{Discount}}",
    "{{Dietary restrictions comments}}",
    "{{Total}}"
  ];

  // 🔁 Loop through each row (skip header)
  for (var i = 1; i < dataValues.length; i++) {
    var row = dataValues[i];

    // 🚫 Skip empty rows
    if (row.join("").trim() === "") continue;

    // 📝 Make a copy of the template
    var templateCopy = templateDoc.makeCopy("Doc - " + row[0] + " - " + new Date().toISOString(), folder);
    var doc = DocumentApp.openById(templateCopy.getId());
    var body = doc.getBody();

    // 🔄 Replace placeholders with sheet data
    for (var j = 0; j < placeholders.length; j++) {
      var placeholder = placeholders[j];
      var value = row[j] || "";

      // 📅 Format dates nicely
      if (value instanceof Date) {
        value = Utilities.formatDate(value, Session.getScriptTimeZone(), "dd/MM/yyyy");
      }

      body.replaceText(placeholder, value.toString());
    }

    // 💾 Save changes
    doc.saveAndClose();
  }

  // ✅ Log and alert folder URL
  var folderUrl = folder.getUrl();
  Logger.log("Generated documents folder: " + folderUrl);
  SpreadsheetApp.getUi().alert("✅ All documents generated!\n\n📁 Folder URL:\n" + folderUrl);
}