GENERAL Component¶
Function copyDatasheet¶
-
copyDatasheet(data)¶ The function copies the datasheet
- Arguments
data (array) – current data
- Returns
{data2) data array
1 2 3 4 5 6 7 8 | function copyDatasheet(data) {
var data2 = [];
for (y = 0; y < data.length; y++) {
data2[y] = new Array();
data2[y] = data[y].concat();
}
return data2;
}
|
Function updateScriptChild¶
-
updateScriptChild(id)¶ The function is used to populate code if there is a child of Control Panel
- Arguments
id (string) – spreadsheet ID
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 | function updateScriptChild(id) {
/*Source Project ID*/
var srcProjectId = CLIENTSCRIPTID;
/*Destination spreadsheet ID*/
var dstId = id;
var baseUrl = "https://script.googleapis.com/v1/projects";
var accessToken = ScriptApp.getOAuthToken();
/* Retrieve filename of bound-script project.*/
var srcName = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + srcProjectId, {
method: "get",
headers: { "Authorization": "Bearer " + accessToken }
}).getContentText()).title;
/* Retrieve bound-script project */
var obj = UrlFetchApp.fetch(baseUrl + "/" + srcProjectId + "/content", {
method: "get",
headers: { "Authorization": "Bearer " + accessToken }
}).getContentText();
/* Retrieve bound-script project */
var res = JSON.parse(UrlFetchApp.fetch(baseUrl + "/" + dstId + "/content", {
method: "put",
contentType: 'application/json',
headers: { "Authorization": "Bearer " + accessToken },
payload: obj
}).getContentText());
return (1);
}
|
Function compareNewExistData¶
-
compareNewExistData(newdata, copydata, sheet)¶ The function compares the new data with existing one, saves and updates only the modified row in order to improve speed
- Arguments
newdata (array) – array with new data
copydata (array) – array with existing data
sheet (string) – updated sheet
1 2 3 4 5 6 7 8 9 | function compareNewExistData(newdata, copydata, sheet) {
for (i = 0; i < newdata.length; i++) {
if (newdata[i].join() != copydata[i].join()) {
var newrow = new Array();
newrow[0] = newdata[i];
sheet.getRange(i + 1, 1, 1, newdata[0].length).setValues(newrow);
}
}
}
|
Function clearTaskHistory¶
-
clearTaskHistory()¶ The function clears the tasks history from TASK sheet
1 2 3 4 | function clearTaskHistory() {
var task = ss.getSheetByName("TASKS");
task.getRange(2, 1, task.getLastRow(), task.getLastColumn()).clear();
}
|
Function clearLogHistory¶
-
clearLogHistory()¶ The function clears the log history from LOG sheet
1 2 3 4 | function clearLogHistory() {
var log = ss.getSheetByName("LOGS");
log.getRange(2, 1, log.getLastRow(), log.getLastColumn()).clear();
}
|
Function excuteAllTask¶
-
excuteAllTask(pkey)¶ The function executes all the task listed in the TASK sheet in the control panel
- Arguments
pkey (Object) – the list of key properties and their values
- Returns
keyinputs – list of key parameters
The block diagram of excuteAllTask function¶
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 | function excuteAllTask(pkey) {
key = pkey;
var keyinputserror = {};
var task = ss.getSheetByName("TASKS");
var gv = ss.getSheetByName("GV");
var startall = new Date().getTime();
var rcolor = 0;
/*temp {array} - get data from the sheet "TSKS" */
var data = getData("TASKS");
try {
for (var r = 0; r < 100; r++) {
var numbertask = 0;
var result = 0;
var e = 0;
var start = new Date().getTime();
if ((start - startall) / 1000 > 180) { break; }
{
/*TASK CHECK FIRST*/
for (t = 0; t < data.length; t++) {
if (data[t][0] == "") { numbertask++; }
if (data[t][0] == "Running") {
var last = gv.getRange(1, 2).getValue();
if (data[t][9] != "Retrying..." && (start - last) / 1000 > 5 * 60) { data[t][0] = ""; data[t][9] = "Retrying..."; }
if (data[t][9] == "Retrying..." && (start - last) / 1000 > 10 * 60) {
data[t][0] = "FAIL SKIP";
var paramater = ss.getSheetByName("PARAMATERS");
var emails = paramater.getRange(2, 1).getValue();
var message = "<b>There has been an error detected in the task list. Please review the code to resolve the issue. </b><br><br>";
var message = message + "https://script.google.com/home/projects/1dhzlc6U1ToYXRHweSotG3gkIR5aQ6zlY8sJN1ftzJg5B9e4hsRg0kdSs/executions?status=5.6";
var html = '<span style="color:black; font-size:90%;font-family:Arial">' + message + '</span>'
/* send email*/
try {
MailApp.sendEmail("", "Error Notificaiton", message, {
name: key["SECTOR"] + ' Monitoring System',
bcc: emails,
htmlBody: html
});
} catch (e) { }
}
putData("TASKS", data); return ("NA");
}
}
}
{
/* NORMAL TASKS SECOND */
for (t = 0; t < data.length; t++) {
if (data[t][0] == "") {
var dd = data[t][1];
{
rcolor = t + 1;
task.getRange(rcolor, 1).setValue("Running").setFontColor("red");;
if (data[t][9] != "Retrying...") { gv.getRange(1, 2).setValue(start); }
SpreadsheetApp.flush();
/*REMINDER*/
if (dd == "REMINDER") {
if (numbertask == 1) {
result = sendReminder();
e++;
} else if (numbertask > 1) {
task.getRange(rcolor, 1).setValue("");
SpreadsheetApp.flush();
continue;
}
}
/*EXTERNALDATABRGE, calls function from DATABRIDGE*/
if (dd == "EXTERNALDATABRGE") {
result = EXTERNALDATABRGE(data[t][2], data[t][3]);
e++;
}
/*TASK MANAGEMENT*/
/*Calls function from SURVEY component*/
if (dd == "LOCATIONUPDATE") {
result = updateManuallyLocationFile(data[t][2]);
e++;
}
/*Calls function from SURVEY component*/
if (dd == "NEWBSURVEY") {
result = createNewBSurvey(data[t][2]);
e++;
}
/*Calls function from CUSTOMISATION component*/
if (dd == "CUSTOMISE") {
result = createCustomiseCSV(data[t][2], data[t][3], data[t][4], data[t][5], data[t][6], data[t][7]);
e++;
}
/*Calls function from USER MANAGMENT component*/
if (dd == "USERACCESS") {
result = addUserAccess(data[t][2], data[t][3], data[t][4], data[t][5], data[t][6]);
e++;
}
/*Calls function from TEMPLATE component*/
if (dd == "TEMPLATEREPORT") {
result = scheduleCreatePdfReport(data[t][2], data[t][3], data[t][4], data[t][5]);
e++;
}
/*Calls function from Data Analysis component*/
if (dd == "CREATEANALYSISSHEET") {
result = createUpdateAnalysisSheet(data[t][2], data[t][3], data[t][4], data[t][5], data[t][6]);
e++;
}
/*Calls function from PARTNER component*/
if (dd == "CREATEPARTNERDATA") {
result = CREATEPARTNERDATA(data[t][2], data[t][3], data[t][4]);
e++;
}
/*Calls function from Data analysis component*/
if (dd == "ANALYSISNOTEEXTRACT") {
result = extractAnalysisNote(data[t][2], data[t][3], data[t][4], data[t][5], data[t][6]);
e++;
}
/*Calls function from Data analysis component*/
if (dd == "ANALYSISNOTECHECK") {
result = checkAnalysisNote();
e++;
}
/*Calls function from DATABRIDGE component*/
if (dd == "DATABRIDGE") {
result = DATABRIDGE(data[t][2], data[t][3], data[t][4], data[t][5]);
e++;
}
/*Calls function from MANUAL UPDATE component*/
if (dd == "EMAIL") {
result = EMAIL(data[t][2], data[t][3], data[t][4]);
e++;
}
/*Calls function from TEMPLATE SUMMARY component*/
if (dd == "TABLEUPDATE") {
result = TABLEUPDATE(data[t][2]);
e++;
}
/*Calls function from MANUAL UPDATE component*/
if (dd == "SAMPLEPROFILE") {
result = SAMPLEPROFILE(data[t][2], data[t][3], data[t][4]);
e++;
}
task.getRange(rcolor, 2).setFontColor("black");
}
if (e == 0) {
data[t][0] = "NOT FOUND";
break;
}
if (result == 1) {
var d = new Date();
data[t][0] = Utilities.formatDate(d, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "MM/dd/yyyy hh:mm:ss");
data[t][9] = (d.getTime() - start) / 1000 + " s.";
break;
}
if (result == 4) {
data[t][0] = "STEPPED";
var d = new Date();
data[t][9] = (d.getTime() - start) / 1000 + " s.";
break;
}
if (result == 3) {
data[t][0] = "CANCELED";
break;
}
if (result != 1) {
data[t][0] = "FAILED";
break;
}
}
}
}
if(numbertask == 0){break;}
putData("TASKS", data);
keyinputserror = keyinputs;
}
} catch (e) { Logger.clear();Logger.log(catchToString (e));return (keyinputserror); }
/*WHEN IT REACHES TO MAX ROW 800, IT WILL REDUCE TO 200*/
var dl = data.length;
if (dl > 800) {
var data = getData("TASKS");
var rm = data.length - 200;
for (var m = rm; m > 0; m--) {
if (data[m][0] != "FAIL SKIP") {
data.splice(m, 1);
}
}
task.getRange(2, 1, task.getLastRow(), task.getLastColumn()).clear();
putData("TASKS", data);
}
return (keyinputs);
}
|
Function catchToString¶
-
catchToString(err)¶ The function converts the error information to a readable text
- Arguments
err (string) – error information
1 2 3 4 5 6 7 8 | function catchToString (err) {
var errInfo = "ERROR FOUND:\n";
for (var prop in err) {
errInfo += " property: "+ prop+ "\n value: ["+ err[prop]+ "]\n";
}
errInfo += " toString(): " + " value: [" + err.toString() + "]";
return errInfo;
}
|
Function scheduleTask¶
-
scheduleTask(taskname, p1, p2, p3, p4, p5, p6)¶ The function schedules the tasks, skips the years indicated for Customisation
- Arguments
taskname (string) –
p1 (type) – column on Task sheet
p2 (type) – column on Task sheet
p3 (type) – column on Task sheet
p4 (type) – column on Task sheet
p5 (type) – column on Task sheet
p6 (type) – column on Task sheet
1 2 3 4 5 6 7 8 9 10 11 | function scheduleTask(taskname, p1, p2, p3, p4, p5, p6) {
var task = ss.getSheetByName("TASKS");
/*SKIPPING THE YEARS INDICATED FOR CUSTOMISATION BUT REPORTING ALLOWED*/
if (taskname == "CUSTOMISE" || taskname == "NEWBSURVEY" || taskname == "USERACCESS")
if (checkYearParametors(p1) == 0) {
return;
}
var d = new Date();
task.appendRow(["", taskname, p1, p2, p3, p4, p5, p6, d]);
}
|
Function checkYearParametors¶
-
checkYearParametors(year)¶ The function checks if the year less than the automatic year on the PARAMETORS tab
- Arguments
year (sting) –
1 2 3 4 | function checkYearParametors(year) {
if (parseInt(year) < parseInt(key["AUTOYEAR"]) || parseInt(year) > parseInt(key["AUTOYEAR2"])) { return 0; }
return 1;
}
|
Function getKoboJsonData¶
-
getKoboJsonData(API, formid)¶ The function connects to KOBOTOOLBOX by using API and formid and get the Jsondata
- Arguments
API (sting) – API link
formid (sting) – data id on Kobotoolbox
- Returns
jsonData – data array
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | function getKoboJsonData(API, formid) {
var url = 'https://kobocat.unhcr.org/api/v1/forms';
if (formid != null) { url = url + "/" + formid; }
var option = {
"method": "get",
"headers": {
"Authorization": "Token " + API
}
};
try { var json = UrlFetchApp.fetch(url, option); var jsonData = JSON.parse(json.getContentText()); } catch (e) { var error = e; Logger.log(error.message); }
return jsonData;
}
|
Function connectKobotoolbox¶
-
connectKobotoolbox(API, formid, id, qcountry)¶ The function connects to KOBOTOOLBOX
- Arguments
API (sting) – API link
formid (sting) – data id on Kobotoolbox
id (sting) – template id
qcountry (sting) – country name
- Returns
jsonData – data array
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 | function connectKobotoolbox(API, formid, id, qcountry) {
var option = {
"method": "get",
"headers": {
"Authorization": "Token " + API
}
};
if (API == key["APITOKEN"]) {
var url = 'https://kobocat.unhcr.org/api/v1/data/' + formid;
if (id != null) {
var url = 'https://kobocat.unhcr.org/api/v1/data/' + formid + "/" + id;
}
try { var json = UrlFetchApp.fetch(url, option); } catch (e) { var error = e; Logger.log(error.message); }
var jsonData = JSON.parse(json.getContentText());
return jsonData;
}
if (id != null && id != "0") {
var url = 'https://kobocat.unhcr.org/api/v1/data/' + formid + "/" + id;
try { var json = UrlFetchApp.fetch(url, option); } catch (e) { var error = e; Logger.log(error.message); }
var jsonData = JSON.parse(json.getContentText());
return jsonData;
}
/*Filter by country*/
if (qcountry != null) {
var querystring = '{"Country":"' + qcountry + '"}'
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());
return jsonData;
}
/*GET DATA NUMBER*/
var url = 'https://kobocat.unhcr.org/api/v1/data/' + formid;
var url = 'https://kobocat.unhcr.org/api/v1/data/' + formid;
var error = 0;
var x = getKoboJsonData(API, formid);
var datacount = x["num_of_submissions"];
if (datacount > 15000) { error = 1; }
else {try { var json = UrlFetchApp.fetch(url, option); var jsonData = JSON.parse(json.getContentText()); } catch (e) { error = 1; } }
if (error == 1) {
var jsonDataAll = {};
var initialid = 500000;
var interval = 350000;
var c = 0;
/*UNTIL IT REACHES DATA NUMBER*/
for (ym = 0; ym < 300; ym++) {
var min = initialid + interval * ym + 1;
var max = initialid + interval * (ym + 1);
var q = ' { "$and": [{ "_id": { "$gte": ' + min + ' }},{"_id":{ "$lt": ' + max + ' }}]}'
var query2 = encodeURIComponent(q);
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());
Logger.log(ym + " " + jsonData.length);
if (c > 0 && jsonData.length != null) { var jsonDataAll = jsonDataAll.concat(jsonData); }
if (c == 0 && jsonData.length != null) { jsonDataAll = jsonData.concat(); c = 1; }
if (jsonDataAll.length >= datacount) { break; }
}
return jsonDataAll;
|
Function connectKoboEnketo¶
-
connectKoboEnketo(API, formid)¶ The function connect to KOBOTOOLBOX enketo form
- Arguments
API (sting) – API link
formid (sting) – data id on Kobotoolbox
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | function connectKoboEnketo(API, formid) {
var url2 = "https://kobocat.unhcr.org/api/v1/forms/" + formid + "/enketo";
var option = {
"method": "get",
"headers": {
"Authorization": "Token " + API
}
};
try {
var json2 = UrlFetchApp.fetch(url2, option);
return JSON.parse(json2.getContentText());
} catch (e) { var error = e; Logger.log(error.message); }
}
|
Function etData¶
-
getData(sheet)¶ The function retrieves data from sheet
- Arguments
sheet (sting) – sheet name
- Returns
data – data
1 2 3 4 5 6 7 | function getData(sheet)
{
x = 1; if (sheet == "CONTROL") { x = 2; }
var sss = ss.getSheetByName(sheet);
var data = sss.getRange(1, x, sss.getLastRow(), sss.getLastColumn()).getValues();
return data;
}
|
Function putData¶
-
putData(sheet, data)¶ The function updates the sheet
- Arguments
sheet (sting) – sheet name
data (array) – array
1 2 3 4 5 6 | function putData(sheet, data)
{
x = 1; if (sheet == "CONTROL") { x = 2; }
var sss = ss.getSheetByName(sheet);
sss.getRange(1, x, data.length, data[0].length).setValues(data);
}
|
Function updateLog¶
-
updateLog(message)¶ The function updates the LOGs sheet
- Arguments
message (sting) –
1 2 3 4 5 | function updateLog(Message) {
var log = ss.getSheetByName("LOGS");
var d = new Date();
log.appendRow([d, Message]);
}
|
Function createFolder¶
-
createFolder(folderid, folderName)¶ The function creates a new folder or search for folder in GoogleDrive
- Arguments
folderid (sting) – id of the folder
folderName (sting) – name of the folder
- Returns
folderid – id of new folder
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 createFolder(folderid, folderName) {
if (folderName == "OTHERS" && key["OTHERFOLDERID"] != null) { return key["OTHERFOLDERID"]; }
if (folderName == "XSLFORMS" && key["XSLFORMFPOLDERID"] != null) { return key["XSLFORMFPOLDERID"]; }
if (folderid != 0) {
/*PFolder {string} - the id of the folder [gets the folder with given ID]*/
var PFolder = DriveApp.getFolderById(folderid);
/*folders {string} - the name of the folders to find [Gets a collection of all folders in the user's Drive that have the given name]*/
var folders = PFolder.getFolders();
}
else {
/*folders {string} - the name of the folders to find [Gets a collection of all folders in the user's Drive that have the given name]*/
var folders = DriveApp.getFolders();
}
/*searching for folders in GoogleDrive*/
while (folders.hasNext()) {
var folder = folders.next();
if (folderName == folder.getName()) {
return folder.getId();
}
}
/*folder {string} - nam of the new filder [Creates a folder in the root of the user's Drive with the given name]*/
var folder = PFolder.createFolder(folderName);
return folder.getId();
}
|
Function createFile¶
-
createFile(folderid, fileName)¶ The function creates a new file
- Arguments
folderid (sting) – id of the folder
fileName (sting) – the name of the file to find
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 | function createFile(folderid, fileName) {
/*Folder {string} - the id of the folder [gets the folder with given ID]*/
var Folder = DriveApp.getFolderById(folderid);
/*files {string} - collection of all files in the Google Drive that have the given name */
var files = Folder.getFiles();
/*searching for folders in GoogleDrive*/
while (files.hasNext()) {
var file = files.next();
if (file.getName() == fileName) {
return file.getId();
}
}
/**/
var TEMPFILE = SpreadsheetApp.create(fileName);
var copyFile = DriveApp.getFileById(TEMPFILE.getId());
Folder.addFile(copyFile);
DriveApp.getRootFolder().removeFile(copyFile);
/*files {string} - collection of all files in the Google Drive that have the given name */
var files = Folder.getFiles();
/*searching for folders in GoogleDrive*/
while (files.hasNext()) {
var file = files.next();
if (file.getName() == fileName) {
return file.getId();
}
}
return null;
}
|
Function getFile¶
-
getFile(folderid, fileName)¶ The function returns id of the file
- Arguments
folderid (sting) – id of the folder
fileName (sting) – the name of the file to find
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | function getFile(folderid, fileName) {
/*Folder {string} - the id of the folder [gets the folder with given ID]*/
var Folder = DriveApp.getFolderById(folderid);
/*files {string} - collection of all files in the Google Drive that have the given name */
var files = Folder.getFiles();
while (files.hasNext()) {
/*searching for folders in GoogleDrive*/
var file = files.next();
if (file.getName() == fileName) {
return file.getId();
}
}
return null;
}
|
Function TSORT¶
-
TSORT(table, column, order)¶ The Function sorts the table
- Arguments
table (sting) – table
column (sting) – column
order (sting) – order
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | function TSORT(table, column, order) {
if (order == 1) {
table.sort(function (a, b) {
var aa = a[column];
var bb = b[column];
if (aa < bb) { return 1; }
if (aa > bb) { return -1; }
return 0;
});
}
if (order == 0) {
table.sort(function (a, b) {
var aa = a[column];
var bb = b[column];
if (aa > bb) { return 1; }
if (aa < bb) { return -1; }
return 0;
});
}
return table;
}
|
Function findRowByName¶
-
findRowByName(table, column, name)¶ The function finds the row by specific name
- Arguments
table (array) – table
column (sting) – column of the table
name (sting) – name of the table
1 2 3 4 5 6 7 | function findRowByName(table, column, name)
{
var c = table[0].indexOf(column);
for (i = 0; i < table.length; i++) {
if (table[i][c] == name) { return i; }
}
}
|
Function changeEmailConfig¶
-
changeEmailConfig(pkey)¶ The function changes the status of the email notification
- Arguments
pkey (Object) – the list of key properties and their values
- Returns
keyinputs – List of key parameters.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | function changeEmailConfig(pkey) {
key = pkey;
/**/
var sheet = ss.getSheetByName("CONTROL");
var EM = "EMAIL";
var EMAILCONFIG = key[EM];
if (EMAILCONFIG == "OFF" || EMAILCONFIG == null) {
keyinputs[EM] = "ON";
var rng = sheet.getRange(2, 12);
rng.clear({ commentsOnly: true });
rng.setComment("EMAIL NOTIFICATION IS ON");
}
if (EMAILCONFIG == "ON") {
keyinputs[EM] = "OFF";
var rng = sheet.getRange(2, 12);
rng.clear({ commentsOnly: true });
rng.setComment("EMAIL NOTIFICATION IS OFF");
}
return (keyinputs);
}
|
Function generateEmailNotice¶
-
generateEmailNotice(dataid, subject, message, linkind, draft)¶ The function contains the basic template for the emails. Takes as parameters the data to put inside and send it or not (depending on the email notification button status).
- Arguments
dataid (string) – id of the data on Kobotoolbox
subject (string) – subject of the email
message (type) –
linkind (type) –
draft (type) –
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | function generateEmailNotice(dataid, subject, message0, linkid, draft) {
if (key["EMAIL"] != "ON") { return; }
var paramater = ss.getSheetByName("PARAMATERS");
var emails = paramater.getRange(2, 1).getValue();
var data = getData("CONTROL");
var message;
var d = new Date();
var dd = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy")
for (i = 0; i < data.length; i++) {
if (data[i][data[0].indexOf("_id")] == dataid) {
var row = i;
break;
}
}
if (linkid != null && linkid != "0") {
var newurl0 = DriveApp.getFileById(linkid).getUrl();
}
/*MESSAGE:*/
message = "<span style='color:black'>" + message0 + "</span>";
message = message + "<br><br><b><span style='color:navy'>1. This notification is related to the following template:</span></b>";
message = message + "<br><br><b>● Year</b>: " + data[row][data[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")];
message = message + "<br><b>● Country</b>: " + data[row][data[0].indexOf("CONTACT_INFORMATION/Country")];
message = message + "<br><b>● Duty Station</b>: " + data[row][data[0].indexOf("CONTACT_INFORMATION/D_Station")];
message = message + "<br><br><b>● Focal Point</b>: " + data[row][data[0].indexOf("CONTACT_INFORMATION/F_Point")];
message = message + "<br><b>● E-Mail</b>: " + data[row][data[0].indexOf("CONTACT_INFORMATION/Email_FP")];
if (draft != null && draft != 0) {
message = message + "<br><br><b><span style='color:navy'>2. Draft email to the focal point:</span></b>";
message = message + "<br><br>Dear " + data[row][data[0].indexOf("CONTACT_INFORMATION/F_Point")];
message = message + "<br><br>" + draft;
if (linkid != null && linkid != "0") { message = message + "<br><br><b>Link: </b>" + newurl0; }
}
/*MAINFOLDER LINK*/
var mainfolderid = createFolder(0, key["MAINFOLDER"]);
/*newurl {string} - the url of form [gets the folder with given ID]*/
var newurl = DriveApp.getFolderById(mainfolderid).getUrl();
/*newurlw {string} - the url of file [gets the file with given ID]*/
var newurl2 = DriveApp.getFileById(thisid).getUrl();
message = message + "<br><br><b><span style='color:navy'>3. For further information:</span></b>";
message = message + "<br><br><b>Main folder</b>: " + newurl
message = message + "<br><b>Control Panel</b>: " + newurl2
var html = '<span style="color:black; font-size:90%;font-family:Arial">' + message + '</span>'
/* send email*/
try {
MailApp.sendEmail("", subject, message, {
name: key["SECTOR"] + ' Monitoring System',
bcc: emails,
htmlBody: html
});
} catch (e) {
}
return 1;
}
|
Function loadCleanData¶
-
loadCleanData(fyear)¶ The function loads the clean data
- Arguments
fyear (string) –
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 | function loadCleanData(fyear) {
var count = 0;
var sheetall;
var country = [];
var tdata = getData("CONTROL");
for (i = 0; i < tdata.length; i++) {
if (tdata[i][tdata[0].indexOf("PROGRAMME_SYNOPSIS/PROGRAMME_INFORMATION/Year")] != fyear) { continue; }
if (country.indexOf(tdata[i][tdata[0].indexOf("CONTACT_INFORMATION/Country")]) > -1) { continue; } else { country.push(tdata[i][tdata[0].indexOf("CONTACT_INFORMATION/Country")]); }
if (tdata[i][tdata[0].indexOf("Cleaning")] == "") { continue; }
try {
var url = key["CSVURL4" + tdata[i][tdata[0].indexOf("_id")]];
if (url == null) { continue; }
var idSS = url.replace('https://docs.google.com/spreadsheets/d/', '');
idSS = idSS.replace('/edit?usp=drivesdk', '');
var anss = SpreadsheetApp.openById(idSS);
var sheet = anss.getSheetByName("ALLDATA");
if (count == 0) { sheetall = sheet.getDataRange().getValues(); }
if (count > 0) {
var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheetall[0].length).getValues();
sheetall = sheetall.concat(data);
}
count++;
} catch (e) { }
}
Browser.msgBox(sheetall.length);
}
|