Apps Scripts Code

Copy Code

function createDocumentsFromSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); // Replace "Sheet1" with the name of your sheet
  var dataRange = sheet.getDataRange();
  var dataValues = dataRange.getValues();


  // Get the template document
  var templateFileId = "1gwMxkJiRR4efxMGu9KDM96wvdWCN2md8AnEWdJY2GoA"; // Replace with the ID of your template document
  var templateDoc = DriveApp.getFileById(templateFileId);


  // Create a folder to store the generated documents
  var folder = DriveApp.createFolder("Generated Documents");


  // Loop through the rows in the sheet
  for (var i = 1; i < dataValues.length; i++) { // Start from row 2 to skip header
    var row = dataValues[i];


    // Replace the placeholders in the template with the row data
    var templateCopy = templateDoc.makeCopy();
    var doc = DocumentApp.openById(templateCopy.getId());
    var body = doc.getBody();


    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}}"
    ];


    var rowData = row.slice(0, placeholders.length);


    for (var j = 0; j < placeholders.length; j++) {
      var placeholder = placeholders[j];
      var value = rowData[j];


      if (typeof value === 'object' && value instanceof Date) {
        // Format the date value
        value = value.toLocaleDateString();
      }


      body.replaceText(placeholder, value);
    }


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


    // Move the document to the generated documents folder
    var generatedDoc = DriveApp.getFileById(doc.getId());
    folder.createFile(generatedDoc);


    // Delete the template copy
    DriveApp.getFileById(templateCopy.getId()).setTrashed(true);
  }


  // Provide a link to the generated documents folder
  var folderUrl = folder.getUrl();
  Logger.log("Generated documents: " + folderUrl);
}