| /Users/johnr/Desktop/IA pdf Downloads/Criteria__P__-_Coding_Project_Upload_all_2022-05-03/Product_Henri_05_01/Server/ServerSideCode.txt |
1 let DEBUG = true;
2 var ss = SpreadsheetApp.openById("1BaL53vnfuDmTZHE2HEGs1PaN7XyqfPu0JdUjkw5wXu0"); //This links the database to the server
3
4 function doGet(e){ //This gets the searchString through th URL
5
6 Logger.log(e);
7
8 const searchString = e.parameter.searchString; //This extracts the searchString from the URL
9
10 var JSONResponse = respondToRequest(searchString, e.parameter);
11 Logger.log(JSONResponse);
12 return ContentService
13 .createTextOutput(e.parameter.callback + "(" + JSONResponse + ")")
14 .setMimeType(ContentService.MimeType.JAVASCRIPT); //This converts the code to JSONP to skip through the same-origin-policy of google
15 }
16
17 function doPost(e){ //This returns the information to the website
18
19 const searchString = e.parameter.searchString; //This extracts the searchString from the URL
20
21 var JSONResponse = respondToRequest(searchString, e.parameter); //This calls the main search function which integrates security
22
23 return ContentService.createTextOutput(JSONResponse).setMimeType(ContentService.MimeType.JSON); //This converts the code to JSONP to skip through the same-origin-policy of google
24
25 }
26
27
28 function respondToRequest(searchString, parameter){ //Main search function
29 if (DEBUG){ console.info(searchString + ":" + parameter );}
30 var originalSearchString=searchString;
31 searchString=getSearchString(searchString);
32
33 var statement;
34 var statementDef=false; //This variable swictehs to true when the statement is filled to ensure that the value isn't overwritten
35 //The following code checks if a quick search button was chosen
36 if (searchString=="<FREQUENT-SEARCHES-1>"){
37 statement=ss.getSheetByName("Data").getRange("Y26").getValue();
38 statementDef=true;
39 return prematureReturn(statement);
40 }else if (searchString=="<FREQUENT-SEARCHES-2>"){
41 statement=ss.getSheetByName("Data").getRange("Y27").getValue();
42 statementDef=true;
43 return prematureReturn(statement);
44 }else if (searchString=="<LAST-ADDED>"){
45 statement=ss.getSheetByName("Data").getRange("Y28").getValue();
46 statementDef=true;
47 return prematureReturn(statement);
48 }else if (searchString=="<MOST-VIEWED-1>"){
49 statement=ss.getSheetByName("Data").getRange("Y29").getValue();
50 statementDef=true;
51 return prematureReturn(statement);
52 }else if (searchString=="<MOST-VIEWED-2>"){
53 statement=ss.getSheetByName("Data").getRange("Y30").getValue();
54 statementDef=true;
55 return prematureReturn(statement);
56 }else{ //NOT A BUTTON TERM
57
58 var isDate;
59 var keyword=[]; //THE KEYORD THAT WILL BE LOOKED FOR ON THE SHEETS
60 Logger.log("Start month test "+checkForMonth(searchString));
61 if(checkForMonth(searchString)!="None"){ //IS THERE A MONTH
62 isDate=true;
63 keyword.push(checkForMonth(searchString));
64 Logger.log("Keyword "+keyword);
65 if((findDate(searchString).length)!=0){ //IS THERE A MONTH+YEAR
66 Logger.log("MY");
67 isDate=true;
68 var arrayAA=[];
69 arrayAA=findDate(searchString);
70 for(var k=0;k<arrayAA.length;k++){
71 keyword.push(arrayAA[k]);
72 }
73 }
74 }else if((findDate(searchString).length)!=0){ //IS THERE A YEAR
75 Logger.log("Y");
76 isDate=true;
77 var arrayBB=[];
78 arrayBB=findDate(searchString);
79 for(var k=0;k<arrayBB.length;k++){
80 keyword.push(arrayBB[k]);
81 }
82 }else{ //NO DATE
83 Logger.log("Has no date");
84 isDate=false;
85 var keywordConversion = searchString;
86 keyword=keywordConversion.split(" "); //CREATE SEPERATE ARRAY SLOTS FOR EACH WORD
87 }
88 }
89
90 var counter=0;
91 var cellCount=[];
92 var points=[];
93 var mar="";
94 Logger.log("Points Logic Commenced. isDate: "+isDate);
95 if(isDate==false){ //CHECKING WORDS
96 while(counter<ss.getSheetByName("Data").getRange("P11").getValue()){ //LOOP THROUGH STORIES
97 mar="L"+(2+counter); //CURRENT CELL
98 cellCount[counter]=mar; //NAME OF CELL
99 points[counter]=keywordTest(mar, keyword); //ADD POINTS
100 counter++;
101 Logger.log(points);
102 }Logger.log("isdate is false. mar="+mar+" counter="+counter+" cellCount="+cellCount);
103 }else{ //CHECKING DATES... IS IT THE SAME THING AS ABOVE
104 while(counter<ss.getSheetByName("Data").getRange("P11").getValue()){ //LOOP THROUGH STORIES
105 mar="M"+(2+counter); //CURRENT CELL
106 cellCount[counter]=mar; //NAME OF CELL
107 points[counter]=keywordTest(mar, keyword); //ADD POINTS
108 counter++;
109 Logger.log(points);
110 }Logger.log("isdate is true. mar="+mar+" counter="+counter+" cellCount="+cellCount);
111 }
112
113 //LOAD CHAMBER WITH HIGHEST POINT STORY
114 var highestCell;
115 var highestPoints=-1;
116 var highestPoint;
117 for(var pointsLoop=0;pointsLoop<points.length;pointsLoop++){
118 if(points[pointsLoop]>highestPoints){
119 highestCell=cellCount[pointsLoop]; //highestCell is cell name of cell with highest points
120 highestPoints=points[pointsLoop];
121 highestPoint=pointsLoop;
122 }
123 }
124 var secondHighestCell;
125 var secondHighestPoints=-1;
126 var secondHighestPoint;
127 points[highestPoint]=-1; //removes the highest point from array so that the second highest is highest to find the second story
128 for(var pointsLoop=0;pointsLoop<points.length;pointsLoop++){
129 if(points[pointsLoop]>secondHighestPoints){
130 secondHighestCell=cellCount[pointsLoop]; //secondHighestCell is cell name of cell with second highest points
131 secondHighestPoints=points[pointsLoop];
132 secondHighestPoint=pointsLoop;
133 }
134 }
135 var thirdHighestCell;
136 var thirdHighestPoints=-1;
137 points[secondHighestPoint]=-1; //removes the second highest point from array so that the third highest is highest
138 for(var pointsLoop=0;pointsLoop<points.length;pointsLoop++){
139 if(points[pointsLoop]>thirdHighestPoints){
140 thirdHighestCell=cellCount[pointsLoop]; //secondHighestCell is cell name of cell with third highest points
141 thirdHighestPoints=points[pointsLoop];
142 }
143 }
144 Logger.log("Loading Chamber");
145
146 var startOfRow=1;
147
148 var secondCoordIsChar = (/[a-zA-Z]/).test(highestCell.substring(1,2)) //REGEX equation to find where the row number is in the google sheet coordinates. E.G. A1 vs AA1
149 Logger.log("secondCoordIsChar="+secondCoordIsChar);
150 if(secondCoordIsChar==true){
151 startOfRow=2
152 }
153
154 //This code finds out when the row, which becomes story number starts so that two character xCoords dont break it.
155 var storyRow=highestCell.substring(startOfRow,(highestCell.length)); //GET ROW OF STORY
156 Logger.log("storyRow"+storyRow);
157 Logger.log(ss.getSheetByName("Data").getRange("D"+storyRow).getValue());
158 ss.getSheetByName("Data").getRange("W21").setValue(ss.getSheetByName("Data").getRange("C"+storyRow).getValue()); //AUTHOR
159 ss.getSheetByName("Data").getRange("V21").setValue(ss.getSheetByName("Data").getRange("F"+storyRow).getValue()); //ARTICLE
160 ss.getSheetByName("Data").getRange("U21").setValue(ss.getSheetByName("Data").getRange("AB"+storyRow).getValue()); //DATE PUBLISHED
161 ss.getSheetByName("Data").getRange("T21").setValue(ss.getSheetByName("Data").getRange("AC"+storyRow).getValue()); //DATE
162 ss.getSheetByName("Data").getRange("S21").setValue(ss.getSheetByName("Data").getRange("D"+storyRow).getValue()); //TITLE
163 ss.getSheetByName("Data").getRange("X21").setValue(ss.getSheetByName("Data").getRange("I"+storyRow).getValue()); //Notes
164
165 var secondStoryRow=secondHighestCell.substring(1,(secondHighestCell.length)); //GET ROW OF SECOND STORY
166 Logger.log(ss.getSheetByName("Data").getRange("D"+secondStoryRow).getValue());
167 ss.getSheetByName("Data").getRange("W22").setValue(ss.getSheetByName("Data").getRange("C"+secondStoryRow).getValue()); //AUTHOR
168 ss.getSheetByName("Data").getRange("V22").setValue(ss.getSheetByName("Data").getRange("F"+secondStoryRow).getValue()); //ARTICLE
169 ss.getSheetByName("Data").getRange("U22").setValue(ss.getSheetByName("Data").getRange("AB"+secondStoryRow).getValue()); //DATE PUBLISHED
170 ss.getSheetByName("Data").getRange("T22").setValue(ss.getSheetByName("Data").getRange("AC"+secondStoryRow).getValue()); //DATE
171 ss.getSheetByName("Data").getRange("S22").setValue(ss.getSheetByName("Data").getRange("D"+secondStoryRow).getValue()); //TITLE
172 ss.getSheetByName("Data").getRange("X22").setValue(ss.getSheetByName("Data").getRange("I"+secondStoryRow).getValue()); //Notes
173
174 var thirdStoryRow=thirdHighestCell.substring(1,(thirdHighestCell.length)); //GET ROW OF SECOND STORY
175 Logger.log(ss.getSheetByName("Data").getRange("D"+thirdStoryRow).getValue());
176 ss.getSheetByName("Data").getRange("W23").setValue(ss.getSheetByName("Data").getRange("C"+thirdStoryRow).getValue()); //AUTHOR
177 ss.getSheetByName("Data").getRange("V23").setValue(ss.getSheetByName("Data").getRange("F"+thirdStoryRow).getValue()); //ARTICLE
178 ss.getSheetByName("Data").getRange("U23").setValue(ss.getSheetByName("Data").getRange("AB"+thirdStoryRow).getValue()); //DATE PUBLISHED
179 ss.getSheetByName("Data").getRange("T23").setValue(ss.getSheetByName("Data").getRange("AC"+thirdStoryRow).getValue()); //DATE
180 ss.getSheetByName("Data").getRange("S23").setValue(ss.getSheetByName("Data").getRange("D"+thirdStoryRow).getValue()); //TITLE
181 ss.getSheetByName("Data").getRange("X23").setValue(ss.getSheetByName("Data").getRange("I"+thirdStoryRow).getValue()); //Notes
182
183 var cellValue=ss.getSheetByName("Data").getRange("Y21").getValue();
184 var secondCellValue=ss.getSheetByName("Data").getRange("Y22").getValue();
185 var thirdCellValue=ss.getSheetByName("Data").getRange("Y23").getValue();
186 increaseViewed(storyRow); //CREATES MOST VIEWED
187 frequentSer(searchString); //CREATES MOST SEARCHED(FREQUENT SEARCHES)
188 if (statementDef==false){
189 statement=cellValue+"<>SECOND<>"+secondCellValue+"<>THIRD<>"+thirdCellValue;
190 }
191 Logger.log("Statement: "+statement+"<>SECURITY<>"+securityTest(getEmail(originalSearchString))+"<>SECURITY<>");
192
193
194 var myJSON = JSON.stringify(statement+"<>SECURITY<>"+securityTest(getEmail(originalSearchString))+"<>SECURITY<>");
195 Logger.log("qqqq "+myJSON);
196
197 return myJSON;
198
199 }
200
201
202 function increaseViewed(cell){
203 SpreadsheetApp.getSheetByName("Data").getRange(cell).setValue(findCell(cell)+1);
204 }
205
206 function findDate(searchString){
207 searchString = searchString.replace(/[^0-9]/g, "#"); //REGEX equation to replace all non digits.
208 var array=searchString;
209 var arrayA=array.split("#");
210
211 var matches = 0;
212 var values = [];
213 var s=0
214 while(s<arrayA.length){
215 if(arrayA[s].match(/\d{4}/)){
216 matches=matches+1;
217 values.push(arrayA[s]);
218 }
219 s=s+1;
220 }
221 return(values);
222 }
223
224 function keywordTest(mar, keyword){
225 var splitVar1=ss.getSheetByName("Data").getRange(mar).getValue();
226 var data = splitVar1.split(" ");
227 Logger.log("In the keyword test, this is the data being tested: "+data);
228 var counter=0;
229 var points=0;
230 for(var i=0;i<keyword.length;i++){
231 if(data[0].toLowerCase()==keyword[i].toLowerCase()){ //FIRST SET
232 points=points+3;
233 }
234 }
235 counter=1; //SKIP FIRST SET
236
237 while(counter<data.length){ //LOOP THE STORY WORDS
238 for(var j=0;j<keyword.length;j++){ //LOOP THE SHEET WORDS
239 if(data[counter].toLowerCase()==keyword[j].toLowerCase()){
240 points=points+1;
241 }
242 }
243 counter=counter+1;
244 }
245 return points;
246 }
247
248 function prematureReturn(statement){
249 var myJSON = JSON.stringify(statement);
250 return myJSON;
251 }
252
253 function increaseViewed(row){
254 var views=ss.getSheetByName("Form Responses").getRange("K"+row).getValue();
255 ss.getSheetByName("Form Responses").getRange("K"+row).setValue(views+1);
256
257 //Load most viewed for next time
258 Logger.log(getMostViewedRow());
259 ss.getSheetByName("Data").getRange("W29").setValue(ss.getSheetByName("Data").getRange("C"+getMostViewedRow()).getValue()); //AUTHOR
260 ss.getSheetByName("Data").getRange("V29").setValue(ss.getSheetByName("Data").getRange("F"+getMostViewedRow()).getValue()); //ARTICLE
261 ss.getSheetByName("Data").getRange("U29").setValue(ss.getSheetByName("Data").getRange("AB"+getMostViewedRow()).getValue()); //DATE PUBLISHED
262 ss.getSheetByName("Data").getRange("T29").setValue(ss.getSheetByName("Data").getRange("AC"+getMostViewedRow()).getValue()); //DATE
263 ss.getSheetByName("Data").getRange("S29").setValue(ss.getSheetByName("Data").getRange("D"+getMostViewedRow()).getValue()); //TITLE
264 ss.getSheetByName("Data").getRange("X29").setValue(ss.getSheetByName("Data").getRange("I"+getMostViewedRow()).getValue()); //Notes
265
266 //Load second most viewed for next time
267 Logger.log(getSecondMostViewedRow());
268 ss.getSheetByName("Data").getRange("W30").setValue(ss.getSheetByName("Data").getRange("C"+getSecondMostViewedRow()).getValue()); //AUTHOR
269 ss.getSheetByName("Data").getRange("V30").setValue(ss.getSheetByName("Data").getRange("F"+getSecondMostViewedRow()).getValue()); //ARTICLE
270 ss.getSheetByName("Data").getRange("U30").setValue(ss.getSheetByName("Data").getRange("AB"+getSecondMostViewedRow()).getValue()); //DATE PUBLISHED
271 ss.getSheetByName("Data").getRange("T30").setValue(ss.getSheetByName("Data").getRange("AC"+getSecondMostViewedRow()).getValue()); //DATE
272 ss.getSheetByName("Data").getRange("S30").setValue(ss.getSheetByName("Data").getRange("D"+getSecondMostViewedRow()).getValue()); //TITLE
273 ss.getSheetByName("Data").getRange("X30").setValue(ss.getSheetByName("Data").getRange("I"+getSecondMostViewedRow()).getValue()); //Notes
274 }
275
276 function getMostViewedRow(){
277 var storyNum=ss.getSheetByName("Data").getRange("P11").getValue();
278 var highest=0;
279 var highestRow=0;
280 for(var i=0;i<storyNum;i++){
281 if((ss.getSheetByName("Data").getRange("K"+(i+2)).getValue())>highest){
282 highest=ss.getSheetByName("Data").getRange("K"+(i+2)).getValue();
283 highestRow=i;
284 }
285 }
286 return highestRow+2;
287 }
288
289 function getSecondMostViewedRow(){
290 var storyNum=ss.getSheetByName("Data").getRange("P11").getValue();
291 var highest=0;
292 var secondHighestRow=0;
293 var array=[];
294 var highestRow=0;
295 for(var i=0;i<storyNum;i++){
296 array[i]=ss.getSheetByName("Data").getRange("K"+(i+2)).getValue();
297 }
298 for(var j=0;j<storyNum;j++){
299 if(array[j]>highest){
300 highest=array[j];
301 highestRow=j;
302 }
303 }array[highestRow]=0; //Remove Highest
304 highest=0;
305 for(var j=0;j<storyNum;j++){
306 if(array[j]>highest){
307 highest=array[j]
308 secondHighestRow=j;
309 }
310 }
311 return secondHighestRow+2;
312 }
313
314 function firstWord(searchString){
315 var word=searchString;
316 var array=[];
317 array=word.split(" ");
318 return array[0];
319 }
320
321 function frequentSer(searchString){ //NEEDS TO BE FINISHED. CHAMBER NEEDS TO BE LOADED.
322 var search=firstWord(searchString);
323 var serNum=ss.getSheetByName("Data").getRange("AG3").getValue(); //NUM of terms on sheet
324 var flag=false; //Is the word already there?
325 for(var j=0;j<serNum;j++){
326 if(search.toLowerCase()==(ss.getSheetByName("Data").getRange("AD"+(j+2)).getValue()).toLowerCase()){
327 flag=true;
328 ss.getSheetByName("Data").getRange("AE"+(j+2)).setValue(ss.getSheetByName("Data").getRange("AE"+(j+2)).getValue()+1);
329 }
330 }if(flag==false){
331 ss.getSheetByName("Data").getRange("AE"+(serNum+2)).setValue(1);
332 ss.getSheetByName("Data").getRange("AD"+(serNum+2)).setValue(searchString);
333 }
334
335 //Load most frequent for next time
336 //WARNING: The code below is commented out but can be used to automatically jump to the story related to the most frequent term in future updates if the client requests it.
337 //The code below also makes reference to html code in the string. This willl be parsed as html on the website instead of text.
338
339 //ss.getSheetByName("Data").getRange("W26").setValue(ss.getSheetByName("Data").getRange("C"+getFrequentSer()).getValue()); //AUTHOR
340 ss.getSheetByName("Data").getRange("V26").setValue("This is the most searched term: <br></br>"+ss.getSheetByName("Data").getRange("AD"+getFrequentSer()).getValue()); //ARTICLE
341 //ss.getSheetByName("Data").getRange("U26").setValue(ss.getSheetByName("Data").getRange("AB"+getFrequentSer()).getValue()); //DATE PUBLISHED
342 //ss.getSheetByName("Data").getRange("T26").setValue(ss.getSheetByName("Data").getRange("AC"+getFrequentSer()).getValue()); //DATE
343 //ss.getSheetByName("Data").getRange("S26").setValue(ss.getSheetByName("Data").getRange("D"+getFrequentSer()).getValue()); //TITLE
344 //ss.getSheetByName("Data").getRange("X26").setValue(ss.getSheetByName("Data").getRange("I"+getFrequentSer()).getValue()); //Notes
345
346 //Load second most frequent for next time
347 //ss.getSheetByName("Data").getRange("W27").setValue(ss.getSheetByName("Data").getRange("C"+getSecondFrequentSer()).getValue()); //AUTHOR
348 ss.getSheetByName("Data").getRange("V27").setValue("This is the second most searched term: <br></br>"+ss.getSheetByName("Data").getRange("AD"+getSecondFrequentSer()).getValue()); //ARTICLE
349 //ss.getSheetByName("Data").getRange("U27").setValue(ss.getSheetByName("Data").getRange("AB"+getSecondFrequentSer()).getValue()); //DATE PUBLISHED
350 //ss.getSheetByName("Data").getRange("T27").setValue(ss.getSheetByName("Data").getRange("AC"+getSecondFrequentSer()).getValue()); //DATE
351 //ss.getSheetByName("Data").getRange("S27").setValue(ss.getSheetByName("Data").getRange("D"+getSecondFrequentSer()).getValue()); //TITLE
352 //ss.getSheetByName("Data").getRange("X27").setValue(ss.getSheetByName("Data").getRange("I"+getSecondFrequentSer()).getValue()); //Notes
353 }
354
355
356 function getFrequentSer(){ //Function to get the most frequent search
357 var serNum=ss.getSheetByName("Data").getRange("AG3").getValue();
358 var highest=0;
359 var freqRow=0;
360 for(var i=0;i<serNum;i++){
361 if((ss.getSheetByName("Data").getRange("AE"+(i+2)).getValue())>highest){
362 highest=ss.getSheetByName("Data").getRange("AE"+(i+2)).getValue();
363 freqRow=i;
364 }
365 }
366 return freqRow+2;
367 }
368
369 function getSecondFrequentSer(){ //Function to get the second most frequent search
370 var serNum=ss.getSheetByName("Data").getRange("AG3").getValue();
371 var highest=0;
372 var secondFreqRow=0;
373 var array=[];
374 var highestRow=0;
375 for(var i=0;i<serNum;i++){
376 array[i]=ss.getSheetByName("Data").getRange("AE"+(i+2)).getValue();
377 }
378 for(var j=0;j<serNum;j++){
379 if(array[j]>highest){
380 highest=array[j];
381 highestRow=j;
382 }
383 }
384 array[highestRow]=0; //Remove highest to get the second highest
385 highest=0;
386 for(var j=0;j<serNum;j++){
387 if(array[j]>highest){
388 highest=array[j]
389 secondFreqRow=j;
390 }
391 }
392 return secondFreqRow+2;
393 }
394
395 function checkForMonth(searchString){ //Function to check if the user searchd for a month
396 var shortMon=["jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"];
397 var longMon=["january", "febuary", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december"];
398 var search=searchString.toLowerCase();
399 var month="none";
400 for(var i=0; i<12;i++){
401 if((search.indexOf(shortMon[i])!=-1) || (search.indexOf(longMon[i])!=-1)){ //Checks for different ways of writing months
402 month=shortMon[i];
403 }
404 }
405 return capitalizeFirstLetter(month);
406 }
407
408 function capitalizeFirstLetter(word){
409 return word.toString().substring(0, 1).toUpperCase() + word.toString().slice(1);
410 }
411
412 function getEmail(searchString){ //Function to gextract the user's email from the searchstring
413 return searchString.slice(searchString.indexOf("<>USER<>")+8,searchString.length);
414 }
415
416 function getSearchString(searchString){ //Function to get the most searchString without the user's email
417 return searchString.slice(0,searchString.indexOf("<>USER<>"));
418 }
419
420 function securityTest(email){ //Function to check if the user is authorised to acess the stories
421 var runs=ss.getSheetByName("Data").getRange("AG5").getValue();
422 var flag=false;
423 for(var i=0;i<runs;i++){
424 Logger.log("SEC-CHECK-01: Runs-"+runs);
425 Logger.log("SEC-CHECK-02: Email-"+email+" Data-"+ss.getSheetByName("Data").getRange("AI"+(i+1)).getValue());
426 if(JSON.stringify(ss.getSheetByName("Data").getRange("AI"+(i+1)).getValue())==JSON.stringify(email)){
427 flag=true;
428 }
429 Logger.log("SEC-CHECK-03: Flag-"+flag);
430 }return flag;
431 }