Automation✓ Verified Guide

Automated PDF Generation from Google Sheets & Docs using Apps Script

Complete technical implementation guide to dynamically generate, format, and email customized PDF invoices and reports directly from spreadsheet rows.

Editorial Standards & Verification: This technical tutorial has been tested in live Google Workspace and Cloud environments by the GoogleSolution engineering desk. Code snippets are compliant with latest API specifications as of October 2026.

Automating Enterprise PDF Generation

Generating standardized customer invoices, certificates, and compliance reports manually consumes hundreds of labor hours annually. By uniting Google Sheets as a database, Google Docs as a template layout engine, and Google Apps Script as the orchestrator, organizations can establish a zero-cost, automated document generation pipeline that compiles data, exports polished vector PDFs, and emails them to clients automatically.

Architecture: Template Replacement Pattern

The most resilient enterprise document generation architecture follows the Template Replacement Pattern:

  1. Master Google Doc Template: Design your invoice or certificate in Google Docs, using double-curly-bracket merge tags (e.g. {{CustomerName}}, {{InvoiceNumber}}, {{TotalDue}}).
  2. File Cloning: When a row in Google Sheets is triggered, the script makes an isolated copy of the template in a designated Google Drive destination folder.
  3. Text Replacement: The script opens the cloned document and replaces all template merge tags with live cell values.
  4. PDF Conversion: The script converts the populated document into a PDF blob: docFile.getAs('application/pdf').
  5. Delivery & Cleanup: The PDF is attached to an email sent via GmailApp.sendEmail(), and the temporary Google Doc is permanently trashed to prevent Drive clutter.

Complete Production Apps Script Code

/**
 * Automated PDF Invoice Generator & Email Dispatcher
 */
function generateAndEmailInvoices() {
  const TEMPLATE_DOC_ID = "YOUR_GOOGLE_DOC_TEMPLATE_ID_HERE";
  const DESTINATION_FOLDER_ID = "YOUR_GOOGLE_DRIVE_FOLDER_ID_HERE";
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Invoices");
  const data = sheet.getDataRange().getValues();
  const folder = DriveApp.getFolderById(DESTINATION_FOLDER_ID);
  const templateFile = DriveApp.getFileById(TEMPLATE_DOC_ID);

  // Iterate across spreadsheet rows (skipping header row 0)
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const invoiceNumber = row[0];
    const clientName = row[1];
    const clientEmail = row[2];
    const invoiceDate = Utilities.formatDate(new Date(row[3]), Session.getScriptTimeZone(), "yyyy-MM-dd");
    const amountDue = Utilities.formatString("$%.2f", row[4]);
    const status = row[5];

    // Only process rows marked as 'Pending'
    if (status !== "Pending" || !clientEmail) continue;

    // 1. Clone Template Doc
    const copyFile = templateFile.makeCopy(`Invoice_${invoiceNumber}_${clientName}`, folder);
    const copyDoc = DocumentApp.openById(copyFile.getId());
    const body = copyDoc.getBody();

    // 2. Replace Merge Tags
    body.replaceText("{{InvoiceNumber}}", invoiceNumber);
    body.replaceText("{{CustomerName}}", clientName);
    body.replaceText("{{Date}}", invoiceDate);
    body.replaceText("{{AmountDue}}", amountDue);
    copyDoc.saveAndClose();

    // 3. Export as PDF Blob
    const pdfBlob = copyFile.getAs("application/pdf").setName(`Invoice_${invoiceNumber}.pdf`);

    // 4. Email to Customer
    GmailApp.sendEmail(clientEmail, `Your Invoice #${invoiceNumber} from Enterprise Solution`, 
      `Hello ${clientName},\n\nPlease find your invoice attached.\n\nTotal Due: ${amountDue}\n\nThank you for your business.`, {
        attachments: [pdfBlob],
        name: "Billing Department"
      });

    // 5. Update Status in Sheet
    sheet.getRange(i + 1, 6).setValue("Sent");
    sheet.getRange(i + 1, 7).setValue(new Date());

    // 6. Delete temporary Google Doc clone to keep Drive clean
    copyFile.setTrashed(true);
  }
}

Security & Data Privacy Best Practices

When generating confidential financial or medical PDFs in Google Workspace:

  • Store generated PDFs in a restricted Google Drive folder with external sharing disabled.
  • Execute test runs on internal dummy records before sending automated emails to active clients.
  • Set up Google Workspace DLP (Data Loss Prevention) rules to audit outbound emails containing social security or credit card numbers.

Frequently Asked Questions

Can I generate multi-row invoice line item tables dynamically?

Yes. In the Google Apps Script code, use body.getTables() to locate the invoice table, and call table.appendTableRow() inside a loop to dynamically populate individual item descriptions, quantities, and prices.

How do I prevent hitting daily Gmail sending limits?

Google Workspace accounts have a daily sending limit of 2,000 emails per day (100 for free @gmail.com accounts). For high-volume transaction catalogs, integrate with transactional email APIs like SendGrid or Mailgun via UrlFetchApp.

Security Best Practices: Restricting PDF Permissions Programmatically

When generating confidential contracts or employee salary slips, controlling document sharing permissions is essential. Google Apps Script provides granular access control via DriveApp.Access and DriveApp.Permission.

Ensure newly created PDF files are saved to a secured destination folder with restricted viewer rights. If emailing documents to external clients, attach the binary PDF blob directly to GmailApp.sendEmail() rather than granting broad public link access to corporate Google Drive folders.

Handling Dynamic Multi-Page Pagination in Google Docs

When injecting dynamic tables with variable item counts, ensure your master Google Doc template defines repeating table headers on page breaks. This guarantees that itemized invoice receipts spanning across multiple pages maintain professional layout styling.