Send Confirmation emails to emails using sendgrid

confirmation_email.gs

//This is the main function to run
function initScript() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("IOXKL");
    var data = sheet.getDataRange().getValues();
    
    //Your sheets should have the below as headers
    var emailIndex = searchHeaderIndex(data, "Email"); 
    var nameIndex = searchHeaderIndex(data, "Name");
    var statusIndex = searchHeaderIndex(data, "Status");

    var lastColumn = searchHeaderColumn(data, "last_column");
    Logger.log(lastColumn);
    if (statusIndex === -1) {
        sheet.getRange(searchHeaderIndex).setValue("Status");
        statusIndex = searchHeaderIndex(data, "Status");
    }
    var statusColumn = statusIndex + 1;
    var emails = [];
    var names = [];
    var sendgrid = new Sendgrid({
        user: "USER",
        key: "SENDGRIDKEY"
    });
    //gathers all the emails into an array and send them emails in one shot via sendgrid
    for (var i = 1; i < data.length; i++) {
        var email = data[i][emailIndex];
        var name = data[i][nameIndex];
        Logger.log('Name: ' + name);
        Logger.log('Email: ' + email);
        if (data[i][statusIndex].toLowerCase() === "pending") {
            //send email
            var quota = checkMailQuota();
            if (quota > 0) {
               // var emailStatus = sendEmail(email, name);
                var emailStatus = sendgrid.addTarget(name, email);
                emails.push(email);
                names.push(name);
                Logger.log('Column to write: ' + columnToLetter(statusColumn) + i);
                var cell = sheet.getRange(i + 1, statusColumn)
                cell.setValue(emailStatus);
            }
        }
    }
    sendgrid.send();
  
}

function sendEmail(emailAddress, name) {
    if (!validateEmail(emailAddress)) {
        Logger.log("invalid email " + emailAddress);
        return "INVALID_EMAIL";
    }
    var hash = hashMD5(emailAddress, "ioxkl2017-jt");
    Logger.log(hash);
    MailApp.sendEmail({
        to: emailAddress,
        subject: "Please send your confirmation email",
        htmlBody: "<h1>Testing email: Hello " + name + "</h1>" +
        "<p><a href='https://us-central1-ioxkl17.cloudfunctions.net/makeConfirmation?hash=" + hash + "&confirmation=confirm'>Confirm attendance</a></p>" +
        "<p><a href='https://us-central1-ioxkl17.cloudfunctions.net/makeConfirmation?hash=" + hash + "&confirmation=cancel'>Cancel attendance</a></p>"
    });
    sendEmail();
    return "SENT"
}

//for testing
function sendEmailWithSendgrid() {

    var to = "test@yahoo.com";
    var cc = "test@gmail.com";
    var bcc = "test@hotmail.com,test1@hotmail.com";
    var subject = "Please send your confirmation email";
    var body = "<h1>Testing email: Hello " + name + "</h1>" +
        "<p><a href='https://us-central1-ioxkl17.cloudfunctions.net/makeConfirmation?hash=" + hash + "&confirmation=confirm'>Confirm attendance</a></p>" +
        "<p><a href='https://us-central1-ioxkl17.cloudfunctions.net/makeConfirmation?hash=" + hash + "&confirmation=cancel'>Cancel attendance</a></p>";

    initSendGridEmail(to, cc, bcc, subject, body);

}

function initSendGridEmail(to, cc, bcc, subject, body) {
    var sendgrid = new Sendgrid({
        user: "YOUR_USER",
        key: "1212312cahuhoaeuheoauh"
    });

    var emailTo = to.split(',');
    // var emailCc = cc.split(',');
    body = encodeURIComponent(body);

    sendgrid.send({
        to: emailTo,
        // cc: emailCc,
        from: 'test@test.com',
        subject: subject,
        html: body,
        fromname: 'From Test Sendgrid'
    });
}

function checkMailQuota() {
    var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
    Logger.log("Remaining email quota: " + emailQuotaRemaining)
    return emailQuotaRemaining;
}

function searchHeaderColumn(data, columnName) {
    var index = searchHeaderIndex(data, columnName);
    if (index > -1) {
        return index + 1;
    }
    return index;
}

function searchHeaderIndex(data, columnName) {
    var headers = [];
    var i = 0;
    for (i; i < data[0].length; i++) {
        if (data[0][i] != null && columnName.toLowerCase() === data[0][i].toLowerCase()) {
            return i;
        }
    }
    if (columnName.toLowerCase() === "last_column") {
        return i + 1;
    }
    return -1;
}

function columnToLetter(column) {
    var temp, letter = '';
    while (column > 0) {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
    }
    return letter;
}

function letterToColumn(letter) {
    var column = 0, length = letter.length;
    for (var i = 0; i < length; i++) {
        column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
    }
    return column;
}

function validateEmail(email) {
    var emailPattern = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
    return emailPattern.test(email);
}

function hashMD5(email, salt) {
    return digest(Utilities.DigestAlgorithm.MD5, email.toLowerCase() + salt);
}

function digest(algorithm, aStr) {
    algorithm = algorithm || Utilities.DigestAlgorithm.MD5; // default MD5
    aStr = aStr || "";  // default to empty string
    var signature = Utilities.computeDigest(algorithm, aStr,
        Utilities.Charset.US_ASCII)
    //Logger.log(signature);
    var signatureStr = '';
    for (i = 0; i < signature.length; i++) {
        var byte = signature[i];
        if (byte < 0)
            byte += 256;
        var byteStr = byte.toString(16);
        // Ensure we have 2 chars in our byte, pad with 0
        if (byteStr.length == 1) byteStr = '0' + byteStr;
        signatureStr += byteStr;
    }
    //Logger.log(signatureStr);
    return signatureStr;
}

var Sendgrid = function (credentials) {
    var creds = this.creds = credentials || {};
    creds.user = creds.username || creds.user;

    if (!creds.user) {
        Logger.log('sendgrid-web requires a user.');
    }
    if (!creds.key) {
        Logger.log('sendgrid-web requires a key.');
    }
};

Sendgrid.prototype.addTarget = function(name, email){
    if (this.personalizations === undefined || this.personalizations == null){
        this.personalizations = [];
    }
    if (!validateEmail(email)) {
        Logger.log("invalid email " + email);
        return "INVALID_EMAIL";
    }
    var hash = hashMD5(email, "ioxkl2017-jt");
    Logger.log(hash);
    this.personalizations.push({
        to: [{
            "email": email
        }],
        "subject": "REMINDER - IOXKL17 Confirmation Email",
        "substitutions": {
            "-name-": name,
            "-hash-": hash
        }
    })
    return "SENT";
    Logger.log(JSON.stringify(this.personalizations));
};

Sendgrid.prototype.send = function () {
    var SENDGRID_KEY = this.creds.key;
    var headers = {
        "Authorization" : "Bearer "+SENDGRID_KEY,
        "Content-Type": "application/json"
    };

    var body =
        {
            "personalizations": this.personalizations,
            "from": {
                "email": "jt@gdg.my",
                "name": "Google Developer Groups Kuala Lumpur"
            },
            "content": [{
                "type": "text/html",
                "value": "IOXKL17 Confirmation Reminder"
            }],
            "template_id": "1112121-11212121-1212121blah"
        };

    var options = {
        'method':'post',
        'headers':headers,
        'payload':JSON.stringify(body)
    };
    var response = UrlFetchApp.fetch("https://api.sendgrid.com/v3/mail/send",options);
    Logger.log(response);
};