google sheet to google doc template autofiller

code.gs

// Custom menu creation with icons
function onOpen() {
  try {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu('🔄 Document Generator')
      .addItem('📝 Start Generator', 'showSidebar')
      .addSeparator()
      .addItem('ℹ️ About', 'showAbout');
    menu.addToUi();
  } catch (e) {
    Logger.log('Error creating menu: ' + e);
  }
}

function showAbout() {
  const ui = SpreadsheetApp.getUi();
  ui.alert(
    'Document Generator',
    'This tool generates documents from your template and spreadsheet data.\n\n' +
    'Version: 1.0\n' +
    'Created: October 2024',
    ui.ButtonSet.OK
  );
}

function showSidebar() {
  const html = HtmlService.createHtmlOutput(`
    <div style="padding: 10px;">
      <h3>📄 Document Generator</h3>
      <form>
        <div style="margin-bottom: 15px;">
          <label for="templateUrl">Template Doc URL:</label><br>
          <input type="text" id="templateUrl" name="templateUrl" style="width: 100%;" required>
          <button type="button" id="analyzeBtn" style="margin-top: 5px; background: #34a853; color: white; padding: 4px 8px; border: none; border-radius: 4px;">
            🔍 Analyze Template
          </button>
        </div>
        
        <div class="progress-container" style="display: none; margin-bottom: 15px;">
          <div class="progress-bar-bg" style="background: #e0e0e0; height: 4px; border-radius: 2px;">
            <div class="progress-bar" style="width: 0%; height: 100%; background: #4285f4; border-radius: 2px; transition: width 0.3s ease-in-out;"></div>
          </div>
          <div class="progress-text" style="font-size: 12px; color: #666; margin-top: 4px;"></div>
        </div>
        
        <div id="templateAnalysis" style="margin-bottom: 15px; display: none;">
          <h4>📋 Template Variables Found:</h4>
          <div id="variablesList" style="background: #f8f9fa; padding: 10px; border-radius: 4px; font-family: monospace;">
          </div>
        </div>
        
        <div style="margin-bottom: 15px;">
          <label for="newDocName">New Document Name:</label><br>
          <input type="text" id="newDocName" name="newDocName" style="width: 100%;" required>
        </div>
        
        <button type="button" id="submitBtn" style="background: #4285f4; color: white; padding: 8px 16px; border: none; border-radius: 4px;">
          🔨 Generate Document
        </button>
        
        <div id="loadingIndicator" style="display: none; margin-top: 10px;">
          <div style="display: flex; align-items: center;">
            <div style="border: 2px solid #f3f3f3; border-top: 2px solid #3498db; border-radius: 50%; width: 12px; height: 12px; animation: spin 1s linear infinite; margin-right: 10px;"></div>
            Processing... Please wait
          </div>
        </div>
        
        <div id="status" style="margin-top: 10px; color: #666;"></div>
      </form>
    </div>
    <style>
      @keyframes spin {
        0% { transform: rotate(0deg); }
        100% { transform: rotate(360deg); }
      }
      .success-link {
        color: #1a73e8;
        text-decoration: none;
        word-break: break-all;
      }
      .success-link:hover {
        text-decoration: underline;
      }
      .success-message {
        margin-top: 10px;
        padding: 10px;
        background: #f8f9fa;
        border-radius: 4px;
        border-left: 4px solid #34a853;
      }
    </style>
    <script>
      function updateProgress(percent, message) {
        const progressContainer = document.querySelector('.progress-container');
        const progressBar = document.querySelector('.progress-bar');
        const progressText = document.querySelector('.progress-text');
        
        progressContainer.style.display = 'block';
        progressBar.style.width = percent + '%';
        progressText.textContent = message;
      }
      
      document.getElementById('analyzeBtn').addEventListener('click', function() {
        const templateUrl = document.getElementById('templateUrl').value;
        if (!templateUrl) {
          alert('Please enter template URL first');
          return;
        }
        
        updateProgress(0, 'Starting template analysis...');
        
        google.script.run
          .withSuccessHandler(function(variables) {
            showTemplateAnalysis(variables);
            updateProgress(100, 'Analysis complete!');
            setTimeout(() => {
              document.querySelector('.progress-container').style.display = 'none';
            }, 1000);
          })
          .withFailureHandler(onFailure)
          .analyzeTemplateVariables(templateUrl);
      });
      
      document.getElementById('submitBtn').addEventListener('click', function() {
        const templateUrl = document.getElementById('templateUrl').value;
        const newDocName = document.getElementById('newDocName').value;
        
        if (!templateUrl || !newDocName) {
          alert('Please fill in all fields');
          return;
        }
        
        this.disabled = true;
        document.getElementById('loadingIndicator').style.display = 'block';
        updateProgress(0, 'Starting document generation...');
        
        google.script.run
          .withSuccessHandler(onSuccess)
          .withFailureHandler(onFailure)
          .processDocument(templateUrl, newDocName);
      });
      
      function showTemplateAnalysis(variables) {
        document.getElementById('templateAnalysis').style.display = 'block';
        document.getElementById('variablesList').innerHTML = variables.map(function(v) {
          return '<div style="margin: 10px 0; padding: 8px; background: #f8f9fa; border-radius: 4px;">' +
                 '<div style="font-family: monospace;">' + v + '</div>' +
                 '</div>';
        }).join('');
        document.getElementById('status').innerHTML = 'Template analysis complete';
      }
      
      function onSuccess(result) {
        document.getElementById('loadingIndicator').style.display = 'none';
        document.getElementById('submitBtn').disabled = false;
        updateProgress(100, 'Document generated successfully!');
        
        const successHtml = 
          '<div class="success-message">' +
          '✅ Document created successfully!<br>' +
          '<a href="' + result + '" target="_blank" class="success-link">' +
          '📄 Click here to open document</a>' +
          '</div>';
        
        document.getElementById('status').innerHTML = successHtml;
      }
      
      function onFailure(error) {
        document.getElementById('loadingIndicator').style.display = 'none';
        document.getElementById('submitBtn').disabled = false;
        document.getElementById('status').innerHTML = 
          '<div style="color: #d93025; margin-top: 10px; padding: 10px; background: #fce8e6; border-radius: 4px; border-left: 4px solid #d93025;">' +
          '❌ Error: ' + error +
          '</div>';
        updateProgress(100, 'Error occurred during processing');
      }
    </script>
  `).setWidth(300).setHeight(500);
  
  SpreadsheetApp.getUi().showSidebar(html);
}



// Function to analyze template variables - simplified version
function analyzeTemplateVariables(templateUrl) {
  try {
    Logger.log('Starting template analysis...');
    const templateDoc = DocumentApp.openByUrl(templateUrl);
    Logger.log('Successfully opened template document');
    
    const bodyText = templateDoc.getBody().getText();
    Logger.log('Retrieved template content');
    
    // Find all {{variable}} patterns
    const variablePattern = /\{\{([^}]+)\}\}/g;
    const matches = [...bodyText.matchAll(variablePattern)];
    const variables = matches.map(match => match[0]);
    
    // Remove duplicates
    const uniqueVariables = [...new Set(variables)];
    
    Logger.log('Found the following variables in template:');
    uniqueVariables.forEach(variable => {
      Logger.log('- ' + variable);
    });
    
    return uniqueVariables;
  } catch (error) {
    Logger.log('Error in template analysis: ' + error);
    throw 'Unable to analyze template. Please check the URL and permissions.';
  }
}





// Function to get template folder
function getTemplateFolder(templateUrl) {
  const templateId = templateUrl.match(/[-\w]{25,}/);
  if (!templateId) throw new Error('Invalid template URL');
  
  const templateFile = DriveApp.getFileById(templateId[0]);
  return templateFile.getParents().hasNext() ? templateFile.getParents().next() : DriveApp.getRootFolder();
}

function processDocument(templateUrl, newDocName) {
  try {
    Logger.log('=== Starting Document Generation Process ===');
    Logger.log('Template URL: ' + templateUrl);
    Logger.log('New Document Name: ' + newDocName);
    
    if (!templateUrl || !newDocName) {
      throw new Error('Template URL and new document name are required');
    }
    
    // Get template folder
    Logger.log('Getting template folder...');
    const targetFolder = getTemplateFolder(templateUrl);
    Logger.log('Template folder located successfully');
    
    // Get the active sheet
    Logger.log('Reading spreadsheet data...');
    const sheet = SpreadsheetApp.getActiveSheet();
    const data = sheet.getDataRange().getValues();
    
    if (data.length < 2) {
      throw new Error('Spreadsheet must contain at least one data row');
    }
    
    // Get headers and clean them
    const headers = data[0].map(header => header.trim());
    Logger.log('Headers found in spreadsheet: ' + headers.join(', '));
    
    // Open template document
    Logger.log('Opening template document...');
    let templateDoc;
    try {
      templateDoc = DocumentApp.openByUrl(templateUrl);
      Logger.log('Template document opened successfully');
    } catch (e) {
      Logger.log('Failed to open template document: ' + e);
      throw new Error('Unable to open template document. Please check the URL and permissions.');
    }
    
    // Create new document in the same folder as template
    Logger.log('Creating new document: ' + newDocName);
    const newDoc = DocumentApp.create(newDocName);
    const docFile = DriveApp.getFileById(newDoc.getId());
    const copiedFile = docFile.makeCopy(newDocName, targetFolder);
    docFile.setTrashed(true);
    
    const newDocOpened = DocumentApp.openById(copiedFile.getId());
    const body = newDocOpened.getBody();
    body.clear();
    Logger.log('New document created in template folder');
    
    // Process each row
    const rows = data.slice(1);
    const totalRows = rows.length;
    Logger.log(`Processing ${totalRows} data rows...`);
    
    rows.forEach((row, rowIndex) => {
      const currentRow = rowIndex + 2; // +2 because we start from row 2
      Logger.log(`Processing row ${currentRow} of ${totalRows + 1}`);
      
      // Copy the template elements for this row
      const templateBody = templateDoc.getBody();
      const numChildren = templateBody.getNumChildren();
      
      // Copy each child element from template
      for (let i = 0; i < numChildren; i++) {
        const templateElement = templateBody.getChild(i);
        let newElement;
        
        if (templateElement.getType() === DocumentApp.ElementType.PARAGRAPH) {
          const templateParagraph = templateElement.asParagraph();
          // Skip if this is a page break
          if (templateParagraph.findElement(DocumentApp.ElementType.PAGE_BREAK)) {
            continue;
          }
          
          let text = templateParagraph.getText();
          
          // Replace variables in the text
          headers.forEach((header, colIndex) => {
            const variable = `{{${header.toLowerCase().replace(/\s+/g, '_')}}}`;
            const value = String(row[colIndex] || '');
            text = text.replace(new RegExp(variable, 'g'), value);
          });
          
          // Copy paragraph with formatting
          const newParagraph = body.appendParagraph(text);
          
          // Copy paragraph attributes
          newParagraph.setAttributes(templateParagraph.getAttributes());
          
          // Copy text element attributes
          const templateText = templateParagraph.editAsText();
          const newText = newParagraph.editAsText();
          
          // Copy formatting for each character
          for (let j = 0; j < text.length; j++) {
            const attrs = templateText.getAttributes(j);
            newText.setAttributes(j, j, attrs);
          }
          
        } else if (templateElement.getType() === DocumentApp.ElementType.TABLE) {
          // Handle tables if needed
          const newTable = body.appendTable(templateElement.asTable().copy());
          newTable.setAttributes(templateElement.getAttributes());
        } else if (templateElement.getType() === DocumentApp.ElementType.LIST_ITEM) {
          // Handle list items
          const templateListItem = templateElement.asListItem();
          let text = templateListItem.getText();
          
          // Replace variables in the text
          headers.forEach((header, colIndex) => {
            const variable = `{{${header.toLowerCase().replace(/\s+/g, '_')}}}`;
            const value = String(row[colIndex] || '');
            text = text.replace(new RegExp(variable, 'g'), value);
          });
          
          const newListItem = body.appendListItem(text);
          newListItem.setAttributes(templateListItem.getAttributes());
          newListItem.setGlyphType(templateListItem.getGlyphType());
          newListItem.setNestingLevel(templateListItem.getNestingLevel());
        }
      }
      
      Logger.log(`Completed processing row ${currentRow}`);
    });
    
    // Save and close
    Logger.log('Saving document...');
    newDocOpened.saveAndClose();
    
    Logger.log('=== Document generation completed successfully ===');
    return newDocOpened.getUrl();
    
  } catch (error) {
    Logger.log('ERROR in processDocument: ' + error);
    throw error.message || error;
  }
}

// Function to analyze template variables remains the same
function analyzeTemplateVariables(templateUrl) {
  try {
    const templateDoc = DocumentApp.openByUrl(templateUrl);
    const bodyText = templateDoc.getBody().getText();
    
    // Find all {{variable}} patterns
    const variablePattern = /\{\{([^}]+)\}\}/g;
    const matches = [...bodyText.matchAll(variablePattern)];
    const variables = matches.map(match => match[0]);
    
    // Remove duplicates
    const uniqueVariables = [...new Set(variables)];
    Logger.log('Template variables found: ' + uniqueVariables.join(', '));
    return uniqueVariables;
    
  } catch (error) {
    Logger.log('Error analyzing template: ' + error);
    throw 'Unable to analyze template. Please check the URL and permissions.';
  }
}