KOBOUP Component

Function clearKoboSheet

clearKoboSheet()

The function clears content the KOBOUP sheet

1
2
3
function clearKoboSheet() {
    ClearContent("KOBOUP")
}

Function clearDupDetectorSheet

clearDupDetectorSheet()

The function clears content the DUP DETECTOR sheet

1
2
3
function clearDupDetectorSheet() {
    ClearContent("DUP DETECTOR")
}

Function queryDataKoboupSheet

queryDataKoboupSheet(pkey)

The function queries the data [KOBOUP sheet]

Arguments

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

1
2
3
4
5
function queryDataKoboupSheet(pkey) {
    key = pkey;
    /*Calls the function from KOBOUP component*/
    queryDataSheet("KOBOUP")
}

Function queryDataDupSheet

queryDataDupSheet(pkey)

The function queries the data [DUP DETECTOR sheet]

Arguments

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

1
2
3
4
5
function queryDataDupSheet(pkey) {
    key = pkey;
    /*Calls the function from KOBOUP component*/
    queryDataSheet("DUP DETECTOR")
}

Function queryDataSheet

queryDataSheet(sname)

The function queries data from specified sheet

Arguments

  • sname (string) – name of the 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
function queryDataSheet(sname) {
    /*dsheet {Object}  - get the sheet "sname" */
    var dsheet = ss.getSheetByName(sname);
    var API = key["APITOKEN2"];
    /*year {string} - current yeat*/
    var year = dsheet.getRange(1, 2).getValue();
    /*uis {string} - current user*/
    var uid = dsheet.getRange(1, 4).getValue();

    /*bsurvey {Object}  - get the BSURVEY sheet */
    var bsurvey = ss.getSheetByName("BSURVEY");
    /*databs {array} - data array from BSURVEY sheet*/
    databs = bsurvey.getDataRange().getValues();

    /*Loop though all the BSURVEY data*/
    for (var bs = 0; bs < databs.length; bs++) {
        /*If there is a Beneficiary Survey and current year*/
        if (databs[bs][databs[0].indexOf("YEAR")] == year && databs[bs][databs[0].indexOf("title")].indexOf("CUSTOMISED - " + key["SECTOR"] + " Beneficiary Survey") > -1 && databs[bs][databs[0].indexOf("title")].indexOf("DEMO") == -1) {
            /*formid {integer} - get the formid*/
            var formid = databs[bs][databs[0].indexOf("formid")];
        }
    }

    /*generates url of the form*/
    var url = 'https://kobocat.unhcr.org/api/v1/data/' + formid

    if (sname == "KOBOUP" && uid != "") {
        /*filter by user*/
        var querystring = '{"UID":"' + uid + '"}'
        var query2 = encodeURIComponent(querystring);
        var url = url + '?query=' + query2;
    }
    var option = {
        "method": "get",
        "headers": {
            "Authorization": "Token " + API, }
    };

    try { var json = UrlFetchApp.fetch(url, option); } catch (e) { var error = e; Logger.log(error.message); }
    var jsonData = JSON.parse(json.getContentText());
    
    var LC = dsheet.getLastColumn();
    var LR = dsheet.getLastRow();
    var header = dsheet.getRange(2, 2, 1, LC - 1).getValues();
    var y = 0;
    var newdata = [];
    /**Linking JSON data [BSURVEY sheet] with KOBOUP sheet by using headers*/
    for (i = 0; i < jsonData.length; i++) {
        
        newdata[y] = new Array();
        for (ii = 0; ii < LC; ii++) {
            if (jsonData[i][header[0][ii]] != null) {
                newdata[y][ii] = jsonData[i][header[0][ii]];
            } else {
                newdata[y][ii] = "";
            }
        }

        y++;
    }

    if (y > 0) { dsheet.getRange(dsheet.getLastRow() + 1, 2, newdata.length, newdata[0].length).setValues(newdata); }

}

Function createXML

createXML(pkey)

The function creates the XML file, zip the file, download the file, and shows the link to bulk upload page of KoboToolbox.

Arguments

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

  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
function createXML(pkey) {
    key = pkey;
    /*dsheet {Object}  - get the KOBOUP sheet" */
    var dsheet = ss.getSheetByName("KOBOUP");
    var year = dsheet.getRange(1, 2).getValue();

    if (year < 2019){ Browser.msgBox("Use this tab only for 2019 data or after!"); return ;}

    var start = new Date().getTime();
    /**/
    var triggers = ScriptApp.getProjectTriggers();

    for (var i = 0; i < triggers.length; i++) {
        if (triggers[i].getHandlerFunction() == "createXML") { ScriptApp.deleteTrigger(triggers[i]); break; }
    }

    /*get API parametor*/
    var API = key["APITOKEN2"];
    /*bsurvey {Object} - get the BSURVEY sheet*/
    var bsurvey = ss.getSheetByName("BSURVEY");
    databs = bsurvey.getDataRange().getValues();
    for (var bs = 0; bs < databs.length; bs++) {
        if (databs[bs][databs[0].indexOf("YEAR")] == year && databs[bs][databs[0].indexOf("title")].indexOf("CUSTOMISED - " + key["SECTOR"] + " Beneficiary Survey") > -1 && databs[bs][databs[0].indexOf("title")].indexOf("DEMO") == -1) {
            var formid = databs[bs][databs[0].indexOf("formid")];
        }
    }

    /*kobodata {Object} - get Kobo id string*/
    var kobodata = KOBOFORM(API, formid);
    fid = kobodata["id_string"];

  
    var text;
    var text2;
    var data;
    var text;

    /*text2 {String} - get the XML template from KOBOUP sheet*/
    text2 = dsheet.getRange(1, 5).getValue();

    /*data {array} - get all data from KOBOUP shhet*/
    data = dsheet.getRange(1, 1, dsheet.getLastRow(), dsheet.getLastColumn()).getValues();
    /*c1 {number}  - index of current user in data [KOBOUP sheet] */
    var c1 = data[1].indexOf("_uuid");
    /*c1 {number}  - index of _id field in data [KOBOUP sheet] */
    var c2 = data[1].indexOf("_id");
  
    /*Loop for creating XML files*/
    for (k = 2; k < data.length; k++) {
        text = text2;
        if (data[k][0] != "") { continue; }
        for (i = 0; i < data[0].length; i++) {
            var y = ">?" + (i + 1) + "<";
            var r = ">" + data[k][i + 1] + "<";
            text = text.replace(y, r);
            
        }

      
        r = '<' + fid;
        text = text.replace('<id1', r);
        r = '"' + fid;
        text = text.replace('"id2', r);
        r = '/' + fid;
        text = text.replace('/id3', r);

        /* randomize UUID */
        var newuuid = data[k][c1];
        for (r = 0; r < 10; r++) {
            newuuid = newuuid.replace(String(r), Math.floor(Math.random() * 9));
        }

        r = 'uuid:' + newuuid;//data[k][c1];
        text = text.replace('uuid:uuid', newuuid);//data[k][c1]);
        text = text.replace('uuid0', r);
        //Special Characters Escape
        text = text.replace(/&/g, "&amp;");
        /*xml upload*/
        var boundary = "labnol";

        var xmlBlob = Utilities.newBlob(text);

        var requestBody = Utilities.newBlob(
            "--" + boundary + "\r\n" +
            "Content-Disposition: form-data; name=\"xml_submission_file\"; filename=\"" + "submission.xml" + "\"\r\n" +
            "Content-Type: " + "application/xml" + "\r\n\r\n").getBytes()
            .concat(xmlBlob.getBytes())
            .concat(Utilities.newBlob("\r\n--" + boundary + "--\r\n").getBytes());

        var options = {
            "method": "post",
            "contentType": "multipart/form-data; boundary=" + boundary,
            "payload": requestBody,
            "headers": {
                "Authorization": "Token " + API
            }
        };

        try {
            var response = UrlFetchApp.fetch("https://kobocat.unhcr.org/api/v1/submissions", options);
            dsheet.getRange(k + 1, 1).setValue("OK");
        }
        catch (e) { dsheet.getRange(k + 1, 1).setValue("Failed"); }

        SpreadsheetApp.flush();

        var now = new Date().getTime();
        var dif = (now - start) / 1000;
        Logger.log(dif);
        if (dif > 350) {
            ScriptApp.newTrigger('createXML')
                .timeBased()
                .everyMinutes(1)
                .create();
            break;
        }

    }
}

Function deleteDataKoboupSheet

deleteDataKoboupSheet(pkey)

The function deletes the data based on the formid and dataid provided from KOBOUP sheet

Arguments

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

 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 deleteDataKoboupSheet(pkey) {
    key = pkey;
    /**dsheet {Object}  - get id of  KOBOUP sheet" */
    var dsheet = ss.getSheetByName("KOBOUP");
    var year = dsheet.getRange(1, 2).getValue();

    if(year<2019){Browser.msgBox("Use this tab only for 2019 data or after!");return;}

    var start = new Date().getTime();
    var triggers = ScriptApp.getProjectTriggers();

    for (var i = 0; i < triggers.length; i++) {
        if (triggers[i].getHandlerFunction() == "deleteDataKoboupSheet") { ScriptApp.deleteTrigger(triggers[i]); break; }
    }
    /*Double check*/
    var dsheet = ss.getSheetByName("KOBOUP");
    var API = key["APITOKEN2"];

    /*bsurvey {Object} - get  the BSURVEY sheet*/
    var bsurvey = ss.getSheetByName("BSURVEY");
    databs = bsurvey.getDataRange().getValues();
    for (var bs = 0; bs < databs.length; bs++) {
        if (databs[bs][databs[0].indexOf("YEAR")] == year && databs[bs][databs[0].indexOf("title")].indexOf("CUSTOMISED - " + key["SECTOR"] + " Beneficiary Survey") > -1 && databs[bs][databs[0].indexOf("title")].indexOf("DEMO") == -1) {
            /*formid {sting} -  id of the form*/
            var formid = databs[bs][databs[0].indexOf("formid")];
        }
    }


    /*kobodata {Object} - get Kobo id string*/
    var kobodata = KOBOFORM(API, formid);
    fid = kobodata["id_string"];

    var data = dsheet.getRange(1, 1, dsheet.getLastRow(), dsheet.getLastColumn()).getValues();
    var c1 = data[1].indexOf("_id");

    var options = {
        "method": "DELETE",
        'headers': {
            'Authorization': 'Token ' + API
        }
    };

    for (k = 2; k < data.length; k++) {
        if (data[k][0] != "OK" && data[k][0] != "DUP") { continue; }
        var DATAID = data[k][c1];
        var list_of_forms = UrlFetchApp.fetch("https://kobocat.unhcr.org/api/v1/data/" + formid + "/" + DATAID, options);
        dsheet.getRange(k + 1, 1).setValue("DELETED");
        SpreadsheetApp.flush();
        var now = new Date().getTime();
        var dif = (now - start) / 1000;
        Logger.log(dif);
        if (dif > 350) {
            ScriptApp.newTrigger('deleteDataKoboupSheet')
                .timeBased()
                .everyMinutes(1)
                .create();
            break;
        }
    }

}

Function findDuplication

findDuplication()

The function finds the duplication

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
function findDuplication() {
    /*sheet {Object}  - get the DUP DETECTOR sheet" */
    var sheet = ss.getSheetByName("DUP DETECTOR");
    var range = sheet.getRange(3, 1, sheet.getLastRow() - 2, sheet.getLastColumn());
    range.sort({ column: 2, ascending: false });
    var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
    var data = range.getValues();
    /*c1 {number}  - index of start column in data [DUP DETECTOR Sheet] */
    var c1 = data[0].indexOf("start");
    /*c2 {number}  - index of deviceid column in data [DUP DETECTOR Sheet] */
    var c2 = data[0].indexOf("deviceid");
    var count = 0;

    for (var i = 1; i < data.length - 1; i++) {
        data[i][0] = "";
        if (data[i][c1] == data[i + 1][c1] && data[i][c2] == data[i + 1][c2]) {
            data[i][0] = "DUP";
            count++;
        }
    }
    range.setValues(data);
    Browser.msgBox("The # of duplicated submissions = " + count);

Function deleteDataDupDetectorSheet

deleteDataDupDetectorSheet(pkey)

The function deletes the data based on the formid and dataid provided from DUP DETECTOR sheet

Arguments

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
function deleteDataDupDetectorSheet(pkey) {
    key = pkey;
     /*sheet {Object}  - get the DUP DETECTOR sheet" */
    var dsheet = ss.getSheetByName("DUP DETECTOR");
    var year = dsheet.getRange(1, 2).getValue();

    var start = new Date().getTime();
    var triggers = ScriptApp.getProjectTriggers();

    for (var i = 0; i < triggers.length; i++) {
        if (triggers[i].getHandlerFunction() == "deleteDataDupDetectorSheet") { ScriptApp.deleteTrigger(triggers[i]); break; }
    }

    /*sheet {Object} - get the DUP DETECTOR sheet*/
    var dsheet = ss.getSheetByName("DUP DETECTOR");
    var API = key["APITOKEN2"];

    /*bsurvey {Object} - get  the BSURVEY sheet*/
    var bsurvey = ss.getSheetByName("BSURVEY");
    databs = bsurvey.getDataRange().getValues();
    for (var bs = 0; bs < databs.length; bs++) {
        if (databs[bs][databs[0].indexOf("YEAR")] == year && databs[bs][databs[0].indexOf("title")].indexOf("CUSTOMISED - " + key["SECTOR"] + " Beneficiary Survey") > -1 && databs[bs][databs[0].indexOf("title")].indexOf("DEMO") == -1) {
            /*formid {sting} -  id of the form*/
            var formid = databs[bs][databs[0].indexOf("formid")];
        }
    }

    /*kobodata {Object} - get Kobo id string*/
    var kobodata = KOBOFORM(API, formid);
    fid = kobodata["id_string"];

    var data = dsheet.getRange(1, 1, dsheet.getLastRow(), dsheet.getLastColumn()).getValues();
    var c1 = data[1].indexOf("_id");

    var options = {
        "method": "DELETE",
        'headers': {
            'Authorization': 'Token ' + API
        }
    };

    for (k = 2; k < data.length; k++) {
        if (data[k][0] != "OK" && data[k][0] != "DUP") { continue; }
        var DATAID = data[k][c1];
        var list_of_forms = UrlFetchApp.fetch("https://kobocat.unhcr.org/api/v1/data/" + formid + "/" + DATAID, options);
        dsheet.getRange(k + 1, 1).setValue("DELETED");
        SpreadsheetApp.flush();
        var now = new Date().getTime();
        var dif = (now - start) / 1000;
        Logger.log(dif);
        if (dif > 350) {
            ScriptApp.newTrigger('deleteDataDupDetectorSheet')
                .timeBased()
                .everyMinutes(1)
                .create();
            break;
        }
    }
}

Function clearKoboupSheet2018

clearKoboupSheet2018()

The function clears content the KOBOUP2018 sheet

1
2
3
 function clearKoboupSheet2018() {
    ClearContent("KOBOUP2018")
}

Function queryDataKoboupSheet2018

queryDataKoboupSheet2018(pkey)

The function queries the data [KOBOUP2018 sheet]

Arguments

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

1
2
3
4
 function queryDataKoboupSheet2018(pkey) {
    key = pkey;
    queryDataSheet2018("KOBOUP2018")
}

Function queryDataSheet2018

queryDataSheet2018(sname)

The function queries data from specified sheet

Arguments

  • sname (string) – name of the 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
 function queryDataSheet2018(sname) {

    var dsheet = ss.getSheetByName(sname);
    var API = key["APITOKEN2"];
    var year = dsheet.getRange(1, 2).getValue();
    var uid = dsheet.getRange(1, 4).getValue();

    var bsurvey = ss.getSheetByName("BSURVEY");
    databs = bsurvey.getDataRange().getValues();
    for (var bs = 0; bs < databs.length; bs++) {
        if (databs[bs][databs[0].indexOf("YEAR")] == year && databs[bs][databs[0].indexOf("title")].indexOf("CUSTOMISED - " + key["SECTOR"] + " Beneficiary Survey") > -1 && databs[bs][databs[0].indexOf("title")].indexOf("DEMO") == -1) {
            var formid = databs[bs][databs[0].indexOf("formid")];
        }
    }

     var url = 'https://kobocat.unhcr.org/api/v1/data/' + formid
    if (sname == "KOBOUP2018" && uid != "") {
        var querystring = '{"UID":"' + uid + '"}'
        var query2 = encodeURIComponent(querystring);
        var url = url + '?query=' + query2;
    }
    var option = {
        "method": "get",
        //"payload": query2,
        "headers": {
            "Authorization": "Token " + API//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());

     var LC = dsheet.getLastColumn();
    var LR = dsheet.getLastRow();
    var header = dsheet.getRange(2, 2, 1, LC - 1).getValues();
    var y = 0;
    var newdata = [];

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

         y++;
    }

     if (y > 0) { dsheet.getRange(dsheet.getLastRow() + 1, 2, newdata.length, newdata[0].length).setValues(newdata); }

 }

Function createXML_2018

createXML_2018(pkey)

The function creates the XML file, zip the file, download the file, and shows the link to bulk upload page of KoboToolbox.

Arguments

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

  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
 function createXML_2018(pkey) {
    key = pkey;
    var dsheet = ss.getSheetByName("KOBOUP2018");
    var year = dsheet.getRange(1, 2).getValue();

    if(year>2018){Browser.msgBox("Use this tab only for 2018 data or before!");return;}

    var start = new Date().getTime();
    var triggers = ScriptApp.getProjectTriggers();

     for (var i = 0; i < triggers.length; i++) {
        if (triggers[i].getHandlerFunction() == "createXML_2018") { ScriptApp.deleteTrigger(triggers[i]); break; }
    }

    
    var API = key["APITOKEN2"];
   
    var bsurvey = ss.getSheetByName("BSURVEY");
    databs = bsurvey.getDataRange().getValues();
    for (var bs = 0; bs < databs.length; bs++) {
        if (databs[bs][databs[0].indexOf("YEAR")] == year && databs[bs][databs[0].indexOf("title")].indexOf("CUSTOMISED - " + key["SECTOR"] + " Beneficiary Survey") > -1 && databs[bs][databs[0].indexOf("title")].indexOf("DEMO") == -1) {
            var formid = databs[bs][databs[0].indexOf("formid")];
        }
    }

     
    var kobodata = KOBOFORM(API, formid);
    fid = kobodata["id_string"];

    /*Variables for operation*/
    var text;
    var text2;
    var data;
    var text;

    /*get the XML template*/
    text2 = dsheet.getRange(1, 5).getValue();

    /*get ALLDATA*/
    data = dsheet.getRange(1, 1, dsheet.getLastRow(), dsheet.getLastColumn()).getValues();
    var c1 = data[1].indexOf("_uuid");
    var c2 = data[1].indexOf("_id");
    

   
    for (k = 2; k < data.length; k++) {
        text = text2;
        if (data[k][0] != "") { continue; }
        for (i = 0; i < data[0].length; i++) {
            var y = ">?" + (i + 1) + "<";
            var r = ">" + data[k][i + 1] + "<";           
            text = text.replace(y, r);           
        }

        
        r = '<' + fid;
        text = text.replace('<id1', r);
        r = '"' + fid;
        text = text.replace('"id2', r);
        r = '/' + fid;
        text = text.replace('/id3', r);

         //RANDOMIZE UUID
        var newuuid = data[k][c1];
        for (r = 0; r < 10; r++) {
            newuuid = newuuid.replace(String(r), Math.floor(Math.random() * 9));
        }

         r = 'uuid:' + newuuid;//data[k][c1];
        text = text.replace('uuid:uuid', newuuid);//data[k][c1]);
        text = text.replace('uuid0', r);
        
        //Special Characters Escape
        text = text.replace(/&/g, "&amp;");
        
        //XML UPLOAD

         var boundary = "labnol";

         var xmlBlob = Utilities.newBlob(text);

         var requestBody = Utilities.newBlob(
            "--" + boundary + "\r\n" +
            "Content-Disposition: form-data; name=\"xml_submission_file\"; filename=\"" + "submission.xml" + "\"\r\n" +
            "Content-Type: " + "application/xml" + "\r\n\r\n").getBytes()
            .concat(xmlBlob.getBytes())
            .concat(Utilities.newBlob("\r\n--" + boundary + "--\r\n").getBytes());

         var options = {
            "method": "post",
            "contentType": "multipart/form-data; boundary=" + boundary,
            "payload": requestBody,
            //"muteHttpExceptions": true,
            "headers": {
                "Authorization": "Token " + API
            }
        };

         try {
            var response = UrlFetchApp.fetch("https://kobocat.unhcr.org/api/v1/submissions", options);
            dsheet.getRange(k + 1, 1).setValue("OK");
        }
        catch (e) { dsheet.getRange(k + 1, 1).setValue("Failed"); }

         SpreadsheetApp.flush();

         var now = new Date().getTime();
        var dif = (now - start) / 1000;
        Logger.log(dif);
        if (dif > 350) {
            ScriptApp.newTrigger('createXML_2018')
                .timeBased()
                .everyMinutes(1)
                .create();
            break;
        }

     }
}

Function deleteDataDupDetectorSheet2018

deleteDataDupDetectorSheet2018(pkey)

The function deletes the data based on the formid and dataid provided from KOBOUP sheet

Arguments

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

 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
 function deleteDataDupDetectorSheet2018(pkey) {
    key = pkey;
    var dsheet = ss.getSheetByName("KOBOUP2018");
    var year = dsheet.getRange(1, 2).getValue();
    
     if(year>2018){Browser.msgBox("Use this tab only for 2018 data or before!");return;}

    var start = new Date().getTime();
    var triggers = ScriptApp.getProjectTriggers();

     for (var i = 0; i < triggers.length; i++) {
        if (triggers[i].getHandlerFunction() == "deleteDataDupDetectorSheet2018") { ScriptApp.deleteTrigger(triggers[i]); break; }
    }

     

    var dsheet = ss.getSheetByName("KOBOUP2018");
    var API = key["APITOKEN2"];

     
    var bsurvey = ss.getSheetByName("BSURVEY");
    databs = bsurvey.getDataRange().getValues();
    for (var bs = 0; bs < databs.length; bs++) {
        if (databs[bs][databs[0].indexOf("YEAR")] == year && databs[bs][databs[0].indexOf("title")].indexOf("CUSTOMISED - " + key["SECTOR"] + " Beneficiary Survey") > -1 && databs[bs][databs[0].indexOf("title")].indexOf("DEMO") == -1) {
            var formid = databs[bs][databs[0].indexOf("formid")];
        }
    }


     //GET KOBO ID STRING
    var kobodata = KOBOFORM(API, formid);
    fid = kobodata["id_string"];

     var data = dsheet.getRange(1, 1, dsheet.getLastRow(), dsheet.getLastColumn()).getValues();
    var c1 = data[1].indexOf("_id");

     var options = {
        "method": "DELETE",
        'headers': {
            'Authorization': 'Token ' + API
        }
    };

     for (k = 2; k < data.length; k++) {
        if (data[k][0] != "OK" && data[k][0] != "DUP") { continue; }
        var DATAID = data[k][c1];
        // try {
        var list_of_forms = UrlFetchApp.fetch("https://kobocat.unhcr.org/api/v1/data/" + formid + "/" + DATAID, options);
        dsheet.getRange(k + 1, 1).setValue("DELETED");
        SpreadsheetApp.flush();
        // }catch(e){}

         var now = new Date().getTime();
        var dif = (now - start) / 1000;
        Logger.log(dif);
        if (dif > 350) {
            ScriptApp.newTrigger('deleteDataDupDetectorSheet2018')
                .timeBased()
                .everyMinutes(1)
                .create();
            break;
        }
    }

 }