AppSScript Code

Copy Code

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("OutrightStore")
    .addItem('Show Sidebar', 'showSidebar')
    .addToUi();
  // Call your function here if you want it to run automatically on open
  showSidebar();
}
// Function to create and display a custom sidebar
function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar4')
      .setTitle('Sidebar Form')
      .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}


function createHeadersFromForm(headers) {
  // Get the active sheet in the spreadsheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 
  // Split the headers string by comma to get individual headers
  const headerArray = headers.split(",");
 
  // Set the headers in the first row dynamically
  sheet.getRange(1, 1, 1, headerArray.length).setValues([headerArray]);
 
  // Apply formatting to the header row
  const headerRange = sheet.getRange(1, 1, 1, headerArray.length);
  headerRange.setFontWeight("bold"); // Set text to bold
  headerRange.setFontSize(14); // Set font size to 14
}


function createDocumentsFromSheet(templateFileId) {
  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 placeholders from the first row of the sheet
  var placeholders = dataValues[0];


  // Get the template document
  var templateDoc = DriveApp.getFileById(templateFileId);
  var templateName = templateDoc.getName(); // Get the name of the template document


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


  // Loop through the rows in the sheet starting from the second row (index 1) to skip the header row
  for (var i = 1; i < dataValues.length; i++) {
    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();


    for (var j = 0; j < placeholders.length; j++) {
      var placeholder = "{{" + placeholders[j] + "}}"; // Assuming placeholders are in the first row
      var value = row[j]; // Get the corresponding value from the row


      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();


    // Set the name of the copied document to match the template name
    var copiedFile = DriveApp.getFileById(templateCopy.getId());
    copiedFile.setName(templateName);


    // Move the document to the generated documents folder
    var newFile = folder.createFile(copiedFile);


    // Get the email address from the row
    var email = row[2]; // Assuming Email is in column C (index 2)


    // Send the PDF file via email
    if (email) {
      var subject = "Generated Document from " + templateName; // Include the template name in the subject
      var mailBody = "Please find the generated document attached.";
      MailApp.sendEmail(email, subject, mailBody, { attachments: [newFile.getAs(MimeType.PDF)] });
    }


    // Delete the template copy
    copiedFile.setTrashed(true);
  }


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