API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 27th, 2017
52
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.60 KB | None | 0 0
raw download clone embed print report
  1. //general code. Needs to be adapted.
  2. //update 11/11/2016 username from email address in setTrigger
  3.  
  4. var columnLetter = "BL1";
  5. var passPercentage = 0.7;
  6.  
  7. function onInstall(e) {
  8. setTrigger(e);
  9. Logger.log('installing');
  10. //myFunction(e);
  11. }
  12.  
  13. function onOpen(e) {
  14. /*var triggers = ScriptApp.getProjectTriggers();
  15. if (triggers.length>0) {
  16. SpreadsheetApp.getUi().createAddonMenu().addItem('Trigger Exists', 'setTrigger').addToUi();
  17. } else { */
  18. SpreadsheetApp.getUi().createAddonMenu().addItem('Set Trigger', 'setTrigger').addToUi();
  19. //}
  20. Logger.log('running');
  21. }
  22.  
  23. function setTrigger(e) {
  24. var spreadsheet = SpreadsheetApp.getActive();
  25. var triggers = ScriptApp.getProjectTriggers();
  26. Logger.log(triggers);
  27. if (triggers.length>0) {
  28. Logger.log('trigger already exists');
  29. } else {
  30. Logger.log('new trigger');
  31. ScriptApp.newTrigger('myFunction').forSpreadsheet(spreadsheet).onFormSubmit().create();
  32. }
  33. var sheet = SpreadsheetApp.getActiveSheet();
  34. var usernameCell = sheet.getRange(1, 2);
  35. //var usernameCell = spreadsheet.getRange(1, 4+noOfQuestions+j);
  36. usernameCell.setValue("Username");
  37. }
  38.  
  39. function myFunction(e) {
  40. Logger.log("running on trigger");
  41. var namedValues = e.namedValues;
  42. getDocument(namedValues);
  43. Logger.log("running on trigger");
  44. }
  45.  
  46. function getDocument(namedValues) {
  47. var length = Object.keys(namedValues).length;
  48. //Logger.log(namedValues);
  49. var keyNames = Object.keys(namedValues);
  50. //start of spreadsheet answers section
  51. var spreadsheet = SpreadsheetApp.getActiveSheet();
  52. var allData = spreadsheet.getDataRange().getValues();
  53. var noOfQuestions = Object.keys(namedValues).length-3;
  54. var username = namedValues.Username;
  55. writeFormulas(noOfQuestions, allData, username);
  56. allData = spreadsheet.getDataRange().getValues(); //refresh allData if does not work, then will have to specifically find the students last entry and get results from that.
  57. var data = spreadsheet.getRange(2, 1, 1, length).getValues(); //correct answer to compare to
  58. var spreadsheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
  59. var spreadsheetNamenr = spreadsheetName.replace("(Responses)","");
  60.  
  61. var namefile = nameForFile(username);
  62. var docName = spreadsheetNamenr + "Answers-" + namefile;
  63. Logger.log(docName);
  64. var files = DriveApp.getFilesByName(docName);
  65. while (files.hasNext()) {
  66. var file = files.next();
  67. }
  68. var fileId = file.getId();
  69. Logger.log(fileId);
  70. var document = DocumentApp.openById(fileId);
  71. Logger.log(document.getName());
  72. var body = document.getBody();
  73. var text = document.getBody().editAsText();
  74.  
  75.  
  76. //refresh does not seem to work possibly because it is not the time limit that is the issue.
  77.  
  78.  
  79.  
  80. var attempt = 0;
  81. var attemptLocation = [];
  82. for(var i = 0; i < allData.length; i++){
  83. if(allData[i][1] == username){
  84. attempt++;
  85. attemptLocation.push(i);
  86. }
  87. }
  88. Logger.log(attemptLocation);
  89. var lastLocation = attemptLocation[attemptLocation.length-1];
  90. Logger.log(lastLocation);
  91.  
  92. text.appendText("\n");
  93. //text.appendText("\nTime of Submission: " + allData[allData.length-1][0]);
  94. text.appendText("\nTime of Submission: " + allData[lastLocation][0]);
  95. text.appendText("\nTry number " + attempt);
  96.  
  97. var correctStyle = {};
  98. correctStyle[DocumentApp.Attribute.BACKGROUND_COLOR] = '#2EFE2E';
  99.  
  100. var incorrectStyle = {};
  101. incorrectStyle[DocumentApp.Attribute.BACKGROUND_COLOR] = '#FF0000';
  102.  
  103. var resultStyle = {};
  104. resultStyle[DocumentApp.Attribute.FONT_SIZE] = 11;
  105. resultStyle[DocumentApp.Attribute.UNDERLINE]= false;
  106.  
  107. var table = body.appendTable([['Question','Your Answer','Mark']]);
  108. for (var i = 0; i < noOfQuestions; i++) {
  109. var tr = table.appendTableRow();
  110. //var tc1 = tr.appendTableCell('\n');
  111. var tc2 = tr.appendTableCell(allData[0][3+i] + '\n');
  112. //var tc3 = tr.appendTableCell(allData[allData.length-1][3+i] + '\n');
  113. var tc3 = tr.appendTableCell(allData[lastLocation][3+i] + '\n');
  114. //var mark = allData[allData.length-1][noOfQuestions+3+i];
  115. var mark = allData[lastLocation][noOfQuestions+3+i];
  116.  
  117. //var mark =""; //test please remove later
  118. var fullMark = allData[1][noOfQuestions+3+i];
  119.  
  120. if (mark/fullMark == 1) {
  121. var tc4 = tr.appendTableCell("Correct answer\n");
  122. tc4.setAttributes(correctStyle);
  123. } else if (mark == "F") {
  124. var tc4 = tr.appendTableCell("You have selected too many options. Please try again.\n");
  125. tc4.setAttributes(incorrectStyle);
  126. } else {
  127. try { //catch error with writing mark.tofixed of nothing.
  128. //mark.toFixed(0);
  129. var tc4 = tr.appendTableCell(mark.toFixed(0) + " out of " + fullMark + " Correct. Try again. \n");
  130. tc4.setAttributes(incorrectStyle);
  131. }
  132. catch(err) {
  133. var tc4 = tr.appendTableCell("Your answer could not be recorded. Please call Mr Owusu then try again.\n" + err.message +" Mark:" + mark);
  134. }
  135. }
  136. }
  137. //var total = allData[allData.length-1][allData[0].length-1];
  138. var total = allData[lastLocation][allData[0].length-1];
  139. var outOf = allData[1][allData[0].length-1];
  140. var percentage = total/outOf*100;
  141. var result1 = text.appendText("You got: " + total + " out of a possible " + outOf + " marks.\n");
  142. result1.setAttributes(resultStyle); //need to fix for full document formatting
  143. var result2 = text.appendText("That is: " + percentage.toFixed(2) + "%");
  144. result2.setAttributes(resultStyle); //need to fix for full document formatting
  145. if (percentage == 100) {
  146. body.setAttributes(correctStyle);
  147. text.appendText(" \nWell Done! :)")
  148. }
  149. text.appendText("\n");
  150. body.appendHorizontalRule();
  151. document.saveAndClose();
  152. }
  153.  
  154. function testtostring() {
  155. var num = 3;
  156. var str=num.toString();
  157. Logger.log(str);
  158.  
  159. }
  160.  
  161.  
  162. function writeFormulas(noOfQuestions, allData, username) {
  163. var spreadsheet = SpreadsheetApp.getActiveSheet();
  164.  
  165. if (allData.length == 2) {
  166. for (var j = 0; j < noOfQuestions; j++) {
  167. //set Question Mark Title
  168. var questionTitle = allData[0][3+j];
  169. //Logger.log(questionTitle);
  170. var questionTitleMarkCell = spreadsheet.getRange(1, 4+noOfQuestions+j);
  171. questionTitleMarkCell.setValue(questionTitle + " - Mark");
  172.  
  173. //Set formula for mark
  174. var answer = allData[1][3+j];
  175. var answer = answer.toString(); //numbers don't work
  176. var answerArray = answer.split(", ");
  177. var answerCell = spreadsheet.getRange(2, 4+j).getA1Notation();
  178. var formula = '=if((len(' + answerCell + ')-len(substitute(' + answerCell + ', ",", "")))>' + (answerArray.length-1) + ', "F", sum(';
  179. Logger.log(formula);
  180. for (var k = 0; k < answerArray.length; k++) {
  181. formula = formula + 'if(isnumber(search("' + answerArray[k] + '",' + answerCell + ')),1,0)';
  182. if (k != answerArray.length-1) {
  183. formula = formula + ', ';
  184. Logger.log(formula);
  185. }
  186. }
  187.  
  188. formula = formula + '))';
  189. Logger.log(formula);
  190. var questionAnswerMarkCell = spreadsheet.getRange(2, 4+noOfQuestions+j);
  191. questionAnswerMarkCell.setFormula(formula);
  192. }
  193.  
  194. var totalCellTitle = spreadsheet.getRange(1, 4+noOfQuestions+noOfQuestions);
  195. totalCellTitle.setValue("Total");
  196. var startSumCell = spreadsheet.getRange(2, 4+noOfQuestions).getA1Notation();
  197. var endSumCell = spreadsheet.getRange(2, 3+noOfQuestions+noOfQuestions).getA1Notation();
  198. var totalCell = spreadsheet.getRange(2, 4+noOfQuestions+noOfQuestions);
  199. totalCell.setFormula('=sum(' + startSumCell + ':' + endSumCell + ')');
  200.  
  201.  
  202. //=if((len(E2)-len(substitute(E2, ",", "")))>1, "F", sum(if(isnumber(search("Hard Disk",E2)),1,0), if(isnumber(search("RAM",E2)),1,0)))
  203. } else {
  204. spreadsheet.sort(1); //new addition to stop from going wrong when students get correct answer but says its wrong. Infinity error. Sort by time.
  205. var lastLocation = nameLocation(allData, username); //resolve duplication in writing
  206. for (var i = 0; i < noOfQuestions+1; i++) {
  207. var oldCell = spreadsheet.getRange(2, noOfQuestions+4+i);
  208. var oldFormula = oldCell.getFormulaR1C1();
  209. //var newCell = spreadsheet.getRange(allData.length, noOfQuestions+4+i);
  210. var newCell = spreadsheet.getRange(lastLocation+1, noOfQuestions+4+i);
  211. newCell.setFormulaR1C1(oldFormula);
  212. }
  213. }
  214.  
  215. }
  216.  
  217. function nameForFile(username){
  218.  
  219. /* Logger.log(username);
  220. if (username=="____________________") {
  221. username = "_____________________";
  222. }*/
  223.  
  224. var nameContact = ContactsApp.getContactsByEmailAddress(username)[0];
  225. var name = nameContact.getFullName();
  226. if (name == "") {
  227. nameContact = ContactsApp.getContactsByEmailAddress(username)[1];
  228. name = nameContact.getFullName();
  229. }
  230.  
  231. var stripName = name.replace(/\W+/g, "-"); //added regex
  232.  
  233. var nameFile = stripName.toLowerCase();
  234. return nameFile;
  235. }
  236.  
  237. //nameLocation
  238. function nameLocation(allData, username) {
  239. Logger.log("in last location");
  240. var attemptLocation = [];
  241. for(var i = 0; i < allData.length; i++){
  242. if(allData[i][1] == username){
  243. attemptLocation.push(i);
  244. }
  245. }
  246. Logger.log(attemptLocation);
  247. var lastLocation = attemptLocation[attemptLocation.length-1];
  248. Logger.log(lastLocation);
  249. return lastLocation;
  250. }
  251.  
  252. function marksheet() {
  253. var spreadsheet = SpreadsheetApp.getActiveSheet();
  254. //get the total
  255. spreadsheet.sort(1);
  256. var allData = spreadsheet.getDataRange().getValues();
  257. var outOf = allData[1][allData[0].length-1];
  258. //Logger.log("Max mark is " + outOf);
  259. //end get total
  260.  
  261. spreadsheet.sort(2);
  262. var allData = spreadsheet.getDataRange().getValues();
  263. //Logger.log(allData);
  264. var user=allData[1][1];
  265. var mark=allData[1][allData[0].length-1];
  266. var i=0;
  267. //Logger.log(user);
  268. //Logger.log(mark);
  269.  
  270. for(i=2; i<allData.length; i++) { //change for full data
  271. var latestUser = allData[i][1];
  272. var latestMark = allData[i][allData[0].length-1];
  273. //Logger.log(latestUser);
  274. //Logger.log(latestMark);
  275. //Logger.log(mark)
  276.  
  277. if(i==allData.length-1){ //change depending on what is at the end
  278. //Logger.log("At the end");
  279. if (latestUser!=user){
  280. sendMarks(user, mark, outOf);
  281. sendMarks(latestUser, latestMark, outOf);
  282. // Logger.log("Send %s to marksheet for %s", latestMark, latestUser);
  283. } else {
  284. if (latestMark > mark) {
  285. mark = latestMark;
  286. // Logger.log("Mark is bigger");
  287. }
  288. // Logger.log(user + " Highest Mark is: " + mark);
  289. sendMarks(user, mark, outOf);
  290. // Logger.log("Send %s to marksheet for %s", mark, user);
  291. }
  292. //Logger.log("at the end. Sen");
  293. } else if (latestUser!=user) {
  294. sendMarks(user, mark, outOf);
  295. // Logger.log("user changed or at end. Send %s to marksheet for %s", mark, user);
  296. user = latestUser;
  297. mark = latestMark;
  298. // Logger.log(user);
  299. // Logger.log(mark);
  300. } else {
  301. if (latestMark > mark) {
  302. mark = latestMark;
  303. // Logger.log("Mark is bigger");
  304. }
  305. //Logger.log(user + " Highest Mark is: " + mark);
  306. }
  307. }
  308. }
  309.  
  310. function sendMarks(user, mark, max){
  311. // mark = 6;
  312. //max = 8;
  313. var masterSpreadsheet = SpreadsheetApp.openById("_______________________________"); //id of spreadsheet here
  314. var masterSheet = masterSpreadsheet.getSheetByName("______________"); //tab name
  315. var data = masterSheet.getDataRange().getValues();
  316. var row=1;
  317. while (data[row][0]!=user && row<data.length-1){
  318.  
  319. // Logger.log(row);
  320. row++;
  321. }
  322.  
  323. // Logger.log(row);
  324. //var columnLetter = "BL1"; //change for each sort
  325. var column = masterSheet.getRange(columnLetter);
  326. var columnNo = column.getColumn();
  327. var cell = masterSheet.getRange(row+1, columnNo);
  328. var score = mark/max*1/passPercentage;
  329. score = score.toFixed(2);
  330. cell.setValue(score);
  331.  
  332. //var cellValue = cell.getValue();
  333. //Logger.log(cellValue);
  334.  
  335.  
  336. //for (var i=1; i<data.length; i++){
  337.  
  338. //}
  339.  
  340.  
  341. }
  342.  
  343.  
  344. //nameLocation backwards
  345. /**
  346. function nameLocationBack(allData, username) {
  347. Logger.log("in last location back");
  348. //var attemptLocation = [];
  349. var lastLocation
  350. var found = false;
  351. for(var i = allData.length-1; found=false; i--){
  352. if(allData[i][1] == username){
  353. attemptLocation.push(i);
  354. }
  355. }
  356. //Logger.log(attemptLocation);
  357. //var lastLocation = attemptLocation[attemptLocation.length-1];
  358. Logger.log(lastLocation);
  359. return lastLocation;
  360. }
  361. **/
  362.  
  363. /*
  364. Setup instructions:
  365. NOW INSTALLABLE SO JUST COPY AND PASTE CODE. REFRESH SPREADSHEET GO TO MENU AND ADD CLICK TRIGGER. DONE!
  366. Create test on a form
  367. install script - tools, script editor (copy and paste)
  368. initialise by doing the following:
  369. -Resources, current project triggers, from spreadsheet, on form submit
  370. -Resources, advanced google services, turn on drive api
  371. -Go to google developers console and turn on contacts api and drive api
  372. Fill out correct answers
  373. check spreadsheet to see if answers have been placed in answer boxes.
  374.  
  375. */
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!