Building a Newspaper Invoice System with Google Apps Script

Streamline your newspaper subscription billing with this Google Apps Script-based invoice system. This solution integrates with Google Sheets to manage subscriber data, generate invoices, and export them as PDFs. Below, we explore the system’s key components and functionality.

Overview

This system automates invoice creation for a newspaper subscription service. It features a user-friendly HTML form, subscriber data management, and PDF generation with email delivery. Built using Google Apps Script, it leverages Google Sheets for data storage and Google Drive for PDF storage.

Key Features

• Subscriber Lookup: Retrieve subscriber details using a phone number.
• Dynamic Invoice Creation: Generate invoices with customizable items, quantities, rates, and tax.
• PDF Export: Convert invoices to PDFs and store them in Google Drive.
• Email Delivery: Automatically email invoices to the active user.
• Customizable UI: A clean, responsive HTML form for inputting invoice details.
• Menu Integration: Adds a custom menu to Google Sheets for easy access.

Code Breakdown

1. Main Functions (Google Apps Script)

The backend logic is handled by Google Apps Script, which interacts with Google Sheets to store and retrieve data.

Show Invoice Form

Displays a modal dialog with the HTML form for creating invoices.

function showInvoiceForm() {
  const html = HtmlService.createHtmlOutputFromFile('InvoiceForm')
    .setWidth(600)
    .setHeight(500)
    .setTitle('Create Invoice');
  SpreadsheetApp.getUi().showModalDialog(html, 'Create New Invoice');
}
        

Get Subscriber Details

Retrieves subscriber information from the "Subscribers" sheet based on the phone number.

function getSubscriberDetails(phoneNumber) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const subscribersSheet = createSubscribersSheetIfNotExists();
  const data = subscribersSheet.getDataRange().getValues();
  const headers = data[0];
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] == phoneNumber || data[i][1] == phoneNumber) {
      return {
        name: data[i][headers.indexOf('Name')],
        phone: phoneNumber,
        address: data[i][headers.indexOf('Address')] || '',
        email: data[i][headers.indexOf('Email')] || ''
      };
    }
  }
  return null;
}
        

Create Invoice

Generates an invoice in the "Invoice" sheet, populating it with subscriber details, items, and totals.

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
    };
  }
}
        

Export Invoice to PDF

Exports the invoice as a PDF to Google Drive and emails it to the active user.

function exportInvoiceToPDF99(sheetName, pdfName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName || "Invoice");
  if (!sheet) return;
  const ssID = ss.getId();
  const sheetTabId = sheet.getSheetId();
  const url_base = ss.getUrl().replace(/edit$/, '');
  const exportUrl = `${url_base}export?exportFormat=pdf&format=pdf&gid=${sheetTabId}&id=${ssID}&range=A1:G${sheet.getLastRow()}&size=A4&portrait=true&fitw=true&gridlines=false`;
  const response = UrlFetchApp.fetch(exportUrl, {
    headers: { 'Authorization': 'Bearer ' + ScriptApp.getOAuthToken() }
  });
  const folder = DriveApp.getFoldersByName('NewspaperInvoices').hasNext()
    ? DriveApp.getFoldersByName('NewspaperInvoices').next()
    : DriveApp.createFolder('NewspaperInvoices');
  const file = folder.createFile(response.getBlob().setName(`${pdfName}.pdf`));
  const activeUserEmail = Session.getActiveUser().getEmail();
  if (activeUserEmail) {
    MailApp.sendEmail({
      to: activeUserEmail,
      subject: `Your Invoice ${pdfName}`,
      htmlBody: `Dear User,

Your invoice is attached.

Thank you!`,
      attachments: [file]
    });
  }
}
        

2. HTML Form (Frontend)

The HTML form provides a user-friendly interface for inputting invoice details, styled with CSS for responsiveness.

<div class="form-container">
    <h2>Create Invoice</h2>
    <form id="invoiceForm">
        <div class="form-group">
            <label for="phoneNumber">Subscriber Phone Number:</label>
            <input type="text" id="phoneNumber" name="phoneNumber" required placeholder="Enter 10-digit phone number">
        </div>
        <div class="form-group">
            <label for="dueDate">Due Date:</label>
            <input type="date" id="dueDate" name="dueDate">
        </div>
        <div class="items-section">
            <div class="item-header">Invoice Items</div>
            <div id="itemsContainer">
                <div class="item-row" data-index="0">
                    <input type="text" placeholder="e.g., Daily Newspaper" name="item_0" required>
                    <input type="text" placeholder="e.g., January 2025" name="description_0">
                    <input type="number" placeholder="30" name="quantity_0" min="1" required>
                    <input type="number" placeholder="5.00" name="rate_0" min="0" step="0.01" required>
                    <button type="button" class="btn-remove" onclick="removeItem(0)">Remove</button>
                </div>
            </div>
            <button type="button" class="btn-add" onclick="addItem()">+ Add Item</button>
        </div>
        <div class="form-group">
            <label for="tax">Tax Amount (₹):</label>
            <input type="number" id="tax" name="tax" min="0" step="0.01" placeholder="0.00">
        </div>
        <button type="submit" class="btn-submit">Create Invoice</button>
    </form>
    <div id="message"></div>
</div>
        

3. JavaScript (Client-Side)

Handles dynamic form interactions, such as adding/removing items and form submission.

document.getElementById('invoiceForm').addEventListener('submit', function(e) {
    e.preventDefault();
    const formData = new FormData(this);
    const data = {
        phoneNumber: formData.get('phoneNumber'),
        dueDate: formData.get('dueDate'),
        tax: parseFloat(formData.get('tax')) || 0,
        items: []
    };
    const items = document.querySelectorAll('.item-row[data-index]');
    items.forEach((item, index) => {
        const itemData = {
            item: item.querySelector(`input[name^="item_"]`).value,
            description: item.querySelector(`input[name^="description_"]`).value,
            quantity: parseInt(item.querySelector(`input[name^="quantity_"]`).value),
            rate: parseFloat(item.querySelector(`input[name^="rate_"]`).value)
        };
        if (itemData.item && itemData.quantity && itemData.rate) {
            data.items.push(itemData);
        }
    });
    google.script.run
        .withSuccessHandler(function(result) {
            if (result.success) {
                showMessage(`Invoice ${result.invoiceNumber} created successfully!`, 'success');
                document.getElementById('invoiceForm').reset();
            } else {
                showMessage(result.message, 'error');
            }
        })
        .createInvoice(data);
});
        

Setup Instructions

1. Create a new Google Sheet.
2. Open the Apps Script editor (Extensions > Apps Script).
3. Paste the Google Apps Script code into a new script file.
4. Create a new HTML file named InvoiceForm and paste the HTML/CSS/JavaScript code.
5. Save and deploy the script, granting necessary permissions.
6. Run the onOpen function to add the custom menu.
7. Use the "Invoice System" menu to create invoices or set up sample data.

Usage

1. Select "Create Invoice" from the "Invoice System" menu in Google Sheets.
2. Enter the subscriber’s phone number, due date, and invoice items in the form.
3. Submit the form to generate the invoice in the "Invoice" sheet.
4. Use the exportInvoiceToPDF99 function to save and email the invoice as a PDF.

Conclusion

This Google Apps Script solution provides a robust, automated system for managing newspaper invoices. Its integration with Google Sheets and Drive makes it ideal for small businesses or agencies looking to streamline billing processes. Customize the form and sheet templates to suit your specific needs, and explore additional features like automated reminders or multi-sheet invoice formats.

For more details on Google Apps Script, visit the official documentation.