0

I have a Google Script for a "leaderboard" type page, where vlookup formulas and such give each user an overall score on a single tab. I check the column (B) that has the user names, and find the last row that is populated, create a range that selects from column B to column AC and sorts by descending score order, followed by user name. I found the way to get the last row populated here, and read about the fact that I have to move forward one row somewhere else I cannot find again right now.

HOWEVER, it is not including the bottom user in the sort. I have tried doing other things to the selected range and it does include that last row (for example, the setBackground shown below colors in the proper range). I feel like it's somehow related to the +1 and how it deals with integer vs. string, but I can't find the right combination.

NOTE: There are no frozen panes on the sheet. I checked that. 

  var avalsnew = xpdoc.getRange('b2:b').getValues(); 
  //I've tried both b1 and b2 here

  var alastnew = avalsnew.filter(String).length;
  alastnew = parseInt(alastnew) + 1; 
  //Tried with and without this line, which is why I suspect this is part of the problem

  var percentsort = percentdoc.getRange('b2:ac' + alastnew);
  percentsort.setBackground('green'); 
  //this includes the last row
  percentsort.sort([{column: 29, ascending: false}, {column: 2, ascending: true}]); 
  //but this does not
MitchO
  • 43
  • 1
  • 8
  • `SpreadsheetApp.getActiveSheet().getLastRow()` will retrieve the index of the last row with data in any column. – Vytautas May 03 '18 at 04:22
  • Replacing var alastnew = avalsnew.filter(String).length; with var alastnew = xpdoc.getLastRow(); still results the same problem. – MitchO May 09 '18 at 15:44
  • Anyone? Anyone at all?! I've been fiddling with this for days, and every other function I do includes the last row, but the sort does not. – MitchO May 16 '18 at 18:38
  • Not enough information to know what the problem is. For one you are doing `var alastnew = avalsnew.filter(String).length;` which might filter out a value that is present in the other document (as I see these are 2 separate data sets). Try to use the same filter for `percentdoc` values and see if you get the same value array lenght. – Vytautas May 17 '18 at 19:51
  • Also, seems a bit odd that you base your sort range in `percentdoc` on length of data in `xpdoc`. This leads to all kinds of possible issues. Hard to say without a sample. If you could provide a spreadsheet with a sample data set where this doesn't work I could take a look. I do sorting differently and it always works for me – Vytautas May 17 '18 at 20:07

0 Answers0