TEMPLATE SUMMARY Component

Function updateTemplateSummaryTable

updateTemplateSummaryTable(year)

The function updates the Template Summary Table and saves to folder.

Arguments

  • year (string) – the current year

Returns

data2 – changed data

  • Diagram

This is the image caption

The block diagram of TABLEUPDATE function

  • Google Drive

This is the image caption

The screenshot of Google Drive

  • Summary table

This is the image caption

The example of Summary table

  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
function updateTemplateSummaryTable(year) {
    var f = 0;
    var xx = 2;
    var yy = 0;
    var x = 0;
    var y = 0;
    var cc = 21;
    var f = 0;/*Country*/
    var C1 = "Navy";
    var C2 = "Gray";
    var C3 = "Silver";
    var numFormats = '#,##0;[Red](#,##0)';

    if (year < 2017 || year == null) { return; }
    var mainfolderid = createFolder(0, key["MAINFOLDER"]);
    YearFolder = createFolder(mainfolderid, "ALLFILES" + year);
    TEMPLATEFOLDER = createFolder(YearFolder, "TEMPLATE" + year);

    /**Checking existing import databridge file*/
    var idSS = createFile(TEMPLATEFOLDER, year + "TEMPLATE SUMMARY TABLE");

    var ss = SpreadsheetApp.openById(idSS);
    var sheet = ss.getSheetByName("Sheet1");

    var data = getData("CONTROL")
    for (d = 0; d < data.length; d++) {
        if (data[d][data[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] == year) { var FID = data[d][data[0].indexOf("formid")]; break; }
    }

    var jsonData = connectKobotoolbox(key["APITOKEN"], FID);

    sheet.clear();
    sheet.getRange(2, cc + 1).setValue(Date()); sheet.getRange(2, cc + 1).setHorizontalAlignment("right");
    x = xx; y = yy;

    /*FORMAT*/
    for (i = 2; i < cc + 2; i++) {
        sheet.setColumnWidth(i, 200);
    }

    /*HEADER*/
    {
        /*TITLE*/
        {
            y++; y++;
            sheet.getRange(y, x).setValue("LIVELIHOODS MONITORING TEMPLATE " + year);
            sheet.getRange(y, x).setFontColor(C1);
            sheet.getRange(y, x).setFontSize(22);
            sheet.getRange(y, x).setFontFamily("Oxygen");
            sheet.getRange(y, x).setFontWeight("bold");
            sheet.getRange(1, 2).setFontFamily("Oxygen");
            sheet.getRange(1, 2).setFontWeight("bold");

            y++;
        }

    }

    sheet.getRange(y, x).setValue("PROGRAMME SUMMARY");
    sheet.getRange(y, x, 1, cc).setBackground(C1);
    sheet.getRange(y, x, 1, cc).setFontColor('white');
    sheet.getRange(y, x).setFontSize(20);
    sheet.getRange(y, x).setFontFamily("Oxygen");
    sheet.getRange(y, x).setFontWeight("bold");

    y++;

    sheet.getRange(y, x, 1, cc).setBackground(C2);
    sheet.getRange(y, x, 1, cc).setFontColor('white');
    sheet.getRange(y, x, jsonData.length + 1, cc).setBorder(true, true, true, true, true, true, "grey", SpreadsheetApp.BorderStyle.SOLID);

    sheet.getRange(y, x).setValue("Country".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Station".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Population Planning Group (PPG)".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Livelihoods Budget (USD)".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Total PoC Population (PPG)".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Total Host Population".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Legal access: Land Ownership".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Legal access: Employment".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Legal access: Financial services".toUpperCase()); x++;

    {
        y--;
        sheet.getRange(y, x).setValue("PARTNER PROJECT(S) SUMMARY");
        sheet.getRange(y, x).setFontColor('white');
        sheet.getRange(y, x).setFontSize(20);
        sheet.getRange(y, x).setFontFamily("Oxygen");
        sheet.getRange(y, x).setFontWeight("bold");
        y++;
    }

    sheet.getRange(y, x).setValue("Partners".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Project Budget (USD)".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("PoC Beneficiary *1".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Host Beneficiary *1".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Cost per Capita (USD)".toUpperCase()); x++;

    {
        y--;
        sheet.getRange(y, x).setValue("OUTPUT1");
        sheet.getRange(y, x).setFontColor('white');
        sheet.getRange(y, x).setFontSize(20);
        sheet.getRange(y, x).setFontFamily("Oxygen");
        sheet.getRange(y, x).setFontWeight("bold");
        y++;
    }
    sheet.getRange(y, x).setValue("OUTPUT1".toUpperCase()); sheet.getRange(y, x).setFontColor('grey'); x++;
    sheet.getRange(y, x).setValue("Sub-sector".toUpperCase()); x++;
    sheet.getRange(y, x).setValue("Interventions".toUpperCase()); x++;

    {
        y--;
        sheet.getRange(y, x).setValue("OUTPUT2");
        sheet.getRange(y, x).setFontColor('white');
        sheet.getRange(y, x).setFontSize(20);
        sheet.getRange(y, x).setFontFamily("Oxygen");
        sheet.getRange(y, x).setFontWeight("bold");
        y++;
    }
    sheet.getRange(y, x).setValue("OUTPUT2".toUpperCase()); sheet.getRange(y, x).setFontColor('grey'); x++;
    sheet.getRange(y, x).setValue("Interventions".toUpperCase()); x++;

    {
        y--;
        sheet.getRange(y, x).setValue("OUTPUT3");
        sheet.getRange(y, x).setFontColor('white');
        sheet.getRange(y, x).setFontSize(20);
        sheet.getRange(y, x).setFontFamily("Oxygen");
        sheet.getRange(y, x).setFontWeight("bold");
        y++;
    }

    sheet.getRange(y, x).setValue("OUTPUT3".toUpperCase()); sheet.getRange(y, x).setFontColor('grey'); x++;
    sheet.getRange(y, x).setValue("Interventions".toUpperCase()); x++;

    y++; x = xx;

    for (i = 0; i < jsonData.length; i++) {
        if (jsonData[i]['PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year'] != year) { continue;}
        sheet.getRange(y, x).setValue(jsonData[i]['CONTACT_INFORMATION/Country']); sheet.getRange(y, x).setFontWeight("bold"); x++;
        sheet.getRange(y, x).setValue(jsonData[i]['CONTACT_INFORMATION/D_Station']); x++;
        sheet.getRange(y, x).setValue(jsonData[i]['PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/PPG'].replace(/_/g, " ")); x++;
        sheet.getRange(y, x).setValue(jsonData[i]['PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Objective_Budget']); sheet.getRange(y, x).setNumberFormat(numFormats); x++;
        sheet.getRange(y, x).setValue(jsonData[i]['PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Population']); sheet.getRange(y, x).setNumberFormat(numFormats); x++;
        sheet.getRange(y, x).setValue(jsonData[i]['PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Host_Population']); sheet.getRange(y, x).setNumberFormat(numFormats); x++;
        sheet.getRange(y, x).setValue(jsonData[i]['PROBLEM_STATEMENT/LEGAL_ACCESS/L_Assessment_Land']); sheet.getRange(y, x).setNumberFormat(numFormats); x++;
        sheet.getRange(y, x).setValue(jsonData[i]['PROBLEM_STATEMENT/LEGAL_ACCESS/L_Assessment_Employment']); sheet.getRange(y, x).setNumberFormat(numFormats); x++;
        sheet.getRange(y, x).setValue(jsonData[i]['PROBLEM_STATEMENT/LEGAL_ACCESS/L_Assessment_Finance']); sheet.getRange(y, x).setNumberFormat(numFormats); x++;

        /*PARTNER REPEAT*/
        var Partner = jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'].length + ": ";
        var pbudget = 0;
        var pocbene = 0;
        var hbene = 0;
        var emp1 = 0;
        var emp2 = 0;
        var emp3 = 0;

        for (p = 0; p < jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'].length; p++) {
            /*PARTNER NAME*/
            if (p > 0) { Partner = Partner + ", "; }
            Partner = Partner + jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/Partner_Name'].replace(/_/g, " ");

            /*BUDGET CALCULAITON*/
            if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_Intervention_budget'] != null) {
                pbudget = pbudget + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_Intervention_budget']);
            }
            if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_Intervention_budget'] != null) {
                pbudget = pbudget + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_Intervention_budget']);
            }
            if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_Intervention_budget'] != null) {
                pbudget = pbudget + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_Intervention_budget']);
            }
            /*PoC BENEFICIARY*/
            if (jsonData[i]['VERSION'] == null) {
                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_HA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_HA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_SA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_SA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_FPA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_FPA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_FA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_FA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_MPPA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_MPPA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_MA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_MA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_PD_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_PD_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_EMP_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_EMP_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_HA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_HA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_SA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_SA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_FPA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_FPA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_FA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_FA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_MPPA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_MPPA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_MA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_MA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_EMP_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_EMP_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_HA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_HA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_SA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_SA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_FPA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_FPA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_FA_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_FA_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_EMP_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_EMP_beneficiaries']);
                }else {
                    if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_beneficiaries'] != null) {
                        pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_beneficiaries']);
                    }
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_beneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_EMP_beneficiaries'] != null) {
                    pocbene = pocbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_EMP_beneficiaries']);
                }
            }

            //HOST BENEFICIARY
            if (jsonData[i]['VERSION'] == null) {
                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_HA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_HA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_SA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_SA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_FPA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_FPA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_FA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_FA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_MPPA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_MPPA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_MA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_MA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_PD_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_PD_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_EMP_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_EMP_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_HA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_HA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_SA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_SA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_FPA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_FPA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_FA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_FA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_MPPA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_MPPA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_MA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_MA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_EMP_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_EMP_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_HA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_HA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_SA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_SA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_FPA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_FPA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_FA_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_FA_hbeneficiaries']);
                }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_EMP_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_EMP_hbeneficiaries']);
                }else {
                    if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_hbeneficiaries'] != null) {
                        hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_hbeneficiaries']);
                    }
                }

                    if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_hbeneficiaries'] != null) {
                        hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_hbeneficiaries']);
                    }

                if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_EMP_hbeneficiaries'] != null) {
                    hbene = hbene + parseInt(jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_EMP_hbeneficiaries']);
                }
            }


            /* EMPLOYMENT INTERVENTION*/
            if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_Intervention'] != null && jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO1_Intervention'].indexOf("EMP") > -1) { emp1++; }
            if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_Intervention'] != null && jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO2_Intervention'].indexOf("EMP") > -1) { emp2++; }
            if (jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_Intervention'] != null && jsonData[i]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner'][p]['PROGRAMME_SYNOPSIS/PROJECT_INFORMATION/Partner/PARTNER_INFORMATION/PO3_Intervention'].indexOf("EMP") > -1) { emp3++; }


        }


        /*SUBSECTOR*/
        {
            var subsector = "";
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/PRF1'] != null) { subsector = subsector + "Farming  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/PRA1'] != null) { subsector = subsector + "Livestock  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/PRFISH1'] != null) { subsector = subsector + "Fishery  "; }
        }

        /*INTERVENTION*/
        {
            var int1 = "";
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/HA1'] != null) { int1 = int1 + "Human Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/SA1'] != null) { int1 = int1 + "Social Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/FPA1'] != null) { int1 = int1 + "Financial/Productive Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/FA1'] != null) { int1 = int1 + "Financial Access,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/MPPA1'] != null) { int1 = int1 + "Means to Protect Productive Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/PD1'] != null) { int1 = int1 + "Product Differenciation,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT1/MA1'] != null) { int1 = int1 + "Market Access,  "; }
            if (emp1 > 0) { int1 = int1 + "Employment Support  "; }

            var int2 = "";
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT2/HA2'] != null) { int2 = int2 + "Human Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT2/SA2'] != null) { int2 = int2 + "Social Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT2/FPA2'] != null) { int2 = int2 + "Financial/Productive Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT2/FA2'] != null) { int2 = int2 + "Financial Access,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT2/MA2'] != null) { int2 = int2 + "Market Access,  "; }
            if (emp2 > 0) { int2 = int2 + "Employment Support  "; }

            var int3 = "";
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT3/HA3'] != null) { int3 = int3 + "Human Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT3/SA3'] != null) { int3 = int3 + "Social Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT3/FPA3'] != null) { int3 = int3 + "Financial/Productive Assets,  "; }
            if (jsonData[i]['INDICATOR_SELECTION/OUTPUT3/FA3'] != null) { int3 = int3 + "Financial Access,  "; }
            if (emp2 > 0) { int3 = int3 + "Employment Support  "; }

        }

        sheet.getRange(y, x).setValue(Partner); x++;
        sheet.getRange(y, x).setValue(pbudget); sheet.getRange(y, x).setNumberFormat(numFormats); x++;
        sheet.getRange(y, x).setValue(pocbene); sheet.getRange(y, x).setNumberFormat(numFormats); x++;
        sheet.getRange(y, x).setValue(hbene); sheet.getRange(y, x).setNumberFormat(numFormats); x++;
        sheet.getRange(y, x).setValue(pbudget / (pocbene + hbene)); sheet.getRange(y, x).setNumberFormat(numFormats); sheet.setColumnWidth(x, 150); x++;

        if (jsonData[i]['INDICATOR_SELECTION/O1'] != null) { sheet.getRange(y, x).setValue("✔"); sheet.getRange(y, x).setHorizontalAlignment("center"); }; sheet.setColumnWidth(x, 60); x++;
        if (jsonData[i]['INDICATOR_SELECTION/O1'] != null) { sheet.getRange(y, x).setValue(subsector); }; sheet.setColumnWidth(x, 100); x++;
        if (jsonData[i]['INDICATOR_SELECTION/O1'] != null) { sheet.getRange(y, x).setValue(int1); }; sheet.setColumnWidth(x, 300); x++;
        if (jsonData[i]['INDICATOR_SELECTION/O2'] != null) { sheet.getRange(y, x).setValue("✔"); sheet.getRange(y, x).setHorizontalAlignment("center"); }; sheet.setColumnWidth(x, 60); x++;
        if (jsonData[i]['INDICATOR_SELECTION/O2'] != null) { sheet.getRange(y, x).setValue(int2); }; sheet.setColumnWidth(x, 300); x++;
        if (jsonData[i]['INDICATOR_SELECTION/O3'] != null) { sheet.getRange(y, x).setValue("✔"); sheet.getRange(y, x).setHorizontalAlignment("center"); }; sheet.setColumnWidth(x, 60); x++;
        if (jsonData[i]['INDICATOR_SELECTION/O3'] != null) { sheet.getRange(y, x).setValue(int3); }; sheet.setColumnWidth(x, 300); x++;


        y++; x = xx;

    }