DATA BRIDGE Component¶
Function DATABRIDGE¶
-
DATABRIDGE(year, BSPS, formid, SE)¶ The function checks the existing export/import data sheet files, exports data to databridge
- Arguments
year (string) – current year
BSPS (string) – pass the BS (Beneficiary Survey) or PS (partner survey)
formid (string) – id of the form
SE (string) – options as “START”,
Dataset sheet for Benefeciary survey
Screenshot of Dataset sheet¶
Dataset sheet for Partner survey
Screenshot of Dataset sheet¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 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 | function DATABRIDGE(year, BSPS, formid, SE) {
if (year < 2017 || year == null) { return; }
/* mainfolderid {string} - id of main folder */
var mainfolderid = createFolder(0, key["MAINFOLDER"]);
YearFolder = createFolder(mainfolderid, "ALLFILES" + year);
/*DATABRIDGEFOLDER {string} - id of DATABRIDGE for specific year*/
var DATABRIDGEFOLDER = createFolder(YearFolder, "DATABRIDGE" + year);
/*Checking exisiting export on analysis data sheet files*/
/*if Beneficiary survey*/
if (BSPS == "BS") {
/*CSVURL4 : URL for beneficiary data sheet */
var url = key["CSVURL4" + formid];
if (url == null) { null; }
var idSS = url.replace('https://docs.google.com/spreadsheets/d/', '');
/*idSS {string} - link to Beneficiary survey*/
idSS = idSS.replace('/edit?usp=drivesdk', '');
}
/*if Partner survey*/
if (BSPS == "PS") {
/*ANALYSISFOLDER {string} - id of COUNTRY_ANALYSIS folder for specific year [General Component]*/
var ANALYSISFOLDER = createFolder(YearFolder, "COUNTRY_ANALYSIS" + year);
/*idSS {string} - id of the file in ANALYSIS folder [General Component]*/
var idSS = getFile(ANALYSISFOLDER, year + "Partner Data Sheet");
}
/*Analysis /partnerdata doen't exist yet*/
if (idSS == null) { return; }
/*Checking existing import databridfge file*/
for (dc = 1; dc < 4; dc++) {
/*idSS {string} - id of the file (yearDataBridge Livelihoods N) in DATABRIDGE folder [General Component]*/
var idSSDD = getFile(DATABRIDGEFOLDER, year + "DataBridge Livelihoods N" + dc);
/*Delete all sheets content at start*/
if (SE == "Start") {
if (idSSDD != null) {
var gss = SpreadsheetApp.openById(idSSDD);
if (BSPS == "BS") { gsheet = gss.getSheetByName("DataSet"); }
if (BSPS == "PS") { gsheet = gss.getSheetByName("DataSetPS"); }
var llc = gsheet.getLastColumn();
var llr = gsheet.getLastRow();
gsheet.getRange(3, 1, llr, llc).clear();
}
if (dc == 1 && idSSDD == null) {
/*for other files*/
var DDfile = createFile(key["OTHERFOLDERID"], "DataBridgeFile");
var folder = DriveApp.getFolderById(DATABRIDGEFOLDER);
var file = DriveApp.getFileById(DDfile);
file.makeCopy(year + "DataBridge Livelihoods N" + dc, folder);
/*obraining id file*/
var files = folder.getFiles();
while (files.hasNext()) {
var file2 = files.next();
if (file2.getName() == year + "DataBridge Livelihoods N" + dc) {
var idSSDD1 = file2.getId();
}
}
/*setting name range*/
var gss = SpreadsheetApp.openById(idSSDD1);
gsheet = gss.getSheetByName("DataSet");
gsheet2 = gss.getSheetByName("DataSetPS");
var Header = gsheet.getRange(2, 2, 1, gsheet.getLastColumn() - 1).getValues();
for (x = 0; x < Header[0].length; x++) {
var rng2 = gsheet.getRange(3, x + 2, 25000, 1);
gss.setNamedRange(Header[0][x], rng2);
}
var Header = gsheet2.getRange(2, 2, 1, gsheet2.getLastColumn() - 1).getValues();
for (x = 0; x < Header[0].length; x++) {
var rng2 = gsheet2.getRange(3, x + 2, 25000, 1);
gss.setNamedRange(Header[0][x], rng2);
}
break;
}
if (dc == 1) { var idSSDD1 = idSSDD; }
continue;
}
if (SE != "Start") {
if (idSSDD == null) {
var DDfile = createFile(key["OTHERFOLDERID"], "DataBridgeFile");
var folder = DriveApp.getFolderById(DATABRIDGEFOLDER);
var file = DriveApp.getFileById(DDfile);
file.makeCopy(year + "DataBridge Livelihoods N" + dc, folder);
var files = folder.getFiles();
while (files.hasNext()) {
var file2 = files.next();
if (file2.getName() == year + "DataBridge Livelihoods N" + dc) {
idSSDD = file2.getId();
}
}
/*Setting name range*/
var gss = SpreadsheetApp.openById(idSSDD1);
gsheet = gss.getSheetByName("DataSet");
gsheet2 = gss.getSheetByName("DataSetPS");
var Header = gsheet.getRange(2, 2, 1, gsheet.getLastColumn() - 1).getValues();
for (x = 0; x < Header[0].length; x++) {
var rng2 = gsheet.getRange(3, x + 2, 25000, 1);
gss.setNamedRange(Header[0][x], rng2);
}
var Header = gsheet2.getRange(2, 2, 1, gsheet2.getLastColumn() - 1).getValues();
for (x = 0; x < Header[0].length; x++) {
var rng2 = gsheet2.getRange(3, x + 2, 25000, 1);
gss.setNamedRange(Header[0][x], rng2);
}
}
var gss = SpreadsheetApp.openById(idSSDD);
if (BSPS == "BS") { gsheet = gss.getSheetByName("DataSet"); }
if (BSPS == "PS") { gsheet = gss.getSheetByName("DataSetPS"); }
var llr = gsheet.getLastRow();
if (llr < 50000) { break; }
}
}
if (SE == "Start") { databridge(idSS, idSSDD1, BSPS); } else { databridge(idSS, idSSDD, BSPS); }
updateLog("DATA EXPORTED TO DATABRIDGE: " + year + " " + BSPS);
return 1;
}
|
Function databridge [function]¶
-
databridge(idSS, idSSDD, BSPS)¶ The function exports data to databridge
- Arguments
idSS (string) – file for Partner survey/ link for Beneficiary survey
idSSDD (string) – id of the file (yearDataBridge Livelihoods N) in DATABRIDGE folder
BSPS (string) – pass the BS (Beneficiary Survey) or PS (partner survey)
INFO sheet in DATABRIDGE file
Screenshot of INFO sheet¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 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 | function databridge(idSS, idSSDD, BSPS) {
/*getting id of Analysis sheet*/
var sst = SpreadsheetApp.openById(idSS);
var gss = SpreadsheetApp.openById(idSSDD);
/*alldatasheet {sheet} - Benefeciary survey file/Partner survey file > ALLDATA sheet*/
var alldatasheet = sst.getSheetByName("ALLDATA");
/*ppgsheet {sheet} - Benefeciary survey file/Partner survey file >ppg sheet*/
var ppgsheet = sst.getSheetByName("PPG");
var PPG = ppgsheet.getRange(1, 1, ppgsheet.getLastRow(), ppgsheet.getLastColumn()).getValues();
var gsheet;
/*getting id of databridge sheet*/
var gss = SpreadsheetApp.openById(idSSDD);
/*gsheet {sheet} - Databridge file >DataSet/DataSetPS sheet*/
if (BSPS == "BS") { gsheet = gss.getSheetByName("DataSet"); }
if (BSPS == "PS") { gsheet = gss.getSheetByName("DataSetPS"); }
/*infosheet {sheet} - INFO sheet*/
var infosheet = gss.getSheetByName("INFO");
var lr = alldatasheet.getLastRow();
var lc = alldatasheet.getLastColumn();
var llc = gsheet.getLastColumn();
var llr = gsheet.getLastRow();
var data = alldatasheet.getRange(1, 1, lr, lc).getValues();
if (data.length < 2) { return; }
var header = gsheet.getRange(1, 1, 2, llc).getValues();
var newdata = gsheet.getRange(llr + 1, 1, data.length - 1, llc).getValues();
var c1 = data[0].indexOf("DATA_CLEANED");
var c2 = data[0].indexOf("PARTNER_INFO/Nearby_Camp");
var yyy = data[1][data[0].indexOf("MonitoringYear")];
if (BSPS == "PS") { var c2 = data[0].indexOf("PPG"); }
var y = 1;
for (i = 1; i < header[0].length; i++) {
var ii = data[0].indexOf(header[0][i]);
for (iii = 1; iii < data.length; iii++) {
if (data[iii][c1] != "OK") {
if (i == 2) { newdata[iii - 1][2] = "PENDING" };
if (i > 1) {
continue;
}
}
if (BSPS == "PS") {
if (key["EOF"+data[iii][data[0].indexOf("TemplateID")]]=="FALSE") {
if (i == 2) { newdata[iii - 1][2] = "PENDING" };
if (i > 1) {
continue;
}
}
}
if (header[1][i] == "PPG") {
for (p = 0; p < PPG.length; p++) {
if (PPG[p][0] == data[iii][ii]) {
newdata[iii - 1][i] = PPG[p][1];
break;
}
}
} else if (header[1][i] == "Partner") {
newdata[iii - 1][i] = data[iii][ii];
for (p = 0; p < PPG.length; p++) {
if (PPG[p][0] == data[iii][ii]) {
if (PPG[p][1] != "") { newdata[iii - 1][i] = PPG[p][1] } else { newdata[iii - 1][i] = PPG[p][0]; }
break;
}
}
} else if (header[1][i] == "Country") {
newdata[iii - 1][i] = data[iii][ii];
for (p = 0; p < PPG.length; p++) {
if (PPG[p][0] == data[iii][ii]) {
if (PPG[p][1] != "") { newdata[iii - 1][i] = PPG[p][1] }
break;
}
}
} else if (header[1][i] == "Site") {
if (data[iii][ii].toString() == "0") {
newdata[iii - 1][i] = "Other";
} else if (data[iii][ii].toString() == "") {
if (data[iii][c2].toString() == "0") {
newdata[iii - 1][i] = "Other";
} else {
newdata[iii - 1][i] = data[iii][c2].replace(/_/g, " ");
}
} else {
newdata[iii - 1][i] = data[iii][ii].replace(/_/g, " ");
}
} else if (header[1][i] == "LegalStatus") {
newdata[iii - 1][i] = data[iii][ii].replace(/_/g, " ");
} else {
if (data[0][ii] == "BENEFICIARY_INFO/Age" && data[iii][ii] > 150) { data[iii][ii] = yyy - data[iii][ii]; }
newdata[iii - 1][i] = data[iii][ii];
}
if (typeof newdata[iii - 1][i] == "string"){ newdata[iii - 1][i] = newdata[iii - 1][i].replace(/\n/g, ""); }else{newdata[iii - 1][i] = newdata[iii - 1][i];}
}
}
{//TOPCODiNG FOR >100% Values
/*coding for 100% value*/
if (BSPS == "PS") {
var count = 0;
var TOPCODE = [['G_OUTPUT1/O1_Training/O1_Training_Enrolled', 'G_OUTPUT1/O1_Training/O1_Training_Completed'], ['G_OUTPUT1/O1_Training/O1_Training_Enrolled_Host', 'G_OUTPUT1/O1_Training/O1_Training_Completed_Host'], ['G_OUTPUT1/O1_FPA_FarmingAssets/O1_FPA_FarmingAssets_Plan', 'G_OUTPUT1/O1_FPA_FarmingAssets/O1_FPA_FarmingAssets_Actual'], ['G_OUTPUT1/O1_FPA_FarmingAssets/O1_FPA_FarmingAssets_Plan_Host', 'G_OUTPUT1/O1_FPA_FarmingAssets/O1_FPA_FarmingAssets_Actual_Host'], ['G_OUTPUT1/O1_FPA_LivestockAssets/O1_FPA_LivestockAssets_Plan', 'G_OUTPUT1/O1_FPA_LivestockAssets/O1_FPA_LivestockAssets_Actual'], ['G_OUTPUT1/O1_FPA_LivestockAssets/O1_FPA_LivestockAssets_Plan_Host', 'G_OUTPUT1/O1_FPA_LivestockAssets/O1_FPA_LivestockAssets_Actual_Host'], ['G_OUTPUT1/O1_FPA_FisheryAssets/O1_FPA_FisheryAssets_Plan', 'G_OUTPUT1/O1_FPA_FisheryAssets/O1_FPA_FisheryAssets_Actual'], ['G_OUTPUT1/O1_FPA_FisheryAssets/O1_FPA_FisheryAssets_Plan_Host', 'G_OUTPUT1/O1_FPA_FisheryAssets/O1_FPA_FisheryAssets_Actual_Host'], ['G_OUTPUT1/O1_FPA_001/O1_FPA_Cash_Plan', 'G_OUTPUT1/O1_FPA_001/O1_FPA_Cash_Actual'], ['G_OUTPUT1/O1_FPA_001/O1_FPA_Cash_Plan_Host', 'G_OUTPUT1/O1_FPA_001/O1_FPA_Cash_Actual_Host'], ['G_OUTPUT1/O1_MPPA1/O1_MPPA1_Plan', 'G_OUTPUT1/O1_MPPA1/O1_MPPA1_Actual'], ['G_OUTPUT1/O1_MPPA1/O1_MPPA1_Plan_Host', 'G_OUTPUT1/O1_MPPA1/O1_MPPA1_Actual_Host'], ['G_OUTPUT2/O2_Training/O2_Training_Enrolled', 'G_OUTPUT2/O2_Training/O2_Training_Completed'], ['G_OUTPUT2/O2_Training/O2_Training_Enrolled_Host', 'G_OUTPUT2/O2_Training/O2_Training_Completed_Host'], ['G_OUTPUT2/O2_FPA_001/O2_FPA_Plan', 'G_OUTPUT2/O2_FPA_001/O2_FPA_Actual'], ['G_OUTPUT2/O2_FPA_001/O2_FPA_Plan_Host', 'G_OUTPUT2/O2_FPA_001/O2_FPA_Actual_Host'], ['G_OUTPUT2/O2_FPA_001/O2_FPA_Cash_Plan', 'G_OUTPUT2/O2_FPA_001/O2_FPA_Cash_Actual'], ['G_OUTPUT2/O2_FPA_001/O2_FPA_Cash_Plan_Host', 'G_OUTPUT2/O2_FPA_001/O2_FPA_Cash_Actual_Host'], ['G_OUTPUT3/O3_Training/O3_Training_Enrolled', 'G_OUTPUT3/O3_Training/O3_Training_Completed'], ['G_OUTPUT3/O3_Training/O3_Training_Enrolled_Host', 'G_OUTPUT3/O3_Training/O3_Training_Completed_Host'], ['G_OUTPUT3/O3_FPA_001/O3_FPA_Plan', 'G_OUTPUT3/O3_FPA_001/O3_FPA_Actual'], ['G_OUTPUT3/O3_FPA_001/O3_FPA_Plan_Host', 'G_OUTPUT3/O3_FPA_001/O3_FPA_Actual_Host'], ['G_OUTPUT3/O3_FPA_001/O3_FPA_Cash_Plan', 'G_OUTPUT3/O3_FPA_001/O3_FPA_Cash_Actual'], ['G_OUTPUT3/O3_FPA_001/O3_FPA_Cash_Plan_Host', 'G_OUTPUT3/O3_FPA_001/O3_FPA_Cash_Actual_Host']];
var plan;
var actual;
var modifyx;
for (var itp = 0; itp < newdata.length; itp++) {
for (var hh2 = 0; hh2 < TOPCODE.length; hh2++) {
plan = 0;
actual = 0;
modifyx = 0;
for (var h2 = 0; h2 < header[0].length; h2++) {
if (header[0][h2] == TOPCODE[hh2][0]) { plan = newdata[itp][h2]; modifyx = h2; }
if (header[0][h2] == TOPCODE[hh2][1]) { actual = newdata[itp][h2]; }
}
if (plan < actual) { newdata[itp][modifyx] = actual; count++; }
}
}
}
Logger.log(count);
}
var rng = gsheet.getRange(llr + 1, 1, data.length - 1, llc);
rng.setValues(newdata);
rng.setFontFamily("arial");
for (h = 1; h < header[0].length; h++) {
if (header[1][h].indexOf("KG") > -1 || header[1][h].indexOf("HA") > -1) { gsheet.getRange(llr + 1, h + 1, data.length - 1, 1).setNumberFormat("0.00000"); }
}
{
if (BSPS == "BS") { infosheet.getRange(2, 3).setValue("BS=" + Date()); }
if (BSPS == "PS") { infosheet.getRange(3, 3).setValue("PS=" + Date()); }
}
|
Function EXTERNALDATABRGE¶
-
EXTERNALDATABRGE(year, formid)¶ The function checks the existing import databridge file for other forms, gets the kobodata, adds a new data
- Arguments
year (string) – current year
formid (string) – form id
INFO sheet in DATABRIDGE file
Screenshot of INFO sheet¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | function EXTERNALDATABRGE(year, formid) {
if (year < 2017 || year == null) { return; }
var mainfolderid = createFolder(0, key["MAINFOLDER"]);
YearFolder = createFolder(mainfolderid, "ALLFILES" + year);
var DATABRIDGEFOLDER = createFolder(YearFolder, "DATABRIDGE" + year);
/*Checking exsiting import databridge file*/
var idSSDD = getFile(DATABRIDGEFOLDER, year + "DataBridge Livelihoods N1");
if (idSSDD == null) { return; }
var gss = SpreadsheetApp.openById(idSSDD);
gsheet = gss.getSheetByName("ExternalData");
var llc = gsheet.getLastColumn();
var llr = gsheet.getLastRow();
gsheet.getRange(3, 1, llr, llc).clear();
var ppgsheet = gss.getSheetByName("PPG");
var PPG = ppgsheet.getRange(1, 1, ppgsheet.getLastRow(), ppgsheet.getLastColumn()).getValues();
/*getting a kobodata*/
var option = {
"method": "get",
"headers": {
"Authorization": "Token " + key["APITOKEN"]//ScriptProperties.getProperty('token'),
}
};
var querystring = '{"PARTNER/Year":"' + year + '"}'
var query2 = encodeURIComponent(querystring);
var url = 'https://kobocat.unhcr.org/api/v1/data/' + formid + '?query=' + query2;
try { var json = UrlFetchApp.fetch(url, option); } catch (e) { var error = e; Logger.log(error.message); }
var jsonData = JSON.parse(json.getContentText());
if (jsonData == null) { return; }
var header = gsheet.getRange(1, 1, 1, llc).getValues();
var newdata = [];
var y = 0;
for (i = 0; i < jsonData.length; i++) {
/*Add a new data*/
/*Baseline*/
newdata[y] = new Array();
for (ii = 0; ii < llc; ii++) {
if (header[0][ii] == "PARTNER/Country") {
newdata[y][ii] = jsonData[i][header[0][ii]];
for (p = 0; p < PPG.length; p++) {
if (PPG[p][0] == jsonData[i][header[0][ii]]) {
if (PPG[p][1] != "") { newdata[y][ii] = PPG[p][1] }
break;
}
}
} else if (jsonData[i][header[0][ii]] != null) {
newdata[y][ii] = jsonData[i][header[0][ii]];
} else {
newdata[y][ii] = "";
}
}
newdata[y][header[0].indexOf("BE")] = "Baseline";
y++;
/*Endline*/
newdata[y] = new Array();
for (ii = 0; ii < llc; ii++) {
if (ii > 2) { var e = header[0][ii].replace("_B", "_E"); } else { var e = header[0][ii]; }
if (header[0][ii] == "PARTNER/Country") {
newdata[y][ii] = jsonData[i][header[0][ii]];
for (p = 0; p < PPG.length; p++) {
if (PPG[p][0] == jsonData[i][header[0][ii]]) {
if (PPG[p][1] != "") { newdata[y][ii] = PPG[p][1] }
break;
}
}
} else if (jsonData[i][e] != null) {
newdata[y][ii] = jsonData[i][e];
} else {
newdata[y][ii] = "";
}
}
newdata[y][header[0].indexOf("BE")] = "Endline";
y++;
}
if (y > 0) { gsheet.getRange(gsheet.getLastRow() + 1, 1, newdata.length, llc).setValues(newdata); }
/*setting a name range*/
gsheet = gss.getSheetByName("ExternalData");
var namedRanges = gsheet.getNamedRanges();
if (namedRanges.length > 0) { } else {
var Header = gsheet.getRange(2, 1, 1, gsheet.getLastColumn()).getValues();
for (x = 0; x < Header[0].length; x++) {
var rng2 = gsheet.getRange(3, x + 1, 150, 1);
gss.setNamedRange(Header[0][x], rng2);
}
}
updateLog("EXTERNAL DATA EXPORTED TO DATABRIDGE: " + year);
return 1;
|