PARTNERDATA Component

Function CREATEPARTNERDATA

CREATEPARTNERDATA(year, formid, tempid)

The function creates Partner Data Sheet if it doesn’t exist, and pulls any new primary data from KoboToolbox into ORIGNALDATA sheet

Arguments

  • year (string) – the current year

  • formid (string) – id of the form

  • tempid (string) – id of the template

1
2
3
4
5
6
7
function CREATEPARTNERDATA(year, formid, tempid) {
    if (year < 2017 || year == null) { return; }
    /* mainfolderid {string}  - id of main folder */
    var mainfolderid = createFolder(0, key["MAINFOLDER"]);
    YearFolder = createFolder(mainfolderid, "ALLFILES" + year);
    /*ANALYSISFOLDER {string} - id of COUNTRY_ANALYSIS folder for specific year*/
    var ANALYSISFOLDER = createFolder(YearFolder, "COUNTRY_ANALYSIS" + year);

  • Other folder > Partner Data file

This is the image caption

Example of Partner Data file

 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
    var datafile = createFile(key["OTHERFOLDERID"], "PartnerDataFile");
    var nullv = "OK";

    /*idSS {string} - id of the file (Partner Data Sheet) in Analysis folder [General Component]*/
    var idSS = getFile(ANALYSISFOLDER, year + "Partner Data Sheet");

    /*if file doen't exist*/
    if (idSS == null) {
        var folder = DriveApp.getFolderById(ANALYSISFOLDER);
        var file = DriveApp.getFileById(datafile);
        file.makeCopy(year + "Partner Data Sheet", folder);
        nullv = "null";

        /*Obtaining id of the file*/
        var files = folder.getFiles();
        while (files.hasNext()) {
            var file2 = files.next();
            if (file2.getName() == year + "Partner Data Sheet") {
                idSS = file2.getId();
            }
        }

        /*putting parametors in new sheet*/
        var anss = SpreadsheetApp.openById(idSS);
        var para = anss.getSheetByName("PARAMATOR");

        var data = para.getRange(1, 1, para.getLastRow() + 1, para.getLastColumn()).getValues();
        data[1][data[0].indexOf("APIDATA")] = key["APITOKEN2"];
        data[1][data[0].indexOf("APITEMPLATE")] = key["APITOKEN"];
        data[1][data[0].indexOf("DATAID")] = formid;
        data[1][data[0].indexOf("TEMPLATEID")] = tempid;
        para.getRange(1, 1, para.getLastRow() + 1, para.getLastColumn()).setValues(data);

    }

    /*registering url*/
    var csv5 = "CSVURL5" + year;
    var url5 = DriveApp.getFileById(idSS).getUrl();
    keyinputs[csv5] = url5;
    key[csv5] = url5;

    /*puts PPG names from Template*/
    var controldata = getData("CONTROL");
    var c1 = controldata[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year");
    var c2 = controldata[0].indexOf("Username");
    var c3 = controldata[0].indexOf("_id");
    var c4 = controldata[0].indexOf("CONTACT_INFORMATION/Country");


    var anss = SpreadsheetApp.openById(idSS);
    var ppgsheet = anss.getSheetByName("PPG");
    var para = anss.getSheetByName("PARAMATOR");
    var fid = para.getRange(2, 4).getValue();
    /*connecting to KOBO [GENERAL component]*/
    var PPGNAMES = connectKobotoolbox(key["APITOKEN"], fid);

    var PPGdata = [];
    var y = 0;

    var username = "";


    for (c = 1; c < controldata.length; c++) {
        if (nullv != "null") { break; }
        if (controldata[c][c1] != year) { continue; }
        PPGdata[y] = new Array();
        PPGdata[y][0] = controldata[c][c2];

        for (p = 0; p < PPGNAMES.length; p++) {
            if (controldata[c][c3] == PPGNAMES[p]["_id"]) {
                PPGdata[y][1] = PPGNAMES[p]["PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/PPG"].replace("_", " ");

                y++;

                /*puts partner names from template*/
                for (ipp = 0; ipp < PPGNAMES[p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'].length; ipp++) {
                    PPGdata[y] = new Array();
                    PPGdata[y][0] = (PPGNAMES[p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ipp]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Name'].replace(/_/g, " "));
                    PPGdata[y][1] = "";
                    y++;
                }

            }
        }
    }

    if (nullv == "null") { ppgsheet.getRange(ppgsheet.getLastRow() + 1, 1, PPGdata.length, 2).setValues(PPGdata); }

    keyinputs["idx"] = idSS;
    /*this global variable controls the logic of analysis sheet across the years*/
    key["idx"] = idSS;
    /*function calls from PartnerData component*/
    ALLDATAPS();

  • schedule DATABRIDGE task

This is the image caption

Example of DATABRIDGE file

1
2
3
4
    SCHEDULE("DATABRIDGE", year, "PS", "xx", "Start");

    return 1;
}

Function ALLDATAPS

ALLDATAPS()

The function imports data from Kobo, syncronises, harmonises, calculates dependency ratio and filters

 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
function ALLDATAPS() {
    var idx = key["idx"];
    var sst = SpreadsheetApp.openById(idx);
    var parametor = sst.getSheetByName("PARAMATOR");
    var DATAID = parametor.getRange(2, 3).getValue();

    var url = 'https://kobocat.unhcr.org/api/v1/data/' + DATAID 

    try { var json = UrlFetchApp.fetch(url, fetchSurvey()); } catch (e) { var error = e; Logger.log(error.message); }
    var jsonData = JSON.parse(json.getContentText());
    Logger.log(jsonData);

    var originaldatasheet = sst.getSheetByName("ORIGINALDATA");
    var LC = originaldatasheet.getLastColumn();
    var LR = originaldatasheet.getLastRow();
    originaldatasheet.getRange(2, 1, LR, LC).clear();

    var data = originaldatasheet.getRange(1, 1, jsonData.length + 1, LC).getValues();
    var y = 1;

    for (i = 0; i < jsonData.length; i++) {
        for (ii = 0; ii < LC; ii++) {
            if (jsonData[i][data[0][ii]] != null) {
                data[y][ii] = jsonData[i][data[0][ii]];
            } else {
                data[y][ii] = "";
            } 
        }
        y++;
    }

    originaldatasheet.getRange(1, 1, jsonData.length + 1, LC).setValues(data);
    /*function calls from PartnerData component*/
    Syncrops();

}

Function Syncrops

Syncrops()

The function coordinates the process of cleaning harmonisation on 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
 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
function Syncrops() {
    var idx = key["idx"];
    var sst = SpreadsheetApp.openById(idx);
    var originaldatasheet = sst.getSheetByName("ORIGINALDATA");
    var alldatasheet = sst.getSheetByName("ALLDATA");
    var parametor = sst.getSheetByName("PARAMATOR");
    var ex = sst.getSheetByName("ExchangeRate");
 
    var LCO = originaldatasheet.getLastColumn();
    var LRO = originaldatasheet.getLastRow();
    var LCD = alldatasheet.getLastColumn();
    var LRD = alldatasheet.getLastRow();

    var Origin = originaldatasheet.getRange(1, 1, LRO, LCO).getValues();
    var Destination = alldatasheet.getRange(1, 1, LRD, LCD).getValues();

    var c1 = Destination[0].indexOf("_id");
    var c2 = Origin[0].indexOf("_id");
    var c3 = Destination[0].indexOf("Financial Access");
    var c33 = Destination[0].indexOf("Land Access");
    var c333 = Destination[0].indexOf("Employment Access");

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

    /*new data integrated*/
    { 
        var cn = 0;
        for (i = 1; i < LRO; i++) {
            if (indexh[0].indexOf(parseInt(Origin[i][c2])) == -1) {
                Destination.push(Origin[i]);
                cn = 1;
            }
        }
    }
    /*Detect the deleted data*/
    {
        /*Get the original id*/
        var countd = 0;
        var indexv2 = originaldatasheet.getRange(1, c2 + 1, originaldatasheet.getLastRow(), 1).getValues();
        var indexh2 = indexv2[0].map(function (col, i) {
            return indexv2.map(function (row) {
                return row[i]
            });
        });

        for (d = 1; d < Destination.length; d++) {
            if (indexh2[0].indexOf(parseInt(Destination[d][c2])) == -1) {
                Destination[d][0] = "XDELETED";
                countd++; cn = 1;
            }
        }
    }

    {/*Financial Access INFO / PPG From template*/
        var formid = parametor.getRange(2, 4).getValue();
        var datatemp = connectKobotoolbox(key["APITOKEN"], formid);

        /*2017 ERROR TREATMENT : TO BE DELETED AFTER 2018 */
        for (iiii = 0; iiii < datatemp.length; iiii++) {
            if (datatemp[iiii]["PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year"] == "2017" && datatemp[iiii]["CONTACT_INFORMATION/Country"] == "Zambia") {
                datatemp[iiii]["_id"] = "373433";
            }
        }

        var c4 = Destination[0].indexOf("TemplateID");
        var c5 = Destination[0].indexOf("PPG");
        var countryb;
        var r, r2, r3, rr;
        for (ii = 1; ii < Destination.length; ii++) {

            if (Destination[ii][c4] == countryb) {
                Destination[ii][c3] = r
                Destination[ii][c5] = rr;
            }
            else {
                for (iii = 0; iii < datatemp.length; iii++) {
                    if (Destination[ii][c4] == datatemp[iii]["_id"]) {
                        r = datatemp[iii]["PROBLEM_STATEMENT/LEGAL_ACCESS/L_Assessment_Finance"];
                        r2 = datatemp[iii]["PROBLEM_STATEMENT/LEGAL_ACCESS/L_Assessment_Land"];
                        r3 = datatemp[iii]["PROBLEM_STATEMENT/LEGAL_ACCESS/L_Assessment_Employment"];
                        rr = datatemp[iii]["PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/PPG"].replace("_", " ");
                        countryb = datatemp[iii]["_id"];
                        Destination[ii][c3] = r;
                        Destination[ii][c33] = r2;
                        Destination[ii][c333] = r3;
                        Destination[ii][c5] = rr;
                        break;
                    }
                }
            }
        }
    }

    {/*COST USD CONVERSION*/

        /*CONCERNED COLUMNS*/
        var CCost1a = Destination[0].indexOf("G_OUTPUT1/O1_FPA_FarmingAssets/O1_FPA_FarmingAssets_Cost");
        var CCost1b = Destination[0].indexOf("G_OUTPUT1/O1_FPA_LivestockAssets/O1_FPA_LivestockAssets_Cost");
        var CCost1c = Destination[0].indexOf("G_OUTPUT1/O1_FPA_FisheryAssets/O1_FPA_FisheryAssets_Cost");
        var CCost11 = Destination[0].indexOf("O1FPACostUSD");
        var CCost2 = Destination[0].indexOf("G_OUTPUT1/O1_FPA_001/O1_FPA_Cash_Cost");
        var CCost22 = Destination[0].indexOf("O1FPACashCostUSD");
        var CCost3 = Destination[0].indexOf("G_OUTPUT1/O1_MPPA1/O1_MPPA1_Cost");
        var CCost33 = Destination[0].indexOf("O1MPPACostUSD");
        var CCost4 = Destination[0].indexOf("G_OUTPUT2/O2_FPA_001/O2_FPA_Cost");
        var CCost44 = Destination[0].indexOf("O2FPACostUSD");
        var CCost5 = Destination[0].indexOf("G_OUTPUT2/O2_FPA_001/O2_FPA_Cash_Cost");
        var CCost55 = Destination[0].indexOf("O2FPACashCostUSD");
        var CCost6 = Destination[0].indexOf("G_OUTPUT3/O3_FPA_001/O3_FPA_Cost");
        var CCost66 = Destination[0].indexOf("O3FPACostUSD");
        var CCost7 = Destination[0].indexOf("G_OUTPUT3/O3_FPA_001/O3_FPA_Cash_Cost");
        var CCost77 = Destination[0].indexOf("O3FPACashCostUSD");

        /*get the Echange sheet*/
        var exdata = ex.getDataRange().getValues();
        var indexv = ex.getRange(1, 1, ex.getLastRow(), 1).getValues();
        var indexh = indexv[0].map(function (col, i) {
            return indexv.map(function (row) {
                return row[i]
            });
        });

        /*Gets column of local currency for each data*/
        var lc = Destination[0].indexOf("LocalCurrency");

       
        for (c = 1; c < Destination.length; c++) {

            var exr = indexh[0].indexOf(Destination[c][lc].toString());
            var exrate = exdata[exr][1];

            if (Destination[c][CCost1a] != "" || Destination[c][CCost1b] != "" || Destination[c][CCost1c] != "") {
                Destination[c][CCost11] = (Destination[c][CCost1a] + Destination[c][CCost1b] + Destination[c][CCost1c]) * exrate;
            }
            if (Destination[c][CCost2] != "") { Destination[c][CCost22] = Destination[c][CCost2] * exrate; }
            if (Destination[c][CCost3] != "") { Destination[c][CCost33] = Destination[c][CCost3] * exrate; }
            if (Destination[c][CCost4] != "") { Destination[c][CCost44] = Destination[c][CCost4] * exrate; }
            if (Destination[c][CCost5] != "") { Destination[c][CCost55] = Destination[c][CCost5] * exrate; }
            if (Destination[c][CCost6] != "") { Destination[c][CCost66] = Destination[c][CCost6] * exrate; }
            if (Destination[c][CCost7] != "") { Destination[c][CCost77] = Destination[c][CCost7] * exrate; }
        }

    }

    /*Data Cleaning*/
    {  
        var cc1 = Destination[0].indexOf("DATA_CLEANED");
        for (ii = 1; ii < Destination.length; ii++) {

            if (Destination[ii][cc1] == "OK") {
                continue;
            }
            /*formulas for data cleaning*/
            Destination[ii][cc1] = "OK";
        }
    }


    { 
        if (cn == 1 && Destination.length > 0) {
            alldatasheet.getRange(1, 1, Destination.length, Destination[0].length).setValues(Destination).setFontColor("black");
        }
    }
    /*Delete the deleted roes at once*/
    {
        if (countd > 0) {
            alldatasheet.getRange(2, 1, Destination.length - 1, Destination[0].length).sort(1);
            alldatasheet.deleteRows(Destination.length + 1 - countd, countd);
        }
    }
}