generate draft emails from a google sheet and save the drafts to gmail

code.gs

function createEmailDrafts() {
  Logger.log('Starting email draft creation process...');
  
  // Get the active spreadsheet and sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  Logger.log('Retrieved active sheet');
  
  // Get all data from the sheet
  const data = sheet.getDataRange().getValues();
  Logger.log(`Found ${data.length} rows of data (including header)`);
  
  // Get headers (first row) and clean them
  const headers = data[0].map(header => {
    let cleanHeader = header.toString().trim();
    Logger.log(`Processing header: [${header}] -> [${cleanHeader}]`);
    return cleanHeader;
  });
  
  Logger.log('All headers found: ' + JSON.stringify(headers));
  
  // Create a map of header indices for easy reference
  const headerIndices = {};
  headers.forEach((header, index) => {
    headerIndices[header] = index;
    Logger.log(`Header '${header}' is at index ${index}`);
  });
  
  // Email template - Note the moved $ symbol outside the placeholder
  const emailTemplate = {
    subject: "Your order #{{orderNumber}} has been processed",
    body: `Dear {{customerName}},

Thank you for your order. We're pleased to confirm that your order #{{orderNumber}} has been processed.

Order Details:
Product: {{productName}}
Quantity: {{quantity}}
Total: $ {{amount}}

Expected Delivery Date: {{deliveryDate}}

If you have any questions, please don't hesitate to contact us.

Best regards,
{{companyName}}`
  };
  
  Logger.log('Email template loaded');
  
  // Process each row starting from row 2 (skipping headers)
  for (let i = 1; i < data.length; i++) {
    Logger.log(`\nProcessing row ${i + 1}`);
    const row = data[i];
    
    // Create an object to store the variables for this row
    const variables = {};
    
    // Log the actual amount value before processing
    Logger.log(`Raw amount value for row ${i + 1}: [${row[headerIndices.amount]}]`);
    
    headers.forEach((header, index) => {
      let value = row[index];
      
      // Special handling for amount
      if (header === 'amount') {
        if (typeof value === 'number') {
          value = value.toFixed(2);
          Logger.log(`Formatted number amount: ${value}`);
        } else if (typeof value === 'string' && !isNaN(parseFloat(value))) {
          value = parseFloat(value).toFixed(2);
          Logger.log(`Parsed string amount: ${value}`);
        } else {
          Logger.log(`WARNING: Unexpected amount format: ${typeof value} - ${value}`);
          value = '0.00';
        }
      } else {
        value = value ? value.toString() : '';
      }
      
      variables[header] = value;
      Logger.log(`Set ${header} = [${variables[header]}]`);
    });
    
    Logger.log(`Variables for row ${i + 1}:`);
    Logger.log(JSON.stringify(variables, null, 2));
    
    // Replace variables in the template
    let emailSubject = emailTemplate.subject;
    let emailBody = emailTemplate.body;
    
    // Replace all variables in subject and body
    Object.keys(variables).forEach(key => {
      const placeholder = `{{${key}}}`;
      const value = variables[key];
      
      Logger.log(`Replacing ${placeholder} with [${value}]`);
      
      emailSubject = emailSubject.replace(new RegExp(placeholder, 'g'), value);
      emailBody = emailBody.replace(new RegExp(placeholder, 'g'), value);
    });
    
    // Debug: Check final email content
    Logger.log('Final email subject: ' + emailSubject);
    Logger.log('Final email body preview: ' + emailBody.substring(0, 100) + '...');
    
    // Check for any remaining unreplaced placeholders
    const remainingPlaceholders = emailBody.match(/{{[^}]+}}/g);
    if (remainingPlaceholders) {
      Logger.log('WARNING - Unreplaced placeholders: ' + remainingPlaceholders.join(', '));
    }
    
    try {
      // Create draft email
      GmailApp.createDraft(
        variables.recipientEmail,
        emailSubject,
        emailBody
      );
      
      // Mark success
      sheet.getRange(i + 1, headers.length + 1).setValue('Draft Created');
      Logger.log(`Successfully created draft for row ${i + 1}`);
      
    } catch (error) {
      const errorMessage = error.toString();
      Logger.log(`ERROR creating draft for row ${i + 1}: ${errorMessage}`);
      sheet.getRange(i + 1, headers.length + 1).setValue(`Failed: ${errorMessage}`);
    }
  }
  
  Logger.log('Email draft creation process complete');
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Email Tools')
    .addItem('Create Email Drafts', 'createEmailDrafts')
    .addToUi();
}

// Debug helper function
function testFirstRow() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const firstRow = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  Logger.log('Testing first data row:');
  headers.forEach((header, index) => {
    Logger.log(`${header}: [${firstRow[index]}] (${typeof firstRow[index]})`);
  });
}