ClaudaiNewsPaperInvoice

Create a Newspaper Invoice System in Google Sheets

Hey there, Google Sheets enthusiasts! Want to streamline your newspaper invoicing process? This Google Apps Script solution creates a user-friendly invoice system right in your spreadsheet. It manages subscribers, generates invoices, and exports them as PDFs. Below, you’ll find two code blocks (Code.gs and InvoiceForm.html) that you can copy and use in your own Google Sheet, along with step-by-step instructions on how to set it up.

What This Invoice System Does

This system includes:

• A custom HTML form to input subscriber details and invoice items.
• A Subscribers sheet to store customer data (phone, name, address, email, etc.).
• An Invoice sheet that generates a formatted invoice.
• An export to PDF feature that saves invoices to Google Drive and emails them.
• A custom menu in Google Sheets to trigger invoice creation.

Step-by-Step Setup Instructions

1. Create a New Google Sheet:
   • Open Google Sheets and create a new spreadsheet (e.g., "Newspaper Invoice System").
   • Save it to your Google Drive.
2. Open the Apps Script Editor:
   • In your Google Sheet, go to Extensions > Apps Script.
3. Add the Google Apps Script Code:
   • In the Apps Script editor, clear any code in Code.gs.
   • Copy the code below (Code Block 1) and paste it into Code.gs.
   • Save the file (Ctrl + S) and name the project (e.g., "Invoice System").
4. Add the HTML Form:
   • In the Apps Script editor, go to File > New > HTML file.
   • Name it InvoiceForm (exact name required).
   • Copy the code below (Code Block 2) and paste it into InvoiceForm.html.
   • Save the file.
5. Set Up Permissions:
   • Click Run in the Apps Script editor to execute the onOpen function.
   • Authorize the script by reviewing and granting permissions (Sheets, Drive, Gmail).
   • A custom menu (Invoice System) will appear in your Google Sheet.
6. Set Up Sample Data (Optional):
   • Go to Invoice System > Setup Sample Data in the menu.
   • This creates a Subscribers sheet with sample data. Edit it to add your own subscribers.

How to Use the Invoice System

1. Open the Form:
   • Go to Invoice System > Create Invoice to open the HTML form.
2. Fill Out the Form:
   • Phone Number: Enter a 10-digit phone number from the Subscribers sheet.
   • Due Date: Select a date (defaults to 30 days from today).
   • Items: Add items (e.g., "Daily Newspaper") with description, quantity, and rate. Use + Add Item or Remove as needed.
   • Tax: Enter tax amount (optional, defaults to 0).
   • Click Create Invoice.
3. View the Invoice:
   • The script creates an Invoice sheet with formatted details (invoice number, date, items, totals in ₹).
4. Export to PDF:
   • The invoice is saved as a PDF in a NewspaperInvoices folder in Google Drive.
   • It’s emailed to your Google account’s email address.
   • A confirmation shows the PDF’s Drive link.
5. Manage Subscribers:
   • Edit the Subscribers sheet to add or update customer data (Phone, Name, Address, Email, Subscription Type).

Code Blocks to Copy

Below are the two code blocks. Click the Copy button for each to copy the code to your clipboard, then paste it into the appropriate file in your Apps Script editor.

Code Block 1: Code.gs (Google Apps Script)

// Google Apps Script for Newspaper Invoice System // Main function to show the HTML form function showInvoiceForm() { const html = HtmlService.createHtmlOutputFromFile('InvoiceForm') .setWidth(600) .setHeight(500) .setTitle('Create Invoice'); SpreadsheetApp.getUi().showModalDialog(html, 'Create New Invoice'); } // Function to get subscriber details by phone number function getSubscriberDetails(phoneNumber) { const ss = SpreadsheetApp.getActiveSpreadsheet(); // Create subscribers sheet if it doesn't exist const subscribersSheet = createSubscribersSheetIfNotExists(); const data = subscribersSheet.getDataRange().getValues(); const headers = data[0]; // Find the subscriber by phone number (assuming phone is in column A or B) for (let i = 1; i < data.length; i++) { if (data[i][0] == phoneNumber || data[i][1] == phoneNumber) { return { name: data[i][headers.indexOf('Name') !== -1 ? headers.indexOf('Name') : 1], phone: phoneNumber, address: data[i][headers.indexOf('Address') !== -1 ? headers.indexOf('Address') : 2] || '', email: data[i][headers.indexOf('Email') !== -1 ? headers.indexOf('Email') : 3] || '' }; } } return null; } // Function to create invoice function createInvoice(formData) { try { const ss = SpreadsheetApp.getActiveSpreadsheet(); let invoiceSheet = ss.getSheetByName('Invoice'); // Create Invoice sheet if it doesn't exist if (!invoiceSheet) { invoiceSheet = ss.insertSheet('Invoice'); setupInvoiceSheet(invoiceSheet); } // Get subscriber details const subscriber = getSubscriberDetails(formData.phoneNumber); if (!subscriber) { throw new Error('Subscriber not found with phone number: ' + formData.phoneNumber); } // Generate invoice number const invoiceNumber = 'INV-' + new Date().getTime().toString().slice(-8); // Clear previous invoice data (keep template) clearInvoiceData(invoiceSheet); // Fill invoice details fillInvoiceDetails(invoiceSheet, subscriber, formData, invoiceNumber); return { success: true, message: 'Invoice created successfully!', invoiceNumber: invoiceNumber }; } catch (error) { return { success: false, message: 'Error: ' + error.message }; } } // Function to setup invoice sheet template function setupInvoiceSheet(sheet) { // Set up the invoice template sheet.getRange('A1').setValue('NEWSPAPER INVOICE').setFontSize(18).setFontWeight('bold'); // Invoice details section sheet.getRange('A3').setValue('Invoice Number:'); sheet.getRange('A4').setValue('Date:'); sheet.getRange('A5').setValue('Due Date:'); // Customer details section sheet.getRange('A7').setValue('Bill To:').setFontWeight('bold'); sheet.getRange('A8').setValue('Name:'); sheet.getRange('A9').setValue('Phone:'); sheet.getRange('A10').setValue('Address:'); sheet.getRange('A11').setValue('Email:'); // Items header sheet.getRange('A13').setValue('Item').setFontWeight('bold'); sheet.getRange('B13').setValue('Description').setFontWeight('bold'); sheet.getRange('C13').setValue('Quantity').setFontWeight('bold'); sheet.getRange('D13').setValue('Rate').setFontWeight('bold'); sheet.getRange('E13').setValue('Amount').setFontWeight('bold'); // Format headers sheet.getRange('A13:E13').setBackground('#e0e0e0').setBorder(true, true, true, true, true, true); // Total section sheet.getRange('D25').setValue('Subtotal:').setFontWeight('bold'); sheet.getRange('D26').setValue('Tax (if applicable):').setFontWeight('bold'); sheet.getRange('D27').setValue('Total:').setFontWeight('bold').setBackground('#ffffcc'); // Set column widths sheet.setColumnWidth(1, 150); sheet.setColumnWidth(2, 200); sheet.setColumnWidth(3, 100); sheet.setColumnWidth(4, 100); sheet.setColumnWidth(5, 120); } // Function to clear previous invoice data function clearInvoiceData(sheet) { // Clear customer details sheet.getRange('B3:B11').clearContent(); // Clear items (rows 14-24) sheet.getRange('A14:E24').clearContent(); // Clear totals sheet.getRange('E25:E27').clearContent(); } // Function to fill invoice details function fillInvoiceDetails(sheet, subscriber, formData, invoiceNumber) { // Fill invoice details sheet.getRange('B3').setValue(invoiceNumber); sheet.getRange('B4').setValue(new Date().toLocaleDateString()); sheet.getRange('B5').setValue(formData.dueDate || new Date(Date.now() + 30*24*60*60*1000).toLocaleDateString()); // Fill customer details sheet.getRange('B8').setValue(subscriber.name); sheet.getRange('B9').setValue(subscriber.phone); sheet.getRange('B10').setValue(subscriber.address); sheet.getRange('B11').setValue(subscriber.email); // Fill items let subtotal = 0; const items = formData.items; for (let i = 0; i < items.length && i < 10; i++) { const row = 14 + i; const amount = items[i].quantity * items[i].rate; sheet.getRange('A' + row).setValue(items[i].item); sheet.getRange('B' + row).setValue(items[i].description || ''); sheet.getRange('C' + row).setValue(items[i].quantity); sheet.getRange('D' + row).setValue(items[i].rate); sheet.getRange('E' + row).setValue(amount).setNumberFormat('₹#,##0.00'); subtotal += amount; } // Fill totals const tax = formData.tax || 0; const total = subtotal + tax; sheet.getRange('E25').setValue(subtotal).setNumberFormat('₹#,##0.00'); sheet.getRange('E26').setValue(tax).setNumberFormat('₹#,##0.00'); sheet.getRange('E27').setValue(total).setNumberFormat('₹#,##0.00').setFontWeight('bold'); } // Function to create subscribers sheet if it doesn't exist function createSubscribersSheetIfNotExists() { const ss = SpreadsheetApp.getActiveSpreadsheet(); let subscribersSheet = ss.getSheetByName('Subscribers'); if (!subscribersSheet) { subscribersSheet = ss.insertSheet('Subscribers'); // Add headers const headers = ['Phone', 'Name', 'Address', 'Email', 'Subscription_Type']; subscribersSheet.getRange(1, 1, 1, headers.length).setValues([headers]); subscribersSheet.getRange(1, 1, 1, headers.length).setFontWeight('bold').setBackground('#e0e0e0'); // Add sample data (2 customers) const sampleData = [ ['9876543210', 'Rajesh Kumar', 'House No. 45, MG Road, Kanayannur, Kerala', 'rajesh@email.com', 'Daily + Weekly'], ['9876543211', 'Priya Nair', 'Flat 12B, Green Valley Apartments, Ernakulam, Kerala', 'priya@email.com', 'Daily + Monthly'] ]; subscribersSheet.getRange(2, 1, sampleData.length, sampleData[0].length).setValues(sampleData); // Auto-resize columns subscribersSheet.autoResizeColumns(1, headers.length); } return subscribersSheet; } // Function to setup sample data (run once) function setupSampleData() { createSubscribersSheetIfNotExists(); } // Function to add menu items function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('Invoice System') .addItem('Create Invoice', 'showInvoiceForm') .addItem('Setup Sample Data', 'setupSampleData') .addToUi(); } // Function to export invoice to PDF function exportInvoiceToPDF99(sheetName, pdfName) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName(sheetName || "Invoice"); if (!sheet) { SpreadsheetApp.getUi().alert('Error', `Sheet "${sheetName}" not found!`, SpreadsheetApp.getUi().ButtonSet.OK); return; } const ssID = ss.getId(); const sheetTabId = sheet.getSheetId(); const url_base = ss.getUrl().replace(/edit$/, ''); // Determine range dynamically, excluding URL column H for "Invoice" let lastRow, lastCol, range; if (sheetName === 'Invoice') { lastRow = sheet.getRange('G:G').getNextDataCell(SpreadsheetApp.Direction.UP).getRow(); lastCol = 7; range = `A1:G${lastRow}`; sheet.getRange(`H2:H${sheet.getLastRow()}`).clear(); } else if (sheetName === 'Invoice2') { range = 'A1:B8'; sheet.getRange('B9').clear(); } else { lastRow = sheet.getLastRow(); lastCol = sheet.getLastColumn(); range = `A1:${String.fromCharCode(65 + lastCol - 1)}${lastRow}`; } // Build export URL const exportUrl = `${url_base}export?exportFormat=pdf&format=pdf` + `&gid=${sheetTabId}&id=${ssID}` + `&range=${range}` + '&size=A4' + '&portrait=true' + '&fitw=true' + '&gridlines=false' + '&fzr=false'; const options = { headers: { 'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(), }, muteHttpExceptions: true }; // Ensure sheet is ready SpreadsheetApp.flush(); // Fetch PDF const response = UrlFetchApp.fetch(exportUrl, options); if (response.getResponseCode() !== 200) { Logger.log(`Error exporting ${sheetName} to PDF! Response Code: ${response.getResponseCode()}`); SpreadsheetApp.getUi().alert('Error', 'Failed to export PDF!', SpreadsheetApp.getUi().ButtonSet.OK); return; } // Save PDF to Drive const folderName = 'NewspaperInvoices'; let folder = DriveApp.getFoldersByName(folderName).hasNext() ? DriveApp.getFoldersByName(folderName).next() : DriveApp.createFolder(folderName); const blob = response.getBlob(); const file = folder.createFile(blob.setName(`${pdfName}.pdf`)); // Email PDF to active user const activeUserEmail = Session.getActiveUser().getEmail(); if (activeUserEmail) { const subject = `Your Invoice ${pdfName}`; const htmlBody = `Dear User,<br><br>Your invoice is attached.<br><br>Thank you!<br><br>Best regards,<br>Your Newspaper Agency`; MailApp.sendEmail({ to: activeUserEmail, subject: subject, htmlBody: htmlBody, attachments: [file] }); SpreadsheetApp.getUi().alert(`PDF exported to Drive and emailed to ${activeUserEmail}: ${file.getUrl()}`); } else { SpreadsheetApp.getUi().alert('PDF exported to Drive, but no email sent: User email not available.'); } }

Code Block 2: InvoiceForm.html (HTML Form)

<!DOCTYPE html> <html> <head> <style> body { font-family: Arial, sans-serif; padding: 20px; background-color: #f5f5f5; } .form-container { background: white; padding: 20px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.1); } h2 { color: #333; margin-bottom: 20px; text-align: center; } .form-group { margin-bottom: 15px; } label { display: block; margin-bottom: 5px; font-weight: bold; color: #555; } input[type="text"], input[type="date"], input[type="number"] { width: 100%; padding: 8px; border: 1px solid #ddd; border-radius: 4px; box-sizing: border-box; font-size: 14px; } .items-section { border: 1px solid #ddd; padding: 15px; border-radius: 5px; margin: 20px 0; background-color: #fafafa; } .item-row { display: grid; grid-template-columns: 2fr 2fr 1fr 1fr 80px; gap: 10px; margin-bottom: 10px; align-items: end; } .item-header { font-weight: bold; color: #333; margin-bottom: 10px; } button { background-color: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; font-size: 14px; } button:hover { background-color: #0056b3; } .btn-add { background-color: #28a745; padding: 5px 10px; font-size: 12px; } .btn-remove { background-color: #dc3545; padding: 5px 10px; font-size: 12px; } .btn-submit { width: 100%; margin-top: 20px; padding: 12px; font-size: 16px; } .message { padding: 10px; margin: 10px 0; border-radius: 4px; text-align: center; } .success { background-color: #d4edda; color: #155724; border: 1px solid #c3e6cb; } .error { background-color: #f8d7da; color: #721c24; border: 1px solid #f5c6cb; } </style> </head> <body> <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 class="item-row"> <div><strong>Item/Service</strong></div> <div><strong>Description</strong></div> <div><strong>Quantity</strong></div> <div><strong>Rate (₹)</strong></div> <div></div> </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)" style="display: none;">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> <script> let itemCount = 1; function addItem() { const container = document.getElementById('itemsContainer'); const newItem = document.createElement('div'); newItem.className = 'item-row'; newItem.setAttribute('data-index', itemCount); newItem.innerHTML = ` <input type="text" placeholder="e.g., Weekly Magazine" name="item_${itemCount}" required> <input type="text" placeholder="e.g., January 2025" name="description_${itemCount}"> <input type="number" placeholder="4" name="quantity_${itemCount}" min="1" required> <input type="number" placeholder="15.00" name="rate_${itemCount}" min="0" step="0.01" required> <button type="button" class="btn-remove" onclick="removeItem(${itemCount})">Remove</button> `; container.appendChild(newItem); itemCount++; // Show remove buttons when there's more than one item updateRemoveButtons(); } function removeItem(index) { const item = document.querySelector(`[data-index="${index}"]`); if (item) { item.remove(); updateRemoveButtons(); } } function updateRemoveButtons() { const items = document.querySelectorAll('.item-row[data-index]'); items.forEach((item, idx) => { const removeBtn = item.querySelector('.btn-remove'); if (removeBtn) { removeBtn.style.display = items.length > 1 ? 'block' : 'none'; } }); } function showMessage(message, type) { const messageDiv = document.getElementById('message'); messageDiv.innerHTML = `<div class="${type}">${message}</div>`; setTimeout(() => { messageDiv.innerHTML = ''; }, 5000); } 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: [] }; // Collect all 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); } }); if (data.items.length === 0) { showMessage('Please add at least one item to the invoice.', 'error'); return; } // Validate phone number (basic validation) if (!/^\d{10}$/.test(data.phoneNumber)) { showMessage('Please enter a valid 10-digit phone number.', 'error'); return; } // Show loading message showMessage('Creating invoice...', 'success'); // Call Google Apps Script function google.script.run .withSuccessHandler(function(result) { if (result.success) { showMessage(`Invoice ${result.invoiceNumber} created successfully!`, 'success'); document.getElementById('invoiceForm').reset(); // Reset items to just one const container = document.getElementById('itemsContainer'); const items = container.querySelectorAll('.item-row[data-index]'); for (let i = 1; i < items.length; i++) { items[i].remove(); } itemCount = 1; updateRemoveButtons(); } else { showMessage(result.message, 'error'); } }) .withFailureHandler(function(error) { showMessage('Error creating invoice: ' + error.message, 'error'); }) .createInvoice(data); }); // Set default due date to 30 days from today document.addEventListener('DOMContentLoaded', function() { const today = new Date(); const futureDate = new Date(today.getTime() + (30 * 24 * 60 * 60 * 1000)); const formattedDate = futureDate.toISOString().split('T')[0]; document.getElementById('dueDate').value = formattedDate; }); </script> </body> </html>

Tips and Troubleshooting

• Validation: The form ensures a 10-digit phone number and at least one item.
• PDF Export: PDFs are saved in A4 format without gridlines and emailed to your Google account.
• Errors:
  • "Sheet not found": Let the script create the Invoice sheet or ensure it exists.
  • "Subscriber not found": Check that the phone number is in the Subscribers sheet.
  • PDF not emailed: Verify Gmail permissions and your account’s email.
• Customize: Change currency in fillInvoiceDetails (e.g., '$#,##0.00') or edit CSS in InvoiceForm.html for styling.

Why This System is Awesome

This invoice system is free, automated, and customizable. It’s perfect for small newspaper agencies or anyone managing subscriptions. Copy the code, follow the steps, and start creating professional invoices today!

Have questions or need help? Leave a comment below, and I’ll get back to you. Happy invoicing!