Google Apps Script Invoice Automation

Generate branded PDF invoices from a Google Sheets spreadsheet and email them to clients automatically using Google Apps Script.

This project adds a custom "Invoicing" menu to your Sheet. It reads each row, renders a luxury-themed HTML invoice, converts it to PDF, emails the client, and marks the row as Done.

Features

• Custom Sheets menu: Invoicing → Generate & Send Invoices
• HTML template → high-quality PDF invoice (Drive conversion)
• Automated email with PDF attachment via Gmail
• Per-row status tracking and error logging in the sheet
• Easy branding: edit invoice.html for look & feel

Tech Stack

• Google Apps Script (SpreadsheetApp, HtmlService, DriveApp, MailApp)
• HTML/CSS (invoice template)

Repository Structure

• Code.gs — Google Apps Script backend logic (menu, PDF generation, email)
• invoice.html — HTML invoice template used to render PDFs
• sample Data.xlsx — Example dataset layout for quick testing (optional)

Spreadsheet Requirements

Create a Google Sheet with a header row that includes the following columns (case-sensitive):

• Invoice_No
• Client_Name
• Email
• Service
• Amount (numeric)
• Due_Date (date)
• Address
• Status (left blank; script updates to Done or writes an error)

Rows with an empty Status are processed; others are skipped.

Setup (10 minutes)

1. Create a Drive folder for PDFs

• In Google Drive, create a folder (e.g., Invoices)
• Copy its Folder ID from the URL

2. Prepare the Sheet

• Create a new Google Sheet
• Add the headers listed above in row 1
• Add a few test rows

3. Add Apps Script files

• In the Sheet, open Extensions → Apps Script
• Create a script file named Code.gs and paste the contents from this repo’s Code.gs
• Create an HTML file named invoice.html and paste the contents from this repo’s invoice.html

4. Configure the script

• In Code.gs, set:
  • PDF_FOLDER_ID to your Drive folder ID
  • SHEET_NAME to your sheet tab name (default Sheet1)

5. Authorize

• Click Run ▶ to execute a simple function (e.g., onOpen) and grant permissions for Drive, Gmail, and Sheets

How to Use

• Open your Sheet → you’ll see a new menu: Invoicing → Generate & Send Invoices
• The script will:
  • Read rows where Status is blank
  • Render invoice.html with row data
  • Create a PDF in your Drive folder
  • Send an email with the PDF attached to Email
  • Update Status to Done (or Error: … on failure)

Tip: Start with one or two test rows and your own email before sending to customers.

Screenshots

• Spreadsheet headers and sample rows:

  

• Apps Script editor with backend code:

  

• Invoice HTML template (branding/customization):

  

• Drive folder with generated PDFs:

  

Sample Output (PDF)

• View or download a generated sample invoice:

  Invoice-inv-017.pdf

Tip: On GitHub, click the link above, then use “Download” or “View raw” to preview the PDF.

Configuration & Customization

• Branding: Edit colors, logo, fonts inside invoice.html
• Currency: Replace the currency symbol (₹) in invoice.html with your own
• Email body/subject: Tweak sendEmailWithAttachment in Code.gs
• Sheet name: Update SHEET_NAME
• Folder: Update PDF_FOLDER_ID

Triggers (optional automation)

You can schedule the job (e.g., daily) via Apps Script Triggers:

• Apps Script editor → Triggers → Add Trigger
• Choose createAndSendInvoices
• Event source: Time-driven, then set frequency

Visual steps:

• Trigger overview after creation:

  

• Choose function to run:

  

• Select event source:

  

• Pick automation time/frequency:

  

Quotas & Limits

Google quotas apply (Gmail send limits, Apps Script runtime, Drive operations). For most small businesses, this workflow stays within limits, but batch sizes should be moderate. If you hit limits, break the work into smaller chunks or run on a schedule.

Troubleshooting

• Status column not found: Ensure Status exists in row 1
• Dates look wrong: Confirm Due_Date cells are valid dates; locale formatting is applied via toLocaleDateString()
• Attachments missing: Verify PDF_FOLDER_ID is valid and Drive permissions are granted
• Amount formatting: invoice.html calls amount.toFixed(2); ensure Amount is numeric in the Sheet
• Authorization prompts: First run requires allowing Gmail/Drive/Sheets scopes

Security Notes

• The script uses Gmail, Drive, and Sheets APIs in your account context
• No third-party services are called
• Be careful committing real client data to GitHub; use test data only

Roadmap Ideas

• Multiple line items and taxes/discounts
• Currency/locale formatting helpers
• Retry and partial failure reporting
• Time-driven schedules and batching
• Unit tests with clasp + TypeScript (optional)

Want more detail?

• See docs/ARCHITECTURE.md for data flow internals
• See docs/USAGE_GUIDE.md for step-by-step screenshots and tips
• See docs/RESUME_BLURB.md for resume-ready bullets

License

Choose a license before open-sourcing (e.g., MIT, Apache-2.0). Add a LICENSE file to the repo.