CUSTOMISATION Component

Function createCustomiseCSV

createCustomiseCSV(year, formid, id, uuid, usernamebefore, newusername, pkey)

The function coordinates the creation of new customised csvs (upload/updates)

Arguments

  • year (string) – current year

  • formid (string) – id of the template

  • id (string) – id of beneficiary data

  • uuid (string) – current user name

  • usernamebefore (usernamebefore) – previous name of file [Kobo Form cutomises the content is through the username, and with the username it will find the right csv file to pull data from]

  • newusername (newusername) – new name of the file

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

Returns

keyinputs – List of key parameters.

This is the image caption

The structure of Control Panel

  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
function createCustomiseCSV(year, formid, id, uuid, usernamebefore, newusername, pkey) {
    if (pkey != null) { key = pkey };
    if (key["CUSTOMISE" + id] == "OK") { return 3; }
    /*The reason is checking the current username is to prevent multiple change before recustomisation*/
    if (newusername != key["UN" + id]) { return 3; }
    /*To avoid the error for the first time*/
    if (usernamebefore == "" || usernamebefore == null) { usernamebefore = newusername; }//for the first time error;

    /*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);
    /*BSURVEYFOLDER {string} - id of BSURVEY folder for specific year*/
    BSURVEYFOLDER = createFolder(YearFolder, "BSURVEY" + year);
    /*Survey CSV*/
    { 
        /*Checking for CSV file existence */
        /*idSS {string} - if of the file [General Component]*/
        var idSS = getFile(BSURVEYFOLDER, "surveys");

        /* IF file doesn't exist */
        if (idSS == null) {
            /*Folder {string} -  the id of the folder [gets the folder with given ID]*/
            var folder = DriveApp.getFolderById(BSURVEYFOLDER);
            /*scvfile {string} - the id of created file [GENERAL Component]*/
            var scvfile = createFile(key["OTHERFOLDERID"], "CSVFile");
            /*file {string}  - the file with the given ID*/
            var file = DriveApp.getFileById(scvfile);
            /*copying the file to specific folder [folder]*/
            file.makeCopy("surveys", folder);

             /*files {string} - collection of all files in the Google Drive that have the given name*/
            var files = folder.getFiles();
            while (files.hasNext()) {
                var file2 = files.next();
                if (file2.getName() == "surveys") {
                    idSS = file2.getId();
                }
            }
        }

        /* Function construct/add surveys with new template */
        createCsvFile(idSS, formid, id, usernamebefore, newusername);

        /*function connects to KOBOTOOLBOX by using API and get JSON*/
        /*key["APITOKEN2"] - survey API TOKEN*/
        var jsonData = getKoboJsonData(key["APITOKEN2"]); 
        var r = 0,
            rr = 0,
            pr = 0;
        var realformid,
            demoformid,
            partnerformid;
            

        for (i = 0; i < jsonData.length; i++) {
            if (jsonData[i]["title"].indexOf(year) > -1 && jsonData[i]["title"].indexOf("TRAINING (DEMO)") == -1 && jsonData[i]["title"].indexOf("Beneficiary") > -1) {
                r = 1;
                /*realformid {object} - formid of Benefeciary survey*/
                realformid = jsonData[i]["formid"];
            }

            if (jsonData[i]["title"].indexOf(year) > -1 && jsonData[i]["title"].indexOf("TRAINING (DEMO)") > -1 && jsonData[i]["title"].indexOf("Beneficiary") > -1) {
                rr = 1;
                /*demoformid {string} - formid of training Benefeciary survey*/
                demoformid = jsonData[i]["formid"];
            }

            if (jsonData[i]["title"].indexOf(year) > -1 && jsonData[i]["title"].indexOf("Partner") > -1) {
                pr = 1;
                /*partnerformid {string} - formid of Partner Survey*/
                partnerformid = jsonData[i]["formid"];
            }
        }

        
        /*URL for csv file "surveys" for the beneficiary survey*/
        var csv = 'CSVURL' + id;
        /*if Beneficiary Survey*/
        if (r == 1) {
            try {
                /*Delete existing csv meta file in kobo*/
                deleteCsvMetaFileKobo(realformid, "surveys.csv");
            } catch (e) { }
            /*Add csv meta file in kobo*/
            createCsvMetaFile(idSS, realformid, "surveys.csv");
            /*newurl {string} - url of the file*/
            var newurl = DriveApp.getFileById(idSS).getUrl();
            keyinputs[csv] = newurl;
            key[csv] = newurl;
        }
        /*if Training Beneficiary Survey*/
        if (rr == 1) {
            try {
                 /*Delete existing csv meta file in kobo*/
                deleteCsvMetaFileKobo(demoformid, "surveys.csv");
            } catch (e) { }
             /*Add csv meta file in kobo*/
            createCsvMetaFile(idSS, demoformid, "surveys.csv");
             /*newurl {string} - url of the file*/
            var newurl = DriveApp.getFileById(idSS).getUrl();
            /*update the key parameters*/
            keyinputs[csv] = newurl;
            key[csv] = newurl;
        }
        /*If partner survey*/
        if (pr == 1) {
            try {
                 /*Delete existing csv meta file in kobo*/
                deleteCsvMetaFileKobo(partnerformid, "surveys.csv");
            } catch (e) { }
            /*Add csv meta file in kobo*/
            createCsvMetaFile(idSS, partnerformid, "surveys.csv");

            /*newurl {string} - url of the file*/
            var newurl = DriveApp.getFileById(idSS).getUrl();
            keyinputs[csv] = newurl;
            key[csv] = newurl;
        }

        updateLog("Surveys CSV updated :" + year);
    }
    /*Country CSV file*/
    { 
        var csvname = usernamebefore;
        if (newusername != "undefined" && newusername != null) {
            csvname = newusername;
        }

        /*get file by id in BSURVEY FOlder and delete for usernamebefore*/
        var oldfile = DriveApp.getFileById(createFile(BSURVEYFOLDER, usernamebefore));
        oldfile.setTrashed(true);
        /*get file by id in BSURVEY FOlder and delete for newusername*/
        oldfile = DriveApp.getFileById(createFile(BSURVEYFOLDER, newusername));
        oldfile.setTrashed(true);

        /*Folder {string} -  the id of the folder [gets the folder with given ID]*/
        var folder = DriveApp.getFolderById(BSURVEYFOLDER);
        /*mainfolderid {string} - the id of main folder [Livlehoods_Monitroing]*/
        var mainfolderid = createFolder(0, key["MAINFOLDER"]);
        /*scvfile {string] - the id of file in CSV folder*/
        var scvfile = createFile(key["OTHERFOLDERID"], "CSVFile"); //CARRIES MANUAL UPDATE SCRIPT
        var file = DriveApp.getFileById(scvfile);
        file.makeCopy(csvname, folder);

 
        var files = folder.getFiles();
        while (files.hasNext()) {
            var file2 = files.next();
            /*Looking for the file*/
            if (file2.getName() == csvname) {
                /*idSS2 {string} - the id of country file*/
                var idSS2 = file2.getId();
            }
        }


        /*Add CSV surveys with new template*/
        addCsvFileCountry(idSS2, formid, id, csvname);

        var jsonData = getKoboJsonData(key["APITOKEN2"]); 

        var r = 0,
            rr = 0,
            pr = 0;
        var realformid,
            demoformid,
            partnerformid;
        for (i = 0; i < jsonData.length; i++) {
            if (jsonData[i]["title"].indexOf(year) > -1 && jsonData[i]["title"].indexOf("TRAINING (DEMO)") == -1 && jsonData[i]["title"].indexOf("Beneficiary") > -1) {
                r = 1;
                realformid = jsonData[i]["formid"];
            }

            if (jsonData[i]["title"].indexOf(year) > -1 && jsonData[i]["title"].indexOf("TRAINING (DEMO)") > -1) {
                rr = 1;
                demoformid = jsonData[i]["formid"];
            }

            if (jsonData[i]["title"].indexOf(year) > -1 && jsonData[i]["title"].indexOf("Partner") > -1) {
                pr = 1;
                partnerformid = jsonData[i]["formid"];
            }
        }
        /*CSVURL2: URL for csv file "[name of country]" customising for that template for the beneficiary survey*/
        var csv2 = 'CSVURL2' + id;
        /*if Benefeciary survey*/
        if (r == 1) {
            csvname = usernamebefore;
            try {
                /*Delete existing csv meta file (country [username]) in kobo*/
                deleteCsvMetaFileKobo(realformid, usernamebefore + ".csv");
            } catch (e) { }
            try {
                /*Delete existing csv meta file (country [newusername]) in kobo*/
                deleteCsvMetaFileKobo(realformid, newusername + ".csv");
            } catch (e) { }
            if (newusername != "undefined") {
                csvname = newusername;
            }
            /*Add csv meta file for country[username].csv in kobo*/
            createCsvMetaFile(idSS2, realformid, csvname + ".csv");
            /*newurl2 {string} - url of the file*/
            var newurl2 = DriveApp.getFileById(idSS2).getUrl();
            keyinputs[csv2] = newurl2;
            key[csv2] = newurl2;
        }
        /*if training survey*/
        if (rr == 1) {
            csvname = usernamebefore;
            try {
                /*Delete existing csv meta file (country [usernamebefore]) in kobo*/
                deleteCsvMetaFileKobo(demoformid, usernamebefore + ".csv");
            } catch (e) { }
            try {
                /*Delete existing csv meta file (country [newusername]) in kobo*/
                deleteCsvMetaFileKobo(demoformid, newusername + ".csv");
            } catch (e) { }
            if (newusername != "undefined") {
                csvname = newusername;
            }
            /*Add csv meta file for country[username].csv in kobo*/
            createCsvMetaFile(idSS2, demoformid, csvname + ".csv");
            /*newurl2 {string} - url of the file*/
            var newurl2 = DriveApp.getFileById(idSS2).getUrl();
            keyinputs[csv2] = newurl2;
            key[csv2] = newurl2;
        }

        /*If Partner survey*/
        if (pr == 1) {
            csvname = usernamebefore;
            try {
                deleteCsvMetaFileKobo(partnerformid, usernamebefore + ".csv");
            } catch (e) { }
            try {
                deleteCsvMetaFileKobo(partnerformid, newusername + ".csv");
            } catch (e) { }
            if (newusername != "undefined") {
                csvname = newusername;
            }
            createCsvMetaFile(idSS2, partnerformid, csvname + ".csv");
            /*newurl2 {string} - url of the file*/
            var newurl2 = DriveApp.getFileById(idSS2).getUrl();
            keyinputs[csv2] = newurl2;
            key[csv2] = newurl2;
        }

        updateLog("Country CSV updated :" + year + " : " + csvname);
    }
    /*Changing template and user status*/
    { 
        /*UNO: The previous username. Only different when username is changed manually.*/
        var kko = 'UNO' + String(id);
        /*UN: The current username*/
        var kk = 'UN' + String(id);
        keyinputs[kko] = key[kk];
        key[kko] = key[kk];
        /*update the key*/
        keyinputs["CUSTOMISE" + id] = "OK";
        key["CUSTOMISE" + id] = "OK";
        /*Old and New Usernames will match now.*/
    }

    /*If Email notificatin is On*/
    if (key["EMAIL"] == "ON") {
        /*Function contains the basic template for the emails. Takes as parameters the data to put inside and send it*/
        generateEmailNotice(id,
            "Beneficiary / Partner surveys customised.",
            "Beneficiary / Partner surveys has been customised according to the template. " +
            "<br><br>This is just an internal notification (user may or may not have access to the surveys)." +
            "<br><br>Link for country csv: " + newurl2 +
            "<br><br>Link for general csv: " + newurl
            , "0",
            "0");
    }
    /*Function Extracts and lists all the meta files uploaded to the surveys [CONTROL Component]*/
    extractMetaFiles();
    /*Function checks the updates on customisation [Updatecheck Component]*/
    updateOnCustomisation(id);
    if (pkey != null) { return (keyinputs); }
    return 1;
}

Function createCsvMetaFile

createCsvMetaFile(fielid, formid, csvname)

The function creates the csv meta file

Arguments

  • fielid (string) – id of the file

  • formid (string) – id of the template

  • csvname (string) – name of csv file

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
function createCsvMetaFile(fileid, formid, csvname) {
    /*ssz {object} - spreadsheet object with the given id [Opens the spreadsheet with the given ID]*/
    var ssz = SpreadsheetApp.openById(fileid);
    /*tempsheet {type} -the sheet with the given name*/
    var tempsheet = ssz.getSheetByName("Sheet1");
    var csv = "";
    /*text {array} - values of the range*/
    var text = tempsheet.getRange(1, 1, tempsheet.getLastRow(), tempsheet.getLastColumn()).getValues();

    if (text.length > 1) {
        for (var row = 0; row < text.length; row++) {
            for (var col = 0; col < text[row].length; col++) {
                 text[row][col] = "\"" + text[row][col] + "\"";
               }
        }
    }

    text.forEach(function (e) {
        csv += e.join(",") + "\n";
    });

    var r = addCsvMetaFileKobo(csv, formid, csvname);
}

Function addCsvMetaFileKobo

addCsvMetaFileKobo(csvData, formid, csvname)

The function adds the created CSV meta file in Kobotoolbox

Arguments

  • csvData (array) – data in csv [the bytes of the blob]

  • formid (string) – id of the template

  • csvname (string) – name of csv file

Returns

response – the HTTP response 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
function addCsvMetaFileKobo(csvData, formid, csvname) {
    var boundary = "labnol";
    /*csvBlob {Blob} - the newly created Blob */
    var csvBlob = Utilities.newBlob(csvData);

    var attributes = "media";
    var dv = "text/csv";
    var df = "text/csv";

    var requestBody = Utilities.newBlob(
        "--" + boundary + "\r\n" +
        "Content-Disposition: form-data; name=\"data_type\"\r\n\r\n" + attributes + "\r\n" + "--" + boundary + "\r\n" +
        "Content-Disposition: form-data; name=\"xform\"\r\n\r\n" + formid + "\r\n" + "--" + boundary + "\r\n" +
        "Content-Disposition: form-data; name=\"data_value\"\r\n\r\n" + dv + "\r\n" + "--" + boundary + "\r\n" +
        "Content-Disposition: form-data; name=\"data_file\"; filename=\"" + csvname + "\"\r\n" +
        "Content-Type: " + df + "\r\n\r\n").getBytes()
        .concat(csvBlob.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 " + key["APITOKEN2"]
        }
    };
    
    try { 
        /*reponse - the HTTP response data */
        var response = UrlFetchApp.fetch("https://kobocat.unhcr.org/api/v1/metadata.json", options);
         } catch (e) { var error = e; Logger.log(error.message); }
    return response;
}

Function deleteCsvMetaFileKobo

deleteCsvMetaFileKobo(formid, csvname)

The function deletes existing the csv meta file in kobo

Arguments

  • formid (string) – id of the template

  • csvname (string) – name of csv 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
function deleteCsvMetaFileKobo(formid, csvname) {

    var url = 'https://kobocat.unhcr.org/api/v1/forms/' + formid 
    var options = {
        "method": "get",
        "headers": {
            "Authorization": "Token " + key["APITOKEN2"] 
        }
    }
    try { 
         /*json - the HTTP response data */
        var json = UrlFetchApp.fetch(url, options);
         } catch (e) { var error = e; Logger.log(error.message); }
    /*meta {string} - the content of the HTTP response [Gets the content of an HTTP response encoded as a string]*/     
    var meta = JSON.parse(json.getContentText());
   
    for (i = 0; i < meta["metadata"].length; i++) {
        if (meta["metadata"][i]["data_value"] == csvname) {
            var metaid = meta["metadata"][i]["id"];
        }
    }
    try { 
        /*list_of_forms - the HTTP response data */
        var list_of_forms = UrlFetchApp.fetch("https://kobocat.unhcr.org/api/v1/metadata/" + metaid, getUrlFetchOptionsdelete());
         } catch (e) { var error = e; Logger.log(error.message); }
}

Function updateCsvMetaFile

updateCsvMetaFile()

The function helps manually update csv

  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
function updateCsvMetaFile() {
    /*nowsheet {object} - active sheet in active spreadsheet*/
    var nowsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var n = nowsheet.getName();
    /*if sheet name is not equal TEMPLATE*/
    if (n != "TEMPLATE") {
        Browser.msgBox("YOU MUST SELECT A TEMPLATE ON 'TEMPLATE' SHEET");
        return;
    }
    /*r {type} - row of active sheet*/
    var r = nowsheet.getActiveCell().getRow() - 1;
    /*data {array} -  data from the CONTROL sheet*/
    var data = getData("CONTROL");
    /*username {number}  - index of current user in data [Control sheet] */
    var username = data[r][data[0].indexOf("Username")];
    /*fyear {number}  - index of year in data [CONTROL sheet] */
    var fyear = data[r][data[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")];
    /*FID {number}  - index of formid in data [CONTROL sheet] */
    var FID = data[r][data[0].indexOf("formid")];
    /*ID{number}  - index of dataid in data [CONTROL sheet] */
    var ID = data[r][data[0].indexOf("_id")];

    if (r != 1) {
        /*YearFolder {string} - the id of main folder >Allfiles*/
        YearFolder = createFolder(mainfolderid, "ALLFILES" + fyear);
        /*YearFolder {string} - the id of BSURVEY folder*/
        BSURVEYFOLDER = createFolder(YearFolder, "BSURVEY" + fyear);
    }

    /*fileid {string} id of the fiel _surveys in BSurvey +year folder */
    var fileid1 = getFile(BSURVEYFOLDER, "surveys");
    /*fileid {string} id of the file country (username) in BSurvey +year folder */
    var fileid = getFile(BSURVEYFOLDER, username);
    if (fileid == null) {
        Browser.msgBox("CANNOT FIND THE FILE");
        return;
    }

    var result = Browser.msgBox("ARE YOU SURE TO UPDATE " + username + " (" + FID + "/" + ID + ")" + " for year " + fyear + "??", Browser.Buttons.YES_NO);
    if (result == "no") {
        return;
    }

    /*jsondata {object} - data from Kobotoolbox [connect to kobo by using API] */
    var jsonData = KOBOFORM(key["APITOKEN2"]); 

    var r = 0,
        rr = 0;
    var realformid,
        demoformid;
    for (i = 0; i < jsonData.length; i++) {
        if (jsonData[i]["title"].indexOf(fyear) > -1 && jsonData[i]["title"].indexOf("TRAINING (DEMO)") == -1) {
            r = 1;
            /*realformid {string} - id of Benefeciary survey */
            realformid = jsonData[i]["formid"];
        }

        if (jsonData[i]["title"].indexOf(fyear) > -1 && jsonData[i]["title"].indexOf("TRAINING (DEMO)") > -1) {
            rr = 1;
            /*demoformid {string} - id of Training survey */
            demoformid = jsonData[i]["formid"];
        }
    }

    /*CSVURL: URL for csv file "surveys" for the beneficiary survey*/
    var csv = 'CSVURL' + ID;
    /*If Beneficiary survey*/
    if (r == 1) {
        try {
            /*delete existing csv meta file (benefeciary) in kobo*/
            deleteCsvMetaFileKobo(realformid, "surveys.csv");
        } catch (e) { }
        /*Add csv meta file for beneficairy survey in kobo */
        createCsvMetaFile(fileid1, realformid, "surveys.csv");
        updateLog("Surveys CSV file updated for " + fyear);
        /*newurl {string} - url of the file*/
        var newurl = DriveApp.getFileById(fileid1).getUrl();
        keyinputs[csv] = newurl;
        key[csv] = newurl;
    }
    /*if Training survey*/

    if (rr == 1) {
        try {
            /*delete existing csv meta file (trainig) in kobo*/
            deleteCsvMetaFileKobo(demoformid, "surveys.csv");
        } catch (e) { }
        /*Add csv meta file for training  survey in kobo */
        createCsvMetaFile(fileid1, demoformid, "surveys.csv");
        updateLog("Surveys CSV file updated for " + fyear + "(DEMO)");
        /**/
        var newurl = DriveApp.getFileById(fileid1).getUrl();
        keyinputs[csv] = newurl;
        key[csv] = newurl;
    }

    /*CSVURL2: URL for csv file "[name of country]" customising for that template for the beneficiary survey*/
    var csv2 = 'CSVURL2' + ID;
    /*if benefeciary survey*/
    if (r == 1) {
        var csvname = DriveApp.getFileById(fileid).getName();
        try {
            deleteCsvMetaFileKobo(realformid, csvname + ".csv");
        } catch (e) { }
        createCsvMetaFile(fileid, realformid, csvname + ".csv");
        updateLog("Country CSV file updated for " + fyear + " : " + csvname);
        /*newurl {string} - url of the file*/
        var newurl2 = DriveApp.getFileById(fileid).getUrl();
        keyinputs[csv2] = newurl2;
        key[csv2] = newurl2;
    }
    /*if training*/
    if (rr == 1) {
        var csvname = DriveApp.getFileById(fileid).getName();
        try {
            deleteCsvMetaFileKobo(demoformid, csvname + ".csv");
        } catch (e) { }
        createCsvMetaFile(fileid, demoformid, csvname + ".csv");
        updateLog("Country CSV file updated for " + fyear + " (DEMO) : " + csvname);
        var newurl2 = DriveApp.getFileById(fileid).getUrl();
        keyinputs[csv2] = newurl2;
        key[csv2] = newurl2;
    }

}

Function createCsvFile

createCsvFile(fileid, formid, id, usernamebefore, newusername)

The function creates one of the csv file which is attached to the Kob survey, so that the content of the survey be customised according to the country specific csv file

Arguments

  • fileid (string) – id of the file

  • formid (string) – id of the template

  • id (string) – id of the data

  • usernamebefore (string) – name of the form

  • newusername (string) – name of new form

  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
function createCsvFile(fileid, formid, id, usernamebefore, newusername) {
    /*ss1 {object} - spreadsheet object with the given id [Opens the spreadsheet with the given ID]*/
    var ss1 = SpreadsheetApp.openById(fileid);
    /*sheet {type} -the sheet with the given name*/
    var sheet = ss1.getSheetByName("Sheet1");

    /*mainfolderid {string} - the id of main folder [Livlehoods_Monitroing]*/
    var mainfolderid = createFolder(0, key["MAINFOLDER"]);
    /*scvsurveys {string] - the id of survey file in CSV folder*/
    var scvsurveys = createFile(key["OTHERFOLDERID"], "surveys");
    /*ss2 {object} - spreadsheet object with the given id [Opens the spreadsheet with the given ID]*/
    var ss2 = SpreadsheetApp.openById(scvsurveys);
    /*sheet2 {type} -the sheet with the given name*/
    var sheet2 = ss2.getSheetByName("Sheet1");

    var head = sheet2.getRange(1, 1, sheet2.getLastRow(), 1).getValues();
    sheet.getRange(1, 1, sheet2.getLastRow(), 1).setValues(head);

    /*gerenating url*/
    var url = "https://kobocat.unhcr.org/api/v1/data/" + formid + "/" + id;
    var option = {
        "method": "get",
        "headers": {
            "Authorization": "Token " + key["APITOKEN"],
        }
    };;
    try {
    /*reponse - the HTTP response data */
     var json = UrlFetchApp.fetch(url, option); 
    } catch (e) { var error = e; Logger.log(error.message); }
    /*jsonData {string} - the content of the HTTP response [Gets the content of an HTTP response encoded as a string]*/   
    var jsonData = JSON.parse(json.getContentText());
    var graduation = "";

   
    data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn() + 1).getValues();
    var C = sheet.getLastColumn();
    var count = 0;
    for (h = 0; h < data[0].length; h++) {
        if (data[0][h] == usernamebefore || data[0][h] == newusername) {
            if (count > 0) {
                for (iii = 0; iii < data.length; iii++) {
                    data[iii][h] = "";
                }
            }
            if (count == 0) { C = h; count++; }
        }
    }
    var csvname = usernamebefore;
    if (newusername != "undefined") {
        csvname = newusername;
    }
    data[0][C] = csvname;

    /*survey general*/
    {
        surveyreplace("Country", C, S(jsonData['CONTACT_INFORMATION/Country']));
        
        surveyreplace("LocalCurrency", C, S(jsonData['PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Local_Currency']))
      
        surveyreplace("TemplateID", C, jsonData['_id']);
    }

    /*survey O1*/
    {
        surveyreplace("O1", C, g(jsonData['INDICATOR_SELECTION/O1']));
        surveyreplace("HA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/HA1']));
        surveyreplace("SA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/SA1']));
        surveyreplace("FPA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/FPA1']));
        surveyreplace("FA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/FA1']));
        surveyreplace("MPPA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/MPPA1']));
        surveyreplace("PD1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/PD1']));
        surveyreplace("MA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/MA1']));
        surveyreplace("EMPSW1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/EMPSW1']));
        surveyreplace("PRF1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/PRF1']));
        surveyreplace("PRA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/PRA1']));
        surveyreplace("PRFISH1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/PRFISH1']));
    }

    /*survey O2*/
    {
        surveyreplace("O2", C, g(jsonData['INDICATOR_SELECTION/O2']));
        surveyreplace("HA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/HA2']));
        surveyreplace("SA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/SA2']));
        surveyreplace("FPA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/FPA2']));
        surveyreplace("FA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/FA2']));
        surveyreplace("MA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/MA2']));
    }

    /*survey O3*/
    {
        surveyreplace("O3", C, g(jsonData['INDICATOR_SELECTION/O3']));
        surveyreplace("HA3", C, g(jsonData['INDICATOR_SELECTION/OUTPUT3/HA3']));
        surveyreplace("SA3", C, g(jsonData['INDICATOR_SELECTION/OUTPUT3/SA3']));
        surveyreplace("FPA3", C, g(jsonData['INDICATOR_SELECTION/OUTPUT3/FPA3']));
        surveyreplace("FA3", C, g(jsonData['INDICATOR_SELECTION/OUTPUT3/FA3']));
    }

    /*survey OPTIONAL INDICATORS*/
    {
        surveyreplace("O1_SA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_SA']));
        surveyreplace("O1_FPA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_FPA']));
        surveyreplace("O1_MPPA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_MPPA']));
        surveyreplace("O1_PD", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_PD']));
        surveyreplace("O1_MA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_MA']));

        surveyreplace("O2_SA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT2/O2_SA']));
        surveyreplace("O2_FPA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT2/O2_FPA']));
        surveyreplace("O2_MA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT2/O2_MA']));

        surveyreplace("O3_SA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT3/O3_SA']));
        surveyreplace("O3_FPA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT3/O3_FPA']));
    }

    /*Graduation*/
    var oo = 0;
    for (ii = 0; ii < 6; ii++) {
        if (ii < jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'].length && jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Graduation'] == "Yes") {
            graduation = graduation + "," + jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Name'].replace(/_/g, " ");
            oo++;
        }
    }
    if (oo == 0) {
        graduation = "0";
    }
 
    surveyreplace("TemplateGraduation", C, g(graduation));

    /*Custome questions***/
    for (h = 0; h < 3; h++) {
        if (jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/C_IndicatorYN'] == "Yes") {
            try {
                surveyreplace("CQType" + (h + 1), C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Type']);
                surveyreplace("CQOutput" + (h + 1), C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Output']);
                surveyreplace("CQ" + (h + 1), C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Question']);
                surveyreplace("CQ" + (h + 1) + "fr", C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Question']);
                surveyreplace("CQ" + (h + 1) + "es", C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Question']);
                surveyreplace("CQ" + (h + 1) + "ar", C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Question']);

            } catch (e) {
                surveyreplace("CQType" + (h + 1), C, "0");
                surveyreplace("CQOutput" + (h + 1), C, "0");
                surveyreplace("CQ" + (h + 1), C, "0");
                surveyreplace("CQ" + (h + 1) + "fr", C, "0");
                surveyreplace("CQ" + (h + 1) + "es", C, "0");
                surveyreplace("CQ" + (h + 1) + "ar", C, "0");
            }
        } else {
            surveyreplace("CQType" + (h + 1), C, "0");
            surveyreplace("CQOutput" + (h + 1), C, "0");
            surveyreplace("CQ" + (h + 1), C, "0");
            surveyreplace("CQ" + (h + 1) + "fr", C, "0");
            surveyreplace("CQ" + (h + 1) + "es", C, "0");
            surveyreplace("CQ" + (h + 1) + "ar", C, "0");
        }
    }
    sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
This is the image caption

Survey Output 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    /*survey O1*/
    {
        surveyreplace("O1", C, g(jsonData['INDICATOR_SELECTION/O1']));
        surveyreplace("HA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/HA1']));
        surveyreplace("SA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/SA1']));
        surveyreplace("FPA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/FPA1']));
        surveyreplace("FA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/FA1']));
        surveyreplace("MPPA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/MPPA1']));
        surveyreplace("PD1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/PD1']));
        surveyreplace("MA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/MA1']));
        surveyreplace("EMPSW1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/EMPSW1']));
        surveyreplace("PRF1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/PRF1']));
        surveyreplace("PRA1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/PRA1']));
        surveyreplace("PRFISH1", C, g(jsonData['INDICATOR_SELECTION/OUTPUT1/PRFISH1']));
    }
This is the image caption

Survey Output 2

1
2
3
4
5
6
7
8
    {
        surveyreplace("O2", C, g(jsonData['INDICATOR_SELECTION/O2']));
        surveyreplace("HA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/HA2']));
        surveyreplace("SA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/SA2']));
        surveyreplace("FPA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/FPA2']));
        surveyreplace("FA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/FA2']));
        surveyreplace("MA2", C, g(jsonData['INDICATOR_SELECTION/OUTPUT2/MA2']));
    }
This is the image caption

Survey Output 3

1
2
3
4
5
6
7
8
    /*survey O3*/
    {
        surveyreplace("O3", C, g(jsonData['INDICATOR_SELECTION/O3']));
        surveyreplace("HA3", C, g(jsonData['INDICATOR_SELECTION/OUTPUT3/HA3']));
        surveyreplace("SA3", C, g(jsonData['INDICATOR_SELECTION/OUTPUT3/SA3']));
        surveyreplace("FPA3", C, g(jsonData['INDICATOR_SELECTION/OUTPUT3/FPA3']));
        surveyreplace("FA3", C, g(jsonData['INDICATOR_SELECTION/OUTPUT3/FA3']));
    }
This is the image caption

Survey Optional Indicator

 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
    /*survey OPTIONAL INDICATORS*/
    {
        surveyreplace("O1_SA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_SA']));
        surveyreplace("O1_FPA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_FPA']));
        surveyreplace("O1_MPPA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_MPPA']));
        surveyreplace("O1_PD", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_PD']));
        surveyreplace("O1_MA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT1/O1_MA']));

        surveyreplace("O2_SA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT2/O2_SA']));
        surveyreplace("O2_FPA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT2/O2_FPA']));
        surveyreplace("O2_MA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT2/O2_MA']));

        surveyreplace("O3_SA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT3/O3_SA']));
        surveyreplace("O3_FPA", C, S(jsonData['INDICATOR_SELECTION/OUTPUT3/O3_FPA']));
    }

    /*Graduation*/
    var oo = 0;
    for (ii = 0; ii < 6; ii++) {
        if (ii < jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'].length && jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Graduation'] == "Yes") {
            graduation = graduation + "," + jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Name'].replace(/_/g, " ");
            oo++;
        }
    }
    if (oo == 0) {
        graduation = "0";
    }
This is the image caption

Custome questions

 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
    /*Custome questions***/
    for (h = 0; h < 3; h++) {
        if (jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/C_IndicatorYN'] == "Yes") {
            try {
                surveyreplace("CQType" + (h + 1), C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Type']);
                surveyreplace("CQOutput" + (h + 1), C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Output']);
                surveyreplace("CQ" + (h + 1), C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Question']);
                surveyreplace("CQ" + (h + 1) + "fr", C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Question']);
                surveyreplace("CQ" + (h + 1) + "es", C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Question']);
                surveyreplace("CQ" + (h + 1) + "ar", C, jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][h]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Question']);

            } catch (e) {
                surveyreplace("CQType" + (h + 1), C, "0");
                surveyreplace("CQOutput" + (h + 1), C, "0");
                surveyreplace("CQ" + (h + 1), C, "0");
                surveyreplace("CQ" + (h + 1) + "fr", C, "0");
                surveyreplace("CQ" + (h + 1) + "es", C, "0");
                surveyreplace("CQ" + (h + 1) + "ar", C, "0");
            }
        } else {
            surveyreplace("CQType" + (h + 1), C, "0");
            surveyreplace("CQOutput" + (h + 1), C, "0");
            surveyreplace("CQ" + (h + 1), C, "0");
            surveyreplace("CQ" + (h + 1) + "fr", C, "0");
            surveyreplace("CQ" + (h + 1) + "es", C, "0");
            surveyreplace("CQ" + (h + 1) + "ar", C, "0");
        }
    }
    sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

Function addCsvFileCountry

addCsvFileCountry(fileid, formid, id, csvname)

The function adds country specific csv file with new template info [BSURVEY-> country.csv]

Arguments

  • fileid (string) – id of the file

  • formid (string) – id of the template

  • id (string) – id of the beneficiary data

  • csvname (string) – name of the csv 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
 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
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
function addCsvFileCountry(fileid, formid, id, csvname) {
    /*ss1 {object} - spreadsheet object with the given id [Opens the spreadsheet with the given ID]*/
    var ss1 = SpreadsheetApp.openById(fileid);
    /*shhet3 {type} -the sheet with the given name*/
    var sheet3 = ss1.getSheetByName("Sheet1");

    
    var url = "https://kobocat.unhcr.org/api/v1/data/" + formid + "/" + id;
    var option = {
        "method": "get",
        "headers": {
            "Authorization": "Token " + key["APITOKEN"],
        }
    };;
     /*json - the HTTP response data */
    try { var json = UrlFetchApp.fetch(url, option); } catch (e) { var error = e; Logger.log(error.message); }
    /*jsonData {string} - the content of the HTTP response [Gets the content of an HTTP response encoded as a string]*/   
    var jsonData = JSON.parse(json.getContentText());
    var graduation = "";

    sheet3.clear();
    var newData = [];
    var vy = 0;

    /*headers*/
    newData[vy] = new Array();
    /*For adding language, add elements in the below two lines*/
    newData[vy].push("Partner_key", "LISTNAME", "EXNAME", "EXLABEL", "EXLABEL::French", "EXLABEL::Spanish", "EXLABEL::Arabic", "Filter", "MULTI", "EXLABEL::Portuguese_(Mozambique)", "EXLABEL::Ukrainian","EXLABEL::Swahili");
    var Translate = ["", "", "", "", "fr", "es", "ar", "", "", "pt-PT", "uk","sw"];

    vy++;
    newData[vy] = new Array();
    for (var vx = 0; vx < newData[0].length; vx++) { newData[vy][vx] = ""; }


    /*working with partner name*/
    for (i = 0; i < 6; i++) {
        vy++;
        newData[vy] = new Array();
        for (var vx = 0; vx < newData[0].length; vx++) { newData[vy][vx] = ""; }
        try {
            newData[vy][0] = gg(jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Name'].replace(/_/g, "").replace(/\s/g, ""));

            newData[vy][1] = "Partners";

            newData[vy][2] = newData[vy][0];

            for (j = 0; j < newData[0].length; j++) {
                if (newData[0][j].indexOf("EXLABEL") > -1) {
                    newData[vy][j] = gg(jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Name'].replace(/_/g, " "));
                }
            }
        } catch (e) { }
    }

    sheet3.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

    /*copy template*/

    /*mainfolderid {string} - the id of main folder [Livlehoods_Monitroing]*/
    var mainfolderid = createFolder(0, key["MAINFOLDER"]);
    var csvcountry = createFile(key["OTHERFOLDERID"], "CSVTemplate");
    /*ss2 {object} - spreadsheet object with the given id [Opens the spreadsheet with the given ID]*/
    var ss2 = SpreadsheetApp.openById(csvcountry);
     /*sheet4 {type} -the sheet with the given name*/
    var sheet4 = ss2.getSheetByName("Sheet1");
    var copy = sheet4.getRange(1, 1, sheet4.getLastRow(), sheet4.getLastColumn()).getValues();
    sheet3.getRange(10, 2, copy.length, copy[0].length).setValues(copy);

    v = sheet3.getRange(1, 1, sheet3.getLastRow(), sheet3.getLastColumn()).getValues();

    /*generating output*/
    {
        if (jsonData['INDICATOR_SELECTION/O1'] != null) {
            choicereplace("O1", "Filter", g(jsonData['INDICATOR_SELECTION/O1']));
        } else {
            choicereplace("O1", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/O2'] != null) {
            choicereplace("O2", "Filter", g(jsonData['INDICATOR_SELECTION/O2']));
        } else {
            choicereplace("O2", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/O3'] != null) {
            choicereplace("O3", "Filter", g(jsonData['INDICATOR_SELECTION/O3']));
        } else {
            choicereplace("O3", "Filter", "");
        }

        if (jsonData['INDICATOR_SELECTION/OUTPUT1/PRF1'] != null) {
            choicereplace("Farming", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/PRF1']));
        } else {
            choicereplace("Farming", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT1/PRA1'] != null) {
            choicereplace("Livestock", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/PRA1']));
        } else {
            choicereplace("Livestock", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT1/PRFISH1'] != null) {
            choicereplace("Fishery", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/PRFISH1']));
        } else {
            choicereplace("Fishery", "Filter", "");
        }

        if (jsonData['INDICATOR_SELECTION/OUTPUT1/HA1'] != null) {
            choicereplace("O1_HA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/HA1']));
        } else {
            choicereplace("O1_HA", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT1/SA1'] != null) {
            choicereplace("O1_SA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/SA1']));
        } else {
            choicereplace("O1_SA", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT1/FPA1'] != null) {
            choicereplace("O1_FPA_ASSETS", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/FPA1']));
        } else {
            choicereplace("O1_FPA_ASSETS", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT1/FPA1'] != null) {
            choicereplace("O1_FPA_CASH", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/FPA1']));
        } else {
            choicereplace("O1_FPA_CASH", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT1/MPPA1'] != null) {
            choicereplace("O1_MPPA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/MPPA1']));
        } else {
            choicereplace("O1_MPPA", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT1/FA1'] != null) {
            choicereplace("O1_FA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/FA1']));
        } else {
            choicereplace("O1_FA", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT1/MA1'] != null || jsonData['INDICATOR_SELECTION/OUTPUT1/PD1'] != null) {
            choicereplace("O1_MAPD", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT1/MA1'] + "," + jsonData['INDICATOR_SELECTION/OUTPUT1/PD1']));
        } else {
            choicereplace("O1_MAPD", "Filter", "");
        }

        var EMPNAME = "";
        for (ii = 0; ii < 6; ii++) {

            if (ii < jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'].length && jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_Intervention'] != null && jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_Intervention'].indexOf("EMP") > -1) {
                EMPNAME = EMPNAME + jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Name'].replace(/_/g, " ") + ", ";
            }

        }
        choicereplace("O1_EMP", "Filter", g(EMPNAME));


        if (jsonData['INDICATOR_SELECTION/OUTPUT2/HA2'] != null) {
            choicereplace("O2_HA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT2/HA2']));
        } else {
            choicereplace("O2_HA", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT2/SA2'] != null) {
            choicereplace("O2_SA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT2/SA2']));
        } else {
            choicereplace("O2_SA", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT2/FPA2'] != null) {
            choicereplace("O2_FPA_ASSETS_START", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT2/FPA2']));
        } else {
            choicereplace("O2_FPA_ASSETS_START", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT2/FPA2'] != null) {
            choicereplace("O2_FPA_ASSETS_IMPROVE", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT2/FPA2']));
        } else {
            choicereplace("O2_FPA_ASSETS_IMPROVE", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT2/FPA2'] != null) {
            choicereplace("O2_FPA_CASH", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT2/FPA2']));
        } else {
            choicereplace("O2_FPA_CASH", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT2/FA2'] != null) {
            choicereplace("O2_FA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT2/FA2']));
        } else {
            choicereplace("O2_FA", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT2/MA2'] != null) {
            choicereplace("O2_MA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT2/MA2']));
        } else {
            choicereplace("O2_MA", "Filter", "");
        }

        EMPNAME = "";
        for (ii = 0; ii < 6; ii++) {

            if (ii < jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'].length && jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_Intervention'] != null && jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_Intervention'].indexOf("EMP") > -1) {
                EMPNAME = EMPNAME + jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Name'].replace(/_/g, " ") + ", ";
            }

        }
        choicereplace("O2_EMP", "Filter", g(EMPNAME));

        if (jsonData['INDICATOR_SELECTION/OUTPUT3/HA3'] != null) {
            choicereplace("O3_HA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT3/HA3']));
        } else {
            choicereplace("O3_HA", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT3/SA3'] != null) {
            choicereplace("O3_SA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT3/SA3']));
        } else {
            choicereplace("O3_SA", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT3/FPA3'] != null) {
            choicereplace("O3_FPA_ASSETS", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT3/FPA3']));
        } else {
            choicereplace("O3_FPA_ASSETS", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT3/FPA3'] != null) {
            choicereplace("O3_FPA_CASH", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT3/FPA3']));
        } else {
            choicereplace("O3_FPA_CASH", "Filter", "");
        }
        if (jsonData['INDICATOR_SELECTION/OUTPUT3/FA3'] != null) {
            choicereplace("O3_FA", "Filter", g(jsonData['INDICATOR_SELECTION/OUTPUT3/FA3']));
        } else {
            choicereplace("O3_FA", "Filter", "");
        }

        EMPNAME = "";
        for (ii = 0; ii < 6; ii++) {

            if (ii < jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'].length && jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_Intervention'] != null && jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_Intervention'].indexOf("EMP") > -1) {
                EMPNAME = EMPNAME + jsonData['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][ii]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Name'].replace(/_/g, " ") + ", ";
            }

        }
        choicereplace("O3_EMP_JOB", "Filter", g(EMPNAME));
        choicereplace("O3_EMP_CASE", "Filter", g(EMPNAME));

        sheet3.getRange(1, 1, sheet3.getLastRow(), sheet3.getLastColumn()).setValues(v);
    }

    /*CROP*****/
    var newData2 = [];
    var vy = 0;
    newData2[vy] = new Array();
    for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }

    partnery = 43;
    var h = 0;
    for (k = 0; k < 2; k++) {
        if (jsonData['INDICATOR_SELECTION/OUTPUT1/PRF1'] != null) {
            try {
                var CR1 = jsonData['Local_Unit/LAND_PRODUCTIVITY_UNIT/PO1_KeyCrops1'].splitCells(" ");
            } catch (e) { }

            if (CR1 != null) {

                for (o = 0; o < CR1.length; o++) {
                    if (CR1[o].indexOf("Other") > -1) {
                        continue;
                    }

                    CR1[o] = CR1[o].replace(/(^\s+)|(\s+$)/g, "");
                    CR1[o] = CR1[o].replace(/_/g, " ");
                    var W = CR1[o];
                    var n = CR1[o].length;
                    CR1[o] = CR1[o].substr(0, 1).toUpperCase() + W.substr(1, n);
                    newData2[vy][3] = CR1[o];
                    newData2[vy][2] = ggg(CR1[o]);
                    newData2[vy][1] = "MainCrops";

                    if (k == 0) {
                        newData2[vy][0] = ggg(CR1[o]);
                    }
                    if (k == 1) {
                        newData2[vy][1] = "MainCrops2";
                    }
                    vy++;
                    newData2[vy] = new Array();
                    for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
                }

            }

            CR1 = null;
            try {
                CR1 = jsonData['Local_Unit/LAND_PRODUCTIVITY_UNIT/PO1_KeyCrops'].splitCells(",");
            } catch (e) { }

            if (CR1 != null) {
                for (o = 0; o < CR1.length; o++) {

                    CR1[o] = CR1[o].replace(/(^\s+)|(\s+$)/g, "");
                    var W = CR1[o];
                    var n = CR1[o].length;
                    CR1[o] = CR1[o].substr(0, 1).toUpperCase() + W.substr(1, n);
                    newData2[vy][3] = CR1[o];
                    newData2[vy][2] = ggg(CR1[o]);
                    newData2[vy][1] = "MainCrops";
                    if (k == 0) {
                        newData2[vy][0] = ggg(CR1[o]);
                    }
                    if (k == 1) {
                        newData2[vy][1] = "MainCrops2";
                    }
                    vy++;
                    newData2[vy] = new Array();
                    for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
                }

            }

            newData2[vy][3] = "Other";
            newData2[vy][2] = "Other";
            newData2[vy][1] = "MainCrops";
            if (k == 1) {
                newData2[vy][1] = "MainCrops2";
            }
            vy++;
            newData2[vy] = new Array();
            for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }

            if (k == 1) {
                newData2[vy][3] = "NA";
                newData2[vy][2] = "NA";
                newData2[vy][1] = "MainCrops2";
                vy++;
                newData2[vy] = new Array();
                for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
            }

        }
    }

    /***CropUnit***/
    if (jsonData['Local_Unit/LAND_PRODUCTIVITY_UNIT/PO1_UnitKgYN'] != null) {

        CR1 = null;
        try {
            var CR1 = jsonData['Local_Unit/LAND_PRODUCTIVITY_UNIT/PO1_UnitCrop'].splitCells(" ");
        } catch (e) { }

        if (CR1 != null) {
            for (o = 0; o < CR1.length; o++) {
                if (CR1[o].indexOf("Other") > -1) {
                    continue;
                }

                CR1[o] = CR1[o].replace(/(^\s+)|(\s+$)/g, "");
                CR1[o] = CR1[o].replace(/_/g, " ");
                var W = CR1[o];
                var n = CR1[o].length;
                CR1[o] = CR1[o].substr(0, 1).toUpperCase() + W.substr(1, n);
                newData2[vy][3] = CR1[o];
                newData2[vy][2] = ggg(CR1[o]);
                newData2[vy][1] = "CropUnit";
                newData2[vy][0] = ggg(CR1[o]);
                vy++;
                newData2[vy] = new Array();
                for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
            }

        }

        CR1 = null;
        try {
            CR1 = jsonData['Local_Unit/LAND_PRODUCTIVITY_UNIT/PO1_UnitCrop_Other'].splitCells(",");
        } catch (e) { }

        if (CR1 != null) {
            for (o = 0; o < CR1.length; o++) {

                CR1[o] = CR1[o].replace(/(^\s+)|(\s+$)/g, "");
                var W = CR1[o];
                var n = CR1[o].length;
                CR1[o] = CR1[o].substr(0, 1).toUpperCase() + W.substr(1, n);
                newData2[vy][3] = CR1[o];
                newData2[vy][2] = ggg(CR1[o]);
                newData2[vy][1] = "CropUnit";
                newData2[vy][0] = ggg(CR1[o]);
                vy++;
                newData2[vy] = new Array();
                for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
            }

        }

        newData2[vy][3] = "Kg";
        newData2[vy][2] = "Kg";
        newData2[vy][1] = "CropUnit";
        newData2[vy][0] = "Kg";
        vy++;
        newData2[vy] = new Array();
        for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
    }

    /***LandUnit**/

    CR1 = null;
    if (jsonData['Local_Unit/LAND_PRODUCTIVITY_UNIT/PO1_UnitHectareYN'] != null) {

        try {
            var CR1 = jsonData['Local_Unit/LAND_PRODUCTIVITY_UNIT/PO1_UnitLand'].splitCells(" ");
        } catch (e) { }

        if (CR1 != null) {
            for (o = 0; o < CR1.length; o++) {
                if (CR1[o].indexOf("Other") > -1) {
                    continue;
                }

                CR1[o] = CR1[o].replace(/(^\s+)|(\s+$)/g, "");
                CR1[o] = CR1[o].replace(/_/g, " ");
                var W = CR1[o];
                var n = CR1[o].length;
                CR1[o] = CR1[o].substr(0, 1).toUpperCase() + W.substr(1, n);
                newData2[vy][3] = CR1[o];
                newData2[vy][2] = ggg(CR1[o]);
                newData2[vy][1] = "LandUnit";
                newData2[vy][0] = ggg(CR1[o]);
                vy++;
                newData2[vy] = new Array();
                for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
            }

        }

        CR1 = null;
        try {
            CR1 = jsonData['Local_Unit/LAND_PRODUCTIVITY_UNIT/PO1_UnitLand_Other'].splitCells(",");
        } catch (e) { }

        if (CR1 != null) {
            for (o = 0; o < CR1.length; o++) {

                CR1[o] = CR1[o].replace(/(^\s+)|(\s+$)/g, "");
                newData2[vy][3] = CR1[o];
                var W = CR1[o];
                var n = CR1[o].length;
                CR1[o] = CR1[o].substr(0, 1).toUpperCase() + W.substr(1, n);
                newData2[vy][2] = ggg(CR1[o]);
                newData2[vy][1] = "LandUnit";
                newData2[vy][0] = ggg(CR1[o]);
                vy++;
                newData2[vy] = new Array();
                for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
            }

        }
        newData2[vy][3] = "Hectare";
        newData2[vy][2] = "Hectare";
        newData2[vy][1] = "LandUnit";
        newData2[vy][0] = "Hectare";
        vy++;
        newData2[vy] = new Array();
        for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
    }

    /*ANIMAL*/
    {
        CR1 = null;
        if (jsonData['Local_Unit/ANIMAL_PRODUCTION_UNIT/PO1_KeyAnimals'] != null) {

            try {
                var CR1 = jsonData['Local_Unit/ANIMAL_PRODUCTION_UNIT/PO1_KeyAnimals'].splitCells(" ");
            } catch (e) { }

            if (CR1 != null) {
                for (o = 0; o < CR1.length; o++) {
                    if (CR1[o].indexOf("Other") > -1) {
                        continue;
                    }

                    CR1[o] = CR1[o].replace(/(^\s+)|(\s+$)/g, "");
                    CR1[o] = CR1[o].replace(/_/g, " ");
                    var W = CR1[o];
                    var n = CR1[o].length;
                    CR1[o] = CR1[o].substr(0, 1).toUpperCase() + W.substr(1, n);
                    newData2[vy][3] = CR1[o];
                    newData2[vy][2] = ggg(CR1[o]);
                    newData2[vy][1] = "Animals";
                    newData2[vy][0] = ggg(CR1[o]);
                    vy++;
                    newData2[vy] = new Array();
                    for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
                }

            }

            CR1 = null;
            try {
                CR1 = jsonData['Local_Unit/ANIMAL_PRODUCTION_UNIT/PO1_KeyAnimalsOther'].splitCells(",");
            } catch (e) { }

            if (CR1 != null) {
                for (o = 0; o < CR1.length; o++) {

                    CR1[o] = CR1[o].replace(/(^\s+)|(\s+$)/g, "");
                    var W = CR1[o];
                    var n = CR1[o].length;
                    CR1[o] = CR1[o].substr(0, 1).toUpperCase() + W.substr(1, n);
                    newData2[vy][3] = CR1[o];
                    newData2[vy][2] = ggg(CR1[o]);
                    newData2[vy][1] = "Animals";
                    newData2[vy][0] = ggg(CR1[o]);
                    vy++;
                    newData2[vy] = new Array();
                    for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
                }

            }

            newData2[vy][3] = "Other";
            newData2[vy][2] = "Other";
            newData2[vy][1] = "Animals";
            newData2[vy][0] = "Other";
            vy++;
            newData2[vy] = new Array();
            for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
        }
    }

    var nnn = vy;
   
    if (jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/C_IndicatorYN'] == "Yes") {
        for (i = 0; i < jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'].length; i++) {
            CR1 = "";
            try {
                CR1 = jsonData['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION'][i]['INDICATOR_SELECTION/CUSTOM_INDICATOR/CI_QUESTION/C_Indicator_Response'].splitCells(",");
            } catch (e) { }

            for (o = 0; o < CR1.length; o++) {
                if (CR1 != null) {
                    CR1[o] = CR1[o].replace(/(^\s+)|(\s+$)/g, "");
                    var W = CR1[o];
                    var n = CR1[o].length;
                    CR1[o] = CR1[o].substr(0, 1).toUpperCase() + W.substr(1, n);
                    newData2[vy][3] = CR1[o];
                    newData2[vy][2] = ggg(CR1[o]);
                    newData2[vy][1] = "CI" + (i + 1);
                    newData2[vy][0] = ggg(CR1[o]);
                } else {
                }
                vy++;
                newData2[vy] = new Array();
                for (var vx = 0; vx < newData[0].length; vx++) { newData2[vy][vx] = ""; }
            }

        }
    }

    Logger.log(newData2);

    for (var p = 0; p < newData2.length; p++) {
        if (p <= nnn) {
            for (var j = 0; j < newData[0].length; j++) {
                if (newData[0][j].indexOf("EXLABEL::") > -1) {
                    var py = p + partnery;
                    newData2[p][j] = '=if(D' + py + '<>"",GOOGLETRANSLATE(D' + py + ',"en","' + Translate[j] + '"),"")';
                  
                    if(Translate[j]=="es" && newData2[p][newData[0].indexOf("EXLABEL")]=="Acres"){ newData2[p][j]="Acres";}
                }
            }
        } else {
            for (var j = 0; j < newData[0].length; j++) {
                if (newData[0][j].indexOf("EXLABEL::") > -1) {
                    newData2[p][j] = newData2[p][3];
                }
            }
        }
    }

    sheet3.getRange(partnery, 1, newData2.length, newData2[0].length).setValues(newData2);
    Utilities.sleep(5000);

    var newData3 = sheet3.getRange(partnery, 1, newData2.length, newData2[0].length).getValues();
    for (var p = 0; p < newData3.length; p++) {

        for (var j = 0; j < newData[0].length; j++) {
            if (newData3[p][j] != "") {
                var n = newData3[p][j].length;
                newData3[p][j] = newData3[p][j].substr(0, 1).toUpperCase() + newData3[p][j].substr(1, n);
            }
        }

        var multi = "";
        for (var j = 4; j < newData[0].length; j++) {
            if (newData3[p][3] == "") { continue; }
            if (newData[0][j].indexOf("EXLABEL") > -1) {
                multi = multi + newData3[p][j] + " | ";
            }
        }
        var n = multi.length;
        multi = multi.substr(0, n - 3);
        newData3[p][8] = multi;

    }
    sheet3.getRange(partnery, 1, newData3.length, newData3[0].length).setValues(newData3);
}
This is the image caption

Function surveyreplace

surveyreplace(YY, C, ZZ)

The function replaces the part of the test in specific position

Arguments

  • YY (string) – text

  • C (string) – position of the text

  • ZZ (string) – text to replace

1
2
3
4
5
6
function surveyreplace(YY, C, ZZ) {
    for (i = 0; i < data.length; i++) {
        if (data[i][0] == YY) {
            data[i][C] = ZZ;
        }
    }