· 5 years ago · Dec 21, 2020, 03:00 PM
1/* Menu Options */
2function onOpen() {
3 var ui = SpreadsheetApp.getUi();
4 ui.createMenu('Options')
5 .addItem("Importa cursurile", 'importCourses')
6 .addItem("Update elevi curs", 'insertCourse2')
7 .addItem("Update cursurile + clasa", 'updateCourses')
8 .addItem('Prezenta Meet foie de calcul curenta tot', 'checkAll')
9 .addItem('Prezenta Meet foie de calcul curenta coloane selectate', 'check_col')
10 .addItem('Prezenta Meet foie de calcul curenta ultima coloana', 'check_col2')
11 .addItem('Prezenta inserare manual', 'check_col3')
12 .addItem('Inserare teza manual', 'teza')
13 .addItem('Inserare semestrul 2', 'sem2')
14 .addItem('Calculeaza media', 'calc_medie')
15 .addItem('Salveaza pe server selectia', 'trimite_la_mysql')
16 .addItem('Salveaza pe server ultimele 6', 'trimite_la_mysql_last_6')
17 .addToUi();
18}
19
20/*
21 Description: Option for teachers to import their
22 most 5 recently created courses
23*/
24function importCourses() {
25 var optionalArgs = {
26 teacherId: 'me',
27 courseStates: 'ACTIVE'
28 };
29 var response = Classroom.Courses.list(optionalArgs);
30 var courses = response.courses
31 for (var i = 0; i < courses.length; i++) {
32 var courseName = courses[i].name
33 var courseId = courses[i].id
34 insertCourse(courseName, courseId)
35 }
36}
37
38/*
39 Description: Create the Sheet for Course
40 @param {String} courseName - Name of Course
41 @param {String} courseId - Corresponding Classroom ID
42*/
43function insertCourse(courseName, courseId) {
44 var spreadsheetName = courseName + "(" + courseId + ")"
45 var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
46 var yourNewSheet = activeSpreadsheet.getSheetByName(spreadsheetName);
47
48 if (yourNewSheet != null) {
49 return
50 }
51
52 yourNewSheet=insertSheet( spreadsheetName, 0, 50, 15, "" )//custom made
53 yourNewSheet.appendRow(['', 'Tip prezenta'])
54 yourNewSheet.appendRow(['', 'Data actualizare'])
55 yourNewSheet.appendRow(['Nume elev', 'Adresa Email (meetcode/Data abs)','Obs' ,'Inlocuiti cu codul Meet'])
56
57 var cell = yourNewSheet.getRange(2,1);
58 var values = ['Tot','Sem_1','Sem_2','Sem_1_medii'];
59 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
60 cell.setDataValidation(validationRule);
61 cell.setValue("Tot");
62
63 yourNewSheet.setFrozenRows(3)
64 var studentNames = getRoster(courseId)["studentNames"]
65 var studentEmails = getRoster(courseId)["studentEmails"]
66 for (var i = 0; i < studentNames.length; i++) {
67 yourNewSheet.appendRow([studentNames[i],studentEmails[i]])
68 }
69 //yourNewSheet.insertColumns(6, 10);
70 for (var i = 0; i < 12; i++) {
71 var cell = yourNewSheet.getRange(1,4+i);
72 var values = ['Manual','MeetMin','MeetPrezent'];
73 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
74 cell.offset(0, 0).setDataValidation(validationRule);
75 }
76
77 yourNewSheet.autoResizeColumns(1, 3)
78 yourNewSheet.setFrozenColumns(3)
79 }
80
81
82
83function insertCourse2() {
84
85
86 var ss = SpreadsheetApp.getActiveSheet();
87 var sheet = ss.getDataRange().getValues();
88 ss.getRange(1, 1, ss.getMaxRows(),ss.getMaxColumns()).setNumberFormat('@STRING@');
89
90 ss.getRange('B1').setValue('Tip prezenta');
91 ss.getRange('B2').setValue('Data actualizare');
92 ss.getRange('B3').setValue('Adresa Email (meetcode/Data abs)');
93
94 var cell = ss.getRange(2,1);
95 var values = ['Tot','Sem_1','Sem_2','Sem_1_medii'];
96 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
97 cell.setDataValidation(validationRule);
98 cell.setValue("Tot");
99
100 var titlu=ss.getName();
101 Logger.log('titlu= %s', titlu);
102 titlu=titlu.match(/\((.*?)\)/);
103 if (titlu) {
104 var courseId=titlu[1];
105
106
107 var studentNames = getRoster(courseId)["studentNames"];
108 var studentEmails = getRoster(courseId)["studentEmails"];
109 for (var i = 0; i < studentNames.length; i++) {
110 var flag=0;
111 for (var j = 2; j < sheet.length-1; j++) {
112 var emailAddress = sheet[j+1][1];
113 var nume = sheet[j+1][0];
114 if(emailAddress==studentEmails[i])
115 {if(nume!=studentNames[i])
116 {var cell = ss.getRange(j+2, 1);
117 cell.setValue(studentNames[i]);}
118 flag=1;break;}
119 //Logger.log('email= %s',emailAddress);
120 }
121 if(flag==0){
122 Logger.log('nume=%s email= %s', studentNames[i],studentEmails[i]);
123 ss.appendRow([studentNames[i],studentEmails[i]])
124 }
125
126 }
127 }
128 ss.autoResizeColumns(1, 3)
129 //ss.setFrozenColumns(3)
130 }
131
132
133
134
135
136/*
137 Description: Adds the course's students to the course sheet
138 @param {String} courseId - Corresponding Classroom ID
139*/
140function getRoster(courseId) {
141 var studentNames = []
142 var studentEmails = []
143 var optionalArgs = {
144 pageSize: 100
145 };
146 var response = Classroom.Courses.Students.list(courseId, optionalArgs);
147 var students = response.students;
148 //Logger.log('students %s', students);
149 if(students==null)
150 {return { "studentNames":studentNames, "studentEmails":studentEmails }
151 }
152 else
153 {
154 for (var i = 0; i <= students.length; i++) {
155 try {
156 studentNames.push(students[i].profile.name.fullName)
157 studentEmails.push(students[i].profile.emailAddress)
158 } catch (err) {
159 return { "studentNames":studentNames, "studentEmails":studentEmails }
160 }
161 }//for
162 }//if null
163}
164
165
166/*
167 Description: Retrieves the Meet code from the Course Sheet
168 and uses helper function to check attendance
169*/
170function checkAll() {
171 var ss = SpreadsheetApp.getActiveSheet();
172 var sheet = ss.getDataRange().getValues();
173 ss.getRange(1, 1, ss.getMaxRows(),ss.getMaxColumns()).setNumberFormat('@STRING@');
174 for (var i = 3; i < sheet.length * 100; i++){
175 sheet = ss.getDataRange().getValues();
176 var meetCode = getCleanCode(sheet[2][i]);
177 var tip_prez = getCleanCode(sheet[0][i]);
178
179 var rand1=sheet[1][i];
180 var rand2=sheet[1][i+1];
181 if(rand1=='') {rand1="1";}
182 // No Meet code given
183
184 Logger.log('meet code %s %s', meetCode, tip_prez);
185 //if (meetCode == null || meetCode == '') {
186 if (meetCode == null ) {
187 break;
188 }
189 else {Logger.log('else meet code %s', meetCode);
190 // check whether each student was present in Meet
191 //if(tip_prez=='Meet'){
192 if(tip_prez!='Manual'){
193 remove_validation(i+1);
194
195 var date = Utilities.formatDate(new Date(), "GMT+2", "dd-MM-yyyy HH:mm:ss")
196
197 if(rand1!=rand2)
198 {
199 var cell = ss.getRange(2, i);
200 var culoare=cell.getBackground();
201 if(culoare=='#ffffff') {culoare='#d1d1d1';}
202 else if(culoare!='#ffffff') {culoare='#ffffff';}
203
204 var cols = ss.getLastColumn();
205 ss.insertColumnAfter(cols);
206 var cell = ss.getRange(1, cols+1);
207 var values = ['Manual','MeetMin','MeetPrezent'];
208 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
209 cell.offset(0, 0).setDataValidation(validationRule);
210
211 //ss.insertColumns(6, 10);
212 ss.insertColumnAfter(i+1);
213 var cell = ss.getRange(1, i+2);
214 var validationRule = null;
215 cell.setDataValidation(validationRule);
216
217
218 var cell = ss.getRange(2, i+1);
219 cell.setValue(date);
220 cell.setBackground(culoare);
221 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
222 var cell = ss.getRange(2, i+2);
223 cell.setValue(date);
224 cell.setBackground(culoare);
225 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
226 //Logger.log('culoare= %s',culoare);
227
228 add_validation2(i+2,culoare)
229 }
230 else{
231 var cell = ss.getRange(2, i+1);
232 cell.setValue(date);
233
234 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
235 var cell = ss.getRange(2, i+2);
236 cell.setValue(date);
237
238 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
239 }
240 checkMeet(meetCode, i+1);
241 i++;
242 }
243
244
245 }
246 }
247}
248
249function check_col() {//verifica selectat
250 var ss = SpreadsheetApp.getActiveSheet();
251 var sheet = ss.getDataRange().getValues();
252 ss.getRange(1, 1, ss.getMaxRows(),ss.getMaxColumns()).setNumberFormat('@STRING@');
253 for (var i = 3; i < sheet.length * 100; i++){
254 sheet = ss.getDataRange().getValues();
255 var meetCode = getCleanCode(sheet[2][i]);
256 var tip_prez = getCleanCode(sheet[0][i]);
257 var rand1=sheet[1][i];
258 var rand2=sheet[1][i+1];
259 if(rand1=='') {rand1="1";}
260 // No Meet code given
261
262 Logger.log('meet code %s %s', meetCode, tip_prez);
263 if (meetCode == null ) {
264 break;
265 }
266 else {Logger.log('else meet code %s', meetCode);
267 // check whether each student was present in Meet
268 if(tip_prez=='MeetMin' || tip_prez=='MeetPrezent'){
269 remove_validation(i+1);
270
271 var date = Utilities.formatDate(new Date(), "GMT+2", "dd-MM-yyyy HH:mm:ss")
272
273
274 if(rand1!=rand2)
275 {
276 var cell = ss.getRange(2, i);
277 var culoare=cell.getBackground();
278 if(culoare=='#ffffff') {culoare='#d1d1d1';}
279 else if(culoare!='#ffffff') {culoare='#ffffff';}
280
281 var cols = ss.getLastColumn();
282 ss.insertColumnAfter(cols);
283 var cell = ss.getRange(1, cols+1);
284 var values = ['Manual','MeetMin','MeetPrezent'];
285 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
286 cell.offset(0, 0).setDataValidation(validationRule);
287
288 //ss.insertColumns(6, 10);
289 ss.insertColumnAfter(i+1);
290 var cell = ss.getRange(1, i+2);
291 var validationRule = null;
292 cell.setDataValidation(validationRule);
293
294 var cell = ss.getRange(2, i+1);
295 cell.setValue(date);
296 cell.setBackground(culoare);
297 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
298 var cell = ss.getRange(2, i+2);
299 cell.setValue(date);
300 cell.setBackground(culoare);
301 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
302 //Logger.log('culoare= %s',culoare);
303
304 add_validation2(i+2,culoare)
305 }
306 else{
307 var cell = ss.getRange(2, i+1);
308 cell.setValue(date);
309
310 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
311 var cell = ss.getRange(2, i+2);
312 cell.setValue(date);
313
314 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
315 }
316 checkMeet(meetCode, i+1);
317 i++;
318 }
319
320 }
321 }
322}
323
324function check_col2() {//ultima coloana
325 var ss = SpreadsheetApp.getActiveSheet();
326 var sheet = ss.getDataRange().getValues();
327 ss.getRange(1, 1, ss.getMaxRows(),ss.getMaxColumns()).setNumberFormat('@STRING@');
328 //for (var i = 3; i < sheet.length * 100; i++){
329 var cols = ss.getLastColumn();
330 var i=cols-1;
331 var meetCode = getCleanCode(sheet[2][i]);
332 var tip_prez = getCleanCode(sheet[0][i]);
333 // No Meet code given
334 var rand1=sheet[1][i];
335 var rand2=sheet[1][i+1];
336 var rand3=sheet[1][i-1];
337 if(rand1=='') {rand1="1_ana_are_mere";}
338
339
340
341 if(rand3!=rand1 && rand1!=rand2)
342 {var meetCode = getCleanCode(sheet[2][i]);
343 var tip_prez = getCleanCode(sheet[0][i]);
344 }
345
346 if(rand3==rand1)
347 {var meetCode = getCleanCode(sheet[2][i-1]);
348 var tip_prez = getCleanCode(sheet[0][i-1]);
349 }
350
351
352 Logger.log('meet code %s %s', meetCode, tip_prez);
353 if (meetCode == null ) {
354 //break;
355 }
356 else {Logger.log('else meet code %s', meetCode);
357 // check whether each student was present in Meet
358 if(tip_prez!='Manual'){
359 var date = Utilities.formatDate(new Date(), "GMT+2", "dd-MM-yyyy HH:mm:ss")
360
361
362 if(rand3!=rand1 && rand1!=rand2)
363 {remove_validation(i+1);
364 var cell = ss.getRange(2, i);
365 var culoare=cell.getBackground();
366 if(culoare=='#ffffff') {culoare='#d1d1d1';}
367 else if(culoare!='#ffffff') {culoare='#ffffff';}
368
369 var cols = ss.getLastColumn();
370 ss.insertColumnAfter(cols);
371 var cell = ss.getRange(1, cols+1);
372 var values = ['Manual','MeetMin','MeetPrezent'];
373 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
374 cell.offset(0, 0).setDataValidation(validationRule);
375
376 //ss.insertColumns(6, 10);
377 ss.insertColumnAfter(i+1);
378 var cell = ss.getRange(1, i+2);
379 var validationRule = null;
380 cell.setDataValidation(validationRule);
381
382 var cell = ss.getRange(2, i+1);
383 cell.setValue(date);
384 cell.setBackground(culoare);
385 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
386 var cell = ss.getRange(2, i+2);
387 cell.setValue(date);
388 cell.setBackground(culoare);
389 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
390 //Logger.log('culoare= %s',culoare);
391
392 add_validation2(i+2,culoare)
393 checkMeet(meetCode, i+1);
394 }
395 if(rand3==rand1){
396 remove_validation(i);
397 checkMeet(meetCode, i);
398
399
400 var cell = ss.getRange(2, i);
401 cell.setValue(date);
402
403 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
404 var cell = ss.getRange(2, i+1);
405 cell.setValue(date);
406
407 cell.protect().setDescription('Nu sterge!!!').setWarningOnly(true);
408
409 }
410
411 }//manual
412 }
413 //}
414}
415
416function check_col3() {
417 var ss = SpreadsheetApp.getActiveSheet();
418 var sheet = ss.getDataRange().getValues();
419 ss.getRange(1, 1, ss.getMaxRows(),ss.getMaxColumns()).setNumberFormat('@STRING@');
420 var ui = SpreadsheetApp.getUi();
421 var response = ui.alert('Sunteti sigur ca doriti sa continuati?',ui.ButtonSet.YES_NO);
422 if (response == ui.Button.YES) {
423 //for (var i = 3; i < sheet.length * 100; i++){
424 var cols = ss.getLastColumn();
425 var rows = ss.getLastRow();
426 var i=cols-1;
427 //var meetCode = getCleanCode(sheet[2][i]);
428
429 var tip_prez = sheet[0][i];
430 //if(tip_prez=='Manual'){
431
432 // No Meet code given
433 ss.insertColumnAfter(i+1);
434 var cell = ss.getRange(1, i+2,rows);
435 cell.clearFormat();
436 var cell = ss.getRange(1, i+2);
437 var values = ['Manual','MeetMin','MeetPrezent'];
438 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
439 cell.offset(0, 0).setDataValidation(validationRule);
440 cell.setValue("Manual");
441 i++;
442 var cell = ss.getRange(3, i);
443 var culoare=cell.getBackground();
444 if(culoare=='#ccffff') {culoare='#c4ffa8';}
445 else if(culoare!='#ccffff') {culoare='#ccffff';}
446
447 add_validation(i+1,culoare);
448 //}
449 }
450}
451
452
453function teza() {
454 var ss = SpreadsheetApp.getActiveSheet();
455 var sheet = ss.getDataRange().getValues();
456 ss.getRange(1, 1, ss.getMaxRows(),ss.getMaxColumns()).setNumberFormat('@STRING@');
457 var ui = SpreadsheetApp.getUi();
458 var response = ui.alert('Sunteti sigur ca doriti sa inserati teza?',ui.ButtonSet.YES_NO);
459 if (response == ui.Button.YES) {
460 //for (var i = 3; i < sheet.length * 100; i++){
461 var cols = ss.getLastColumn();
462 var rows = ss.getLastRow();
463 var i=cols-1;
464 //var meetCode = getCleanCode(sheet[2][i]);
465
466 var tip_prez = sheet[0][i];
467 //if(tip_prez=='Manual'){
468
469
470 // No Meet code given
471 ss.insertColumnAfter(i+1);
472 var cell = ss.getRange(1, i+2,rows);
473 cell.clearFormat();
474 var cell = ss.getRange(1, i+2);
475 var values = ['Manual','MeetMin','MeetPrezent'];
476 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
477 cell.offset(0, 0).setDataValidation(validationRule);
478 cell.setValue("Manual");
479
480 //var cell = ss.getRange(2, i+2,rows);
481 //var validationRule = null;
482 //cell.setDataValidation(validationRule);
483
484 i++;
485 var cell = ss.getRange(2, i+1);
486 var values = ['Teza_sem_1','Teza_sem_2'];
487 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
488 cell.setDataValidation(validationRule);
489 cell.setValue('Teza_sem_1');
490
491 var culoare='#ffff00';
492 /*var cell = ss.getRange(3, i);
493 var culoare=cell.getBackground();
494 if(culoare=='#ccffff') {culoare='#c4ffa8';}
495 else if(culoare!='#ccffff') {culoare='#ccffff';}
496 */
497 add_validation(i+1,culoare);
498 //}
499 }
500}
501
502
503function sem2() {
504 var ss = SpreadsheetApp.getActiveSheet();
505 var sheet = ss.getDataRange().getValues();
506 ss.getRange(1, 1, ss.getMaxRows(),ss.getMaxColumns()).setNumberFormat('@STRING@');
507 var ui = SpreadsheetApp.getUi();
508 var response = ui.alert('Sunteti sigur ca doriti sa inserati semestrul2 ?',ui.ButtonSet.YES_NO);
509 if (response == ui.Button.YES) {
510 //for (var i = 3; i < sheet.length * 100; i++){
511 var cols = ss.getLastColumn();
512 var rows = ss.getLastRow();
513 var i=cols-1;
514 //var meetCode = getCleanCode(sheet[2][i]);
515
516 var tip_prez = sheet[0][i];
517 //if(tip_prez=='Manual'){
518
519
520 // No Meet code given
521 ss.insertColumnAfter(i+1);
522 var cell = ss.getRange(1, i+2,rows);
523 cell.clearFormat();
524 var cell = ss.getRange(1, i+2);
525 var values = ['Manual','MeetMin','MeetPrezent'];
526 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
527 cell.offset(0, 0).setDataValidation(validationRule);
528 cell.setValue("Manual");
529
530 var culoare='#ff0000';
531 var cell = ss.getRange(2, i+2,rows-1);
532 var validationRule = null;
533 cell.setDataValidation(validationRule);
534 cell.setBackground(culoare);
535 i++;
536 var cell = ss.getRange(2, i+1);
537 cell.setValue('Semestrul_2');
538
539 var cell = ss.getRange(1, i+1,3);
540 cell.setBackground(culoare);
541
542 var col_sem2=columnToLetter(i+1);
543 var cell = ss.getRange(1,1);
544 cell.setValue('Sem 2 incepe de la coloana ('+col_sem2+')');
545
546 }
547}
548
549
550function add_validation(index,culoare){
551 var ss = SpreadsheetApp.getActiveSheet();
552 var sheet = ss.getDataRange().getValues();
553 var tip_prez = sheet[0][index-1];
554 for (var i = 2; i < sheet.length-1; i++) {
555 if(tip_prez=='Manual'){
556 var cell = ss.getRange(3, index);
557 var date = Utilities.formatDate(new Date(), "GMT+2", "dd-MM-yyyy HH:mm:ss")
558 date="'"+date;
559 cell.setValue(date);
560 cell.setBackground(culoare);
561 var cell = ss.getRange(i+2, index);
562 var values = ['Prezent','Absent','Motivat','Absent_M','1','2','3','4','5','6','7','8','9','10'];
563 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
564 cell.offset(0, 0).setDataValidation(validationRule);
565 cell.setValue("Prezent");
566 cell.setBackground(culoare);
567 }
568 }
569}
570
571function add_validation2(index, culoare){
572 var ss = SpreadsheetApp.getActiveSheet();
573 var sheet = ss.getDataRange().getValues();
574 var tip_prez = sheet[0][index-1];
575 for (var i = 2; i < sheet.length-1; i++) {
576
577 //var cell = ss.getRange(2, index);
578 //var date = Utilities.formatDate(new Date(), "GMT+2", "dd-MM-yyyy")
579 //cell.setValue(date);
580 var cell = ss.getRange(i+2, index);
581 var values = ['Prezent','Absent','Motivat','Absent_M','1','2','3','4','5','6','7','8','9','10'];
582 var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(values).build();
583 cell.offset(0, 0).setDataValidation(validationRule);
584 //cell.setValue("Prezent");
585 cell.setValue("Absent");
586 cell.setBackground(culoare);
587
588 }
589}
590
591function remove_validation(index){
592 var ss = SpreadsheetApp.getActiveSheet();
593 var sheet = ss.getDataRange().getValues();
594 var tip_prez = sheet[0][index-1];
595 for (var i = 2; i < sheet.length-1; i++) {
596 if(tip_prez!='Manual'){
597 var cell = ss.getRange(i+2, index);
598 var validationRule = null;
599 cell.setDataValidation(validationRule);
600 //var rule = cell.getDataValidation();
601 //Logger.log('rule= %s',rule);
602 }
603 }
604}
605/*
606 Description: Checks the Meet for attendance of the given student
607 @param {String} meetCode - Raw Meet Code from Course Sheet
608 @param {Integer} index - Index corresponding to the Student's row
609 in the Course Sheet
610*/
611function checkMeet(meetCode, index) {
612 // universal settings - static
613 var userKey = 'all';
614 var applicationName = 'meet';
615 var ss = SpreadsheetApp.getActiveSheet();
616 var sheet = ss.getDataRange().getValues();
617 var tip_prez = sheet[0][index-1];
618 //Logger.log('%s',meetCode);
619 //if()
620 for (var i = 2; i < sheet.length-1; i++) {
621 var emailAddress = sheet[i+1][1]
622 var optionalArgs = {
623 event_name: "call_ended",
624 filters: "identifier==" + emailAddress + ",meeting_code==" + meetCode
625 };
626 try {Logger.log('%s',emailAddress);
627 var response = AdminReports.Activities.list(userKey, applicationName, optionalArgs);
628 var activities = response.items;
629 //Logger.log('rasp %s act=%s',response,activities);
630 if (activities == null) {
631 markAbsent(ss,i+2,index)
632 }
633 else {
634
635 var conf_mail='';
636 var time_value=0;
637 var timp = response.items[0].id.time;
638 var date=timp.split('T')[0];
639 var date2=timp.split('T')[1];
640 var date3=date.split('-');
641 var date4=date3[2]+'-'+date3[1]+'-'+date3[0];
642 //var date1234 = Utilities.formatDate(new Date(), "GMT+2", "HH:mm:ss")
643 date4="'"+date4+" "+meetCode;
644 Logger.log('timp11=%s',date4);
645
646 var timp2 = response.items[0].events[0].parameters;
647 for(var h=0;h<timp2.length;h++){
648 if(timp2[h].name=='organizer_email')
649 {Logger.log('h=%s mail=%s',h,timp2[h].value);
650 conf_mail=timp2[h].value;
651 }
652 if(timp2[h].name=='duration_seconds')
653 {Logger.log('h=%s sec=%s',h,timp2[h].intValue);
654 time_value=timp2[h].intValue/60;
655 time_value=time_value.toString().split('.')[0];
656 time_value=time_value+' min '+date2;
657 }
658 }//for
659
660 var cell = ss.getRange(1, index+1);
661 cell.setValue(conf_mail);
662 var cell = ss.getRange(3, index+1);
663 cell.setValue(date4);
664
665 Logger.log('tip_prez123=%s',tip_prez);
666 if(tip_prez=='MeetPrezent'){time_value='Prezent_A';}
667 markPresent(ss,i+2,index,time_value);
668 markPresent2(ss,i+2,index+1,'Prezent');
669
670 }//else
671 } catch (err) {Logger.log('eroarea este: %s', err.message);
672 continue
673 }
674 }
675}
676
677function test_checkMeet(){
678var meetCode = getCleanCode('ivn-onvh-fgt');
679
680
681}
682
683function updateCourses(){
684var ss = SpreadsheetApp.getActiveSheet();
685 var sheet = ss.getDataRange().getValues();
686 ss.getRange(1, 1, ss.getMaxRows(),ss.getMaxColumns()).setNumberFormat('@STRING@');
687
688
689 //Logger.log('%s',meetCode);
690 //if()
691 for (var i = 2; i < sheet.length-1; i++) {
692 var emailAddress = sheet[i+1][1]
693 try{
694 var user3=AdminDirectory.Users.get(emailAddress);
695 var cls_path=user3.orgUnitPath.substring(1);
696 //var cls_path='Elevi/Liceal/Clasa a XI-a A';
697 var customerId=user3.customerId;
698 var page;
699 page = AdminDirectory.Orgunits.get(customerId, cls_path)
700
701 var cls = page.description
702 //cls=cls.match(/\[(.*?)\]/);
703 cls=cls.match(/\((.*?)\@/);
704 if (cls) {
705 var cls2=cls[1];
706 cls2=cls2.substring(5);
707 var cell = ss.getRange(i+2, 3);
708 cell.setValue(cls2);
709 Logger.log('email:%s cls=%s',emailAddress,cls2);
710
711 }
712
713
714
715 //Logger.log('%s',emailAddress);
716
717 }catch(e){
718 Logger.log('Eroare adaugare individual elev %s: %s %s', emailAddress,cls_path, e.message);
719 }
720
721
722 }
723}
724
725
726
727/*
728 Description: Strips any "-' Characters to match needed format
729 for Reports API
730 @param {String} meetCode - Raw Meet Code from Course Sheet
731*/
732function getCleanCode(meetCode) {
733 try{
734 return meetCode.replace(/-/g,"")
735 } catch (err) { return meetCode; }
736}
737
738/*
739 Description: Marks the student as absent for their corresponding cell
740 @param {Object} sheet - Course Sheet object
741 @param {Integer} i - Index of Sheet cell column to be filled
742 @param {Integer} j - Index of Sheet cell row to be filled
743*/
744function markAbsent(sheet, i, j) {
745
746 var cell = sheet.getRange(i, j-1);
747 var culoare=cell.getBackground();
748 if(culoare=='#ffffff') {culoare='#d1d1d1';}
749 else if(culoare!='#ffffff') {culoare='#ffffff';}
750 var cell = sheet.getRange(i, j);
751 cell.setValue("Absent_A");
752 cell.setBackground(culoare);
753
754}
755
756/*
757 Description: Marks the student as absent for their corresponding cell
758 @param {Object} sheet - Course Sheet object
759 @param {Integer} i - Index of Sheet cell column to be filled
760 @param {Integer} j - Index of Sheet cell row to be filled
761*/
762function markPresent(sheet, i, j,value) {
763 var cell = sheet.getRange(i, j-1);
764 var culoare=cell.getBackground();
765 if(culoare=='#ffffff') {culoare='#d1d1d1';}
766 else if(culoare!='#ffffff') {culoare='#ffffff';}
767 var cell = sheet.getRange(i, j);
768 cell.setValue(value);
769 cell.setBackground(culoare);
770}
771
772function markPresent2(sheet, i, j,value) {
773
774
775 var cell = sheet.getRange(i, j);
776 //cell.setValue("Present");
777 var tip=cell.getValue(); //'Motivat','Prezent_M','Absent_M'
778 if(tip=='Absent')
779 {cell.setValue(value);}
780}
781
782///sql server
783var server = 'azure.lmvineu.ro';
784var port = 3306;
785var dbName = 'prezenta';
786var username = 'adminprez';
787var password = 'aQ8MRS5vczDP4VJKTV!';
788var encode='?useUnicode=yes&characterEncoding=UTF-8';
789var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName+encode;
790//jdbc:mysql://localhost:3306/?useUnicode=yes&characterEncoding=UTF-8
791//*/
792/**
793 * Wrapper for Spreadsheet.insertSheet() method to support customization.
794 * All parameters are optional & positional.
795 *
796 * @param {String} sheetName Name of new sheet (defaults to "Sheet #")
797 * @param {Number} sheetIndex Position for new sheet (default 0 means "end")
798 * @param {Number} rows Vertical dimension of new sheet (default 0 means "system default", 1000)
799 * @param {Number} columns Horizontal dimension of new sheet (default 0 means "system default", 26)
800 * @param {String} template Name of existing sheet to copy (default "" means none)
801 *
802 * @returns {Sheet} Sheet object for chaining.
803 */
804function insertSheet( sheetName, sheetIndex, rows, columns, template ) {
805
806 // Check parameters, set defaults
807 var ss = SpreadsheetApp.getActive();
808 var numSheets = ss.getSheets().length;
809 sheetIndex = sheetIndex || (numSheets + 1);
810 sheetName = sheetName || "Sheet " + sheetIndex;
811 var options = template ? { 'template' : ss.getSheetByName(template) } : {};
812
813 // Will throw an exception if sheetName already exists
814 var newSheet = ss.insertSheet(sheetName, sheetIndex, options);
815
816 if (rows !== 0) {
817 // Adjust dimension: rows
818 var newSheetRows = newSheet.getMaxRows();
819
820 if (rows < newSheetRows) {
821 // trim rows
822 newSheet.deleteRows(rows+1, newSheetRows-rows);
823 }
824 else if (rows > newSheetRows) {
825 // add rows
826 newSheet.insertRowsAfter(newSheetRows, rows-newSheetRows);
827 }
828 }
829
830 if (columns !== 0) {
831 // Adjust dimension: columns
832 var newSheetColumns = newSheet.getMaxColumns();
833
834 if (columns < newSheetColumns) {
835 // trim rows
836 newSheet.deleteColumns(columns+1, newSheetColumns-columns);
837 }
838 else if (columns > newSheetColumns) {
839 // add rows
840 newSheet.insertColumnsAfter(newSheetColumns,columns-newSheetColumns);
841 }
842 }
843
844 // Return new Sheet object
845 return newSheet;
846}
847
848
849
850
851
852function trimite_la_mysql() {
853 var start = new Date();
854 var spreadsheetName = "main"
855 var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
856 var ss1 = activeSpreadsheet.getSheetByName(spreadsheetName);
857 var ss = SpreadsheetApp.getActiveSheet();
858 var sheet = ss.getDataRange().getValues();
859
860 var nume_curs=ss.getName();
861 var matches=nume_curs.match(/\((.*?)\)/);
862 //var matches = mystring.match(/\[(.*?)\]/); //Some text with [some important info]
863 if (matches) {
864 var curs_id= matches[1];
865 }
866 var nume_curs=nume_curs.replace('('+curs_id+')','');
867 var mail_prof=ss1.getRange("A1").getValue();
868
869 var cols=ss.getLastColumn();
870 var rows=ss.getLastRow();
871
872
873 var col_sem2=sheet[0][0];
874 var matches=col_sem2.match(/\((.*?)\)/);
875 //var matches = mystring.match(/\[(.*?)\]/); //Some text with [some important info]
876 if (matches) {
877 var col_sem2= matches[1];
878 }
879 var col_sem2_nr=letterToColumn(col_sem2);
880 var start=3;
881 var stop=cols;
882 var selectie=sheet[1][0];
883 if(selectie=="Sem_1")
884 {start=3;stop=col_sem2_nr;}
885 if(selectie=="Sem_2")
886 {start=col_sem2_nr;stop=cols;}
887
888
889 var conn = Jdbc.getConnection(url, username, password);
890 var stmt = conn.prepareStatement('SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci');
891 stmt.execute();
892
893
894
895 //var sql='INSERT INTO absente (id_abs, nume_elev) values ("ceva123", "'+sheet[3][0]+'") ON DUPLICATE KEY UPDATE id_abs="ceva123",nume_elev="'+sheet[3][0]+'"';
896 //var sql='INSERT INTO absente (id_abs, nume_elev) values ("ceva123", "'+sheet[3][0]+'") ON DUPLICATE KEY UPDATE id_abs="ceva123",nume_elev=values(nume_elev)';
897 //Logger.log("nume_m=%s sql=%s",sheet[3][0],sql);
898 //var stmt = conn.prepareStatement(sql);
899 //stmt.execute();
900 conn.setAutoCommit(false);
901
902 //id, id_abs, mail, nume_elev, curs_nume, curs_id, mail_prof, danu, data_abs, absenta, meetcode, timp,
903
904
905 //id_abs mail nume_elev curs_nume curs_id mail_prof data_abs data_update absenta meetcode timp update_date
906
907
908 /*
909 var stmt = conn.prepareStatement('INSERT INTO absente '
910 + '(id_abs, mail, nume_elev, curs_nume, curs_id, mail_prof, data_abs, data_update, absenta, meetcode, timp) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE timp=values(timp), meetcode=values(meetcode), absenta=values(absenta), data_update=values(data_update), data_abs=values(data_abs), nume_elev=values(nume_elev)');
911 */
912
913 var n=10;
914 var k=0;
915 //cols=7;
916 for(var j=3;j<cols;j++)
917 {
918
919 var data1=sheet[1][j];
920 var data2=sheet[1][j+1];
921 var data3=sheet[1][j-1];
922
923 if(data1=='')
924 {data1='ceva1234';}
925
926 if(data1==data2 )
927 {
928 var stmt = conn.prepareStatement('INSERT INTO absente '
929 + '(id_abs, mail, nume_elev, curs_nume, curs_id, mail_prof, data_abs, data_update, absenta, meetcode, timp) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE timp=values(timp), meetcode=values(meetcode), absenta=values(absenta), data_update=values(data_update), data_abs=values(data_abs), nume_elev=values(nume_elev)');
930
931 var meetcode=sheet[2][j];
932 var data_update=sql_date(sheet[1][j],1);
933 var data_abs=sheet[2][j+1];
934 var luna=data_abs.toString().substring(2,6);
935 data_abs=data_abs.toString().substring(0,10);
936 var data_abs=sql_date(data_abs,2);
937 Logger.log("data abs=%s, update=%s, meet=%s luna=%s",data_abs,data_update,meetcode,luna);
938
939
940
941
942 for(i=3;i<rows;i++)
943 {
944 var nume=sheet[i][0];
945 var mail=sheet[i][1];
946 var timp=sheet[i][j];
947 var prezenta=sheet[i][j+1];
948
949 var id_abs=mail+curs_id+mail_prof+meetcode;
950 stmt.setString(1, id_abs);
951 stmt.setString(2, mail);
952 stmt.setString(3, nume);
953 stmt.setString(4, nume_curs);
954 stmt.setString(5, curs_id);
955 stmt.setString(6, mail_prof);
956 stmt.setString(7, data_abs);
957 stmt.setString(8, data_update);
958 stmt.setString(9, prezenta);
959 stmt.setString(10, meetcode);
960 stmt.setString(11, timp);
961 //stmt.set
962 stmt.addBatch();
963
964
965 Logger.log("nume=%s, mail=%s, timp=%s, Pre=%s",nume,mail,timp,prezenta);
966
967
968 // Logger.log("mail_prof=%s, nume_curs=%s, curs_id=%s, data=%s, rand=%s, cols=%s", mail_prof,nume_curs,curs_id,data,rows,cols);
969
970 k++;
971 if(k>n)
972 {
973 k=0;
974 stmt.setQueryTimeout(0);
975 var batch = stmt.executeBatch();
976 conn.commit();
977 }
978
979
980
981 }
982 stmt.setQueryTimeout(0);
983 var batch = stmt.executeBatch();
984 conn.commit();
985
986 j++;
987
988 }else
989 if(isdate2(sheet[2][j]))
990 {
991 var stmt = conn.prepareStatement('INSERT INTO absente '
992 + '(id_abs, mail, nume_elev, curs_nume, curs_id, mail_prof, data_abs, data_update, absenta, meetcode, timp) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE timp=values(timp), meetcode=values(meetcode), absenta=values(absenta), data_update=values(data_update), data_abs=values(data_abs), nume_elev=values(nume_elev)');
993
994 var meetcode='manual';//teza
995 var teza=sheet[1][j];
996 if(teza=='Teza_sem_1' || teza=='Teza_sem_2')
997 {meetcode=teza;}
998 var data_abs=sheet[2][j];
999 var data_abs=sql_date(data_abs,1);
1000
1001 var timp='Nu exista';//nota
1002 //data_abs=data_abs.toString().substring(0,10);
1003 Logger.log("data abs_m=%s",data_abs);
1004
1005 for(i=3;i<rows;i++)
1006 {
1007 var nume=sheet[i][0];
1008 var mail=sheet[i][1];
1009 var prezenta=sheet[i][j];
1010
1011 Logger.log("nume_m=%s, mail_m=%s,Pre_m=%s",nume,mail,prezenta);
1012
1013 if(meetcode==teza)
1014 {var id_abs=mail+curs_id+mail_prof+teza;}
1015 else{var id_abs=mail+curs_id+mail_prof+data_abs;}
1016
1017 stmt.setString(1, id_abs);
1018 stmt.setString(2, mail);
1019 stmt.setString(3, nume);
1020 stmt.setString(4, nume_curs);
1021 stmt.setString(5, curs_id);
1022 stmt.setString(6, mail_prof);
1023 stmt.setString(7, data_abs);
1024 stmt.setString(8, data_abs);
1025 stmt.setString(9, prezenta);
1026 stmt.setString(10, meetcode);
1027 stmt.setString(11, timp);
1028 stmt.addBatch();
1029
1030 // Logger.log("mail_prof=%s, nume_curs=%s, curs_id=%s, data=%s, rand=%s, cols=%s", mail_prof,nume_curs,curs_id,data,rows,cols);
1031
1032 k++;
1033 if(k>n)
1034 {
1035 k=0;
1036 stmt.setQueryTimeout(0);
1037 var batch = stmt.executeBatch();
1038 conn.commit();
1039 }
1040
1041
1042
1043 }
1044
1045 stmt.setQueryTimeout(0);
1046 var batch = stmt.executeBatch();
1047 conn.commit();
1048
1049 }
1050
1051
1052 }
1053
1054
1055
1056
1057
1058 //stmt.setQueryTimeout(0);
1059 //var batch = stmt.executeBatch();
1060 //conn.commit();
1061 conn.close();
1062
1063 var end = new Date();
1064 Logger.log('Time elapsed: %sms for %s rows.', end - start, batch.length);
1065 //Logger.log('Time elapsed: %sms.', end - start);
1066}
1067
1068
1069function trimite_la_mysql_last_6() {
1070 var start = new Date();
1071 //var spreadsheetName = "12E Modul 4(176830920569)"
1072 //var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
1073 //var ss5 = activeSpreadsheet.getSheetByName(spreadsheetName);
1074 //ss5.activate();
1075
1076 var spreadsheetName = "main"
1077 var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
1078 var ss1 = activeSpreadsheet.getSheetByName(spreadsheetName);
1079 var ss = SpreadsheetApp.getActiveSheet();
1080 var sheet = ss.getDataRange().getValues();
1081
1082 var nume_curs=ss.getName();
1083 var matches=nume_curs.match(/\((.*?)\)/);
1084 //var matches = mystring.match(/\[(.*?)\]/); //Some text with [some important info]
1085 if (matches) {
1086 var curs_id= matches[1];
1087 }
1088 var nume_curs=nume_curs.replace('('+curs_id+')','');
1089 var mail_prof=ss1.getRange("A1").getValue();
1090
1091 var cols=ss.getLastColumn();
1092 var rows=ss.getLastRow();
1093
1094
1095
1096 var conn = Jdbc.getConnection(url, username, password);
1097 var stmt = conn.prepareStatement('SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci');
1098 stmt.execute();
1099
1100
1101
1102 //var sql='INSERT INTO absente (id_abs, nume_elev) values ("ceva123", "'+sheet[3][0]+'") ON DUPLICATE KEY UPDATE id_abs="ceva123",nume_elev="'+sheet[3][0]+'"';
1103 //var sql='INSERT INTO absente (id_abs, nume_elev) values ("ceva123", "'+sheet[3][0]+'") ON DUPLICATE KEY UPDATE id_abs="ceva123",nume_elev=values(nume_elev)';
1104 //Logger.log("nume_m=%s sql=%s",sheet[3][0],sql);
1105 //var stmt = conn.prepareStatement(sql);
1106 //stmt.execute();
1107 conn.setAutoCommit(false);
1108
1109 //id, id_abs, mail, nume_elev, curs_nume, curs_id, mail_prof, danu, data_abs, absenta, meetcode, timp,
1110
1111
1112 //id_abs mail nume_elev curs_nume curs_id mail_prof data_abs data_update absenta meetcode timp update_date
1113
1114
1115 /*
1116 var stmt = conn.prepareStatement('INSERT INTO absente '
1117 + '(id_abs, mail, nume_elev, curs_nume, curs_id, mail_prof, data_abs, data_update, absenta, meetcode, timp) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE timp=values(timp), meetcode=values(meetcode), absenta=values(absenta), data_update=values(data_update), data_abs=values(data_abs), nume_elev=values(nume_elev)');
1118 */
1119 Logger.log("cols%s",cols);
1120 //cols=7;
1121 for(j=cols-6;j<cols;j++)
1122 {
1123
1124 var data1=sheet[1][j];
1125 var data2=sheet[1][j+1];
1126 var data3=sheet[1][j-1];
1127
1128 if(data1=='')
1129 {data1='ceva1234';}
1130
1131 if(data1==data2 )
1132 {
1133 var stmt = conn.prepareStatement('INSERT INTO absente '
1134 + '(id_abs, mail, nume_elev, curs_nume, curs_id, mail_prof, data_abs, data_update, absenta, meetcode, timp) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE timp=values(timp), meetcode=values(meetcode), absenta=values(absenta), data_update=values(data_update), data_abs=values(data_abs), nume_elev=values(nume_elev)');
1135
1136 var meetcode=sheet[2][j];
1137 var data_update=sql_date(sheet[1][j],1);
1138 var data_abs=sheet[2][j+1];
1139 var luna=data_abs.toString().substring(2,6);
1140 data_abs=data_abs.toString().substring(0,10);
1141 var data_abs=sql_date(data_abs,2);
1142 Logger.log("data abs=%s, update=%s, meet=%s luna=%s",data_abs,data_update,meetcode,luna);
1143
1144
1145
1146
1147 for(i=3;i<rows;i++)
1148 {
1149 var nume=sheet[i][0];
1150 var mail=sheet[i][1];
1151 var timp=sheet[i][j];
1152 var prezenta=sheet[i][j+1];
1153
1154 var id_abs=mail+curs_id+mail_prof+meetcode;
1155 stmt.setString(1, id_abs);
1156 stmt.setString(2, mail);
1157 stmt.setString(3, nume);
1158 stmt.setString(4, nume_curs);
1159 stmt.setString(5, curs_id);
1160 stmt.setString(6, mail_prof);
1161 stmt.setString(7, data_abs);
1162 stmt.setString(8, data_update);
1163 stmt.setString(9, prezenta);
1164 stmt.setString(10, meetcode);
1165 stmt.setString(11, timp);
1166 //stmt.set
1167 stmt.addBatch();
1168
1169
1170
1171
1172
1173 //Logger.log("nume=%s, mail=%s, timp=%s, Pre=%s",nume,mail,timp,prezenta);
1174
1175
1176 // Logger.log("mail_prof=%s, nume_curs=%s, curs_id=%s, data=%s, rand=%s, cols=%s", mail_prof,nume_curs,curs_id,data,rows,cols);
1177
1178 }
1179 stmt.setQueryTimeout(0);
1180 var batch = stmt.executeBatch();
1181 conn.commit();
1182
1183 j++;
1184
1185 }else
1186 {Logger.log("o coloana j=%s data=%s",j,sheet[2][j]);
1187 if(isdate2(sheet[2][j]))
1188 {
1189 var stmt = conn.prepareStatement('INSERT INTO absente '
1190 + '(id_abs, mail, nume_elev, curs_nume, curs_id, mail_prof, data_abs, data_update, absenta, meetcode, timp) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE timp=values(timp), meetcode=values(meetcode), absenta=values(absenta), data_update=values(data_update), data_abs=values(data_abs), nume_elev=values(nume_elev)');
1191
1192 var meetcode='manual';//teza
1193 var teza=sheet[1][j];
1194 if(teza=='Teza_sem_1' || teza=='Teza_sem_2')
1195 {meetcode=teza;}
1196 var data_abs=sheet[2][j];
1197 var data_abs=sql_date(data_abs,1);
1198
1199 var timp='Nu exista';//nota
1200 //data_abs=data_abs.toString().substring(0,10);
1201 Logger.log("data abs_m=%s",data_abs);
1202
1203 for(i=3;i<rows;i++)
1204 {
1205 var nume=sheet[i][0];
1206 var mail=sheet[i][1];
1207 var prezenta=sheet[i][j];
1208
1209 Logger.log("nume_m=%s, mail_m=%s,Pre_m=%s",nume,mail,prezenta);
1210
1211 if(meetcode==teza)
1212 {var id_abs=mail+curs_id+mail_prof+teza;}
1213 else{var id_abs=mail+curs_id+mail_prof+data_abs;}
1214
1215 stmt.setString(1, id_abs);
1216 stmt.setString(2, mail);
1217 stmt.setString(3, nume);
1218 stmt.setString(4, nume_curs);
1219 stmt.setString(5, curs_id);
1220 stmt.setString(6, mail_prof);
1221 stmt.setString(7, data_abs);
1222 stmt.setString(8, data_abs);
1223 stmt.setString(9, prezenta);
1224 stmt.setString(10, meetcode);
1225 stmt.setString(11, timp);
1226 stmt.addBatch();
1227
1228 // Logger.log("mail_prof=%s, nume_curs=%s, curs_id=%s, data=%s, rand=%s, cols=%s", mail_prof,nume_curs,curs_id,data,rows,cols);
1229
1230 }
1231
1232 stmt.setQueryTimeout(0);
1233 var batch = stmt.executeBatch();
1234 conn.commit();
1235
1236 }
1237 }
1238
1239
1240 }
1241
1242
1243 //stmt.setQueryTimeout(0);
1244 //var batch = stmt.executeBatch();
1245 //conn.commit();
1246 conn.close();
1247
1248 var end = new Date();
1249 Logger.log('Time elapsed: %sms for %s rows.', end - start, batch.length);
1250 //Logger.log('Time elapsed: %sms.', end - start);
1251}
1252
1253function writeOneRecord() {
1254 var conn = Jdbc.getConnection(url, username, password);
1255
1256 var stmt = conn.prepareStatement('INSERT INTO absente '
1257 + '(data_abs, nume_elev) values (?, ?)');
1258 stmt.setString(1, '2021-01-16 14:41:15');
1259 stmt.setString(2, 'Popescu2');
1260 //Logger.log("query:",stmt.getWarnings());
1261 stmt.execute();
1262 //conn.close();
1263}
1264
1265function isdate(data){
1266eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
1267data=data.toString().trim().substring(0,10);
1268 var data_test=moment(data, "DD-MM-YYYY", true).isValid();
1269 return data_test;
1270}
1271
1272
1273function isdate2(data){
1274eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
1275data=data.toString().trim();
1276 var data_test=moment(data, "DD-MM-YYYY HH:mm:ss", true).isValid();
1277 return data_test;
1278}
1279
1280function sql_date(date, opt)
1281{eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
1282
1283 const format1 = "YYYY-MM-DD HH:mm:ss"
1284 date=date.toString().trim();
1285 if(opt==1)
1286 {
1287 var data_test=moment(date, "DD-MM-YYYY HH:mm:ss", true).format(format1);
1288 }
1289 else if(opt==2)
1290 {
1291 var data_test=moment(date, "DD-MM-YYYY", true).format(format1);
1292 }
1293
1294 //Logger.log("data=%s, data_test=%s", dateTime1,data_test);
1295 return data_test;
1296
1297}
1298
1299
1300function test_isdate2(){
1301 var data='17-12-2020 14:59:42';
1302eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.18.1/moment.min.js').getContentText());
1303data=data.toString().trim();
1304 var data_test=moment(data, "DD-MM-YYYY HH:mm:ss", true).isValid();
1305 Logger.log("datares=%s",data_test);
1306}
1307function calc_medie(){
1308
1309var ss = SpreadsheetApp.getActiveSheet();
1310 var sheet = ss.getDataRange().getValues();
1311
1312 var nume_curs=ss.getName();
1313 var matches=nume_curs.match(/\((.*?)\)/);
1314 //var matches = mystring.match(/\[(.*?)\]/); //Some text with [some important info]
1315 if (matches) {
1316 var curs_id= matches[1];
1317 }
1318 var nume_curs=nume_curs.replace('('+curs_id+')','');
1319
1320
1321 var cols=ss.getLastColumn();
1322 var rows=ss.getLastRow();
1323 var col_sem2=sheet[0][0];
1324 var matches=col_sem2.match(/\((.*?)\)/);
1325 //var matches = mystring.match(/\[(.*?)\]/); //Some text with [some important info]
1326 if (matches) {
1327 var col_sem2= matches[1];
1328 }
1329 var col_sem2_nr=letterToColumn(col_sem2);
1330 var start=3;
1331 var stop=cols;
1332 var selectie=sheet[1][0];
1333 if(selectie=="Sem_1")
1334 {start=3;stop=col_sem2_nr;}
1335 if(selectie=="Sem_2")
1336 {start=col_sem2_nr;stop=cols;}
1337
1338var html='<p><i>Med</i> - media notelor pe semestrul selectat</p>';
1339html=html+'<p><i>T</i> - teza pe semestrul selectat</p>';
1340html=html+'<p><i>Med_F</i> - media finala (cu teza daca exista)</p>';
1341for(i=3;i<rows;i++)
1342{
1343 var mail=sheet[i][1];
1344 var nume=sheet[i][0];
1345 var sum=0;
1346 var k=0;
1347 var nota_t=0;
1348 for(j=start;j<stop;j++)
1349 {
1350 var teza=sheet[1][j];
1351 var nota=sheet[i][j];
1352 //nota=parseInt(nota, 10);
1353 nota=Number(nota);
1354
1355
1356 if(nota)
1357 {
1358 if(teza=='Teza_sem_1' || teza=='Teza_sem_2')
1359 {nota_t=nota;}
1360 else
1361 {
1362 sum=sum+nota;
1363 k++;
1364 }
1365 }
1366
1367
1368 }
1369 if(nota_t!=0)
1370 {var nota_f=((sum/k)*3+nota_t)/4;}
1371 else {var nota_f=sum/k;}
1372
1373 if(selectie=='Tot')
1374 {nota_f=sum/k;nota_t='imposibil'}
1375
1376
1377 html=html+'<p>'+nume+' <b>Med=</b>'+sum/k+', <b>T=</b>'+nota_t+', <b>Med_F=</b>'+nota_f+'</p>';
1378
1379}
1380
1381
1382// Display a sidebar with custom HtmlService content.
1383var htmlOutput = HtmlService
1384 //.createHtmlOutput('<p>A change of speed, a change of style...</p><br>ceva<br>ceva2<br>')
1385 .createHtmlOutput(html)
1386 .setTitle(nume_curs);
1387SpreadsheetApp.getUi().showSidebar(htmlOutput);
1388}
1389
1390
1391function columnToLetter(column)
1392{
1393 var temp, letter = '';
1394 while (column > 0)
1395 {
1396 temp = (column - 1) % 26;
1397 letter = String.fromCharCode(temp + 65) + letter;
1398 column = (column - temp - 1) / 26;
1399 }
1400 return letter;
1401}
1402
1403function letterToColumn(letter)
1404{
1405 var column = 0, length = letter.length;
1406 for (var i = 0; i < length; i++)
1407 {
1408 column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
1409 }
1410 return column;
1411}
1412
1413