Automate Invoice Processing with Google Apps Script (End-to-End Tutorial)

Why Automate Invoicing with Apps Script

I've built invoicing systems for clients using everything from Zapier to custom Node.js services. But for small-to-mid businesses already living in Google Workspace, nothing beats Google Apps Script for automating invoice processing.

The reason is simple. Zero infrastructure cost, direct access to Sheets/Docs/Gmail/Drive, and a trigger system that runs your code on a schedule without a server. You're not paying for Zapier seats or maintaining a VPS. You're writing JavaScript that lives inside the tools you already use.

The typical manual invoicing workflow (copy data, open a template, fill in fields, export PDF, attach to email, send, track payment) takes 15-20 minutes per invoice. Multiply that by 50 clients and you've burned an entire workday. Every month.

So let's automate invoice processing with Google Apps Script, end to end.

Architecture Overview: The Invoice Pipeline

Before writing any code, here's what we're building:

1. Google Sheets stores client data, line items, and payment status
2. Google Docs holds the invoice template with merge placeholders
3. Apps Script pulls data from Sheets, merges it into the Doc template, converts to PDF
4. Gmail sends the invoice with the PDF attached
5. Sheets tracks sent status, payment status, and logs errors
6. Time-driven triggers run the pipeline monthly (or whatever cadence you need)

Optional: Stripe or QuickBooks API integration for payment reconciliation.

Every piece of this pipeline runs inside Google's ecosystem. No external dependencies required for the core flow.

Step 1: Create the Invoice Template in Google Docs

Create a Google Doc that looks like your actual invoice. Use placeholder tokens wrapped in double curly braces. Apps Script will replace these with real data at generation time.

Here's what your template should include:

{{CompanyName}}
{{CompanyAddress}}

INVOICE #{{InvoiceNumber}}
Date: {{InvoiceDate}}
Due Date: {{DueDate}}

Bill To:
{{ClientName}}
{{ClientEmail}}
{{ClientAddress}}

| Description | Quantity | Rate | Amount |
|-------------|----------|------|--------|
{{LineItems}}

Subtotal: ${{Subtotal}}
Tax ({{TaxRate}}%): ${{TaxAmount}}
Total: ${{Total}}

Payment Terms: {{PaymentTerms}}plainCopy

Save this doc and grab its ID from the URL. You'll need it in the script. The document ID is the long string between /d/ and /edit in the URL.

Step 2: Build the Invoice Data Sheet

Client Info, Line Items, and Totals

Set up your Google Sheet with these tabs:

Clients tab:

Invoices tab:

LineItems tab:

This structure keeps your data normalized. One invoice can have multiple line items. The script joins them at generation time.

Step 3: Generate PDFs from Template

This is where it gets fun. The script copies your template, replaces all placeholders, and converts the result to PDF.

Merging Data into the Doc Template

function generateInvoice(invoiceNumber) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const invoiceSheet = ss.getSheetByName('Invoices');
  const clientSheet = ss.getSheetByName('Clients');
  const lineItemSheet = ss.getSheetByName('LineItems');

  // Find the invoice row
  const invoiceData = getRowByColumn(invoiceSheet, 'InvoiceNumber', invoiceNumber);
  const clientData = getRowByColumn(clientSheet, 'ClientID', invoiceData.ClientID);
  const lineItems = getRowsByColumn(lineItemSheet, 'InvoiceNumber', invoiceNumber);

  // Calculate totals
  const subtotal = lineItems.reduce((sum, item) => sum + (item.Quantity * item.Rate), 0);
  const taxRate = 0; // Adjust per your needs
  const taxAmount = subtotal * (taxRate / 100);
  const total = subtotal + taxAmount;

  // Copy the template
  const templateId = 'YOUR_TEMPLATE_DOC_ID';
  const copyFile = DriveApp.getFileById(templateId)
    .makeCopy('Invoice_' + invoiceNumber);
  const doc = DocumentApp.openById(copyFile.getId());
  const body = doc.getBody();

  // Replace placeholders
  body.replaceText('{{InvoiceNumber}}', invoiceNumber);
  body.replaceText('{{InvoiceDate}}', invoiceData.InvoiceDate);
  body.replaceText('{{DueDate}}', invoiceData.DueDate);
  body.replaceText('{{ClientName}}', clientData.ClientName);
  body.replaceText('{{ClientEmail}}', clientData.ClientEmail);
  body.replaceText('{{ClientAddress}}', clientData.ClientAddress);
  body.replaceText('{{PaymentTerms}}', clientData.PaymentTerms);
  body.replaceText('{{CompanyName}}', 'Your Company Name');
  body.replaceText('{{CompanyAddress}}', 'Your Company Address');
  body.replaceText('{{Subtotal}}', subtotal.toFixed(2));
  body.replaceText('{{TaxRate}}', taxRate.toString());
  body.replaceText('{{TaxAmount}}', taxAmount.toFixed(2));
  body.replaceText('{{Total}}', total.toFixed(2));

  // Build line items text
  const lineItemsText = lineItems.map(item => {
    const amount = (item.Quantity * item.Rate).toFixed(2);
    return `${item.Description}\t${item.Quantity}\t$${item.Rate}\t$${amount}`;
  }).join('\n');
  body.replaceText('{{LineItems}}', lineItemsText);

  doc.saveAndClose();
  return { doc: doc, file: copyFile, total: total };
}javascriptCopy

Converting to PDF and Saving to Drive

function convertToPDFAndSave(copyFile, invoiceNumber) {
  const pdfBlob = DriveApp.getFileById(copyFile.getId())
    .getAs('application/pdf')
    .setName('Invoice_' + invoiceNumber + '.pdf');

  // Save to an Invoices folder in Drive
  const folderId = 'YOUR_INVOICES_FOLDER_ID';
  const folder = DriveApp.getFolderById(folderId);
  const pdfFile = folder.createFile(pdfBlob);

  // Delete the temporary Doc copy
  DriveApp.getFileById(copyFile.getId()).setTrashed(true);

  return pdfFile;
}javascriptCopy

Step 4: Auto-Email Invoices to Clients

Now we send the PDF. The GmailApp service handles this cleanly:

function emailInvoice(clientData, invoiceNumber, pdfFile, total) {
  const subject = `Invoice ${invoiceNumber} - $${total.toFixed(2)}`;

  const htmlBody = `
    <p>Hi ${clientData.ClientName},</p>
    <p>Please find attached invoice <strong>${invoiceNumber}</strong> 
    for <strong>$${total.toFixed(2)}</strong>.</p>
    <p>Payment is due per your agreed terms (${clientData.PaymentTerms}).</p>
    <p>If you have any questions about this invoice, just reply to this email.</p>
    <p>Thanks,<br>Daniel Diaz</p>
  `;

  const plainBody = `Invoice ${invoiceNumber} for $${total.toFixed(2)}. Payment due per ${clientData.PaymentTerms}. See attached PDF.`;

  GmailApp.sendEmail(clientData.ClientEmail, subject, plainBody, {
    htmlBody: htmlBody,
    attachments: [pdfFile.getAs('application/pdf')],
    name: 'Daniel Diaz'
  });

  return new Date();
}javascriptCopy

A few things I've learned the hard way about sending invoices via Apps Script:

• Gmail daily limit is 100 emails for consumer accounts, 1,500 for Workspace. If you're invoicing more than that, batch across multiple days.
• Always include a plain text fallback (the third argument to sendEmail). Some email clients strip HTML.
• Use `name` in the options so the email comes from your name, not just your email address.

Step 5: Track Payment Status in Sheets

After sending, update the invoice status in your Sheet:

function updateInvoiceStatus(invoiceNumber, status, pdfUrl, sentDate) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Invoices');
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  const invoiceCol = headers.indexOf('InvoiceNumber');
  const statusCol = headers.indexOf('Status');
  const sentDateCol = headers.indexOf('SentDate');
  const pdfLinkCol = headers.indexOf('PDFLink');

  for (let i = 1; i < data.length; i++) {
    if (data[i][invoiceCol] === invoiceNumber) {
      const row = i + 1;
      sheet.getRange(row, statusCol + 1).setValue(status);
      sheet.getRange(row, sentDateCol + 1).setValue(sentDate);
      sheet.getRange(row, pdfLinkCol + 1).setValue(pdfUrl);
      break;
    }
  }
}javascriptCopy

For overdue tracking, add a conditional formatting rule or a helper column:

function checkOverdueInvoices() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Invoices');
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const today = new Date();

  const dueDateCol = headers.indexOf('DueDate');
  const statusCol = headers.indexOf('Status');

  for (let i = 1; i < data.length; i++) {
    const dueDate = new Date(data[i][dueDateCol]);
    const status = data[i][statusCol];

    if (status === 'Sent' && today > dueDate) {
      sheet.getRange(i + 1, statusCol + 1).setValue('Overdue');
      // Optionally send a reminder email here
    }
  }
}javascriptCopy

Step 6: Set Up Monthly Recurring Triggers

Time-driven triggers let you run the entire pipeline automatically:

function setupTriggers() {
  // Generate and send invoices on the 1st of every month at 9 AM
  ScriptApp.newTrigger('processAllPendingInvoices')
    .timeBased()
    .onMonthDay(1)
    .atHour(9)
    .create();

  // Check for overdue invoices daily at 10 AM
  ScriptApp.newTrigger('checkOverdueInvoices')
    .timeBased()
    .everyDays(1)
    .atHour(10)
    .create();
}

function processAllPendingInvoices() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Invoices');
  const data = sheet.getDataRange().getValues();
  const headers = data[0];

  const invoiceCol = headers.indexOf('InvoiceNumber');
  const statusCol = headers.indexOf('Status');

  for (let i = 1; i < data.length; i++) {
    if (data[i][statusCol] === 'Draft') {
      const invoiceNumber = data[i][invoiceCol];
      try {
        processSingleInvoice(invoiceNumber);
      } catch (error) {
        logError(invoiceNumber, error);
      }
    }
  }
}javascriptCopy

Run setupTriggers() once manually from the Apps Script editor. After that, the system runs itself.

One critical detail. Use `LockService` to prevent overlapping executions. If your trigger fires while a previous run is still processing, you'll get duplicate invoices:

function processAllPendingInvoices() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) {
    console.log('Another instance is running. Skipping.');
    return;
  }

  try {
    // ... processing logic from above
  } finally {
    lock.releaseLock();
  }
}javascriptCopy

Bonus: Integrating with QuickBooks or Stripe API

If you want payment reconciliation, UrlFetchApp lets you call external APIs directly from Apps Script.

Stripe example, checking payment status:

function checkStripePayment(stripeInvoiceId) {
  const STRIPE_KEY = PropertiesService.getScriptProperties()
    .getProperty('STRIPE_SECRET_KEY');

  const response = UrlFetchApp.fetch(
    'https://api.stripe.com/v1/invoices/' + stripeInvoiceId,
    {
      headers: {
        'Authorization': 'Bearer ' + STRIPE_KEY
      },
      muteHttpExceptions: true
    }
  );

  const data = JSON.parse(response.getContentText());
  return data.status; // 'paid', 'open', 'void', etc.
}javascriptCopy

QuickBooks example, creating an invoice:

function createQuickBooksInvoice(invoiceData) {
  const QB_TOKEN = PropertiesService.getScriptProperties()
    .getProperty('QB_ACCESS_TOKEN');
  const QB_COMPANY_ID = PropertiesService.getScriptProperties()
    .getProperty('QB_COMPANY_ID');

  const payload = {
    Line: invoiceData.lineItems.map(item => ({
      Amount: item.Quantity * item.Rate,
      DetailType: 'SalesItemLineDetail',
      SalesItemLineDetail: {
        Qty: item.Quantity,
        UnitPrice: item.Rate
      }
    })),
    CustomerRef: { value: invoiceData.qbCustomerId }
  };

  const response = UrlFetchApp.fetch(
    `https://quickbooks.api.intuit.com/v3/company/${QB_COMPANY_ID}/invoice`,
    {
      method: 'POST',
      headers: {
        'Authorization': 'Bearer ' + QB_TOKEN,
        'Content-Type': 'application/json',
        'Accept': 'application/json'
      },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    }
  );

  return JSON.parse(response.getContentText());
}javascriptCopy

Error Handling and Logging

Production invoice systems need proper error handling. Here's the pattern I use on every Apps Script automation:

function logError(invoiceNumber, error) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let logSheet = ss.getSheetByName('ErrorLog');

  if (!logSheet) {
    logSheet = ss.insertSheet('ErrorLog');
    logSheet.appendRow(['Timestamp', 'InvoiceNumber', 'Error', 'Stack']);
  }

  logSheet.appendRow([
    new Date(),
    invoiceNumber,
    error.message,
    error.stack
  ]);

  // Alert yourself for critical failures
  MailApp.sendEmail(
    'daniel@diazovate.com',
    'Invoice Error: ' + invoiceNumber,
    'Error: ' + error.message + '\n\nStack: ' + error.stack
  );
}

function processSingleInvoice(invoiceNumber) {
  try {
    const clientSheet = SpreadsheetApp.getActiveSpreadsheet()
      .getSheetByName('Clients');
    const invoiceResult = generateInvoice(invoiceNumber);
    const pdfFile = convertToPDFAndSave(invoiceResult.file, invoiceNumber);
    const clientData = getRowByColumn(clientSheet, 'ClientID',
      getRowByColumn(
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoices'),
        'InvoiceNumber', invoiceNumber
      ).ClientID
    );

    const sentDate = emailInvoice(
      clientData, invoiceNumber, pdfFile, invoiceResult.total
    );

    updateInvoiceStatus(invoiceNumber, 'Sent', pdfFile.getUrl(), sentDate);
    console.log('Successfully processed: ' + invoiceNumber);
  } catch (error) {
    logError(invoiceNumber, error);
    updateInvoiceStatus(invoiceNumber, 'Error', '', '');
    throw error; // Re-throw so the batch processor knows it failed
  }
}javascriptCopy

Three things that will save you in production:

1. Log to a Sheet, not just `console.log`. Apps Script execution logs expire. Your error Sheet doesn't.
2. Email yourself on failure. Don't find out a week later that invoices stopped sending.
3. Use `muteHttpExceptions: true` on all UrlFetchApp calls. Without it, a 400 response throws an exception instead of returning the error body you need to debug.

Full Source Code and Setup Instructions

Here's the complete setup process:

1. Create the Google Sheet with three tabs: Clients, Invoices, LineItems (schema above)
2. Create the invoice template in Google Docs with {{placeholder}} tokens
3. Create an Invoices folder in Google Drive for PDF storage
4. Open Apps Script from the Sheet (Extensions > Apps Script)
5. Paste the full source code (all functions from this tutorial)
6. Update the configuration at the top of your script:

const CONFIG = {
  TEMPLATE_DOC_ID: 'your-template-doc-id',
  INVOICES_FOLDER_ID: 'your-drive-folder-id',
  COMPANY_NAME: 'Your Company',
  COMPANY_ADDRESS: '123 Business St',
  TAX_RATE: 0,
  SENDER_NAME: 'Your Name'
};javascriptCopy

1. Run `setupTriggers()` once to create the automated schedule
2. Test with a single invoice by running processSingleInvoice('INV-2026-001') from the editor

The helper functions you'll need:

function getRowByColumn(sheet, columnName, value) {
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const col = headers.indexOf(columnName);

  for (let i = 1; i < data.length; i++) {
    if (data[i][col] === value) {
      const row = {};
      headers.forEach((header, index) => {
        row[header] = data[i][index];
      });
      return row;
    }
  }
  return null;
}

function getRowsByColumn(sheet, columnName, value) {
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const col = headers.indexOf(columnName);
  const rows = [];

  for (let i = 1; i < data.length; i++) {
    if (data[i][col] === value) {
      const row = {};
      headers.forEach((header, index) => {
        row[header] = data[i][index];
      });
      rows.push(row);
    }
  }
  return rows;
}javascriptCopy

That's the whole system. Sheets for data, Docs for templates, Drive for storage, Gmail for delivery, and Apps Script tying it all together. No external services, no monthly fees, no servers to maintain.

Start with the basic flow: generate, send, track. Add the Stripe/QuickBooks integration once the core pipeline is solid. And whatever you do, test with a single invoice before enabling the monthly trigger. Trust me on that one.