CONTROL Component

Function synchronizeAllForms

synchronizeAllForms(pkey)

The function synchronizes the forms and data from Kobotoolbox for specific year (key[“AUTO YEAR”])

Arguments

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

Returns

keyinputs – the list of key parameters.

alternate text

The block diagram of synchronizeAllForms function

Warning

Update the schema with the latest names of the functions

 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
function synchronizeAllForms(pkey) {
    key = pkey;
    /*main {Object}  - get the sheet "TEMPSURVEY" [LHS monitoring survey] */
    var main = ss.getSheetByName("TEMPSURVEY");
    /*bsurvey {Object}  - get the sheet "Bsurvey" [LHS beneficiary and partner survey] */
    var bsurvey = ss.getSheetByName("BSURVEY");
    /*paramater {Object}  - get the sheet "PARAMATERS"*/
    var paramater = ss.getSheetByName("PARAMATERS");
    /*tempstring {string}  - Automatic mode from  [sheet "PARAMATERS"]*/
    var tempstring = paramater.getRange(5, 1).getValue();
    keyinputs["AUTOYEAR"] = tempstring;
    key["AUTOYEAR"] = tempstring;
    /*tempstring {string}  - Automatic mode Up to:  [sheet "PARAMATERS"]*/
    var tempstring2 = paramater.getRange(8, 1).getValue();
    keyinputs["AUTOYEAR2"] = tempstring2;
    key["AUTOYEAR2"] = tempstring2;
    /*calls the function [Control Component]*/
    extractKoboForms(key["APITOKEN"], main);    
    extractKoboForms(key["APITOKEN2"], bsurvey);
    /*calls the function [User managment Component]*/
    UPDATEUSERNAME("skip");
    /*calls the function [Control Component]*/
    extractAllTemplates();
    /*calls the function [Control Component]*/
    extractMetaFiles();
    /*calls the function [Control Component]*/
    countDataSubmissions();
    return (keyinputs);

}

Function countDataSubmissions

countDataSubmissions(temp, btemp)

The function counts and checks the data submissions for beneficiary and partner surveys and displays the results on CONTROL sheet

Arguments

  • temp (Object) – data from CONTROL sheet

  • btemp (Object) – data from BSURVEY sheet (forms for benefeciairy and partner surveys)

  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
function countDataSubmissions() {
    /*temp {array}  - get data from the sheet "CONTROL" */
    var temp = getData("CONTROL");
    /*btemp {Object}  - get data from sheet "Bsurvey" [LHS beneficiary and partner survey] */
    var btemp = getData("BSURVEY");
    var fid = 0;
    var year = 0;
    /*c5 {number}  - index of Baseline Column in data [Control Sheet] */
    var c5 = temp[0].indexOf("Baseline");
    /*c6 {number}  - index of Endline Column in data [Control Sheet] */
    var c6 = temp[0].indexOf("Endline");
    /*c7 {number}  - index of Midline in data [Control Sheet] */
    var c7 = temp[0].indexOf("Midline");

    for (i = 2; i < temp.length; i++) {
        if (temp[i][2] == "") { continue; }
        {
            /* bcount - data count for Baseline  */
            var bcount = 0;
            /* ecount - data count for Endline  */
            var ecount = 0;
            /* mcount - data count for Midline  */
            var mcount = 0;
            /* pcount - for partner survey*/
            var pcount = 0;
            /*f2 {number}  - index of formid in data [Control Sheet] */
            var f2 = temp[i][temp[0].indexOf("formid")];
            /*user {number}  - index of Username in data [Control Sheet] */
            var user = temp[i][temp[0].indexOf("Username")];
            /*dataid {number}  - index of _id in data [Control Sheet] */
            var dataid = temp[i][temp[0].indexOf("_id")];
            /*y2 {number}  - index of year in data [Control Sheet] */
            var y2 = temp[i][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")];

            if (y2 != year)
            {
                var realformid = 0;
                var partnerformid = 0;
                var partnersurveyYN = 1;
                var beneficiarysurveyYN = 1;

                for (t = 1; t < btemp.length; t++) {
                    if (btemp[t][btemp[0].indexOf("YEAR")] == temp[i][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] &&
                        btemp[t][btemp[0].indexOf("title")].indexOf("TRAINING (DEMO)") == -1 && btemp[t][btemp[0].indexOf("title")].indexOf("PRINTOUT") == -1 &&
                        btemp[t][btemp[0].indexOf("title")].indexOf("Partner") == -1) {
                         /*realformid {number}  - index of formid (benefeciary survey) in data [CBSURVEY Sheet] */
                        realformid = btemp[t][btemp[0].indexOf("formid")];
                    }

                    if (btemp[t][btemp[0].indexOf("YEAR")] == temp[i][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] &&
                      btemp[t][btemp[0].indexOf("title")].indexOf("Partner") > -1) {
                        /*partnerformid {number}  - index of formid (partner survey) in data [CBSURVEY Sheet] */
                        partnerformid = btemp[t][btemp[0].indexOf("formid")];
                    }
                }
                 /*data {array}  - data array for Beneficiary survey [KOBODATA function]*/
                try { var data = connectKobotoolbox(key["APITOKEN2"], realformid); } catch (e) { beneficiarysurveyYN = 0; }
                /*datas {array}  - data array for Parner survey [KOBODATA function]*/
                try { var data2 = connectKobotoolbox(key["APITOKEN2"], partnerformid); } catch (e) { partnersurveyYN = 0; }
                /*fid {number}  - index of formid in data [Control Sheet] */
                fid = temp[i][temp[0].indexOf("formid")];
                year= temp[i][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")];
            }
            /*If data for beneficiary survey exists*/
            if (beneficiarysurveyYN == 1) {
                for (ii = 0; ii < data.length; ii++) {
                    /*if user from KOBO is not equal user on the Control Sheet and if TEMPLATEID column from KOBO is not equal fromid from Control Sheet*/
                    if (/* data[ii]["UID"] != user &&  */data[ii]["TemplateID"] != dataid) { continue; }
                   /*check PARTNER_INFO/BE from Kobo beneficiary form*/
                    if (data[ii]["PARTNER_INFO/BE"] == "Baseline") { bcount++; keyinputs["UNA" + dataid] = "LOCKED"; }
                    else if (data[ii]["PARTNER_INFO/BE"] == "Endline") { ecount++; keyinputs["UNA" + dataid] = "LOCKED"; }
                    else if (data[ii]["PARTNER_INFO/BE"] == "Midline") { mcount++; keyinputs["UNA" + dataid] = "LOCKED"; }
                }
            }

            if (partnersurveyYN == 1) {
                for (ii2 = 0; ii2 < data2.length; ii2++) {
                    if (/* data2[ii2]["UID"] != user &&  */data2[ii2]["TemplateID"] != dataid) { continue; } else { pcount++; keyinputs["UNA" + dataid] = "LOCKED"; }
                }
            }
            /*Baseline column from Control Sheet equal bcount [Baseline Count]*/
            if (bcount > 0) { temp[i][temp[0].indexOf("Baseline")] = bcount; }
            /*Endline column from Control Sheet equal ecount [Endline Count]*/
            if (ecount > 0) { temp[i][temp[0].indexOf("Endline")] = ecount; }
            /*Midline column from Control Sheet equal mcount [Midline Count]*/
            if (mcount > 0) { temp[i][temp[0].indexOf("Midline")] = mcount; }
            /*Partner_Survey column from Control Sheet equal pcount [Partner Countr]*/
            if (pcount > 0) { temp[i][temp[0].indexOf("Partner_Survey")] = pcount; }
            /*ck1 {string} name of the key*/
            var ck1 = "CLEAN" + dataid;
            if (key[ck1] != null && (bcount + ecount + mcount) > 0) {
                /*Column Cleaned data on Control Sheet*/
                /*Calculating the percentage of cleaned data*/
                temp[i][temp[0].indexOf("Cleaning")] = Math.round(key[ck1] / (bcount + ecount + mcount) * 100) / 100;
            }
        }

        {
            /*Generating the keys, dates for baseline and endline*/
            var bdate = 'BDATE' + String(temp[i][temp[0].indexOf("_id")]);
            var edate = 'EDATE' + String(temp[i][temp[0].indexOf("_id")]);

            if (key[bdate] != null) { temp[i][temp[0].indexOf("B_Date")] = key[bdate]; }
            if (key[edate] != null) { temp[i][temp[0].indexOf("E_Date")] = key[edate]; }
        }

        {
            /*Generating the key*/
            /*UN is current username*/
            var kk = 'UN' + String(temp[i][temp[0].indexOf("_id")]);
            /**/
            var hk = "FIDSTRING" + temp[i][temp[0].indexOf("formid")];
            /*EDIT LINK column on Control Sheet.Generating the links by using the keys*/
            temp[i][temp[0].indexOf("EDIT LINK")] = '=hyperlink("https://kobocat.unhcr.org/' + key["TUSERNAME"] + '/forms/' + key[hk] + '/edit-data/' + temp[i][temp[0].indexOf("_id")] + '","EDIT LINK")'; //EDIT LINKS
        }

    }
    /*Save data on Control Sheet*/
    putData("CONTROL", temp);
 
}

Function calculateCleaningProgress

calculateCleaningProgress(id, username, control, temp)

The function counts the data cleaned and calculate the % of data cleaning progress

Arguments

  • id (String) – id of the form [data id]

  • username (String) – account name

  • control (String) – the name of the sheet to get (CONTROL sheet).

  • temp (Object) – data from CONTROL 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
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
function calculateCleaningProgress(id, username) {
    /*control {Object}  - get the sheet "CONTROL */
    var control = ss.getSheetByName("CONTROL");
    /*temp {array}  - data from sheet "CONTROL" */
    var temp = getData("CONTROL");
    var year = 0;
    /*c1 {number}  - index of _id (DATAID) in data [Control Sheet] */
    var c1 = temp[0].indexOf("_id");
    /*c2 {number}  - index of Cleaning in data [Control Sheet] */
    var c2 = temp[0].indexOf("Cleaning");
    var output = control.getRange(1, c2 + 2, control.getLastRow(), 1).getValues();
    /*c5 {number}  - index of Baseline Column in data [Control Sheet] */
    var c5 = temp[0].indexOf("Baseline");
    /*c6 {number}  - index of Endline Column in data [Control Sheet] */
    var c6 = temp[0].indexOf("Endline");
    /*c8 {number}  - index of Midline Column in data [Control Sheet] */
    var c8 = temp[0].indexOf("Midline");

    for (i = 2; i < temp.length; i++) {

        if (id != "ALL" && temp[i][c1] != id ) { continue; }
        if (temp[i][c5] == "" && temp[i][c6] == "" && temp[i][c8] == "") { continue; }

        if (id == "ALL") { dataid = temp[i][c1]; } else { dataid = id; }
         /*ck1 {string} name of the key*/
         /*CLEAN: # of cleaned data ready for analysis, updated when datasheet is updated.*/
        var ck1 = "CLEAN" + dataid;
        if (key[ck1] == null) { keyinputs[ck1] = 0; }
        var cleancount = 0;
        /*url {string} name of the key*/
        /*CSVURL4: URL for beneficiary data sheet */
        var url = key["CSVURL4" + dataid];
        if (url == null) { continue; }
        var idSS = url.replace('https://docs.google.com/spreadsheets/d/', '');
        idSS = idSS.replace('/edit?usp=drivesdk', '');

        try {
            var anss = SpreadsheetApp.openById(idSS);
        } catch (e) {
            keyinputs[ck1] = 0;
            output[i][0] = 0; continue;
        }
        /*control {Object}  - get the sheet "ALLDATA" from open googlesheet*/
        var sheet = anss.getSheetByName("ALLDATA");
        var data = sheet.getDataRange().getValues();
        /*c3 {number}  - index of TEMPLATEID on ALLDATA Sheet [equal to DATAID on CONTROL Sheet] */
        var c3 = data[0].indexOf("TemplateID");
        /*c4 {number}  - index of DATA_CLEANED on ALLDATA Sheet] */
        var c4 = data[0].indexOf("DATA_CLEANED");
        /*c7 {number}  - index of UID (account) on ALLDATA Sheet */
        var c7 = data[0].indexOf("UID");

        var totalcount = 0;

        for (ii = 0; ii < data.length; ii++) {
            /*If Dataid from Control Sheet is not equal to TEMPLATEID from ALLDATA sheet*/
            /*If Username from Control Sheet is not equal to UID (account) from ALLDATA sheet*/
            if (temp[i][c1] != data[ii][c3] && temp[i][temp[0].indexOf("Username")] != data[ii][c7]) { continue; }
            totalcount++;
            /*if Value is under the column "DATA_CLEANED" is OK*/
            if (data[ii][c4] == "OK") { cleancount++; }
        }
        output[i][0] = Math.round(cleancount / (totalcount) * 100) / 100;
        /*If there is difference between the calculated number of cleaned data and existing number*/
        if (cleancount != key[ck1]) {
            /*year {number}  - index of YEAR on CONTROL Sheet] */
            var year = temp[i][temp[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")];
            /*country {number}  - index of COUNTRY on CONTROL Sheet] */
            var fcountry = temp[i][temp[0].indexOf("CONTACT_INFORMATION/Country")];
            /*formid {number}  - index of DATAID on CONTROL Sheet] */
            var formid = temp[i][temp[0].indexOf("_id")];
            /*UID {number}  - index of USERNAME on CONTROL Sheet */
            var UID = temp[i][temp[0].indexOf("Username")];
            /*If country is not equal to Username*/
           
                /*fusername {number} -index of USERNAME on CONTROL Sheet */
                var fusername = temp[i][temp[0].indexOf("Username")];
                /*fdutystation {number} -index of Station on CONTROL Sheet */
                var fdutystation = temp[i][temp[0].indexOf("CONTACT_INFORMATION/D_Station")];
     

            /*COUNTRYNOTE: Task status of updating Country Analysis Note.*/
            keyinputs["COUNTRYNOTE" + temp[i][temp[0].indexOf("_id")]] = "UPDATE";
            key["COUNTRYNOTE" + temp[i][temp[0].indexOf("_id")]] = "UPDATE";
            /*function calls from General Component*/
            SCHEDULE("ANALYSISNOTEEXTRACT", year, fcountry, fusername, fdutystation, formid);
            keyinputs[ck1] = cleancount;
        }
    }
    control.getRange(1, c2 + 2, control.getLastRow(), 1).setValues(output);
    
}

Function extractKoboForms

extractKoboForms(API, sheet)

The function extracts and lists all the forms deployed for monitoring template or beneficiary and partner surveys.

Arguments

  • API (String) – link

  • sheet (String) – name of the sheet [main - monitoring template; bsurvey - beneficiary and partner 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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
function extractKoboForms(API, Sheet) {
    /*function calls from GENERAL Component*/
    var jsonData = KOBOFORM(API);
    /*LC {number} - last column*/
    var LC = Sheet.getLastColumn();
    /*LR {number} - last row*/
    var LR = Sheet.getLastRow();
    Sheet.getRange(2, 1, LR, LC).clear();
    /*data {array}*/
    /*sheet TEMPSURVEY ["formid","id_string","title","data_created","date_modified","num_of_submissions","url","PUBLIC URL"]*/
    var data = Sheet.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] = "";
            }
        }
        /***************************************************/
        if (API == key["APITOKEN"]) {
            /*Function calls from GENERAL Component*/
            var load = connectKoboEnketo(API, data[y][data[0].indexOf("formid")]);
            data[y][data[0].indexOf("PUBLIC URL")] = load["enketo_url"];
            if (jsonData[i]["title"].indexOf("V4") > -1) {
                keyinputs["TEMPLATEURL"] = load["enketo_url"];
                key["TEMPLATEURL"] = load["enketo_url"];
            }
        }
        /***************************************************/
        y++;
    }

    /*Linking TEMPLATE and BSURVEY by year : Only for BSURVEY update*/
    if (API == key["APITOKEN2"]) {
     
        var y = 2017;
        for (id = 1; id < data.length; id++) { 

            var kf = "XSLF" + data[id][data[0].indexOf("formid")];
            data[id][data[0].indexOf("YEAR")] = y;
            for (ii = y; ii < 2030; ii++) {
                if (data[id][data[0].indexOf("title")].indexOf(String(ii)) > -1) {
                    data[id][data[0].indexOf("YEAR")] = ii;
                    break;
                }
            }
            data[id][data[0].indexOf("XSLFORM_FILE")] = key[kf];
        }
    }
    /*Function calls from GENERAL component*/
    TSORT(data, 1, 1);
    Sheet.getRange(1, 1, jsonData.length + 1, LC).setValues(data).setFontFamily("arial");
    return 1;
}


/**
 * The function extracts and lists all the templates submitted from the field

Function extractAllTemplates

extractAllTemplates(template, formdata)

The function extracts and lists all the templates submitted from the field

Arguments

  • template (String) – the name of the sheet to get (CONTROL sheet).

  • formdata (Object) – data from TEMPSURVEY sheet

alternate text

The block diagram of ALLTEMPLATE_SUBMITED function

  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
function extractAllTemplates() {
    /*template {Object}  - get the sheet "CONTROL */
    var template = ss.getSheetByName("CONTROL");
    /*formdata {array}  - get data from the sheet "TEMPSURVEY" [GENERAL COMPONENT] */
    var formdata = getData("TEMPSURVEY");
    var thiscount = 0;
    var LC = template.getLastColumn();
    var LR = template.getLastRow(); 
    /*array of headers from TEMPSURVEY sheet*/
    var data = template.getRange(1, 1, 2, LC).getValues();
    for (f = 1; f < formdata.length; f++) {

        if (formdata[f][formdata[0].indexOf("num_of_submissions")] == 0 || formdata[f][formdata[0].indexOf("title")].indexOf("Template") == -1) { continue; }
         /*get data for the temlate [GENERAL COMPONENT]*/
        var jsonData = connectKobotoolbox(key["APITOKEN"], formdata[f][0]);      
        if (thiscount == 0) { var y = 2; }
        for (i = 0; i < jsonData.length; i++) {
            if (yearcheck(jsonData[i]["PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year"]) != 1) { continue; }
            data[y] = new Array();
            /*Linking json data with header*/
            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] = ""; }
            }
            /*Linking Template and FORM ID*/
            data[y][data[0].indexOf("formid")] = formdata[f][formdata[0].indexOf("formid")]; 

            /*Generating URL for Edit link set*/
            var hk = "FIDSTRING" + data[y][data[0].indexOf("formid")];
            if (key[hk] == null) { keyinputs[hk] = formdata[f][formdata[0].indexOf("id_string")]; }
           
            /*ENDLINE Databridge passing ON/OFF*/
            { 
                /*EOF: ON or OFF for endline survey data to be exported and analysed.*/
                var eof = "EOF" + String(data[y][data[0].indexOf("_id")]);
                if (key[eof] == null) { data[y][data[0].indexOf("EOF")] = "False"; } else { data[y][data[0].indexOf("EOF")] = key[eof]; }
            }       

            y++;
        }
        thiscount++;
    }

    {/*Allocating Usernames or showing existing usernames*/
        for (i = 2; i < data.length; i++) {
            /*UN: The current username*/
            var k = 'UN' + data[i][data[0].indexOf("_id")];
            var tempusername = key[k];

            /*In case of new template*/
            if (key[k] == null || key[k] == "") 
            {
                var count = 0;
                for (ii = 1; ii < data.length; ii++) {
                    if (data[ii][data[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] != data[i][data[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")]) {
                        continue;
                    }
                    if (data[ii][data[0].indexOf("CONTACT_INFORMATION/Country")] == data[i][data[0].indexOf("CONTACT_INFORMATION/Country")]) {
                        count++;
                    }
                }
                /* if only one country template exists*/
                if (count == 1) 
                {
                    tempusername = data[i][data[0].indexOf("CONTACT_INFORMATION/Country")].toLowerCase();
                    keyinputs[k] = tempusername;
                } else 
                /* In case of more than one template in one country*/
                {
                    tempusername = data[i][data[0].indexOf("CONTACT_INFORMATION/Country")].toLowerCase() + String(data[i][data[0].indexOf("CONTACT_INFORMATION/D_Station")].toLowerCase()).substr(0, 1);
                    keyinputs[k] = tempusername;
                }
            }

            data[i][data[0].indexOf("Username")] = tempusername;
        }
    }

    {/*Other display*/
        if (data.length < 8) {
            for (l = data.length; l < 9; l++) {
                data[l] = new Array();
                for (ii = 0; ii < LC; ii++) {
                    data[l][ii] = "";
                }
            }
        }
        data[3][0] = "TASK";
        data[4][0] = '=counta(TASKS!B:B)-counta(TASKS!A:A)';
        data[6][0] = '=hyperlink("' + key["TEMPLATEURL"] + '","TEMPLATE URL")';
        /*function calls from GENERAL component */
        var mainfolderid = createFolder(0, key["MAINFOLDER"]);
        var newurl = DriveApp.getFolderById(mainfolderid).getUrl();
        data[8][0] = '=hyperlink("' + newurl + '","MAIN FOLDER")';
    }

    TSORT(data, 1, 1);
    template.getRange(3, 2, LR, LC).clear();
    template.getRange(1, 1, data.length, LC)
        .setValues(data)
        .setFontFamily("Roboto");

    template.getRange(3, 2, data.length - 2, LC - 1).activate()
        .sort([{ column: template.getActiveRange().getColumn() + 1, ascending: true }, { column: template.getActiveRange().getColumn() + 4, ascending: true }]);


    {/*New surveys for new year */
        var jsonData2 = KOBOFORM(key["APITOKEN2"]);


        for (year = parseInt(key["AUTOYEAR"]); year < 2030; year++) {
            if (year == 2017) { continue; }
            for (i = 0; i < data.length; i++) {
                if (data[i][data[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] == year) {
                    var ccc = 0;
                    for (ii = 0; ii < jsonData2.length; ii++) {
                        if (jsonData2[ii]["title"].indexOf(year) > -1) {
                            ccc++;
                        }
                    }
                    if (ccc != 3) {/*2 Beneficiary surveys and 1 partnersurvey*/
                        /*GENERAL*/
                        SCHEDULE("NEWBSURVEY", year);
                    }
                    break;
                }
            }
        }
    }


}
/**

Function extractMetaFiles

extractMetaFiles(meta)

The function extracts and lists all the meta files uploaded to the surveys (beneficiary and partner surveys).

Arguments

  • meta (String) – the name of the sheet to get (META 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
function extractMetaFiles() {
    /*meta {Object}  - get the sheet "META */
    var meta = ss.getSheetByName("META");
    var url = 'https://kobocat.unhcr.org/api/v1/metadata';
    var option = {
        "method": "get",
        "headers": {
            "Authorization": "Token " + key["APITOKEN2"] //ScriptProperties.getProperty('token'),
        }
    };

    try { var json = UrlFetchApp.fetch(url, option); } catch (e) { var error = e; Logger.log(error.message); }
    
    var jsonData = JSON.parse(json.getContentText());
    Logger.log(jsonData);
    var LC = meta.getLastColumn();
    var LR = meta.getLastRow();

    meta.getRange(2, 1, LR, LC).clear();
     /*array of headers from META sheet*/
    var data = meta.getRange(1, 1, jsonData.length + 1, LC).getValues();
    var y = 1;

    for (i = 0; i < jsonData.length; i++) {
        if (jsonData[i]["data_value"] == "") {
            continue;
        }
        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++;
    }
    /*Function calls from GENERAL Component*/
    TSORT(data, 1, 1);
    
    meta.getRange(1, 1, jsonData.length + 1, LC).setValues(data);
    meta.getRange(1, 1, jsonData.length + 1, LC).setFontFamily("arial");
}

Function openSidebar

openSidebar()

The function opens a sidebar

1
2
3
function openSidebar() {
    showSidebar();
}

Function showSidebar

showSidebar()

The function opens a sidebar on the page

1
2
3
4
5
6
function showSidebar() {
    var html = HtmlService.createHtmlOutputFromFile('PAGE')
        .setTitle('MANUAL TASK MENU');
    SpreadsheetApp.getUi() 
        .showSidebar(html);
}