AppSScript Code

Copy Code

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 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 (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];
    const mobileNumber = new Date(row[3]).toLocaleDateString();
    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
    sheet.getRange(index + 1, 6).setValue(docUrl);
  });
}