API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 28th, 2017
509
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 18.89 KB | None | 0 0
raw download clone embed print report
  1. //When a user opens their sheet, this happens
  2. function onOpen(e){
  3. //List of Variables
  4. var ui = SpreadsheetApp.getUi();
  5.  
  6. //Creating Menus
  7. ui.createMenu('Useful Information')
  8. .addItem('Your Sheet', 'gotoUser')
  9. .addItem('Info', 'gotoInfo')
  10. .addItem('Leaderboards', 'gotoLeaderboards')
  11. .addSeparator()
  12. .addItem('Updates', 'showUpdates')
  13. .addSeparator()
  14. .addItem('Report an issue', 'reportIssue')
  15. .addToUi();
  16. }
  17.  
  18. //Goes to the User's Main Sheet
  19. function gotoUser(){
  20. var ws = SpreadsheetApp.getActiveSpreadsheet();
  21. var user = ws.getSheets()[4];
  22. var active = user.getRange(3, 3);
  23. SpreadsheetApp.setActiveSheet(user);
  24. SpreadsheetApp.setActiveRange(active);
  25. }
  26.  
  27. //Goes to the "Info" Sheet
  28. function gotoInfo(){
  29. var ws = SpreadsheetApp.getActiveSpreadsheet();
  30. var info = ws.getSheets()[5];
  31. var active = info.getRange(4, 3);
  32. SpreadsheetApp.setActiveSheet(info);
  33. SpreadsheetApp.setActiveRange(active);
  34. }
  35.  
  36. //Goes to the "Leaderboards" Sheet
  37. function gotoLeaderboards(){
  38. var ws = SpreadsheetApp.getActiveSpreadsheet();
  39. SpreadsheetApp.setActiveSheet(ws.getSheets()[6]);
  40. }
  41.  
  42. //Shows available updates (treat this like a changelog)
  43. function showUpdates(){
  44. var html = HtmlService.createHtmlOutputFromFile('Test')
  45. .setTitle('Updates')
  46. .setWidth(300);
  47. SpreadsheetApp.getUi()
  48. .showSidebar(html);
  49. }
  50.  
  51. //Reports an issue to QuitarHero
  52. function reportIssue(){
  53. var ui = SpreadsheetApp.getUi();
  54. var prompt1 = ui.prompt(
  55. 'Bug Report',
  56. 'What seems to be the issue?:',
  57. ui.ButtonSet.OK_CANCEL);
  58. var button1 = prompt1.getSelectedButton();
  59. var text1 = prompt1.getResponseText();
  60.  
  61. if(button1 == ui.Button.OK){
  62. MailApp.sendEmail("Quitar@ymail.com", "Raid Sheet Bug Report", text1);
  63. ui.alert("Thank you for your report. QuitarHero will look into it when he has time. I apologise for the inconvenience.");
  64. }
  65. }
  66.  
  67. //Does all kinds of fun stuff whenever a user does something
  68. function onEdit(e){
  69. //List of Variables
  70. //Sheet Variables
  71. var ws = SpreadsheetApp.getActiveSpreadsheet();
  72. var wsActive = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName();
  73. var ref = ws.getSheets()[0];
  74. var build = ws.getSheets()[1];
  75. var mech = ws.getSheets()[2];
  76. var avg = ws.getSheets()[3];
  77. var user = ws.getSheets()[4];
  78. var info = ws.getSheets()[5];
  79. //Storage Variables
  80. var correctBuild = 0;
  81. var correctBoss = 0;
  82. var buildCount = 0;
  83. var bossCount = 0;
  84. var maxMech = 0;
  85.  
  86. //Determining the MaxBuilds and MaxBosses
  87. for(var i=1; i<=1000; i++){
  88. if(ref.getRange(i, 1).isBlank() != true){
  89. buildCount++;
  90. }
  91. if(ref.getRange(i, 4).isBlank() != true){
  92. bossCount++;
  93. }
  94. if(ref.getRange(i, 1).isBlank() == true && ref.getRange(i, 4).isBlank() == true){
  95. break;
  96. }
  97. }
  98.  
  99. //IF THE CURRENTLY ACTIVE SHEET IS THE USER'S MAIN SHEET
  100. if(wsActive == "Hadmas"){
  101.  
  102. //Column Variables
  103. var buildList = ref.getRange("A:A");
  104. var bossList = ref.getRange("D:D");
  105. //Storage Variables
  106. var weightMech = 0;
  107. var valueToChange = 0;
  108. //Comparison Variables
  109. var dpsCompare = 0;
  110. var critCompare = 0;
  111. var movCompare = 0;
  112. var schCompare = 0;
  113. var downCompare = 0;
  114.  
  115. //Finding the Correct Build
  116. for(var i=1; i<=buildCount; i++){
  117. correctBuild = ref.getRange(i, 1).getValue();
  118. if(correctBuild == user.getRange(3, 3).getValue()){
  119. correctBuild = i;
  120. break;
  121. }
  122. }
  123.  
  124. //Finding the Correct Boss
  125. for(var i=1; i<=bossCount; i++){
  126. correctBoss = ref.getRange(i, 4).getValue();
  127. if(correctBoss == user.getRange(12, 3).getValue()){
  128. correctBoss = i;
  129. break;
  130. }
  131. }
  132.  
  133. //Finding the maximum amount of mechanics a boss has.
  134. for(var i=1; i<=30; i++){
  135. if(ref.getRange(i, 5+correctBoss).isBlank() != true){
  136. maxMech++;
  137. }else{
  138. break;
  139. }
  140. }
  141.  
  142. //Displaying Build Data
  143. for(var i=4; i<=9; i++){
  144. if(i < 8){
  145. build.getRange(correctBuild+4, i-1).copyTo(user.getRange(i, 3), {contentsOnly:true});
  146. }else if(i == 9){
  147. build.getRange(correctBuild+4, i-2).copyTo(user.getRange(i, 3), {contentsOnly:true});
  148. }
  149. }
  150.  
  151. //Clearing Boss Data
  152. for(var i=13; i<29; i++){
  153. if(i >= 18){
  154. user.getRange(i, 2).clear({contentsOnly:true});
  155. }
  156. user.getRange(i, 3).clear({contentsOnly:true});
  157. user.getRange(i, 4).clear({contentsOnly:true});
  158. }
  159.  
  160. //Displaying Boss Data
  161. for(var i=13; i<(18+maxMech); i++){
  162. //Build Average Data
  163. if(i < 18){
  164. if(correctBoss <= 5){
  165. if(i == 13){
  166. dpsCompare = avg.getRange(3+correctBuild, (correctBoss*35)-32).getValue();
  167. critCompare = avg.getRange(3+correctBuild, (correctBoss*35)-25).getValue();
  168. movCompare = avg.getRange(3+correctBuild, (correctBoss*35)-18).getValue();
  169. schCompare = avg.getRange(3+correctBuild, (correctBoss*35)-11).getValue();
  170. downCompare = avg.getRange(3+correctBuild, (correctBoss*35)-4).getValue();
  171. }
  172. avg.getRange(3+correctBuild, (correctBoss*35)-39+((i-12)*7)).copyTo(user.getRange(i, 3), {contentsOnly:true});
  173. }
  174. if(correctBoss > 5 && correctBoss <= 10){
  175. if(i == 13){
  176. dpsCompare = avg.getRange(3+correctBuild, ((correctBoss-5)*35)-32).getValue();
  177. critCompare = avg.getRange(3+correctBuild, ((correctBoss-5)*35)-25).getValue();
  178. movCompare = avg.getRange(3+correctBuild, ((correctBoss-5)*35)-18).getValue();
  179. schCompare = avg.getRange(3+correctBuild, ((correctBoss-5)*35)-11).getValue();
  180. downCompare = avg.getRange(3+correctBuild, ((correctBoss-5)*35)-4).getValue();
  181. }
  182. avg.getRange(49+correctBuild, ((correctBoss-5)*35)-39+((i-12)*7)).copyTo(user.getRange(i, 3), {contentsOnly:true});
  183. }
  184. if(correctBoss > 10 && correctBoss <= 13){
  185. if(i == 13){
  186. dpsCompare = avg.getRange(3+correctBuild, ((correctBoss-10)*35)-32).getValue();
  187. critCompare = avg.getRange(3+correctBuild, ((correctBoss-10)*35)-25).getValue();
  188. movCompare = avg.getRange(3+correctBuild, ((correctBoss-10)*35)-18).getValue();
  189. schCompare = avg.getRange(3+correctBuild, ((correctBoss-10)*35)-11).getValue();
  190. downCompare = avg.getRange(3+correctBuild, ((correctBoss-10)*35)-4).getValue();
  191. }
  192. avg.getRange(95+correctBuild, ((correctBoss-10)*35)-39+((i-12)*7)).copyTo(user.getRange(i, 3), {contentsOnly:true});
  193. }
  194. //Weighted Build Average Data
  195. if(i == 17){
  196. dpsCompare = (dpsCompare / ref.getRange(correctBuild, 3).getValue())*100;
  197. switch(correctBoss){
  198.  
  199. //Vale Guardian
  200. case 1:
  201. if(dpsCompare >= 40 && dpsCompare <= 65){
  202. user.getRange(13, 4).setValue(0);
  203. }
  204. if(dpsCompare < 40){
  205. dpsCompare = (dpsCompare - 40) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  206. user.getRange(13, 4).setValue(dpsCompare);
  207. }
  208. if(dpsCompare > 65){
  209. dpsCompare = (dpsCompare - 65) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  210. user.getRange(13, 4).setValue(dpsCompare);
  211. }
  212. break;
  213.  
  214. //Gorseval
  215. case 2:
  216. if(dpsCompare >= 50 && dpsCompare <= 70){
  217. user.getRange(13, 4).setValue(0);
  218. }
  219. if(dpsCompare < 50){
  220. dpsCompare = (dpsCompare - 50) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  221. user.getRange(13, 4).setValue(dpsCompare);
  222. }
  223. if(dpsCompare > 70){
  224. dpsCompare = (dpsCompare - 70) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  225. user.getRange(13, 4).setValue(dpsCompare);
  226. }
  227. break;
  228.  
  229. //Sabetha
  230. case 3:
  231. if(dpsCompare >= 40 && dpsCompare <= 70){
  232. user.getRange(13, 4).setValue(0);
  233. }
  234. if(dpsCompare < 40){
  235. dpsCompare = (dpsCompare - 40) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  236. user.getRange(13, 4).setValue(dpsCompare);
  237. }
  238. if(dpsCompare > 70){
  239. dpsCompare = (dpsCompare - 70) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  240. user.getRange(13, 4).setValue(dpsCompare);
  241. }
  242. break;
  243.  
  244. //Slothasor
  245. case 4:
  246. if(dpsCompare >= 40 && dpsCompare <= 60){
  247. user.getRange(13, 4).setValue(0);
  248. }
  249. if(dpsCompare < 40){
  250. dpsCompare = (dpsCompare - 40) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  251. user.getRange(13, 4).setValue(dpsCompare);
  252. }
  253. if(dpsCompare > 60){
  254. dpsCompare = (dpsCompare - 60) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  255. user.getRange(13, 4).setValue(dpsCompare);
  256. }
  257. break;
  258.  
  259. //Matthias
  260. case 6:
  261. if(dpsCompare >= 35 && dpsCompare <= 55){
  262. user.getRange(13, 4).setValue(0);
  263. }
  264. if(dpsCompare < 35){
  265. dpsCompare = (dpsCompare - 35) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  266. user.getRange(13, 4).setValue(dpsCompare);
  267. }
  268. if(dpsCompare > 55){
  269. dpsCompare = (dpsCompare - 55) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  270. user.getRange(13, 4).setValue(dpsCompare);
  271. }
  272. break;
  273.  
  274. //Keep's Construct
  275. case 8:
  276. if(dpsCompare >= 55 && dpsCompare <= 75){
  277. user.getRange(13, 4).setValue(0);
  278. }
  279. if(dpsCompare < 55){
  280. dpsCompare = (dpsCompare - 55) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  281. user.getRange(13, 4).setValue(dpsCompare);
  282. }
  283. if(dpsCompare > 75){
  284. dpsCompare = (dpsCompare - 75) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  285. user.getRange(13, 4).setValue(dpsCompare);
  286. }
  287. break;
  288.  
  289. //Xera
  290. case 9:
  291. if(dpsCompare >= 45 && dpsCompare <= 70){
  292. user.getRange(13, 4).setValue(0);
  293. }
  294. if(dpsCompare < 45){
  295. dpsCompare = (dpsCompare - 45) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  296. user.getRange(13, 4).setValue(dpsCompare);
  297. }
  298. if(dpsCompare > 70){
  299. dpsCompare = (dpsCompare - 70) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  300. user.getRange(13, 4).setValue(dpsCompare);
  301. }
  302. break;
  303.  
  304. //Cairn
  305. case 10:
  306. if(dpsCompare >= 45 && dpsCompare <= 70){
  307. user.getRange(13, 4).setValue(0);
  308. }
  309. if(dpsCompare < 45){
  310. dpsCompare = (dpsCompare - 45) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  311. user.getRange(13, 4).setValue(dpsCompare);
  312. }
  313. if(dpsCompare > 65){
  314. dpsCompare = (dpsCompare - 65) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  315. user.getRange(13, 4).setValue(dpsCompare);
  316. }
  317. break;
  318.  
  319. //Mursaat Overseer
  320. case 11:
  321. if(dpsCompare >= 65 && dpsCompare <= 85){
  322. user.getRange(13, 4).setValue(0);
  323. }
  324. if(dpsCompare < 65){
  325. dpsCompare = (dpsCompare - 65) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  326. user.getRange(13, 4).setValue(dpsCompare);
  327. }
  328. if(dpsCompare > 85){
  329. dpsCompare = (dpsCompare - 85) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  330. user.getRange(13, 4).setValue(dpsCompare);
  331. }
  332. break;
  333.  
  334. //Samarog
  335. case 12:
  336. if(dpsCompare >= 35 && dpsCompare <= 60){
  337. user.getRange(13, 4).setValue(0);
  338. }
  339. if(dpsCompare < 35){
  340. dpsCompare = (dpsCompare - 35) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  341. user.getRange(13, 4).setValue(dpsCompare);
  342. }
  343. if(dpsCompare > 60){
  344. dpsCompare = (dpsCompare - 60) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  345. user.getRange(13, 4).setValue(dpsCompare);
  346. }
  347. break;
  348.  
  349. //Deimos
  350. case 13:
  351. if(dpsCompare >= 50 && dpsCompare <= 75){
  352. user.getRange(13, 4).setValue(0);
  353. }
  354. if(dpsCompare < 50){
  355. dpsCompare = (dpsCompare - 50) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  356. user.getRange(13, 4).setValue(dpsCompare);
  357. }
  358. if(dpsCompare > 75){
  359. dpsCompare = (dpsCompare - 75) * ref.getRange(maxMech+1, 35+correctBoss).getValue();
  360. user.getRange(13, 4).setValue(dpsCompare);
  361. }
  362. break;
  363. }
  364. //All Bosses
  365. //Crit Comparison
  366. if(critCompare >= 92){
  367. critCompare = (critCompare - 92) * ref.getRange(maxMech+2, 35+correctBoss).getValue();
  368. user.getRange(14, 4).setValue(critCompare);
  369. }else{
  370. user.getRange(14, 4).setValue(0);
  371. }
  372. //Moving Rate Comparison
  373. if(movCompare >= 85){
  374. movCompare = (movCompare - 85) * ref.getRange(maxMech+3, 35+correctBoss).getValue();
  375. user.getRange(15, 4).setValue(movCompare);
  376. }else{
  377. user.getRange(15, 4).setValue(0);
  378. }
  379. //Scholar Rate Comparison
  380. if(schCompare > 70 && schCompare < 92){
  381. user.getRange(16, 4).setValue(0);
  382. }
  383. if(schCompare >= 92){
  384. schCompare = (schCompare - 92) * ref.getRange(maxMech+4, 35+correctBoss).getValue();
  385. user.getRange(16, 4).setValue(schCompare);
  386. }
  387. if(schCompare <= 70){
  388. schCompare = (schCompare - 70) * ref.getRange(maxMech+4, 35+correctBoss).getValue();
  389. user.getRange(16, 4).setValue(schCompare);
  390. }
  391. //Down Comparison
  392. if(downCompare > 0){
  393. downCompare = downCompare * ref.getRange(maxMech+5, 35+correctBoss).getValue();
  394. user.getRange(17, 4).setValue(downCompare);
  395. }else{
  396. user.getRange(17, 4).setValue(0);
  397. }
  398. }
  399. }
  400.  
  401. //Mechanic and Weighted Mechanic Data
  402. if(i >= 18){
  403. mech.getRange(i-15, (correctBoss*8)-6).copyTo(user.getRange(i, 2), {contentsOnly:true});
  404. mech.getRange(i-15, (correctBoss*8)-5).copyTo(user.getRange(i, 3), {contentsOnly:true});
  405. weightMech = ref.getRange(i-17, 35+correctBoss).getValue();
  406. valueToChange = user.getRange(i, 3).getValue();
  407. user.getRange(i, 4).setValue(weightMech*valueToChange);
  408. }
  409. }
  410. }
  411.  
  412. //IF THE CURRENTLY ACTIVE SHEET IS "INFO"
  413. if(wsActive == "Info"){
  414.  
  415. //Storage Variables
  416. var naCount = 0;
  417. var wipCount = 0;
  418. var gearCount = 0;
  419. var profCount = 0;
  420.  
  421. //Getting your Proficiencies Total
  422. for(var i=5; i<=buildCount+4; i++){
  423. switch(build.getRange(i, 3).getValue()){
  424. case "N/A":
  425. naCount++;
  426. break;
  427. case "WIP":
  428. wipCount++;
  429. break;
  430. case "Geared":
  431. gearCount++;
  432. break;
  433. case "Proficient":
  434. profCount++;
  435. break;
  436. }
  437. }
  438.  
  439. //Finding the Correct Build
  440. for(var i=1; i<=buildCount; i++){
  441. correctBuild = ref.getRange(i, 1).getValue();
  442. if(correctBuild == info.getRange(4, 3).getValue()){
  443. correctBuild = i;
  444. break;
  445. }
  446. }
  447.  
  448. //Clearing Cells
  449. for(var i=5; i<=10; i++){
  450. info.getRange(i, 3).clear({contentsOnly: true});
  451. info.getRange(i, 4).clear({contentsOnly: true});
  452. }
  453.  
  454. //Displaying your Proficiency Totals
  455. for(var i=5; i<=8; i++){
  456. switch(i){
  457. case 5:
  458. info.getRange(i, 3).setValue(naCount);
  459. break;
  460. case 6:
  461. info.getRange(i, 3).setValue(wipCount);
  462. break;
  463. case 7:
  464. info.getRange(i, 3).setValue(gearCount);
  465. break;
  466. case 8:
  467. info.getRange(i, 3).setValue(profCount);
  468. break;
  469. }
  470. }
  471.  
  472. //Displaying a Build's Description
  473. ref.getRange(correctBuild, 20).copyTo(info.getRange(9, 3), {contentsOnly: true});
  474.  
  475. //Displaying a Build's Difficulty
  476. ref.getRange(correctBuild, 21).copyTo(info.getRange(10, 3), {contentsOnly: true});
  477. }
  478.  
  479. if(wsActive == "Leaderboards"){
  480.  
  481. }
  482. }
  483.  
  484. //Overwrites new (user input) data with existing data
  485. function userEditing(){
  486. //List of variables
  487. var ws = SpreadsheetApp.getActiveSpreadsheet();
  488. var user = ws.getSheets()[4];
  489. var build = ws.getSheets()[1];
  490. var ref = ws.getSheets()[0];
  491. var buildList = ref.getRange("A:A");
  492. var correctBuild = 0;
  493. var buildCount = 0;
  494.  
  495. //Determining the max amount of Builds there are
  496. for(var i=1; i<=1000; i++){
  497. if(ref.getRange(i, 1).isBlank() != true){
  498. buildCount++;
  499. }else{
  500. break;
  501. }
  502. }
  503.  
  504. //Overwriting Data
  505. for(var i=1; i<=buildCount; i++){
  506. var currentBuild = ref.getRange(i, 1).getValue();
  507. if(currentBuild == user.getRange(3, 3).getValue()){
  508. correctBuild = i;
  509. for(var j=4; j<=9; j++){
  510. if(user.getRange(j, 4).getValue() != ""){
  511. if(j < 8){
  512. user.getRange(j, 4).copyTo(build.getRange(i+4, j-1), {contentsOnly:true});
  513. }else if(j = 9){
  514. user.getRange(j, 4).copyTo(build.getRange(i+4, j-2), {contentsOnly:true});
  515. break;
  516. }
  517. }
  518. }
  519. }
  520. }
  521.  
  522. //Displaying Data & Clearing Data
  523. for(var i=4; i<=9; i++){
  524. if(i < 8){
  525. build.getRange(correctBuild+4, i-1).copyTo(user.getRange(i, 3), {contentsOnly:true});
  526. }else if(i == 9){
  527. build.getRange(correctBuild+4, i-2).copyTo(user.getRange(i, 3), {contentsOnly:true});
  528. }
  529. user.getRange(i, 4).clear({contentsOnly:true});
  530. }
  531. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!