0

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);
}
  • Welcome James. You could get the result you are looking for by using built-in formulas to transpose the range then sort it, then transpose and sort it again, also this could be done by using a script. Please narrow your question in order to focus the answer in one direction, other way, the question is too broad/opinion based. Please take the tour and checkout How to Ask to learn the basics about the workings of this site. – Rubén Mar 14 '17 at 12:43
  • Thanks @Rubén. I appreciate your response. I have managed to get a working version of the code but I am sure my attempt goes about achieving the task in an inefficient way. If you have any advice for me, would be appreciated. Thanks James. – James Mar 18 '17 at 1:07
  • Great! Thanks for coming back and share your solution. Why do you think that it's inefficient? Is it slow? Do you think that the result could be done with few lines of code? Side note, Stack Exchange has a site for code improving, Code Review. – Rubén Mar 18 '17 at 14:20
0

I've created a sheets add-on that can sort a range or sheet horizontally: https://chrome.google.com/webstore/detail/ffdejbbfllcdabkoijjdkkhajaemjpea (I've not tested the horiz-sort function extensively; please provide feedback if there are problems)

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.