Web Tool Help

Manage Newspaper Subscriptions with Google Apps Script: A Complete Solution

Running a newspaper agency? Managing subscribers, invoices, and payments can be time-consuming, but this Google Apps Script simplifies it all! This powerful script automates subscriber management, invoice creation, payment tracking, PDF generation, and reporting, all within Google Sheets. Whether you’re handling daily newspaper deliveries, weekly supplements, or special items like New Year Calendars, this script is your one-stop solution. Click the copy buttons below to easily paste the code into your Google Sheets Script Editor!

What Does This Script Do?

This script provides a comprehensive system for your newspaper agency, allowing you to:

Manage Subscribers: Add or update subscriber details (contact number, name, address, subscription type) using a form or prompts.
Create Invoices: Generate invoices for subscriptions or items (e.g., Daily, Weekly, New Year Calendar) with custom amounts, stored in a dedicated sheet.
Track Payments: Record payments and update subscriber records with the last payment date.
Export Invoices as PDFs: Save invoices as PDFs in Google Drive and email them to the agency’s email address.
Check Subscriptions: View a subscriber’s details and subscription status by contact number.
Generate Reports: Create a summary report of total invoiced amounts and payments.
Backup Data: Save a copy of your spreadsheet to Google Drive for safekeeping.

Perfect for: Newspaper agencies, magazine publishers, or any subscription-based business looking to streamline operations!

The Script

Below are the main script and two HTML files for forms. Use the copy buttons to quickly copy each into your Google Sheets Script Editor.

Main Script

Copy the main script to manage subscribers, invoices, payments, and more:

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 (Form)', 'showInvoiceForm')
    .addItem('Create Invoice (Prompt)', 'generateInvoice')
    .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();
}

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

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

function showSubscriberForm() {
  initializeSheets();
  const html = HtmlService.createHtmlOutputFromFile('SubscriberForm')
    .setWidth(400)
    .setHeight(450);
  SpreadsheetApp.getUi().showModalDialog(html, 'Add/Update Subscriber');
}

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

function validateContact(contact) {
  return /^\d{10}$/.test(contact);
}

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

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

function processInvoiceForm(formData) {
  initializeSheets();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const invoicesSheet = ss.getSheetByName("Invoices");
  
  const contact = formData.contactNumber;
  if (!validateContact(contact)) {
    return { status: 'error', message: 'Please enter a valid 10-digit contact number' };
  }
  
  const name = formData.name;
  if (!name) {
    return { status: 'error', message: 'Name is required' };
  }
  
  const items = JSON.parse(formData.items);
  if (!items || items.length === 0) {
    return { status: 'error', message: 'At least one item is required' };
  }
  
  let totalAmount = 0;
  const itemStrings = [];
  for (const item of items) {
    if (!item.item || isNaN(item.amount) || item.amount <= 0) {
      return { status: 'error', message: `Invalid item or amount: ${item.item}` };
    }
    totalAmount += parseFloat(item.amount);
    itemStrings.push(`${item.item}:${item.amount}`);
  }
  
  const date = formData.date;
  try {
    new Date(date);
  } catch (e) {
    return { status: 'error', message: 'Please enter a valid date (YYYY-MM-DD)' };
  }
  
  const invoiceId = 'INV' + (invoicesSheet.getLastRow()).toString().padStart(3, '0');
  const itemsString = itemStrings.join(',');
  invoicesSheet.appendRow([invoiceId, contact, name, itemsString, totalAmount, new Date(date), 'Pending', '']);
  
  createInvoiceSheet(invoiceId, contact, name, items, totalAmount, new Date(date));
  
  return { status: 'success', message: `Invoice ${invoiceId} created for ${itemsString}` };
}

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

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

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

function generateInvoice() {
  initializeSheets();
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const subscribersSheet = ss.getSheetByName("Subscribers");
  const invoicesSheet = ss.getSheetByName("Invoices");
  
  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);
  const name = subscriptions ? subscriptions[0].name : ui.prompt('Enter Name').getResponseText();
  
  if (!name) {
    ui.alert('Error', 'Name is required', ui.ButtonSet.OK);
    return;
  }
  
  const itemsInput = ui.prompt('Enter Items and Amounts (e.g., Daily:100,Weekly:60)').getResponseText();
  const items = [];
  let totalAmount = 0;
  const itemStrings = itemsInput.split(',').map(item => item.trim()).filter(item => item);
  for (const item of itemStrings) {
    const [itemName, amount] = item.split(':').map(part => part.trim());
    if (!itemName || isNaN(amount) || amount <= 0) {
      ui.alert('Error', `Invalid item or amount: ${item}`, ui.ButtonSet.OK);
      return;
    }
    items.push({ item: itemName, amount: parseFloat(amount) });
    totalAmount += parseFloat(amount);
  }
  
  if (items.length === 0) {
    ui.alert('Error', 'At least one item is required', ui.ButtonSet.OK);
    return;
  }
  
  const date = ui.prompt('Enter Invoice Date (YYYY-MM-DD)', ui.ButtonSet.OK_CANCEL).getResponseText();
  try {
    new Date(date);
  } catch (e) {
    ui.alert('Error', 'Please enter a valid date (YYYY-MM-DD)', ui.ButtonSet.OK);
    return;
  }
  
  const invoiceId = 'INV' + (invoicesSheet.getLastRow()).toString().padStart(3, '0');
  const itemsString = itemStrings.join(',');
  invoicesSheet.appendRow([invoiceId, contact, name, itemsString, totalAmount, new Date(date), 'Pending', '']);
  
  createInvoiceSheet(invoiceId, contact, name, items, totalAmount, new Date(date));
  
  ui.alert('Success', `Invoice ${invoiceId} generated for ${itemsString}`, ui.ButtonSet.OK);
}

function createInvoiceSheet(invoiceId, contactNumber, name, items, totalAmount, date) {
  initializeSheets();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const invoiceSheet = ss.insertSheet(`Invoice_${invoiceId}`);
  let row = 1;
  invoiceSheet.getRange(`A${row}`).setValue('Invoice ID').setFontWeight('bold');
  invoiceSheet.getRange(`B${row}`).setValue(invoiceId);
  row++;
  invoiceSheet.getRange(`A${row}`).setValue('Name').setFontWeight('bold');
  invoiceSheet.getRange(`B${row}`).setValue(name);
  row++;
  invoiceSheet.getRange(`A${row}`).setValue('Contact').setFontWeight('bold');
  invoiceSheet.getRange(`B${row}`).setValue(contactNumber);
  row++;
  invoiceSheet.getRange(`A${row}`).setValue('Items').setFontWeight('bold');
  row++;
  items.forEach(item => {
    invoiceSheet.getRange(`A${row}`).setValue(item.item);
    invoiceSheet.getRange(`B${row}`).setValue(item.amount);
    row++;
  });
  invoiceSheet.getRange(`A${row}`).setValue('Total Amount').setFontWeight('bold');
  invoiceSheet.getRange(`B${row}`).setValue(totalAmount);
  row++;
  invoiceSheet.getRange(`A${row}`).setValue('Date').setFontWeight('bold');
  invoiceSheet.getRange(`B${row}`).setValue(date);
}

function generateInvoicePDF(contactNumber, invoiceId) {
  initializeSheets();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const invoicesSheet = ss.getSheetByName("Invoices");
  const sh = ss.getSheetByName(`Invoice_${invoiceId}`);
  if (!sh) {
    Logger.log(`Invoice sheet Invoice_${invoiceId} not found`);
    return;
  }
  
  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) {
    Logger.log(`Invoice ${invoiceId} not found in Invoices sheet`);
    return;
  }
  
  const name = invoiceData[2];
  const items = invoiceData[3].split(',').map(item => {
    const [itemName, amount] = item.split(':').map(part => part.trim());
    return { item: itemName, amount: parseFloat(amount) };
  });
  
  const range = 'A1:B' + (4 + items.length + 2);
  
  const ssID = ss.getId();
  const sheetTabId = sh.getSheetId();
  const url_base = ss.getUrl().replace(/edit$/, '');
  
  const folderName = "AppleInvoiceFolder";
  let folder;
  const folders = DriveApp.getFoldersByName(folderName);
  if (folders.hasNext()) {
    folder = folders.next();
  } else {
    folder = DriveApp.createFolder(folderName);
  }
  
  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
  };
  
  SpreadsheetApp.flush();
  
  const response = UrlFetchApp.fetch(exportUrl, options);
  
  if (response.getResponseCode() !== 200) {
    Logger.log(`Error exporting PDF for Invoice ${invoiceId}. Response Code: ${response.getResponseCode()}`);
    return;
  }
  
  const blob = response.getBlob();
  blob.setName(`${name} ${invoiceId} invoice.pdf`);
  
  const file = folder.createFile(blob);
  const pdfUrl = file.getUrl();
  
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === invoiceId) {
      invoicesSheet.getRange(i + 1, 8).setValue(pdfUrl);
      break;
    }
  }
  
  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: [file]
  });
}

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') {
      generateInvoicePDF(data[i][1], data[i][0]);
      invoicesSheet.getRange(i + 1, 7).setValue('Generated');
    }
  }
  SpreadsheetApp.getUi().alert('Success', 'All pending invoice PDFs generated and emailed', SpreadsheetApp.getUi().ButtonSet.OK);
}

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;
  }
  
  generateInvoicePDF(invoiceData[1], invoiceId);
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === invoiceId) {
      invoicesSheet.getRange(i + 1, 7).setValue('Generated');
      break;
    }
  }
  
  ui.alert('Success', `PDF for Invoice ${invoiceId} exported to Google Drive and emailed`, ui.ButtonSet.OK);
}

function sendDueReminders() {
  initializeSheets();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const invoicesSheet = ss.getSheetByName("Invoices");
  const data = invoicesSheet.getDataRange().getValues();
  const subscribers = {};
  
  for (let i = 1; i < data.length; i++) {
    if (data[i][6] === 'Pending') {
      const contact = data[i][1];
      if (!subscribers[contact]) {
        subscribers[contact] = {
          name: data[i][2],
          invoices: []
        };
      }
      subscribers[contact].invoices.push(`Invoice ${data[i][0]} (${data[i][3]}): ${data[i][4]}`);
    }
  }
  
  for (const contact in subscribers) {
    if (subscribers[contact].invoices.length > 0) {
      try {
        MailApp.sendEmail({
          to: contact + '@example.com',
          subject: 'Invoice Reminder',
          body: `Dear ${subscribers[contact].name},\n\nYour pending invoices:\n${subscribers[contact].invoices.join('\n')}`
        });
      } catch (e) {
        Logger.log(`Failed to send email to ${contact}: ${e}`);
      }
    }
  }
}

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

function backupSheet() {
  initializeSheets();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const folder = DriveApp.getFolderById('YOUR_FOLDER_ID');
  folder.createFile(ss.getAs(MimeType.GOOGLE_SHEETS)).setName(`Backup_${new Date().toDateString()}`);
}

Subscriber Form (SubscriberForm.html)

Copy the Subscriber Form HTML to create a user-friendly form for adding or updating subscribers:

<!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: 100px; }
      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/Item:</label>
        <select id="subscriptionType" name="subscriptionType">
          <option value="">None</option>
          <option value="Daily">Daily</option>
          <option value="Weekly">Weekly</option>
          <option value="Monthly">Monthly</option>
          <option value="New Year Calendar">New Year Calendar</option>
        </select>
      </div>
      <div class="form-group">
        <input type="submit" value="Submit">
      </div>
    </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 || '';
          resultDiv.innerHTML = 'Existing subscriber found. Select subscription/item type to add or update, or leave blank for no subscription.';
        } 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>

Invoice Form (InvoiceForm.html)

Copy the Invoice Form HTML to create a form for generating invoices with multiple items:

<!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: 100px; }
      input, select { width: 200px; padding: 5px; font-size: 14px; }
      input[type="submit"], button { width: auto; padding: 8px 20px; background-color: #4CAF50; color: white; border: none; cursor: pointer; }
      input[type="submit"]:hover, button:hover { background-color: #45a049; }
      .item-row { display: flex; align-items: center; margin-bottom: 5px; }
      .item-row input { width: 150px; margin-right: 10px; }
      .remove-btn { background-color: #ff4444; }
      .remove-btn:hover { background-color: #cc0000; }
      #result { margin-top: 10px; font-weight: bold; color: green; }
      #error { margin-top: 10px; font-weight: bold; color: red; }
    </style>
  </head>
  <body>
    <form id="invoiceForm">
      <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="date">Invoice Date:</label>
        <input type="date" id="date" name="date" required>
      </div>
      <div class="form-group">
        <label>Items:</label>
        <div id="itemsContainer">
          <div class="item-row">
            <input type="text" name="item" placeholder="Item (e.g., Daily)" required>
            <input type="number" name="amount" min="0" step="0.01" placeholder="Amount" required>
            <button type="button" class="remove-btn" onclick="removeItem(this)">Remove</button>
          </div>
        </div>
        <button type="button" onclick="addItem()">Add Item</button>
      </div>
      <div class="form-group">
        <input type="submit" value="Create Invoice">
      </div>
    </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;
          resultDiv.innerHTML = 'Existing subscriber found. Enter items and amounts.';
        } else {
          resultDiv.innerHTML = 'New subscriber. Enter name, items, and amounts.';
        }
      }

      function addItem() {
        const container = document.getElementById('itemsContainer');
        const itemRow = document.createElement('div');
        itemRow.className = 'item-row';
        itemRow.innerHTML = `
          <input type="text" name="item" placeholder="Item (e.g., Daily)" required>
          <input type="number" name="amount" min="0" step="0.01" placeholder="Amount" required>
          <button type="button" class="remove-btn" onclick="removeItem(this)">Remove</button>
        `;
        container.appendChild(itemRow);
      }

      function removeItem(button) {
        const container = document.getElementById('itemsContainer');
        if (container.children.length > 1) {
          button.parentElement.remove();
        }
      }

      document.getElementById('invoiceForm').addEventListener('submit', function(e) {
        e.preventDefault();
        const items = [];
        const itemRows = document.querySelectorAll('.item-row');
        itemRows.forEach(row => {
          const item = row.querySelector('input[name="item"]').value;
          const amount = row.querySelector('input[name="amount"]').value;
          items.push({ item, amount });
        });
        const formData = {
          contactNumber: this.contactNumber.value,
          name: this.name.value,
          date: this.date.value,
          items: JSON.stringify(items)
        };
        google.script.run
          .withSuccessHandler(function(result) {
            document.getElementById('result').innerHTML = result.message;
            document.getElementById('error').innerHTML = '';
            if (result.status === 'success') {
              document.getElementById('invoiceForm').reset();
              document.getElementById('itemsContainer').innerHTML = `
                <div class="item-row">
                  <input type="text" name="item" placeholder="Item (e.g., Daily)" required>
                  <input type="number" name="amount" min="0" step="0.01" placeholder="Amount" required>
                  <button type="button" class="remove-btn" onclick="removeItem(this)">Remove</button>
                </div>
              `;
              setTimeout(function() { google.script.host.close(); }, 1000);
            }
          })
          .withFailureHandler(showError)
          .processInvoiceForm(formData);
      });

      function showError(error) {
        document.getElementById('error').innerHTML = 'Error: ' + error.message;
        document.getElementById('result').innerHTML = '';
      }
    </script>
  </body>
</html>

How to Use the Script

Follow these steps to implement the script in your Google Sheets:

Open Your Google Sheet: Create or open a Google Sheet for your newspaper agency.
Access Apps Script:
- Click Extensions > Apps Script in the Google Sheets menu.
- This opens the Apps Script editor in a new tab.
Paste the Script and HTML Files:
- Click the “Copy Main Script” button and paste into a new file named Code.gs.
- Click the “Copy Subscriber Form” button and paste into a new HTML file named SubscriberForm.html.
- Click the “Copy Invoice Form” button and paste into a new HTML file named InvoiceForm.html.
- Save all files by clicking the save icon or File > Save all.
Update Folder ID:
- In the backupSheet function, replace YOUR_FOLDER_ID with the ID of a Google Drive folder where backups will be stored. (Find the folder ID in the folder’s URL: https://drive.google.com/drive/folders/[FOLDER_ID])
Run the Script:
- Reload your Google Sheet to see the “Subscription Management” menu.
- The first time you run any function (e.g., “Add/Update Subscriber (Form)”), Google will prompt you to authorize the script. Review and grant permissions.
Use the Menu Options:
- Add/Update Subscriber (Form): Opens a form to add or update a subscriber’s details (contact number, name, address, subscription type).
- Add/Update Subscriber (Prompt): Uses prompts to enter subscriber details.
- Create Invoice (Form): Opens a form to create an invoice with multiple items (e.g., “Daily:100,Weekly:60”).
- Create Invoice (Prompt): Uses prompts to create an invoice.
- Check Subscriptions: View a subscriber’s details by contact number.
- Record Payment: Log a payment for a subscriber’s subscription or item.
- Export Invoice PDF: Generate a PDF for a specific invoice and email it to the agency’s email.
- Generate All Pending Invoice PDFs: Create PDFs for all pending invoices and email them.
- Create Summary Report: Generate a summary of total invoiced amounts and payments.
- Backup Sheet: Save a backup of the spreadsheet to Google Drive.
Review Results:
- Success messages confirm actions (e.g., “Subscriber added,” “Invoice created”).
- Error messages appear for issues like invalid contact numbers or missing data.

Example Scenarios

Add a New Subscriber:
- Menu: Select “Add/Update Subscriber (Form)”.
- Input: Enter contact number (e.g., 1234567890), name (e.g., “John Doe”), address (e.g., “123 Main St”), and subscription type (e.g., “Daily”).
- Result: Adds a new subscriber to the Subscribers sheet.
Create an Invoice:
- Menu: Select “Create Invoice (Form)”.
- Input: Enter contact number (e.g., 1234567890), name (auto-filled if subscriber exists), date (e.g., 2025-08-07), and items (e.g., “Daily:100,New Year Calendar:50”).
- Result: Creates an invoice in the Invoices sheet and a dedicated Invoice_INV001 sheet.
Record a Payment:
- Menu: Select “Record Payment”.
- Input: Enter contact number (e.g., 1234567890), subscription type (e.g., “Daily”), amount (e.g., 100), date (e.g., 2025-08-07), and optional notes.
- Result: Logs the payment in the Payments sheet and updates the subscriber’s last payment date.
Export Invoice PDF:
- Menu: Select “Export Invoice PDF”.
- Input: Enter invoice ID (e.g., INV001).
- Result: Saves a PDF to the “AppleInvoiceFolder” in Google Drive and emails it to the agency’s email.

Tips and Notes

Contact Numbers: Must be 10 digits (e.g., 1234567890) for validation.
Subscription Types: Use “Daily,” “Weekly,” “Monthly,” or “New Year Calendar” in forms, or any custom type in prompts.
Forms vs. Prompts: Forms (e.g., “Add/Update Subscriber (Form)”) are more user-friendly with dropdowns and pre-filled fields; prompts are simpler but require manual input.
PDF Emails: PDFs are emailed to the active user’s email. Add an email column to the Subscribers sheet for direct customer emails (requires script modification).
Folder ID: Replace YOUR_FOLDER_ID in backupSheet with a valid Google Drive folder ID to avoid errors.
Performance: Large subscriber lists or multiple invoices may take a few seconds to process.
Error Handling: The script provides clear error messages for invalid inputs (e.g., wrong contact number, missing name).

Why Use This Script?

This script is a must-have for newspaper agencies or subscription-based businesses because it:

Saves Time: Automates subscriber management, invoice creation, and payment tracking.
Reduces Errors: Validates inputs like contact numbers and dates, with user-friendly forms.
Streamlines Billing: Generates professional invoice PDFs and emails them to the agency.
Tracks Finances: Provides a summary report of invoices and payments for easy accounting.
Ensures Data Safety: Backs up your spreadsheet to Google Drive.

It’s perfect for small to medium-sized newspaper agencies looking to manage subscribers efficiently without complex software.

Customize It!

Want to enhance the script? Here are some ideas:

Add Email Column: Modify the Subscribers sheet to include emails and update sendDueReminders to email subscribers directly.
Custom Invoice Templates: Add your agency’s logo or branding to the invoice sheets created by createInvoiceSheet.
Automated Reminders: Set up a time-driven trigger to run sendDueReminders daily for pending invoices.
Subscription Expiry Alerts: Add a function to check Last Payment Date and alert for expiring subscriptions.

Drop a comment below if you need help customizing this script!

Try It Out!

Ready to transform your newspaper agency’s workflow? Use the copy buttons above to add the script and HTML files to your Google Sheets, test it with a few subscribers, and see how it simplifies your operations. Have questions or run into issues? Leave a comment, and I’ll help you out. Happy automating!

Posted on August 7, 2025

Webtoolhelp

Thursday, August 7, 2025

NewsPaperAgency