How to Automate PDF Invoice & Receipt Generation from Google Sheets (Free Template & Apps Script)

Published on | ProSheetHub

Generating individual client invoices, receipts, or payment vouchers manually is one of the most tedious administrative tasks for freelancers, small business owners, and agency managers. Copying transaction numbers, client names, and line-item totals into word processors or static templates takes hours and invites human error.

While dedicated invoicing software exists, most options come with costly recurring subscription fees. In this comprehensive guide, we will build a Zero-Cost Automated PDF Invoice & Receipt Generator using Google Sheets, Google Docs as a dynamic visual template, and Google Apps Script.

💡 What You Will Build: A single click in your Google Sheet reads client billing rows, clones a custom Google Docs invoice design, replaces dynamic placeholders (like {{Client_Name}} and {{Total_Due}}), converts the file to a clean PDF in Google Drive, and posts the direct download link right back to your spreadsheet row.

System Architecture & Workflow

This automated generator relies on three interconnected Google Workspace components to handle data, formatting, and file export:

Layer Tool Primary Role
Data Source Google Sheets Stores invoice line items, dates, rates, tax calculations, and status flags.
Visual Template Google Docs Serves as the visual layout containing your logo, terms, and variable tags (e.g., {{Invoice_No}}).
Automation Engine Google Apps Script Clones the template Doc, substitutes data tags, exports as PDF, and logs the PDF URL.

Step 1: Set Up Your Invoicing Spreadsheet

Create a new spreadsheet in Google Sheets titled Client Billing Hub.

Rename your primary tab to Invoices and configure the header row in Range A1:I1 as follows:

  • Column A: Invoice No
  • Column B: Issue Date
  • Column C: Due Date (Formula)
  • Column D: Client Name
  • Column E: Client Email
  • Column F: Service Description
  • Column G: Total Amount (₹ / $)
  • Column H: PDF Status
  • Column I: PDF Download Link
Pro Tip: To calculate dynamic net payment deadlines that skip weekends and official company holidays, pair Column C with the =WORKDAY(B2, 15, Holidays!$A$2:$A$20) formula as covered in our Automated SLA Tracker Guide.

Step 2: Create the Invoice Layout in Google Docs

Instead of hardcoding complex HTML/CSS layouts inside code, Google Docs offers a visual drag-and-drop canvas for professional invoices.

  1. Open Google Docs and create a blank document titled Invoice_Template_Master.
  2. Add your business logo, address, payment terms, and bank details.
  3. Insert double-curly bracket placeholders wherever dynamic row data should be injected:
    • {{Invoice_No}}
    • {{Issue_Date}}
    • {{Due_Date}}
    • {{Client_Name}}
    • {{Description}}
    • {{Total_Amount}}
  4. Note the unique Document ID from your browser's address bar. For example, in https://docs.google.com/document/d/1a2B3c4D5e.../edit, the ID is 1a2B3c4D5e....

Step 3: Create the Destination Drive Folder

Go to Google Drive and create a dedicated folder named Generated_Client_Invoices.

Open this folder and copy its unique Folder ID from the URL (the string of characters following /folders/ in your browser address bar). The automation engine will store all final PDF exports here.

Step 4: Add the Apps Script Automation Engine

In your Google Sheet, navigate to Extensions > Apps Script. Replace the existing default text with the following optimized batch generation script:

/**
 * Automated Google Sheets to PDF Invoice Generator
 * ProSheetHub Automation Blueprint
 */

const CONFIG = {
  TEMPLATE_DOC_ID: 'PASTE_YOUR_GOOGLE_DOC_TEMPLATE_ID_HERE',
  OUTPUT_FOLDER_ID: 'PASTE_YOUR_DRIVE_FOLDER_ID_HERE',
  SHEET_NAME: 'Invoices'
};

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('⚡ Invoicing Engine')
    .addItem('Generate Pending Invoices', 'generateBatchInvoices')
    .addToUi();
}

function generateBatchInvoices() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
  const data = sheet.getDataRange().getValues();
  
  const templateFile = DriveApp.getFileById(CONFIG.TEMPLATE_DOC_ID);
  const destinationFolder = DriveApp.getFolderById(CONFIG.OUTPUT_FOLDER_ID);

  let generatedCount = 0;

  // Loop through rows skipping headers (row index 0)
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const invoiceNo   = row[0];
    const issueDate   = row[1] instanceof Date ? Utilities.formatDate(row[1], Session.getScriptTimeZone(), 'yyyy-MM-dd') : row[1];
    const dueDate     = row[2] instanceof Date ? Utilities.formatDate(row[2], Session.getScriptTimeZone(), 'yyyy-MM-dd') : row[2];
    const clientName  = row[3];
    const description = row[5];
    const totalAmount = row[6];
    const pdfStatus   = row[7];

    // Only process rows where status is not already 'Generated'
    if (invoiceNo && pdfStatus !== 'Generated') {
      const fileName = `Invoice_${invoiceNo}_${clientName}`;
      
      // Step A: Clone the Master Doc Template
      const tempDocCopy = templateFile.makeCopy(fileName, destinationFolder);
      const tempDoc = DocumentApp.openById(tempDocCopy.getId());
      const body = tempDoc.getBody();

      // Step B: Replace all {{Tag}} placeholders
      body.replaceText('{{Invoice_No}}', invoiceNo);
      body.replaceText('{{Issue_Date}}', issueDate);
      body.replaceText('{{Due_Date}}', dueDate);
      body.replaceText('{{Client_Name}}', clientName);
      body.replaceText('{{Description}}', description);
      body.replaceText('{{Total_Amount}}', totalAmount.toString());

      // Save and close Document to flush all buffer writes
      tempDoc.saveAndClose();

      // Step C: Convert the populated Doc to clean PDF
      const pdfBlob = tempDocCopy.getAs(MimeType.PDF).setName(`${fileName}.pdf`);
      const finalPdf = destinationFolder.createFile(pdfBlob);
      finalPdf.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

      // Step D: Delete intermediate Google Doc to save Drive space
      tempDocCopy.setTrashed(true);

      // Step E: Update row status and append direct download URL
      sheet.getRange(i + 1, 8).setValue('Generated');
      sheet.getRange(i + 1, 9).setValue(finalPdf.getUrl());

      generatedCount++;
    }
  }

  SpreadsheetApp.getUi().alert(`Success: ${generatedCount} invoice(s) generated cleanly!`);
}

Step 5: Testing and Running the Automation

  1. In the code editor, replace PASTE_YOUR_GOOGLE_DOC_TEMPLATE_ID_HERE and PASTE_YOUR_DRIVE_FOLDER_ID_HERE with your IDs.
  2. Click Save (Disk icon), then refresh your Google Sheets tab.
  3. A new custom menu titled ⚡ Invoicing Engine will appear in the top bar.
  4. Click ⚡ Invoicing Engine > Generate Pending Invoices.
  5. Review Google's one-time authorization dialogue to grant Drive and Docs read/write permissions.
  6. Within seconds, your pending invoice rows will switch to Generated, complete with direct clickable PDF links.

Common Errors & Troubleshooting

Issue Probable Cause Solution
Exception: Document is missing (Cannot find id) Incorrect Template Doc ID or inadequate permissions. Verify the ID between /d/ and /edit in your template Doc URL. Ensure you have Edit access.
Unreplaced {{Tags}} in PDF Spelling or case mismatch in curly bracket tags. Placeholders are case-sensitive. Check that {{Client_Name}} in Docs matches your script exactly.
Exceeded Maximum Execution Time Processing over 50–70 invoices in a single run. Run generation in smaller batches or implement execution checkpoints using PropertiesService.

Next Steps & Advanced Enhancements

Now that your automated invoicing pipeline is operational, you can extend it further:

  • Automatic Email Delivery: Use GmailApp.sendEmail() to attach the generated PDF and send it directly to the client's email in Column E.

Comments

Post a Comment