Pages

Saturday, 5 January 2019

Student Feedback system - HTML form - part 1

This section focuses on the main part of the student feedback system designed for the Law department - the HTML form used by tutors to provide student feedback, as such it has been broken down into 3 parts:
  1. getValues - the function designed to lookup the name of a student and gather their corresponding feedback (if any) to pass this through to the HTML form.
  2. htmlForm - the form which provides a user-friendly way to add/change student feedback data through dropdown and free-text boxes.
  3. writeBack - the function that takes the data from the submitted form and puts it back into the spreadsheet depending on which tutor has completed it.
1. getValues
Image of the Student Selection Screen
Student Selection Screen for picking a student to provide feedback for
On the Welcome sheet a tutor selects a name from the dropdown and clicks the submit button (which is assigned the function openHTML). The getValues function gathers data from the spreadsheet and runs the getActiveUser script to determine if tutor 1 or 2 is accessing the data. It then creates a loop to lookup the student from the dropdown on the Welcome sheet to the relevant row on the Group Feedback sheet - to find their data. We add 2 to this row value as the first two rows contain column headings.

By feeding in the row number of the student we can use 'getRange' to get an array of the data, leading on to the creation of key/value pairs. These pairs are then turned into a JSON string ('stringify') that can be used by the HTML form.

function getValues() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var groupSheet = ss.getSheetByName('Group Feedback');
var groupSheetName = groupSheet.getSheetName();
var numCols = groupSheet.getLastColumn();
var numRows = groupSheet.getLastRow();
// get 2-D list of 'Student Name'
var groupSheetName = groupSheet.getRange(2, 2, numRows, 1).getValues();
var welcomeSheet = ss.getSheetByName('Welcome');
var welcomeSheetName = welcomeSheet.getRange(6, 4).getValue();
Logger.log('Welcome sheet name is: ' + welcomeSheetName);
// run Function to determine if Tutor 1 or 2 for displaying relevant Form values
var whoAmI = getActiveUser(groupSheetName, groupSheet);
Logger.log('Value of whoAmI is: ' + whoAmI);
// loop through name in dropdown to find match in 'Group Feedback' **********************
for (var i=0;i<groupSheetName.length;i++) {
Logger.log(groupSheetName[i][0]);
if (welcomeSheetName == groupSheetName[i][0]) {
var selectedName = groupSheetName[i][0];
Logger.log(welcomeSheetName + ' matches ' + selectedName);
var rowNum = i+2;
}
}// end of loop through name in dropdown to find match in 'Group Feedback' **************
// first row of student data
var currentRow = rowNum;
Logger.log('currentRow value is: ' + currentRow);
// get values from row as 1-D an array - achieved by '[0]'
var values = groupSheet.getRange(currentRow, 2, 1, numCols-3).getValues()[0];
Logger.log(values);
// make a key:value object of these values
var studentName = values[0];
var attendance1 = values[1];
var contribution1 = values[2];
var attitude1 = values[3];
var comments1 = values[4];
var attendance2 = values[5];
var contribution2 = values[6];
var attitude2 = values[7];
var comments2 = values[8];
// get Tutor names to display on Form
var tutor1 = groupSheet.getRange(1, 3).getValue();
var tutor2 = groupSheet.getRange(1, 7).getValue();
// turn values into json string for sending to HTML page
var dataToGo = {currentRow:currentRow,whoAmI:whoAmI,studentName:studentName,attendance1:attendance1,contribution1:contribution1,attitude1:attitude1,comments1:comments1,attendance2:attendance2,contribution2:contribution2,attitude2:attitude2,comments2:comments2,tutor1:tutor1,tutor2:tutor2};
var jsonData = JSON.stringify(dataToGo);
Logger.log(jsonData);
return jsonData;
}

No comments:

Post a Comment