FOLLOWUP Component

Function sendReminder

sendReminder(pkey)

The function sends reminder to focal point

Arguments

  • pkey (Object) – the list of key properties and their values

Returns

keyinputs – list of key parameters.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
function sendReminder(pkey) {
    if (pkey != null) { key = pkey };
    getListEmail(key);
    var today = new Date();
    var temp = getData("CONTROL");
    var followup = ss.getSheetByName("FOLLOWUP");
    var data = followup.getRange(3, 2, followup.getLastRow(), 8).getValues(); //getRange(row, column, numRows, numColumns)
    var period = followup.getRange(1, 6).getValue();

    var paramater = ss.getSheetByName("PARAMATERS");
    var emails = paramater.getRange(2, 1).getValue(); //Administrator email
    var contactemail = emails.splitCells(",");
    var contactemail2 = "";

    if (contactemail.length > 1) {
        contactemail2 = contactemail[0] + "; " + contactemail[1]
    } else { contactemail2 = contactemail[0] }

    try {
        for (var i3 = 1; i3 < data.length; i3++) {
            if (data[i3][data[0].indexOf("Template ID")] == "") { continue; }
            var lrd = "LASTREM" + data[i3][data[0].indexOf("Template ID")];
            var x = key[lrd];


            if (key[lrd] == "" || key[lrd] == null || parseFloat(key[lrd]) + period < today.valueOf()) {
                for (var ii = 2; ii < temp.length; ii++) {
                    var subject = "";
                    var message;
                    var type = 0;

                    if (data[i3][data[0].indexOf("Template ID")] != temp[ii][temp[0].indexOf("_id")]) { continue; }

                    /*NOT READY*/
                     if (temp[ii][temp[0].indexOf("USER_ACCESS")].indexOf("OK") == -1) { continue; }

                    /*NO information on PLAN*/
                    var bdate = "BDATE" + temp[ii][temp[0].indexOf("_id")];
                    var edate = "EDATE" + temp[ii][temp[0].indexOf("_id")];
                    var dear = "DEAR" + temp[ii][temp[0].indexOf("_id")];
                    var url = "FORMURL" + temp[ii][temp[0].indexOf("_id")];

                    var year = +key[bdate].substring(0, 4)
                    var month = +key[bdate].substring(5, 7)
                    var day = +key[bdate].substring(8, 10)
                    var bdate2 = new Date(year, month - 1, parseInt(day))

                    var year = +key[edate].substring(0, 4)
                    var month = +key[edate].substring(5, 7)
                    var day = +key[edate].substring(8, 10)
                    var edate2 = new Date(year, month - 1, parseInt(day))

                    Logger.log(bdate2.valueOf());

                   

                    var yearlimit = temp[ii][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")];
                    var earlydate = new Date(yearlimit, 0, 1)
                    var latedate = new Date(yearlimit + 1, 5, 30)

  • Case ‘UPDATE REQUIRED - YOUR BENEFICIARY SURVEY PLAN - ID’

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
                    if (key[bdate] == null || key[bdate] == "" || key[edate] == null || key[edate] == "" || edate2.valueOf() < bdate2.valueOf() || bdate2.valueOf() < earlydate.valueOf() || edate2.valueOf() > latedate.valueOf()) {
                        type = 1;
                        subject = "UPDATE REQUIRED - YOUR BENEFICIARY SURVEY PLAN - ID: " + temp[ii][temp[0].indexOf("_id")] + " ( " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/Country")] + " - " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/D_Station")] + " )";
                        message = "<span style='color:black; font-size:120%;font-family:Roboto'>Dear " + key[dear] + "<br><br>";
                        message = message + "This message is to inform you that your implementation plan for the " + temp[ii][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] + " UNHCR " + key["SECTOR"] + " Beneficiary Surveys ( " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/Country")] + " - " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/D_Station")] + " ) needs to be reviewed either because:<br><br>";
                        message = message + "&nbsp&nbsp● The plan has not yet been registered in our system, or <br><br>&nbsp&nbsp● The endline survey date is set before baseline survey date, or <br><br>&nbsp&nbsp● The baseline survey date is set too early (before 1st January) or the endline survey is set too late (after June of the following year).<br><br>";
                        message = message + "Please kindly update your survey implementation plan using the following link.<br><br>";
                        message = message + key[url] + "<br><br>";
                        message = message + "Your template id is " + temp[ii][temp[0].indexOf("_id")] + ". Please use this information to submit your plan.<br><br>";
                        message = message + "Please note that this reminder is sent on weekly basis when the survey is due. Please do not reply as it is an automated email. In case you have any questions, or believe that you have received this message in error, please kindly contact HQ support team ( " + contactemail2 + " ).<br><br>";
                        message = message + "Best regards,<br><br>" + key["SECTOR"] + " Monitoring System</span>"
                    }

  • Case ‘AUTO-REMINDER : YOUR BENEFICIARY BASELINE SURVE IS DUE - ID’

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
                    else if (bdate2.valueOf() < today.valueOf() && temp[ii][temp[0].indexOf("Baseline")] == "" && temp[ii][temp[0].indexOf("_id")] != "") {
                        type = 2;
                        subject = "AUTO-REMINDER : YOUR BENEFICIARY BASELINE SURVE IS DUE - ID: " + temp[ii][temp[0].indexOf("_id")] + " ( " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/Country")] + " - " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/D_Station")] + " )";;
                        message = "<span style='color:black; font-size:120%;font-family:Roboto'>Dear " + key[dear] + "<br><br>";
                        message = message + "Please note that the anticipated date for submitting your " + temp[ii][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] + " UNHCR " + key["SECTOR"] + " Beneficiary <i>BASELINE Survey</i> ( " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/Country")] + " - " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/D_Station")] + " ) has passed, and we have not yet recieved data from your operation.<br><br>";
                        message = message + "Kindly inform us of any changes to your implementation plan using the following link:<br><br>";
                        message = message + key[url] + "<br><br>";
                        message = message + "Your template id is " + temp[ii][temp[0].indexOf("_id")] + ". Please use this information to submit your plan.<br><br>";
                        message = message + "Note that this reminder is sent on weekly basis when the survey is due. Please do not reply as it is an automated email. In case you have any questions, or believe that you have received this message in error, please kindly contact HQ support team ( " + contactemail2 + " ).<br><br>";
                        message = message + "Best regards,<br><br>" + key["SECTOR"] + " Monitoring System</span>"
                    }

  • Case ‘AUTO-REMINDER : YOUR BENEFICIARY ENDLINE SURVEY IS DUE - ID’

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
                    }
                    else if (edate2.valueOf() < today.valueOf() && temp[ii][temp[0].indexOf("Endline")] == "" && temp[ii][temp[0].indexOf("_id")] != "") {
                        type = 3;
                        subject = "AUTO-REMINDER : YOUR BENEFICIARY ENDLINE SURVEY IS DUE - ID: " + temp[ii][temp[0].indexOf("_id")] + " ( " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/Country")] + " - " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/D_Station")] + " )";
                        message = "<span style='color:black; font-size:120%;font-family:Roboto'>Dear " + key[dear] + "<br><br>";
                        message = message + "Please note that the anticipated date for submitting your " + temp[ii][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] + " UNHCR " + key["SECTOR"] + " Beneficiary <i>ENDLINE Survey</i> ( " + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/Country")] + " -" + temp[ii][temp[0].indexOf("CONTACT_INFORMATION/D_Station")] + " ) has passed, and we have not yet recieved data from your operation.<br><br>";
                        message = message + "Kindly inform us of any changes to your implementation plan using the following link:<br><br>";
                        message = message + key[url] + "<br><br>";
                        message = message + "Your template id is " + temp[ii][temp[0].indexOf("_id")] + ". Please use this information to submit your plan.<br><br>";
                        message = message + "Note that this reminder is sent on weekly basis when the survey is due. Please do not reply as it is an automated email. In case you have any questions, or believe that you have received this message in error, please kindly contact HQ support team ( " + contactemail2 + " ).<br><br>";
                        message = message + "Best regards,<br><br>" + key["SECTOR"] + " Monitoring System</span>"
                    }

Function getListEmail

getListEmail(pkey)

The function gets the list of emails and checkes existing submissions for Baseline and Endline

Arguments

  • pkey (Object) – the list of key properties and their values

Returns

keyinputs – list of key parameters.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
function getListEmail(pkey) {
    key = pkey;
    var temp = getData("CONTROL");
    var followup = ss.getSheetByName("FOLLOWUP");
    var year = followup.getRange(1, 3).getValue();
    var newdata = [];
    var y = 0;

    var form = FormApp.openById(key["SURVEYPLANFORMID"]);
    var url = form.getPublishedUrl() + "?entry.1035836465=";

    /*CHECK THE EXISTING SUBMISSIONS*/
    var formresponses = form.getResponses();

    followup.getRange(4, 1, followup.getLastRow(), 10).clear();
    for (var i = 2; i < temp.length; i++) {
        if (temp[i][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] < year) { continue; }

        newdata[y] = new Array();
        newdata[y][0] = temp[i][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")];
        newdata[y][1] = temp[i][temp[0].indexOf("_id")];
        newdata[y][2] = temp[i][temp[0].indexOf("CONTACT_INFORMATION/Country")];
        newdata[y][3] = temp[i][temp[0].indexOf("CONTACT_INFORMATION/D_Station")];

        var ck1 = "DEAR" + temp[i][temp[0].indexOf("_id")];
        if (key[ck1] == null) { newdata[y][4] = temp[i][temp[0].indexOf("CONTACT_INFORMATION/F_Point")]; keyinputs[ck1] = newdata[y][4];} else { newdata[y][4] = key[ck1]; }
        var ck2 = "ETO" + temp[i][temp[0].indexOf("_id")];
         if (key[ck2] == null) { newdata[y][5] = temp[i][temp[0].indexOf("CONTACT_INFORMATION/Email_FP")]; keyinputs[ck2] = newdata[y][5]; } else { newdata[y][5] = key[ck2]; }

        var ck3 = "ECC" + temp[i][temp[0].indexOf("_id")];
        if (key[ck3] != null) { newdata[y][6] = key[ck3]; } else { newdata[y][6] = ""; }


        /*CHECK EXISTING SUBMISSIONS*/

        var ck4 = "FORMURL" + temp[i][temp[0].indexOf("_id")];
        var ck5 = "BDATE" + temp[i][temp[0].indexOf("_id")];
        var ck6 = "EDATE" + temp[i][temp[0].indexOf("_id")]

        for (var i2 = 0; i2 < formresponses.length; i2++) {
            var itemResponses = formresponses[i2].getItemResponses();
            if (itemResponses[2].getResponse() != temp[i][temp[0].indexOf("_id")]) { continue; }
            var urltemp = formresponses[i2].getEditResponseUrl();

            keyinputs[ck4] = urltemp;
            keyinputs[ck5] = itemResponses[0].getResponse();
            keyinputs[ck6] = itemResponses[1].getResponse();
            newdata[y][7] = urltemp;
            break;
        }

        if (keyinputs[ck4] == null) {
            keyinputs[ck4] = url + temp[i][temp[0].indexOf("_id")];
            keyinputs[ck5] = "";
            keyinputs[ck6] = "";
            newdata[y][7] = url + temp[i][temp[0].indexOf("_id")];
        }
        y++;
    }

    if (newdata.length > 0) { followup.getRange(4, 2, newdata.length, newdata[0].length).setValues(newdata); }
    if (pkey != null) { return (keyinputs); }
}

Function updateEmailAddress

updateEmailAddress(pkey)

The function updates email addresses

Arguments

  • pkey (Object) – the list of key properties and their values

Returns

keyinputs – list of key parameters.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
function updateEmailAddress(pkey) {
    key = pkey;
    var followup = ss.getSheetByName("FOLLOWUP");
    var temp = followup.getRange(3, 2, followup.getLastRow(), 8).getValues();
 
    for (i = 1; i < temp.length; i++) {
        var ck1 = "DEAR" + temp[i][temp[0].indexOf("Template ID")];
        keyinputs[ck1] = temp[i][temp[0].indexOf("Dear Name")];

        var ck2 = "ETO" + temp[i][temp[0].indexOf("Template ID")];
        keyinputs[ck2] = temp[i][temp[0].indexOf("TO:")];

        var ck3 = "ECC" + temp[i][temp[0].indexOf("Template ID")];
        keyinputs[ck3] = temp[i][temp[0].indexOf("CC1:")];

        var ck4 = "FORMURL" + temp[i][temp[0].indexOf("Template ID")];
        keyinputs[ck4] = temp[i][temp[0].indexOf("Formlink")];
    }

    if (pkey != null) { return (keyinputs); }
}