How to Generate and Email PDF Invoices from Google Sheets

Google Apps Script can read invoice rows from Google Sheets, fill a Google Docs template, create a PDF, and send it with MailApp. A safe workflow needs preview mode, validation, duplicate-invoice checks, a sent timestamp, an error column, and a rule that marks a row as sent only after the email call completes.

Prepare a sheet with audit columns

Create a Google Docs template

INVOICE

Invoice Number: {{INVOICE_NUMBER}}
Date: {{DATE}}
Client: {{CLIENT}}
Description: {{DESCRIPTION}}
Quantity: {{QUANTITY}}
Unit Price: {{UNIT_PRICE}}
Total: {{TOTAL}}

Use exact placeholder names and copy the document ID from the template URL. Keep the template in a Drive location the script account can access.

Add a preview-first Apps Script

const TEMPLATE_DOC_ID = "PASTE_YOUR_GOOGLE_DOC_ID_HERE";
const SEND_EMAILS = false; // Keep false until preview rows are verified.

function generateAndEmailInvoices() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Invoices");
  if (!sheet) throw new Error('Sheet named "Invoices" was not found.');

  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return;

  const invoiceCounts = new Map();
  values.slice(1).forEach(row => {
    const key = String(row[2] || "").trim();
    if (key) invoiceCounts.set(key, (invoiceCounts.get(key) || 0) + 1);
  });

  for (let rowIndex = 1; rowIndex < values.length; rowIndex++) {
    const rowNumber = rowIndex + 1;
    const [client, email, invoiceNumber, description, quantity, unitPrice, status] = values[rowIndex];

    if (String(status).trim() === "Sent") continue;

    const errors = [];
    const invoiceId = String(invoiceNumber || "").trim();
    const recipient = String(email || "").trim();
    const numericQuantity = Number(quantity);
    const numericUnitPrice = Number(unitPrice);

    if (!client) errors.push("Client is missing");
    if (!recipient || !/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(recipient)) errors.push("Email needs review");
    if (!invoiceId) errors.push("Invoice number is missing");
    if (invoiceCounts.get(invoiceId) > 1) errors.push("Duplicate invoice number");
    if (!Number.isFinite(numericQuantity) || numericQuantity <= 0) errors.push("Quantity must be positive");
    if (!Number.isFinite(numericUnitPrice) || numericUnitPrice < 0) errors.push("Unit price must be zero or greater");

    if (errors.length) {
      sheet.getRange(rowNumber, 7).setValue("Needs review");
      sheet.getRange(rowNumber, 9).setValue(errors.join("; "));
      continue;
    }

    if (SEND_EMAILS && MailApp.getRemainingDailyQuota() < 1) {
      throw new Error("No remaining MailApp recipient quota for this account today.");
    }

    const total = numericQuantity * numericUnitPrice;
    const temporaryFile = DriveApp.getFileById(TEMPLATE_DOC_ID)
      .makeCopy(`Invoice-${invoiceId}-TEMP`);

    try {
      const document = DocumentApp.openById(temporaryFile.getId());
      const body = document.getBody();

      replacePlaceholder(body, "INVOICE_NUMBER", invoiceId);
      replacePlaceholder(body, "DATE", Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd"));
      replacePlaceholder(body, "CLIENT", client);
      replacePlaceholder(body, "DESCRIPTION", description || "");
      replacePlaceholder(body, "QUANTITY", numericQuantity);
      replacePlaceholder(body, "UNIT_PRICE", numericUnitPrice.toFixed(2));
      replacePlaceholder(body, "TOTAL", total.toFixed(2));
      document.saveAndClose();

      const pdf = temporaryFile.getAs(MimeType.PDF)
        .setName(`Invoice-${invoiceId}.pdf`);

      if (SEND_EMAILS) {
        MailApp.sendEmail({
          to: recipient,
          subject: `Invoice ${invoiceId}`,
          body: `Hello ${client},\n\nPlease find your invoice attached.\n\nThank you.`,
          attachments: [pdf]
        });
        sheet.getRange(rowNumber, 7).setValue("Sent");
        sheet.getRange(rowNumber, 8).setValue(new Date());
      } else {
        sheet.getRange(rowNumber, 7).setValue("Preview OK");
      }

      sheet.getRange(rowNumber, 9).clearContent();
    } catch (error) {
      sheet.getRange(rowNumber, 7).setValue("Needs review");
      sheet.getRange(rowNumber, 9).setValue(String(error.message || error));
    } finally {
      temporaryFile.setTrashed(true);
    }
  }
}

function replacePlaceholder(body, name, value) {
  const safeValue = String(value).replace(/\\/g, "\\\\").replace(/\$/g, "\\$");
  body.replaceText(`\\{\\{${name}\\}\\}`, safeValue);
}

Authorize and run one fabricated row

1. Set the spreadsheet time zone and confirm the template ID.
2. Keep SEND_EMAILS = false.
3. Run the function manually and review the permission request.
4. Confirm the test row becomes Preview OK and no recipient received mail.
5. Temporarily keep the generated file instead of trashing it if you need to inspect layout during development, then restore cleanup before normal use.

Verify the PDF before enabling email

Check invoice number, client, description, quantity, unit price, total, date, page breaks, currency wording, and template branding. The example performs arithmetic but does not define tax, discounts, legal invoice requirements, or a currency conversion rule.

Enable sending cautiously

Change SEND_EMAILS to true only after preview rows are correct. Use your own email as the first recipient. MailApp sends on behalf of the authorized account and is subject to account quotas. A successful script execution does not prove that a message avoided spam filtering or reached the intended human.

Prevent duplicate sends

The script skips rows marked Sent and rejects duplicate invoice numbers in the current sheet. Do not clear status cells merely to rerun the script. If a client needs a corrected invoice, use a documented revision process and a unique identifier rather than silently reusing the original row.

Do not schedule a trigger too early

Time-driven triggers can send unattended email. Add one only after manual runs are stable, errors are visible, and the account owner understands quotas and recipient data. On a shared or managed Workspace account, obtain the required approval.

Completion checklist

• Fabricated data produces a correctly formatted PDF.
• Invalid rows are marked Needs review with a useful error.
• Duplicate invoice numbers are blocked.
• The first real send goes only to a controlled recipient.
• Status and sent timestamp are written only after sending.
• Temporary Drive copies are cleaned up.

Official Google references

• Apps Script Mail service
• Generate and send PDFs from Google Sheets sample