DATA ANALYSIS Component [Energy]

Note

Some functions are the same as for Livelihoods Module

Warning

Some function are the same as for Livelihoods Module and no longer used

Function extractAnalysisNote_energy

  • Locate the file in the folder, and if it does not exist create new one. Set the idx address (datasheet id) so that the code in different functions can work on the correct DataSheet through global variables.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
function extractAnalysisNote_energy(year, fcountry, fusername, fdutystation, formid, pkey) 
{
    if (pkey != null) { key = pkey };
    if (year < 2017 || key["COUNTRYNOTE" + formid] == "OK") { 
        /*return status 'CANCELED' on Task sheet*/   
        return 3;
    }
    if (key["CLEAN" + formid] == 0) { keyinputs["COUNTRYNOTE" + formid] = "OK"; key["COUNTRYNOTE" + formid] = "OK"; return 3; }
    /*mainfolderid {string}  - id of main folder */
    var mainfolderid = createFolder(0, key["MAINFOLDER"]);
    /*YearFolder {string} - id of ALLFILES folder for specific year*/
    YearFolder = createFolder(mainfolderid, "ALLFILES" + year);
    /*ANALYSISFOLDER {string} - id of COUNTRY_ANALYSIS folder for specific year*/
    var ANALYSISFOLDER = createFolder(YearFolder, "COUNTRY_ANALYSIS" + year);

    /*Generating the name of the file Country > Place*/
    if (fdutystation != null) { var fname = year + " " + fcountry + "_" + fdutystation + " Country Analysis Note"; }
    else { var fname = year + " " + fcountry + " Country Analysis Note"; }

    var fname2 = year + " " + fcountry + " ANALYSIS Energy Beneficiary Survey";

    /*check for file existence*/
    /*idSS {string} - id of the file (fname2) in ANALYSIS folder [General Component]*/
    var idSS = getFile(ANALYSISFOLDER, fname2);

  • Schedule CREATEANALYSISHEET or ANALYSISNOTEEXTRACT Task

This is the image caption

Example of CREATEANALYSISHEET or ANALYSISNOTEEXTRACT Task

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    /*IF file doesn't exist, schedules the creating one and repeats the task */
    if (idSS == null) {
         /*tdata {array} -  data from CONTROL sheet (GENERAL component)*/
        var tdata = getData("CONTROL");
        for (it = 0; it < tdata.length; it++) {
            if (tdata[it][tdata[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] == year) {
                tempid = tdata[it][tdata[0].indexOf("formid")];
                SCHEDULE("CREATEANALYSISSHEET", fyear, bdata[i][bdata[0].indexOf("formid")], tempid, fcountry);
                break;
            }
            SCHEDULE("ANALYSISNOTEEXTRACT", year, fcountry, fusername, fdutystation, formid);
            return;
        }
    }

  • Add Fllter sheet in Beneficiary data sheet [ALLFILESYear >COUNTRY_ANALYSISYEAR>Year Country Analysis Energy Beneficiary Survey]

 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
   /*Add filters in new data analysis sheet*/
    var LU = parseFloat(DriveApp.getFileById(idSS).getLastUpdated().getTime());
    /*anss {object} - spreadsheet object with the given id [Opens the spreadsheet with the given ID]*/
    var anss = SpreadsheetApp.openById(idSS);
     /*para {Object}  - get the FILTER sheet */
    var para = anss.getSheetByName("filter");

    var data = para.getRange(1, 1, para.getLastRow() + 1, para.getLastColumn() + 1).getValues();
    for (i = 0; i < data.length; i++) {
        if (data[i][1] == "COUNTRY:") {
            data[i][2] = fcountry;
        }
        if (data[i][4] == "USERNAME:") {
            if (fusername == null) {
                data[i][5] = "";
            } else {
                data[i][5] = fusername;
            }
        }
        if (data[i][4] == "Duty Station:") {
            if (fdutystation == null) {
                data[i][5] = "";
            } else {
                data[i][5] = fdutystation;
            }
        }
        if (data[i][1] == "PARTNER:") {
            data[i][2] = "ALL";
        }
        if (data[i][1] == "CAMP:") {
            data[i][2] = "ALL";
        }
        if (data[i][1] == "STATUS:") {
            data[i][2] = "ALL";
        }
        if (data[i][1] == "GENDER:") {
            data[i][2] = "ALL";
        }
        if (data[i][1] == "AGE:") {
            data[i][2] = "ALL";
        }
        if (data[i][1] == "YEAR:") {
            data[i][2] = year;
        }
    }
    para.getRange(1, 1, para.getLastRow() + 1, para.getLastColumn() + 1).setValues(data);

  • Read Partner Survey Data

This is the image caption

Screenshot of Partner data

 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
    /*read paartner survey data*/
    var urlp = key["CSVURL5" + year];
    var idSSp = urlp.replace('https://docs.google.com/spreadsheets/d/', '');
    idSSp = idSSp.replace('/edit?usp=drivesdk', '');
    /*ssp {object} - spreadsheet object with the given id [Opens the spreadsheet with the given ID]*/
    var ssp = SpreadsheetApp.openById(idSSp);
    /*alldatap {Object}  - get the ALLDATA sheet [Partner Survey] */
    var alldatap = ssp.getSheetByName("ALLDATA");
    var pexs = ssp.getSheetByName("ExchangeRate");
    var jP = alldatap.getDataRange().getValues();
    var c1 = jP[0].indexOf("TemplateID");
    var pex = pexs.getDataRange().getValues();

    /*Filter only the template ID matches*/
    jsonPartner = jP.filter(function (dataRow) {
        var x = 1;
        if (dataRow[0] == "today") { x = 0; }
        if (dataRow[c1] == formid) { x = 0; }
        return x === 0;
    });

    /*Get exchange rate*/
    if (jsonPartner.length > 1) {

        for (p = 0; p < pex.length; p++) {
            if (pex[p][0] == jsonPartner[1][jsonPartner[0].indexOf("PARTNER_INFO/LocalCurrency2")]) {
                pexr = pex[p][1];
            }
        }
    }

     /*partner name label*/
    var nlist = [];
    partnernamesp = "(";
    for (pu = 1; pu < jsonPartner.length; pu++) {
        if (nlist.indexOf(jsonPartner[pu][jsonPartner[0].indexOf("PARTNER_INFO/PARTNER")]) == -1) {
            nlist.push(jsonPartner[pu][jsonPartner[0].indexOf("PARTNER_INFO/PARTNER")])
            partnernamesp = partnernamesp.replace("#", "");
            partnernamesp = partnernamesp + jsonPartner[pu][jsonPartner[0].indexOf("PARTNER_INFO/PARTNER")] + ",# ";
        }
    }
    partnernamesp = partnernamesp.replace(",# ", "");
    partnernamesp = partnernamesp + ")";

    keyinputs["idx"] = idSS;
     /*global valiable controls the working analysis sheet across the years*/
    key["idx"] = idSS;

 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
64
65
66
67
68
69
70
71
72
73
74
   /*Function extract country analysis note on country_analysis_note sheet [Data Analysis Sheet]*/
    extractCountryAnalysisNote();

    /*function creates a new file (folderid,fileName) [GENERAL component]*/
    var x = createFile(ANALYSISFOLDER, fname);
    
    var oldfile = DriveApp.getFileById(x);
    /*old {object} - spreadsheet object with the given id [Opens the spreadsheet with the given ID]*/
    var old = SpreadsheetApp.openById(x);
    var oldsheet = old.getSheetByName(fname);
    try {
        var pdfpagebreak = oldsheet.getRange(1, 1, oldsheet.getLastRow(), 1).getValues();
        sumsheet.getRange(1, 1, oldsheet.getLastRow(), 1).setValues(pdfpagebreak);
    } catch (e) { }
    oldfile.setTrashed(true);

    /*function creates a new file (folderid,fileName) [GENERAL component]*/
    var templatefile = createFile(key["OTHERFOLDERID"], "AnalysisCode");
    var folder = DriveApp.getFolderById(ANALYSISFOLDER);
    var file = DriveApp.getFileById(templatefile);
    file.makeCopy(fname, folder);

    /*Obtaining ID of the file*/
    var files = folder.getFiles();
    while (files.hasNext()) {
        var file2 = files.next();
        if (file2.getName() == fname) {
            var newfile = file2.getId();
        }
    }

     /*ss_copyTo {object} - spreadsheet object with the given id [Opens the spreadsheet with the given ID]*/
    var ss_copyTo = SpreadsheetApp.openById(newfile);
    var sheet1 = ss_copyTo.getSheetByName('Sheet1');

     /*countryanalysisreport {Object}  - get the country analysis _Public sheet */
    var countryanalysisreport = ss.getSheetByName("COUNTRY_ANALYSIS_NOTE");
    try {
        countryanalysisreport.copyTo(ss_copyTo).setName(fname);
    } catch (e) {
        var oldfile = DriveApp.getFileById(createFile(ANALYSISFOLDER, fname));
        oldfile.setTrashed(true);
        countryanalysisreport.copyTo(ss_copyTo).setName(fname);
    }
    ss_copyTo.deleteSheet(sheet1);

    /*Registring PDF file link to Keys*/
    var newurl = DriveApp.getFileById(newfile).getUrl();
    var cn = 'CNOTE' + formid;
    keyinputs[cn] = newurl; key[cn] = newurl;
    keyinputs["COUNTRYNOTE" + formid] = "OK"; key["COUNTRYNOTE" + formid] = "OK";

    updateOnCustomisation(formid);

   /*EMAILNOTICE*/
    if (key["EMAIL"] == "ON") {
        generateEmailNotice(formid,
            "Country Analysis Note Created / Updated",
            "Country Analysis Note has been created or updated based on the latest data.",
            newfile,
            "Please find below the link to the latest Country Analysis Note based on the latest data submitted. Should you have any comments/questions, please do not hesitate to contact the HQ focal point.");
    }
 
    /*Update LASTUPDATE KEY OF THE SHEET*/
    if (LU - parseFloat(key["LASTUPDATE" + formid]) < 90 * 1000) {
        var X = DriveApp.getFileById(idSS).getLastUpdated().getTime();
        keyinputs["LASTUPDATE" + formid] = X; key["LASTUPDATE" + formid] = X;
    }


    updateLog("County Analysis Note updated: " + year + " " + fname);
    if (pkey != null) { return (keyinputs); }
    return 1;
}

General Variables [Energy]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
{ //General settings
    var sumsheet;
    var graphcolor;
    var titlecolor;
    var XX = 1,
        YY = 1;
    var y = 26,
        x = 2;
    var y2,
        x2;
    var Country2;

}

DataLoad Variables [Energy]

1
2
3
4
5
6
7
8
9
{ //DataLoad
    var APIDATA = key["APITOKEN2"];
    var APITEMPLATE = key["APITOKEN"]
    var jsonData;
    var jsonTemplate;

    var jsonPartner;
    var pexr;
    var partnernamesp;

Function verifyKPTEnergy

verifyKPTEnergy(EOF, formdataid)

The function checks KPT Day 1 and Day 2 ID numnber, and trasnfer KPT Day 1 data to KPT Day 2 data.

Arguments

  • EOF (string) – ENDLINE ON/OFF

  • formdataid (string) – dataid

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
    function verifyKPTEnergy(EOF,formdataid) {
        var idx = key["idx"];
        var sst = SpreadsheetApp.openById(idx);
        var alldatasheet = sst.getSheetByName("ALLDATA");
        var LRD = alldatasheet.getLastRow();
        var data = alldatasheet.getRange(1, 1, LRD, alldatasheet.getLastColumn()).getValues();

        var cbe = data[0].indexOf("PARTNER_INFO/BE");

        var c0 = data[0].indexOf("DATA_CLEANED");
        var c1 = data[0].indexOf("PARTNER_INFO/Output");
        var c2 = data[0].indexOf("PARTNER_INFO/Output2");
        var c3 = data[0].indexOf("O1_KPT_D1/O1_KPT_NO/RANDOMID");
        var c4 = data[0].indexOf("KPT");
        var c5 = data[0].indexOf("PARTNER_INFO/KPTD2_ID");

        var c6 = data[0].indexOf("Wood");
        var c7 = data[0].indexOf("Charcoal");
        var c8 = data[0].indexOf("Liquid");
        var c9 = data[0].indexOf("Gas");
        var c10 = data[0].indexOf("Other");
        var c11 = data[0].indexOf("KPT1OK");

        var c66 = data[0].indexOf("O1_KPT_D1/O1_KPT_D1_Wood_KG");
        var c77 = data[0].indexOf("O1_KPT_D1/O1_KPT_D1_Charcoal_KG");
        var c88 = data[0].indexOf("O1_KPT_D1/O1_KPT_D1_Kerosene_KG");
        var c99 = data[0].indexOf("O1_KPT_D1/O1_KPT_D1_Gas_KG");
        var c100 = data[0].indexOf("O1_KPT_D1/O1_KPT_D1_Other_KG");
        var c110 = data[0].indexOf("O1_KPT_D1/O1_KPT_D1_Confirmation");

        var c12 = data[0].indexOf("PARTNER_INFO/Output2");
        var c21 = data[0].indexOf("TemplateID");

        var copy = COPY(data);

    
        if (EOF == "true") {
            for (i = 1; i < LRD; i++) {
                if (data[i][c0] == "OFF" && data[i][c21]==formdataid) {
                    data[i][c0] = "";
                }
            }
        }
        if (EOF == "false") {
            for (i = 1; i < LRD; i++) {
                if (data[i][cbe] == "Endline" && data[i][c21]==formdataid)  {
                    data[i][c0] = "OFF";
                }
            }
        }


        //NON KPT DATA TO BE OK

        for (i = 1; i < data.length; i++) {

            if (data[i][c3] == "" || data[i][c4] == 1) //SKIP NON DAY1 KPT OR ALREADY VERIFIED => OK
            {
                continue;
            }

            data[i][c4] = 0;
            for (ii = 1; ii < data.length; ii++) {
                if (data[ii][c5] == "" || data[ii][c4] == 1) //NON KPT OR ALREADY VERIFIED => OK
                {
                    continue;
                }
                data[ii][c4] = 0;
                if (data[ii][c5] == data[i][c3] && data[ii][cbe] == data[i][cbe]) {
                    data[i][c4] = 1;
                    data[ii][c4] = 1;
                    data[ii][c6] = data[i][c66]; //WOOD
                    data[ii][c7] = data[i][c77]; //CHARCOAL
                    data[ii][c8] = data[i][c88]; //LIQUID
                    data[ii][c9] = data[i][c99]; //GAS
                    data[ii][c10] = data[i][c100]; //OTHER
                    data[ii][c11] = data[i][c110]; //KPT1 CONFIRMATION
                    break;
                }
            }

        }

        /*Data Clean Status GET*/
        for (i = 1; i < data.length; i++) {

            if (String(data[i][c12]) == "0") {
                data[i][c0] = "";
                continue;
            }

            if (String(data[i][c0]) == "OFF") {
                continue;
            }

            if (data[i][c3] == "" && data[i][c5] == "") //NON KPT Data
            {
                data[i][c0] = "OK";
                continue;
            }
            if (data[i][c3] != "" && data[i][c4] == 1) //KPT D1 DATA ALREADY VERIFIED
            {
                data[i][c0] = "OK";
                continue;
            }
            if (data[i][c5] != "" && data[i][c4] == 1) //KPT D2 DATA ALREADY VERIFIED
            {
                data[i][c0] = "OK";
                continue;
            }

            if (data[i][c1].indexOf("FEC") > -1 || data[i][c2].indexOf("FEC") > -1) {
                if (String(data[i][c4]) != "1") {
                    if (data[i][c1].indexOf("SL") == -1 && data[i][c1].indexOf("PLD") == -1 && data[i][c1].indexOf("CLP") == -1 && data[i][c1].indexOf("SL") == -1 && data[i][c2].indexOf("PLD") == -1 && data[i][c2].indexOf("CLP") == -1) {
                        continue;
                    }
                    else {
                        data[i][c0] = "OK2";
                        continue;
                    }
                }
            }

        }

        COMPARE(data, copy, alldatasheet);
        updateLog("BS Analysis Sheet updated No.4 : (KPT Verification) ");
        return 1;
    }

Function recalculateTopBottomThresholds_energy

recalculateTopBottomThresholds_energy()

The function calls from CREATEANALYSISSHEET, recalculates Top and Bottom 2 % thresholds for each of the variables provided in OUTLIERV sheet, for each country and baseline/endline, using the primary data from ORIGNALDATA sheet.

1
2
3
4
5
6
7
8
   function recalculateTopBottomThresholds_energy() {
        outliercalculation();
        overwriteOutlier();

        updateLog("BS Analysis Sheet updated No.5 (Outlier Control)");

        return 1;
    }

Function convertToUSD

convertToUSD()

The function exchanges the local currency to USD

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
    function convertToUSD() {
        var idx = key["idx"];
        var sst = SpreadsheetApp.openById(idx);
        var alldatasheet = sst.getSheetByName("ALLDATA");
        var LRD = alldatasheet.getLastRow();
        var data = alldatasheet.getRange(1, 1, LRD, alldatasheet.getLastColumn()).getValues();

        var exchange = sst.getSheetByName("Exchange");
        var exdata = exchange.getRange(1, 1, exchange.getLastRow(), exchange.getLastColumn()).getValues();
        var exdatav = exchange.getRange(1, 1, exchange.getLastRow(), 1).getValues();
        var indexham = exdatav[0].map(function (col, i) {
            return exdatav.map(function (row) {
                return row[i]
            });
        });

        var local = data[0].indexOf("LocalCurrency");

        var ex = 1;
        var cxx1 = data[0].indexOf('AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Wood');
        var cxx2 = data[0].indexOf('AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Liquid_Fuel');
        var cxx3 = data[0].indexOf('AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Cooking_Gas');
        var cxx4 = data[0].indexOf('AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Charcoal');
        var cxx5 = data[0].indexOf('AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Briquettes');
        var cxx6 = data[0].indexOf('AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Biogas');
        var cxx7 = data[0].indexOf('AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Other');

        var cxx11 = data[0].indexOf('WoodAmount');
        var cxx22 = data[0].indexOf('LiquidAmount');
        var cxx33 = data[0].indexOf('GasAmount');
        var cxx44 = data[0].indexOf('CharcoalAmount');
        var cxx55 = data[0].indexOf('BriquettesAmount');
        var cxx66 = data[0].indexOf('BiogasAmount');
        var cxx77 = data[0].indexOf('OtherAmount');

        var l1 = data[0].indexOf('HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_BatteryTouch');
        var l2 = data[0].indexOf('HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Electricity');
        var l3 = data[0].indexOf('HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_KeroseneGas');
        var l4 = data[0].indexOf('HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Candles');
        var l5 = data[0].indexOf('HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Phones');
        var l6 = data[0].indexOf('HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Firewood');
        var l7 = data[0].indexOf('HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Other');

        var l11 = data[0].indexOf('BatteryAmount');
        var l22 = data[0].indexOf('ElectricityAmount');
        var l33 = data[0].indexOf('KeroseneGasAmount');
        var l44 = data[0].indexOf('CandlesAmount');
        var l55 = data[0].indexOf('PhonesAmount');
        var l66 = data[0].indexOf('FirewoodAmount');
        var l77 = data[0].indexOf('OtherLightAmount');

        var x1 = data[0].indexOf('DISTRIBUTION_HHLIGHTS/O2_TypeLights_UNHCR');
        var x2 = data[0].indexOf('DISTRIBUTION_HHLIGHTS/O2_Main_Light');
        var x11 = data[0].indexOf('UsingUNHCRLight');

        var xx = data[0].indexOf('SpentOnOtherLights');

        var copy = COPY(data);

        for (c = 1; c < data.length; c++) {
            ex = exdata[indexham[0].indexOf(data[c][local])][2];

            if (String(data[c][cxx1]) != "") {
                data[c][cxx11] = data[c][cxx1] * ex;
            }
            if (String(data[c][cxx2]) != "") {
                data[c][cxx22] = data[c][cxx2] * ex;
            }
            if (String(data[c][cxx3]) != "") {
                data[c][cxx33] = data[c][cxx3] * ex;
            }
            if (String(data[c][cxx4]) != "") {
                data[c][cxx44] = data[c][cxx4] * ex;
            }
            if (String(data[c][cxx5]) != "") {
                data[c][cxx55] = data[c][cxx5] * ex;
            }
            if (String(data[c][cxx6]) != "") {
                data[c][cxx66] = data[c][cxx6] * ex;
            }
            if (String(data[c][l1]) != "") {
                data[c][l11] = data[c][l1] * ex;
            }
            if (String(data[c][l2]) != "") {
                data[c][l22] = data[c][l2] * ex;
            }
            if (String(data[c][l3]) != "") {
                data[c][l33] = data[c][l3] * ex;
            }
            if (String(data[c][l4]) != "") {
                data[c][l44] = data[c][l4] * ex;
            }
            if (String(data[c][l5]) != "") {
                data[c][l55] = data[c][l5] * ex;
            }
            if (String(data[c][l6]) != "") {
                data[c][l66] = data[c][l6] * ex;
            }
            if (String(data[c][l7]) != "") {
                data[c][l77] = data[c][l7] * ex;
            }

            if (data[c][x1] != "") {

                data[c][x11] = 0;

                if (data[c][x1] == "SolarL" && data[c][x2] == "Solar") { data[c][x11] = 1; }
                if (data[c][x1] == "Electricity" && data[c][x2] == "Electricity") { data[c][x11] = 1; }
                if (data[c][x1] == "Kerosene_Gas" && data[c][x2] == "Kerosene_gas_lantern") { data[c][x11] = 1; }
                if (data[c][x1] == "Battery_Torch" && data[c][x2] == "Torch_Battery") { data[c][x11] = 1; }
                if (data[c][x1] == "HomeSolarSystem" && data[c][x2] == "Home_Solar_System") { data[c][x11] = 1; }
            }

            var count = 0;
            if (data[c][x1] == "") {
                if (data[c][l11] + data[c][l22] + data[c][l33] + data[c][l44] + data[c][l55] + data[c][l66] + data[c][l77] > 0) {
                    count++;
                }
            }
            if (data[c][x1] == "SolarL") {
                if (data[c][l11] + data[c][l22] + data[c][l33] + data[c][l44] + data[c][l55] + data[c][l66] + data[c][l77] > 0) {
                    count++;
                }
            }
            if (data[c][x1] == "Electricity") {
                if (data[c][l11] + data[c][l33] + data[c][l44] + data[c][l55] + data[c][l66] + data[c][l77] > 0) {
                    count++;
                }
            }
            if (data[c][x1] == "Kerosene_Gas") {
                if (data[c][l11] + data[c][l22] + data[c][l44] + data[c][l55] + data[c][l66] + data[c][l77] > 0) {
                    count++;
                }
            }
            if (data[c][x1] == "Battery_Torch") {
                if (data[c][l22] + data[c][l33] + data[c][l44] + data[c][l55] + data[c][l66] + data[c][l77] > 0) {
                    count++;
                }
            }
            if (data[c][x1] == "HomeSolarSystem") {
                if (data[c][l11] + data[c][l22] + data[c][l33] + data[c][l44] + data[c][l55] + data[c][l66] + data[c][l77] > 0) {
                    count++;
                }
            }

            if (count > 0) { data[c][xx] = 1; }
            if (data[0][l11] + data[0][l22] + data[0][l33] + data[0][l44] + data[0][l55] + data[0][l66] + data[0][l77] > 0) {
                count++;
            }
        }
        COMPARE(data, copy, alldatasheet);

        var idx = key["idx"];
        var file = DriveApp.getFileById(idx);
        var fileParents = file.getParents();
        var filename = fileParents.next().getName()
        var year;
        for (y = 2017; y < 2030; y++) {
            if (filename.indexOf(y) > -1) { year = y; break; }
        }

        updateLog("BS Analysis Sheet updated No.6 : (USD Conversion) ");
        return 1;
    }

Function overwriteOutlier_energy

overwriteOutlier_energy()

The function converts data from OUTLIERV sheet to JSON format, calls from OUTLIERS > outliercalculation

 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
    function overwriteOutlier_energy() {
        var ow = 0;
        var idx = key["idx"];
        var sst = SpreadsheetApp.openById(idx);
        var outlierr = sst.getSheetByName("OUTLIERR");
        var alldatasheet = sst.getSheetByName("ALLDATA");
        var originaldatasheet = sst.getSheetByName("ORIGINALDATA");

        var percent = outlierr.getRange(1, 1, outlierr.getLastRow(), 7).getValues();
        var LCD = alldatasheet.getLastColumn();
        var LRD = alldatasheet.getLastRow();
        var Destination = alldatasheet.getRange(1, 1, LRD, LCD).getValues();
        var copy = COPY(Destination);
        var Origin = originaldatasheet.getRange(1, 1, originaldatasheet.getLastRow(), originaldatasheet.getLastColumn()).getValues();

        var c1 = Origin[0].indexOf("Country");
        var c2 = Origin[0].indexOf("PARTNER_INFO/BE");
        var c3 = Origin[0].indexOf("_id");

        var indexv = alldatasheet.getRange(1, c3 + 1, alldatasheet.getLastRow(), 1).getValues();
        var indexh = indexv[0].map(function (col, i) {
            return indexv.map(function (row) {
                return row[i]
            });
        });

        var c6 = Destination[0].indexOf("Wood");
        var c7 = Destination[0].indexOf("Charcoal");
        var c8 = Destination[0].indexOf("Liquid");
        var c9 = Destination[0].indexOf("Gas");
        var c10 = Destination[0].indexOf("Other");
        var c66 = Destination[0].indexOf("O1_KPT_D1/O1_KPT_D1_Wood_KG");
        var c77 = Destination[0].indexOf("O1_KPT_D1/O1_KPT_D1_Charcoal_KG");
        var c88 = Destination[0].indexOf("O1_KPT_D1/O1_KPT_D1_Kerosene_KG");
        var c99 = Destination[0].indexOf("O1_KPT_D1/O1_KPT_D1_Gas_KG");
        var c100 = Destination[0].indexOf("O1_KPT_D1/O1_KPT_D1_Other_KG");
        var cbe = Destination[0].indexOf("PARTNER_INFO/BE");
        var c33 = Destination[0].indexOf("O1_KPT_D1/O1_KPT_NO/RANDOMID");
        var c55 = Destination[0].indexOf("PARTNER_INFO/KPTD2_ID");//72

        var count = 0;
        for (v = 0; v < percent.length; v++) {

            var xx = Origin[0].indexOf(percent[v][1]);

            for (i = 0; i < Origin.length; i++) {

                if (Origin[i][xx] == "" || Origin[i][c1] != percent[v][0] || Origin[i][c2] != percent[v][2]) { continue; }

                var match = indexh[0].indexOf(parseInt(Origin[i][c3]));

                ow = 0;

                if (match > -1) {

                    Destination[match][xx] = Origin[i][xx];

                    if (Origin[i][xx] != "" && Origin[i][xx] != "0" && parseFloat(Origin[i][xx]) > parseFloat(percent[v][6])) {
                        Destination[match][xx] = percent[v][6];
                        count++; ow = 1;
                    }

                    if (ow == 0 && Origin[i][xx] != "" && Origin[i][xx] != "0" && parseFloat(Origin[i][xx]) < parseFloat(percent[v][5])) {
                        Destination[match][xx] = percent[v][5];
                        count++; ow = 1;
                    };

                    if (ow == 1) {
                        for (id = 1; id < Destination.length; id++) {

                            if (Destination[id][c55] == Destination[match][c33] && Destination[id][cbe] == Destination[match][cbe]) {
                                Destination[id][c6] = Destination[match][c66]; //WOOD
                                Destination[id][c7] = Destination[match][c77]; //CHARCOAL
                                Destination[id][c8] = Destination[match][c88]; //LIQUID
                                Destination[id][c9] = Destination[match][c99]; //GAS
                                Destination[id][c10] = Destination[match][c100]; //OTHER
                                break;
                            }

                        }

                    }
                }
            }

            COMPARE(Destination, copy, alldatasheet);
            
        }
    }

Function extractCountryAnalysisNote_energy

extractCountryAnalysisNote_energy()

The function extracts country analysis note on country_analysis_note sheet

  • Resetting GLobal Variables

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
    function extractCountryAnalysisNote_energy() {
       /*Resetting Global Variables*/
        {
            XX = 1;
            YY = 1;
            y = 26;
            x = 2;
            /*KPTD2 Baseline*/
            KPTDAY2 = 0;
            /*KPTD2 Endline*/
            KPTDAY22 = 0;
            /*KPT Baseline*/
            KPTDAY2PANEL = 0;
            /*KPT Endline*/
            KPTDAY2PANEL2 = 0;
            KPTperson = 0;
            KPTmeal = 0;
            /*TABLE2 for valid samples*/
            KPTttb2 = [];
            /*TABLE3 for complete data per fuel*/
            KPTttb3 = [];
            validsample = 0;
            average = 0;
            standard = 0;
            localcurrency = "";
            exdata = [];
            ex = 1;
            StartDate = "";
            EndDate = "";
            bgdata = null;
            tn = 1;
            endline = 0;
            /*Total convertNumberToText of forcibly displaced targeted [OUTPUT1]*/
            tpocbene1 = 0;
            /*Total convertNumberToText of forcibly displaced targeted [Intervention]*/
            tpocbene2 = 0;
             /* Total convertNumberToText of forcibly displaced targeted [Title]*/
            tpocbene3 = 0;
             /* Total convertNumberToText of host beneficiaries targeted [Output1]*/
            thbene1 = 0;
            /*Total convertNumberToText of host beneficiaries targeted [Intervention]*/
            thbene2 = 0;
            /*Total convertNumberToText of host beneficiaries targeted [Title]*/
            thbene3 = 0;
            sl = "";
            l = "";
            typestove = "";
            typelight = "";
            text;
            t1 = "";
            table = null;
            table2 = null;
            bltotalkg = 0;
            bltotalp = 0;
            eltotalkg = 0;
            eltotalp = 0;
            /*convertNumberToText of beneficiaries report feeling safe outside their houses moving around the community at night.*/
            safec = 0; /*Baseline*/
            safec2 = 0;/*Endline*/
           /* Nubmer of beneficiaries report feeling unsafe outside their houses moving around the community at night*/
            unsafec = 0;/*Baseline*/
            unsafec2 = 0;/*Endline*/
            /*The percent of beneficiaries who report feeling safe inside their houses at night*/
            safeh = 0; /*Baseline*/
            safeh2 = 0 /*Endline*/
            /*The percent of beneficiaries who report feeling unsafe inside their houses at night*/
            unsafeh = 0; /*Baseline*/
            unsafeh2 = 0;/*Endline*/
            /*convertNumberToText of beneficiaries surveyed retained the fuel efficient cookstoves*/
            retentionstove = 0;
            /*convertNumberToText of beneficiaries have utilised the fuel efficient cookstoves*/
            utlisationsotve = 0;
            /*The average amount of fuel used for cooking per person per day */
            woodkg = 0;  /*Wood fuel - Baseline*/
            woodkg2 = 0; /*Wood fuel - Endline*/

            liquidkg = 0; /*liquid fuel - Baseline*/
            liquidkg2 = 0; /*liquid fuel - Endline*/

            gaskg = 0;/* gas fuel (including LPG and LNG) - Baseline*/
            gaskg2 = 0;/* gas fuel (including LPG and LNG) - Endline*/
            otherkg = 0; otherkg2 = 0;
            totalusd = 0; totalusd2 = 0;
            totallocal = 0; totallocal = 0;
            retentionlights = 0;
            functionlight = 0;
            utlisationlight = 0;
            totalusd = 0; totalusd2 = 0;
            totallocal = 0; totallocal2 = 0;
            totaloutputbl = 0;
            totaloutputel = 0;
            budget1 = 0;
            budget2 = 0;
            budget3 = 0;
            pbudget = 0;
            pocbene1 = 0;
            pocbene2 = 0;
            pocbene3 = 0;
            hbene1 = 0;
            hbene2 = 0;
            hbene3 = 0;;
            camps = 0;
            cnames = "";
            totalbudget = 0;
            totalpoc = 0;
            totalhost = 0;
            npartner = 0;
            int1 = "";
            int2 = "";
            int3 = "";
            sumsheet = ss.getSheetByName("COUNTRY_ANALYSIS_NOTE");

        }

  • Generating and displaying data on Country Analysis note

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
        filterpage();
        DataLoad();
        displayDataIndicators123();
        displayDataIndicators4Otput1();
        displayDataIndicator5Output2_energy();
        y2++;
        formatText(y2, 2, "For questions regarding the revised monitoring system, data and analysis, please contact: energy@unhcr.org", "Arimo", 9, "white", "#1c4587", 10, "#1c4587", "right", "0");
        y2++;
    }

}

Function displayDataIndicators123_energy

displayDataIndicators123_energy()

The function displays an information/indicators on Country Analysis Note (1.LIVELIHOODS PROGRAMME AND MONITORING SCOPE ; 2. PROFILE OF SAMPLE BENEFICIARIES AT BASELINE; 3. SAMPLE SIZE PER OUTPUT)

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
    function displayDataIndicators123_energy() {
         /*idx {string } - year*/
        var idx = key["idx"];
        var sst = SpreadsheetApp.openById(idx);
        /*filters {type} - the sheet with the given name [Context]*/
        var problem = sst.getSheetByName("Context");
        /*sheetbg{type} - the sheet with the given name [BG]*/
        var sheetbg = sst.getSheetByName("BG");

        sumsheet.clear();
        titlecolor = "#1c4587";
        graphcolor = "#cfe2f3";

        var range = sumsheet.getRange(1, 1, 300, 50);

        for (i = 2; i < 16; i++) {
            sumsheet.getRange(1, i).setValue("");
            sumsheet.setColumnWidth(i, 100);
        }

        sumsheet.setColumnWidth(1, 25);
        sumsheet.setColumnWidth(2, 25);
        sumsheet.setColumnWidth(8, 25);
        sumsheet.setColumnWidth(2, 150);

        for (i = 1; i < 300; i++) {
            sumsheet.setRowHeight(i, 25);
        }
        sumsheet.getRange(2, 11, 3, 1).mergeCells();
        sumsheet.getRange(2, 11).setFormula('=image("https://docs.google.com/uc?export=download&id=1vxiKfLA8TrZGZz92F5lhpeFteGK9FhiY",4,110,100)');

        range.setVerticalAlignment("middle");
        range.setFontFamily("Arimo");
        range.setFontSize(12);
        range.setDataValidation(null);
        range.setconvertNumberToTextFormat('0');

        var d = new Date();

        y2 = 2;

        x = 2;

        /*Head on Country Analysis Note */
        formatText(y2, 2, "ENERGY COUNTRY ANALYSIS NOTE", "Arimo", 18, "Navy", "white", 6, "white", "left", "bold");
        y2++;
        y2++;

        /*Benefciciary calculation from BG sheet [Beneficiary data]*/
        bgdata = sheetbg.getRange(1, 1, sheetbg.getLastRow(), sheetbg.getLastColumn()).getValues();
        budget1 = 0;
        budget2 = 0;
        buget3 = 0;
        hbene = 0;
        pocbene = 0;
        pbudget = 0;
        camps = 0;

        for (id = 0; id < sheetbg.getLastRow(); id++) {
            if (bgdata[id][0] == "") {
                continue;
            }
            /*taking data for specific country [Data Analyis]*/
            generateTemplateDataSpCountry("ALL", bgdata[id][0]);
        }


        Country2 = Country;
        /*replace header*/
        var pppg = formatHeader(['PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/PPG'], "Multi").replace(/_/g, " ");
        var pppg2;
        /*filters {type} -the sheet with the given name [FILTER sheet on Baeneficary data]*/
        var filters = sst.getSheetByName("Filter");
        /*ppgsheet {type} -the sheet with the given name [PPG sheet]*/
        var ppgsheet = sst.getSheetByName("PPG");
        var PPGss = ppgsheet.getRange(1, 1, ppgsheet.getLastRow(), ppgsheet.getLastColumn()).getValues();


        var cy;

        if (Country != "ALL") {
            cy = Country.toUpperCase() + " " + Year;
            for (p = 0; p < PPGss.length; p++) {
                if (PPGss[p][0] == Country) {
                    cy = PPGss[p][1].toUpperCase() + " " + Year;

                }
                if (Dstation != "" && PPGss[p][0] == Dstation) {
                    cy = PPGss[p][1].toUpperCase() + " " + Year;
                    Country = PPGss[p][1];

                }
            }
        } else {
            cy = "Global " + Year;
        }
        formatText(y2, 2, cy, "Arimo", 36, "Navy", "white", 8, "white", "left", "bold");
        sumsheet.getRange(y2, 2).setWrap(true);

        y2++;
        cy = "";

        try { PROBLEMSTATEMENT = problem.getRange(1, 1, problem.getLastRow(), problem.getLastColumn()).getValues(); } catch (e) { PROBLEMSTATEMENT = ""; }
        if (Country != "ALL") {

            var usn = filters.getRange(5, 6).getValue();
            if (usn == "") { pppg2 = Country.toLowerCase(); } else { pppg2 = usn; }
            for (p = 0; p < PPGss.length; p++) {
                if (PPGss[p][0] == pppg2) {
                    pppg = PPGss[p][1].replace(/_/g, " ");
                    break;
                }
            }

            formatText(y2, 2, pppg, "Arimo", 16, "black", "white", 9, "white", "left");

            sumsheet.getRange(y2, 2).setWrap(true);
        } else {
            for (i = 0; i < sheetbg.getLastRow(); i++) {
                if (bgdata[i][0] == "") {
                    continue;
                }
                cy = cy + bgdata[i][0];
                if (bgdata[i + 1][0] != "") {
                    cy = cy + ", ";
                }
            }
            formatText(y2, 2, cy, "Arimo", 16, "black", "white", 9, "white", "left");
            sumsheet.getRange(y2, 2).setWrap(true);
        }

        formatText(y2, 11, d, "Arimo", 14, "#1c4587", "white", 1, "white", "right");
        sumsheet.getRange(y2, 11, 1, 1).setVerticalAlignment("bottom");
        y2++;

        formatText(y2, 2, "1. ENERGY PROGRAMME AND MONITORING SCOPE", "Arimo", 18, "white", "#1c4587", 10, "#1c4587", "left", "bold");
        sumsheet.setRowHeight(y2, 50);
        y2 = y2 + 2;


        if (Country != "ALL") {
            formatText(y2, 2, "Problem Statement:", "Arimo", 14, "black", "white", 3, "white", "left", "0");
            y2++;
        }
        var textp = "";
        var cccc = 0;
        for (pp = 0; pp < PROBLEMSTATEMENT.length; pp++) {
            if (PROBLEMSTATEMENT[pp][0] == Country2) {
                if (User != "" && PROBLEMSTATEMENT[pp][1] != User) {
                    continue;
                }
                if (cccc == 0) {
                    textp = textp + PROBLEMSTATEMENT[pp][2];
                    cccc++;
                } else {
                    textp = textp + String.fromCharCode(10) + String.fromCharCode(10) + PROBLEMSTATEMENT[pp][2];
                }
            } else {
                continue;
            }
            formatText(y2, 2, textp, "Arimo", 14, "black", "#cfe2f3", 10, "#cfe2f3", "left", "Normal");
        }

        y2++;
        y2++;

        formatText(y2, 2, "Context:", "Arimo", 14, "black", "white", 3, "white", "left", "0");
        y2++;

        formatText(y2, 2, "Total convertNumberToText of Energy Partner(s)", "Arimo", 14, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
        formatText(y2, 5, npartner, "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 5).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;

        formatText(y2, 2, "Total Energy Programme (USD)", "Arimo", 14, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
        formatText(y2, 5, totalbudget, "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 5).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;
        y2++;

        if (camps < takeUniqueDataBGsheet(5, "0")) { camps = takeUniqueDataBGsheet(5, "0"); }
        formatText(y2, 2, "convertNumberToText of Sites Targeted", "Arimo", 14, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
        formatText(y2, 5, camps, "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 5).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;


      
        budget1 = 0;
        budget2 = 0;
        budget3 = 0;
        hbene = 0;
        pocbene = 0;
        pbudget = 0;
        camps = 0;
        int1 = "";
        int2 = "";
        int3 = "";

        for (cx = 0; cx < sheetbg.getLastRow(); cx++) {
            if (bgdata[cx][0] == "") {
                continue;
            }
            for (itd = 0; itd < sheetbg.getLastRow(); itd++) {
                if (bgdata[itd][2] == "") {
                    continue;
                }
                generateTemplateDataSpCountry(bgdata[itd][2], bgdata[cx][0]);

                tpocbene1 = tpocbene1 + pocbene1;
                pocbene1 = 0;
                tpocbene2 = tpocbene2 + pocbene2;
                pocbene2 = 0;
                tpocbene3 = tpocbene3 + pocbene3;
                pocbene3 = 0;
                thbene1 = thbene1 + hbene1;
                hbene1 = 0;
                thbene2 = thbene2 + hbene2;
                hbene2 = 0;
                thbene3 = thbene3 + hbene3;
                hbene3 = 0;
            }
        }

        var fsize = 16.5;
        if (totalpoc > 9999999 || totalhost > 9999999) { fsize = 14; }
        formatText(y2, 2, "Total Population of Forcibly Displaced", "Arimo", 14, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
        if (totalpoc < tpocbene1 + tpocbene2 + tpocbene3) { totalpoc = tpocbene1 + tpocbene2 + tpocbene3; }
        formatText(y2, 5, totalpoc, "Arimo", fsize, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 5).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;


        if (totalhost < thbene1 + thbene2 + thbene3) { totalhost = thbene1 + thbene2 + thbene3; }
        formatText(y2, 2, "Total Estimated Host Population", "Arimo", 14, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
        formatText(y2, 5, totalhost, "Arimo", fsize, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 5).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;
        y2++;

        formatText(y2, 2, "Supported Cookstoves:", "Arimo", 14, "black", "#cfe2f3", 1, "#cfe2f3", "left", "0");
        formatText(y2, 3, typestove, "Arimo", 13, "black", "#cfe2f3", 3, "#cfe2f3", "left", "bold");
        sumsheet.getRange(y2, 3).setVerticalAlignment("Top");
        y2++;
        formatText(y2, 2, "Supported Portable Lights:", "Arimo", 14, "black", "#cfe2f3", 1, "#cfe2f3", "left", "0");
        formatText(y2, 3, typelight, "Arimo", 13, "black", "#cfe2f3", 3, "#cfe2f3", "left", "bold");
        sumsheet.getRange(y2, 3).setVerticalAlignment("Top");
        y2++;
        y2 = y2 - 9;



        formatText(y2 - 1, 7, "Programs Monitored:", "Arimo", 14, "black", "white", 3, "white", "left", "0");
        formatText(y2, 7, "convertNumberToText of Partners Monitored", "Arimo", 14, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
        formatText(y2, 11, takeUniqueDataBGsheet(3, "0"), "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        y2++;

        formatText(y2, 7, "Partner Project (USD)", "Arimo", 14, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
        formatText(y2, 11, budget1 + budget2 + budget3, "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 11).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;

        y2++;

        formatText(y2, 7, "convertNumberToText of Sites Surveyed", "Arimo", 14, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
        formatText(y2, 11, takeUniqueDataBGsheet(5, "0"), "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        y2++;

        formatText(y2, 7, "convertNumberToText of Forcibly Displaced Targeted", "Arimo", 14, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
        formatText(y2, 11, tpocbene1 + tpocbene2 + tpocbene3, "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 11).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;

        formatText(y2, 7, "convertNumberToText of Host Beneficiaries Targeted", "Arimo", 14, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
        formatText(y2, 11, thbene1 + thbene2 + thbene3, "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 11).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;
        formatText(y2, 11, "(Resource allocation)", "Arimo", 11, "black", "white", 1, "white", "right", "0")
        y2++;

        formatText(y2, 7, "Area 1 budget:" + String.fromCharCode(10) + "Fuel & Cookstove", "Arimo", 14, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
        formatText(y2, 11, budget1, "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 11).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;

        formatText(y2, 7, "Area 2 budget:" + String.fromCharCode(10) + "Community & HH Lighting", "Arimo", 14, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
        formatText(y2, 11, budget2, "Arimo", 16.5, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
        sumsheet.getRange(y2, 11).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
        y2++;

        note(y2, 11, "Note: Total Energy Programme (USD) includes UNHCR and partners' project generateTableValueIndicator42Fuels under livelihoods objective.")
        y2++;

       

        totaloutputbl = Count(jsonData, "Country", ["Total"], "Baseline", "1", "WD2");
        totaloutputel = Count(jsonData, "Country", ["Total"], "Endline", "1", "WD2");
        totaloutput = totaloutputbl + totaloutputel;

        formatText(y2, 9, "Sample Size:", "Arimo", 11, "black", "white", 1, "white", "right", "0");
        formatText(y2, 10, "Baseline", "Arimo", 11, "black", "white", 1, "white", "right", "0");
        formatText(y2, 11, "Endline", "Arimo", 11, "black", "white", 1, "white", "right", "0")
        sumsheet.getRange(y2, 9, 1, 3).setWrap(false);
        sumsheet.getRange(y2, 9, 1, 3).setVerticalAlignment("bottom");
        y2++;

        endline = totaloutputel;
        var baseline = totaloutputbl;
        if (baseline != 0) {
            formatText(y2, 2, "2. PROFILE OF SAMPLE BENEFICIARIES AT BASELINE", "Arimo", 18, "white", "#1c4587", 7, "#1c4587", "left", "bold");
            sumsheet.setRowHeight(y2, 50);


            formatText(y2, 9, "", "Arimo", 24, "white", "#1c4587", 1, "#1c4587", "right", "bold");
            formatText(y2, 10, totaloutputbl, "Arimo", 24, "white", "#1c4587", 1, "#1c4587", "right", "bold");
            formatText(y2, 11, endline, "Arimo", 24, "white", "#1c4587", 1, "#1c4587", "right", "bold");

            y2++;
            y2++;

            formatText(y2, 2, "% of Refugees/Asylum Seekers", "Arimo", 15, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
            formatText(y2, 5, Count(jsonData, "BENEFICIARY_INFO/Status", ["Refugee", "Asylum_Seeker", "Stateless"], "Baseline", "0", "WD2") / baseline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 2, "% of Former Refugees", "Arimo", 15, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
            formatText(y2, 5, Count(jsonData, "BENEFICIARY_INFO/Status", ["Resident_Status", "Naturalised", "Returnee"], "Baseline", "0", "WD2") / baseline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 2, "% of IDPs", "Arimo", 15, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
            formatText(y2, 5, Count(jsonData, "BENEFICIARY_INFO/Status", ["IDP"], "Baseline", "0", "WD2") / baseline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 2, "% of Host Communities", "Arimo", 15, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
            formatText(y2, 5, Count(jsonData, "BENEFICIARY_INFO/Status", ["Host_Community"], "Baseline", "0", "WD2") / baseline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2 = y2 - 3;


            formatText(y2, 7, "% of Female Beneficiaries", "Arimo", 15, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0", "WD2");
            formatText(y2, 11, Count(jsonData, "BENEFICIARY_INFO/Gender", ["Female"], "Baseline", "0", "WD2") / baseline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            sumsheet.getRange(y2, 5, 4, 1).setconvertNumberToTextFormat('0%;[Red](0%)');
            sumsheet.getRange(y2, 11, 4, 1).setconvertNumberToTextFormat('0%;[Red](0%)');
            y2++;

            formatText(y2, 7, "% of Adults (18-65) Beneficiaries", "Arimo", 15, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
            var tnn = baseline - countTotalNumAgeGroup(jsonData, "BENEFICIARY_INFO/Age", 64, 0, "Baseline", "0", "WD2") - countTotalNumAgeGroup(jsonData, "BENEFICIARY_INFO/Age", 1000, 19, "Baseline", "0", "WD2");
            formatText(y2, 11, tnn / baseline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 7, "% of Elderly (>=65) Beneficiaries", "Arimo", 15, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
            formatText(y2, 11, countTotalNumAgeGroup(jsonData, "BENEFICIARY_INFO/Age", 65, 0, "Baseline", "0", "WD2") / baseline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 7, "% of Youth (<18) Beneficiaries", "Arimo", 15, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
            formatText(y2, 11, countTotalNumAgeGroup(jsonData, "BENEFICIARY_INFO/Age", 1000, 18, "Baseline", "0", "WD2") / baseline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;


            note(y2, 11, "Note: FORMER REFUGEES includes returnees, naturalised refugees, or refugees with resident status.")
            y2++;
            y2++;
        } else if (endline > 0) {
            formatText(y2, 2, "2. PROFILE OF SAMPLE BENEFICIARIES AT ENDLINE", "Arimo", 18, "white", "#1c4587", 7, "#1c4587", "left", "bold");
            sumsheet.setRowHeight(y2, 50);


            formatText(y2, 9, "", "Arimo", 24, "white", "#1c4587", 1, "#1c4587", "right", "bold");
            formatText(y2, 10, totaloutputbl, "Arimo", 24, "white", "#1c4587", 1, "#1c4587", "right", "bold");
            formatText(y2, 11, endline, "Arimo", 24, "white", "#1c4587", 1, "#1c4587", "right", "bold");

            y2++;
            y2++;

            formatText(y2, 2, "% of Refugees/Asylum Seekers", "Arimo", 15, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
            formatText(y2, 5, Count(jsonData, "BENEFICIARY_INFO/Status", ["Refugee", "Asylum_Seeker", "Stateless"], "Endline", "0", "WD2") / endline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 2, "% of Former Refugees", "Arimo", 15, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
            formatText(y2, 5, Count(jsonData, "BENEFICIARY_INFO/Status", ["Resident_Status", "Naturalised", "Returnee"], "Endline", "0", "WD2") / endline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 2, "% of IDPs", "Arimo", 15, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
            formatText(y2, 5, Count(jsonData, "BENEFICIARY_INFO/Status", ["IDP"], "Endline", "0", "WD2") / endline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 2, "% of Host Communities", "Arimo", 15, "black", "#cfe2f3", 3, "#cfe2f3", "left", "0");
            formatText(y2, 5, Count(jsonData, "BENEFICIARY_INFO/Status", ["Host_Community"], "Endline", "0", "WD2") / endline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2 = y2 - 3;


            formatText(y2, 7, "% of Female Beneficiaries", "Arimo", 15, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0", "WD2");
            formatText(y2, 11, Count(jsonData, "BENEFICIARY_INFO/Gender", ["Female"], "Endline", "0", "WD2") / endline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            sumsheet.getRange(y2, 5, 4, 1).setconvertNumberToTextFormat('0%;[Red](0%)');
            sumsheet.getRange(y2, 11, 4, 1).setconvertNumberToTextFormat('0%;[Red](0%)');
            y2++;

            formatText(y2, 7, "% of Adults (18-65) Beneficiaries", "Arimo", 15, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
            var tnn = endline - countTotalNumAgeGroup(jsonData, "BENEFICIARY_INFO/Age", 64, 0, "Endline", "0", "WD2") - countTotalNumAgeGroup(jsonData, "BENEFICIARY_INFO/Age", 1000, 19, "Endline", "0", "WD2");
            formatText(y2, 11, tnn / endline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 7, "% of Elderly (>65) Beneficiaries", "Arimo", 15, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
            formatText(y2, 11, countTotalNumAgeGroup(jsonData, "BENEFICIARY_INFO/Age", 64, 0, "Endline", "0", "WD2") / endline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;

            formatText(y2, 7, "% of Youth (<18) Beneficiaries", "Arimo", 15, "black", "#cfe2f3", 4, "#cfe2f3", "left", "0");
            formatText(y2, 11, countTotalNumAgeGroup(jsonData, "BENEFICIARY_INFO/Age", 1000, 19, "Endline", "0", "WD2") / endline, "Arimo", 18, "black", "#cfe2f3", 1, "#cfe2f3", "right", "bold");
            y2++;


            formatText(y2, 2, "Note: 1) FORMER REFUGEES includes returnees, naturalised refugees, or refugees with resident status, 2) Due to timing of roll-out of revised monitoring tools and program implementation, only an Endline survey was completed as the beneficiaries had already begun participation in the program.", "Arimo", 10, "grey", "white", 10, "white", "left", "0");
            y2++;
            y2++;
        }

        formatText(y2, 2, "3. SAMPLE SIZE PER OUTPUT", "Arimo", 18, "white", "#1c4587", 10, "#1c4587", "left", "bold");
        sumsheet.setRowHeight(y2, 50);

        y2 = y2 + 2;

        var ttb = generateTablePercentIndicatorImpact_energy([['FEC', 'FP', 'CFP'], ['SL', 'PLD', 'CLP']], ['AREA 1: Fuel & Cookstove', 'AREA 2: Lighting'], jsonData, ["PARTNER_INFO/Output", "PARTNER_INFO/Output2"], "1", "WD2");
        formatTables(y2, x + 1, ttb, 'Table ' + tn + ': convertNumberToText of beneficiaries per energy programme areas surveyed');
        displayLabelIndicator4Ouput1(y2, x - 1);
        displayNarrative3SampleSize(y2 - ttb.length - 2, 9, ttb.length + 2);
        sumsheet.getRange(y2 - ttb.length - 2, 9).setWrap(true);
        y2++;

       
    }
}

    1. ENERGY PROGRAMME AND MONITORING SCOPE on country abalysis note and Problem statemenet

This is the image caption

Screenshot of header

    1. PROFILE OF SAMPLE BENEFICIARIES AT BASELINE/ENDLINE

This is the image caption

Screenshot of header

    1. SAMPLE SIZE PER OUTPUT

This is the image caption

Screenshot of header

Function displayDataIndicators4Otput1_energy

    1. AREA 1: SUSTAINABLE COOKSTOVES & FUEL SUPPLY

This is the image caption

Screenshot of 4. AREA 1: SUSTAINABLE COOKSTOVES & FUEL SUPPLY

 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
64
    function displayDataIndicators4Otput1_energy() {

        KPTDAY2 = Count(jsonData, "PARTNER_INFO/KPTD2", ['Yes'], "Baseline", "1");
        KPTDAY22 = Count(jsonData, "PARTNER_INFO/KPTD2", ['Yes'], "Endline", "1");
        KPTDAY2PANEL = Count(jsonData, "KPT", ['1'], "Baseline", "0") / 2;
        KPTDAY2PANEL2 = Count(jsonData, "KPT", ['1'], "Endline", "0") / 2;
        totaloutputbl = Count(jsonData, "PARTNER_INFO/Output", ['FEC', 'FP', 'CFP'], "Baseline", "1", "WD2");
        totaloutputel = Count(jsonData, "PARTNER_INFO/Output2", ['FEC', 'FP', 'CFP'], "Endline", "1", "WD2");
        totaloutput = totaloutputbl + totaloutputel;

        var FECcountbl = Count(jsonData, "PARTNER_INFO/Output", ['FEC'], "Baseline", "1", "WD2");
        var FECcountel = Count(jsonData, "PARTNER_INFO/Output2", ['FEC'], "Endline", "1", "WD2");

        if (IndicatorYN('O1') == 1 && totaloutput > 0) {
            x = 2; x2 = 6;
            titlecolor = "#134f5c";
            graphcolor = "#d9ead3";

            /*4. AREA 1: SUSTAINABLE COOKSTOVES & FUEL SUPPLY*/
            formatText(y2, 11, "", "Arimo", 11, "black", "white", 1, "white", "right", "0");
            y2++;
            formatText(y2, x, "4. AREA 1: SUSTAINABLE COOKSTOVES & FUEL SUPPLY", "Arimo", 18, "white", titlecolor, 8, titlecolor, "left", "bold");

            formatText(y2, 10, "", "Arimo", 24, "white", titlecolor, 2, titlecolor, "right", "bold");
            sumsheet.setRowHeight(y2, 50);

            if (totaloutput == 0) {
                y2++;
                y2++;
                return;
            }
            { 
                y2++;
                y2++;

                formatText(y2, 2, "Total convertNumberToText of forcibly displaced targeted", "Arimo", 15, "black", graphcolor, 3, graphcolor, "left", "0");
                formatText(y2, 5, tpocbene1, "Arimo", 18, "black", graphcolor, 1, graphcolor, "right", "bold");
                sumsheet.getRange(y2, 5).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');

                formatText(y2, 7, "Total convertNumberToText of host beneficiaries targeted", "Arimo", 15, "black", graphcolor, 4, graphcolor, "left", "0");
                formatText(y2, 11, thbene1, "Arimo", 18, "black", graphcolor, 1, graphcolor, "right", "bold");
                sumsheet.getRange(y2, 11).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
                y2++;
                y2++;


                if (totaloutputel > 0) { var msgend = " and " + totaloutputel + " at endline" } else { var msgend = ""; }
                var msgtemp = "For Area 1: 'Sustainable Fuel and Cookstoves', " + String(tpocbene1).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " forcibly displaced and " + String(thbene1).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " host beneficiaries were targeted for the program, of which " + totaloutputbl + " beneficiaries were surveyed at baseline" + msgend + "."
                formatText(y2, 2, msgtemp, "Arimo", 14, "black", "white", 10, "white", "left", "0");
                y2++;
                y2++;
                var nn = y2;
                var ttb = generateTablePercentIndicatorImpact_energy([['FEC'], ['FP'], ['CFP']], ['Fuel-efficient Cookstove', 'Fuel Provided', 'Cash for Fuel'], jsonData, ["PARTNER_INFO/Output", "PARTNER_INFO/Output2"], "1", "WD2");
                tn++;
                formatTables(y2, x + 1, ttb, 'Table ' + tn + ': convertNumberToText of beneficiaries per energy intervention areas surveyed');
                displayLabelIndicator4Ouput1(y2, x);
                displayNarrativeIndicator45Interventions(nn, 9, 5, tn);
                if (ttb.length < 5) {
                    y2++;
                }
                y2++;
               

            }

  • The information on the average amount of assistance

 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
            {
                if (jsonPartner.length > 0) {
                    var cx = 0;
                    /*Average solid fuel*/
                    var nominator = 0;
                    var denominator = 0;
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Amount_Fuel/O1_Amount_Fuel_Wood");
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Amount_Fuel/O1_Amount_Fuel_Charcoal");
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Amount_Fuel/O1_Amount_Fuel_Briquettes");
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Amount_Fuel/O1_Amount_Fuel_SolidOther");

                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel/O1_Beneficiary_Fuel_Wood");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel/O1_Beneficiary_Fuel_Charcoal");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel/O1_Beneficiary_Fuel_Briquettes");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel/O1_Beneficiary_Fuel_SolidOther");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel_Host/O1_Beneficiary_Fuel_Wood_Host");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel_Host/O1_Beneficiary_Fuel_Charcoal_Host");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel_Host/O1_Beneficiary_Fuel_Briquettes_Host");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel_Host/O1_Beneficiary_Fuel_SolidOther_Host");
                    if (denominator > 0) {
                        formatText(y2, 2, "Average convertNumberToText of kg of solid fuel for cooking provided per HH per year", "Arimo", 15, "black", graphcolor, 8, graphcolor, "left", "0");
                        formatText(y2, 10, String(Math.round(((nominator) / denominator) * 100) / 100).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " Kg", "Arimo", 18, "black", graphcolor, 2, graphcolor, "right", "bold");
                        y2++;
                        y2++;
                        cx++;
                    }

                    /*Average liquid fuel*/
                    var nominator = 0;
                    var denominator = 0;
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Amount_Fuel/O1_Amount_Fuel_Kerosene");
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Amount_Fuel/O1_Amount_Fuel_Ethanol");
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Amount_Fuel/O1_Amount_Fuel_Petrol");

                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel/O1_Beneficiary_Fuel_Kerosene");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel/O1_Beneficiary_Fuel_Ethanol");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel/O1_Beneficiary_Fuel_Petrol");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel_Host/O1_Beneficiary_Fuel_Kerosene_Host");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel_Host/O1_Beneficiary_Fuel_Ethanol_Host");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel_Host/O1_Beneficiary_Fuel_Petrol_Host");
                    if (denominator > 0) {
                        formatText(y2, 2, "Average convertNumberToText of kg of liquid fuel for cooking provided per HH per year", "Arimo", 15, "black", graphcolor, 8, graphcolor, "left", "0");
                        formatText(y2, 10, String(Math.round(((nominator) / denominator) * 100) / 100).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " Kg", "Arimo", 18, "black", graphcolor, 2, graphcolor, "right", "bold");

                        y2++;
                        y2++;
                        cx++;
                    }

                    /*Average gas*/
                    var nominator = 0;
                    var denominator = 0;
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Amount_Fuel/O1_Amount_Fuel_Gas");

                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel/O1_Beneficiary_Fuel_Gas");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_FP/O1_Beneficiary_Fuel_Host/O1_Beneficiary_Fuel_Gas_Host");
                    if (denominator > 0) {
                        formatText(y2, 2, "Average convertNumberToText of kg of gas fuel for cooking provided per HH per year", "Arimo", 15, "black", graphcolor, 8, graphcolor, "left", "0");
                        formatText(y2, 10, String(Math.round(((nominator) / denominator) * 100) / 100).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " Kg", "Arimo", 18, "black", graphcolor, 2, graphcolor, "right", "bold");

                        y2++;
                        y2++;
                        cx++;
                    }

                    /*Average cash*/
                    var nominator = 0;
                    var denominator = 0;
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_CFP/O1_CFP_Cash_Cost");

                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_CFP/O1_CFP_Actual");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT1/O1_CFP/O1_CFP_Actual_Host");
                    if (denominator > 0) {
                        formatText(y2, 2, "Average cash received/monetary equivalent of voucher for cooking fuel per HH per year", "Arimo", 15, "black", graphcolor, 8, graphcolor, "left", "0");
                        formatText(y2, 10, String(Math.round(((nominator * pexr) / denominator) * 100) / 100).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " USD", "Arimo", 18, "black", graphcolor, 2, graphcolor, "right", "bold");

                        y2++;
                        y2++;
                        cx++;
                    }

                    if (cx > 0) {
                        formatText(y2 - 1, 2, "Note: The information on the average amount of assistance provided has been provided at the end of the fiscal year by the implementing partner " + partnernamesp + ".", "Arimo", 10, "grey", "white", 10, "white", "left", "0");
                        y2++;
                    }
                }
            }

  • 4.1 UTILISATION OF COOKSTOVE

This is the image caption

Screenshot of 4.1 UTILISATION OF COOKSTOVE

  • 4.2 KITCHEN PERFORMANCE TEST (KPT)

This is the image caption

Screenshot of 4.2 KITCHEN PERFORMANCE TEST (KPT)

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
            { /*4.2 KITCHEN PERFORMANCE TEST (KPT)*/
                if (IndicatorYN('KPT1') == 1) {
                    if (FECcountbl > 0 || FECcountel > 0) {
                        formatText(y2 - 1, 11, "", "Arimo", 11, "black", "white", 1, "white", "right", "0");
                        sumsheet.getRange(y2 - 1, 11).setWrap(false);
                        formatText(y2, x, "4.2 KITCHEN PERFORMANCE TEST (KPT)", "Arimo", 15, "white", titlecolor, 9, titlecolor, "left", "bold");
                        formatText(y2, 11, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "right", "bold");
                        formatText(y2 + 1, 11, "Note: The KPT Sample is convertNumberToText of paired data from KPT Day 1 and Day 2 tests, whereby the exact same beneficiaries are revisited after 24 hours.", "Arimo", 9, "grey", "white", 1, "white", "right", "normal");
                        sumsheet.getRange(y2 + 1, 11).setBorder(true, false, false, false, false, false, titlecolor, SpreadsheetApp.BorderStyle.SOLID);
                        sumsheet.getRange(y2 + 1, 11).setWrap(false);
                        y2++;
                        y2++;
                        y2++;
                        nn = y2;
                        formatTableHeader(y2);
                        y2++;

                        formatText(y2, 2, "Average WOOD fuel used for cooking (Kg)", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        var yy = y2;
                        formatText(y2, 5, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        y2++;

                        formatText(y2, 2, "Average LIQUID fuel used for cooking (Kg)", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        formatText(y2, 5, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        y2++;

                        formatText(y2, 2, "Average GAS fuel used for cooking (Kg)", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        formatText(y2, 5, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        y2++;

                        formatText(y2, 2, "Average other fuel used for cooking (Kg)", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        formatText(y2, 5, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        sumsheet.getRange(y2 - 3, 5, 4, 3).setconvertNumberToTextFormat('#,##0.00;[Red](#,##0.00)');
                        y2++;
                        formatText(y2, 2, "Note: Liquid fuel used primarily consists of ethanol and kerosene, while gas fuel used primarily consists of Liquefied Petroleum Gas (LPG) or Liquefied Natural Gas (LNG).", "Arimo", 9, "grey", "white", 6, "white", "left", "normal");
                        y2++;
                        y2++;

                        var ttb = generateTableAverageIndicator42Kitchen();
                        Logger.log(ttb);
                        tn++;
                        formatTablesAddMerge(y2, 2, ttb, 'Table ' + tn + ': Average convertNumberToText of kg of fuel used for cooking per targeted HH per person, per day');
                        sumsheet.getRange(y2 - ttb.length + 1, 3, ttb.length - 2, 1).setconvertNumberToTextFormat("0.0");
                        sumsheet.getRange(y2 - ttb.length + 1, 4, ttb.length - 2, 1).setconvertNumberToTextFormat("0.00");
                        sumsheet.getRange(y2 - ttb.length + 1, 6, ttb.length - 2, 2).setconvertNumberToTextFormat("0.00");
                        sumsheet.getRange(y2 - ttb.length + 1, 5, ttb.length - 2, 1).setconvertNumberToTextFormat("0.00");
                        sumsheet.getRange(y2 - ttb.length + 1, 7, ttb.length - 2, 1).setconvertNumberToTextFormat("0.00_);[Red](0.00)");
                        sumsheet.getRange(y2 - ttb.length, 2, 1, 6).setWrap(true);
                        sumsheet.getRange(y2 - ttb.length, 2, 1, 6).setVerticalAlignment("bottom");
                        sumsheet.getRange(y2 - 2, 3, 1, 5).setFontWeight("Normal");

                        formatText(y2 - 1, 2, "Note 1). To limit outliers and ensure valid data only is included in the average for each baseline and endline value, data are excluded when: a). the enumerator notes that the KPT test did not go according to plan; b). if the household was hosting an irregular cooking event such as a festival, party, or funeral which required additional cooking to feed guests; c). when Day 1 and Day 2 results cannot be matched by household; and d). when the second day of a KPT finds the fuel remaining exceeds the day 1 measure. Finally, only fuel used by households is reported on, or if a household does not use a certain fuel in the 24 hour KPT, it is not reported on. 2). To calculate the average household size at meals we took the average of the convertNumberToText of people cooked for at the main meals- lunch and dinner. Data for these indicators have been top and bottom coded at the 2% and 98% level to control for outliers.", "Arimo", 9, "grey", "white", 6, "white", "left", "normal");
                        sumsheet.getRange(y2 - 1, 2).setFontWeight("normal");
                        y2++;
                        table = ttb.concat();

                        var ibb1 = new Array();
                        var iee1 = new Array();

                        for (uuu = 0; uuu < 4; uuu++) {
                            ibb1[uuu] = 0;
                            iee1[uuu] = 0;
                        }

                        for (uu = 0; uu < ttb.length - 2; uu++) {

                            if (ttb[uu + 2][0].indexOf("Wood fuel") > -1) {
                                ibb1[0] = ttb[uu + 2][2];
                                iee1[0] = ttb[uu + 2][4];
                            }

                            if (ttb[uu + 2][0].indexOf("Liquid fuel") > -1) {
                                ibb1[1] = ttb[uu + 2][2];
                                liquidkg = ibb1[1];
                                iee1[1] = ttb[uu + 2][4];
                            }

                            if (ttb[uu + 2][0].indexOf("Gas fuel") > -1) {
                                ibb1[2] = ttb[uu + 2][2];
                                iee1[2] = ttb[uu + 2][4];
                            }

                            if (ttb[uu + 2][0].indexOf("Other") > -1 || ttb[uu + 2][0].indexOf("Charcoal fuel") > -1) {
                                ibb1[3] = ibb1[3] + ttb[uu + 2][2];
                                iee1[3] = iee1[3] + ttb[uu + 2][4];
                            }
                        }

                        for (uy = 0; uy < 4; uy++) {
                            formatText(yy + uy, 5, ibb1[uy], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                            if (totaloutputel > 0) {
                                formatText(yy + uy, 6, iee1[uy], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                                if (totaloutputbl > 0) { formatText(yy + uy, 7, iee1[uy] - ibb1[uy], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold"); }
                            }
                        }
                        sumsheet.getRange(yy - uy, 5, uy, 3).setconvertNumberToTextFormat("0.00_);[Red](0.00)");

                        woodkg = ibb1[0];
                        liquidkg = ibb1[1];
                        gaskg = ibb1[2];
                        otherkg = ibb1[3];
                        woodkg2 = iee1[0];
                        liquidkg2 = iee1[1];
                        gaskg2 = iee1[2];
                        otherkg2 = iee1[3];
                        displayNarrativeIndicator42KPT(nn, 9, ttb.length + 5);
                    }
                }
            }

  • 4.3 FUEL EXPENDITURE

This is the image caption

Screenshot of 4.3 FUEL EXPENDITURE

 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
64
65
66
67
68
69
70
71
72
73
74
            { /*4.3 FUEL Expenditure*/

                if (IndicatorYN('O1') == 1) {
                    totaloutputbl = Count(jsonData, ["AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_BuyFuel"], ["Yes", "No"], "Baseline", 0);
                    totaloutputel = Count(jsonData, ["AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_BuyFuel"], ["Yes", "No"], "Endline", 0);

                    if (totaloutputel + totaloutputbl == 0) { return; }

                    var temp1 = Count(jsonData, ["AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_BuyFuel"], ["Yes"], "Baseline", 0);
                    var temp2 = Count(jsonData, ["AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_BuyFuel"], ["No"], "Endline", 0);

                    formatText(y2 - 1, 11, "", "Arimo", 11, "black", "white", 1, "white", "right", "0");
                    formatText(y2, x, "4.3 generateTableValueIndicator42Fuel ON FUEL", "Arimo", 15, "white", titlecolor, 9, titlecolor, "left", "bold");
                    formatText(y2, 11, "", "Arimo", 15, "white", titlecolor, 1, titlecolor, "right", "bold");
                    y2++;
                    y2++;
                    nn = y2;

                    formatTableHeader(y2);
                    y2++;

                    var h = 0;

                    formatText(y2, 2, "Monthly average fuel generateTableValueIndicator42Fuel for all fuels (USD)", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                    var yy = y2;
                    formatText(y2, 5, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                    formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                    formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                    y2++;
                    y2++;

                    var ttb = generateTableValueIndicator42Fuel(jsonData, ['Wood', 'Liquid fuel', 'Gas', 'Charcoal', 'Briquettes', 'Biogas', 'Other'], ['AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Wood', 'AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Liquid_Fuel', 'AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Cooking_Gas', 'AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Charcoal', 'AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Briquettes', 'AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Biogas', 'AREA1_COMMON/O1_generateTableValueIndicator42Fuel_Fuel/O1_Amount_Fuel/O1_Amount_Fuel_Other'], "0");
                    tn++;
                    formatTablesAddMerge(y2, x, ttb, 'Table ' + tn + ': Average HH generateTableValueIndicator42Fuel on all fuels for cooking per month');
                    formatText(y2 - 1, 2, "Note: 1). At baseline (endline), the total sample size is " + totaloutputbl + " (" + totaloutputel + ")" + " beneficiaries, including " + temp1 + " (" + temp2 + ")  persons who reported not having purchased any fuels during the month. 2). The exchange rate used for conversion  is " + Math.round(ex * 1000000) / 1000000 + " USD per " + localcurrency + " (U.S. Treasury, Dec. 2016). 3). Data has been top and bottom coded at the 2% and 98% level to control for outliers. ", "Arimo", 9, "grey", "white", 6, "white", "left", "normal");
                    sumsheet.getRange(y2 - 1, 2).setFontWeight("normal");
                    sumsheet.getRange(y2 - ttb.length, 2, 1, 6).setWrap(true);
                    sumsheet.getRange(y2 - ttb.length, 2, ttb.length, 1).setWrap(true);
                    sumsheet.getRange(y2 - ttb.length + 1, x + 2, ttb.length - 1, 1).setconvertNumberToTextFormat("0.00");
                    sumsheet.getRange(y2 - ttb.length + 1, x + 4, ttb.length - 1, 2).setconvertNumberToTextFormat("0.00");
                    sumsheet.getRange(y2 - ttb.length + 1, x + 5, ttb.length - 1, 2).setconvertNumberToTextFormat("0.00_);[Red](0.00)");

                    y2++;

                    //FOR TANZANIA WORKROUND (THIS IS NOT SUSTAINABLE)
                    if ((totaloutputbl + totaloutputel) == 0) { return; }

                    h = h + ttb.length + 2;

                    table = ttb.concat();

              

                    var ib1 = 0;
                    var ie1 = 0;
                    for (uu = 0; uu < ttb.length - 2; uu++) {
                        if (ttb[uu + 2][0].indexOf("TOTAL") > -1) {
                            ib1 = ttb[uu + 2][2];
                            ie1 = ttb[uu + 2][4];
                        }
                    }

                    totalusd = ib1;
                    totalusd2 = ie1;
                    formatText(yy, 5, ib1, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                    if (totaloutputel > 0) {
                        formatText(yy, 6, ie1, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        if (totaloutputbl > 0) { formatText(yy, 7, ie1 - ib1, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold"); }
                    }
                    sumsheet.getRange(yy, 5, 1, 3).setconvertNumberToTextFormat("0.00_);[Red](0.00)");

                    displayNarrativeIndicator43Fuel(nn, 9, h + 1);
                }
            }

Function displayDataIndicator5Output2_energy

displayDataIndicator5Output2_energy()

The function generates and displays data for 5. AREA 2: COMMUNITY AND HOUSEHOLD LIGHTING

    1. AREA 2: COMMUNITY AND HOUSEHOLD LIGHTING

This is the image caption

Screenshot of 5. AREA 2: COMMUNITY AND HOUSEHOLD LIGHTING

 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
    function displayDataIndicator5Output2_energy() {
        /*Count baseline for Output */
        totaloutputbl = countTotalNumCategoryIndicator2Agr(jsonData, "PARTNER_INFO/Output", ['SL', 'PLD', 'CLP'], "Baseline", "1");
        totaloutputel = countTotalNumCategoryIndicator2Agr(jsonData, "PARTNER_INFO/Output2", ['SL', 'PLD', 'CLP'], "Endline", "1");
        totaloutput = totaloutputbl + totaloutputel;

        if (IndicatorYN('O2') == 1 && totaloutput > 0) {
            x2 = 6; x = 2;
            titlecolor = "#b45f06";
            graphcolor = "#fce5cd";

            /*5. AREA 2: COMMUNITY AND HOUSEHOLD LIGHTING*/
            formatText(y2, 11, "", "Arimo", 11, "black", "white", 1, "white", "right", "0");
            y2++;
            formatText(y2, x, "5. AREA 2: COMMUNITY AND HOUSEHOLD LIGHTING", "Arimo", 18, "white", titlecolor, 8, titlecolor, "left", "bold");
            formatText(y2, 10, "", "Arimo", 24, "white", titlecolor, 2, titlecolor, "right", "bold");
            sumsheet.setRowHeight(y2, 50);

            if (totaloutput == 0) {
                y2++;
                y2++;
                return;
            }
            { //5.1 INTERVENTIONS
                y2++;
                y2++;

                formatText(y2, 2, "Total convertNumberToText of forcibly displaced targeted", "Arimo", 15, "black", graphcolor, 3, graphcolor, "left", "0");
                formatText(y2, 5, tpocbene2, "Arimo", 18, "black", graphcolor, 1, graphcolor, "right", "bold");
                sumsheet.getRange(y2, 5).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');

                formatText(y2, 7, "Total convertNumberToText of host beneficiaries targeted", "Arimo", 15, "black", graphcolor, 4, graphcolor, "left", "0");
                formatText(y2, 11, thbene2, "Arimo", 18, "black", graphcolor, 1, graphcolor, "right", "bold");
                sumsheet.getRange(y2, 11).setconvertNumberToTextFormat('#,##0;[Red](#,##0)');
                y2++;
                y2++;

                if (totaloutputel > 0) { var msgend = " and " + totaloutputel + " at endline" } else { var msgend = ""; }
                var msgtemp = "For Area 2: 'Community and Household Lighting', " + String(tpocbene2).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " forcibly displaced and " + String(thbene2).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " host beneficiaries were targeted for the program, of which " + totaloutputbl + " beneficiaries were surveyed at baseline" + msgend + "."
                formatText(y2, 2, msgtemp, "Arimo", 14, "black", "white", 10, "white", "left", "0");

                y2++;
                y2++;
                var nn = y2;

                // Intervention

                var ttb = generateTablePercentIndicatorImpact_energy([['SL'], ['PLD'], ['CLP']], ['Street Lighting', 'Potable Lights Provided', 'Cash for Light'], jsonData, ["PARTNER_INFO/Output", "PARTNER_INFO/Output2"], "1");
                tn++;
                formatTables(y2, x + 1, ttb, 'Table ' + tn + ': convertNumberToText of beneficiaries per energy intervention areas surveyed');
                multdisplayLabelIndicator4Ouput1(y2, x);
                displayNarrativeIndicator45Interventions(nn, 9, 5, tn);
                if (ttb.length < 5) {
                    y2++;
                }
                y2++;
            }

  • The information on the average amount of assistance

 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
            //PARTNER SURVEY DATA
            {
                if (jsonPartner.length > 0) {
                    var cx = 0;

                    //Average solid fuel
                    var nominator = 0;
                    var denominator = 0;
                    nominator = nominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT2/O2_CLP_001/O2_CLP_Cash_Cost");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT2/O2_CLP_001/O2_CLP_Actual");
                    denominator = denominator + countNumItemsPartnerData(jsonPartner, "G_OUTPUT2/O2_CLP_001/O2_CLP_Actual_Host");
                    if (denominator > 0) {
                        formatText(y2, 2, "Average cash received/monetary equivalent of voucher for lighting per HH per year", "Arimo", 15, "black", graphcolor, 8, graphcolor, "left", "0");
                        formatText(y2, 10, String(Math.round(((nominator * pexr) / denominator) * 100) / 100).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " Kg", "Arimo", 18, "black", graphcolor, 2, graphcolor, "right", "bold");

                        y2++;
                        y2++;
                        cx++;
                    }

                    if (cx > 0) {
                        formatText(y2 - 1, 2, "Note: The information on the average amount of assistance provided has been provided at the end of the fiscal year by the implementing partner " + partnernamesp + ".", "Arimo", 10, "grey", "white", 10, "white", "left", "0");
                        y2++;
                    }
                }
            }

  • 5.1 COMMUNITY LIGHTING: PERCEPTION OF SAFETY

1_structure\images/dataanalysis/energy/country_analysis_energy_5_1.png:width:400px:align:center:alt:Thisistheimagecaption

Screenshot of 5.1 COMMUNITY LIGHTING: PERCEPTION OF SAFETY

 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
            { /*5.1 COMMUNITY LIGHTING: PERCEPTION OF SAFETY*/
                if (IndicatorYN('SCC2') == 1) {

                    totaloutputbl = Count(jsonData, "PARTNER_INFO/Output", ['SL'], "Baseline", "1");
                    totaloutputel = Count(jsonData, "PARTNER_INFO/Output2", ['SL'], "Endline", "1");
                    totaloutput = totaloutputbl + totaloutputel;

                    if (totaloutputbl > 0 || totaloutputel > 0) {
                        formatText(y2 - 1, 11, "", "Arimo", 11, "black", "white", 1, "white", "right", "0");
                        formatText(y2, x, "5.1 COMMUNITY LIGHTING: PERCEPTION OF SAFETY", "Arimo", 15, "white", titlecolor, 9, titlecolor, "left", "bold");
                        formatText(y2, 11, "", "Arimo", 15, "white", titlecolor, 1, titlecolor, "right", "bold");
                        y2++;
                        y2++;
                        formatTableHeader(y2);
                        nn = y2;
                        y2++;

                        // table2 = table3101.concat(); ;
                        // narrative310(y2 - 1, 9, 3);
                        // table = table311.concat(); ;
                        // table2 = table312.concat(); ;
                        // narrative311(y2 - 1, 9, table311.length);

                        formatText(y2, 2, "% reporti feeling safe at night out in the community", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        var yy = y2;

                        formatText(y2, 5, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        y2++;

                        sumsheet.getRange(y2 - 1, 5, 1, 3).setconvertNumberToTextFormat('0%;[Red](0%)');
                        y2++;

                        var ttb = generateTablePercentIndicatorImpactChange([['Yes'], ['Sometimes'], ['No']], ['Yes', 'Sometimes', 'No'], jsonData, ["COMMUNITY_LIGHTING/O2_COM_LIGHTING_Safety_Outside"], "11");
                        tn++;
                        formatTablesAddMerge(y2, x, ttb, 'Table ' + tn + ': % of individuals who self report feeling safe at night outside in the community');

                        safec = ttb[2][2];
                        unsafec = ttb[4][2];
                        safec2 = ttb[2][4];
                        unsafec2 = ttb[4][4];
                        displayNarrativeIndicator52StreetSafety(nn, 9, ttb.length);

                        formatText(yy, 5, ttb[2][2], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        if (totaloutputel > 0) {
                            formatText(yy, 6, ttb[2][4], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                            if (totaloutputbl > 0) { formatText(yy, 7, ttb[2][4] - ttb[2][2], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold"); }
                        }
                        yy++;
                    }
                }
            }

  • 5.2 HOUSEHOLD LIGHTING: RETENTION AND UTLISATION OF PORTABLE LIGHTS

This is the image caption

Screenshot of 5.2 HOUSEHOLD LIGHTING: RETENTION AND UTLISATION OF PORTABLE LIGHTS

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
            { /*5.2 HOUSEHOLD LIGHTING: RETENTION AND UTLISATION OF PORTABLE LIGHTS*/
                if (IndicatorYN('PLRF2') == 1) {

                    totaloutputbl = Count(jsonData, "PARTNER_INFO/Output", ['PLD'], "Baseline", "1");
                    totaloutputel = Count(jsonData, "PARTNER_INFO/Output2", ['PLD'], "Endline", "1");
                    totaloutput = totaloutputbl + totaloutputel;

                    if (totaloutputbl > 0 || totaloutputel > 0) {
                        formatText(y2 - 1, 11, "", "Arimo", 11, "black", "white", 1, "white", "right", "0");
                        formatText(y2, x, "5.2 HOUSEHOLD LIGHTING: RETENTION AND UTLISATION OF PORTABLE LIGHTS", "Arimo", 15, "white", titlecolor, 9, titlecolor, "left", "bold");
                        formatText(y2, 11, "", "Arimo", 15, "white", titlecolor, 1, titlecolor, "right", "bold");
                        y2++;
                        y2++;
                        nn = y2;
                        formatTableHeader(y2);
                        y2++;

                        var h = 0;

                        formatText(y2, 2, "% Retaining portable lights", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        var yy = y2;
                        formatText(y2, 5, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        y2++;

                        formatText(y2, 2, "% portable lights retained & functioning", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        formatText(y2, 5, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        y2++;

                        formatText(y2, 2, "% Utilising portable lights as primary source", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        formatText(y2, 5, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");

                        y2++;
                        y2++;

                        var ie1 = 0;
                        var ie2 = 0;
                        var ie3 = 0;
                        var ib3 = 0;

                        if (totaloutputel > -1) { //hide by changing it to zero
                            var ttb = generateTablePercentIndicatorImpactChange([['Yes'], ['No']], ['Retained', 'Not Retained'], jsonData, ["DISTRIBUTION_HHLIGHTS/O2_TypeLights_UNHCR_Showme"], "11");
                            tn++;
                            //DELETE INFO FROM TABLE
                            for (tr = 0; tr < ttb.length; tr++) {
                                ttb[tr][1] = "";
                                ttb[tr][2] = ttb[tr][0];
                                ttb[tr][0] = "";
                            }
                            formatTables(y2, x + 1, ttb, 'Table ' + tn + ': % of targeted HH that retain UNHCR portable lights');
                            sumsheet.getRange(y2 - ttb.length, 5, ttb.length, 1).setFontWeight("bold");
                            if (endline == 0) {
                                formatText(y2 - 1, 2, "Note: The data will be available only after the endline survey", "Arimo", 9, "grey", "white", 6, "white", "right", "normal");
                                sumsheet.getRange(y2 - 1, 2).setFontWeight("normal");
                                y2++;
                            }
                            h = h + ttb.length;

                            for (uu = 0; uu < ttb.length - 2; uu++) {
                                if (ttb[uu + 2][2] == "Retained") {
                                    ie1 = ttb[uu + 2][4];
                                }
                            }

                            var ttb = generateTablePercentIndicatorImpactChange([['Yes'], ['No']], ['Works', 'Not work'], jsonData, ["DISTRIBUTION_HHLIGHTS/O2_TypeLights_UNHCR_Works"], "11");
                            tn++;
                            //DELETE INFO FROM TABLE
                            for (tr = 0; tr < ttb.length; tr++) {
                                ttb[tr][1] = "";
                                ttb[tr][2] = ttb[tr][0];
                                ttb[tr][0] = "";
                            }
                            formatTables(y2, x + 1, ttb, 'Table ' + tn + ': % of targeted HH that can demostrate how the portable lights work');
                            sumsheet.getRange(y2 - ttb.length, 5, ttb.length, 1).setFontWeight("bold");
                            if (endline == 0) {
                                formatText(y2 - 1, 2, "Note: The data will be available only after the endline survey", "Arimo", 9, "grey", "white", 6, "white", "right", "normal");
                                sumsheet.getRange(y2 - 1, 2).setFontWeight("normal");
                                y2++;
                            } else {
                                formatText(y2 - 1, 2, "Note: Only those who retain the portable light are requested to demostrate how the portable light works, hence the sample size is a subsample of those who retain the portable light.", "Arimo", 9, "grey", "white", 6, "white", "right", "normal");
                                sumsheet.getRange(y2 - 1, 2).setFontWeight("normal");
                                y2++;
                            }
                            h = h + ttb.length;

                            for (uu = 0; uu < ttb.length - 2; uu++) {
                                if (ttb[uu + 2][2] == "Works") {
                                    ie2 = ttb[uu + 2][4];
                                }
                            }
                        }

                        var ttb = generateTableValueIndicator52Light(jsonData, "DISTRIBUTION_HHLIGHTS/O2_TypeLights_UNHCR", "DISTRIBUTION_HHLIGHTS/O2_Main_Light");
                        tn++;
                        formatTablesAddMerge(y2, x, ttb, 'Table ' + tn + ': % of HH using UNHCR and/or partner portable lights as primary light source');
                        formatText(y2 - 1, 2, "Note: The table indicates if the beneficiaries use the portable lights provided by UNHCR and/or partners as primary source of lighting in the household. At the baseline, as the portable lights are not yet distributed, the percent of those utilising indicates zero.", "Arimo", 9, "grey", "white", 6, "white", "right", "normal");
                        sumsheet.getRange(y2 - 1, 2).setFontWeight("normal");
                        y2++;
                        h = h + ttb.length;

                        for (uu = 0; uu < ttb.length - 2; uu++) {
                            if (ttb[uu + 2][0] == "Utilising") {
                                ib3 = ttb[uu + 2][2];
                                ie3 = ttb[uu + 2][4];
                            }
                        }

                        var ttb = generateTablePercentIndicatorImpactChange([['Solar'], ['Torch_Battery'], ['Electricity'], ['Rechargeable_lantern'], ['Kerosene_gas_lantern'], ['Home_Solar_System'], ['Mobilephone'], ['Disposable_lantern'], ['Candles'], ['Firewood'], ['Other']], ['Solar', 'Battery torch', 'Electricity', 'Rechargeable lantern', 'Kerosene/gas lantern', 'Home solar system', 'Mobile phone', 'Disposable lantern', 'Candles', 'Firewood', 'Other'], jsonData, ["DISTRIBUTION_HHLIGHTS/O2_Main_Light"], "0");
                        tn++;
                        formatTablesAddMerge(y2, x, ttb, 'Table ' + tn + ': Types of primary light sources used by beneficiaries surveyed');
                        formatText(y2 - 1, 2, "Note: The table indicates all types of primary sources of lighting used in the beneficiary households, including the portable lights provided by UNHCR and/or partners.", "Arimo", 9, "grey", "white", 6, "white", "right", "normal");
                        sumsheet.getRange(y2 - 1, 2).setFontWeight("normal");
                        sumsheet.getRange(y2 - ttb.length, 2, ttb.length, 1).setWrap(true);
                        for (yi = y2 - ttb.length + 1; yi < y2; yi++) {
                            sumsheet.setRowHeight(yi, 30);
                        }
                        y2++;
                        h = h + ttb.length;
                        table = ttb.concat();

                        formatText(yy, 5, "NA", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        if (totaloutputel > 0) {
                            formatText(yy, 6, ie1, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                            if (totaloutputbl > 0) { formatText(yy, 7, ie1, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold"); }
                        }
                        yy++;

                        formatText(yy, 5, "NA", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        if (totaloutputel > 0) {
                            formatText(yy, 6, ie2, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                            if (totaloutputbl > 0) { formatText(yy, 7, ie2, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold"); }
                        }
                        yy++;

                        formatText(yy, 5, ib3, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        if (totaloutputel > 0) {
                            formatText(yy, 6, ie3, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                            if (totaloutputbl > 0) { formatText(yy, 7, ie3, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold"); }
                        }
                        sumsheet.getRange(yy - 2, 5, 3, 3).setconvertNumberToTextFormat('0%;[Red](0%)');
                        yy++;

                        //TYPE OF LIGHTS DISTRIBUTED
                        {
                            var lighttype = "";
                            var stt = "";
                            for (st = 0; st < jsonData.length; st++) {

                                if (filterMain(st, jsonData, Country, Partner, Camp, Status, Year, Gender, Age, "ALL", '0', '0') != 9) {
                                    continue;
                                }

                                if (jsonData[st]["DISTRIBUTION_HHLIGHTS/O2_TypeLights_UNHCR"] != null) {
                                    if (stt.indexOf(jsonData[st]["DISTRIBUTION_HHLIGHTS/O2_TypeLights_UNHCR"]) == -1) { stt = stt + jsonData[st]["DISTRIBUTION_HHLIGHTS/O2_TypeLights_UNHCR"] + ", "; }
                                }
                            }


                            if (stt.indexOf("SolarL") > -1) { lighttype = lighttype + "Solar Light, " }
                            if (stt.indexOf("Electricity") > -1) { lighttype = lighttype + "Electricity light, " }
                            if (stt.indexOf("Kerosene_Gas") > -1) { lighttype = lighttype + "Kerosene / gas light, " }
                            if (stt.indexOf("Battery_Torch") > -1) { lighttype = lighttype + "Battery touch, " }
                            if (stt.indexOf("HomeSolarSystem") > -1) { lighttype = lighttype + "Home Solar System, " }

                            var lighttype = lighttype.toLocaleLowerCase();
                            var ax = lighttype.lastIndexOf(', ');
                            lighttype = lighttype.substring(0, ax);


                            var ax = lighttype.lastIndexOf(',');
                            if (ax != -1) {
                                lighttype = lighttype.substring(0, ax) + ' and' + lighttype.substring(ax + 1);
                            }


                        }


                        retentionlights = ie1,
                            functionlight = ie2,
                            utlisationlight = ie3;
                        displayNarrativeIndicator52HHLighting(nn, 9, h + 3, lighttype);
                    }
                }
            }

  • 5.3 HOUSEHOLD LIGHTING: EXPENDITURE ON HH LIGHTING

This is the image caption

Screenshot of 5.3 HOUSEHOLD LIGHTING: EXPENDITURE ON HH LIGHTING

 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
            { /*5.3 HOUSEHOLD LIGHTING: generateTableValueIndicator42Fuel ON HH LIGHTING*/
                if (IndicatorYN('SCC22') == 1) {

                    totaloutputbl = Count(jsonData, "PARTNER_INFO/Output", ['PLD', 'CLP'], "Baseline", "1");
                    totaloutputel = Count(jsonData, "PARTNER_INFO/Output2", ['PLD', 'CLP'], "Endline", "1");
                    totaloutput = totaloutputbl + totaloutputel;

                    if (totaloutputbl > 0 || totaloutputel > 0) {
                        formatText(y2 - 1, 11, "", "Arimo", 11, "black", "white", 1, "white", "right", "0");
                        formatText(y2, x, "5.3 HOUSEHOLD LIGHTING: generateTableValueIndicator42Fuel ON HH LIGHTING", "Arimo", 15, "white", titlecolor, 9, titlecolor, "left", "bold");
                        formatText(y2, 11, "", "Arimo", 15, "white", titlecolor, 1, titlecolor, "right", "bold");
                        y2++;
                        y2++;
                        nn = y2;

                        formatTableHeader(y2);
                        y2++;
                        var h = 0;

                        formatText(y2, 2, "Average amount spent on lighting last month (USD)", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        var yy = y2;
                        formatText(y2, 5, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        sumsheet.getRange(y2, 5, 1, 3).setconvertNumberToTextFormat('#,##0.0;[Red](#,##0.0)');
                        y2++;
                        y2++;

                        var ttb = generateTableValueIndicator42Fuel(jsonData, ['Battery torch', 'Electricity', 'Kerosene/gas', 'Candles', 'Mobile phone', 'Firewood', 'Other'], ['HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_BatteryTouch', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Electricity', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_KeroseneGas', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Candles', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Phones', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Firewood', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Other'], "0");
                        tn++;
                        formatTablesAddMerge(y2, x, ttb, 'Table ' + tn + ': Average amount spent on lighting last month');
                        formatText(y2 - 1, 2, "Note: 1). At baseline (endline), the total sample size is " + totaloutputbl + " (" + totaloutputel + ")" + " beneficiaries. 2). The exchange rate used for conversion is " + Math.round(ex * 1000000) / 1000000 + " USD per " + localcurrency + " (the average of US Treasury exchange rate using the spot rates quartely published during the previous year). 3). Values have been top and bottom coded at the 2% and 98% level to control for outliers.", "Arimo", 9, "grey", "white", 6, "white", "left", "normal");
                        sumsheet.getRange(y2 - 1, 2).setFontWeight("normal");
                        sumsheet.getRange(y2 - ttb.length, 2, 1, 6).setWrap(true);
                        sumsheet.getRange(y2 - ttb.length, 2, ttb.length, 1).setWrap(true);
                        sumsheet.getRange(y2 - ttb.length + 1, x + 2, ttb.length - 1, 1).setconvertNumberToTextFormat("0.00");
                        sumsheet.getRange(y2 - ttb.length + 1, x + 4, ttb.length - 1, 2).setconvertNumberToTextFormat("0.00");
                        sumsheet.getRange(y2 - ttb.length + 1, x + 5, ttb.length - 1, 2).setconvertNumberToTextFormat("0.00_);[Red](0.00)");
                        for (yi = y2 - ttb.length + 1; yi < y2; yi++) {
                            sumsheet.setRowHeight(yi, 30);
                        }
                        y2++;
                        h = h + ttb.length + 2;

                        //var ttb = generateTableValueIndicator42Fuel(jsonData, ['Battery touch', 'Electricity', 'Kerosene/gas', 'Candles', 'Mobilephone', 'Firewood'], ['HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_BatteryTouch', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Electricity', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_KeroseneGas', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Candles', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Phones', 'HH_LIGHTING/O2_Amount_Light/O2_Amount_Light_Firewood'], "1");
                        //tn++;
                        //formatTablesAddMerge(y2, x, ttb, 'Table ' + tn + ': Average amount spent on lighting last month (USD)');
                        //sumsheet.getRange(y2 - ttb.length + 1, x + 2, ttb.length - 1, 1).setconvertNumberToTextFormat("0.00");
                        //sumsheet.getRange(y2 - ttb.length + 1, x + 4, ttb.length - 1, 1).setconvertNumberToTextFormat("0.00");
                        //formatText(y2 - 1, 2, "Note: The exchange rate used for conversion is " + ex + " USD per " + localcurrency + ".", "Arimo", 9, "grey", "white", 6, "white", "right", "normal");
                        //sumsheet.getRange(y2 - 1, 2).setFontWeight("normal");
                        //y2++;
                        //h = h + ttb.length;
                        table = ttb.concat();

                        var ib1 = 0;
                        var ie1 = 0;
                        for (uu = 0; uu < ttb.length - 2; uu++) {
                            if (ttb[uu + 2][0].indexOf("TOTAL") > -1) {
                                ib1 = ttb[uu + 2][2];
                                ie1 = ttb[uu + 2][4];
                            }
                        }

                        formatText(yy, 5, ib1, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        if (totaloutputel > 0) {
                            formatText(yy, 6, ie1, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                            if (totaloutputbl > 0) { formatText(yy, 7, ie1 - ib1, "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold"); }
                        }
                        sumsheet.getRange(yy, 5, 1, 3).setconvertNumberToTextFormat("0.00_);[Red](0.00)");

                        totalusd = ib1;
                        totalusd2 = ie1;
                        displayNarrativeIndicator53HHLighting(nn, 9, h + 1);
                    }
                }

            }

  • 5.4 HOUSEHOLD LIGHTING: PERCEPTION OF SAFETY

This is the image caption

Screenshot of 5.4 HOUSEHOLD LIGHTING: PERCEPTION OF SAFETY

 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
64
65
66
            { /*5.4 HOUSEHOLD LIGHTING: PERCEPTION OF SAFETY*/
                if (IndicatorYN('SCC22') == 1) {

                    if (totaloutputbl > 0 || totaloutputel > 0) {
                        formatText(y2 - 1, 11, "", "Arimo", 11, "black", "white", 1, "white", "right", "0");
                        formatText(y2, x, "5.4 HOUSEHOLD LIGHTING: PERCEPTION OF SAFETY", "Arimo", 15, "white", titlecolor, 9, titlecolor, "left", "bold");
                        formatText(y2, 11, "", "Arimo", 15, "white", titlecolor, 1, titlecolor, "right", "bold");
                        y2++;
                        y2++;

                        formatTableHeader(y2);
                        nn = y2;
                        y2++;

                        formatText(y2, 2, "% report feeling safe at night in own home", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        var yy = y2;
                        formatText(y2, 5, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        y2++;

                        formatText(y2, 2, "% report feeling safe at night out in the community", "Arimo", 12, "white", titlecolor, 3, titlecolor, "left", "bold");
                        formatText(y2, 5, "", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 6, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        formatText(y2, 7, " ", "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        sumsheet.getRange(y2 - 1, 5, 2, 3).setconvertNumberToTextFormat('0%;[Red](0%)');
                        y2++;

                        y2++;

                        var ttb = generateTablePercentIndicatorImpactChange([['Yes'], ['Sometimes'], ['No']], ['Yes', 'Sometimes', 'Not safe'], jsonData, ["HH_LIGHTING/O2_HH_LIGHTING_Inside_Safety"], "11");
                        tn++;
                        formatTablesAddMerge(y2, x, ttb, 'Table ' + tn + ': % of individuals who self report feeling safe at night inside the house');

                        formatText(yy, 5, ttb[2][2], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        if (totaloutputel > 0) {
                            formatText(yy, 6, ttb[2][4], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                            if (totaloutputbl > 0) { formatText(yy, 7, ttb[2][4] - ttb[2][2], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold"); }
                        }
                        yy++;

                        safeh = ttb[2][2];
                        unsafeh = ttb[4][2];
                        safeh2 = ttb[2][4];
                        unsafeh2 = ttb[4][4];

                        var ttb = generateTablePercentIndicatorImpactChange([['Yes'], ['Sometimes'], ['No']], ['Yes', 'Sometimes', 'No'], jsonData, ["HH_LIGHTING/O2_HH_LIGHTING_Safety_Outside"], "11");
                        tn++;
                        formatTablesAddMerge(y2, x, ttb, 'Table ' + tn + ': % of individuals who self report feeling safe at night outside in the community');

                        safec = ttb[2][2];
                        unsafec = ttb[4][2];
                        safec2 = ttb[2][4];
                        unsafec2 = ttb[4][4];
                        displayNarrativeIndicator54HHSafety(nn, 9, ttb.length * 2);

                        formatText(yy, 5, ttb[2][2], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                        if (totaloutputel > 0) {
                            formatText(yy, 6, ttb[2][4], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold");
                            if (totaloutputbl > 0) { formatText(yy, 7, ttb[2][4] - ttb[2][2], "Arimo", 18, "white", titlecolor, 1, titlecolor, "Right", "bold"); }
                        }
                        yy++;
                    }
                }

            }

Function displayNarrativeIndicator52StreetSafety

displayNarrativeIndicator52StreetSafety(y, x, z)

The function generates the narrative for 5.1 COMMUNITY LIGHTING: PERCEPTION OF SAFETY

Arguments

  • y (string) – row

  • x (string) – column

  • z (string) – position

This is the image caption

Screenshot of narrative

 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
    function displayNarrativeIndicator52StreetSafety(y, x, z) { //FEELING OF SAFETY COMMUNITY

        if (totaloutputel > 0 && totaloutputbl > 0) {

            text = "The percent of beneficiaries who report feeling safe outside their houses moving around the community at night <1><2> from <3> at baseline to <4> at endline. And the percent of those feeling unsafe moving around the community at night <5><6> from <7> to <8>."

            if (Math.round(safec * 100) == Math.round(safec2 * 100)) {
                text = text.replace("<1>", "remained the same");
                text = text.replace("2", " at " + Math.abs(Math.round((safec) * 100)) + "%");
                text = text.replace(" from <3> at baseline to <4> at endline", "");
            }
            else {
                if (Math.round(safec2 * 100) > Math.round(safec * 100)) {
                    text = text.replace("<1>", "increased")
                } else {
                    text = text.replace("<1>", "decreased")
                }

                text = text.replace("<2>", " by " + Math.abs(Math.round((safec2 - safec) * 100)) + "%");
                text = text.replace("<3>", Math.round(safec * 100) + "%");
                text = text.replace("<4>", Math.round(safec2 * 100) + "%");
            }

            if (Math.round(unsafec * 100) == Math.round(unsafec2 * 100)) {
                text = text.replace("while", "and");
                text = text.replace("<5>", "remained the same");
                text = text.replace("<6>", " at " + Math.abs(Math.round((unsafec) * 100)) + "%");
                text = text.replace(" <6> from <7> to <8>", "");
            }
            else {
                if (Math.round(unsafec2 * 100) > Math.round(unsafec * 100)) {
                    text = text.replace("<5>", "increased")
                } else {
                    text = text.replace("<5>", "decreased")
                }

                text = text.replace("<6>", " by " + Math.abs(Math.round((unsafec2 - unsafec) * 100)) + "%");
                text = text.replace("<7>", Math.round(unsafec * 100) + "%");
                text = text.replace("<8>", Math.round(unsafec2 * 100) + "%");
            }

        }
        else if (totaloutputbl > 0 && totaloutputel == 0) {
            text = "At baseline, <4> of beneficiaries report feeling safe outside their houses moving around the community at night. And  <6> report feeling unsafe moving around the community at night.";
            t1 = Math.round(safec * 100) + "%";
            text = text.replace("<4>", t1);

            t1 = Math.round(unsafec * 100) + "%";
            text = text.replace("<6>", t1);
        }
        else if (totaloutputbl == 0 && totaloutputel > 0) {
            text = "At endline, <4> of beneficiaries report feeling safe outside their houses moving around the community at night. And  <6> report feeling unsafe moving around the community at night.";

            t1 = Math.round(safec2 * 100) + "%";
            text = text.replace("<4>", t1);

            t1 = Math.round(unsafec2 * 100) + "%";
            text = text.replace("<6>", t1);
        }

        mergeText(y, x, z, text);
    }

Function displayNarrativeIndicator54HHSafety

displayNarrativeIndicator54HHSafety(y, x, z)

The function generates the narrative for 5.4 HH LIGHTING: SAFETY

Arguments

  • y (string) – row

  • x (string) – column

  • z (string) – position

This is the image caption

Screenshot of narrative

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
    function displayNarrativeIndicator54HHSafety(y, x, z) {
        if (totaloutputel > 0 && totaloutputbl > 0) {
            text = "The percent of beneficiaries who report feeling safe inside their houses at night <1><2> from <3> at baseline to <4> at endline. And the percent of those feeling unsafe <5><6> from <7> to <8>." + String.fromCharCode(10) + String.fromCharCode(10) + "The percent of beneficiaries who report feeling safe outside their houses moving around the community at night <9><10> from <11> at baseline to <12> at endline. And the percent of those feeling unsafe <13><14> from <15> to <16>."

            if (Math.round(safeh * 100) == Math.round(safeh2 * 100)) {
                text = text.replace("<1>", "remained the same");
                text = text.replace("2", " at " + Math.abs(Math.round((safeh) * 100)) + "%");
                text = text.replace(" from <3> at baseline to <4> at endline", "");
            }
            else {
                if (Math.round(safeh2 * 100) > Math.round(safeh * 100)) {
                    text = text.replace("<1>", "increased")
                } else {
                    text = text.replace("<1>", "decreased")
                }

                text = text.replace("<2>", " by " + Math.abs(Math.round((safeh2 - safeh) * 100)) + "%");
                text = text.replace("<3>", Math.round(safeh * 100) + "%");
                text = text.replace("<4>", Math.round(safeh2 * 100) + "%");
            }

            if (Math.round(unsafeh * 100) == Math.round(unsafeh2 * 100)) {
                text = text.replace("<5>", "remained the same");
                text = text.replace("<6>", " at " + Math.abs(Math.round((unsafeh) * 100)) + "%");
                text = text.replace(" <6> from <7> to <8>", "");
            }
            else {
                if (Math.round(unsafeh2 * 100) > Math.round(unsafeh * 100)) {
                    text = text.replace("<5>", "increased")
                } else {
                    text = text.replace("<5>", "decreased")
                }

                text = text.replace("<6>", " by " + Math.abs(Math.round((unsafeh2 - unsafeh) * 100)) + "%");
                text = text.replace("<7>", Math.round(unsafeh * 100) + "%");
                text = text.replace("<8>", Math.round(unsafeh2 * 100) + "%");
            }

            if (Math.round(safec * 100) == Math.round(safec2 * 100)) {
                text = text.replace("<9>", "remained the same");
                text = text.replace("<10>", " at " + Math.abs(Math.round((safec) * 100)) + "%");
                text = text.replace(" from <11> at baseline to <12> at endline", "");
            }
            else {
                if (Math.round(safec2 * 100) > Math.round(safec * 100)) {
                    text = text.replace("<9>", "increased")
                } else {
                    text = text.replace("<9>", "decreased")
                }

                text = text.replace("<10>", " by " + Math.abs(Math.round((safec2 - safec) * 100)) + "%");
                text = text.replace("<11>", Math.round(safec * 100) + "%");
                text = text.replace("<12>", Math.round(safec2 * 100) + "%");
            }

            if (Math.round(unsafec * 100) == Math.round(unsafec2 * 100)) {
                text = text.replace("<13>", "remained the same");
                text = text.replace("<14>", " at " + Math.abs(Math.round((unsafec) * 100)) + "%");
                text = text.replace(" from <15> to <16>", "");
            }
            else {
                if (Math.round(unsafec2 * 100) > Math.round(unsafec * 100)) {
                    text = text.replace("<13>", "increased")
                } else {
                    text = text.replace("<13>", "decreased")
                }

                text = text.replace("<14>", " by " + Math.abs(Math.round((unsafec2 - unsafec) * 100)) + "%");
                text = text.replace("<15>", Math.round(unsafec * 100) + "%");
                text = text.replace("<16>", Math.round(unsafec2 * 100) + "%");
            }
        }
        else if (totaloutputbl > 0 && totaloutputel == 0) {
            text = "At baseline, <44> of beneficiaries report feeling safe inside their houses at night, while <66> report feeling unsafe." + String.fromCharCode(10) + String.fromCharCode(10) + "At the same time, <4> of beneficiaries report feeling safe outside their houses moving around the community at night, while <6> report feeling unsafe.";

            t1 = Math.round(safeh * 100) + "%";
            text = text.replace("<44>", t1);

            t1 = Math.round(unsafeh * 100) + "%";
            text = text.replace("<66>", t1);

            t1 = Math.round(safec * 100) + "%";
            text = text.replace("<4>", t1);

            t1 = Math.round(unsafec * 100) + "%";
            text = text.replace("<6>", t1);
        }
        else if (totaloutputbl == 0 && totaloutputel > 0) {
            text = "At endline, <44> of beneficiaries report feeling safe inside their houses at night, while <66> report feeling unsafe." + String.fromCharCode(10) + String.fromCharCode(10) + "At the same time, <4> of beneficiaries report feeling safe outside their houses moving around the community at night, while <6> report feeling unsafe.";

            t1 = Math.round(safeh2 * 100) + "%";
            text = text.replace("<44>", t1);

            t1 = Math.round(unsafeh2 * 100) + "%";
            text = text.replace("<66>", t1);

            t1 = Math.round(safec2 * 100) + "%";
            text = text.replace("<4>", t1);

            t1 = Math.round(unsafec2 * 100) + "%";
            text = text.replace("<6>", t1);

        }
        mergeText(y, x, z, text);
    }

Function displayNarrativeIndicator41UtilisationStove

displayNarrativeIndicator41UtilisationStove(y, x, z)

The function generates the narrative for 4.1 UTILISATION OF COOKSTOVE

Arguments

  • y (string) – row

  • x (string) – column

  • z (string) – position

This is the image caption

Screenshot of narrative

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
    function displayNarrativeIndicator41UtilisationStove(y, x, z, stovetype) { //UTLISATION STOVE

        if (KPTDAY2PANEL2 > 0 && KPTDAY2PANEL > 0) {
            text = String(KPTDAY2) + " beneficiaries at baseline and " + String(KPTDAY22) + " at endline underwent the Kitchen Performance Test (KPT) to quantitatively measure the effect of stove interventions on household fuel consumption.";
        }
        else if (KPTDAY2PANEL > 0 && KPTDAY2PANEL2 == 0) {
            text = "At baseline, " + String(KPTDAY2) + " beneficiaries underwent the Kitchen Performance Test (KPT) to quantitatively measure the effect of stove interventions on household fuel consumption."
        }
        else if (KPTDAY2PANEL == 0 && KPTDAY2PANEL2 > 0) {
            text = "At endline, " + String(KPTDAY22) + " beneficiaries underwent the Kitchen Performance Test (KPT) to quantitatively measure the effect of stove interventions on household fuel consumption."
        }

        text = text + String.fromCharCode(10) + String.fromCharCode(10) + "<2>";

        if (KPTDAY2PANEL2 > 0) {
            t1 = "In " + Year + " the project supported the following stoves: " + stovetype + "." + String.fromCharCode(10) + String.fromCharCode(10) + "At endline, " + Math.round(retentionstove * 100) + "% of beneficiaries surveyed retained the fuel efficient cookstoves distributed by UNHCR and/or partners, while " + Math.round(utlisationsotve * 100) + "% have utilised the fuel efficient cookstoves provided by UNHCR and/or partners to cook main meals (lunch and dinner)."
        } else {
            t1 = "The indicators for retention and utilisation of fuel efficient cookstoves provided by UNHCR and/or partners will be available only at endline survey - after cookstoves are distributed."
        }

        text = text.replace("<2>", t1);

        if (KPTDAY2PANEL2 > 0 && KPTDAY2PANEL > 0) {
            text = text + String.fromCharCode(10) + String.fromCharCode(10) + "<5>."

            var inc = 0;
            var dc = 0;
            var t1 = "", t2 = "", t3 = "", t4 = "";
            var text2 = "Regarding other stoves utilised for cooking main meals (lunch and dinner), ";

            table = sortTable(table, 5, 1);
            for (s = 2; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) > 0 && table[s][0] != "Fuel efficient cookstove") {
                    if (inc == 0) {
                        t1 = "the use of  " + table[s][0].toLowerCase();
                        t2 = "by " + Math.round(table[s][5] * 100) + "%";
                    }
                    if (inc > 0) {
                        t1 = t1.replace(" and# ", ", ");
                        t1 = t1 + " and# " + table[s][0].toLowerCase();
                        t2 = t2.replace(" and# ", ", ");
                        t2 = t2.replace(" respectively", "");
                        t2 = t2 + " and# " + Math.round(table[s][5] * 100) + "% respectively";
                    }
                    inc++;
                }
            }

            table = sortTable(table, 5, 0);
            for (s = 2; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) < 0 && table[s][0] != "Fuel efficient cookstove") {
                    if (dc == 0) {
                        if (inc > 0) { t3 = ", while "; }
                        t3 = t3 + "the use of " + table[s][0].toLowerCase();
                        t4 = "by " + Math.abs(Math.round(table[s][5] * 100)) + "%";
                    }
                    if (dc > 0) {
                        t3 = t3.replace(" and$ ", ", ");
                        t3 = t3 + " and$ " + table[s][0].toLowerCase();
                        t4 = t4.replace(" and$ ", ", ");
                        t4 = t4.replace(" respectively", "");
                        t4 = t4 + " and$ " + Math.abs(Math.round(table[s][5] * 100)) + "% respectively";
                    }
                    dc++;
                }
            }

            if (inc > 0) { text2 = text2 + t1 + " increased " + t2; }
            if (dc > 0) { text2 = text2 + t3 + " decreased " + t4; }
            text2 = text2.replace("and#", "and");
            text2 = text2.replace("and#", "and");
            text2 = text2.replace("and$", "and");
            text2 = text2.replace("and$", "and");


            text = text.replace("<5>", text2);
        }
        else if (KPTDAY2PANEL > 0 && KPTDAY2PANEL2 == 0) {


            text = text + String.fromCharCode(10) + String.fromCharCode(10) + "At baseline, the majority of beneficiaries report utilising <3> to cook main meals - <4>";
            table = sortTable(table, 1, 1);
            text = text.replace("<3>", table[2][0].toLowerCase());
            text = text.replace("<4>", Math.round(table[2][2] * 100) + "%");

            if (table[3][0] != "TOTAL") {
                text = text + "; while the second largest convertNumberToText of beneficiaries report utilising <5> - <6>";
                text = text.replace("<5>", table[3][0].toLowerCase());
                text = text.replace("<6>", Math.round(table[3][2] * 100) + "%");
            }

            text = text + ".";
        }
        else if (KPTDAY2PANEL == 0 && KPTDAY2PANEL2 > 0) {
            text = text + String.fromCharCode(10) + String.fromCharCode(10) + "At endline, the majority of beneficiaries report utilising <3> to cook main meals - <4>";

            table = sortTable(table, 3, 1);
            text = text.replace("<3>", table[2][0].toLowerCase());
            text = text.replace("<4>", Math.round(table[2][4] * 100) + "%");
            if (table[3][0] != "TOTAL") {
                text = text + "; while the second largest convertNumberToText of beneficiaries report utilising <5> - <6>";
                text = text.replace("<5>", table[3][0].toLowerCase());
                text = text.replace("<6>", Math.round(table[3][4] * 100) + "%");
            }

            text = text + ".";
        }

        mergeText(y, x, z, text);
    }

Function displayNarrativeIndicator42KPT

displayNarrativeIndicator42KPT(y, x, z)

The function generates the narrative for 4.2 KITCHEN PERFORMANCE TEST (KPT)

Arguments

  • y (string) – row

  • x (string) – column

  • z (string) – position

This is the image caption

Screenshot of narrative

 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
    function displayNarrativeIndicator42KPT(y, x, z) { //KPT

        if (KPTDAY2PANEL2 > 0 && KPTDAY2PANEL > 0) {

            text = "<5><6>";

            var inc = 0;
            var dc = 0;
            var t1 = "", t2 = "", t3 = "", t4 = "";
            var text2 = "", text3 = "";

            table = sortTable(table, 5, 1);
            Logger.log(table);
            for (s = 1; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) > 0) {
                    if (inc == 0) {
                        var split = table[s][0].split(String.fromCharCode(10));
                        t1 = split[0].toLowerCase();
                        t1 = t1 + " by " + Math.round(table[s][5] * 100) / 100 + " Kg per person per day";
                    }
                    if (inc > 0) {
                        t1 = t1.replace(" and# for ", ", for ");
                        t1 = t1.replace(" respectively", "");
                        var split = table[s][0].split(String.fromCharCode(10));
                        t1 = t1 + " and# for " + split[0].toLowerCase();
                        t1 = t1 + " by " + Math.round(table[s][5] * 100) / 100 + " Kg per person per day";
                    }
                    inc++;
                }
            }

            table = sortTable(table, 5, 0);
            Logger.log(table);
            for (s = 1; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) < 0) {
                    if (dc == 0) {
                        var split = table[s][0].split(String.fromCharCode(10));
                        t3 = t3 + split[0].toLowerCase();
                        t3 = t3 + " by " + Math.abs(Math.round(table[s][5] * 100) / 100) + " Kg per person per day";
                    }
                    if (dc > 0) {
                        t3 = t3.replace(" and$ for ", ", for ");
                        t3 = t3.replace(" respectively", "");
                        var split = table[s][0].split(String.fromCharCode(10));
                        t3 = t3 + " and$ for " + split[0].toLowerCase(); t3 = t3 + " by " + Math.abs(Math.round(table[s][5] * 100) / 100) + " Kg per person per day";
                    }
                    dc++;
                }
            }

            if (dc > 0) { text2 = "After the introduction of the fuel efficient cookstove at endline the average amount of fuel used for cooking per person per day decreased for " + t3 + "."; }

            if (dc == 0 && inc > 0) { text3 = " After the introduction of the fuel efficient cookstove at endline the average amount of fuel used for cooking per person per day increased for " + t1 + "."; }

            if (inc > 0 && dc > 0) { text3 = text3 + String.fromCharCode(10) + String.fromCharCode(10) + " On the other hand, the average generateTableValueIndicator42Fuel increased for " + t1 + "."; }
            text3 = text3.replace("and#", "and");
            text3 = text3.replace("and#", "and");
            text2 = text2.replace("and$", "and");
            text2 = text2.replace("and$", "and");

            text = text.replace("<5>", text2);
            text = text.replace("<6>", text3);

            text = text.replace("liquid fuel", "liquid fuel (including kerosene, ethanol and petrol)");
            text = text.replace("gas fuel", "gas fuel (including LPG and LNG)");


        }
        else if (KPTDAY2PANEL > 0 && KPTDAY2PANEL2 == 0) {
            text = "The average amount of fuel used for cooking per person per day is <1> for wood fuel, <2> for liquid fuel (including kerosene, ethanol and petrol), <3> for gas fuel (including LPG and LNG), and <4> for other types of fuel (including charcoal, etc.) at baseline."

            text = text.replace("<1>", Math.round(woodkg * 100) / 100 + " kg");
            text = text.replace("<2>", Math.round(liquidkg * 100) / 100 + " kg");
            text = text.replace("<3>", Math.round(gaskg * 100) / 100 + " kg");
            text = text.replace("<4>", Math.round(otherkg * 100) / 100 + " kg");
        }
        else if (KPTDAY2PANEL == 0 && KPTDAY2PANEL2 > 0) {
            text = "The average amount of fuel used for cooking per person per day is <1> for wood fuel, <2> for liquid fuel (including kerosene, ethanol and petrol), <3> for gas fuel (including LPG and LNG), and <4> for other types of fuel (including charcoal, etc.) at endline."

            text = text.replace("<1>", Math.round(woodkg2 * 100) / 100 + " kg");
            text = text.replace("<2>", Math.round(liquidkg2 * 100) / 100 + " kg");
            text = text.replace("<3>", Math.round(gaskg2 * 100) / 100 + " kg");
            text = text.replace("<4>", Math.round(otherkg2 * 100) / 100 + " kg");
        }
        mergeText(y, x, z, text);
    }

Function displayNarrativeIndicator43Fuel

displayNarrativeIndicator43Fuel(y, x, z)

The function generates the narrative for 4.3 generateTableValueIndicator42Fuel ON FUEL

Arguments

  • y (string) – row

  • x (string) – column

  • z (string) – position

This is the image caption

Screenshot of narrative

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
    function displayNarrativeIndicator43Fuel(y, x, z) {

        if (totaloutputel > 0 && totaloutputbl > 0) {

            text = "For " + totaloutputbl + " beneficiaries surveyed at baseline and " + totaloutputel + " beneficiaries surveyed at endline, the monthly average generateTableValueIndicator42Fuel for all types of fuels <1><2> from <3> at baseline to <4> at endline." + String.fromCharCode(10) + String.fromCharCode(10) +
                "<5><6>";

            if (Math.round(totalusd * 100) == Math.round(totalusd2 * 100)) {
                text = text.replace("<1>", "remained the same");
                text = text.replace("2", " at " + Math.abs(Math.round((totalusd - totalusd2) * 100) / 100) + " USD");
                text = text.replace(" from <3> to <4>", "");
            }
            else {
                if (Math.round(totalusd2 * 100) > Math.round(totalusd * 100)) {
                    text = text.replace("<1>", "increased")
                } else {
                    text = text.replace("<1>", "decreased")
                }

                text = text.replace("<2>", " by " + Math.abs(Math.round((totalusd2 - totalusd) * 100) / 100) + " USD");
                text = text.replace("<3>", Math.round(totalusd * 100) / 100 + " USD");
                text = text.replace("<4>", Math.round(totalusd2 * 100) / 100 + " USD");
            }



            var inc = 0;
            var dc = 0;
            var t1, t2, t3, t4;
            var text2 = "", text3 = "";

            table = sortTable(table, 5, 1);
            for (s = 2; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) > 0 && table[s][0] != "TOTAL") {
                    if (inc == 0) {
                        t1 = table[s][0].toLowerCase() + " fuel";
                        t2 = "by " + Math.round(table[s][5] * 100) / 100 + " USD";
                    }
                    if (inc > 0) {
                        t1 = t1.replace(" and# ", ", ");
                        t1 = t1 + " and# " + table[s][0].toLowerCase() + " fuel";
                        t2 = t2.replace(" and# ", ", ");
                        t2 = t2.replace(" respectively", "");
                        t2 = t2 + " and# " + Math.round(table[s][5] * 100) / 100 + " USD respectively";
                    }
                    inc++;
                }
            }

            table = sortTable(table, 5, 0);
            for (s = 2; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) < 0 && table[s][0] != "TOTAL") {
                    if (dc == 0) {
                        t3 = table[s][0].toLowerCase() + " fuel";
                        t4 = "by " + Math.abs(Math.round(table[s][5] * 100) / 100) + " USD";
                    }
                    if (dc > 0) {
                        t3 = t3.replace(" and$ ", ", ");
                        t3 = t3 + " and$ " + table[s][0].toLowerCase() + " fuel";
                        t4 = t4.replace(" and$ ", ", ");
                        t4 = t4.replace(" respectively", "");
                        t4 = t4 + " and$ " + Math.abs(Math.round(table[s][5] * 100) / 100) + " USD respectively";
                    }
                    dc++;
                }
            }


            if (dc > 0) { text2 = "The average fuel generateTableValueIndicator42Fuel per month decreased for " + t3 + " " + t4 + "."; }

            if (dc == 0 && inc > 0) { text3 = "The average fuel generateTableValueIndicator42Fuel per month increased for " + t1 + " " + t2 + "."; }

            if (inc > 0 && dc > 0) { text3 = " On the other hand, the average increased for " + t1 + " " + t2 + "."; }
            text3 = text3.replace("and#", "and");
            text3 = text3.replace("and#", "and");
            text2 = text2.replace("and$", "and");
            text2 = text2.replace("and$", "and");

            text = text.replace("<5>", text2);
            text = text.replace("<6>", text3);

        }
        else if (totaloutputbl > 0 && totaloutputel == 0) {

            table = sortTable(table, 1, 1);

            text = "For " + totaloutputbl + " beneficiaries surveyed at baseline, the monthly average generateTableValueIndicator42Fuel for all types of fuels is <1>." + String.fromCharCode(10) + String.fromCharCode(10) + "The largest share of generateTableValueIndicator42Fuel reported is for <3> fuel - <4>; while the second largest generateTableValueIndicator42Fuel reported is for <5> fuel - <6> on monthly average per beneficiary household."

            text = text.replace("<1>", Math.round(totalusd * 100) / 100 + " USD");
            //table = sortTable(table, 1);
            text = text.replace("<3>", table[2][0].toLowerCase());
            text = text.replace("<4>", Math.round(table[2][2] * 100) / 100 + " USD");
            if (table[3][0] != "TOTAL") {
                text = text.replace("<5>", table[3][0].toLowerCase());
                text = text.replace("<6>", Math.round(table[3][2] * 100) / 100 + " USD");
            } else { text = text.replace("; while the second largest generateTableValueIndicator42Fuel reported is for <5> fuel - <6>", ""); }
        }
        else if (totaloutputbl == 0 && totaloutputel > 0) {
            text = "For " + totaloutputel + " beneficiaries surveyed at endline, the monthly average generateTableValueIndicator42Fuel for all types of fuels is <1>." + String.fromCharCode(10) + String.fromCharCode(10) + "The largest share of generateTableValueIndicator42Fuel reported is for <3> fuel - <4>; while the second largest generateTableValueIndicator42Fuel reported is for <5> fuel - <6> on monthly average per beneficiary household."
            table = sortTable(table, 3, 1);
            text = text.replace("<1>", Math.round(totalusd2 * 100) / 100 + " USD");
            // table = sortTable(table, 3);
            text = text.replace("<3>", table[2][0].toLowerCase());
            text = text.replace("<4>", Math.round(table[2][4] * 100) / 100 + " USD");
            if (table[3][0] != "TOTAL") {
                text = text.replace("<5>", table[3][0].toLowerCase());
                text = text.replace("<6>", Math.round(table[3][4] * 100) / 100 + " USD");
            } else { text = text.replace("; while the second largest generateTableValueIndicator42Fuel reported is for <5> fuel - <6>", ""); }

        }

        mergeText(y, x, z, text);
    }

Function displayNarrativeIndicator52HHLighting

displayNarrativeIndicator52HHLighting(y, x, z)

The function generates the narrative for 5.2 HOUSEHOLD LIGHTING: RETENTION AND UTLISATION OF PORTABLE LIGHTS

Arguments

  • y (string) – row

  • x (string) – column

  • z (string) – position

This is the image caption

Screenshot of narrative

 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
    function displayNarrativeIndicator52HHLighting(y, x, z, lighttype) { //UTLISATION LIGHTS
        if (totaloutputel > 0 && totaloutputbl > 0) {
            text = "Overall, " + totaloutputbl + " beneficiaries at baseline and " + totaloutputel + " beneficiaries at endline were surveyed to verify the types of portable lights being used in the household." + String.fromCharCode(10) + String.fromCharCode(10) + "<2>" + String.fromCharCode(10) + String.fromCharCode(10) + "<5><6>"
            var inc = 0;
            var dc = 0;
            var t1, t2, t3, t4;
            var text2 = "", text3 = "";

            table = sortTable(table, 5, 1);
            for (s = 2; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) > 0 && table[s][0] != "TOTAL" && table[s][0] != "Other") {
                    if (inc == 0) {
                        t1 = table[s][0].toLowerCase();
                        t2 = "by " + Math.round(table[s][5] * 100) + "%";
                    }
                    if (inc > 0) {
                        t1 = t1.replace(" and# ", ", ");
                        t1 = t1 + " and# " + table[s][0].toLowerCase();
                        t2 = t2.replace(" and# ", ", ");
                        t2 = t2.replace(" respectively", "");
                        t2 = t2 + " and# " + Math.round(table[s][5] * 100) + "% respectively";
                    }
                    inc++;
                }
            }

            table = sortTable(table, 5, 0);
            for (s = 2; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) < 0 && table[s][0] != "TOTAL" && table[s][0] != "Other") {
                    if (dc == 0) {
                        t3 = table[s][0].toLowerCase();
                        t4 = "by " + Math.abs(Math.round(table[s][5] * 100)) + "%";
                    }
                    if (dc > 0) {
                        t3 = t3.replace(" and$ ", ", ");
                        t3 = t3 + " and$ " + table[s][0].toLowerCase();
                        t4 = t4.replace(" and$ ", ", ");
                        t4 = t4.replace(" respectively", "");
                        t4 = t4 + " and$ " + Math.abs(Math.round(table[s][5] * 100)) + "% respectively";
                    }
                    dc++;
                }
            }

            if (dc > 0) { text2 = "The percent of primary light sources used by beneficiaries surveyed decreased for " + t3 + " " + t4 + "."; }

            if (dc == 0 && inc > 0) { text3 = "The percent of primary light sources used by beneficiaries surveyed increased for " + t1 + " " + t2 + "."; }

            if (inc > 0 && dc > 0) { text3 = " On the other hand, the percent of the same increased for " + t1 + " " + t2 + "."; }
            text3 = text3.replace("and#", "and");
            text3 = text3.replace("and#", "and");
            text2 = text2.replace("and$", "and");
            text2 = text2.replace("and$", "and");

            text = text.replace("<5>", text2);
            text = text.replace("<6>", text3);
        }
        else if (totaloutputbl > 0 && totaloutputel == 0) {
            text = "At baseline, " + totaloutputbl + " beneficiaries were surveyed to verify the types of portable lights being used in the household." + String.fromCharCode(10) + String.fromCharCode(10) + "<2>" + String.fromCharCode(10) + String.fromCharCode(10) + "The majority of beneficiaries report utilising <3> as the primary source of lighting in their houses - <4>; while the second largest convertNumberToText of beneficiaries report utilising <5> as the primary source - <6>."
            table = sortTable(table, 1, 1);
            text = text.replace("<3>", table[2][0].toLowerCase());
            text = text.replace("<4>", Math.round(table[2][2] * 100) + "%");
            if (table[3][0] != "TOTAL") {
                text = text.replace("<5>", table[3][0].toLowerCase());
                text = text.replace("<6>", Math.round(table[3][2] * 100) + "%");
            } else { text = text.replace("; while the second largest convertNumberToText of beneficiaries report utilising <5> as the primary source - <6>", ""); }
        }
        else if (totaloutputbl == 0 && totaloutputel > 0) {
            text = "Overall, " + totaloutputbl + " beneficiaries were surveyed at baseline, and " + totaloutputel + " at endline to verify the types of portable lights being used in the household." + String.fromCharCode(10) + String.fromCharCode(10) + "<2>" + String.fromCharCode(10) + String.fromCharCode(10) + "The majority of beneficiaries report utilising <3> as the primary source of lighting in their houses - <4>; while the second largest convertNumberToText of beneficiaries report utilising <5> as the primary source - <6>."
            table = sortTable(table, 3, 1);
            text = text.replace("<3>", table[2][0].toLowerCase());
            text = text.replace("<4>", Math.round(table[2][4] * 100) + "%");
            if (table[3][0] != "TOTAL") {
                text = text.replace("<5>", table[3][0].toLowerCase());
                text = text.replace("<6>", Math.round(table[3][4] * 100) + "%");
            } else { text = text.replace("; while the second largest convertNumberToText of beneficiaries report utilising <5> as the primary source - <6>", ""); }
        }

        t1 = "";
        if (totaloutputel > 0) {
            t1 = "In " + Year + " the project supported the following portable lights: " + lighttype + "." + String.fromCharCode(10) + String.fromCharCode(10) + "At endline, " + Math.round(retentionlights * 100) + "% of beneficiaries surveyed retained the portable lights provided by UNHCR and/or partners, of which " + Math.round(functionlight * 100) + "% were able to show the lights retained functioning. Of all the beneficiaries, " + Math.round(utlisationlight * 100) + "% report having utilising the portable lights provided by UNHCR and/or partners as the primary source of lighting in the house."
        } else {
            t1 = "The indicators for retention, function and utilisation of portable lights provided by UNHCR and/or partners will be available only at endline survey - after portable lights are distributed."
        }

        text = text.replace("<2>", t1);

        mergeText(y, x, z, text);
    }

Function displayNarrativeIndicator53HHLighting

displayNarrativeIndicator53HHLighting(y, x, z)

The function generates the narrative for 5.3 HOUSEHOLD LIGHTING: generateTableValueIndicator42Fuel ON HH LIGHTING

Arguments

  • y (string) – row

  • x (string) – column

  • z (string) – position

This is the image caption

Screenshot of narrative

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
    function displayNarrativeIndicator53HHLighting(y, x, z) {
        if (totaloutputel > 0 && totaloutputbl > 0) {
            text = "Among the " + totaloutputbl + " beneficiaries at baseline and " + totaloutputel + " beneficiaries surveyed at endline, the monthly average generateTableValueIndicator42Fuel for all types of lighting <1> <2> from <3> at baseline to <4> at endline." + String.fromCharCode(10) + String.fromCharCode(10) +
                "<5><6>";

            if (Math.round(totalusd * 100) == Math.round(totalusd2 * 100)) {
                text = text.replace("<1>", "remained the same");
                text = text.replace("2", " at " + Math.abs(Math.round((totalusd - totalusd2) * 100) / 100) + " USD");
                text = text.replace(" from <3> to <4>", "");
            }
            else {
                if (Math.round(totalusd2 * 100) > Math.round(totalusd * 100)) {
                    text = text.replace("<1>", "increased")
                } else {
                    text = text.replace("<1>", "decreased")
                }

                text = text.replace("<2>", "by " + Math.abs(Math.round((totalusd2 - totalusd) * 100) / 100) + " USD");
                text = text.replace("<3>", Math.round(totalusd * 100) / 100 + " USD");
                text = text.replace("<4>", Math.round(totalusd2 * 100) / 100 + " USD");
            }

            var inc = 0;
            var dc = 0;
            var t1, t2, t3, t4;
            var text2 = "", text3 = "";

            table = sortTable(table, 5, 1);
            for (s = 2; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) > 0 && table[s][0] != "TOTAL" && table[s][0] != "Other") {
                    if (inc == 0) {
                        t1 = table[s][0].toLowerCase();
                        t2 = "by " + Math.round(table[s][5] * 100) / 100 + " USD";
                    }
                    if (inc > 0) {
                        t1 = t1.replace(" and# ", ", ");
                        t1 = t1 + " and# " + table[s][0].toLowerCase();
                        t2 = t2.replace(" and# ", ", ");
                        t2 = t2.replace(" respectively", "");
                        t2 = t2 + " and# " + Math.round(table[s][5] * 100) / 100 + " USD respectively";
                    }
                    inc++;
                }
            }

            table = sortTable(table, 5, 0);
            for (s = 2; s < table.length; s++) {
                if (Math.round(table[s][5] * 100) < 0 && table[s][0] != "TOTAL" && table[s][0] != "Other") {
                    if (dc == 0) {
                        t3 = table[s][0].toLowerCase();
                        t4 = "by " + Math.abs(Math.round(table[s][5] * 100) / 100) + " USD";
                    }
                    if (dc > 0) {
                        t3 = t3.replace(" and$ ", ", ");
                        t3 = t3 + " and$ " + table[s][0].toLowerCase();
                        t4 = t4.replace(" and$ ", ", ");
                        t4 = t4.replace(" respectively", "");
                        t4 = t4 + " and$ " + Math.abs(Math.round(table[s][5] * 100) / 100) + " USD respectively";
                    }
                    dc++;
                }
            }

            if (dc > 0) { text2 = "The average generateTableValueIndicator42Fuel for lighting per person per month decreased for " + t3 + " " + t4 + "."; }

            if (dc == 0 && inc > 0) { text3 = "The average generateTableValueIndicator42Fuel for lighting per person per month increased for " + t1 + " " + t2 + "."; }

            if (inc > 0 && dc > 0) { text3 = " On the other hand, the average increased for " + t1 + " " + t2 + "."; }
            text3 = text3.replace("and#", "and");
            text3 = text3.replace("and#", "and");
            text2 = text2.replace("and$", "and");
            text2 = text2.replace("and$", "and");

            text = text.replace("<5>", text2);
            text = text.replace("<6>", text3);
        }
        else if (totaloutputbl > 0 && totaloutputel == 0) {
            text = "For " + totaloutputbl + " beneficiaries surveyed at baseline,  the monthly average generateTableValueIndicator42Fuel for all types of lighting is <1>." + String.fromCharCode(10) + String.fromCharCode(10) + "The largest share of monthly generateTableValueIndicator42Fuel on lighting reported is for <3> - <4>; while the second largest generateTableValueIndicator42Fuel reported is for <5> - <6> on monthly average per beneficiary household."

            text = text.replace("<1>", Math.round(totalusd * 100) / 100 + " USD");
            table = sortTable(table, 1, 1);
            text = text.replace("<3>", table[2][0].toLowerCase());
            text = text.replace("<4>", Math.round(table[2][2] * 100) / 100 + " USD");
            if (table[3][0] != "TOTAL") {
                text = text.replace("<5>", table[3][0].toLowerCase());
                text = text.replace("<6>", Math.round(table[3][2] * 100) / 100 + " USD");
            } else { text = text.replace("; while the second largest generateTableValueIndicator42Fuel reported is for <5> - <6> on monthly average per beneficiary household", ""); }
        }
        else if (totaloutputbl == 0 && totaloutputel > 0) {
            text = "For " + totaloutputel + " beneficiaries surveyed at endline,  the monthly average generateTableValueIndicator42Fuel for all types of lighting is <1>." + String.fromCharCode(10) + String.fromCharCode(10) + "The largest share of monthly generateTableValueIndicator42Fuel on lighting reported is for <3> - <4>; while the second largest generateTableValueIndicator42Fuel reported is for <5> - <6> on monthly average per beneficiary household."

            text = text.replace("<1>", Math.round(totalusd2 * 100) / 100 + " USD");
            table = sortTable(table, 3, 1);
            text = text.replace("<3>", table[2][0].toLowerCase());
            text = text.replace("<4>", Math.round(table[2][4] * 100) / 100 + " USD");
            if (table[3][0] != "TOTAL") {
                text = text.replace("<5>", table[3][0].toLowerCase());
                text = text.replace("<6>", Math.round(table[3][4] * 100) / 100 + " USD");
            } else { text = text.replace("; while the second largest generateTableValueIndicator42Fuel reported is for <5> - <6> on monthly average per beneficiary household", ""); }
        }
        mergeText(y, x, z, text);
    }

Function skipHeaders

Function Count_energy

Warning

it’s recommended that the function be renamed

 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
    function Count_energy(item, header, category, line, contain, KPT, KPT2) { //KPT OK
        var result = 0;
        var count = 0;

        for (i = 0; i < item.length; i++) {
            if (filterMain(i, item, Country, Partner, Camp, Status, Year, Gender, Age, line, '0', '0') != 9) {
                continue;
            }
            if (item[i]["PARTNER_INFO/Output2"] == "0") { continue;}
            //SKIP NON PANEL KPT FROM AREA 1 CALCULATION
            if (String(item[i]["FILTER"]) == "2") { if (skipHeaders(header) == 0) { continue; } }

            //SKIP KPT DAY 2
            if (KPT == "WD2" && item[i]["PARTNER_INFO/KPTD2"] == "Yes") {
                continue;
            }


            for (ii = 0; ii < category.length; ii++) {

                if (category[ii] == "Total" && item[i][header] != "") {
                    count++;
                    continue;
                }
                if (contain == 0) {
                    if (item[i][header] != "" && item[i][header] == category[ii]) {
                        count++;
                        continue;
                    }
                }
                if (contain == 1) {
                    if (item[i][header] != "" && item[i][header].indexOf(category[ii]) > -1) {
                        count++;
                        continue;
                    }
                }
                if (item[i][header] == "0" && category[ii] == "Other") {
                    count++;
                    continue;
                }
            }
            if (count > 0) {
                result++;
                count = 0;
            } else {
                count = 0;
            }
        }
        return result;
    }

Function countNumBenefIndicator_energy

countNumBenefIndicator_energy(taskname, item, header, category, line, contain, ACI, AC2, KPT)

The function counts convertNumberToText of beneficiary per indicators

Arguments

  • taskname (string) – name of the task

  • item (array) – jsonData

  • header (string) – list of headers

  • category (type) – type of category

  • line (type) – Baseline/Endline

  • contain (type) – [0,1]

  • ACI (type) –

  • AC2 (type) –

  • KPT (string) – 

 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
    function countNumBenefIndicator_energy(item, header, category, line, contain, AC1, AC2, KPT) { //KPT OK
        var result = 0;
        var count = 0;
        for (i = 0; i < item.length; i++) {
            if (filterMain(i, item, Country, Partner, Camp, Status, Year, Gender, Age, line, AC1, AC2) != 9) {
                continue;
            }
            //SKIP NON PANEL KPT FROM AREA 1 CALCULATION
            if (String(item[i]["FILTER"]) == "2") { if (skipHeaders(header) == 0) { continue; } }
            //SKIP KPT DAY 2
            if (KPT == "WD2" && item[i]["PARTNER_INFO/KPTD2"] == "Yes") {
                continue;
            }

            for (ii = 0; ii < category.length; ii++) {
                //Browser.msgBox(category[ii]);

                if (category[ii] == "Total" && item[i][header] != "") {
                    count++;
                    continue;
                }
                if (contain == 0) {
                    if (item[i][header] != "" && item[i][header] == category[ii]) {
                        count++;
                        continue;
                    }
                }
                if (contain == 1) {
                    if (item[i][header] != "" && item[i][header].indexOf(category[ii]) > -1) {
                        count++;
                        continue;
                    }
                }
                if (item[i][header] == "0" && category[ii] == "Other") {
                    count++;
                    continue;
                }
            }
            if (count > 0) {
                result++;
                count = 0;
            } else {
                count = 0;
            }
        }
        return result;
    }

Function Count2_energy

Warning

it’s recommended that the function be renamed

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    function Count2_energy(item, header, line, cc, AC1, AC2) {
        var result = 0;
        var count = 0;
        for (i = 0; i < item.length; i++) {
            if (filterMain(i, item, Country, Partner, Camp, Status, Year, Gender, Age, line, AC1, AC2) != 9) {
                continue;
            }
            //SKIP NON PANEL KPT FROM AREA 1 CALCULATION
            if (String(item[i]["FILTER"]) == "2") { if (skipHeaders(header) == 0) { continue; } }
            if (item[i][header] != "") {
                result = result + item[i][header];
            }
        }
        return result;
    }

Function countNumIndicator4Kitchen

countNumIndicator4Kitchen(item, header, category, amount, unit, line-)

The function counts the values for 4.2 KITCHEN PERFORMANCE TEST (KPT)

Arguments

  • item (array) – json data

  • header (array) – list of headers

  • category (string) – name of the category

  • amount (array) –

  • unit (string) –

  • line- (string) – Baseline/Endline

 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
    function countNumIndicator4Kitchen(item, header, header2, line) {
        var result = 0;
        var kpt1 = 0;
        var kpt2 = 0;
        validsample = 0;
        KPTperson = 0;
        KPTmeal = 0;
        for (i = 0; i < item.length; i++) {
            if (filterMain(i, item, Country, Partner, Camp, Status, Year, Gender, Age, line, '0', '0') != 9) {
                continue;
            }
            if (item[i]["PARTNER_INFO/Output2"] == "0") { continue;}
            //SKIP NON PANEL KPT FROM AREA 1 CALCULATION
            if (String(item[i]["FILTER"]) == "2") { if (skipHeaders(header) == 0) { continue; } }
            //KPT PANEL CHECK
            if (item[i]['PARTNER_INFO/KPTD2'] != "Yes" || item[i]['KPT'] != "1" || item[i]['KPT1OK'] != "Yes" || item[i]['O1_KPT_D2/O1_KPT_D2_Confirmation'] != "Yes" || item[i]['O1_KPT_D2/O1_KPT_D2_Party'] == "Yes") {
                continue;
            }

            //No data skip
            if (item[i][header] == 0 || String(item[i][header]) == "" || String(item[i][header2]) == "") {
                continue;
            }

            validsample++;
            if (item[i][header] != "") {
                kpt1 = kpt1 + parseFloat(item[i][header]);
            }
            if (item[i][header2] != "") {
                kpt2 = kpt2 + parseFloat(item[i][header2]);
            }


            if (item[i]['O1_KPT_D2/O1_KPT_D2_LunchPeople'] != "") {
                KPTperson = KPTperson + parseInt(item[i]['O1_KPT_D2/O1_KPT_D2_LunchPeople']);
                KPTmeal++;
            }

            if (item[i]['O1_KPT_D2/O1_KPT_D2_DinnerPeople'] != "") {
                KPTperson = KPTperson + parseInt(item[i]['O1_KPT_D2/O1_KPT_D2_DinnerPeople']);
                KPTmeal++;
            }
        }
        result = kpt1 - kpt2;
        return result;
    }

Function initialFilter_energy

initialFilter_energy()

The function returns the initial states for the filters

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
    function initialFilter_energy() {

        for (i = 0; i < jsonData.length; i++) {
            if (filterMain2(i, jsonData, Country, Partner, Camp, Status, Year, Gender, Age, "ALL", "0", "0") != 9) {
                continue;
            }

            //SKIP NON PANEL KPT
            if (jsonData[i]["PARTNER_INFO/Output"].indexOf("FEC") > -1 || jsonData[i]["PARTNER_INFO/Output2"].indexOf("FEC") > -1) {
                if (String(jsonData[i]["KPT"]) != "1") {
                    if (jsonData[i]["PARTNER_INFO/Output"].indexOf("SL") == -1 && jsonData[i]["PARTNER_INFO/Output"].indexOf("PLD") == -1 && jsonData[i]["PARTNER_INFO/Output"].indexOf("CLP") == -1 && jsonData[i]["PARTNER_INFO/Output2"].indexOf("SL") == -1 && jsonData[i]["PARTNER_INFO/Output2"].indexOf("PLD") == -1 && jsonData[i]["PARTNER_INFO/Output2"].indexOf("CLP") == -1) { continue; } else { jsonData[i]['FILTER'] = "2"; continue; }
                }
            }

                jsonData[i]['FILTER'] = "1";
        }
    }

Function generateTablePercentIndicatorImpact_energy

generateTablePercentIndicatorImpact_energy(clm, newclm, item, header, skip, KPT)

The function generates tables for 3. SAMPLE SIZE PER OUTPUT, 4. AREA 1: SUSTAINABLE COOKSTOVES & FUEL SUPPLY and 5. AREA 2: COMMUNITY AND HOUSEHOLD LIGHTING

Arguments

  • clm (array) – array of columns

  • newclm (array) – array of new columns

  • item (array) – data

  • header (header) – headers

  • skip (type) –

  • KPT (string) – 

 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
    function generateTablePercentIndicatorImpact_energy(clm, newclm, item, header, skip, KPT) { 
        var tb = [];
        var TotalB = 0;
        var TotalE = 0;
        var bl = new Array();
        var el = new Array();
        var cc = 1;

        for (ic = 0; ic < clm.length; ic++) {
            bl[ic] = 0;
            el[ic] = 0;
        }

        for (ic = 0; ic < clm.length; ic++) {
            for (hh = 0; hh < header.length; hh++) {
                bl[ic] = bl[ic] + Count(item, header[hh], clm[ic], "Baseline", cc, KPT);
                el[ic] = el[ic] + Count(item, header[hh], clm[ic], "Endline", cc, KPT);
            }
        }

        tb[0] = new Array();
        tb[0][0] = "";
        tb[0][1] = "Baseline";
        tb[0][2] = "";
        tb[0][3] = "Endline";
        tb[0][4] = "";
        tb[0][5] = "Impact";

        tb[1] = new Array();
        tb[1][0] = "";
        tb[1][1] = "Sample";
        tb[1][2] = "%";
        tb[1][3] = "Sample";
        tb[1][4] = "%";
        tb[1][5] = "%";

        for (h = 0; h < clm.length; h++) {
            TotalB = totaloutputbl;
            TotalE = totaloutputel;
        }

        var u = 2;
        for (iz = 0; iz < clm.length; iz++) {
            if (skip != "No" && bl[iz] == 0 && el[iz] == 0) {
                continue;
            }
            var cvalue = clm[iz];
            tb[u] = new Array();
            tb[u][0] = newclm[iz];
            tb[u][1] = bl[iz];
            if (TotalB != 0) {
                tb[u][2] = bl[iz] / TotalB;
            } else {
                tb[u][2] = "";
            }
            tb[u][3] = el[iz];
            if (TotalE != 0 && endline != 0) {
                tb[u][4] = el[iz] / TotalE;
                if (TotalB != 0) { tb[u][5] = el[iz] / TotalE - bl[iz] / TotalB; } else { tb[u][5] = ""; }
            } else {
                tb[u][4] = "";
                tb[u][5] = "";
            }
            u++;
        }

        tb[u] = new Array();
        tb[u][0] = "TOTAL";
        tb[u][1] = TotalB;
        if (TotalB != 0) {
            tb[u][2] = TotalB / TotalB;
        } else {
            tb[u][2] = "";
        }
        tb[u][3] = TotalE;
        if (TotalE != 0 && endline != 0) {
            tb[u][4] = TotalE / TotalE;
            tb[u][5] = "";
        } else {
            tb[u][4] = "";
            tb[u][5] = "";
        }
        return tb;
    }

Function generateTablePercentIndicatorImpactChange_energy

generateTablePercentIndicatorImpactChange_energy(clm, newclm, item, header, skip, AC1, AC2)

The function generates the values for tables 4. AREA 1: SUSTAINABLE COOKSTOVES & FUEL SUPPLY and 5. AREA 2: COMMUNITY AND HOUSEHOLD LIGHTING

Arguments

  • clm (array) – array of columns

  • newclm (array) – array of new columns

  • item (array) – data

  • header (header) – headers

  • skip (type) –

  • AC1 (type) –

  • AC2 (type) – 

 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
    function generateTablePercentIndicatorImpactChange_energy(clm, newclm, item, header, skip, AC1, AC2, KPT) { //USED
        var tb = [];
        var TotalB = 0;
        var TotalE = 0;
        var bl = new Array();
        var el = new Array();
        var cc = skip;

        if (skip == "11") { cc = 1; skip = "No"; }


        for (ic = 0; ic < clm.length; ic++) {
            bl[ic] = 0;
            el[ic] = 0;
        }

        for (ic = 0; ic < clm.length; ic++) {
            for (hh = 0; hh < header.length; hh++) {
                bl[ic] = bl[ic] + countNumBenefIndicator(item, header[hh], clm[ic], "Baseline", cc, AC1, AC2, KPT);
                el[ic] = el[ic] + countNumBenefIndicator(item, header[hh], clm[ic], "Endline", cc, AC1, AC2, KPT);
            }
        }

        tb[0] = new Array();
        tb[0][0] = "";
        tb[0][1] = "Baseline";
        tb[0][2] = "";
        tb[0][3] = "Endline";
        tb[0][4] = "";
        tb[0][5] = "Impact";

        tb[1] = new Array();
        tb[1][0] = "";
        tb[1][1] = "Sample";
        tb[1][2] = "%";
        tb[1][3] = "Sample";
        tb[1][4] = "%";
        tb[1][5] = "%";

        for (h = 0; h < clm.length; h++) {
            TotalB = TotalB + bl[h];
            TotalE = TotalE + el[h];
        }

        var u = 2;
        for (iz = 0; iz < clm.length; iz++) {
            if (skip != "No" && bl[iz] == 0 && el[iz] == 0) {
                continue;
            }
            var cvalue = clm[iz];
            tb[u] = new Array();
            tb[u][0] = newclm[iz];
            tb[u][1] = bl[iz];
            if (TotalB != 0) {
                tb[u][2] = bl[iz] / TotalB;
            } else {
                tb[u][2] = "";
            }
            tb[u][3] = el[iz];
            if (TotalE != 0 && endline != 0) {
                tb[u][4] = el[iz] / TotalE;
                if (TotalB != 0) { tb[u][5] = el[iz] / TotalE - bl[iz] / TotalB; } else { tb[u][5] = ""; }
            } else {
                tb[u][4] = "";
                tb[u][5] = "";
            }
            u++;
        }

        tb[u] = new Array();
        tb[u][0] = "TOTAL";
        tb[u][1] = TotalB;
        if (TotalB != 0) {
            tb[u][2] = TotalB / TotalB;
        } else {
            tb[u][2] = "";
        }
        tb[u][3] = TotalE;
        if (TotalE != 0 && endline != 0) {
            tb[u][4] = TotalE / TotalE;
            tb[u][5] = "";
        } else {
            tb[u][4] = "";
            tb[u][5] = "";
        }
        return tb;
    }

Function formatTables_energy

formatTables_energy(y, x, ttb, title, sht)

The function presentes and formats the table

Arguments

  • y (type) – row

  • x (type) – column

  • ttb (type) – table

  • title (type) – title

  • sht (type) – sheet

 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
    function formatTables_energy(y, x, ttb, title, sht) { //USED

        var sss = sumsheet;
        if (sht == "C") {
            var idx = key["idx"];
            var sst = SpreadsheetApp.openById(idx);
            sss = sst.getSheetByName("Report");
        }

        sss.getRange(y, x - 1).setValue(title);
        sss.getRange(y, x - 1).setHorizontalAlignment("left");
        sss.getRange(y, x - 1).setFontColor("#434343");
        sss.getRange(y, x - 1).setFontWeight("bold");
        sss.getRange(y, x - 1).setFontSize(12);
        y++;

       

        sss.getRange(y, x, ttb.length, ttb[0].length).setValues(ttb);
        sss.getRange(y - 1, x, ttb.length + 1, ttb[0].length).setFontSize(12);
        sss.getRange(y - 1, x, ttb.length + 1, ttb[0].length).setFontFamily("Arimo");
        sss.getRange(y, x, ttb.length, ttb[0].length).setHorizontalAlignment("right");

     


        sss.getRange(y, x, 1, ttb[0].length).setVerticalAlignment("bottom");

        sss.getRange(y, x, ttb.length, 1).setFontWeight("bold");
        sss.getRange(y + ttb.length - 1, x, 1, ttb[0].length).setFontWeight("bold");
       
        for (i = 0; i < ttb[0].length; i++) {
            if (ttb[1][i] == "%") {
                sss.getRange(y, x + i, ttb.length, 1).setconvertNumberToTextFormat('0%;[Red](0%)');
            } else {
                sss.getRange(y, x + i, ttb.length, 1).setconvertNumberToTextFormat('#,##0');
            }
        }

        sss.getRange(y, x + 1, 1, 2).mergeCells();
        sss.getRange(y, x + 1).setHorizontalAlignment("Center");
        sss.getRange(y, x + 3, 1, 2).merge();
        sss.getRange(y, x + 3).setHorizontalAlignment("Center");

        sss.getRange(y, x - 1, 2, ttb[0].length).setBackground(graphcolor);
        sss.getRange(y, x - 1, 2, ttb[0].length).setFontColor("#434343");
        sss.getRange(y + 2, x - 1, ttb.length - 2, ttb[0].length).setBackground("#f3f3f3");
        sss.getRange(y, x + 5, ttb.length, 1).clear();
        y2 = y2 + 2 + ttb.length;
    }

Function formatTablesAddMerge_energy

formatTablesAddMerge_energy(y, x, ttb, title, sht)

The function presentes and formats the table

Arguments

  • y (type) – row

  • x (type) – column

  • ttb (type) – table

  • title (type) – title

  • sht (type) – sheet

 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
    function formatTablesAddMerge_energy(y, x, ttb, title, sht) {

        var sss = sumsheet;
        if (sht == "C") {
            var idx = key["idx"];
            var sst = SpreadsheetApp.openById(idx);
            sss = sst.getSheetByName("Report");
        }

        sss.getRange(y, x).setValue(title);
        sss.getRange(y, x).setFontColor("#434343");
        sss.getRange(y, x).setHorizontalAlignment("left");
        sss.getRange(y, x).setFontWeight("bold");
        sss.getRange(y, x).setFontSize(12);
        sss.getRange(y, x, 1, 6).mergeCells();
        sss.getRange(y, x).setWrap(true);;
        y++;

        //sss.getRange(y,x,1,merge).merge();

        //Logger.log(ttb);

        sss.getRange(y, x, ttb.length, ttb[0].length).setValues(ttb);
        sss.getRange(y - 1, x, ttb.length + 1, ttb[0].length).setFontSize(12);
        sss.getRange(y - 1, x, ttb.length + 1, ttb[0].length).setFontFamily("Arimo");
        sss.getRange(y, x, ttb.length, ttb[0].length).setHorizontalAlignment("right");

        //sss.getRange(y,x,1,ttb[0].length).setWrap(true);


        sss.getRange(y, x, 1, ttb[0].length).setVerticalAlignment("bottom");

        sss.getRange(y + 2, x, ttb.length, 1).setFontWeight("bold");
        sss.getRange(y + ttb.length - 1, x, 1, ttb[0].length).setFontWeight("bold");
        //sss.getRange(y,x,ttb.length,ttb[0].length).setBorder(true, true, true, true, false, false, "silver", SpreadsheetApp.BorderStyle.SOLID);
        for (i = 0; i < ttb[0].length; i++) {
            if (ttb[1][i] == "%") {
                sss.getRange(y, x + i, ttb.length, 1).setconvertNumberToTextFormat('0%;[Red](0%)');
            } else {
                sss.getRange(y, x + i, ttb.length, 1).setconvertNumberToTextFormat("#,##0_);[Red](#,##0)");
            }
        }

        sss.getRange(y, x + 1, 1, 2).mergeCells();
        sss.getRange(y, x + 1).setHorizontalAlignment("Center");
        sss.getRange(y, x + 3, 1, 2).merge();
        sss.getRange(y, x + 3).setHorizontalAlignment("Center");

        sss.getRange(y, x, 2, ttb[0].length).setBackground(graphcolor);
        sss.getRange(y, x, 2, ttb[0].length).setFontColor("#434343");
        sss.getRange(y + 2, x, ttb.length - 2, ttb[0].length).setBackground("#f3f3f3");

        y2 = y2 + 2 + ttb.length;
    }

Function generateTableAverageIndicator42Kitchen

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
    function generateTableAverageIndicator42Kitchen() {

        var tb = [];
        var TotalB = 0;
        var TotalE = 0;
        var blkg = new Array();
        var blp = new Array();
        var elkg = new Array();
        var elp = new Array();
        var list = new Array();
        var vs = new Array();
        var vs2 = new Array();
        var m1 = new Array();
        var m2 = new Array();

        list[0] = "Wood fuel";
        list[1] = "Liquid fuel";
        list[2] = "Gas fuel";
        list[3] = "Charcoal fuel";
        list[4] = "Other fuel";

        for (ic = 0; ic < 5; ic++) {
            blkg[ic] = 0;
            blp[ic] = 0;
            elkg[ic] = 0;
            elp[ic] = 0;
        }

        tb[0] = new Array();
        tb[0][0] = "";
        tb[0][1] = "Baseline";
        tb[0][2] = "";
        tb[0][3] = "Endline";
        tb[0][4] = "";
        tb[0][5] = "Impact";

        tb[1] = new Array();
        tb[1][0] = "Fuel Type";
        tb[1][1] = "Average Hh size present at meals (persons)";
        tb[1][2] = "Average Kg of fuel used per person per day";
        tb[1][3] = "Average Hh size present at meals (persons)";
        tb[1][4] = "Average Kg of fuel used per person per day";
        tb[1][5] = "▲(Kg)";

        blkg[0] = countNumIndicator4Kitchen(jsonData, "Wood", "O1_KPT_D2/O1_KPT_D2_Wood_KG", "Baseline");
        blp[0] = KPTperson; 5
        vs[0] = validsample;
        m1[0] = KPTmeal;
        blkg[1] = countNumIndicator4Kitchen(jsonData, "Liquid", "O1_KPT_D2/O1_KPT_D2_Kerosene_KG", "Baseline");
        blp[1] = KPTperson;
        vs[1] = validsample;
        m1[1] = KPTmeal;
        blkg[2] = countNumIndicator4Kitchen(jsonData, "Gas", "O1_KPT_D2/O1_KPT_D2_Gas_KG", "Baseline");
        blp[2] = KPTperson;
        vs[2] = validsample;
        m1[2] = KPTmeal;
        blkg[3] = countNumIndicator4Kitchen(jsonData, "Charcoal", "O1_KPT_D2/O1_KPT_D2_Charcoal_KG", "Baseline");
        blp[3] = KPTperson;
        vs[3] = validsample;
        m1[3] = KPTmeal;
        blkg[4] = countNumIndicator4Kitchen(jsonData, "Other", "O1_KPT_D2/O1_KPT_D2_Other_KG", "Baseline");
        blp[4] = KPTperson;
        vs[4] = validsample;
        m1[4] = KPTmeal;

        elkg[0] = countNumIndicator4Kitchen(jsonData, "Wood", "O1_KPT_D2/O1_KPT_D2_Wood_KG", "Endline");
        elp[0] = KPTperson;
        vs2[0] = validsample;
        m2[0] = KPTmeal;
        elkg[1] = countNumIndicator4Kitchen(jsonData, "Liquid", "O1_KPT_D2/O1_KPT_D2_Kerosene_KG", "Endline");
        elp[1] = KPTperson;
        vs2[1] = validsample;
        m2[1] = KPTmeal;
        elkg[2] = countNumIndicator4Kitchen(jsonData, "Gas", "O1_KPT_D2/O1_KPT_D2_Gas_KG", "Endline");
        elp[2] = KPTperson;
        vs2[2] = validsample;
        m2[2] = KPTmeal;
        elkg[3] = countNumIndicator4Kitchen(jsonData, "Charcoal", "O1_KPT_D2/O1_KPT_D2_Charcoal_KG", "Endline");
        elp[3] = KPTperson;
        vs2[3] = validsample;
        m2[3] = KPTmeal;
        elkg[4] = countNumIndicator4Kitchen(jsonData, "Other", "O1_KPT_D2/O1_KPT_D2_Other_KG", "Endline");
        elp[4] = KPTperson;
        vs2[4] = validsample;
        m2[4] = KPTmeal;

        for (i5 = 0; i5 < list.length; i5++) {
            bltotalkg = bltotalkg + blkg[i5];
            bltotalp = bltotalp + blp[i5];
            eltotalkg = eltotalkg + elkg[i5];
            eltotalp = eltotalp + elp[i5];
        }

        var u = 2;
        var u2 = 2;
        for (iz = 0; iz < list.length; iz++) {

            if (vs[iz] == 0 && vs2[iz] == 0) {
                u2++;
                continue;
            } //skipping non data

            tb[u] = new Array();
            tb[u][0] = list[iz];// + String.fromCharCode(10) + "(B:" + String(vs[u2 - 2] + " E:" + vs2[u2 - 2]) + ")";
            if (blp[iz] == 0) { tb[u][1] = 0; } else { tb[u][1] = blp[u2 - 2] / m1[u2 - 2]; }//String(blkg3).replace(/(\d)(?=(\d{3})+(?!\d))/g, '$1,') + " KG" + String.fromCharCode(10) + blp[u2 - 2] + " Persons";
            if (blkg[iz] == 0) {
                tb[u][2] = 0;
            } else if (blp[iz] == 0) {
                tb[u][2] = "NA";
            } else {
                tb[u][2] = (blkg[iz] / vs[u2 - 2]) / (blp[u2 - 2] / m1[u2 - 2]);
            }

            if (KPTDAY2PANEL2 != 0) {
                if (elp[iz] == 0) { tb[u][3] = 0; } else { tb[u][3] = elp[u2 - 2] / m2[u2 - 2]; }
                if (elkg[iz] == 0) { tb[u][4] = 0; } else { tb[u][4] = (elkg[iz] / vs2[u2 - 2]) / (elp[u2 - 2] / m2[u2 - 2]); }
                //if (blp[u2 - 2] != 0) { 
                tb[u][5] = tb[u][4] - tb[u][2];
                //} else { tb[u][5] = ""; }
            } else {
                tb[u][3] = "";
                tb[u][4] = "";
                tb[u][5] = "";
            }

            u++;
            u2++;
        }

        //tb[u] = new Array();
        //tb[u][0] = "";
        //tb[u][1] = "";
        //tb[u][2] = "";
        //tb[u][3] = "";
        //tb[u][4] = "";
        //tb[u][5] = "";

        ///TABLE2 for valid samples
        KPTttb2[0] = new Array();

        KPTttb2[0][0] = "";
        KPTttb2[0][1] = "Baseline";
        KPTttb2[0][2] = "";
        KPTttb2[0][3] = "Endline";
        KPTttb2[0][4] = "";
        KPTttb2[0][5] = "";

        KPTttb2[1] = new Array();
        KPTttb2[1][0] = "";
        KPTttb2[1][1] = "Valid Sample";
        KPTttb2[1][2] = "%";
        KPTttb2[1][3] = "Valid Sample";
        KPTttb2[1][4] = "%";
        KPTttb2[1][5] = "";

        //DAY 1 as PLANED
        var temp = countNumBenefIndicator(jsonData, "KPT1OK", ['Yes'], "Baseline", "1", "KPT", "1");
        var temp2 = countNumBenefIndicator(jsonData, "KPT1OK", ['Yes'], "Endline", "1", "KPT", "1");

        KPTttb2[2] = new Array();

        KPTttb2[2][0] = "Day 1 as Planned";
        KPTttb2[2][1] = temp;
        KPTttb2[2][2] = temp / KPTDAY2PANEL;
        if (endline != 0) {
            KPTttb2[2][3] = temp2;
            KPTttb2[2][4] = temp2 / KPTDAY2PANEL2;
        } else {
            KPTttb2[2][3] = 0;
            KPTttb2[2][4] = "";
        }
        KPTttb2[2][5] = "";

        //DAY2 as PLANED
        temp = countNumBenefIndicator(jsonData, "O1_KPT_D2/O1_KPT_D2_Confirmation", ['Yes'], "Baseline", "1", "KPT", "1");
        temp2 = countNumBenefIndicator(jsonData, "O1_KPT_D2/O1_KPT_D2_Confirmation", ['Yes'], "Endline", "1", "KPT", "1");

        KPTttb2[3] = new Array();
        KPTttb2[3][0] = "Day 2 as Planned";
        KPTttb2[3][1] = temp;
        KPTttb2[3][2] = temp / KPTDAY2PANEL;
        if (endline != 0) {
            KPTttb2[3][3] = temp2;
            KPTttb2[3][4] = temp2 / KPTDAY2PANEL2;
        } else {
            KPTttb2[3][3] = 0;
            KPTttb2[3][4] = "";
        }
        KPTttb2[3][5] = "";

        //NotParty
        temp = countNumBenefIndicator(jsonData, "O1_KPT_D2/O1_KPT_D2_Party", ['No'], "Baseline", "1", "KPT", "1");
        temp2 = countNumBenefIndicator(jsonData, "O1_KPT_D2/O1_KPT_D2_Party", ['No'], "Endline", "1", "KPT", "1");

        KPTttb2[4] = new Array();
        KPTttb2[4][0] = "Normal coocking";
        KPTttb2[4][1] = temp;
        KPTttb2[4][2] = temp / KPTDAY2PANEL;
        if (endline != 0) {
            KPTttb2[4][3] = temp2;
            KPTttb2[4][4] = temp2 / KPTDAY2PANEL2;
        } else {
            KPTttb2[4][3] = 0;
            KPTttb2[4][4] = "";
        }
        KPTttb2[4][5] = "";

        for (uu = 0; uu < 5; uu++) {
            KPTttb2[uu + 5] = new Array();
            KPTttb2[uu + 5][0] = list[uu];
            KPTttb2[uu + 5][1] = vs[uu];
            KPTttb2[uu + 5][2] = vs[uu] / KPTDAY2PANEL;
            if (endline != 0) {
                KPTttb2[uu + 5][3] = vs2[uu];
                KPTttb2[uu + 5][4] = vs2[uu] / KPTDAY2PANEL2;
            } else {
                KPTttb2[uu + 5][3] = 0;
                KPTttb2[uu + 5][4] = "";
            }
            KPTttb2[uu + 5][5] = "";
        }

        KPTttb2[10] = new Array();
        KPTttb2[10][0] = "TOTAL";
        KPTttb2[10][1] = KPTDAY2PANEL;
        KPTttb2[10][2] = KPTDAY2PANEL / KPTDAY2PANEL;
        if (endline != 0) {
            KPTttb2[10][3] = KPTDAY2PANEL2;
            KPTttb2[10][4] = KPTDAY2PANEL2 / KPTDAY2PANEL2;
        } else {
            KPTttb2[10][3] = 0;
            KPTttb2[10][4] = "";
        }
        KPTttb2[10][5] = "";

        ///TABLE3 for complete data per fuel
        KPTttb3[0] = new Array();

        KPTttb3[0][0] = "";
        KPTttb3[0][1] = "Baseline";
        KPTttb3[0][2] = "";
        KPTttb3[0][3] = "Endline";
        KPTttb3[0][4] = "";
        KPTttb3[0][5] = "";

        KPTttb3[1] = new Array();

        KPTttb3[1][0] = "";
        KPTttb3[1][1] = "Valid Sample";
        KPTttb3[1][2] = "%";
        KPTttb3[1][3] = "Valid Sample";
        KPTttb3[1][4] = "%";
        KPTttb3[1][5] = "";

        for (uu = 0; uu < 5; uu++) {
            KPTttb3[uu + 2] = new Array();
            KPTttb3[uu + 2][0] = list[uu];
            KPTttb3[uu + 2][1] = vs[uu];
            KPTttb3[uu + 2][2] = vs[uu] / KPTDAY2PANEL;
            if (endline != 0) {
                KPTttb3[uu + 2][3] = vs2[uu];
                KPTttb3[uu + 2][4] = vs2[uu] / KPTDAY2PANEL2;
            } else {
                KPTttb3[uu + 2][3] = 0;
                KPTttb3[uu + 2][4] = "";
            }
            KPTttb3[uu + 2][5] = "";
        }

        KPTttb3[6] = new Array();

        KPTttb3[6][0] = "TOTAL";
        KPTttb3[6][1] = KPTDAY2PANEL;
        KPTttb3[6][2] = KPTDAY2PANEL / KPTDAY2PANEL;
        if (endline != 0) {
            KPTttb3[6][3] = KPTDAY2PANEL2;
            KPTttb3[6][4] = KPTDAY2PANEL2 / KPTDAY2PANEL2;
        } else {
            KPTttb3[6][3] = 0;
            KPTttb3[6][4] = "";
        }
        KPTttb3[6][5] = "";

        return tb;
    }

Function generateTableValueIndicator42Fuel

  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
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
    function generateTableValueIndicator42Fuel(item, newclm, header, USD) {
        
        var idx = key["idx"];
        var sst = SpreadsheetApp.openById(idx);
        var exchange = sst.getSheetByName("Exchange");

        var tb = [];
        var TotalBL = 0;
        var TotalBU = 0;
        var TotalEL = 0;
        var TotalEU = 0;
        var cc = 1;
        var templc = "";
        ex = 1;

        var exdata = exchange.getRange(1, 1, exchange.getLastRow(), exchange.getLastColumn()).getValues();
        for (id = 0; id < jsonData.length; id++) {
            if (filterMain(id, jsonData, Country, Partner, Camp, Status, Year, Gender, Age, "ALL", 0, 0) != 9) {
                continue;
            }
            templc = jsonData[id]['LocalCurrency'];

            break;
        }

        for (i = 0; i < exdata.length; i++) {

            if (exdata[i][0] == templc) {
                localcurrency = exdata[i][1];
                ex = exdata[i][2];
            }
        }

        tb[0] = new Array();
        tb[0][0] = "";
        tb[0][1] = "Baseline";
        tb[0][2] = "";
        tb[0][3] = "Endline";
        tb[0][4] = "";
        tb[0][5] = "Impact";

        tb[1] = new Array();
        tb[1][0] = "";
        tb[1][1] = "Average (" + localcurrency + ")";
        tb[1][2] = "Average (USD)";
        tb[1][3] = "Average (" + localcurrency + ")";
        tb[1][4] = "Average (USD)";
        tb[1][5] = String.fromCharCode(10) + "▲ (USD)";

        var u = 2;

        var expbl = new Array();
        var expel = new Array();

        for (iz = 0; iz < header.length; iz++) {
            expbl[iz] = Count2(item, header[iz], "Baseline");
            expel[iz] = Count2(item, header[iz], "Endline");
        }

        for (iz = 0; iz < header.length; iz++) {

            if (expbl[iz] == 0 && expel[iz] == 0) {
                continue;
            }

            tb[u] = new Array();
            tb[u][0] = newclm[iz];
            if (totaloutputbl != 0) {
                tb[u][1] = expbl[iz] / totaloutputbl;
                TotalBL = TotalBL + expbl[iz];
                tb[u][2] = expbl[iz] * ex / totaloutputbl;
                TotalBU = TotalBU + expbl[iz] * ex;
            } else {
                tb[u][1] = "";
                tb[u][2] = "";
            }
            if (totaloutputel != 0) {
                tb[u][3] = expel[iz] / totaloutputel;
                TotalEL = TotalEL + expel[iz];
                tb[u][4] = expel[iz] * ex / totaloutputel;
                TotalEU = TotalEU + expel[iz] * ex;
                if (totaloutputbl != 0) { tb[u][5] = tb[u][4] - tb[u][2]; } else { tb[u][5] = ""; }
            } else {
                tb[u][3] = "";
                tb[u][4] = "";
                tb[u][5] = "";
            }
            u++;
        }

        tb[u] = new Array();
        tb[u][0] = "TOTAL";
        if (totaloutputbl != 0) {
            tb[u][1] = TotalBL / totaloutputbl;
            tb[u][2] = TotalBU / totaloutputbl;
        } else {
            tb[u][1] = "";
            tb[u][2] = "";
        }
        if (totaloutputel != 0 && endline != 0) {
            tb[u][3] = TotalEL / totaloutputel;
            tb[u][4] = TotalEU / totaloutputel;
            if (totaloutputbl != 0) { tb[u][5] = tb[u][4] - tb[u][2]; } else { tb[u][5] = ""; }
        } else {
            tb[u][3] = "";
            tb[u][4] = "";
            tb[u][5] = "";
        }

        return tb;
    }

Function generateTableValueIndicator52Light

 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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
    function generateTableValueIndicator52Light(item, header, header2) { //USED
        var tb = [];
        var TotalB = 0;
        var TotalE = 0;
        var bl = new Array();
        var el = new Array();
        var cc = 1;

        bl[0] = 0;
        bl[1] = 0;
        el[0] = 0;
        el[1] = 0;
        for (i = 0; i < item.length; i++) {
            if (item[i]["PARTNER_INFO/BE"] == "Baseline" && item[i][header2] != "") {
                var ok = 0;
                if (item[i][header] == "SolarL" && item[i][header2] == "Solar") { bl[0] = bl[0] + 1; ok = 1; }
                if (item[i][header] == "Electricity" && item[i][header2] == "Electricity") { bl[0] = bl[0] + 1; ok = 1; }
                if (item[i][header] == "Kerosene_Gas" && item[i][header2] == "Kerosene_gas_lantern") { bl[0] = bl[0] + 1; ok = 1; }
                if (item[i][header] == "Battery_Torch" && item[i][header2] == "Torch_Battery") { bl[0] = bl[0] + 1; ok = 1; }
                if (item[i][header] == "HomeSolarSystem" && item[i][header2] == "Home_Solar_System") { bl[0] = bl[0] + 1; ok = 1; }
                if (ok == 0) { bl[1] = bl[1] + 1 };
            }
            if (item[i]["PARTNER_INFO/BE"] == "Endline" && item[i][header2] != "") {
                var ok = 0;
                if (item[i][header] == "SolarL" && item[i][header2] == "Solar") { el[0] = el[0] + 1; ok = 1; }
                if (item[i][header] == "Electricity" && item[i][header2] == "Electricity") { el[0] = el[0] + 1; ok = 1; }
                if (item[i][header] == "Kerosene_Gas" && item[i][header2] == "Kerosene_gas_lantern") { el[0] = el[0] + 1; ok = 1; }
                if (item[i][header] == "Battery_Torch" && item[i][header2] == "Torch_Battery") { el[0] = el[0] + 1; ok = 1; }
                if (item[i][header] == "HomeSolarSystem" && item[i][header2] == "Home_Solar_System") { el[0] = el[0] + 1; ok = 1; }
                if (ok == 0) { el[1] = el[1] + 1 };
            }
        }
        tb[0] = new Array();
        tb[0][0] = "";
        tb[0][1] = "Baseline";
        tb[0][2] = "";
        tb[0][3] = "Endline";
        tb[0][4] = "";
        tb[0][5] = "Impact";

        tb[1] = new Array();
        tb[1][0] = "";
        tb[1][1] = "Sample";
        tb[1][2] = "%";
        tb[1][3] = "Sample";
        tb[1][4] = "%";
        tb[1][5] = "%";

        TotalB = bl[0] + bl[1];
        TotalE = el[0] + el[1];

        tb[2] = new Array();
        tb[2][0] = "Utilising";
        tb[2][1] = bl[0];
        tb[2][2] = bl[0] / totaloutputbl;
        tb[2][3] = el[0];
        if (TotalE != 0 && endline != 0) {

            tb[2][4] = el[0] / totaloutputel;
            tb[2][5] = tb[2][4] - tb[2][2];
        } else {
            tb[2][4] = "";
            tb[2][5] = "";
        }

        tb[3] = new Array();
        tb[3][0] = "Not utilising";
        tb[3][1] = bl[1];
        tb[3][2] = bl[1] / totaloutputbl;
        tb[3][3] = el[1];
        if (TotalE != 0 && endline != 0) {

            tb[3][4] = el[1] / totaloutputel;
            tb[3][5] = tb[3][4] - tb[3][2];
        } else {
            tb[3][4] = "";
            tb[3][5] = "";
        }

        tb[4] = new Array();
        tb[4][0] = "TOTAL";
        tb[4][1] = TotalB;
        if (TotalB != 0) {
            tb[4][2] = TotalB / TotalB;
        } else {
            tb[4][2] = "";
        }
        tb[4][3] = TotalE;
        if (TotalE != 0 && endline != 0) {
            tb[4][4] = TotalE / TotalE;
            tb[4][5] = "";
        } else {
            tb[4][4] = "";
            tb[4][5] = "";
        }
        return tb;
    }