How to use getRange method in Cypress

Best JavaScript code snippet using cypress

script.js

Source:script.js Github

copy

Full Screen

...32}33function rewrite(module_number) {34 var ss = SpreadsheetApp.getActiveSpreadsheet();35 var send_sheet = ss.getSheetByName("대기열");36 send_sheet.getRange('A:J').offset(1, 0, send_sheet.getRange('A:J').getNumRows() - 1).sort([{column: 1, ascending: true}]);37 var range2 = send_sheet.getDataRange();38 var last_row = range2.getLastRow();39 var list_module = [];40 for(var i of [module_number]){41 var range2 = send_sheet.getDataRange();42 var last_row = range2.getLastRow();43 for(var j =2; j <= last_row; j++){44 45 if (send_sheet.getRange(j,1).getValue() === "모듈"+String(i)){46 list_module.push(j);47 }// if48 } //for j 49 50 if (list_module.length !== 0){51 var s = list_module[0]; 52 var e = list_module[list_module.length-1];53 send_sheet.deleteRows(s,e);54 }55 56 57 } //for i 58}59function module_1_rewrite() {60 rewrite(1);61 module_1();62 sort_and_get_todaylist();63}64function module_2_rewrite() {65 rewrite(2);66 module_2();67 sort_and_get_todaylist();68}69function module_3_rewrite() {70 rewrite(3);71 module_3();72 sort_and_get_todaylist();73}74function module_4_rewrite() {75 rewrite(4);76 module_4();77 sort_and_get_todaylist();78}79function module_5_rewrite() {80 rewrite(5);81 module_5();82 sort_and_get_todaylist();83}84function module_6_rewrite() {85 rewrite(6);86 module_6();87 sort_and_get_todaylist();88}89function module_7_rewrite() {90 rewrite(7);91 module_7();92 sort_and_get_todaylist();93}94function module_8_rewrite() {95 rewrite(8);96 module_8();97 sort_and_get_todaylist();98}99function clear_send(){100 var ss = SpreadsheetApp.getActiveSpreadsheet();101 var send_sheet = ss.getSheetByName("대기열");102 send_sheet.getRange('2:1000000').clear({contentsOnly: true});103}104function sort_and_get_todaylist(){105 var ss = SpreadsheetApp.getActiveSpreadsheet();106 var send_sheet = ss.getSheetByName("대기열");107 send_sheet.getRange('A:J').offset(1, 0, send_sheet.getRange('A:J').getNumRows() - 1).sort([{column: 7, ascending: true}, {column: 8, ascending: true}, {column: 9, ascending: true}, {column: 10, ascending: true}, {column: 1, ascending: true}, {column: 2, ascending: true}]);108 var current_date = new Date();109 var tommorrow = new Date();110 tommorrow.setDate(current_date.getDate()+1);111 var current_Year = current_date.getFullYear();112 var current_Month = current_date.getMonth()+1 ;113 var current_Day = current_date.getDate();114 var current_Hour = current_date.getHours();115 116 console.log(current_date)117 console.log(tommorrow)118 var range2 = send_sheet.getDataRange();119 var last_row = range2.getLastRow();120 console.log(last_row);121 var list_today = [];122 for(var i =2; i <= last_row; i++){123 124 if (send_sheet.getRange(i,7).getValue() === current_Year &&125 send_sheet.getRange(i,8).getValue() === current_Month &&126 send_sheet.getRange(i,9).getValue() === current_Day){127 list_today.push(i);128 }// if129 130 131 } // for i 132 if (list_today.length === 0){133 send_sheet.getRange('2:1000000').clear({contentsOnly: true});134 }135 136 else {137 try{138 var s = list_today[0]; 139 var e = list_today[list_today.length-1];140 send_sheet.deleteRows(e+1,last_row-e);141 send_sheet.deleteRows(2,s-2);142 }143 catch{ }144 }145 send_sheet.getRange('D:D').setNumberFormat('00000000000');146}147function data_set(module_number) {148 var ss = SpreadsheetApp.getActiveSpreadsheet();149 var name_sheet = ss.getSheetByName("명단");150 var module_sheet = ss.getSheetByName("모듈정보");151 var text_sheet = ss.getSheetByName("문자내용");152 var send_sheet = ss.getSheetByName("대기열");153 var sample_sheet = ss.getSheetByName("문자샘플");154 send_sheet.getRange('D:D').setNumberFormat('00000000000');155 for(var i of [module_number+3]){156 157 var module_indicator = []158 module_indicator.push(i-3)159 console.log(module_indicator)160 161 sample_sheet.getRange('C:C').clear({contentsOnly: true});162 sample_sheet.getRange('D:D').clear({contentsOnly: true});163 sample_sheet.getRange('E:E').clear({contentsOnly: true});164 sample_sheet.getRange('F:F').clear({contentsOnly: true});165 sample_sheet.getRange('G:G').clear({contentsOnly: true});166 167 var range = module_sheet.getRange(2,i-2);168 var date_start = new Date(range.getValue());169 var weekday = date_start.getDay(); 170 var module_name = module_sheet.getRange(3,i-2).getValue();171 var module_prof_name = module_sheet.getRange(4,i-2).getValue();172 var module_prof_phone = module_sheet.getRange(5,i-2).getValue();173 var module_prof_email = module_sheet.getRange(6,i-2).getValue();174 var module_admin_name = module_sheet.getRange(7,i-2).getValue(); 175 var module_admin_phone = module_sheet.getRange(8,i-2).getValue();176 var module_admin_email = module_sheet.getRange(9,i-2).getValue();177 var module_off_class = module_sheet.getRange(10,i-2).getValue();178 var module_on_class = module_sheet.getRange(11,i-2).getValue(); 179 var module_offattend_survey = module_sheet.getRange(12,i-2).getValue();180 181 182 var date_list_before = []183 var date_list_after = []184 185 for(var d=0; d<15 ; d++){186 var dating1 = new Date(date_start);187 var dating2 = new Date(date_start); 188 dating1.setDate(date_start.getDate()-d);189 dating2.setDate(date_start.getDate()+d);190 date_list_before.push(dating1);191 date_list_after.push(dating2);192 }//for d193 console.log(date_start);194 console.log(date_list_before);195 console.log(date_list_before[4]);196 console.log(date_list_before[4+12/12]);197 console.log(weekday);198 199 if(weekday === 5){200 for(k of [0,12]){201 //유형1202 text_sheet.getRange(1+1+k,50).setValue(date_list_before[4+k/12].getFullYear());203 text_sheet.getRange(1+1+k,51).setValue(date_list_before[4+k/12].getMonth());204 text_sheet.getRange(1+1+k,52).setValue(date_list_before[4+k/12].getDate());205 text_sheet.getRange(1+1+k,53).setValue(7);206 //유형2-1207 text_sheet.getRange(2+1+k,50).setValue(date_list_before[4+k/12].getFullYear());208 text_sheet.getRange(2+1+k,51).setValue(date_list_before[4+k/12].getMonth());209 text_sheet.getRange(2+1+k,52).setValue(date_list_before[4+k/12].getDate());210 text_sheet.getRange(2+1+k,53).setValue(7);211 //유형2-2212 text_sheet.getRange(3+1+k,50).setValue(date_list_before[1+k/12].getFullYear());213 text_sheet.getRange(3+1+k,51).setValue(date_list_before[1+k/12].getMonth());214 text_sheet.getRange(3+1+k,52).setValue(date_list_before[1+k/12].getDate());215 text_sheet.getRange(3+1+k,53).setValue(7);216 //유형3217 text_sheet.getRange(4+1+k,50).setValue(date_list_after[2-k/12].getFullYear());218 text_sheet.getRange(4+1+k,51).setValue(date_list_after[2-k/12].getMonth());219 text_sheet.getRange(4+1+k,52).setValue(date_list_after[2-k/12].getDate());220 text_sheet.getRange(4+1+k,53).setValue(7);221 //유형4222 text_sheet.getRange(5+1+k,50).setValue(date_list_before[4+k/12].getFullYear());223 text_sheet.getRange(5+1+k,51).setValue(date_list_before[4+k/12].getMonth());224 text_sheet.getRange(5+1+k,52).setValue(date_list_before[4+k/12].getDate());225 text_sheet.getRange(5+1+k,53).setValue(7); 226 //유형5227 text_sheet.getRange(6+1+k,50).setValue(date_list_before[0+k/12].getFullYear());228 text_sheet.getRange(6+1+k,51).setValue(date_list_before[0+k/12].getMonth());229 text_sheet.getRange(6+1+k,52).setValue(date_list_before[0+k/12].getDate());230 text_sheet.getRange(6+1+k,53).setValue(17); 231 //유형6-1232 text_sheet.getRange(7+1+k,50).setValue(date_list_before[0+k/12].getFullYear());233 text_sheet.getRange(7+1+k,51).setValue(date_list_before[0+k/12].getMonth());234 text_sheet.getRange(7+1+k,52).setValue(date_list_before[0+k/12].getDate());235 text_sheet.getRange(7+1+k,53).setValue(7);236 //유형6-2237 text_sheet.getRange(8+1+k,50).setValue(date_list_after[1-k/12].getFullYear());238 text_sheet.getRange(8+1+k,51).setValue(date_list_after[1-k/12].getMonth());239 text_sheet.getRange(8+1+k,52).setValue(date_list_after[1-k/12].getDate());240 text_sheet.getRange(8+1+k,53).setValue(7);241 //유형6-3242 text_sheet.getRange(9+1+k,50).setValue(date_list_after[7-k/12].getFullYear());243 text_sheet.getRange(9+1+k,51).setValue(date_list_after[7-k/12].getMonth());244 text_sheet.getRange(9+1+k,52).setValue(date_list_after[7-k/12].getDate());245 text_sheet.getRange(9+1+k,53).setValue(7);246 //유형6-4247 text_sheet.getRange(10+1+k,50).setValue(date_list_after[8-k/12].getFullYear());248 text_sheet.getRange(10+1+k,51).setValue(date_list_after[8-k/12].getMonth());249 text_sheet.getRange(10+1+k,52).setValue(date_list_after[8-k/12].getDate());250 text_sheet.getRange(10+1+k,53).setValue(7);251 //유형7252 text_sheet.getRange(11+1+k,50).setValue(date_list_after[1-k/12].getFullYear());253 text_sheet.getRange(11+1+k,51).setValue(date_list_after[1-k/12].getMonth());254 text_sheet.getRange(11+1+k,52).setValue(date_list_after[1-k/12].getDate());255 text_sheet.getRange(11+1+k,53).setValue(17); 256 //유형8257 text_sheet.getRange(12+1+k,50).setValue(date_list_after[1-k/12].getFullYear());258 text_sheet.getRange(12+1+k,51).setValue(date_list_after[1-k/12].getMonth());259 text_sheet.getRange(12+1+k,52).setValue(date_list_after[1-k/12].getDate());260 text_sheet.getRange(12+1+k,53).setValue(17);261 262 }263 for(var a=1; a < 25 ; a++){264 text_sheet.getRange(a+1,51).setValue(text_sheet.getRange(a+1,51).getValue() + 1);265 }266 text_sheet.getRange(2,55).setValue(date_start);267 text_sheet.getRange(2,56).setValue(date_list_after[1]);268 text_sheet.getRange(2,57).setValue(date_list_after[7]);269 text_sheet.getRange(2,58).setValue(date_list_after[8]);270 text_sheet.getRange(2,55).setNumberFormat('m"월 "d"일("ddd")"');271 text_sheet.getRange(2,56).setNumberFormat('m"월 "d"일("ddd")"');272 text_sheet.getRange(2,57).setNumberFormat('m"월 "d"일("ddd")"');273 text_sheet.getRange(2,58).setNumberFormat('m"월 "d"일("ddd")"');274 text_sheet.getRange(2,55).setValue.toString();275 text_sheet.getRange(2,56).setValue.toString();276 text_sheet.getRange(2,57).setValue.toString();277 text_sheet.getRange(2,58).setValue.toString();278 sample_sheet.getRange(14,6).setFormula("=text('문자내용'!BC2,\"m월 d일(aaa)\")"); // 일시 279 sample_sheet.getRange(14,7).setFormula("=text('문자내용'!BE2,\"m월 d일(aaa)\")"); // 일시 280 sample_sheet.getRange(16,6).setFormula("=text('문자내용'!BD2,\"m월 d일(aaa)\")"); // 일시 281 sample_sheet.getRange(16,7).setFormula("=text('문자내용'!BF2,\"m월 d일(aaa)\")"); // 일시282 }//if 금283 284 else if(weekday === 6){285 for(k of [0,12]){286 //유형1287 text_sheet.getRange(1+1+k,50).setValue(date_list_before[5+k/12].getFullYear());288 text_sheet.getRange(1+1+k,51).setValue(date_list_before[5+k/12].getMonth());289 text_sheet.getRange(1+1+k,52).setValue(date_list_before[5+k/12].getDate());290 text_sheet.getRange(1+1+k,53).setValue(7)291 //유형2-1292 text_sheet.getRange(2+1+k,50).setValue(date_list_before[5+k/12].getFullYear());293 text_sheet.getRange(2+1+k,51).setValue(date_list_before[5+k/12].getMonth());294 text_sheet.getRange(2+1+k,52).setValue(date_list_before[5+k/12].getDate());295 text_sheet.getRange(2+1+k,53).setValue(7);296 //유형2-2297 text_sheet.getRange(3+1+k,50).setValue(date_list_before[1+k/12].getFullYear());298 text_sheet.getRange(3+1+k,51).setValue(date_list_before[1+k/12].getMonth());299 text_sheet.getRange(3+1+k,52).setValue(date_list_before[1+k/12].getDate());300 text_sheet.getRange(3+1+k,53).setValue(7);301 //유형3302 text_sheet.getRange(4+1+k,50).setValue(date_list_after[1-k/12].getFullYear());303 text_sheet.getRange(4+1+k,51).setValue(date_list_after[1-k/12].getMonth());304 text_sheet.getRange(4+1+k,52).setValue(date_list_after[1-k/12].getDate());305 text_sheet.getRange(4+1+k,53).setValue(7);306 //유형4307 text_sheet.getRange(5+1+k,50).setValue(date_list_before[5+k/12].getFullYear());308 text_sheet.getRange(5+1+k,51).setValue(date_list_before[5+k/12].getMonth());309 text_sheet.getRange(5+1+k,52).setValue(date_list_before[5+k/12].getDate());310 text_sheet.getRange(5+1+k,53).setValue(7);311 //유형5312 text_sheet.getRange(6+1+k,50).setValue(date_list_before[0+k/12].getFullYear());313 text_sheet.getRange(6+1+k,51).setValue(date_list_before[0+k/12].getMonth());314 text_sheet.getRange(6+1+k,52).setValue(date_list_before[0+k/12].getDate());315 text_sheet.getRange(6+1+k,53).setValue(7);316 //유형6-1317 text_sheet.getRange(7+1+k,50).setValue(date_list_before[0+k/12].getFullYear());318 text_sheet.getRange(7+1+k,51).setValue(date_list_before[0+k/12].getMonth());319 text_sheet.getRange(7+1+k,52).setValue(date_list_before[0+k/12].getDate());320 text_sheet.getRange(7+1+k,53).setValue(7);321 //유형6-2322 text_sheet.getRange(8+1+k,50).setValue(date_list_after[6-k/12].getFullYear());323 text_sheet.getRange(8+1+k,51).setValue(date_list_after[6-k/12].getMonth());324 text_sheet.getRange(8+1+k,52).setValue(date_list_after[6-k/12].getDate());325 text_sheet.getRange(8+1+k,53).setValue(7);326 //유형6-3327 text_sheet.getRange(9+1+k,50).setValue(date_list_after[7-k/12].getFullYear());328 text_sheet.getRange(9+1+k,51).setValue(date_list_after[7-k/12].getMonth());329 text_sheet.getRange(9+1+k,52).setValue(date_list_after[7-k/12].getDate());330 text_sheet.getRange(9+1+k,53).setValue(7);331 //유형6-4332 text_sheet.getRange(10+1+k,50).setValue(date_list_after[13-k/12].getFullYear());333 text_sheet.getRange(10+1+k,51).setValue(date_list_after[13-k/12].getMonth());334 text_sheet.getRange(10+1+k,52).setValue(date_list_after[13-k/12].getDate());335 text_sheet.getRange(10+1+k,53).setValue(7);336 //유형7337 text_sheet.getRange(11+1+k,50).setValue(date_list_before[0+k/12].getFullYear());338 text_sheet.getRange(11+1+k,51).setValue(date_list_before[0+k/12].getMonth());339 text_sheet.getRange(11+1+k,52).setValue(date_list_before[0+k/12].getDate());340 text_sheet.getRange(11+1+k,53).setValue(22);341 //유형8342 text_sheet.getRange(12+1+k,50).setValue(date_list_before[0+k/12].getFullYear());343 text_sheet.getRange(12+1+k,51).setValue(date_list_before[0+k/12].getMonth());344 text_sheet.getRange(12+1+k,52).setValue(date_list_before[0+k/12].getDate());345 text_sheet.getRange(12+1+k,53).setValue(22);346 347 }348 for(var x=1; x < 25 ; x++){349 text_sheet.getRange(x+1,51).setValue(text_sheet.getRange(x+1,51).getValue() + 1);350 }351 text_sheet.getRange(2,55).setValue(date_start);352 text_sheet.getRange(2,56).setValue(date_list_after[6]);353 text_sheet.getRange(2,57).setValue(date_list_after[7]);354 text_sheet.getRange(2,58).setValue(date_list_after[13]);355 text_sheet.getRange(2,55).setNumberFormat('m"월 "d"일("ddd")"');356 text_sheet.getRange(2,56).setNumberFormat('m"월 "d"일("ddd")"');357 text_sheet.getRange(2,57).setNumberFormat('m"월 "d"일("ddd")"');358 text_sheet.getRange(2,58).setNumberFormat('m"월 "d"일("ddd")"'); 359 sample_sheet.getRange(14,6).setFormula("=text('문자내용'!BD2,\"m월 d일(aaa)\")"); 360 sample_sheet.getRange(14,7).setFormula("=text('문자내용'!BF2,\"m월 d일(aaa)\")"); 361 sample_sheet.getRange(16,6).setFormula("=text('문자내용'!BC2,\"m월 d일(aaa)\")"); 362 sample_sheet.getRange(16,7).setFormula("=text('문자내용'!BE2,\"m월 d일(aaa)\")"); 363 }//if 토 364 //2행365 sample_sheet.getRange(2,3).setValue(module_on_class);366 //4행367 text_sheet.getRange('BC3').setFormula('=textjoin(", ",,BC2:BF2)');368 sample_sheet.getRange(4,3).setFormula("='문자내용'!BC3");369 //6행370 //8행371 text_sheet.getRange(4,55).setValue(module_name);372 text_sheet.getRange(4,56).setValue(module_prof_name);373 text_sheet.getRange(5,55).setFormula('=textjoin(,,BC4,"(",BD4," 교수)")');374 sample_sheet.getRange(8,3).setFormula("='문자내용'!BC5");375 //10행376 sample_sheet.getRange(10,3).setFormula("='문자내용'!BC5");377 //12행378 text_sheet.getRange(7,55).setFormula('=textjoin(,,BC2," ~ ",BF2)');379 sample_sheet.getRange(12,3).setFormula("='문자내용'!BC7");380 sample_sheet.getRange(12,4).setFormula("='문자내용'!BC5");381 //14행382 sample_sheet.getRange(14,3).setValue(module_name); 383 sample_sheet.getRange(14,4).setValue(module_prof_name); 384 sample_sheet.getRange(14,5).setValue(module_off_class); 385 //16행386 sample_sheet.getRange(16,3).setValue(module_name); 387 sample_sheet.getRange(16,4).setValue(module_prof_name); 388 sample_sheet.getRange(16,5).setValue(module_off_class); 389 //18행390 sample_sheet.getRange(18,3).setValue(module_prof_name); 391 sample_sheet.getRange(18,4).setFormula("=text('문자내용'!BC2,\"m/d(aaa)\")"); 392 sample_sheet.getRange(18,5).setValue(module_name); 393 sample_sheet.getRange(18,6).setFormula('=textjoin(", ",,text(\'문자내용\'!BC2,"m/d(aaa)"),text(\'문자내용\'!BD2,"m/d(aaa)"),text(\'문자내용\'!BE2,"m/d(aaa)"),text(\'문자내용\'!BF2,"m/d(aaa)"))'); 394 sample_sheet.getRange(18,7).setValue(module_off_class); 395 //20행396 var kor_num_module = ""397 if(y=4){kor_num_module = "첫"}398 else if(y=5){kor_num_module = "두"}399 else if(y=6){kor_num_module = "세"}400 else if(y=7){kor_num_module = "네"}401 else if(y=8){kor_num_module = "다섯"}402 else if(y=9){kor_num_module = "여섯"}403 else if(y=10){kor_num_module = "일곱"}404 else if(y=11){kor_num_module = "여덟"}405 sample_sheet.getRange(20,3).setValue(kor_num_module); 406 sample_sheet.getRange(20,4).setFormula("=text('문자내용'!BC2,\"m월 d일(aaa)\")"); 407 sample_sheet.getRange(20,5).setValue(module_name); 408 sample_sheet.getRange(20,6).setFormula('=textjoin(", ",,text(\'문자내용\'!BC2,"m/d(aaa)"),text(\'문자내용\'!BD2,"m/d(aaa)"),text(\'문자내용\'!BE2,"m/d(aaa)"),text(\'문자내용\'!BF2,"m/d(aaa)"))'); 409 sample_sheet.getRange(20,7).setValue(module_offattend_survey); 410 411 sample_sheet.getRange('\'문자샘플\'!A20').copyTo(text_sheet.getRange(2,i+5), {contentsOnly: true});412 sample_sheet.getRange('\'문자샘플\'!A18').copyTo(text_sheet.getRange(3,i+5), {contentsOnly: true});413 sample_sheet.getRange('\'문자샘플\'!A18').copyTo(text_sheet.getRange(4,i+5), {contentsOnly: true});414 sample_sheet.getRange('\'문자샘플\'!A24').copyTo(text_sheet.getRange(5,i+5), {contentsOnly: true});415 sample_sheet.getRange('\'문자샘플\'!A4').copyTo(text_sheet.getRange(6,i+5), {contentsOnly: true});416 sample_sheet.getRange('\'문자샘플\'!A6').copyTo(text_sheet.getRange(7,i+5), {contentsOnly: true});417 if(weekday === 5){418 sample_sheet.getRange('\'문자샘플\'!A14').copyTo(text_sheet.getRange(8,i+5), {contentsOnly: true});419 sample_sheet.getRange('\'문자샘플\'!A16').copyTo(text_sheet.getRange(9,i+5), {contentsOnly: true});420 sample_sheet.getRange('\'문자샘플\'!B14').copyTo(text_sheet.getRange(10,i+5), {contentsOnly: true});421 sample_sheet.getRange('\'문자샘플\'!B16').copyTo(text_sheet.getRange(11,i+5), {contentsOnly: true});422 }423 else if(weekday === 6){424 sample_sheet.getRange('\'문자샘플\'!A16').copyTo(text_sheet.getRange(8,i+5), {contentsOnly: true});425 sample_sheet.getRange('\'문자샘플\'!A14').copyTo(text_sheet.getRange(9,i+5), {contentsOnly: true});426 sample_sheet.getRange('\'문자샘플\'!B16').copyTo(text_sheet.getRange(10,i+5), {contentsOnly: true});427 sample_sheet.getRange('\'문자샘플\'!B14').copyTo(text_sheet.getRange(11,i+5), {contentsOnly: true});428 }429 sample_sheet.getRange('\'문자샘플\'!A10').copyTo(text_sheet.getRange(12,i+5), {contentsOnly: true});430 sample_sheet.getRange('\'문자샘플\'!A8').copyTo(text_sheet.getRange(13,i+5), {contentsOnly: true});431 432 for(var q=1;q<13;q++){433 var value1 = "'******************************************* \n담당자 확인용 문자입니다. \n확인하시어 수정사항이 있으실 경우 [모듈정보] 시트 또는 [문자샘플] 시트를 수정하시기 바랍니다. \n기한은 오늘 오후 11시 30분까지입니다. \n*******************************************\n"434 var value2 = text_sheet.getRange(q+1,i+5).getValue();435 var value3 = value1 + value2436 send_sheet.appendRow([module_name,437 text_sheet.getRange(q+1,1).getValue(), 438 module_admin_name,439 module_admin_phone,,440 value3,441 text_sheet.getRange(q+1+12,50).getValue(),442 text_sheet.getRange(q+1+12,51).getValue(),443 text_sheet.getRange(q+1+12,52).getValue(),444 7])445 }446 for(var w of [2,3]){447 send_sheet.appendRow([module_name,448 text_sheet.getRange(w+1,1).getValue(), 449 module_prof_name,450 module_prof_phone,,451 text_sheet.getRange(w+1,i+5).getValue(),452 text_sheet.getRange(w+1,50).getValue(),453 text_sheet.getRange(w+1,51).getValue(),454 text_sheet.getRange(w+1,52).getValue(),455 text_sheet.getRange(w+1,53).getValue()])456 }457 458 459 for(var j=2; j < 1000 ; j++){460 var name = name_sheet.getRange(j,16).getValue();461 var phone = name_sheet.getRange(j,17).getValue();462 if(name_sheet.getRange(j, i).getValue() === '수강'){ 463 for(var e of [1,5,7,8,9,10,11]){464 if(text_sheet.getRange(e+1,i+5).getValue() !== ""){465 send_sheet.appendRow([module_name,466 text_sheet.getRange(e+1,1).getValue(), 467 name,468 phone,,469 text_sheet.getRange(e+1,i+5).getValue(),470 text_sheet.getRange(e+1,50).getValue(),471 text_sheet.getRange(e+1,51).getValue(),472 text_sheet.getRange(e+1,52).getValue(),473 text_sheet.getRange(e+1,53).getValue()])474 } 475 }476 for(var r of [4]){477 if(module_sheet.getRange(13,i-2).getValue() === "있음" && text_sheet.getRange(r+1,i+5).getValue() !== ""){478 send_sheet.appendRow([module_name,479 text_sheet.getRange(r+1,1).getValue(), 480 name,481 phone,,482 text_sheet.getRange(r+1,i+5).getValue(),483 text_sheet.getRange(r+1,50).getValue(),484 text_sheet.getRange(r+1,51).getValue(),485 text_sheet.getRange(r+1,52).getValue(),486 text_sheet.getRange(r+1,53).getValue()])487 }488 }489 if(name_sheet.getRange(j, 24).getValue() === '참석'){490 for(var z of [6]){491 if(text_sheet.getRange(z+1,i+5).getValue() !== ""){492 send_sheet.appendRow([module_name,493 text_sheet.getRange(z+1,1).getValue(), 494 name,495 phone,,496 text_sheet.getRange(z+1,i+5).getValue(),497 text_sheet.getRange(z+1,50).getValue(),498 text_sheet.getRange(z+1,51).getValue(),499 text_sheet.getRange(z+1,52).getValue(),500 text_sheet.getRange(z+1,53).getValue()])501 }502 }503 } // if 504 505 }// if 506 else if(name_sheet.getRange(j, i).getValue() === '청강'){507 for(var t of [1,5,7,8,9,10,12]){508 if(text_sheet.getRange(t+1,i+5).getValue() !== ""){509 send_sheet.appendRow([module_name,510 text_sheet.getRange(t+1,1).getValue(), 511 name,512 phone,,513 text_sheet.getRange(t+1,i+5).getValue(),514 text_sheet.getRange(t+1,50).getValue(),515 text_sheet.getRange(t+1,51).getValue(),516 text_sheet.getRange(t+1,52).getValue(),517 text_sheet.getRange(t+1,53).getValue()])518 }519 }520 } //else if 521 522 523 524 525 526 }//for j527 528 529 530 } // for i531 } // function...

Full Screen

Full Screen

sheetManipulation.gs

Source:sheetManipulation.gs Github

copy

Full Screen

...181 var day = startTimeString.substring(8, 10);182 var currentListSearchDate = month + '-' + day;183 184 // Creates an array of row numbers in the Current List matching the currentListSearchDate.185 var currentListData = currentListSheet.getRange(2, 1, currentListSheet.getLastRow(), currentListSheet.getLastColumn()).getValues();186 var matchingDateRows = [];187 for (var i = 0; i < currentListData.length; i++ ) {188 if (currentListData[i][0] === currentListSearchDate) {189 matchingDateRows.push(i + 2);190 };191 };192 193 var rowToDeleteFromCurrentList;194 195 // If there's more than one matching row from the Current List, cycle through and look for witness match.196 if (matchingDateRows.length === 0) {197 SpreadsheetApp.getActiveSpreadsheet().toast('❌ Matching depo not found in Current List. Nothing deleted from Current List.');198 } else if (matchingDateRows.length === 1) {199 rowToDeleteFromCurrentList = matchingDateRows[0];200 } else {201 var witness = eventDescription.match(/Witness Name:.*(?=\n)/)[0].slice(14);202 matchingDateRows.forEach(function(matchingRow) {203 if (currentListSheet.getRange(matchingRow, 2).getValue() === witness) {204 rowToDeleteFromCurrentList = matchingRow;205 };206 });207 };208 209 // Deletes the deposition from the Current List Sheet.210 currentListSheet.deleteRow(rowToDeleteFromCurrentList);211 SpreadsheetApp.getActiveSpreadsheet().toast('✂️ Deposition removed from Current List.');212 213 // Adds the cancelled deposition event to the Cancelled calendar.214 var event = cancelledCal.createEvent(cancelledEventTitle, 215 new Date(startTime),216 new Date(startTime),{217 description: eventDescription,218 location: eventLocation219 });220 221 // Updates the Schedule a depo Sheet with the cancelled event ID.222 var cancelledEventId = event.getId();223 scheduledDepoSheet.getRange(editRow, 37).setValue(cancelledEventId);224 225 // Deletes the event from the Services Calendar. 226 servicesCal.getEventById(eventId).deleteEvent();227 228 SpreadsheetApp.getActiveSpreadsheet().toast('🔀 Deposition moved to Cancelled Calendar');229};230////////////////////////////////////////////////////////////////////////////////////231///////////////////////////// SHEET UPDATE FUNCTIONS ///////////////////////////////232////////////////////////////////////////////////////////////////////////////////////233/** Updates all three worksheets based on highlighted row.234* Note: Triggered from menu item in UI.235*/236function updateWorksheetsByRow () {237 var ss = SpreadsheetApp.getActive();238 var currentSheet = ss.getSheetName();239 var deposSheet = ss.getSheetByName(currentSheet);240 var currentRow = ss.getActiveRange().getRow();241 242 /** Alerts user that they need to be in the "Schedule a depos" Sheet to perform this action, if needed. */243 if (currentSheet !== "Schedule a depo") {244 ss.toast("⚠️ This action can only be performed from the \"Schedule a depo\" Sheet.");245 return;246 };247 248 /** Gets data for worksheet updates from row and stores them in variables. */249 SpreadsheetApp.getActiveSpreadsheet().toast('💽 Gathering information');250 var rowData = deposSheet.getRange(currentRow, 2, 1, deposSheet.getLastColumn()).getValues()[0];251 var depoDate = rowData[0];252 var witnessName = rowData[1];253 var orderedBy = rowData[2];254 var caseStyle = rowData[4];255 var depoTime = rowData[5];256 var firm = rowData[6];257 var attorney = rowData[7];258 var firmAddress1 = rowData[8];259 var firmAddress2 = rowData[9];260 var city = rowData[10];261 var state = rowData[11];262 var zip = rowData[12];263 var attorneyPhone = rowData[13];264 var attorneyEmail = rowData[14];265 var locationFirm = rowData[15];266 var locationAddress1 = rowData[16];267 var locationAddress2 = rowData[17];268 var locationCity = rowData[18];269 var locationState = rowData[19];270 var locationZip = rowData[20];271 var services = rowData[22];272 var courtReporter = rowData[23];273 var videographer = rowData[24];274 var pip = rowData[25];275 var copyAttorney = rowData[26];276 var copyFirm = rowData[27];277 var copyAddress1 = rowData[28];278 var copyAddress2 = rowData[29];279 var copyCity = rowData[30];280 var copyState = rowData[31];281 var copyZip = rowData[32];282 var copyPhone = rowData[33];283 var copyEmail = rowData[34];284 285 // 📋286 /** Prints to Worksheets and alerts users with progress updates via Toasts. */287 SpreadsheetApp.getActiveSpreadsheet().toast('📋 Pasting deposition information to Worksheets');288 updateVideoWorksheet(locationFirm, locationAddress1, locationAddress2, locationCity, locationState, locationZip, depoDate, witnessName, caseStyle, depoTime, courtReporter, videographer, firm, attorney, attorneyEmail, firmAddress1, firmAddress2, city, state, zip, orderedBy, services, copyAttorney, copyFirm, copyAddress1, copyAddress2, copyCity, copyState, copyZip, copyEmail);289 updateCRWorksheet(locationFirm, locationAddress1, locationAddress2, locationCity, locationState, locationZip, depoDate, witnessName, caseStyle, depoTime, courtReporter, firm, attorney, attorneyEmail, firmAddress1, firmAddress2, city, state, zip, attorneyPhone, orderedBy, services, copyAttorney, copyFirm, copyAddress1, copyAddress2, copyCity, copyState, copyZip, copyEmail, copyPhone);290 updateConfirmationOfScheduling(locationFirm, locationAddress1, locationAddress2, locationCity, locationState, locationZip, depoDate, witnessName, caseStyle, depoTime, courtReporter, firm, attorney, firmAddress1, firmAddress2, city, state, zip, attorneyPhone, orderedBy, videographer, pip);291 SpreadsheetApp.getActiveSpreadsheet().toast('✅ Worksheets updated successfully');292};293/** Updates the Current List on addition of a new deposition.294@params {depositionInformation} strings Deposition information received from the sidebar.295*/296function updateCurrentList (depoDate, witnessName, firm, city, courtReporter, videographer, pip) { 297 var ss = SpreadsheetApp.getActive();298 var currentListSheet = ss.getSheetByName('Current List');299 300 // Sets defaults for hasCourtReporter and hasVideo, then formats them based on value from sidebar.301 var hasCourtReporter = 'x';302 var hasVideo = 'x';303 304 if (courtReporter === '') {305 courtReporter = 'x';306 } else {307 hasCourtReporter = 'Yes';308 };309 310 if (videographer === '') {311 videographer = 'x';312 } else {313 hasVideo = 'Yes';314 };315 316 if (pip === 'No') {317 pip = 'x';318 };319 320 // Trims depoDate down to MM-DD format.321 // 2020-10-10322 depoDate = depoDate.toString(); // $$$323 324 // Prints values to Current List Sheet.325 currentListSheet.insertRowBefore(2);326 currentListSheet.getRange('A2').setValue(depoDate);327 currentListSheet.getRange('B2').setValue(witnessName);328 currentListSheet.getRange('C2').setValue(firm);329 currentListSheet.getRange('D2').setValue(city);330 currentListSheet.getRange('F2').setValue(hasCourtReporter);331 currentListSheet.getRange('G2').setValue(courtReporter);332 currentListSheet.getRange('H2').setValue(hasVideo);333 currentListSheet.getRange('J2').setValue(pip);334 currentListSheet.getRange('L2').setValue(videographer);335 336 // Sorts the current list by date (first column) (disabled -- it messes with hidden rows)337 // currentListSheet.getRange(2, 1, currentListSheet.getLastRow() + 1, currentListSheet.getLastColumn()).sort(1)338};339/** Updates the Video Worksheet with the most recently-entered deposition information.340@params {depositionInformation} strings Deposition information received from the sidebar.341*/342function updateVideoWorksheet(locationFirm, locationAddress1, locationAddress2, locationCity, locationState, locationZip, depoDate, witnessName, caseStyle, depoTime, courtReporter, videographer, firm, attorney, attorneyEmail, firmAddress1, firmAddress2, city, state, zip, orderedBy, services, copyAttorney, copyFirm, copyAddress1, copyAddress2, copyCity, copyState, copyZip, copyEmail) {343 var videoSheet = SpreadsheetApp.getActive().getSheetByName('Video Worksheet');344 345 // Sets values inside video worksheet.346 videoSheet.getRange('B9').setValue(locationFirm);347 videoSheet.getRange('B10').setValue(locationAddress1);348 videoSheet.getRange('B11').setValue(locationAddress2);349 videoSheet.getRange('B12').setValue(locationCity);350 videoSheet.getRange('C12').setValue(locationState);351 videoSheet.getRange('D12').setValue(locationZip);352 videoSheet.getRange('F9').setValue(depoDate);353 videoSheet.getRange('A1').setValue(depoDate);354 videoSheet.getRange('F10').setValue(witnessName);355 videoSheet.getRange('D1').setValue(witnessName);356 videoSheet.getRange('F11').setValue(caseStyle);357 videoSheet.getRange('F13').setValue(videographer);358 videoSheet.getRange('F14').setValue(depoTime);359 videoSheet.getRange('B13').setValue(courtReporter);360 videoSheet.getRange('B22').setValue(firm);361 videoSheet.getRange('B1').setValue(firm);362 videoSheet.getRange('B20').setValue(attorney);363 videoSheet.getRange('D21').setValue(attorneyEmail);364 videoSheet.getRange('B24').setValue(firmAddress1);365 videoSheet.getRange('B25').setValue(firmAddress2);366 videoSheet.getRange('A26').setValue(city);367 videoSheet.getRange('B26').setValue(state);368 videoSheet.getRange('C26').setValue(zip);369 videoSheet.getRange('H55').setValue(orderedBy);370 videoSheet.getRange('G1').setValue(services);371 videoSheet.getRange('B28').setValue(copyAttorney);372 videoSheet.getRange('B30').setValue(copyFirm);373 videoSheet.getRange('B32').setValue(copyAddress1);374 videoSheet.getRange('B33').setValue(copyAddress2);375 videoSheet.getRange('A34').setValue(copyCity);376 videoSheet.getRange('B34').setValue(copyState);377 videoSheet.getRange('C34').setValue(copyZip);378 videoSheet.getRange('D29').setValue(copyEmail);379};380/** Updates the CR Worksheet with the most recently-entered deposition information.381@params {depositionInformation} strings Deposition information received from the sidebar.382*/383function updateCRWorksheet(locationFirm, locationAddress1, locationAddress2, locationCity, locationState, locationZip, depoDate, witnessName, caseStyle, depoTime, courtReporter, firm, attorney, attorneyEmail, firmAddress1, firmAddress2, city, state, zip, attorneyPhone, orderedBy, services, copyAttorney, copyFirm, copyAddress1, copyAddress2, copyCity, copyState, copyZip, copyEmail, copyPhone) {384 var crSheet = SpreadsheetApp.getActive().getSheetByName('CR Worksheet');385 386 // Sets values inside CR Worksheet.387 crSheet.getRange('B7').setValue(locationFirm);388 crSheet.getRange('B8').setValue(locationAddress1);389 crSheet.getRange('B9').setValue(locationAddress2);390 crSheet.getRange('B10').setValue(locationCity);391 crSheet.getRange('C10').setValue(locationState);392 crSheet.getRange('D10').setValue(locationZip);393 crSheet.getRange('F7').setValue(depoDate);394 crSheet.getRange('A1').setValue(depoDate);395 crSheet.getRange('F8').setValue(witnessName);396 crSheet.getRange('D1').setValue(witnessName);397 crSheet.getRange('F9').setValue(caseStyle);398 crSheet.getRange('F11').setValue(depoTime);399 crSheet.getRange('B11').setValue(courtReporter);400 crSheet.getRange('D20').setValue(firm);401 crSheet.getRange('B1').setValue(firm);402 crSheet.getRange('B19').setValue(attorney);403 crSheet.getRange('E21').setValue(attorneyEmail);404 crSheet.getRange('A22').setValue(firmAddress1);405 crSheet.getRange('C22').setValue(firmAddress2);406 crSheet.getRange('A23').setValue(city);407 crSheet.getRange('B23').setValue(state);408 crSheet.getRange('C23').setValue(zip);409 crSheet.getRange('C21').setValue(attorneyPhone);410 crSheet.getRange('H57').setValue(orderedBy);411 crSheet.getRange('G1').setValue(services);412 crSheet.getRange('B28').setValue(copyAttorney);413 crSheet.getRange('D29').setValue(copyFirm);414 crSheet.getRange('A31').setValue(copyAddress1);415 crSheet.getRange('C31').setValue(copyAddress2);416 crSheet.getRange('A32').setValue(copyCity);417 crSheet.getRange('B32').setValue(copyState);418 crSheet.getRange('C32').setValue(copyZip);419 crSheet.getRange('D31').setValue(copyEmail);420 crSheet.getRange('C30').setValue(copyPhone);421};422/** Updates the Confirmation of Scheduling with the most recently-entered deposition information.423@params {depositionInformation} strings Deposition information received from the sidebar.424*/425function updateConfirmationOfScheduling(locationFirm, locationAddress1, locationAddress2, locationCity, locationState, locationZip, depoDate, witnessName, caseStyle, depoTime, courtReporter, firm, attorney, firmAddress1, firmAddress2, city, state, zip, attorneyPhone, orderedBy, videographer, pip) {426 var confSheet = SpreadsheetApp.getActive().getSheetByName('Confirmation of Scheduling');427 428 // Sets values inside CR Worksheet.429 confSheet.getRange('C18').setValue(locationFirm);430 confSheet.getRange('C19').setValue(locationAddress1);431 confSheet.getRange('C20').setValue(locationAddress2);432 confSheet.getRange('C21').setValue(locationCity);433 confSheet.getRange('D21').setValue(locationState);434 confSheet.getRange('E21').setValue(locationZip);435 confSheet.getRange('G16').setValue(depoDate);436 confSheet.getRange('G20').setValue(witnessName);437 confSheet.getRange('C16').setValue(caseStyle);438 confSheet.getRange('G18').setValue(depoTime);439 confSheet.getRange('C22').setValue(courtReporter);440 confSheet.getRange('C8').setValue(firm);441 confSheet.getRange('G22').setValue(attorney);442 confSheet.getRange('G8').setValue(firmAddress1);443 confSheet.getRange('G9').setValue(firmAddress2);444 confSheet.getRange('G10').setValue(city);445 confSheet.getRange('H10').setValue(state);446 confSheet.getRange('I10').setValue(zip);447 confSheet.getRange('E11').setValue(attorneyPhone);448 confSheet.getRange('C10').setValue(orderedBy);449 confSheet.getRange('E22').setValue(videographer);450 confSheet.getRange('D28').setValue(pip);451};452/** Updates Video Worksheet, CR Worksheet, and Confirmation of Scheduling on manual time or date edit to Schedule a depo Sheet.453@param {editRow} number The row where a user has just edited the time or date of a depo in the Schedule a depo Sheet.454*/455function updateSheetsOnTimeOrDateEdit(editRow) {456 var ss = SpreadsheetApp.getActive();457 var scheduleSheet = ss.getSheetByName('Schedule a depo');458 459 // Structures data from row user has just edited.460 var locationFirm = scheduleSheet.getRange(editRow, 17).getValue();461 var locationAddress1 = scheduleSheet.getRange(editRow, 18).getValue();462 var locationAddress2 = scheduleSheet.getRange(editRow, 19).getValue();463 var locationCity = scheduleSheet.getRange(editRow, 20).getValue();464 var locationState = scheduleSheet.getRange(editRow, 21).getValue();465 var locationZip = scheduleSheet.getRange(editRow, 22).getValue();466 var depoDate = scheduleSheet.getRange(editRow, 2).getValue();467 var witnessName = scheduleSheet.getRange(editRow, 3).getValue();468 var caseStyle = scheduleSheet.getRange(editRow, 6).getValue();469 var depoTime = scheduleSheet.getRange(editRow, 7).getValue();470 var orderedBy = scheduleSheet.getRange(editRow, 4).getValue();471 var firm = scheduleSheet.getRange(editRow, 8).getValue();472 var attorneyEmail = scheduleSheet.getRange(editRow, 16).getValue();473 var firmAddress1 = scheduleSheet.getRange(editRow, 10).getValue();474 var firmAddress2 = scheduleSheet.getRange(editRow, 11).getValue();475 var city = scheduleSheet.getRange(editRow, 12).getValue();476 var state = scheduleSheet.getRange(editRow, 13).getValue();477 var zip = scheduleSheet.getRange(editRow, 14).getValue();478 var attorneyPhone = scheduleSheet.getRange(editRow, 15).getValue();479 var attorney = scheduleSheet.getRange(editRow, 9).getValue();480 var services = scheduleSheet.getRange(editRow, 24).getValue();481 var courtReporter = scheduleSheet.getRange(editRow, 25).getValue();482 var videographer = scheduleSheet.getRange(editRow, 26).getValue();483 var pip = scheduleSheet.getRange(editRow, 27).getValue();484 var copyAttorney = scheduleSheet.getRange(editRow, 28).getValue();485 var copyFirm = scheduleSheet.getRange(editRow, 29).getValue();486 var copyAddress1 = scheduleSheet.getRange(editRow, 30).getValue();487 var copyAddress2 = scheduleSheet.getRange(editRow, 31).getValue();488 var copyCity = scheduleSheet.getRange(editRow, 32).getValue();489 var copyState = scheduleSheet.getRange(editRow, 33).getValue();490 var copyZip = scheduleSheet.getRange(editRow, 34).getValue();491 var copyEmail = scheduleSheet.getRange(editRow, 36).getValue();492 var copyPhone = scheduleSheet.getRange(editRow, 35).getValue();493 494 updateCRWorksheet(locationFirm, locationAddress1, locationAddress2, locationCity, locationState, locationZip, depoDate, witnessName, caseStyle, depoTime, courtReporter, firm, attorney, attorneyEmail, firmAddress1, firmAddress2, city, state, zip, attorneyPhone, orderedBy, services, copyAttorney, copyFirm, copyAddress1, copyAddress2, copyCity, copyState, copyZip, copyEmail, copyPhone);495 updateVideoWorksheet(locationFirm, locationAddress1, locationAddress2, locationCity, locationState, locationZip, depoDate, witnessName, caseStyle, depoTime, courtReporter, videographer, firm, attorney, attorneyEmail, firmAddress1, firmAddress2, city, state, zip, orderedBy, services, copyAttorney, copyFirm, copyAddress1, copyAddress2, copyCity, copyState, copyZip, copyEmail);496 updateConfirmationOfScheduling(locationFirm, locationAddress1, locationAddress2, locationCity, locationState, locationZip, depoDate, witnessName, caseStyle, depoTime, courtReporter, firm, attorney, firmAddress1, firmAddress2, city, state, zip, attorneyPhone, orderedBy, videographer, pip);497 ss.toast('📚 All worksheets updated with new information');498};499////////////////////////////////////////////////////////////////////////////////////500//////////////////////////////////// UTILITIES /////////////////////////////////////501////////////////////////////////////////////////////////////////////////////////////502/** Prints an array to the final row of the "Schedule a depo" sheet503@param {array} 1d array ordered to align with the columns in "Schedule a depo."504*/505function printNewDeposition (array) {506 var ss = SpreadsheetApp.getActive();507 var scheduleSheet = ss.getSheetByName('Schedule a depo');508 509 // Create an empty row for the new deposition at the top of the sheet, shift others down by 1, print to the new row510 scheduleSheet.insertRowBefore(2);511 scheduleSheet.getRange(2, 1, 1, 36).setValues(array);512};513/** Takes the most recently-scheduled depo by an orderer and returns an array with the lawyer and firm information.514@param {orderer} string The previous orderer's name as selected from the New Deposition from a Previous Orderer sidebar dropdown menu.515*/516function firmInformationFromOrderer (orderer) {517 var ss = SpreadsheetApp.getActive();518 var scheduleSheet = ss.getSheetByName('Schedule a depo');519 520 // Gets an array of row arrays that match orderer name521 var allScheduledRows = scheduleSheet.getRange(2, 1, scheduleSheet.getLastRow(), scheduleSheet.getLastColumn()).getValues();522 var attyAndFirmInformation = [];523 for (var i = 0; i < allScheduledRows.length; i++) {524 if (allScheduledRows[i][3] === orderer) {525 // allScheduledRows[i][n] because columns 7 - 15 contain the desired information on the "Schedule a depo" sheet526 attyAndFirmInformation.push(allScheduledRows[i][7]);527 attyAndFirmInformation.push(allScheduledRows[i][8]);528 attyAndFirmInformation.push(allScheduledRows[i][9]);529 attyAndFirmInformation.push(allScheduledRows[i][10]);530 attyAndFirmInformation.push(allScheduledRows[i][11]);531 attyAndFirmInformation.push(allScheduledRows[i][12]);532 attyAndFirmInformation.push(allScheduledRows[i][13]);533 attyAndFirmInformation.push(allScheduledRows[i][14]);534 attyAndFirmInformation.push(allScheduledRows[i][15]);535 break;536 };537 };538 return attyAndFirmInformation;539};540/** Gets the email address of a previous orderer from the most recently-scheduled depo from them541@param {orderer} string The previous orderer's name as selected from the New Deposition from a Previous Orderer sidebar dropdown menu.542*/543function emailFromOrderer (orderer) {544 var ss = SpreadsheetApp.getActive();545 var scheduleSheet = ss.getSheetByName('Schedule a depo');546 547 // Gets an array of row arrays that match orderer name548 var allScheduledRows = scheduleSheet.getRange(2, 1, scheduleSheet.getLastRow(), scheduleSheet.getLastColumn()).getValues();549 var ordererEmail = '';550 for (var i = 0; i < allScheduledRows.length; i++) {551 if (allScheduledRows[i][3] === orderer) {552 // allScheduledRows[i][n] because columns 7 - 15 contain the desired information on the "Schedule a depo" sheet553 ordererEmail = allScheduledRows[i][4];554 break;555 };556 };557 558 return ordererEmail;559};560// Converts all dates in Current List to 2020-10-10 format561function convertDates() {562 var ss = SpreadsheetApp.getActive();563 var currentList = ss.getSheetByName('Current List');564 565 // Instantiates data ranges and references566 var rowCount = currentList.getLastRow() - 1567 var range = currentList.getRange(2, 1, rowCount, 1)568 var data = range.getValues()569 570 // Loops over each, formats the string, and replaces if necessary571 for (var i = 0; i < data.length; i++) {572 var dateString = data[i][0]573 if (dateString.length == 5) {574 try {575 var newDate = '2020-' + dateString576 var row = i + 2577 currentList.getRange(row, 1).setValue(newDate)578 } catch (error) {579 cosole.log(error)580 }581 }582 }583 // currentList.getRange(1, currentList.getLastColumn(), currentList.getLastRow(), currentList.getLastColumn()).sort(1)584}585function test() {586 var ss = SpreadsheetApp.getActive();587 var currentList = ss.getSheetByName('Current List');588 currentList.getRange(2, 1, currentList.getLastRow(), currentList.getLastColumn()).sort(1)...

Full Screen

Full Screen

DD CALCULATIONS.js

Source:DD CALCULATIONS.js Github

copy

Full Screen

...82 var temp_sheet = ssopen_byid_byname(DDC_currentfile_id,'TEMPLATE');83 if(temp_sheet==null){84 return [0,1]85 }86 var temp_header = temp_sheet.getRange(1,1,1,4);87 var head_color = temp_sheet.getRange(1,1,1,temp_sheet.getLastColumn()).getBackgroundColor();88 var ur = 2;89 var unit_temp = temp_sheet.getRange(ur,1,1,1);90 var output_sheet = ssopen_byid_byname(DDC_currentfile_id,DDC_currentmonth);91 if(output_sheet == null)92 {93 var output_sheet = ssopen_byid_insertsheet(DDC_currentfile_id,DDC_currentmonth);94 }95 output_sheet.setColumnWidth(1, 600).setFrozenRows(1);96 output_sheet.setColumnWidth(2,300);97 var output_header = output_sheet.getRange(1,1,1,4);98 temp_header.copyTo(output_header);99 var starting_row=output_sheet.getLastRow()+3;100 var unit_row = output_sheet.getRange(starting_row,1,1,1);101 unit_temp.copyTo(unit_row);102 //SET THE UNIT NUMBER IN THE SHEET//103 var unit_row1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue("'"+unit_value);104 var cust_temp = temp_sheet.getRange(ur+1,1,1,1);105 var cust_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);106 cust_temp.copyTo(cust_row);107 //SET THE CUSTOMER NAME IN THE SHEET//108 var cust_row1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(name);109 var start_temp = temp_sheet.getRange(ur+2,1,1,1);110 var start_date = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);111 start_temp.copyTo(start_date);112 //SET THE START DATE//113 if(DDC_recverlgth==selectedrecverlength)114 {115 var start_date1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_startdatearrary).setNote('LEASE_PERIOD '+DDC_recverarray);116 }117 else118 {119 var start_date1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_startdatearrary).setNote('LEASE_PERIOD '+DDC_recverarray);120 }121 //SET THE END DATE//122 var end_temp = temp_sheet.getRange(ur+3,1,1,1);123 var end_date = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);124 end_temp.copyTo(end_date);125 if(DDC_recverlgth==selectedrecverlength)126 {127 var end_date1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_enddatearrary);128 }129 else130 {131 var end_date1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_enddatearrary);132 }133 var dep_temp = temp_sheet.getRange(ur+4,1,1,1);134 var dep_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);135 dep_temp.copyTo(dep_row);136 var dep_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(dep_value);137 if(DDC_pay_unpaiddate!="")138 {139 var cell = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);140 cell.setWrap(true);141 var rentcard_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("RENT = UNPAID FR "+DDC_pay_unpaiddate).setBackgroundRGB(255, 242, 204).setFontWeight("bold");142 }143 if(DDC_proratedunpaid=="UNPAID")144 {145 var pccard_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("PC ="+DDC_proratedunpaid).setBackgroundRGB(255, 242, 204).setFontWeight("bold");146 }147 //LOST CARD AMOUNT ,TILL DATE STORED IN THE SHEET//148 if(DDC_cardcount!=0)149 {150 var lastcard_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(lastcarddate).setBackgroundRGB(255, 242, 204).setFontWeight("bold");151 var lastcard_rowcrg1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_cardamount);152 }else153 {154 var lastcard_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("ACCESS CARD LOST").setBackgroundRGB(255, 242, 204).setFontWeight("bold");155 var lastcard_rowcrg1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_cardamount);156 }157 if(rentalcase==3)158 {159 dep_row1.setNote(depcomment).setBackgroundColor('RED');160 }161 var elec_temp = temp_sheet.getRange(ur+5,1,1,1);162 var elec_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);163 elec_temp.copyTo(elec_row);164 var elec_row1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_cap);165 //SET THE NO OF DIVISION VALUE IN THE SHEET//166 var div_val = output_sheet.getRange(output_sheet.getLastRow(),3,1,1).setValue('No of Div '+DDC_no_ofdivision);167 var elec_beginrow = output_sheet.getLastRow()+2;168 var elec_counter=elec_beginrow;169 var caps_split=caps.split('^^');170 var modifies_split=modifies.split('^^');171 var invoicedate_key=invoicedatekey.split('^^');172 var electrcap_key=electrcapkey.split('^^');173 var invdate_split=maininvdate.split('^^');174 var elecamt_split=mainelecamt.split('^^');175 var elecdivamt_split=mainelecdivamt.split('^^');176 var DDC_sheet_range='';177 for(var c=0;c<DDC_electrcap;c++){178 if(DDC_cap_flag==0){179 output_sheet.getRange(elec_counter,1).setValue(caps_split[c]);180 if(caps_split[c]!='')181 DDC_sheet_range=elec_counter;182 if((c!=0)&&(modifies_split[c]!=''))183 output_sheet.getRange(DDC_sheet_range,1).setValue(modifies_split[c])184 }185 for(var j=0;j<DDC_invoicedate;j++){186 if(electrcap_key[c]==invoicedate_key[j]){187 output_sheet.getRange(elec_counter,2).setValue(invdate_split[j]).setFontWeight("bold");188 output_sheet.getRange(elec_counter,3).setValue(elecamt_split[j]);189 output_sheet.getRange(elec_counter,4).setValue(elecdivamt_split[j]);190 elec_counter++;191 }192 }193 }194 //SET THE TOTAL VALUES UPTO ELECTRICITY//195 var sub_temp = temp_sheet.getRange(ur+17,1,1,4);196 var sub_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,4);197 sub_temp.copyTo(sub_row);198 var sub_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(parseFloat(DDC_electsubtotal)).setFontWeight("bold");199 var aircon_temp = temp_sheet.getRange(ur+20,1,1,1);200 var aircon_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,1);201 aircon_temp.copyTo(aircon_row);202 //SET THE AIRCON FEE//203 if(fixedaircon!=''){204 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue('Fixed Aircon Fee ');205 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(fixedaircon);206 }207 if(DDC_sumofquater!=0 && DDC_sumofquater<DDC_aircon){208 var lpquaterval_split=lpquaterval.split('^^');209 var quaterval_split=quaterval.split('^^');210 var totalval_split=totalval.split('^^');211 for(var a=0;a<DDC_sumofquater;a++){212 var quarter_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(lpquaterval_split[a]);213 var cell_quarter_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);214 cell_quarter_row.setWrap(true);215 var quarter_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(quaterval_split[a]);216 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(totalval_split[a]);217 }218 }219 else{220 var airquarter_split=airquarter.split('^^');221 var airperquater_split=airperquater.split('^^');222 var airvaldiff_split=airvaldiff.split('^^');223 for(var l=0; l<DDC_aircon;l++){224 var quarter_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(airquarter_split[l]);225 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(airperquater_split[l]);226 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(airvaldiff_split[l]);227 }228 }229 var sub1_temp = temp_sheet.getRange(ur+23,1,1,4);230 var sub1_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,4);231 sub1_temp.copyTo(sub1_row);232 var sub1_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_airconsubtotal).setFontWeight("bold");233 var main_temp = temp_sheet.getRange(ur+25,1,1,1);234 var maint_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,1);235 main_temp.copyTo(maint_row);236 var beginrow = output_sheet.getLastRow()+2;237 var counter=beginrow;238 var invitem_split=unitinvoiceitem.split('^^');239 var invdate_split=unitinvoicedate.split('^^');240 var unitamt_split=unitamount.split('^^');241 var unitdivamt_split=unitdivamount.split('^^');242 for (var u=0;u<parseInt(DDC_maintenancelength);u++)243 {244 output_sheet.getRange(counter,1).setValue(invitem_split[u]);245 output_sheet.getRange(counter,2).setValue(invdate_split[u]);246 output_sheet.getRange(counter,3).setValue(unitamt_split[u]);247 output_sheet.getRange(counter,4).setValue(unitdivamt_split[u]);248 counter++249 }250 if(checkout_clean!='')251 {252 var checkout_clean_temp = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("Checkout Cleaning");253 var checkout_clean_row = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(checkout_clean);254 }255 if(drycleaning!="")256 {257 var dry_clean_temp = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("Curtain Dry Cleaning");258 var dry_clean_row = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(drycleaning);259 }260 var crgbeginrow = output_sheet.getLastRow()+1;261 var crgcounter=crgbeginrow;262 var chargtype_split=chargtype.split('^^');263 var chargamount_split=chargamount.split('^^');264 for ( var c=0;c<parseInt(DDC_chargelength);c++)265 {266 output_sheet.getRange(crgcounter,1).setValue(chargtype_split[c]).setFontWeight("bold");267 output_sheet.getRange(crgcounter,4).setValue(chargamount_split[c]);268 crgcounter++;269 }270 var sub2_temp = temp_sheet.getRange(ur+33,1,1,4);271 var sub2_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,4);272 sub2_temp.copyTo(sub2_row);273 var sub2_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_unitsubtotal).setFontWeight("bold");274 var tded_temp = temp_sheet.getRange(ur+37,1,1,4);275 var tded_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,4);276 tded_temp.copyTo(tded_row);277 var tded_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_totalallsubtl);278 var refun_temp = temp_sheet.getRange(ur+38,1,1,4);279 var refun_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,4);280 refun_temp.copyTo(refun_row);281 var refun_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_tefundtotal);282 var end_row =output_sheet.getLastRow();283 var no_of_row = end_row-starting_row+1;284 output_sheet.getRange(starting_row,1,no_of_row,4).setBorder(true,true,true,true,true,true);285 output_sheet.getRange(starting_row,2,end_row,1).setHorizontalAlignment("left");286 var black_row1 = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,4).setBackgroundColor('black');287 output_sheet.insertRows(output_sheet.getLastRow()+5, 50);288 return [1,1];...

Full Screen

Full Screen

DD CALCULATIONS.gs

Source:DD CALCULATIONS.gs Github

copy

Full Screen

...80 var temp_sheet = ssopen_byid_byname(DDC_currentfile_id,'TEMPLATE');81 if(temp_sheet==null){82 return 'no_template_sheet';83 }84 var temp_header = temp_sheet.getRange(1,1,1,4);85 var head_color = temp_sheet.getRange(1,1,1,temp_sheet.getLastColumn()).getBackgroundColor();86 var ur = 2; 87 var unit_temp = temp_sheet.getRange(ur,1,1,1);88 var output_sheet = ssopen_byid_byname(DDC_currentfile_id,DDC_currentmonth);89 if(output_sheet == null)90 {91 var output_sheet = ssopen_byid_insertsheet(DDC_currentfile_id,DDC_currentmonth);92 }93 output_sheet.setColumnWidth(1, 600).setFrozenRows(1);94 output_sheet.setColumnWidth(2,300);95 var output_header = output_sheet.getRange(1,1,1,4); 96 temp_header.copyTo(output_header);97 var starting_row=output_sheet.getLastRow()+3;98 var unit_row = output_sheet.getRange(starting_row,1,1,1);99 unit_temp.copyTo(unit_row);100 //SET THE UNIT NUMBER IN THE SHEET//101 var unit_row1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue("'"+unit_value);102 var cust_temp = temp_sheet.getRange(ur+1,1,1,1);103 var cust_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);104 cust_temp.copyTo(cust_row);105 //SET THE CUSTOMER NAME IN THE SHEET//106 var cust_row1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(name);107 var start_temp = temp_sheet.getRange(ur+2,1,1,1);108 var start_date = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);109 start_temp.copyTo(start_date);110 //SET THE START DATE//111 if(DDC_recverlgth==selectedrecverlength)112 {113 var start_date1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_startdatearrary).setNote('LEASE_PERIOD '+DDC_recverarray);114 }115 else116 {117 var start_date1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_startdatearrary).setNote('LEASE_PERIOD '+DDC_recverarray);118 }119 //SET THE END DATE//120 var end_temp = temp_sheet.getRange(ur+3,1,1,1);121 var end_date = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);122 end_temp.copyTo(end_date);123 if(DDC_recverlgth==selectedrecverlength)124 {125 var end_date1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_enddatearrary);126 }127 else128 {129 var end_date1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_enddatearrary);130 }131 var dep_temp = temp_sheet.getRange(ur+4,1,1,1);132 var dep_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);133 dep_temp.copyTo(dep_row);134 var dep_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(dep_value);135 if(DDC_pay_unpaiddate!="")136 {137 var cell = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);138 cell.setWrap(true);139 var rentcard_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("RENT = UNPAID FR "+DDC_pay_unpaiddate).setBackgroundRGB(255, 242, 204).setFontWeight("bold");140 }141 if(DDC_proratedunpaid=="UNPAID")142 {143 var pccard_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("PC ="+DDC_proratedunpaid).setBackgroundRGB(255, 242, 204).setFontWeight("bold");144 }145 //LOST CARD AMOUNT ,TILL DATE STORED IN THE SHEET//146 if(DDC_cardcount!=0)147 {148 var lastcard_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(lastcarddate).setBackgroundRGB(255, 242, 204).setFontWeight("bold");149 var lastcard_rowcrg1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_cardamount);150 }else151 {152 var lastcard_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("ACCESS CARD LOST").setBackgroundRGB(255, 242, 204).setFontWeight("bold");153 var lastcard_rowcrg1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_cardamount);154 }155 if(rentalcase==3)156 {157 dep_row1.setNote(depcomment).setBackgroundColor('RED');158 }159 var elec_temp = temp_sheet.getRange(ur+5,1,1,1);160 var elec_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);161 elec_temp.copyTo(elec_row);162 var elec_row1 = output_sheet.getRange(output_sheet.getLastRow(),2,1,1).setValue(DDC_cap); 163 //SET THE NO OF DIVISION VALUE IN THE SHEET//164 var div_val = output_sheet.getRange(output_sheet.getLastRow(),3,1,1).setValue('No of Div '+DDC_no_ofdivision);165 var elec_beginrow = output_sheet.getLastRow()+2; 166 var elec_counter=elec_beginrow;167 var caps_split=caps.split('^^');168 var modifies_split=modifies.split('^^');169 var invoicedate_key=invoicedatekey.split('^^');170 var electrcap_key=electrcapkey.split('^^');171 var invdate_split=maininvdate.split('^^');172 var elecamt_split=mainelecamt.split('^^');173 var elecdivamt_split=mainelecdivamt.split('^^');174 var DDC_sheet_range='';175 for(var c=0;c<DDC_electrcap;c++){176 if(DDC_cap_flag==0){177 output_sheet.getRange(elec_counter,1).setValue(caps_split[c]);178 if(caps_split[c]!='')179 DDC_sheet_range=elec_counter;180 if((c!=0)&&(modifies_split[c]!=''))181 output_sheet.getRange(DDC_sheet_range,1).setValue(modifies_split[c]) 182 }183 for(var j=0;j<DDC_invoicedate;j++){184 if(electrcap_key[c]==invoicedate_key[j]){185 output_sheet.getRange(elec_counter,2).setValue(invdate_split[j]).setFontWeight("bold"); 186 output_sheet.getRange(elec_counter,3).setValue(elecamt_split[j]);187 output_sheet.getRange(elec_counter,4).setValue(elecdivamt_split[j]); 188 elec_counter++;189 }190 }191 }192 //SET THE TOTAL VALUES UPTO ELECTRICITY//193 var sub_temp = temp_sheet.getRange(ur+17,1,1,4);194 var sub_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,4);195 sub_temp.copyTo(sub_row);196 var sub_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(parseFloat(DDC_electsubtotal)).setFontWeight("bold");197 var aircon_temp = temp_sheet.getRange(ur+20,1,1,1);198 var aircon_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,1);199 aircon_temp.copyTo(aircon_row);200 //SET THE AIRCON FEE// 201 if(fixedaircon!=''){202 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue('Fixed Aircon Fee ');203 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(fixedaircon);204 } 205 if(DDC_sumofquater!=0 && DDC_sumofquater<DDC_aircon){206 var lpquaterval_split=lpquaterval.split('^^');207 var quaterval_split=quaterval.split('^^');208 var totalval_split=totalval.split('^^');209 for(var a=0;a<DDC_sumofquater;a++){210 var quarter_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(lpquaterval_split[a]);211 var cell_quarter_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1);212 cell_quarter_row.setWrap(true);213 var quarter_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(quaterval_split[a]);214 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(totalval_split[a]);215 }216 }217 else{218 var airquarter_split=airquarter.split('^^');219 var airperquater_split=airperquater.split('^^');220 var airvaldiff_split=airvaldiff.split('^^');221 for(var l=0; l<DDC_aircon;l++){ 222 var quarter_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(airquarter_split[l]);223 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue(airperquater_split[l]);224 var aircon_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(airvaldiff_split[l]); 225 }226 }227 var sub1_temp = temp_sheet.getRange(ur+23,1,1,4); 228 var sub1_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,4);229 sub1_temp.copyTo(sub1_row);230 var sub1_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_airconsubtotal).setFontWeight("bold");231 var main_temp = temp_sheet.getRange(ur+25,1,1,1);232 var maint_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,1); 233 main_temp.copyTo(maint_row);234 var beginrow = output_sheet.getLastRow()+2;235 var counter=beginrow;236 var invitem_split=unitinvoiceitem.split('^^');237 var invdate_split=unitinvoicedate.split('^^');238 var unitamt_split=unitamount.split('^^');239 var unitdivamt_split=unitdivamount.split('^^');240 for (var u=0;u<parseInt(DDC_maintenancelength);u++)241 {242 output_sheet.getRange(counter,1).setValue(invitem_split[u]);243 output_sheet.getRange(counter,2).setValue(invdate_split[u]);244 output_sheet.getRange(counter,3).setValue(unitamt_split[u]); 245 output_sheet.getRange(counter,4).setValue(unitdivamt_split[u]); 246 counter++ 247 }248 if(checkout_clean!='')249 {250 var checkout_clean_temp = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("Checkout Cleaning");251 var checkout_clean_row = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(checkout_clean);252 }253 if(drycleaning!="")254 {255 var dry_clean_temp = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,1).setValue("Curtain Dry Cleaning");256 var dry_clean_row = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(drycleaning);257 }258 var crgbeginrow = output_sheet.getLastRow()+1;259 var crgcounter=crgbeginrow;260 var chargtype_split=chargtype.split('^^');261 var chargamount_split=chargamount.split('^^');262 for ( var c=0;c<parseInt(DDC_chargelength);c++)263 {264 output_sheet.getRange(crgcounter,1).setValue(chargtype_split[c]).setFontWeight("bold");265 output_sheet.getRange(crgcounter,4).setValue(chargamount_split[c]); 266 crgcounter++;267 }268 var sub2_temp = temp_sheet.getRange(ur+33,1,1,4);269 var sub2_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,4);270 sub2_temp.copyTo(sub2_row);271 var sub2_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_unitsubtotal).setFontWeight("bold");272 var tded_temp = temp_sheet.getRange(ur+37,1,1,4);273 var tded_row = output_sheet.getRange(output_sheet.getLastRow()+2,1,1,4);274 tded_temp.copyTo(tded_row); 275 var tded_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_totalallsubtl);276 var refun_temp = temp_sheet.getRange(ur+38,1,1,4); 277 var refun_row = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,4);278 refun_temp.copyTo(refun_row);279 var refun_row1 = output_sheet.getRange(output_sheet.getLastRow(),4,1,1).setValue(DDC_tefundtotal);280 var end_row =output_sheet.getLastRow();281 var no_of_row = end_row-starting_row+1;282 output_sheet.getRange(starting_row,1,no_of_row,4).setBorder(true,true,true,true,true,true);283 output_sheet.getRange(starting_row,2,end_row,1).setHorizontalAlignment("left");284 var black_row1 = output_sheet.getRange(output_sheet.getLastRow()+1,1,1,4).setBackgroundColor('black');285 output_sheet.insertRows(output_sheet.getLastRow()+5, 50); 286 return 'success'; ...

Full Screen

Full Screen

transpose.js

Source:transpose.js Github

copy

Full Screen

...14 sheet.setActiveCell("P40").setFormula("=transpose(A16:AB18)");15 sheet.setActiveCell("S40").setFormula("=transpose(A19:AB21)");16 sheet.setActiveCell("V40").setFormula("=transpose(A22:AB24)");17 var newSheet = ss.insertSheet(newSheets[i]);18 var country = sheet.getRange("A40").getValue(); 19 var domain = sheet.getRange("A41").getValue();20 var type = sheet.getRange("A42").getValue();21 var dates = sheet.getRange("B40:B67").getValues();22 var values = sheet.getRange("C40:C67").getValues();23 var countryRange = newSheet.getRange("A2").setValue(country);24 var typeRange = newSheet.getRange("C2").setValue(type);25 var domainRange = newSheet.getRange("B2").setValue(domain);26 var domainPaste = newSheet.getRange("B3:B29");27 domainRange.copyTo(domainPaste);28 newSheet.getRange("D2:D29").setValues(dates);29 newSheet.getRange("E2:E29").setValues(values);30 var countrya = sheet.getRange("D40").getValue(); 31 var domaina = sheet.getRange("D41").getValue();32 var typea = sheet.getRange("D42").getValue();33 var datesa = sheet.getRange("E40:E67").getValues();34 var valuesa = sheet.getRange("F40:F67").getValues();35 newSheet.getRange("A30").setValue(countrya);36 var domainRangeA = newSheet.getRange("B30").setValue(domaina);37 var domainPasteA = newSheet.getRange("B31:B57");38 domainRangeA.copyTo(domainPasteA);39 newSheet.getRange("C30").setValue(typea);40 newSheet.getRange("D30:D57").setValues(datesa);41 newSheet.getRange("E30:E57").setValues(valuesa);42 var countryb = sheet.getRange("G40").getValue(); 43 var domainb = sheet.getRange("G41").getValue();44 var typeb = sheet.getRange("G42").getValue();45 var datesb = sheet.getRange("H40:H67").getValues();46 var valuesb = sheet.getRange("I40:I67").getValues();47 newSheet.getRange("A58").setValue(countryb);48 var domainRangeB = newSheet.getRange("B58").setValue(domainb);49 var domainPasteB = newSheet.getRange("B59:B85");50 domainRangeB.copyTo(domainPasteB);51 newSheet.getRange("C58").setValue(typeb);52 newSheet.getRange("D58:D85").setValues(datesb);53 newSheet.getRange("E58:E85").setValues(valuesb);54 var countryc = sheet.getRange("J40").getValue(); 55 var domainc = sheet.getRange("J41").getValue();56 var typec = sheet.getRange("J42").getValue();57 var datesc = sheet.getRange("K40:K67").getValues();58 var valuesc = sheet.getRange("L40:L67").getValues();59 newSheet.getRange("A86").setValue(countryc);60 var domainRangeC = newSheet.getRange("B86").setValue(domainc);61 var domainPasteC = newSheet.getRange("B87:B113");62 domainRangeC.copyTo(domainPasteC);63 newSheet.getRange("C86").setValue(typec);64 newSheet.getRange("D86:D113").setValues(datesc);65 newSheet.getRange("E86:E113").setValues(valuesc);66 var countryd = sheet.getRange("M40").getValue(); 67 var domaind = sheet.getRange("M41").getValue();68 var typed = sheet.getRange("M42").getValue();69 var datesd = sheet.getRange("N40:N67").getValues();70 var valuesd = sheet.getRange("O40:O67").getValues();71 newSheet.getRange("A114").setValue(countryd);72 var domainRangeD = newSheet.getRange("B114").setValue(domaind);73 var domainPasteD = newSheet.getRange("B115:B141");74 domainRangeD.copyTo(domainPasteD);75 newSheet.getRange("C114").setValue(typed);76 newSheet.getRange("D114:D141").setValues(datesd);77 newSheet.getRange("E114:E141").setValues(valuesd);78 var countrye = sheet.getRange("P40").getValue(); 79 var domaine = sheet.getRange("P41").getValue();80 var typee = sheet.getRange("P42").getValue();81 var datese = sheet.getRange("Q40:Q67").getValues();82 var valuese = sheet.getRange("R40:R67").getValues();83 newSheet.getRange("A142").setValue(countrye);84 var domainRangeE = newSheet.getRange("B142").setValue(domaine);85 var domainPasteE = newSheet.getRange("B143:B169");86 domainRangeE.copyTo(domainPasteE);87 newSheet.getRange("C142").setValue(typee);88 newSheet.getRange("D142:D169").setValues(datese);89 newSheet.getRange("E142:E169").setValues(valuese); 90 var countryf = sheet.getRange("S40").getValue(); 91 var domainf = sheet.getRange("S41").getValue();92 var typef = sheet.getRange("S42").getValue();93 var datesf = sheet.getRange("T40:T67").getValues();94 var valuesf = sheet.getRange("U40:U67").getValues();95 newSheet.getRange("A170").setValue(countryf);96 var domainRangeF = newSheet.getRange("B170").setValue(domainf);97 var domainPasteF = newSheet.getRange("B171:B197");98 domainRangeF.copyTo(domainPasteF);99 newSheet.getRange("C170").setValue(typef);100 newSheet.getRange("D170:D197").setValues(datesf);101 newSheet.getRange("E170:E197").setValues(valuesf); 102 var countryg = sheet.getRange("V40").getValue(); 103 var domaing = sheet.getRange("V41").getValue();104 var typeg = sheet.getRange("V42").getValue();105 var datesg = sheet.getRange("W40:W67").getValues();106 var valuesg = sheet.getRange("X40:X67").getValues();107 newSheet.getRange("A198").setValue(countryg);108 var domainRangeG = newSheet.getRange("B198").setValue(domaing);109 var domainPasteG = newSheet.getRange("B199:B225");110 domainRangeG.copyTo(domainPasteG);111 newSheet.getRange("C198").setValue(typeg);112 newSheet.getRange("D198:D225").setValues(datesg);113 newSheet.getRange("E198:E225").setValues(valuesg); 114 var clear = newSheet.getRange("A3:A225");115 clear.clearContent();116 var countryPaste = newSheet.getRange("A3:A225");117 countryRange.copyTo(countryPaste);118 var clearagain = newSheet.getRange("C3:C225");119 clearagain.clearContent();120 var typePaste = newSheet.getRange("C3:C225");121 typeRange.copyTo(typePaste); 122 }123}124function transposeMAU() 125{126 var ss = SpreadsheetApp.getActiveSpreadsheet();127 var sheets = ['Desktop MAU', 'MobileWeb MAU'];128 var newSheets = ['DesktopMAU', 'Mobile MAU'];129 for(var i=0; i<sheets.length; i++) 130 {131 var sheet = ss.getSheetByName(sheets[i]);132 sheet.setActiveCell("A40").setFormula("=transpose(A1:M3)");133 sheet.setActiveCell("D40").setFormula("=transpose(A4:M6)");134 sheet.setActiveCell("G40").setFormula("=transpose(A7:M9)"); 135 sheet.setActiveCell("J40").setFormula("=transpose(A10:M12)"); 136 sheet.setActiveCell("M40").setFormula("=transpose(A13:M15)");137 sheet.setActiveCell("P40").setFormula("=transpose(A16:M18)");138 sheet.setActiveCell("S40").setFormula("=transpose(A19:M21)");139 sheet.setActiveCell("V40").setFormula("=transpose(A22:M24)");140 var newSheet = ss.insertSheet(newSheets[i]);141 var country = sheet.getRange("A40").getValue(); 142 var domain = sheet.getRange("A41").getValue();143 var type = sheet.getRange("A42").getValue();144 var dates = sheet.getRange("B40:B52").getValues();145 var values = sheet.getRange("C40:C52").getValues();146 var countryRange = newSheet.getRange("A2").setValue(country);147 var typeRange = newSheet.getRange("C2").setValue(type);148 var domainRange = newSheet.getRange("B2").setValue(domain);149 var domainPaste = newSheet.getRange("B3:B14");150 domainRange.copyTo(domainPaste);151 newSheet.getRange("D2:D14").setValues(dates);152 newSheet.getRange("E2:E14").setValues(values);153 var countrya = sheet.getRange("D40").getValue(); 154 var domaina = sheet.getRange("D41").getValue();155 var typea = sheet.getRange("D42").getValue();156 var datesa = sheet.getRange("E40:E52").getValues();157 var valuesa = sheet.getRange("F40:F52").getValues();158 newSheet.getRange("A15").setValue(countrya);159 var domainRangeA = newSheet.getRange("B15").setValue(domaina);160 var domainPasteA = newSheet.getRange("B16:B27");161 domainRangeA.copyTo(domainPasteA);162 newSheet.getRange("C15").setValue(typea);163 newSheet.getRange("D15:D27").setValues(datesa);164 newSheet.getRange("E15:E27").setValues(valuesa);165 var countryb = sheet.getRange("G40").getValue(); 166 var domainb = sheet.getRange("G41").getValue();167 var typeb = sheet.getRange("G42").getValue();168 var datesb = sheet.getRange("H40:H52").getValues();169 var valuesb = sheet.getRange("I40:I52").getValues();170 newSheet.getRange("A28").setValue(countryb);171 var domainRangeB = newSheet.getRange("B28").setValue(domainb);172 var domainPasteB = newSheet.getRange("B29:B40");173 domainRangeB.copyTo(domainPasteB);174 newSheet.getRange("C28").setValue(typeb);175 newSheet.getRange("D28:D40").setValues(datesb);176 newSheet.getRange("E28:E40").setValues(valuesb);177 var countryc = sheet.getRange("J40").getValue(); 178 var domainc = sheet.getRange("J41").getValue();179 var typec = sheet.getRange("J42").getValue();180 var datesc = sheet.getRange("K40:K52").getValues();181 var valuesc = sheet.getRange("L40:L52").getValues();182 newSheet.getRange("A41").setValue(countryc);183 var domainRangeC = newSheet.getRange("B41").setValue(domainc);184 var domainPasteC = newSheet.getRange("B42:B53");185 domainRangeC.copyTo(domainPasteC);186 newSheet.getRange("C41").setValue(typec);187 newSheet.getRange("D41:D53").setValues(datesc);188 newSheet.getRange("E41:E53").setValues(valuesc);189 var countryd = sheet.getRange("M40").getValue(); 190 var domaind = sheet.getRange("M41").getValue();191 var typed = sheet.getRange("M42").getValue();192 var datesd = sheet.getRange("N40:N52").getValues();193 var valuesd = sheet.getRange("O40:O52").getValues();194 newSheet.getRange("A54").setValue(countryd);195 var domainRangeD = newSheet.getRange("B54").setValue(domaind);196 var domainPasteD = newSheet.getRange("B55:B66");197 domainRangeD.copyTo(domainPasteD);198 newSheet.getRange("C54").setValue(typed);199 newSheet.getRange("D54:D66").setValues(datesd);200 newSheet.getRange("E54:E66").setValues(valuesd);201 var countrye = sheet.getRange("P40").getValue(); 202 var domaine = sheet.getRange("P41").getValue();203 var typee = sheet.getRange("P42").getValue();204 var datese = sheet.getRange("Q40:Q52").getValues();205 var valuese = sheet.getRange("R40:R52").getValues();206 newSheet.getRange("A67").setValue(countrye);207 var domainRangeE = newSheet.getRange("B67").setValue(domaine);208 var domainPasteE = newSheet.getRange("B68:B79");209 domainRangeE.copyTo(domainPasteE);210 newSheet.getRange("C67").setValue(typee);211 newSheet.getRange("D67:D79").setValues(datese);212 newSheet.getRange("E67:E79").setValues(valuese); 213 var countryf = sheet.getRange("S40").getValue(); 214 var domainf = sheet.getRange("S41").getValue();215 var typef = sheet.getRange("S42").getValue();216 var datesf = sheet.getRange("T40:T52").getValues();217 var valuesf = sheet.getRange("U40:U52").getValues();218 newSheet.getRange("A80").setValue(countryf);219 var domainRangeF = newSheet.getRange("B80").setValue(domainf);220 var domainPasteF = newSheet.getRange("B81:B92");221 domainRangeF.copyTo(domainPasteF);222 newSheet.getRange("C80").setValue(typef);223 newSheet.getRange("D80:D92").setValues(datesf);224 newSheet.getRange("E80:E92").setValues(valuesf); 225 var countryg = sheet.getRange("V40").getValue(); 226 var domaing = sheet.getRange("V41").getValue();227 var typeg = sheet.getRange("V42").getValue();228 var datesg = sheet.getRange("W40:W52").getValues();229 var valuesg = sheet.getRange("X40:X52").getValues();230 newSheet.getRange("A93").setValue(countryg);231 var domainRangeG = newSheet.getRange("B93").setValue(domaing);232 var domainPasteG = newSheet.getRange("B94:B105");233 domainRangeG.copyTo(domainPasteG);234 newSheet.getRange("C93").setValue(typeg);235 newSheet.getRange("D93:D105").setValues(datesg);236 newSheet.getRange("E93:E105").setValues(valuesg); 237 var clear = newSheet.getRange("A3:A105");238 clear.clearContent();239 var countryPaste = newSheet.getRange("A3:A105");240 countryRange.copyTo(countryPaste);241 var clearagain = newSheet.getRange("C3:C105");242 clearagain.clearContent();243 var typePaste = newSheet.getRange("C3:C105");244 typeRange.copyTo(typePaste);245 } ...

Full Screen

Full Screen

getStats.js

Source:getStats.js Github

copy

Full Screen

...22 let row = 0;23 playerStats.forEach((obj, i) => {24 row = parseInt(i + 2);25 cell = parseInt(1);26 spreadsheet.getRange(row, cell).setValue('');27 spreadsheet.getRange(row, cell++).setValue(i + 1);28 spreadsheet.getRange(row, cell).setValue('');29 spreadsheet.getRange(row, cell++).setValue(obj.nickname);30 spreadsheet.getRange(row, cell).setValue('');31 spreadsheet.getRange(row, cell++).setValue(obj.clan);32 spreadsheet.getRange(row, cell).setValue('');33 spreadsheet.getRange(row, cell++).setValue(obj.mainClass);34 spreadsheet.getRange(row, cell).setValue('');35 spreadsheet.getRange(row, cell++).setValue(obj.rank);36 spreadsheet.getRange(row, cell).setValue('');37 spreadsheet.getRange(row, cell++).setValue(obj.mmr);38 spreadsheet.getRange(row, cell).setValue('');39 spreadsheet.getRange(row, cell++).setValue(obj.played);40 spreadsheet.getRange(row, cell).setValue('');41 spreadsheet.getRange(row, cell++).setValue(obj.wins);42 spreadsheet.getRange(row, cell).setValue('');43 spreadsheet.getRange(row, cell++).setValue(obj.wr);44 spreadsheet.getRange(row, cell).setValue('');45 spreadsheet.getRange(row, cell++).setValue(obj.kda);46 spreadsheet.getRange(row, cell).setValue('');47 spreadsheet.getRange(row, cell++).setValue(obj.sr);48 spreadsheet.getRange(row, cell).setValue('');49 spreadsheet.getRange(row, cell++).setValue(obj.mvpr);50 spreadsheet.getRange(row, cell).setValue('');51 52 cell = parseInt(1);53 });54}55function setMainStats(spreadsheet, stats){56 const playerStats = stats.playerStats;57 let cell = parseInt(1);58 let row = 0;59 playerStats.forEach((obj, i) => {60 row = parseInt(i + 2);61 cell = parseInt(1);62 spreadsheet.getRange(row, cell).setValue('');63 spreadsheet.getRange(row, cell++).setValue(i + 1);64 spreadsheet.getRange(row, cell).setValue('');65 spreadsheet.getRange(row, cell++).setValue(obj.nickname);66 spreadsheet.getRange(row, cell).setValue('');67 spreadsheet.getRange(row, cell++).setValue(obj.country);68 spreadsheet.getRange(row, cell).setValue('');69 spreadsheet.getRange(row, cell++).setValue(obj.igl);70 spreadsheet.getRange(row, cell).setValue('');71 spreadsheet.getRange(row, cell++).setValue(obj.clan);72 spreadsheet.getRange(row, cell).setValue('');73 spreadsheet.getRange(row, cell++).setValue(obj.mainClass);74 spreadsheet.getRange(row, cell).setValue('');75 spreadsheet.getRange(row, cell++).setValue(obj.secondaryClass);76 spreadsheet.getRange(row, cell).setValue('');77 spreadsheet.getRange(row, cell++).setValue(obj.rank);78 spreadsheet.getRange(row, cell).setValue('');79 spreadsheet.getRange(row, cell++).setValue(obj.mmr);80 spreadsheet.getRange(row, cell).setValue('');81 spreadsheet.getRange(row, cell++).setValue(obj.played);82 spreadsheet.getRange(row, cell).setValue('');83 spreadsheet.getRange(row, cell++).setValue(obj.wins);84 spreadsheet.getRange(row, cell).setValue('');85 spreadsheet.getRange(row, cell++).setValue(obj.wr);86 spreadsheet.getRange(row, cell).setValue('');87 spreadsheet.getRange(row, cell++).setValue(obj.rounds);88 spreadsheet.getRange(row, cell).setValue('');89 spreadsheet.getRange(row, cell++).setValue(obj.kills);90 spreadsheet.getRange(row, cell).setValue('');91 spreadsheet.getRange(row, cell++).setValue(obj.kr);92 spreadsheet.getRange(row, cell).setValue('');93 spreadsheet.getRange(row, cell++).setValue(obj.deaths);94 spreadsheet.getRange(row, cell).setValue('');95 spreadsheet.getRange(row, cell++).setValue(obj.dr);96 spreadsheet.getRange(row, cell).setValue('');97 spreadsheet.getRange(row, cell++).setValue(obj.kd);98 spreadsheet.getRange(row, cell).setValue('');99 spreadsheet.getRange(row, cell++).setValue(obj.assists);100 spreadsheet.getRange(row, cell).setValue('');101 spreadsheet.getRange(row, cell++).setValue(obj.ar);102 spreadsheet.getRange(row, cell).setValue('');103 spreadsheet.getRange(row, cell++).setValue(obj.kar);104 spreadsheet.getRange(row, cell).setValue('');105 spreadsheet.getRange(row, cell++).setValue(obj.kda);106 spreadsheet.getRange(row, cell).setValue('');107 spreadsheet.getRange(row, cell++).setValue(obj.score);108 spreadsheet.getRange(row, cell).setValue('');109 spreadsheet.getRange(row, cell++).setValue(obj.sr);110 spreadsheet.getRange(row, cell).setValue('');111 spreadsheet.getRange(row, cell++).setValue(obj.mvp);112 spreadsheet.getRange(row, cell).setValue('');113 spreadsheet.getRange(row, cell++).setValue(obj.mvpr);114 spreadsheet.getRange(row, cell).setValue('');115 116 cell = parseInt(1);117 });118}119function setAdditionalTableStats(spreadsheet, startCell, startRow, stats){120 const iglStats = stats.iglStats;121 const divisionStats = stats.divisionStats;122 const factionStats = stats.factionStats;123 const topPlayerByClassStats = stats.topPlayerByClassStats;124 let row = startRow;125 let cell = startCell;126 const rowsTotal = 10;127 const archers = Object.keys(topPlayerByClassStats.archer);128 const infs = Object.keys(topPlayerByClassStats.infantry);129 const cavalry = Object.keys(topPlayerByClassStats.cavalry);130 for (var i = 0; i < rowsTotal; i++){131 let arch = archers[i];132 let inf = infs[i];133 let cav = cavalry[i];134 spreadsheet.getRange(row, cell).setValue('');135 spreadsheet.getRange(row, cell).setValue(i + 1);136 spreadsheet.getRange(row, cell + 1).setValue('');137 spreadsheet.getRange(row, cell + 2).setValue('');138 if (arch){139 spreadsheet.getRange(row, cell + 1).setValue(arch);140 spreadsheet.getRange(row, cell+ 2).setValue(topPlayerByClassStats.archer[arch]);141 } 142 spreadsheet.getRange(row, cell + 3).setValue('');143 spreadsheet.getRange(row, cell + 4).setValue('');144 if (inf){145 spreadsheet.getRange(row, cell + 3).setValue(inf);146 spreadsheet.getRange(row, cell + 4).setValue(topPlayerByClassStats.infantry[inf]);147 }148 spreadsheet.getRange(row, cell + 5).setValue('');149 spreadsheet.getRange(row, cell + 6).setValue('');150 if (cav){151 spreadsheet.getRange(row, cell + 5).setValue(cav);152 spreadsheet.getRange(row, cell + 6).setValue(topPlayerByClassStats.cavalry[cav]);153 }154 row++;155 }156 row += 2;157 const iglStatsIndices = 10;158 for (var i = 0; i < iglStatsIndices; i++){159 spreadsheet.getRange(row + i, cell).setValue('');160 spreadsheet.getRange(row + i, cell).setValue(i + 1);161 }162 Object.keys(iglStats).forEach((obj, i) => {163 spreadsheet.getRange(row + i, cell + 1).setValue('');164 spreadsheet.getRange(row + i, cell + 1).setValue(obj);165 spreadsheet.getRange(row + i, cell + 2).setValue('');166 spreadsheet.getRange(row + i, cell + 2).setValue(iglStats[obj]);167 })168 cell += 3;169 Object.keys(divisionStats).forEach((obj, i) => {170 spreadsheet.getRange(row + i, cell).setValue('');171 spreadsheet.getRange(row + i, cell).setValue(obj);172 spreadsheet.getRange(row + i, cell + 1).setValue('');173 spreadsheet.getRange(row + i, cell + 1).setValue(divisionStats[obj]);174 })175 cell += 2;176 Object.keys(factionStats).forEach((obj, i) => {177 spreadsheet.getRange(row + i, cell).setValue('');178 spreadsheet.getRange(row + i, cell).setValue(obj);179 spreadsheet.getRange(row + i, cell + 1).setValue('');180 spreadsheet.getRange(row + i, cell + 1).setValue(factionStats[obj]);181 })182}183function setPF(spreadsheet, stats){184 const playersByFactionStats = stats.playersByFactionStats;185 spreadsheet.deleteRows(2, spreadsheet.getLastRow() - 1);186 playersByFactionStats.forEach((obj, i) => {187 let row = parseInt(i + 2);188 let cell = 1;189 spreadsheet.getRange(row, cell).setValue('');190 spreadsheet.getRange(row, cell++).setValue(obj.nickname);191 spreadsheet.getRange(row, cell).setValue('');192 spreadsheet.getRange(row, cell++).setValue(obj.aseraiCount);193 spreadsheet.getRange(row, cell).setValue('');194 spreadsheet.getRange(row, cell++).setValue(obj.aseraiWR);195 spreadsheet.getRange(row, cell).setValue('');196 spreadsheet.getRange(row, cell++).setValue(obj.battaniaCount);197 spreadsheet.getRange(row, cell).setValue('');198 spreadsheet.getRange(row, cell++).setValue(obj.battaniaWR);199 spreadsheet.getRange(row, cell).setValue('');200 spreadsheet.getRange(row, cell++).setValue(obj.empireCount);201 spreadsheet.getRange(row, cell).setValue('');202 spreadsheet.getRange(row, cell++).setValue(obj.empireWR);203 spreadsheet.getRange(row, cell).setValue('');204 spreadsheet.getRange(row, cell++).setValue(obj.khuzaitCount);205 spreadsheet.getRange(row, cell).setValue('');206 spreadsheet.getRange(row, cell++).setValue(obj.khuzaitWR);207 spreadsheet.getRange(row, cell).setValue('');208 spreadsheet.getRange(row, cell++).setValue(obj.sturgiaCount);209 spreadsheet.getRange(row, cell).setValue('');210 spreadsheet.getRange(row, cell++).setValue(obj.sturgiaWR);211 spreadsheet.getRange(row, cell).setValue('');212 spreadsheet.getRange(row, cell++).setValue(obj.vlandiaCount);213 spreadsheet.getRange(row, cell).setValue('');214 spreadsheet.getRange(row, cell++).setValue(obj.vlandiaWR);215 })...

Full Screen

Full Screen

calendarScript.gs

Source:calendarScript.gs Github

copy

Full Screen

...12 var sheet= SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();13 monthNames = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];14 15 // Formatting Title of Calendar16 sheet.getRange('A1:G1').mergeAcross();17 sheet.getRange('A1').setValue(monthNames[month]);18 19 // Formats days of the Week20 sheet.getRange('A2').setValue('Sunday');21 sheet.getRange('B2').setValue('Monday');22 sheet.getRange('C2').setValue('Tuesday');23 sheet.getRange('D2').setValue('Wednesday');24 sheet.getRange('E2').setValue('Thursday');25 sheet.getRange('F2').setValue('Friday');26 sheet.getRange('G2').setValue('Saturday');27 28 // Adds dates. There are 4 rows in between each week29 // I would have done this part iteratively, but getRange required hard coded values. That or js did something really strange with strings.30 // First Calendar Row - Cleans up sheet, starts calendar on correct day of the week31 x=1;32 if (d.getDay()==0){33 sheet.getRange('A3').setValue(x++);34 }35 else{36 sheet.getRange('A3').clearContent();37 }38 if (d.getDay()<=1){39 sheet.getRange('B3').setValue(x++);40 }41 else{42 sheet.getRange('B3').clearContent();43 }44 if (d.getDay()<=2){45 sheet.getRange('C3').setValue(x++);46 }47 else{48 sheet.getRange('C3').clearContent();49 }50 if (d.getDay()<=3){51 sheet.getRange('D3').setValue(x++);52 }53 else{54 sheet.getRange('D3').clearContent();55 }56 if (d.getDay()<=4){57 sheet.getRange('E3').setValue(x++);58 }59 else{60 sheet.getRange('E3').clearContent();61 }62 if (d.getDay()<=5){63 sheet.getRange('F3').setValue(x++);64 }65 else{66 sheet.getRange('F3').clearContent();67 }68 if (d.getDay()<=6){69 sheet.getRange('G3').setValue(x++);70 }71 else{72 sheet.getRange('G3').clearContent();73 }74 75 // Second Calendar Row76 sheet.getRange('A8').setValue(x++);77 sheet.getRange('B8').setValue(x++);78 sheet.getRange('C8').setValue(x++);79 sheet.getRange('D8').setValue(x++);80 sheet.getRange('E8').setValue(x++);81 sheet.getRange('F8').setValue(x++);82 sheet.getRange('G8').setValue(x++);83 84 // Third Calendar Row85 sheet.getRange('A13').setValue(x++);86 sheet.getRange('B13').setValue(x++);87 sheet.getRange('C13').setValue(x++);88 sheet.getRange('D13').setValue(x++);89 sheet.getRange('E13').setValue(x++);90 sheet.getRange('F13').setValue(x++);91 sheet.getRange('G13').setValue(x++);92 93 // Fourth Calendar Row94 sheet.getRange('A18').setValue(x++);95 sheet.getRange('B18').setValue(x++);96 sheet.getRange('C18').setValue(x++);97 sheet.getRange('D18').setValue(x++);98 sheet.getRange('E18').setValue(x++);99 sheet.getRange('F18').setValue(x++);100 sheet.getRange('G18').setValue(x++);101 102 // Fifth Calendar Row - Adds dates, if there are more. Cleans up calendar too103 if (x>daysInMonth){104 sheet.getRange('A23').clearContent();105 }106 else{107 sheet.getRange('A23').setValue(x++);108 }109 110 if (x>daysInMonth){111 sheet.getRange('B23').clearContent();112 }113 else{114 sheet.getRange('B23').setValue(x++);115 }116 117 if (x>daysInMonth){118 sheet.getRange('C23').clearContent();119 }120 else{121 sheet.getRange('C23').setValue(x++);122 }123 124 if (x>daysInMonth){125 sheet.getRange('D23').clearContent();126 }127 else{128 sheet.getRange('D23').setValue(x++);129 }130 131 if (x>daysInMonth){132 sheet.getRange('E23').clearContent();133 }134 else{135 sheet.getRange('E23').setValue(x++);136 }137 138 if (x>daysInMonth){139 sheet.getRange('F23').clearContent();140 }141 else{142 sheet.getRange('F23').setValue(x++);143 }144 145 if (x>daysInMonth){146 sheet.getRange('G23').clearContent();147 }148 else{149 sheet.getRange('G23').setValue(x++);150 }151 152 // Sixth Calendar Row153 if (x>daysInMonth){154 sheet.getRange('A28').clearContent();155 }156 else{157 sheet.getRange('A28').setValue(x++);158 }159 160 if (x>daysInMonth){161 sheet.getRange('B28').clearContent();162 }163 else{164 sheet.getRange('B28').setValue(x++);165 }166 167 if (x>daysInMonth){168 sheet.getRange('C28').clearContent();169 }170 else{171 sheet.getRange('C28').setValue(x++);172 }173 174 if (x>daysInMonth){175 sheet.getRange('D28').clearContent();176 }177 else{178 sheet.getRange('D28').setValue(x++);179 }180 181 if (x>daysInMonth){182 sheet.getRange('E28').clearContent();183 }184 else{185 sheet.getRange('E28').setValue(x++);186 }187 188 if (x>daysInMonth){189 sheet.getRange('F28').clearContent();190 }191 else{192 sheet.getRange('F28').setValue(x++);193 }194 195 if (x>daysInMonth){196 sheet.getRange('G28').clearContent();197 }198 else{199 sheet.getRange('G28').setValue(x++);200 }201 202 return;...

Full Screen

Full Screen

getRange.spec.js

Source:getRange.spec.js Github

copy

Full Screen

1import test from 'ava';2import bigmoment from '../bigmoment';3test('#getRange years', t => {4 t.is(bigmoment('2').getRange('years'), 1000 );5 t.is(bigmoment('20').getRange('years'), 100 );6 t.is(bigmoment('201').getRange('years'), 10 );7 t.is(bigmoment('2018').getRange('years'), 1 );8 t.is(bigmoment('2016-02').getRange('years'), 1);9 t.is(bigmoment('2016-W01').getRange('years'), 1);10 t.is(bigmoment('2018-11-11').getRange('years'), 1);11 t.is(bigmoment('2018-11-11 22').getRange('years'), 1);12 t.is(bigmoment('2018-11-11 22:11').getRange('years'), 1);13 t.is(bigmoment('2018-11-11 22:11:11').getRange('years'), 1);14});15test('#getRange months', t => {16 t.is(bigmoment('2').getRange('months'), 1000 * 12);17 t.is(bigmoment('20').getRange('months'), 100 * 12);18 t.is(bigmoment('201').getRange('months'), 10 * 12 );19 t.is(bigmoment('2018').getRange('months'), 12 );20 t.is(bigmoment('2016-02').getRange('months'), 1);21 t.is(bigmoment('2016-W01').getRange('months'), 1);22 t.is(bigmoment('2018-11-11').getRange('months'), 1);23 t.is(bigmoment('2018-11-11 22').getRange('months'), 1);24 t.is(bigmoment('2018-11-11 22:11').getRange('months'), 1);25 t.is(bigmoment('2018-11-11 22:11:11').getRange('months'), 1);26});27test('#getRange weeks', t => {28 t.is(bigmoment('2').getRange('weeks'), 52178);29 t.is(bigmoment('20').getRange('weeks'), 5218);30 t.is(bigmoment('201').getRange('weeks'), 522);31 t.is(bigmoment('2018').getRange('weeks'), 53 );32 t.is(bigmoment('2016-02').getRange('weeks'), 5);33 t.is(bigmoment('2016-W01').getRange('weeks'), 1);34 t.is(bigmoment('2018-11-11').getRange('weeks'), 1);35 t.is(bigmoment('2018-11-11 22').getRange('weeks'), 1);36 t.is(bigmoment('2018-11-11 22:11').getRange('weeks'), 1);37 t.is(bigmoment('2018-11-11 22:11:11').getRange('weeks'), 1);38});39test('#getRange days', t => {40 t.is(bigmoment('2').getRange('days'), 365243);41 t.is(bigmoment('20').getRange('days'), 36525);42 t.is(bigmoment('201').getRange('days'), 3652);43 t.is(bigmoment('2018').getRange('days'), 365);44 t.is(bigmoment('2016-02').getRange('days'), 29);45 t.is(bigmoment('2016-W01').getRange('days'), 7);46 t.is(bigmoment('2018-11-11').getRange('days'), 1);47 t.is(bigmoment('2018-11-11 22').getRange('days'), 1);48 t.is(bigmoment('2018-11-11 22:11').getRange('days'), 1);49 t.is(bigmoment('2018-11-11 22:11:11').getRange('days'), 1);50});51test('#getRange hours', t => {52 t.is(bigmoment('2').getRange('hours'), 365243 * 24);53 t.is(bigmoment('20').getRange('hours'), 36525 * 24);54 t.is(bigmoment('201').getRange('hours'), 3652 * 24);55 t.is(bigmoment('2018').getRange('hours'), 365 * 24);56 t.is(bigmoment('2018-11').getRange('hours'), 30 * 24);57 t.is(bigmoment('2016-W01').getRange('hours'), 7 * 24);58 t.is(bigmoment('2018-11-11').getRange('hours'), 24);59 t.is(bigmoment('2018-11-11 22').getRange('hours'), 1);60 t.is(bigmoment('2018-11-11 22:11').getRange('hours'), 1);61 t.is(bigmoment('2018-11-11 22:11:11').getRange('hours'), 1);62});63test('#getRange minutes', t => {64 t.is(bigmoment('2').getRange('minutes'), 365243 * 24 * 60);65 t.is(bigmoment('20').getRange('minutes'), 36525 * 24 * 60);66 t.is(bigmoment('201').getRange('minutes'), 3652 * 24 * 60);67 t.is(bigmoment('2018').getRange('minutes'), 365 * 24 * 60);68 t.is(bigmoment('2018-11').getRange('minutes'), 30 * 24 * 60);69 t.is(bigmoment('2016-W01').getRange('minutes'), 7 * 24 * 60);70 t.is(bigmoment('2018-11-11').getRange('minutes'), 24 * 60);71 t.is(bigmoment('2018-11-11 22').getRange('minutes'), 60);72 t.is(bigmoment('2018-11-11 22:11').getRange('minutes'), 1);73 t.is(bigmoment('2018-11-11 22:11:11').getRange('minutes'), 1);74});75test('#getRange seconds', t => {76 t.is(bigmoment('2').getRange('seconds'), 365243 * 24 * 60 * 60);77 t.is(bigmoment('20').getRange('seconds'), 36525 * 24 * 60 * 60);78 t.is(bigmoment('201').getRange('seconds'), 3652 * 24 * 60 * 60);79 t.is(bigmoment('2018').getRange('seconds'), 365 * 24 * 60 * 60);80 t.is(bigmoment('2018-11').getRange('seconds'), 30 * 24 * 60 * 60);81 t.is(bigmoment('2016-W01').getRange('seconds'), 7 * 24 * 60 * 60);82 t.is(bigmoment('2018-11-11').getRange('seconds'), 24 * 60 * 60);83 t.is(bigmoment('2018-11-11 22').getRange('seconds'), 60 * 60);84 t.is(bigmoment('2018-11-11 22:11').getRange('seconds'), 60);85 t.is(bigmoment('2018-11-11 22:11:11').getRange('seconds'), 1);...

Full Screen

Full Screen

Using AI Code Generation

copy

Full Screen

1describe('My First Test', function() {2 it('Does not do much!', function() {3 cy.contains('get').click()4 cy.url().should('include', '/commands/actions')5 cy.get('.action-email')6 .get('[type="email"]')7 .type('

Full Screen

Full Screen

Using AI Code Generation

copy

Full Screen

1cy.get('input[name="username"]').type('test')2cy.get('input[name="password"]').type('test')3cy.get('button[type="submit"]').click()4cy.get('input[name="username"]').type('test')5cy.get('input[name="password"]').type('test')6cy.get('button[type="submit"]').click()7cy.get('input[name="username"]').type('test')8cy.get('input[name="password"]').type('test')9cy.get('button[type="submit"]').click()10cy.get('input[name="username"]').type('test')11cy.get('input[name="password"]').type('test')12cy.get('button[type="submit"]').click()13cy.get('input[name="username"]').type('test')14cy.get('input[name="password"]').type('test')15cy.get('button[type="submit"]').click()16cy.get('input[name="username"]').type('test')17cy.get('input[name="password"]').type('test')18cy.get('button[type="submit"]').click()19cy.get('input[name="username"]').type('test')20cy.get('input[name="password"]').type('test')21cy.get('button[type="submit"]').click()22cy.get('input[name="username"]').type('test')23cy.get('input[name="password"]').type('test')24cy.get('button[type="submit"]').click()25cy.get('input[name="username"]').type('test')26cy.get('input[name="password"]').type('test')27cy.get('button[type="submit"]').click()28cy.get('input[name="username"]').type('test')29cy.get('input[name="password"]').type('test')30cy.get('button[type="submit"]').click()31cy.get('input[name="username"]').type('test')32cy.get('input[name="password"]').type('test')33cy.get('button[type="submit"]').click()34cy.get('input[name="username"]').type('test')35cy.get('

Full Screen

Full Screen

Using AI Code Generation

copy

Full Screen

1var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cypress");2var range = sheet.getRange("A2:A");3var values = range.getValues();4var data = values.map(function(row) {5 return row[0];6});7Logger.log(data);8var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cypress");9var range = sheet.getRange("A2:A");10var values = range.getValues();11var data = values.map(function(row) {12 return row[0];13});14Logger.log(data);15var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cypress");16var range = sheet.getRange("A2:A");17var values = range.getValues();18var data = values.map(function(row) {19 return row[0];20});21Logger.log(data);22var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cypress");23var range = sheet.getRange("A2:A");24var values = range.getValues();25var data = values.map(function(row) {26 return row[0];27});28Logger.log(data);29var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cypress");30var range = sheet.getRange("A2:A");31var values = range.getValues();32var data = values.map(function(row) {33 return row[0];34});35Logger.log(data);36var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cypress");37var range = sheet.getRange("A2:A");38var values = range.getValues();39var data = values.map(function(row) {40 return row[0];41});42Logger.log(data);43var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cypress");44var range = sheet.getRange("A2:A");45var values = range.getValues();46var data = values.map(function(row) {47 return row[0];48});49Logger.log(data);50var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cypress");51var range = sheet.getRange("A2:A");52var values = range.getValues();53var data = values.map(function(row) {

Full Screen

Full Screen

Using AI Code Generation

copy

Full Screen

1describe("getRange", () => {2 it("should get the range of the selected element", () => {3 cy.get("h1").should($h1 => {4 const range = $h1.getRange();5 expect(range).to.be.an.instanceof(Range);6 });7 });8});

Full Screen

Full Screen

Using AI Code Generation

copy

Full Screen

1cy.get('table').get('tr').get('td').getRange(1, 4).each(($el, index, $list) => {2 cy.log($el.text())3})4cy.get('table').get('tr').get('td').getRange(1, 4).each(($el, index, $list) => {5 cy.log($el.text())6})7cy.get('table').get('tr').get('td').getRange(1, 4).each(($el, index, $list) => {8 cy.log($el.text())9})10cy.get('table').get('tr').get('td').getRange(1, 4).each(($el, index, $list) => {11 cy.log($el.text())12})13cy.get('table').get('tr').get('td').getRange(1, 4).each(($el, index, $list) => {14 cy.log($el.text())15})16cy.get('table').get('tr').get('td').getRange(1, 4).each(($el, index, $list) => {17 cy.log($el.text())18})19cy.get('table').get('tr').get('td').getRange(1, 4).each(($el, index, $list) => {20 cy.log($el.text())21})22cy.get('table').get('tr').get('

Full Screen

Full Screen

Using AI Code Generation

copy

Full Screen

1Cypress.$(document).ready(() => {2 const range = Cypress.$(document).getRange();3});4cy.get('body').then(($body) => {5 const range = $body.getRange();6});

Full Screen

Full Screen

Using AI Code Generation

copy

Full Screen

1cy.get('table').find('tr').find('td').eq(0).then(($td) => {2 const range = $td.text()3 const rangeArray = range.split('-')4 const min = parseInt(rangeArray[0])5 const max = parseInt(rangeArray[1])6 const randomNumber = Math.floor(Math.random() * (max - min + 1)) + min7 cy.log(randomNumber)8})9cy.get('table').find('tr').find('td').eq(1).then(($td) => {10 const range = $td.text()11 const rangeArray = range.split('-')12 const min = parseInt(rangeArray[0])13 const max = parseInt(rangeArray[1])14 const randomNumber = Math.floor(Math.random() * (max - min + 1)) + min15 cy.log(randomNumber)16})17cy.get('table').find('tr').find('td').eq(2).then(($td) => {18 const range = $td.text()19 const rangeArray = range.split('-')20 const min = parseInt(rangeArray[0])21 const max = parseInt(rangeArray[1])22 const randomNumber = Math.floor(Math.random() * (max - min + 1)) + min23 cy.log(randomNumber)24})

Full Screen

Full Screen

Using AI Code Generation

copy

Full Screen

1cy.get('table > tbody > tr')2 .getRange(0, 2)3 .forEach((row) => {4 cy.log(row)5 })6cy.get('table > tbody > tr')7 .getRange(0, 2)8 .each((row) => {9 cy.log(row)10 })11cy.get('table > tbody > tr')12 .getRange(0, 2)13 .each((row) => {14 cy.log(row)15 })16cy.get('table > tbody > tr')17 .getRange(0, 2)18 .each((row) => {19 cy.log(row)20 })21cy.get('table > tbody > tr')22 .getRange(0, 2)23 .each((row) => {24 cy.log(row)25 })26cy.get('table > tbody > tr')27 .getRange(0, 2)28 .each((row) => {29 cy.log(row)30 })31cy.get('table > tbody > tr')32 .getRange(0,

Full Screen

Full Screen

Using AI Code Generation

copy

Full Screen

1cy.get('input[type='range']').invoke('val', 25)2cy.get('input[type='range']').invoke('val', 25)3cy.get('input[type='range']').invoke('val', 25)4cy.get('input[type='range']').invoke('val', 25)5cy.get('input[type='range']').invoke('val', 25)6cy.get('input[type='range']').invoke('val', 25)7cy.get('input[type='range']').invoke('val', 25)8cy.get('input[type='range']').invoke('val', 25)9cy.get('input[type='range']').invoke('val', 25)10cy.get('input[type='range']').invoke('val', 25)

Full Screen

Full Screen

Cypress Tutorial

Cypress is a renowned Javascript-based open-source, easy-to-use end-to-end testing framework primarily used for testing web applications. Cypress is a relatively new player in the automation testing space and has been gaining much traction lately, as evidenced by the number of Forks (2.7K) and Stars (42.1K) for the project. LambdaTest’s Cypress Tutorial covers step-by-step guides that will help you learn from the basics till you run automation tests on LambdaTest.

Chapters:

  1. What is Cypress? -
  2. Why Cypress? - Learn why Cypress might be a good choice for testing your web applications.
  3. Features of Cypress Testing - Learn about features that make Cypress a powerful and flexible tool for testing web applications.
  4. Cypress Drawbacks - Although Cypress has many strengths, it has a few limitations that you should be aware of.
  5. Cypress Architecture - Learn more about Cypress architecture and how it is designed to be run directly in the browser, i.e., it does not have any additional servers.
  6. Browsers Supported by Cypress - Cypress is built on top of the Electron browser, supporting all modern web browsers. Learn browsers that support Cypress.
  7. Selenium vs Cypress: A Detailed Comparison - Compare and explore some key differences in terms of their design and features.
  8. Cypress Learning: Best Practices - Take a deep dive into some of the best practices you should use to avoid anti-patterns in your automation tests.
  9. How To Run Cypress Tests on LambdaTest? - Set up a LambdaTest account, and now you are all set to learn how to run Cypress tests.

Certification

You can elevate your expertise with end-to-end testing using the Cypress automation framework and stay one step ahead in your career by earning a Cypress certification. Check out our Cypress 101 Certification.

YouTube

Watch this 3 hours of complete tutorial to learn the basics of Cypress and various Cypress commands with the Cypress testing at LambdaTest.

Run Cypress automation tests on LambdaTest cloud grid

Perform automation testing on 3000+ real desktop and mobile devices online.

... View sample repo... View DocumentationSign up Free
›
_

Try LambdaTest Now !!

Get 100 minutes of automation test minutes FREE!!

...
Sign up with GoogleSign up with Email
Next-Gen App & Browser Testing Cloud

Was this article helpful?

Helpful

NotHelpful