Streamline Your Newspaper Billing with Google Apps Script

Managing subscriptions and invoices for a newspaper business can be a hassle, especially with multiple subscribers and varying billing cycles. With Google Apps Script and Google Sheets, you can automate this process, saving time and reducing errors. In this post, we’ll explore a custom Newspaper Invoice System that simplifies invoice creation, subscriber management, and PDF generation—all within Google Sheets.

This solution is ideal for small to medium-sized newspaper agencies looking to streamline billing without expensive software. Let’s dive in!

What This System Does

The Newspaper Invoice System uses Google Apps Script to create a seamless invoicing workflow in Google Sheets. Key functionalities include:

• Interactive Form: A user-friendly HTML form for entering subscriber details and invoice items.
• Subscriber Lookup: Automatically retrieves customer details by phone number from a subscriber database.
• Invoice Generation: Creates professional invoices with automatic calculations for subtotals, taxes, and totals.
• PDF Export: Exports invoices as PDFs and saves them to Google Drive.
• Email Delivery: Sends invoices directly to users or subscribers via email.
• Subscriber Management: Maintains a dedicated sheet for customer data.

Key Features

1. Interactive HTML Form

The system features a responsive HTML form styled with CSS, allowing users to:

• Enter a subscriber’s phone number to auto-populate their details.
• Add multiple invoice items (e.g., daily newspapers, weekly magazines) with dynamic rows.
• Specify due dates and tax amounts.
• Validate inputs (e.g., 10-digit phone numbers) before submission.

2. Subscriber Database

A Subscribers sheet stores customer details like phone numbers, names, addresses, emails, and subscription types. If the sheet doesn’t exist, the script creates it with sample data for quick setup.

3. Automated Invoice Creation

Invoices are generated in a formatted Invoice sheet with:

• Unique invoice numbers (e.g., INV-12345678).
• Professional layout with headers, customer details, and itemized lists.
• Automatic calculations for subtotal, tax, and total amounts in INR (₹).

4. PDF Export and Email

The system exports invoices as PDFs using Google Sheets’ export API, saves them to a NewspaperInvoices folder in Google Drive, and emails them to the active user. This ensures easy sharing and archiving.

How It Works

The system comprises two main components:

1. Google Apps Script Backend:
   • showInvoiceForm(): Displays the HTML form in a modal dialog.
   • getSubscriberDetails(phoneNumber): Retrieves subscriber data by phone number.
   • createInvoice(formData): Generates the invoice with calculated totals.
   • exportInvoiceToPDF99(sheetName, pdfName): Exports the invoice as a PDF and emails it.
   • setupInvoiceSheet(sheet): Sets up a professional invoice template.
   • createSubscribersSheetIfNotExists(): Initializes the subscriber database.
2. HTML/CSS Frontend (InvoiceForm.html):
   • A clean, user-friendly form with dynamic item rows.
   • JavaScript for adding/removing items, form validation, and communication with the backend via google.script.run.
   • Responsive design with success/error messages for user feedback.

Implementation Steps

1. Create a Google Sheet: Open a new Google Sheet for your invoice system.
2. Add the Script: Go to Extensions > Apps Script, and paste the provided Google Apps Script code.
3. Add the HTML Form: Create a new file in the Apps Script editor named InvoiceForm.html and paste the provided HTML/CSS/JavaScript code.
4. Authorize and Run: Save the script, authorize permissions, and run onOpen() to add a custom menu to your Sheet.
5. Use the System: From the Sheet, select Invoice System > Create Invoice to open the form, enter details, and generate invoices.

Sample Code Snippet

Here’s a glimpse of the core invoice creation function:

function createInvoice(formData) {
    try {
        const ss = SpreadsheetApp.getActiveSpreadsheet();
        let invoiceSheet = ss.getSheetByName('Invoice');
        if (!invoiceSheet) {
            invoiceSheet = ss.insertSheet('Invoice');
            setupInvoiceSheet(invoiceSheet);
        }
        const subscriber = getSubscriberDetails(formData.phoneNumber);
        if (!subscriber) {
            throw new Error('Subscriber not found with phone number: ' + formData.phoneNumber);
        }
        const invoiceNumber = 'INV-' + new Date().getTime().toString().slice(-8);
        clearInvoiceData(invoiceSheet);
        fillInvoiceDetails(invoiceSheet, subscriber, formData, invoiceNumber);
        return { success: true, message: 'Invoice created successfully!', invoiceNumber: invoiceNumber };
    } catch (error) {
        return { success: false, message: 'Error: ' + error.message };
    }
}
        

Why Use This System?

This Newspaper Invoice System is:

• Cost-Effective: Built entirely within Google Sheets, requiring no paid software.
• Customizable: Easily modify the script or HTML to suit your business needs.
• Efficient: Automates repetitive tasks like invoice formatting and PDF creation.
• User-Friendly: Designed for non-technical users with a simple interface.

Get Started Today

Ready to simplify your newspaper billing? Copy the provided code into your Google Sheets, customize it as needed, and start generating professional invoices in minutes. For the full code and detailed setup instructions, check out the resources below or reach out for support.

Explore More Automation with xAI

Published on August 8, 2025 | Powered by xAI