I am a teacher and I store data from assessments in a Google Sheet.
In the rows, I have student names and in the columns, the score they received from each question.
I want to be able to sort by row (to see the students that scored highest to lowest), but also by question (to see which questions they answered correctly vs incorrectly).
I am aware of the transpose function (e.g. I could transpose, sort, and transpose back again) however I want to create a template for any teacher to use and this will mean the student and question numbers will fluctuate.
I am thinking that a script would work best but can't find any info on StackExchange or Google Product Forums. Is anyone able to help?
Edit: I have managed to cobble together some code to do the job. I'm wondering if there is a more efficient way to achieve this however?
function copyCells() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var source = ss.getSheets()[0];
var data = source.getRange(4, 4, sheet.getLastRow(), sheet.getLastColumn());
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
ss.insertSheet(1);
var destination = ss.getSheets()[1];
data.copyValuesToRange(destination, 1, lastCol, 1, lastRow);
// get all the data in the sheet
var active = SpreadsheetApp.getActiveSheet();
var range = active.getDataRange();
var values = range.getValues();
// clear existing
range.clear();
// transpose it & write it out
active.getRange(1,1,values[0].length,values.length)
.setValues(Object.keys(values[0]).map ( function (columnNumber) {
return values.map( function (row) {
return row[columnNumber];
});
}));
// sort
var newSheet = SpreadsheetApp.getActiveSpreadsheet();
var asheet = newSheet.getSheets()[1];
var arange = asheet.getRange(1, 1, asheet.getLastRow(), asheet.getLastColumn());
arange.sort(2);
//transpose back
// get all the data in the sheet
var active2 = SpreadsheetApp.getActiveSheet();
var range2 = active2.getDataRange();
var values2 = range2.getValues();
// clear existing
range2.clear();
// transpose it & write it out
active2.getRange(1,1,values2[0].length,values2.length)
.setValues(Object.keys(values2[0]).map ( function (columnNumber2) {
return values2.map( function (row2) {
return row2[columnNumber2];
});
}));
//paste back to the main sheet
var ssb = SpreadsheetApp.getActiveSpreadsheet();
var sheetb = ssb.getActiveSheet();
var sourceb = ssb.getSheets()[1];
var datab = sourceb.getRange(5, 1, sheetb.getLastRow(), sheetb.getLastColumn());
var datac = sourceb.getRange(1, 1, 1, sheetb.getLastColumn());
var lastRowb = sheetb.getLastRow();
var lastColb = sheetb.getLastColumn();
SpreadsheetApp.setActiveSheet(ssb.getSheets()[0]);
var destinationb = ssb.getSheets()[0];
datab.copyValuesToRange(destinationb, 4, lastColb, 8, lastRowb);
datac.copyValuesToRange(destinationb, 4, lastColb, 4, 4);
}
