- 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.
- htmlForm - the form which provides a user-friendly way to add/change student feedback data through dropdown and free-text boxes.
- 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.
Student Selection Screen for picking a student to provide feedback for |
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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