🟢3.Modified News Agency Invoise blog post.Added3

Create a Subscription and Invoice Management System for Newspapers in Google Sheets

Hello, Google Sheets enthusiasts! Are you running a small newspaper agency and looking to automate subscriber management, invoice generation, and payment tracking? This Google Apps Script solution creates a powerful subscription and invoice management system right in your Google Sheet. It includes features like adding subscribers, generating invoices with a dropdown interface, tracking payments, exporting PDFs, creating summary reports, and sending invoices via WhatsApp. Below, you’ll find the main script, an HTML form, a WhatsApp integration script, and an invoice generation script, each with copy buttons for easy setup.

What This System Does

This system offers a robust set of tools for managing newspaper subscriptions:

• A custom HTML form to add or update subscriber details.
• A Subscribers sheet to store customer data (contact number, name, address, subscription type, last payment date).
• An Invoices sheet to log all invoices with details like Invoice ID, contact number, items, amount, date, status, and PDF URL.
• An Items sheet to store predefined items and their prices (used in invoice generation).
• A Payments sheet to record payment details (contact, subscription type, date, amount, notes).
• A dropdown-based invoice creation feature to select items and quantities.
• An export to PDF feature that saves invoices to Google Drive and emails them.
• A batch PDF generation feature for all pending invoices.
• A summary report feature to calculate total invoiced and paid amounts.
• A backup feature to save the spreadsheet to Google Drive.
• A WhatsApp integration to send formatted invoice messages with a clickable link.
• A custom menu in Google Sheets to access all features.

Step-by-Step Setup Instructions

1. Create a New Google Sheet:
   • Open Google Sheets and create a new spreadsheet (e.g., "Newspaper Subscription 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 Main Google Apps Script Code:
   • In the Apps Script editor, clear any code in Code.gs.
   • Copy the code from Code Block 1 and paste it into Code.gs.
   • Save the file (Ctrl + S) and name the project (e.g., "Subscription System").
4. Add the HTML Form:
   • In the Apps Script editor, go to File > New > HTML file.
   • Name it SubscriberForm (exact name required).
   • Copy the code from Code Block 2 and paste it into SubscriberForm.html.
   • Save the file.
5. Add the WhatsApp Integration Code:
   • In the Apps Script editor, go to File > New > Script file.
   • Name it WhatsApp.gs (exact name recommended).
   • Copy the code from Code Block 3 and paste it into WhatsApp.gs.
   • Save the file.
6. Add the Invoice Generation Code:
   • In the Apps Script editor, go to File > New > Script file.
   • Name it InvoiceGeneration.gs (exact name recommended).
   • Copy the code from Code Block 4 and paste it into InvoiceGeneration.gs.
   • Save the file.
   • Note: The invoice generation code relies on functions in Code.gs (initializeSheets, validateContact, getSubscriberByContact, generateInvoicePDFDirect, and populateInvoiceSheet). Ensure Code.gs is set up first.
7. Set Up the Items Sheet:
   • In your Google Sheet, create an Items sheet or let the script create it automatically.
   • Add item names in column C (C4:C6) and their prices in column D (D4:D6). For example:
     • C4: Daily Newspaper, D4: 5.00
     • C5: Weekly Magazine, D5: 20.00
     • C6: Monthly Subscription, D6: 100.00
8. Configure the Backup Folder:
   • In the backupSheet function in Code.gs, replace YOUR_FOLDER_ID with the ID of a Google Drive folder where backups will be stored. To find the folder ID, open the folder in Google Drive, and copy the ID from the URL (e.g., https://drive.google.com/drive/folders/[FOLDER_ID]).
9. 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, Document).
   • A custom menu (Subscription Management) will appear in your Google Sheet.
10. Set Up Sample Data (Optional):
    • Manually add sample subscribers to the Subscribers sheet (e.g., contact number, name, address, subscription type).
    • Add sample items to the Items sheet as described above.

How to Use the Subscription System

1. Add or Update Subscribers:
   • Via Form: Go to Subscription Management > Add/Update Subscriber (Form) to open the HTML form. Enter a 10-digit contact number, name, address, and subscription type. If the contact exists, the form auto-fills the name and address.
   • Via Prompt: Select Add/Update Subscriber (Prompt) and enter details through dialog boxes.
2. Create an Invoice:
   • Go to Subscription Management > Create Invoice (Dropdown).
   • Enter a 10-digit contact number. If the subscriber exists, their name auto-fills; otherwise, enter a name.
   • Select items and quantities from the dropdown (populated from the Items sheet, C4:C6).
   • Choose an invoice date (defaults to today).
   • The invoice is logged in the Invoices sheet, a PDF is generated, and it’s emailed to your Google account.
   • An Invoice sheet is created/updated with the invoice details for WhatsApp integration.
3. Send Invoice via WhatsApp:
   • After creating an invoice, go to the Invoice sheet.
   • Run the WhatsAppWordsWithVariedTotalRowFixed function from the Apps Script editor or add it to the custom menu (see Tips and Troubleshooting).
   • A WhatsApp hyperlink is generated in cell D1 of the Invoice sheet. Click it to open WhatsApp with a pre-formatted invoice message.
4. Check Subscriptions:
   • Select Check Subscriptions, enter a contact number, and view the subscriber’s details (name, subscription types, last payment dates).
5. Record a Payment:
   • Go to Record Payment, enter the contact number, subscription type, amount, date, and optional notes.
   • The payment is logged in the Payments sheet, and the Subscribers sheet updates the last payment date.
6. Export Invoice PDF:
   • Select Export Invoice PDF, enter an Invoice ID (e.g., INV001), and regenerate the PDF for that invoice.
   • The PDF is saved to the AppleInvoiceFolder in Google Drive and emailed to your account.
7. Generate All Pending Invoice PDFs:
   • Choose Generate All Pending Invoice PDFs to create PDFs for all invoices marked as "Pending" in the Invoices sheet.
   • PDFs are saved to Google Drive and emailed.
8. Create a Summary Report:
   • Select Create Summary Report to generate a Summary sheet with total invoiced and paid amounts.
9. Backup the Sheet:
   • Choose Backup Sheet to save a copy of the spreadsheet to the specified Google Drive folder.

Code Blocks to Copy

Below are the four 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 (Main Script)

// Main menu setup function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu('Subscription Management') .addItem('Add/Update Subscriber (Form)', 'showSubscriberForm') .addItem('Add/Update Subscriber (Prompt)', 'addOrUpdateSubscriber') .addItem('Create Invoice (Dropdown)', 'generateInvoicetest') .addItem('Check Subscriptions', 'checkSubscriptions') .addItem('Record Payment', 'recordPayment') .addItem('Export Invoice PDF', 'exportInvoicePDF') .addItem('Generate All Pending Invoice PDFs', 'generateAllPendingInvoicePDFs') .addItem('Create Summary Report', 'createSummaryReport') .addItem('Backup Sheet', 'backupSheet') .addToUi(); } // Initialize required sheets with headers function initializeSheets() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const requiredSheets = [ { name: 'Subscribers', headers: ['Contact Number', 'Name', 'Address', 'Subscription Type', 'Last Payment Date'] }, { name: 'Payments', headers: ['Contact Number', 'Subscription Type', 'Payment Date', 'Amount Paid', 'Notes'] }, { name: 'Invoices', headers: ['Invoice ID', 'Contact Number', 'Name', 'Items', 'Amount', 'Date', 'Status', 'PDF URL'] }, { name: 'Items', headers: ['Item', 'Price'] } ]; requiredSheets.forEach(sheetInfo => { let sheet = ss.getSheetByName(sheetInfo.name); if (!sheet) { sheet = ss.insertSheet(sheetInfo.name); sheet.getRange(1, 1, 1, sheetInfo.headers.length).setValues([sheetInfo.headers]).setFontWeight('bold'); } }); } // Subscriber form display function showSubscriberForm() { initializeSheets(); const html = HtmlService.createHtmlOutputFromFile('SubscriberForm') .setWidth(400) .setHeight(450); SpreadsheetApp.getUi().showModalDialog(html, 'Add/Update Subscriber'); } // Contact validation function validateContact(contact) { return /^\d{10}$/.test(contact); } // Get subscriber by contact number function getSubscriberByContact(contactNumber) { initializeSheets(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const subscribersSheet = ss.getSheetByName("Subscribers"); const data = subscribersSheet.getDataRange().getValues(); const subscriptions = []; for (let i = 1; i < data.length; i++) { if (data[i][0] == contactNumber) { subscriptions.push({ contact: data[i][0], name: data[i][1], address: data[i][2], subscriptionType: data[i][3] || '', lastPaymentDate: data[i][4] ? data[i][4].toDateString() : 'N/A' }); } } return subscriptions.length > 0 ? subscriptions : null; } // Process subscriber form data function processSubscriberForm(formData) { initializeSheets(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const subscribersSheet = ss.getSheetByName("Subscribers"); const contact = formData.contactNumber; if (!validateContact(contact)) { return { status: 'error', message: 'Please enter a valid 10-digit contact number' }; } const name = formData.name; const address = formData.address || ''; const subscriptionType = formData.subscriptionType || ''; if (!name) { return { status: 'error', message: 'Name is required' }; } const data = subscribersSheet.getDataRange().getValues(); let updated = false; for (let i = 1; i < data.length; i++) { if (data[i][0] == contact && data[i][3] == subscriptionType) { subscribersSheet.getRange(i + 1, 1, 1, 3).setValues([[contact, name, address]]); updated = true; break; } } if (!updated) { subscribersSheet.appendRow([contact, name, address, subscriptionType, '']); } return { status: 'success', message: `Subscriber ${name} ${subscriptionType ? subscriptionType + ' ' : ''}${updated ? 'updated' : 'added'}` }; } // Add or update subscriber via prompt function addOrUpdateSubscriber() { initializeSheets(); const ui = SpreadsheetApp.getUi(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const subscribersSheet = ss.getSheetByName("Subscribers"); const contact = ui.prompt('Enter Contact Number (10 digits)').getResponseText(); if (!validateContact(contact)) { ui.alert('Error', 'Please enter a valid 10-digit contact number', ui.ButtonSet.OK); return; } const subscriptions = getSubscriberByContact(contact); let name = subscriptions ? subscriptions[0].name : ''; let address = subscriptions ? subscriptions[0].address : ''; name = ui.prompt('Enter Name', name).getResponseText() || name; address = ui.prompt('Enter Address', address).getResponseText() || address; const subscriptionType = ui.prompt('Enter Subscription Type (e.g., Daily, Weekly, Monthly, New Year Calendar, or leave blank)').getResponseText(); if (!name) { ui.alert('Error', 'Name is required', ui.ButtonSet.OK); return; } const data = subscribersSheet.getDataRange().getValues(); let updated = false; for (let i = 1; i < data.length; i++) { if (data[i][0] == contact && data[i][3] == subscriptionType) { subscribersSheet.getRange(i + 1, 1, 1, 3).setValues([[contact, name, address]]); updated = true; break; } } if (!updated) { subscribersSheet.appendRow([contact, name, address, subscriptionType, '']); } ui.alert('Success', `Subscriber ${name} ${subscriptionType ? subscriptionType + ' ' : ''}${updated ? 'updated' : 'added'}`, ui.ButtonSet.OK); } // Check subscriptions function checkSubscriptions() { initializeSheets(); const ui = SpreadsheetApp.getUi(); const contact = ui.prompt('Enter Contact Number').getResponseText(); if (!validateContact(contact)) { ui.alert('Error', 'Please enter a valid 10-digit contact number', ui.ButtonSet.OK); return; } const subscriptions = getSubscriberByContact(contact); if (subscriptions) { let message = `Subscriber: ${subscriptions[0].name}\n\n`; subscriptions.forEach(sub => { const typeDisplay = sub.subscriptionType || 'None'; message += `Subscription/Item: ${typeDisplay}\nLast Payment: ${sub.lastPaymentDate}\n\n`; }); message += 'Note: Invoices are created manually with custom amounts.'; ui.alert('Subscriber Details', message, ui.ButtonSet.OK); } else { ui.alert('Error', 'Subscriber not found', ui.ButtonSet.OK); } } // Record payment function recordPayment() { initializeSheets(); const ui = SpreadsheetApp.getUi(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const subscribersSheet = ss.getSheetByName("Subscribers"); const paymentsSheet = ss.getSheetByName("Payments"); const contact = ui.prompt('Enter Contact Number').getResponseText(); if (!validateContact(contact)) { ui.alert('Error', 'Please enter a valid 10-digit contact number', ui.ButtonSet.OK); return; } const subscriptions = getSubscriberByContact(contact); if (!subscriptions) { ui.alert('Error', 'Subscriber not found', ui.ButtonSet.OK); return; } const subscriptionType = ui.prompt(`Enter Subscription/Item Type for ${subscriptions[0].name} (e.g., Daily, Weekly, Monthly, New Year Calendar)`).getResponseText(); if (!subscriptionType) { ui.alert('Error', 'Subscription/Item type is required for payments', ui.ButtonSet.OK); return; } const foundSub = subscriptions.find(sub => sub.subscriptionType === subscriptionType); if (!foundSub && subscriptionType !== 'None') { ui.alert('Error', `Subscription/Item ${subscriptionType} not found for this subscriber`, ui.ButtonSet.OK); return; } const amount = ui.prompt(`Enter Payment Amount for ${subscriptions[0].name} (${subscriptionType})`).getResponseText(); if (isNaN(amount) || amount <= 0) { ui.alert('Error', 'Please enter a valid amount', ui.ButtonSet.OK); return; } const date = ui.prompt('Enter Payment Date (YYYY-MM-DD)', ui.ButtonSet.OK_CANCEL).getResponseText() || new Date().toISOString().split('T')[0]; try { new Date(date); } catch (e) { ui.alert('Error', 'Please enter a valid date (YYYY-MM-DD)', ui.ButtonSet.OK); return; } const notes = ui.prompt('Enter Notes (Optional)', ui.ButtonSet.OK_CANCEL).getResponseText(); paymentsSheet.appendRow([contact, subscriptionType, new Date(date), parseFloat(amount), notes]); const data = subscribersSheet.getDataRange().getValues(); for (let i = 1; i < data.length; i++) { if (data[i][0] == contact && data[i][3] == subscriptionType) { subscribersSheet.getRange(i + 1, 5).setValue(new Date(date)); break; } } ui.alert('Success', 'Payment recorded', ui.ButtonSet.OK); } // Generate PDF directly using Google Docs for Invoices function generateInvoicePDFDirect(invoiceId, contactNumber, name, items, totalAmount, date) { try { initializeSheets(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const invoicesSheet = ss.getSheetByName("Invoices"); const doc = DocumentApp.create(`Invoice_${invoiceId}_${name}`); const body = doc.getBody(); body.clear(); const header = body.appendParagraph('INVOICE'); header.setHeading(DocumentApp.ParagraphHeading.HEADING1); header.setAlignment(DocumentApp.HorizontalAlignment.CENTER); body.appendParagraph(''); body.appendParagraph(`Invoice ID: ${invoiceId}`).setBold(true); body.appendParagraph(`Date: ${Utilities.formatDate(date, 'Asia/Kolkata', 'dd/MM/yyyy HH:mm')}`); body.appendParagraph(`Customer: ${name}`); body.appendParagraph(`Contact: ${contactNumber}`); body.appendParagraph(''); const table = body.appendTable(); const headerRow = table.appendTableRow(); headerRow.appendTableCell('Item').setBold(true); headerRow.appendTableCell('Amount').setBold(true); items.forEach(item => { const row = table.appendTableRow(); row.appendTableCell(item.item); const amount = parseFloat(item.amount) || 0; row.appendTableCell(`₹${amount.toFixed(2)}`); }); const totalRow = table.appendTableRow(); totalRow.appendTableCell('TOTAL').setBold(true); const total = parseFloat(totalAmount) || 0; totalRow.appendTableCell(`₹${total.toFixed(2)}`).setBold(true); body.appendParagraph(''); body.appendParagraph('Thank you for your business!').setItalic(true); body.appendParagraph('COME AGAIN!').setBold(true); doc.saveAndClose(); const docFile = DriveApp.getFileById(doc.getId()); const pdfBlob = docFile.getAs(MimeType.PDF); pdfBlob.setName(`${name}_${invoiceId}_invoice.pdf`); const folderName = "AppleInvoiceFolder"; let folder = DriveApp.getFoldersByName(folderName).next() || DriveApp.createFolder(folderName); const pdfFile = folder.createFile(pdfBlob); const pdfUrl = pdfFile.getUrl(); const activeUserEmail = Session.getActiveUser().getEmail(); const subject = `Invoice ${invoiceId}`; const htmlBody = `Dear ${name},<br><br>Your invoice is attached.<br><br><strong>COME AGAIN!</strong>`; MailApp.sendEmail({ to: activeUserEmail, subject: subject, htmlBody: htmlBody, attachments: [pdfFile] }); DriveApp.getFileById(doc.getId()).setTrashed(true); const data = invoicesSheet.getDataRange().getValues(); for (let i = 1; i < data.length; i++) { if (data[i][0] === invoiceId) { invoicesSheet.getRange(i + 1, 8).setValue(pdfUrl); invoicesSheet.getRange(i + 1, 7).setValue('Generated'); break; } } return pdfUrl; } catch (error) { Logger.log(`Error generating PDF for Invoice ${invoiceId}: ${error}`); throw error; } } // Generate all pending invoice PDFs function generateAllPendingInvoicePDFs() { initializeSheets(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const invoicesSheet = ss.getSheetByName("Invoices"); const data = invoicesSheet.getDataRange().getValues(); for (let i = 1; i < data.length; i++) { if (data[i][6] === 'Pending') { try { const invoiceId = data[i][0]; const contactNumber = data[i][1]; const name = data[i][2]; const itemsString = data[i][3]; const totalAmount = data[i][4]; const date = data[i][5]; const items = itemsString.split(',').map(item => { const [itemName, amount] = item.split(':').map(part => part.trim()); return { item: itemName, amount: parseFloat(amount) || 0 }; }); const pdfUrl = generateInvoicePDFDirect(invoiceId, contactNumber, name, items, totalAmount, date); invoicesSheet.getRange(i + 1, 7).setValue('Generated'); invoicesSheet.getRange(i + 1, 8).setValue(pdfUrl); } catch (error) { Logger.log(`Failed to generate PDF for invoice at row ${i + 1}: ${error}`); } } } SpreadsheetApp.getUi().alert('Success', 'All pending invoice PDFs generated and emailed', SpreadsheetApp.getUi().ButtonSet.OK); } // Export specific invoice PDF function exportInvoicePDF() { initializeSheets(); const ui = SpreadsheetApp.getUi(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const invoicesSheet = ss.getSheetByName("Invoices"); const invoiceId = ui.prompt('Enter Invoice ID (e.g., INV001)').getResponseText(); const data = invoicesSheet.getDataRange().getValues(); let invoiceData; for (let i = 1; i < data.length; i++) { if (data[i][0] === invoiceId) { invoiceData = data[i]; break; } } if (!invoiceData) { ui.alert('Error', 'Invoice ID not found', ui.ButtonSet.OK); return; } try { const contactNumber = invoiceData[1]; const name = invoiceData[2]; const itemsString = invoiceData[3]; const totalAmount = invoiceData[4]; const date = invoiceData[5]; const items = itemsString.split(',').map(item => { const [itemName, amount] = item.split(':').map(part => part.trim()); return { item: itemName, amount: parseFloat(amount) || 0 }; }); const pdfUrl = generateInvoicePDFDirect(invoiceId, contactNumber, name, items, totalAmount, date); ui.alert('Success', `PDF for Invoice ${invoiceId} exported to Google Drive and emailed`, ui.ButtonSet.OK); } catch (error) { ui.alert('Error', `Failed to generate PDF: ${error}`, ui.ButtonSet.OK); } } // Create summary report function createSummaryReport() { initializeSheets(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const invoicesSheet = ss.getSheetByName("Invoices"); const paymentsSheet = ss.getSheetByName("Payments"); const summarySheet = ss.getSheetByName("Summary") || ss.insertSheet("Summary"); summarySheet.clear(); summarySheet.getRange('A1').setValue('Total Invoiced').setFontWeight('bold'); summarySheet.getRange('B1').setValue('=SUM(Invoices!E2:E)'); summarySheet.getRange('A2').setValue('Total Payments').setFontWeight('bold'); summarySheet.getRange('B2').setValue('=SUM(Payments!D2:D)'); } // Backup sheet function backupSheet() { initializeSheets(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const folder = DriveApp.getFolderById('YOUR_FOLDER_ID'); // Replace with your folder ID folder.createFile(ss.getAs(MimeType.GOOGLE_SHEETS)).setName(`Backup_${new Date().toDateString()}`); } // Populate Invoice sheet for WhatsApp function function populateInvoiceSheet(invoiceId, contact, name, items, totalAmount, selectedDate) { const ss = SpreadsheetApp.getActiveSpreadsheet(); let invoiceSheet = ss.getSheetByName("Invoice"); if (!invoiceSheet) { invoiceSheet = ss.insertSheet("Invoice"); } invoiceSheet.clear(); invoiceSheet.getRange(1, 1).setValue("Invoice ID:"); invoiceSheet.getRange(1, 2).setValue(invoiceId); invoiceSheet.getRange(3, 1).setValue("Date:"); invoiceSheet.getRange(3, 2).setValue(selectedDate); invoiceSheet.getRange(4, 1).setValue("Customer Name:"); invoiceSheet.getRange(4, 2).setValue(name); invoiceSheet.getRange(5, 1).setValue("Phone Number:"); invoiceSheet.getRange(5, 2).setValue(contact); invoiceSheet.getRange(6, 1).setValue("Items"); let currentRow = 7; items.forEach(function(item) { invoiceSheet.getRange(currentRow, 1).setValue(item.item); invoiceSheet.getRange(currentRow, 2).setValue(item.amount); currentRow++; }); invoiceSheet.getRange(currentRow, 1).setValue("Total Amount"); invoiceSheet.getRange(currentRow, 2).setValue(totalAmount); }

Code Block 2: SubscriberForm.html (HTML Form)

<!DOCTYPE html> <html> <head> <base target="_top"> <style> body { font-family: Arial, sans-serif; padding: 20px; } .form-group { margin-bottom: 10px; } label { display: inline-block; width: 120px; } input, select { width: 200px; padding: 5px; font-size: 14px; } input[type="submit"] { width: auto; padding: 8px 20px; background-color: #4CAF50; color: white; border: none; cursor: pointer; } input[type="submit"]:hover { background-color: #45a049; } #result { margin-top: 10px; font-weight: bold; color: green; } #error { margin-top: 10px; font-weight: bold; color: red; } </style> </head> <body> <form id="subscriberForm"> <div class="form-group"> <label for="contactNumber">Contact Number:</label> <input type="text" id="contactNumber" name="contactNumber" maxlength="10" required> </div> <div class="form-group"> <label for="name">Name:</label> <input type="text" id="name" name="name" required> </div> <div class="form-group"> <label for="address">Address:</label> <input type="text" id="address" name="address"> </div> <div class="form-group"> <label for="subscriptionType">Subscription Type:</label> <input type="text" id="subscriptionType" name="subscriptionType" placeholder="e.g., Daily, Weekly"> </div> <div class="form-group"> <input type="submit" value="Submit"> </div> </form Middle: form <div id="result"></div> <div id="error"></div> <script> document.getElementById('contactNumber').addEventListener('input', function() { const contact = this.value; if (contact.length === 10 && /^\d{10}$/.test(contact)) { google.script.run .withSuccessHandler(fillForm) .withFailureHandler(showError) .getSubscriberByContact(contact); } }); function fillForm(subscriptions) { const resultDiv = document.getElementById('result'); const errorDiv = document.getElementById('error'); errorDiv.innerHTML = ''; if (subscriptions && subscriptions.length > 0) { document.getElementById('name').value = subscriptions[0].name; document.getElementById('address').value = subscriptions[0].address; document.getElementById('subscriptionType').value = subscriptions[0].subscriptionType; resultDiv.innerHTML = 'Existing subscriber found. Update details and submit.'; } else { resultDiv.innerHTML = 'New subscriber. Enter details and submit.'; } } document.getElementById('subscriberForm').addEventListener('submit', function(e) { e.preventDefault(); const formData = { contactNumber: this.contactNumber.value, name: this.name.value, address: this.address.value, subscriptionType: this.subscriptionType.value }; google.script.run .withSuccessHandler(function(result) { document.getElementById('result').innerHTML = result.message; document.getElementById('error').innerHTML = ''; if (result.status === 'success') { document.getElementById('subscriberForm').reset(); setTimeout(function() { google.script.host.close(); }, 1000); } }) .withFailureHandler(showError) .processSubscriberForm(formData); }); function showError(error) { document.getElementById('error').innerHTML = 'Error: ' + error.message; document.getElementById('result').innerHTML = ''; } </script> </body> </html>

Code Block 3: WhatsApp.gs (WhatsApp Integration)

function WhatsAppWordsWithVariedTotalRowFixed() { var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var sheet = spreadsheet.getSheetByName('Invoice'); spreadsheet.setActiveSheet(sheet); // Read data based on debug results var invoiceId = sheet.getRange(1, 2).getValue(); // B1: INV008 var invoiceDate = sheet.getRange(3, 2).getValue(); // B3: Date object var customerName = sheet.getRange(4, 2).getValue(); // B4: Muralikri var phoneNumber = sheet.getRange(5, 2).getValue(); // B5: 9037393709 // Process customer name customerName = customerName ? customerName.toString().trim() : ""; // Process phone number (it's already a number, convert to string) phoneNumber = phoneNumber ? phoneNumber.toString() : ""; // Format date properly from Date object if (invoiceDate instanceof Date) { var day = ("0" + invoiceDate.getDate()).slice(-2); var month = ("0" + (invoiceDate.getMonth() + 1)).slice(-2); var year = invoiceDate.getFullYear(); invoiceDate = day + "/" + month + "/" + year; } Logger.log("Processed customer name: '" + customerName + "'"); Logger.log("Processed phone number: '" + phoneNumber + "'"); Logger.log("Formatted date: '" + invoiceDate + "'"); // Get items starting from row 7 var items = []; var currentRow = 7; while (currentRow < 20) { var itemName = sheet.getRange(currentRow, 1).getValue(); var itemValue = sheet.getRange(currentRow, 2).getValue(); // Stop when we reach "Total Amount" or empty cells if (itemName === "Total Amount" || itemName === "" || itemName === null) { break; } // Add valid items if (itemName && itemValue && typeof itemValue === 'number') { items.push({ name: itemName.toString().trim(), amount: itemValue }); } currentRow++; } // Calculate total from items var totalAmount = items.reduce(function(sum, item) { return sum + item.amount; }, 0); Logger.log("Retrieved invoice ID: " + invoiceId); Logger.log("Retrieved customer name: '" + customerName + "'"); Logger.log("Retrieved phone number: '" + phoneNumber + "'"); Logger.log("Calculated total amount: " + totalAmount); Logger.log("Retrieved invoice date: " + invoiceDate); Logger.log("Retrieved items: " + JSON.stringify(items)); // Validation checks if (!phoneNumber || phoneNumber.length < 10) { Logger.log("Phone number is missing or invalid. Phone: '" + phoneNumber + "'"); Browser.msgBox("Error", "Phone number is missing or invalid. Found: '" + phoneNumber + "'", Browser.Buttons.OK); return; } if (!customerName) { Logger.log("Customer name is missing."); Browser.msgBox("Error", "Customer name is missing. Found: '" + customerName + "'", Browser.Buttons.OK); return; } if (items.length === 0) { Logger.log("No items found."); Browser.msgBox("Error", "No items found. Please check your items list.", Browser.Buttons.OK); return; } // Function to convert number to words (Indian format) function numberToWords(num) { const ones = ['', 'One', 'Two', 'Three', 'Four', 'Five', 'Six', 'Seven', 'Eight', 'Nine']; const tens = ['', '', 'Twenty', 'Thirty', 'Forty', 'Fifty', 'Sixty', 'Seventy', 'Eighty', 'Ninety']; const teens = ['Ten', 'Eleven', 'Twelve', 'Thirteen', 'Fourteen', 'Fifteen', 'Sixteen', 'Seventeen', 'Eighteen', 'Nineteen']; function convert(n) { if (n < 10) return ones[n]; if (n < 20) return teens[n - 10]; if (n < 100) return tens[Math.floor(n / 10)] + (n % 10 !== 0 ? ' ' + ones[n % 10] : ''); if (n < 1000) return ones[Math.floor(n / 100)] + ' Hundred' + (n % 100 !== 0 ? ' ' + convert(n % 100) : ''); if (n < 100000) return convert(Math.floor(n / 1000)) + ' Thousand' + (n % 1000 !== 0 ? ' ' + convert(n % 1000) : ''); if (n < 10000000) return convert(Math.floor(n / 100000)) + ' Lakh' + (n % 100000 !== 0 ? ' ' + convert(n % 100000) : ''); return convert(Math.floor(n / 10000000)) + ' Crore' + (n % 10000000 !== 0 ? ' ' + convert(n % 10000000) : ''); } const rupees = Math.floor(num); const paise = Math.round((num - rupees) * 100); let result = convert(rupees) + ' Rupees'; if (paise > 0) { result += ' and ' + convert(paise) + ' Paise'; } return result; } // Create items list for WhatsApp message var itemsList = ""; items.forEach(function(item) { itemsList += item.name + " - ₹" + item.amount + "\n"; }); var totalInWords = numberToWords(totalAmount); var spaces = " ".repeat(25); var formattedMessage = spaces + "*INVOICE*\n\n" + "*Invoice ID:* " + invoiceId + "\n" + "*Date:* " + invoiceDate + "\n" + "*Customer:* " + customerName + "\n" + "*Phone:* " + phoneNumber + "\n\n" + "*ITEMS:*\n" + "```" + itemsList + "```\n" + "*Total Amount: ₹" + totalAmount + "*\n\n" + "*Amount in Words:*\n" + "_" + totalInWords + "_\n\n" + "*Thank You for Your Business!* ❤️"; // Create WhatsApp link var whatsappLink = "https://api.whatsapp.com/send?phone=" + phoneNumber + "&text=" + encodeURIComponent(formattedMessage); var displayText = "Send WhatsApp Invoice"; var hyperLinkFormula = '=HYPERLINK("' + whatsappLink + '", "' + displayText + '")'; // Place the hyperlink in cell D1 var cellD1 = sheet.getRange(1, 4); cellD1.setFormula(hyperLinkFormula); Logger.log("SUCCESS: WhatsApp hyperlink has been created in cell D1!"); // Show success message Browser.msgBox("Success", "WhatsApp invoice link has been created successfully in cell D1!", Browser.Buttons.OK); // Optional: Show preview of the message in logs Logger.log("WhatsApp Message Preview:"); Logger.log(formattedMessage); }

Code Block 4: InvoiceGeneration.gs (Invoice Generation)

// Generate invoice via dropdown function generateInvoicetest() { initializeSheets(); const ui = SpreadsheetApp.getUi(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const subscribersSheet = ss.getSheetByName("Subscribers"); const itemsSheet = ss.getSheetByName("Items"); // Prompt for contact number const contact = ui.prompt('Enter Contact Number').getResponseText(); if (!validateContact(contact)) { ui.alert('Error', 'Please enter a valid 10-digit contact number', ui.ButtonSet.OK); return; } // Get subscriber name const subscriptions = getSubscriberByContact(contact); const name = subscriptions ? subscriptions[0].name : ui.prompt('Enter Name').getResponseText(); if (!name) { ui.alert('Error', 'Name is required', ui.ButtonSet.OK); return; } // Fetch items and prices from Items sheet (C4:C6 for items, D4:D6 for prices) const itemRange = itemsSheet.getRange("C4:C6").getValues(); const priceRange = itemsSheet.getRange("D4:D6").getValues(); const itemsList = itemRange .map((row, index) => ({ item: row[0], price: parseFloat(priceRange[index][0]) })) .filter(item => item.item && !isNaN(item.price) && item.price > 0); if (itemsList.length === 0) { ui.alert('Error', 'No valid items found in Items sheet (C4:C6)', ui.ButtonSet.OK); return; } // Store contact and name in PropertiesService const properties = PropertiesService.getScriptProperties(); properties.setProperties({ 'invoiceContact': contact, 'invoiceName': name, 'itemsList': JSON.stringify(itemsList) }); // Show item selection dialog const html = HtmlService.createHtmlOutput(` <style> .container { font-family: Arial; padding: 10px; } .item-row { margin-bottom: 10px; } .item-select { width: 150px; } .quantity-input { width: 50px; } button { margin: 5px; } </style> <div class="container"> <h3>Select Items and Quantities</h3> <div id="itemsContainer"> <div class="item-row"> <select class="item-select" id="item0"> <option value="">Select Item</option> ${itemsList.map(item => `<option value="${item.item}:${item.price}">${item.item} (₹${item.price.toFixed(2)})</option>`).join('')} </select> <input type="number" class="quantity-input" id="quantity0" min="1" placeholder="Qty" /> </div> </div> <button onclick="addItemRow()">Add Another Item</button> <br><br> <button onclick="submitItems()">OK</button> <button onclick="google.script.host.close()">Cancel</button> </div> <script> let rowCount = 1; function addItemRow() { const container = document.getElementById('itemsContainer'); const newRow = document.createElement('div'); newRow.className = 'item-row'; newRow.innerHTML = \` <select class="item-select" id="item\${rowCount}"> <option value="">Select Item</option> ${itemsList.map(item => `<option value="${item.item}:${item.price}">${item.item} (₹${item.price.toFixed(2)})</option>`).join('')} </select> <input type="number" class="quantity-input" id="quantity\${rowCount}" min="1" placeholder="Qty" /> \`; container.appendChild(newRow); rowCount++; } function submitItems() { const items = []; let totalAmount = 0; for (let i = 0; i < rowCount; i++) { const itemSelect = document.getElementById('item' + i); const quantityInput = document.getElementById('quantity' + i); if (itemSelect.value && quantityInput.value) { const [itemName, price] = itemSelect.value.split(':'); const quantity = parseInt(quantityInput.value); if (quantity > 0) { items.push({ item: itemName, amount: parseFloat(price) * quantity }); totalAmount += parseFloat(price) * quantity; } } } if (items.length === 0) { alert('Please select at least one item with a valid quantity.'); return; } google.script.run .withSuccessHandler(showDatePicker) .storeItems(JSON.stringify(items), totalAmount.toString()); } function showDatePicker() { google.script.run .withSuccessHandler(closeDialog) .showDatePicker(); } function closeDialog() { google.script.host.close(); } </script> `) .setTitle('Select Invoice Items') .setWidth(400) .setHeight(300); ui.showModalDialog(html, 'Select Invoice Items'); } // Store selected items function storeItems(itemsJson, totalAmount) { const properties = PropertiesService.getScriptProperties(); properties.setProperties({ 'invoiceItems': itemsJson, 'invoiceTotalAmount': totalAmount, 'invoiceItemsInput': JSON.parse(itemsJson).map(item => `${item.item}:${item.amount}`).join(',') }); } // Show date picker function showDatePicker() { const ui = SpreadsheetApp.getUi(); const html = HtmlService.createHtmlOutput(` <input type="date" id="invoiceDate" value="${new Date().toISOString().split('T')[0]}" /> <br><br> <button onclick="submitDate()">OK</button> <button onclick="google.script.host.close()">Cancel</button> <script> function submitDate() { const date = document.getElementById('invoiceDate').value; google.script.run.withSuccessHandler(closeDialog).processDate(date); } function closeDialog() { google.script.host.close(); } </script> `) .setTitle('Select Invoice Date') .setWidth(300) .setHeight(150); ui.showModalDialog(html, 'Select Invoice Date'); } // Process the selected date function processDate(date) { const ui = SpreadsheetApp.getUi(); const ss = SpreadsheetApp.getActiveSpreadsheet(); const invoicesSheet = ss.getSheetByName("Invoices"); const properties = PropertiesService.getScriptProperties(); const contact = properties.getProperty('invoiceContact'); const name = properties.getProperty('invoiceName'); const items = JSON.parse(properties.getProperty('invoiceItems')); const totalAmount = parseFloat(properties.getProperty('invoiceTotalAmount')); const itemsInput = properties.getProperty('invoiceItemsInput'); properties.deleteAllProperties(); try { const selectedDate = new Date(date); if (isNaN(selectedDate.getTime())) { ui.alert('Error', 'Please select a valid date', ui.ButtonSet.OK); return; } if (!contact || !name || !items || isNaN(totalAmount) || !itemsInput) { ui.alert('Error', 'Missing required data. Please try again.', ui.ButtonSet.OK); return; } const invoiceId = 'INV' + (invoicesSheet.getLastRow()).toString().padStart(3, '0'); const itemsString = itemsInput.split(',').map(item => item.trim()).filter(item => item).join(','); invoicesSheet.appendRow([invoiceId, contact, name, itemsString, totalAmount, selectedDate, 'Pending', '']); const pdfUrl = generateInvoicePDFDirect(invoiceId, contact, name, items, totalAmount, selectedDate); const data = invoicesSheet.getDataRange().getValues(); for (let i = 1; i < data.length; i++) { if (data[i][0] === invoiceId) { invoicesSheet.getRange(i + 1, 8).setValue(pdfUrl); invoicesSheet.getRange(i + 1, 7).setValue('Generated'); break; } } populateInvoiceSheet(invoiceId, contact, name, items, totalAmount, selectedDate); ui.alert('Success', `Invoice ${invoiceId} generated and PDF created for ${itemsString}`, ui.ButtonSet.OK); } catch (e) { ui.alert('Error', `An error occurred: ${e.message}`, ui.ButtonSet.OK); Logger.log(`Error in processDate: ${e}`); } }

Tips and Troubleshooting

• Validation: The system ensures 10-digit contact numbers and requires names for subscribers and invoices.
• Items Sheet: Ensure valid items and prices are listed in C4:C6 and D4:D6 of the Items sheet for invoice creation.
• PDF Export: PDFs are saved in a Google Doc format with a table of items, stored in the AppleInvoiceFolder, and emailed to your Google account.
• WhatsApp Integration: Ensure the Invoice sheet is populated (by creating an invoice) before running the WhatsAppWordsWithVariedTotalRowFixed function. The hyperlink in cell D1 requires a valid 10-digit phone number.
• Backup: Replace YOUR_FOLDER_ID in the backupSheet function with a valid Google Drive folder ID to enable backups.
• Add WhatsApp to Custom Menu: To add the WhatsApp feature to the custom menu, modify the onOpen function in Code.gs to:

  function onOpen() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('Subscription Management')
      .addItem('Add/Update Subscriber (Form)', 'showSubscriberForm')
      .addItem('Add/Update Subscriber (Prompt)', 'addOrUpdateSubscriber')
      .addItem('Create Invoice (Dropdown)', 'generateInvoicetest')
      .addItem('Check Subscriptions', 'checkSubscriptions')
      .addItem('Record Payment', 'recordPayment')
      .addItem('Export Invoice PDF', 'exportInvoicePDF')
      .addItem('Generate All Pending Invoice PDFs', 'generateAllPendingInvoicePDFs')
      .addItem('Create Summary Report', 'createSummaryReport')
      .addItem('Backup Sheet', 'backupSheet')
      .addItem('Send WhatsApp Invoice', 'WhatsAppWordsWithVariedTotalRowFixed')
      .addToUi();
  }
        

• Errors:
  • "Invalid contact number": Ensure the contact number is 10 digits.
  • "Subscriber not found": Verify the contact number exists in the Subscribers sheet.
  • "No valid items found": Check that the Items sheet has valid entries in C4:C6 and D4:D6.
  • "PDF not emailed": Confirm Gmail permissions and your account’s email address.
  • "Invalid date": Ensure dates are in YYYY-MM-DD format.
  • "Phone number is missing or invalid" (WhatsApp): Verify the phone number in the Invoice sheet is valid.
  • "No items found" (WhatsApp): Ensure the Invoice sheet has valid items listed starting from row 7.
  • "Script function not found": Ensure all script files (Code.gs, WhatsApp.gs, InvoiceGeneration.gs) are correctly named and saved.
• Customize: Modify the HTML form’s CSS for styling, adjust the PDF format in generateInvoicePDFDirect, tweak the WhatsApp message format in WhatsAppWordsWithVariedTotalRowFixed, or enhance the summary report in createSummaryReport.

Note: The invoice generation functions in InvoiceGeneration.gs depend on initializeSheets, validateContact, getSubscriberByContact, generateInvoicePDFDirect, and populateInvoiceSheet from Code.gs. Ensure Code.gs is set up before running invoice-related functions. To make the WhatsApp feature more accessible, add it to the custom menu as shown above.