Automating PDF Invoices from Google Docs Templates with Apps Script

Manual data entry between Google Sheets and Google Docs is a silent productivity killer. Whether you are a business analyst generating monthly invoices or an HR administrator processing contracts, copy-pasting data into templates is error-prone and unscalable.

While "mail merge" add-ons exist, they often pose security risks, cost money, or lack the flexibility required for custom workflows.

This guide provides a robust, Principal Engineer-level implementation of an automated pipeline using Google Apps Script. We will build a system that reads structured data from Sheets, injects it into a Docs template, converts the result to a PDF, and emails it to the client—all with zero manual intervention.

The Technical Challenge: The Document Object Model (DOM)

Before writing code, it is critical to understand why this process often breaks for beginners.

Google Docs are not simple text files; they are complex structures represented by a Document Object Model (DOM). When you type into a Doc, you are manipulating objects (Paragraphs, Text, Tables) within a tree structure.

The "Blank PDF" Problem

The most common failure in Apps Script automation is the "Blank PDF" error. A developer writes code to replace text, then immediately converts the file to a PDF, only to find the PDF still contains the original placeholders.

Root Cause: The DocumentApp service optimizes performance by batching updates. Changes made via body.replaceText() are queued in memory. If you attempt to convert the file to a blob (binary large object) before forcing a save, the API captures the file state before the updates were applied.

Our solution explicitly handles the saveAndClose() input/output flush to ensure data integrity before conversion.

Prerequisites

To follow this tutorial, you need two assets:

Google Sheet: A source of truth containing columns for Client Name, Invoice Number, Amount, Date, Email, and Status.
Google Doc Template: A formatted document using unique placeholders. We will use the handlebars syntax (e.g., {{ClientName}}) to avoid accidental replacements of common words.

The Solution: A Production-Ready Script

The following script implements a complete pipeline. It uses modern JavaScript (ES6+) features available in the V8 runtime, such as const/let, arrow functions, and template literals.

Step 1: Accessing the Script Editor

Open your Google Sheet.
Go to Extensions > Apps Script.
Rename the project to "Invoice Automator".

Step 2: The Codebase

Copy the following code into your Code.gs file. Replace the TEMPLATE_ID constant with the ID found in your template's URL (the string between /d/ and /edit).

/**
 * Configuration Constants
 * Replace these with your actual IDs and Column Indices
 */
const CONFIG = {
  TEMPLATE_ID: '1x_YOUR_TEMPLATE_ID_HERE_abc123', // From Doc URL
  FOLDER_ID: '1y_YOUR_DESTINATION_FOLDER_ID_HERE', // Where to save PDFs
  SHEET_NAME: 'Invoices',
  // Zero-based column indices (A=0, B=1, etc.)
  COLS: {
    CLIENT: 0,
    EMAIL: 1,
    INVOICE_NUM: 2,
    AMOUNT: 3,
    DATE: 4,
    STATUS: 5
  }
};

/**
 * Main function to execute the invoice generation pipeline.
 */
function generateInvoices() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  
  // Fetch all data at once to minimize read operations (Best Practice)
  const dataRange = sheet.getDataRange();
  const data = dataRange.getValues();
  const headers = data.shift(); // Remove header row
  
  // Iterate through rows
  data.forEach((row, index) => {
    const status = row[CONFIG.COLS.STATUS];
    
    // Skip if already processed
    if (status === 'Sent') return;

    try {
      const invoiceData = extractRowData(row);
      const pdfBlob = createInvoicePDF(invoiceData);
      
      sendEmail(invoiceData, pdfBlob);
      
      // Update status column (row index + 2 accounts for 0-index and header removal)
      sheet.getRange(index + 2, CONFIG.COLS.STATUS + 1).setValue('Sent');
      console.log(`Success: Invoice generated for ${invoiceData.clientName}`);
      
    } catch (error) {
      console.error(`Failed to process row ${index + 2}: ${error.message}`);
      sheet.getRange(index + 2, CONFIG.COLS.STATUS + 1).setValue(`Error: ${error.message}`);
    }
  });
}

/**
 * Helper to structure row data into a usable object.
 * Handles formatting for currency and dates.
 */
function extractRowData(row) {
  return {
    clientName: row[CONFIG.COLS.CLIENT],
    email: row[CONFIG.COLS.EMAIL],
    invoiceNum: row[CONFIG.COLS.INVOICE_NUM],
    // Format date to readable string
    date: Utilities.formatDate(new Date(row[CONFIG.COLS.DATE]), Session.getScriptTimeZone(), "MMMM dd, yyyy"),
    // formatting currency (adjust symbol as needed)
    amount: new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD' }).format(row[CONFIG.COLS.AMOUNT])
  };
}

/**
 * Core logic: Duplicates template, replaces text, and converts to PDF.
 */
function createInvoicePDF(data) {
  const templateFile = DriveApp.getFileById(CONFIG.TEMPLATE_ID);
  const destinationFolder = DriveApp.getFolderById(CONFIG.FOLDER_ID);
  
  // 1. Create a temporary copy of the template
  const tempFile = templateFile.makeCopy(`Invoice_${data.invoiceNum}_${data.clientName}`, destinationFolder);
  const tempDoc = DocumentApp.openById(tempFile.getId());
  const body = tempDoc.getBody();

  // 2. Perform text replacements using the {{Key}} pattern
  body.replaceText('{{ClientName}}', data.clientName);
  body.replaceText('{{InvoiceNum}}', data.invoiceNum);
  body.replaceText('{{Date}}', data.date);
  body.replaceText('{{Amount}}', data.amount);

  // 3. CRITICAL: Save and close to flush changes before conversion
  tempDoc.saveAndClose();

  // 4. Convert the updated doc to a PDF Blob
  const pdfBlob = tempFile.getAs(MimeType.PDF);
  
  // 5. Cleanup: Delete the temporary Google Doc to keep Drive clean
  tempFile.setTrashed(true);

  return pdfBlob;
}

/**
 * Sends the email with the PDF attachment.
 */
function sendEmail(data, pdfBlob) {
  const subject = `Invoice #${data.invoiceNum} from My Company`;
  const htmlBody = `
    <p>Hi ${data.clientName},</p>
    <p>Please find attached invoice #${data.invoiceNum} for <b>${data.amount}</b>.</p>
    <p>Thank you for your business.</p>
  `;

  MailApp.sendEmail({
    to: data.email,
    subject: subject,
    htmlBody: htmlBody,
    attachments: [pdfBlob]
  });
}

Deep Dive: Why This Implementation Works

This script incorporates several architectural patterns designed for stability and maintenance.

1. Data Object Abstraction

Notice the extractRowData function. Instead of passing raw array indices (e.g., row[3]) throughout the script, we map the row to a structured object immediately. If the column order in your Sheet changes, you only need to update the CONFIG object and extractRowData. The rest of your logic remains untouched.

2. The `saveAndClose()` Pattern

In the createInvoicePDF function, step 3 is the most critical line of code.

tempDoc.saveAndClose();
const pdfBlob = tempFile.getAs(MimeType.PDF);

When replaceText is called, the operation updates the DocumentApp instance in the server's RAM. The file on Google Drive (storage) has not yet changed. saveAndClose() forces the execution buffer to write to the underlying file system. Without this, getAs(MimeType.PDF) would convert the file as it existed before the script ran—resulting in a template filled with placeholders.

3. Temporary File Cleanup

Generating PDFs requires an intermediate step: creating a Google Doc copy. If you process 500 invoices, you don't want 500 duplicate Google Docs cluttering your Drive. The script creates the copy, converts it to a blob (RAM), and immediately trashes the temporary file using tempFile.setTrashed(true).

Handling Common Pitfalls

Rate Limiting (Quotas)

Google Apps Script has daily quotas (e.g., emails sent per day, script runtime duration). If you are processing thousands of invoices, the script may time out.

The Fix: For large batches, do not use a forEach loop. Instead, process rows in batches of 50. Use the script's PropertiesService to store the index of the last processed row, and set up a Time-Driven Trigger to run the script every 10 minutes until all rows are complete.

Date and Currency Formatting

Raw data pulled from Sheets often behaves unexpectedly in JavaScript.

Dates: A cell displaying "12/01/2023" comes into Apps Script as a JavaScript Date object. Concatenating this directly into a string results in something like Tue Dec 01 2023 00:00:00 GMT.... We used Utilities.formatDate to solve this.
Currency: A cell displaying "$500.00" might come in as the number 500. We used Intl.NumberFormat to ensure the PDF displays the currency symbol and two decimal places.

Conclusion

By leveraging the DocumentApp and DriveApp services correctly, we transform a manual administrative burden into a one-click operation. This approach ensures data accuracy, maintains professional formatting via PDF, and provides a scalable architecture that grows with your business needs.

The key to success lies in respecting the object model: duplicate the template, modify the object, force the save, and only then convert to the final output format.

Programming Tutorials

Search This Blog