Title: Automating Subscription Management with Google Apps Script: Part 1

Checking Monthly Subscription Status Using Google Apps Script

Managing monthly subscriptions can be a challenging task for associations, but with the power of Google Apps Script, you can automate the process. In this blog post, we'll guide you through a script that checks the subscription status of association members based on their phone numbers and the range of months specified. We'll also show how the script updates a receipt sheet with the relevant details

Step-by-Step Guide to the Script 

Spreadsheet Setup

our Google Sheets here will havethree main sheets:

Collection: Where the subscription data for various months is stored.

MembersRegister: Where member details such as name, phone number, and email are stored.

Receipt: Where you input the phone number and the range of months.

MembersRegister: Where member details such as name, phone number, and email are stored.
 

Here's what the Receipt sheet looks like:

Cell B2: Enter the phone number you want to check.

Cells A4 and A5: Enter the start and end months for the range you want to check (e.g., "Jan (2024)", "Mar (2024)").

Below is the script that will perform the subscription check:

Copy the Apps Script code below:

SubscriptionDone() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var receipt = ss.getSheetByName('Receipt'); receipt.getRange("C2:E25").clearContent(); // Clear columns C, D, and E var phoneNumberToCheck = receipt.getRange("B2").getValue(); if (!phoneNumberToCheck) { Browser.msgBox("Phone number was not provided in cell B2. Exiting."); return; } var startRange = receipt.getRange("A4").getValue(); var endRange = receipt.getRange("A5").getValue(); var collectionSheet = ss.getSheetByName('Collection'); var headers = collectionSheet.getRange("C1:N1").getValues()[0]; var startIndex = headers.indexOf(startRange); var endIndex = headers.indexOf(endRange); if (startIndex === -1 || endIndex === -1) { Browser.msgBox("Invalid start or end range. Exiting."); return; } var monthsCleared = []; var phoneNumberFound = false; for (var i = startIndex; i <= endIndex; i++) { var columnData = collectionSheet.getRange(2, i + 3, collectionSheet.getLastRow() - 1, 1).getValues(); for (var j = 0; j < columnData.length; j++) { if (columnData[j][0] == phoneNumberToCheck) { monthsCleared.push([headers[i]]); phoneNumberFound = true; break; } } } if (!phoneNumberFound) { Browser.msgBox("Subscription is not done for any month."); return; } var membersSheet = ss.getSheetByName('MembersRegister'); var nameColumn = membersSheet.getRange("D4:D" + membersSheet.getLastRow()).getValues(); var phoneNumberColumn = membersSheet.getRange("E4:E" + membersSheet.getLastRow()).getValues(); var emailColumn = membersSheet.getRange("F4:F" + membersSheet.getLastRow()).getValues(); var name = ""; var email = ""; for (var i = 0; i < phoneNumberColumn.length; i++) { if (phoneNumberColumn[i][0] == phoneNumberToCheck) { name = nameColumn[i][0]; email = emailColumn[i][0]; break; } } receipt.getRange("A2").setValue(name); receipt.getRange("E2").setValue(email); receipt.getRange("B2").setValue(phoneNumberToCheck); var resultMessage = "Dear Member " + name + " (" + phoneNumberToCheck + "), ASSICIATION acknowledge the receipt of Subscription from you for the month(s) of "; if (monthsCleared.length > 0) { receipt.getRange("C3:C" + (monthsCleared.length + 2)).setValues(monthsCleared); receipt.getRange("C2").setValue(resultMessage); } else { receipt.getRange("C2").setValue("No Months Cleared For This Member."); } Browser.msgBox('Function Executed', 'SubscriptionDone', Browser.Buttons.OK); }

How the Script Works

Clearing Previous Data: The script first clears the content of columns C, D, and E in the Receipt sheet to ensure there's no old data.

Checking for Phone Number: It retrieves the phone number from cell B2. If no phone number is provided, it exits.

Getting the Range of Months: It gets the start and end months from cells A4 and A5. These are used to determine the range of months to check.

Checking Subscription Status: The script checks each month in the specified range to see if the member's phone number appears, indicating that the subscription is cleared for that month.

Updating the Receipt: It updates the Receipt sheet with the member's name, phone number, email, and the months for which the subscription is cleared.

Displaying the Result: Finally, it displays a message box indicating whether the function executed successfully.

This Google Apps Script automates the process of checking subscription status and updating the receipt with relevant details, making it easier for association managers to keep track of member subscriptions. By following the steps outlined in this post, you can implement this script in your own Google Sheets and streamline your subscription management process.