0

I'm writing a program where part of the core functionality is sorting the input rows by information found in multiple columns (chapters>lessons>entry-type...). I'm attempting to do this by sorting nested Ranges.

Here's the problem I'm encountering: r.sort({column: col, ascending: asc}) doesn't seem to do anything. The spreadsheet remains unchanged, even though the source data is clearly unsorted. What am I missing/doing wrong here?

Here's a sample dataset (unrelevant areas are blanked out):

https://docs.google.com/spreadsheets/d/1h8CLpN7LnURyAfZZs-jMWES2pETkgHD0VriLWLLWQaI/edit?usp=sharing

here's my code:

function Sort2Sort() {
  //Get new spreadsheet data
  var Spreadsheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1h8CLpN7LnURyAfZZs-jMWES2pETkgHD0VriLWLLWQaI/edit");
  var sheetIn = Spreadsheet.getSheetByName('Form Responses 1');
  var sheetOut = Spreadsheet.getSheetByName('Sorted');

  //Append sheetIn onto the end of sheetOut
  var copy = sheetIn.getRange(2, 1, sheetIn.getLastRow()-1, sheetIn.getLastColumn()-1);
  var location = sheetOut.getRange(sheetOut.getLastRow()+1, 1, 1, 1);
  copy.copyTo(location);

  //Sort the sheet!
  var sheetRange = sheetIn.getRange( 2, 1, sheetIn.getLastRow()-1, sheetIn.getLastColumn()-1);
  //First by chapters...
  var chapters = sort(sheetRange, 2, true);
  Logger.log("Sorted chapters, sorting lessons...");
  //Loop through chapters, sorting by lesson...
  for(var c = 0; c < chapters.length; c++){
    var lessons = sort(chapters[c], 3, true);
    Logger.log("Sorted chapter "+c+" of "+chapters.length);

    if(lessons.length>1){
      //Loop through lessons, sorting by type
      for(var l = 0; l < lessons.length; l++){
        var categories = sort(lessons[l], 9, false);
        Logger.log("Sorted chapter "+(c+1)+" lesson "+(l+1));

        if(categories.lenth>1){
          //Tie-break with source, rest can be left arbitrary
          for(var c = 0; c<categories.length; c++){
            sort(categories[c], 8, false);
          }
        }
      }
    }
  }
}

function sort(r, col, asc){
  //Sort by chapter
  r.sort({column: col, ascending: asc}).copyTo(r);
  Logger.log("Sorted range: "+r.getA1Notation());


  var ranges = [];
  //Separate into Ranges by sorted variable, sorting each as it passes
  var unitStart;
  var max = r.getNumRows();
  var unit;
  for(unitStart = 2; unitStart < max; ){
    unit = r.getValues()[unitStart][0];

    //Find the end of the unit
    var offset;
    for(offset = 1; unitStart+offset < max; offset++){
      if(r.getValues()[unitStart+offset][0] != unit){
        break;
      }
    }
    //Found offset; unitStart + offset = first item of next unit
    ranges.push(subRange(r, unitStart, 0, offset, 12));
    //Prepare for next unit
    unitStart += offset;
  }
  return ranges;
}

//Gets a 'subrange'with coordinates relative to range origin
function subRange(r, row, column, numRows, numColumns){
  return r.getSheet().getRange(r.getRow()+row, r.getColumn()+column, numRows, numColumns);
}

And here's my Transcript: [18-07-05 09:12:08:814 PDT] Starting execution

[18-07-05 09:12:08:909 PDT] SpreadsheetApp.openByUrl([https://docs.google.com/spreadsheets/d/1xxycy-uzUNbq7YFFcQA70hn7I4rpfhD4_Ieml5rOxUM/edit]) [0.088 seconds]

[18-07-05 09:12:08:911 PDT] Spreadsheet.getSheetByName([Form Responses 1]) [0 seconds]

[18-07-05 09:12:08:911 PDT] Spreadsheet.getSheetByName([Sorted]) [0 seconds]

[18-07-05 09:12:09:112 PDT] Sheet.getLastRow() [0.2 seconds]

[18-07-05 09:12:09:313 PDT] Sheet.getLastColumn() [0.2 seconds]

[18-07-05 09:12:09:314 PDT] Sheet.getRange([2, 1, 16, 9]) [0 seconds]

[18-07-05 09:12:09:515 PDT] Sheet.getLastRow() [0.199 seconds]

[18-07-05 09:12:09:516 PDT] Sheet.getRange([1, 1, 1, 1]) [0 seconds]

[18-07-05 09:12:09:516 PDT] Range.copyTo([Range]) [0 seconds]

[18-07-05 09:12:10:120 PDT] Sheet.getLastRow() [0.602 seconds]

[18-07-05 09:12:10:315 PDT] Sheet.getLastColumn() [0.195 seconds]

[18-07-05 09:12:10:317 PDT] Sheet.getRange([2, 1, 16, 9]) [0 seconds]

[18-07-05 09:12:10:318 PDT] Range.sort([{column=2.0, ascending=true}]) [0 seconds]

[18-07-05 09:12:10:318 PDT] Range.copyTo([Range]) [0 seconds]

[18-07-05 09:12:10:318 PDT] Range.getA1Notation() [0 seconds]

[18-07-05 09:12:10:320 PDT] Logger.log([Sorted range: A2:I17, []]) [0 seconds]

[18-07-05 09:12:10:320 PDT] Range.getNumRows() [0 seconds]

[18-07-05 09:12:10:923 PDT] Range.getValues() [0.602 seconds]

[18-07-05 09:12:10:924 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:924 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:925 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:925 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:925 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:926 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:926 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:926 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:927 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:927 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:927 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:928 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:928 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:10:929 PDT] Range.getSheet() [0 seconds]

[18-07-05 09:12:10:929 PDT] Range.getRow() [0 seconds]

[18-07-05 09:12:10:930 PDT] Range.getColumn() [0 seconds]

[18-07-05 09:12:10:931 PDT] Sheet.getRange([4, 1, 14, 12]) [0 seconds]

[18-07-05 09:12:10:931 PDT] Logger.log([Sorted chapters, sorting lessons..., []]) [0 seconds]

[18-07-05 09:12:10:932 PDT] Range.sort([{column=3.0, ascending=true}]) [0 seconds]

[18-07-05 09:12:10:933 PDT] Range.copyTo([Range]) [0 seconds]

[18-07-05 09:12:10:933 PDT] Range.getA1Notation() [0 seconds]

[18-07-05 09:12:10:934 PDT] Logger.log([Sorted range: A4:L17, []]) [0 seconds]

[18-07-05 09:12:10:934 PDT] Range.getNumRows() [0 seconds]

[18-07-05 09:12:11:557 PDT] Range.getValues() [0.622 seconds]

[18-07-05 09:12:11:557 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:558 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:558 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:559 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:559 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:559 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:560 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:560 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:560 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:561 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:561 PDT] Range.getValues() [0 seconds]

[18-07-05 09:12:11:562 PDT] Range.getSheet() [0 seconds]

[18-07-05 09:12:11:563 PDT] Range.getRow() [0 seconds]

[18-07-05 09:12:11:563 PDT] Range.getColumn() [0 seconds]

[18-07-05 09:12:11:564 PDT] Sheet.getRange([6, 1, 12, 12]) [0 seconds]

[18-07-05 09:12:11:565 PDT] Logger.log([Sorted chapter 0 of 1, []]) [0 seconds]

[18-07-05 09:12:11:567 PDT] Execution succeeded [2.745 seconds total runtime]

  • Welcome. Please add a demo spreadsheet shared with anyone with the link to view including a sample sheet and the code in order to make it easier for others to reproduce the problem. Also it's a good practice to include the execution transcript. – Rubén Jul 5 '18 at 13:49
  • OK, I just added the full code, a sample document, and the transcript to the question. – Lowell Camp Jul 5 '18 at 16:23

Your Answer

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

Browse other questions tagged or ask your own question.