PDF Component

Function PDFP

PDFP()

The function extracts the pfd file (Public) in Country Analysis note. Public version excludes some indicators from partner survey. It excludes indicators which calculates how much in USD a beneficiary recieve

  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
function PDFP() {
    /*thisid {string} - id of active sheet*/
    var thisid = SpreadsheetApp.getActiveSpreadsheet().getId();

    var ss = SpreadsheetApp.openById(thisid);
    var sheetn = ss.getSheets();
    var bname = ss.getName();
    /*r {string} - message*/
    var r = Browser.msgBox("Do you want to upload the extracted PDF to the Platform?", Browser.Buttons.YES_NO);

    var directParents2 = DriveApp.getFileById(ss.getId()).getParents();
    while (directParents2.hasNext()) { var directParents = directParents2.next().getId(); }

    /*Create a template*/
    var idSS = SpreadsheetApp.create("TEMP").getId();
    var fileSS = DriveApp.getFileById(idSS);
    var sss = SpreadsheetApp.openById(idSS);

    var y = 2;
    var page = 1;

    var lr = sheetn[2].getLastRow();
    var xp = sheetn[2].getRange(1, 1, lr, 1).getValues();
    sheetn[2].getRange(lr + 1, 1).setValue("");

    for (ii = 0; ii < xp.length; ii++) {
        if (xp[ii][0] != "" || ii == xp.length - 1) {

            var newsheetn = sheetn[2].copyTo(sss).getSheetName();
            var newsheet = sss.getSheetByName(newsheetn);
            newsheet.setName(page);
            newsheet.getRange(1, 1, newsheet.getLastRow(), 1).clear();
            if (ii != xp.length - 1) {
                newsheet.deleteRows(ii + 1, newsheet.getLastRow() - ii + 1);
            } else {
                newsheet.deleteRows(ii + 2, newsheet.getLastRow() - ii + 2);
            }

            if (y > 2) { newsheet.deleteRows(1, y); }

            y = ii;
            page++;
        }

    }

    var sheetn2 = sss.getSheets();
    sss.deleteSheet(sheetn2[0]);

    var url = sss.getUrl();
    url = url.replace(/edit$/, '');
    var url_ext = url + 'export?exportFormat=pdf';
    var token = ScriptApp.getOAuthToken();
    var params = { method: "get", headers: { 'Authorization': 'Bearer ' + token } };
    try { var blob = UrlFetchApp.fetch(url_ext, params).getBlob(); } catch (e) { var error = e; Logger.log(error.message); }
    var idSS2 = DriveApp.createFile(blob).setName(bname + "_Public").getId();
    
    var fileSS2 = DriveApp.getFileById(idSS2);

    /*creates a new file [GENERAL component]*/ 
    var x = createFile(directParents, bname + "_Public");
    var oldfile = DriveApp.getFileById(x);
    /*Delete the old file*/  
    oldfile.setTrashed(true);

    /*Move from rootfolder to the analysis folder*/

    dest_folder = DriveApp.getFolderById(directParents);
    dest_folder.addFile(fileSS2).getId();

    /*Delet the temp file*/
    DriveApp.getRootFolder().removeFile(fileSS);
    DriveApp.getRootFolder().removeFile(fileSS2);

    if (r == "no") { return; }
    /**/
    var ccode = ['AFG', 'ALA', 'ALB', 'DZA', 'ASM', 'AND', 'AGO', 'AIA', 'ATA', 'ATG', 'ARG', 'ARM', 'ABW', 'AUS', 'AUT', 'AZE', 'BHS', 'BHR', 'BGD', 'BRB', 'BLR', 'BEL', 'BLZ', 'BEN', 'BMU', 'BTN', 'BOL', 'BIH', 'BWA', 'BVT', 'BRA', 'VGB', 'IOT', 'BRN', 'BGR', 'BFA', 'BDI', 'KHM', 'CMR', 'CAN', 'CPV', 'CYM', 'CAF', 'TCD', 'CHL', 'CHN', 'HKG', 'MAC', 'CXR', 'CCK', 'COL', 'COM', 'COG', 'COD', 'COK', 'CRI', 'CIV', 'HRV', 'CUB', 'CYP', 'CZE', 'DNK', 'DJI', 'DMA', 'DOM', 'ECU', 'EGY', 'SLV', 'GNQ', 'ERI', 'EST', 'ETH', 'FLK', 'FRO', 'FJI', 'FIN', 'FRA', 'GUF', 'PYF', 'ATF', 'GAB', 'GMB', 'GEO', 'DEU', 'GHA', 'GIB', 'GRC', 'GRL', 'GRD', 'GLP', 'GUM', 'GTM', 'GGY', 'GIN', 'GNB', 'GUY', 'HTI', 'HMD', 'VAT', 'HND', 'HUN', 'ISL', 'IND', 'IDN', 'IRN', 'IRQ', 'IRL', 'IMN', 'ISR', 'ITA', 'JAM', 'JPN', 'JEY', 'JOR', 'KAZ', 'KEN', 'KIR', 'PRK', 'KOR', 'KWT', 'KGZ', 'LAO', 'LVA', 'LBN', 'LSO', 'LBR', 'LBY', 'LIE', 'LTU', 'LUX', 'MKD', 'MDG', 'MWI', 'MYS', 'MDV', 'MLI', 'MLT', 'MHL', 'MTQ', 'MRT', 'MUS', 'MYT', 'MEX', 'FSM', 'MDA', 'MCO', 'MNG', 'MNE', 'MSR', 'MAR', 'MOZ', 'MMR', 'NAM', 'NRU', 'NPL', 'NLD', 'ANT', 'NCL', 'NZL', 'NIC', 'NER', 'NGA', 'NIU', 'NFK', 'MNP', 'NOR', 'OMN', 'PAK', 'PLW', 'PSE', 'PAN', 'PNG', 'PRY', 'PER', 'PHL', 'PCN', 'POL', 'PRT', 'PRI', 'QAT', 'REU', 'ROU', 'RUS', 'RWA', 'BLM', 'SHN', 'KNA', 'LCA', 'MAF', 'SPM', 'VCT', 'WSM', 'SMR', 'STP', 'SAU', 'SEN', 'SRB', 'SYC', 'SLE', 'SGP', 'SVK', 'SVN', 'SLB', 'SOM', 'ZAF', 'SGS', 'SSD', 'ESP', 'LKA', 'SDN', 'SUR', 'SJM', 'SWZ', 'SWE', 'CHE', 'SYR', 'TWN', 'TJK', 'TZA', 'THA', 'TLS', 'TGO', 'TKL', 'TON', 'TTO', 'TUN', 'TUR', 'TKM', 'TCA', 'TUV', 'UGA', 'UKR', 'ARE', 'GBR', 'USA', 'UMI', 'URY', 'UZB', 'VUT', 'VEN', 'VNM', 'VIR', 'WLF', 'ESH', 'YEM', 'ZMB', 'ZWE']

    var sheet = ss.getSheetByName(bname + "_Public");
    if (sheet == null) { var sheet = ss.getSheetByName(bname); }
    /*countryname1 {string} - country name*/
    var countryname1 = sheet.getRange(4, 2).getValue();
    /**/
    var ppgname = sheet.getRange(5, 2).getValue().replace(/ /g, "");
    /**/
    var LORE = sheet.getRange(6, 2).getValue();
    var LE = "";
    if (LORE.indexOf("LIVELIHOODS") > -1) { LE = "lis"; }
    if (LORE.indexOf("ENERGY") > -1) { LE = "eis"; }

    try {
        var countries = UrlFetchApp.fetch("https://" + LE + ".unhcr.org/api/data/countries");
        var clist = JSON.parse(countries.getContentText());
    }
    catch (e) { var error = e; Logger.log(error.message); }

    for (var y = 2017; y < 2031; y++) {
        if (countryname1.indexOf(y) > -1) {
            var countryname = countryname1.replace(" " + y, "");
            var countryname = countryname.toUpperCase();
            var year = y;
            break;
        }
    }

    var countv = 0;
    for (var i = 0; i < ccode.length; i++) {
        try {
            if (clist[ccode[i]]["name"].toUpperCase() == countryname) { countryname = ccode[i]; countv = 1; }
        } catch (e) { }
    }


    for (var t = 0; t < 4; t++) {
        if (countv == 0) {
            var cname = Browser.inputBox('The name of the country did not match. Please manually enter the 3 degit (ISO ALFA 3 at https://en.wikipedia.org/wiki/ISO_3166-1_alpha-3) code: ');
            if (ccode.indexOf(cname) > -1) { countryname = cname; break; }
        }
    }

    var id;
    try {
        var repositorylist = UrlFetchApp.fetch("https://" + LE + ".unhcr.org/api/data/repository/?year=" + year + "&ppg=" + ppgname);
        var repositorylist = JSON.parse(repositorylist.getContentText());
    }
    catch (e) { var error = e; Logger.log(error.message); }

    for (var i = 0; i < repositorylist.length; i++) {
        try {
            if (repositorylist[i]["area"] == countryname && repositorylist[i]["name"] == bname + "_Public.pdf") { id = repositorylist[i]["id"]; }
        } catch (e) { }
    }

    try { var response = UrlFetchApp.fetch("https://" + LE + ".unhcr.org/api/data/repository/" + id + "/delete_file/"); } catch (e) { }

    var boundary = "xxx";
    var requestBody = Utilities.newBlob(
        "--" + boundary + "\r\n" +
        "Content-Disposition: form-data; name=\"area\"\r\n\r\n" + countryname + "\r\n" + "--" + boundary + "\r\n" +
        "Content-Disposition: form-data; name=\"year\"\r\n\r\n" + year + "\r\n" + "--" + boundary + "\r\n" +
        "Content-Disposition: form-data; name=\"ppg\"\r\n\r\n" + ppgname + "\r\n" + "--" + boundary + "\r\n" +
        "Content-Disposition: form-data; name=\"name\"\r\n\r\n" + bname + "_Public.pdf" + "\r\n" + "--" + boundary + "\r\n" +
        "Content-Disposition: form-data; name=\"file\";filename=\"" + bname + "_Public" + ".pdf\"\r\n\r\n"
    ).getBytes()
        .concat(blob.getBytes())
        .concat(Utilities.newBlob("\r\n--" + boundary + "--\r\n").getBytes());

    var options = {
        "method": "POST",
        "contentType": "multipart/form-data; boundary=" + boundary,
        "payload": requestBody,
        "headers": {
            "Authorization": "Basic " + Utilities.base64Encode("API" + ':' + "123")
        }
    };

    try {
        var response = UrlFetchApp.fetch("https://" + LE + ".unhcr.org/api/data/repository/", options);
        Logger.log(response);
    }
    catch (e) { var error = e; Browser.msgBox(error.message); }

    var response = JSON.parse(response.getContentText());
    var htmlOutput = HtmlService
        .createHtmlOutput("<style>p { margin-left: 0pt; margin-top: 5pt;margin-bottom: 5pt;color: black; font: 16px bold, Arial; }</style><p>PDF file was upload to:<br><br>Area: " + response["area"] + "<br><br>Year: " + response["year"] + "<br><br>PPG: " + response["ppg"] + "<br><br>Link : <a href='" + response["file"] + "' target='_blank'>" + response["name"] + "</a></p>")
        .setWidth(450) //optional
        .setHeight(200); //optional
    SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'PDF Uploaded to the Platform');
}

Function PDFI

PDFI()

The function extracts the pfd file (Private) in Country Analysis note

 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
function PDFI() {
    /*thisid {string} - returns the id of current active sheet*/    
    var thisid = SpreadsheetApp.getActiveSpreadsheet().getId();
    /*ss {string} - opens the spreadsheet with the given ID.*/
    var ss = SpreadsheetApp.openById(thisid);
    var sheetn = ss.getSheets();
    var bname = ss.getName();
    /*directParents {File/FolderIterator} - gets the file with give ID and a collection of folders that are immediate parents of the file.*/
    var directParents2 = DriveApp.getFileById(ss.getId()).getParents();
    while (directParents2.hasNext()) { var directParents = directParents2.next().getId(); }

    /*idSS {type}  - id of new created spreadsheet */
    var idSS = SpreadsheetApp.create("TEMP").getId();
    /*fileSS {string}  - id of the file*/
    var fileSS = DriveApp.getFileById(idSS);
    /*sss {string} - opens the spreadsheet with the given ID.*/
    var sss = SpreadsheetApp.openById(idSS);


    var y = 2;
    var page = 1;


    var lr = sheetn[1].getLastRow();
    var xp = sheetn[1].getRange(1, 1, lr, 1).getValues();
    sheetn[1].getRange(lr + 1, 1).setValue("");

    for (ii = 0; ii < xp.length; ii++) {
        if (xp[ii][0] != "" || ii == xp.length - 1) {

            var newsheetn = sheetn[1].copyTo(sss).getSheetName();
            var newsheet = sss.getSheetByName(newsheetn);
            newsheet.setName(page);
            newsheet.getRange(1, 1, newsheet.getLastRow(), 1).clear();
            if (ii != xp.length - 1) {
                newsheet.deleteRows(ii + 1, newsheet.getLastRow() - ii + 1);
            } else {
                newsheet.deleteRows(ii + 2, newsheet.getLastRow() - ii + 2);
            }

            if (y > 2) { newsheet.deleteRows(1, y); }

            y = ii;
            page++;
        }

    }

    var sheetn2 = sss.getSheets();
    sss.deleteSheet(sheetn2[0]);

    var url = sss.getUrl();
    url = url.replace(/edit$/, '');
    var url_ext = url + 'export?exportFormat=pdf';
    var token = ScriptApp.getOAuthToken();
    var params = { method: "get", headers: { 'Authorization': 'Bearer ' + token } };
    try { var blob = UrlFetchApp.fetch(url_ext, params).getBlob(); } catch (e) { var error = e; Logger.log(error.message); }
    var idSS2 = DriveApp.createFile(blob).setName(bname + "_Internal").getId();
    var fileSS2 = DriveApp.getFileById(idSS2);

    /*creates a new file*/
    var x = createFile(directParents, bname + "_Internal");
    var oldfile = DriveApp.getFileById(x);
    /*deletes the old file*/
    oldfile.setTrashed(true);

    /*gets folder of the file*/
    dest_folder = DriveApp.getFolderById(directParents);
    dest_folder.addFile(fileSS2).getId();


    /*deletes the temporary file*/
    DriveApp.getRootFolder().removeFile(fileSS);
    DriveApp.getRootFolder().removeFile(fileSS2);
}

Function splitCells

splitCells()

The function breaks any multi-column cells in the range into individual cells

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
function splitCells() {
    /* nowsheet {type} - active sheet in a spreadsheet.*/
    var nowsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    /*Returns the selected range in the active sheet and break any multi-column cells in the range into individual cells again*/
    nowsheet.getActiveRange().breakApart();
    /*r{string} - row of active cell*/
    var r = nowsheet.getActiveCell().getRow();

    var text = nowsheet.getRange(r, 9).getValue();
    var text2 = text.splitCells(/\r\n|\r|\n/);

    for (i = 0; i < text2.length; i++) {
        if (text2[i] != "") {
            /*Sets the value of the range*/
            nowsheet.getRange(r, 9).setValue(text2[i]);
            nowsheet.getRange(r, 9, 1, 3).activate()
            /*Merge the cells in the range across the columns of the range.*/
                .mergeAcross();
            r++;
        }

    }

}

Function mergeCells

mergeCells()

The function merges the cells

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
function mergeCells() {
    /* nowsheet {type} - active sheet in a spreadsheet.*/
    var nowsheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    /*test {object}  - returns the rectangular grid of values for this range.*/
    var text = nowsheet.getActiveRange().getValues();
    var newtext = "";
    /*Merges the cells in the range together into a single block.*/
    nowsheet.getActiveRange().mergeCells();
    for (i = 0; i < text.length; i++) {

        if (text[i][0].length > 0) {

            newtext = newtext + text[i][0].toString() + "\r\n\r\n";
        }
    }
    /*Sets the value of the range.*/
    nowsheet.getActiveRange().setValue(newtext);
}