Membership subscription application assistance to duplicate it

Skip to first unread message

Niraj Bachheta

Sep 11, 2020, 4:28:50 AM9/11/20
to Google Apps Script Community
Dear Community,
I am an enthusiastic IT individual working on improving my coding experience.
I am building my learning and watched a video on a membership subscription application developed on google sheet. the link is here Link.

I managed to watch it and create my own with an example of a school changing from subscription to accreditation.
while working on it, a scenario played in my mind if a school is accredited on 2 separate dates, how can this be managed in the app?

I would like some assistance to help me achieve this because i have tried and tried but have failed.
I managed to make it work for accreditation 1 but couldn't reach for accreditation 2.
here is the link of the sheets file. HERE

so what I would like to achieve is that if it is 3 days before the accredited one then an email will send. (the current codes work for this aspect) but now also at the same time if its not accredited one then it will check accredited two and send an email.
the current codes are below
// This function reads the input from associated Google sheet and sends subscription renewal email notification
// in advance based on the request in sheet.
function sendReminders() {
    // Read the values from tracking sheet
    const trackingsheet = SpreadsheetApp.getActive().getSheetByName("Accreditation")
    const data = trackingsheet.getRange(2, 1, trackingsheet.getLastRow() - 1, 7).getValues()
    // Loop through all rows
    data.forEach(function(row) {
        const country = row[0]
        const school = row[1]
        const specialtyone = row[2]
        const acconeon = row[3]
        const ExpirationDate = row[4]
        const reminderone = row[5]
        const commentsone = row[6]
        const specialtytwo = row[7]
        const acctwoon = row[8]
        const ExpirationDateTwo = row[9]
        const remindertwo = row[10]
        const commentstwo = row[11]
        const email = row[12]
        const daysLeft = getDate(ExpirationDate)
        // Parse active user (who is running this sheet script) email address
        // If you want to rather send email to some other email id (e.g directly to your customer)
        // then you can add a column between F and G and name this column whatever you want (e.g Customer Email to notify)
        // then in below recipientEmail variable, replace Session.getActiveUser().getEmail() with row[6]
        // and change number 6 to 7 in line # 20 above so script can read your 7th column
        const recipientEmail = row[12]

        // Send first email to the user if the subscription renewal date is equal to the advance notification date provided by the user
        if (daysLeft == reminderone) {
            const emailSubject = `NOTICE Expiring Accreditation for ${school}`;
            const emailBody =
                `To whom it may concern,
Please note, the accreditation for ${school} in ${country} is expiring on ${ExpirationDate}. It was accredited for ${specialtyone}.
Kindly take this time to renew the accreditation by visiting the website through the link below.
Note : for any inquiries/assistance, kindly contact us.
Thank You.
            MailApp.sendEmail(recipientEmail, emailSubject, emailBody)

// This function takes a date and return days difference between today's date and provided date
function getDate(providedDate) {
    var todaysDate = new Date();
    var creationDate = new Date(providedDate);
    const diffTime = Math.abs(creationDate - todaysDate);
    const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
    return diffDays

I hope I explained it well for some assistance to achieve this.
my humble appreciations for the assistance provided
Reply all
Reply to author
0 new messages