How to Auto-Generate & Email PDF Invoices from Google Sheets Using Apps Script

Introduction

Billing operations in small-to-medium businesses frequently rely on manual invoice generation: copying customer details into a template, exporting a PDF, and attaching it to a Gmail draft. By leveraging Google Sheets as a database, Google Docs as an invoice template, and Google Apps Script as the orchestrator, you can fully automate customized PDF invoice creation and email delivery with a single click.

The Architecture

This automated invoicing engine operates across three integrated Google services:

• Google Sheet: Houses transaction records (Client Name, Email, Invoice Number, Line Items, Amount, Status).
• Google Doc Template: Formatted invoice document containing merge tags like {{ClientName}}, {{InvoiceNumber}}, and {{TotalDue}}.
• Google Drive & Gmail: Automatically converts the merged document into a locked PDF, stores it in an organized Drive folder, and dispatches it via Gmail.

Step 1: Create Your Google Doc Invoice Template

Create a clean Google Doc with your company logo, bank details, and merge fields formatted exactly like this:

• {{InvoiceNumber}}
• {{InvoiceDate}}
• {{ClientName}}
• {{ClientEmail}}
• {{ServiceDescription}}
• {{TotalAmount}}

Note the Document ID from the URL bar (e.g., docs.google.com/document/d/<TEMPLATE_ID>/edit).

Step 2: The Core Apps Script Code

Open your Google Sheet, navigate to Extensions > Apps Script, and implement the script:

function generateAndEmailInvoices() {
  const TEMPLATE_DOC_ID = "YOUR_GOOGLE_DOC_TEMPLATE_ID_HERE";
  const DESTINATION_FOLDER_ID = "YOUR_GOOGLE_DRIVE_FOLDER_ID_HERE";
  const SHEET_NAME = "Invoices";
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const data = sheet.getDataRange().getValues();
  const folder = DriveApp.getFolderById(DESTINATION_FOLDER_ID);
  
  // Headers: [Invoice#, Client Name, Email, Service, Total, Status, PDF Link]
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const invoiceNum = row[0];
    const clientName = row[1];
    const clientEmail = row[2];
    const serviceDesc = row[3];
    const totalAmount = row[4];
    const status = row[5];
    
    // Skip already generated invoices
    if (status === "Sent" || status === "Paid") continue;
    
    // 1. Make a copy of the template
    const templateFile = DriveApp.getFileById(TEMPLATE_DOC_ID);
    const copyFile = templateFile.makeCopy("Invoice_" + invoiceNum + "_" + clientName, folder);
    const copyDoc = DocumentApp.openById(copyFile.getId());
    const body = copyDoc.getBody();
    
    // 2. Replace merge tags
    body.replaceText("{{InvoiceNumber}}", invoiceNum);
    body.replaceText("{{InvoiceDate}}", Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd"));
    body.replaceText("{{ClientName}}", clientName);
    body.replaceText("{{ClientEmail}}", clientEmail);
    body.replaceText("{{ServiceDescription}}", serviceDesc);
    body.replaceText("{{TotalAmount}}", "$" + Number(totalAmount).toFixed(2));
    
    copyDoc.saveAndClose();
    
    // 3. Export as PDF
    const pdfBlob = copyFile.getAs("application/pdf").setName("Invoice_" + invoiceNum + ".pdf");
    const pdfFile = folder.createFile(pdfBlob);
    
    // 4. Delete the temporary Google Doc copy
    copyFile.setTrashed(true);
    
    // 5. Send Email via GmailApp
    const emailSubject = "Invoice #" + invoiceNum + " from Your Company";
    const emailBody = "Dear " + clientName + ",\n\nPlease find attached invoice #" + 
      invoiceNum + " for " + serviceDesc + ".\nTotal Due: $" + Number(totalAmount).toFixed(2) + 
      "\n\nThank you for your business!\nYour Company Team";
      
    GmailApp.sendEmail(clientEmail, emailSubject, emailBody, {
      attachments: [pdfBlob],
      name: "Accounts Receivable"
    });
    
    // 6. Update sheet row with status and file link
    sheet.getRange(i + 1, 6).setValue("Sent");
    sheet.getRange(i + 1, 7).setValue(pdfFile.getUrl());
  }
}

Step 3: Creating a Custom Menu Button in Sheets

Add an onOpen() trigger to let accounting staff run the batch process directly from the spreadsheet top navigation:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("⚡ Invoicing")
    .addItem("Generate & Send Invoices", "generateAndEmailInvoices")
    .addToUi();
}

Enterprise Tips

• Draft Mode: During initial testing, replace GmailApp.sendEmail() with GmailApp.createDraft() to review emails before they reach real client inboxes.
• PDF Table Formatting: For invoices with multiple line items, use body.getTables() in Apps Script to dynamically append rows to your invoice table.