Generate PDF Invoices from Google Sheets Automatically

// CONFIGURATION
const TEMPLATE_ID = "YOUR_DOC_TEMPLATE_ID";
const FOLDER_ID = "YOUR_INVOICE_FOLDER_ID";
 
function generateInvoices() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoices');
  const data = sheet.getDataRange().getValues();
  
  // Skip headers
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const status = row[6]; // Col G
    
    if (status === "Sent") continue;
    
    try {
      createAndSendInvoice(row, i + 1, sheet);
    } catch (e) {
      Logger.log("Error: " + e.toString());
    }
  }
}
 
function createAndSendInvoice(row, rowIndex, sheet) {
  const [invNum, client, email, amount, date, desc] = row;
  
  // 1. Setup Files
  const template = DriveApp.getFileById(TEMPLATE_ID);
  const folder = DriveApp.getFolderById(FOLDER_ID);
  const tempFile = template.makeCopy(`Invoice ${invNum} - ${client}`, folder);
  const tempDoc = DocumentApp.openById(tempFile.getId());
  const body = tempDoc.getBody();
  
  // 2. Replace Placeholders
  body.replaceText("{{Client}}", client);
  body.replaceText("{{Amount}}", "$" + amount);
  body.replaceText("{{Date}}", new Date(date).toLocaleDateString());
  body.replaceText("{{Description}}", desc);
  body.replaceText("{{InvoiceNum}}", invNum);
  
  tempDoc.saveAndClose();
  
  // 3. Convert to PDF
  const pdf = tempFile.getAs(MimeType.PDF);
  
  // 4. Email
  MailApp.sendEmail({
    to: email,
    subject: `Invoice #${invNum} from [Your Company]`,
    body: `Hi ${client},\n\nPlease find your invoice attached for ${desc}.\n\nThanks!`,
    attachments: [pdf]
  });
  
  // 5. Cleanup & Update Status
  tempFile.setTrashed(true);
  sheet.getRange(rowIndex, 7).setValue("Sent"); // Col G
}