How to sort date strings in accending order in Google Apps Script Javascript

Question

I have 3 columns of dates I need to sort in a spreadsheet.

First I need to sort Horizontally. The dates are in string format.

e.g. "5/3","5/20", "6/3" or sometimes BLANK.

I need everything moved into the left-most column if some of the cells are blank.

Secondly I need to sort the rows by date. range.sort is fine for that.

Here's What I have so far.

function sortDate() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Loads");
 var range = sheet.getRange("C9:BA53"); 


  //Sorts Horizontally

    //getValues gives a 2D array. 
    var data = sheet.getRange('AC9:AE53');



    //This for loop with loop through each row
    for(var i=0; i<data.length; i++) 
    {

    var dateArry = [data[i][0],data[i][1],data[i][2]];
          //This for loop with loop through each column
         //for (var j = 0; j < data[i].length ; j ++){
          //This assumes Column AC has the dates you are comparing aganist

         //dateElem = date.split('/');

         //dateElem[1] = Number(dateElem[1]) + 1;

         //newDate = dateElem.join('/');

         var sortDates = dateArry.sort();

         sheet.getRange("AC"+i+"AE"+i).setValues(sortDates);




         };
    };

UPDATED CODE:

Here are the updates I've made. The hardest part is sorting each row of dates. I've added a second for loop to separate each set of dates.

function sortDate() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Loads");
 //var range = sheet.getRange("C9:BA53"); 


  //Sorts Horizontally

    //getValues gives a 2D array. 
    var data = sheet.getRange('AC1:AE53').getValues();



    //This for loop with loop through each row
    for(var i=0; i<data.length; i++) {
      for (var j = 8; j < data[i].length ; j ++){

    var dateArry = [data[i][0],data[i][1],data[i][2]];

????????? How do I sort the 3 dates per row????????????

         var sortDates = dateArry.sort();

         //sheet.getRange("AC"+i+":AE"+i).setValues(sortDates);
         sheet.getRange("AC"+i).setValue(sortDates[0]);
         sheet.getRange("AD"+i).setValue(sortDates[1]);
         sheet.getRange("AE"+i).setValue(sortDates[2]);

         };
    };

    };

UPDATED CODE 2:

Here is my 3rd run of the program. It works great except it puts null/empty cells first when sorting. I need anything null to go at the end while keeping the rest in ascending order. Thanks!!!

Example:

inputArray = ["5/3", " ","6/2"]

correctOutput = ["5/3","6/2"," "]

incorrectOutput = [" ", "5/3","6/2"] This is what it is doing now.

3rd set of code:

 function sortDate2() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Loads");
 //var range = sheet.getRange("C9:BA53"); 
 //Trying to fix improper sorting. works great for alphabetic sorting


  //Sorts Horizontally

    //getValues gives a 2D array. 
    var data = sheet.getRange('AC9:AE53').getValues();



    //This for loop with loop through each row
    for(var i=0; i<data.length; i++) {


    for (var j = 0; j < data[i].length ; j ++){ 

    var dateArry = [data[i][0],data[i][1],data[i][2]];





         //var sortDates = dateArry.sort(function(a, b){return a-b});
         var sortedDates = dateArry.sort(function (a, b) {
    // '01/03/2014'.split('/')
    // gives ["01", "03", "2014"]
    a = a.split('/');
    b = b.split('/');
    return a[1] - b[1] || a[0] - b[0] || (a===null)-(b===null) ;
});






         data[i][0] = sortedDates[0];
         data[i][1] = sortedDates[1];
         data[i][2] = sortedDates[2];


        };

         };

         sheet.getRange('AC9:AE53').setValues(data);


  };

Please remove/replace with "Javascript" "Script Java" in the question's title to avoid confusing people. — Pyromonk
– Pyromonk, Commented Jun 2, 2017 at 4:37
First, you'll need to retrieve the values for the cells: add .getValues() to your sheet.getRange("Ax:Zy") calls, which is quicker than grabbing the values as you iterate over the range. So, if you do that to var data = sheet.getRange('AC9:AE53'); you can get rid of the dateArry. You should then look for your empty cells before you start sorting. Once you have the cells populated, then you can use Date.parse() to get a date serial & sort them (don't forget to add the year to these date strings). Could you clarify your question so we can give better answers? — Dean Ransevycz
– Dean Ransevycz, Commented Jun 2, 2017 at 7:38
Hi Dean, I've added the .getValues(). I'm not working with actual dates in this code because it ultimately needs to be in plain text format to create .csv files. So the dates are actually strings. Years are not used either and not needed because all dates that are sorted will be with in a week or two of each other. — Clayten
– Clayten, Commented Jun 3, 2017 at 5:46
You should use real date objects, everything would be more simple... and convert them to strings at the end. Not using the year value could be an issue when date will be in December or January ! — Serge insas
– Serge insas, Commented Jun 3, 2017 at 6:02
so right now the most difficult thing is sorting/ordering the 3 dates of each row. each row of 3 dates needs compared seperately. I've added some new code but not getting anywhere yet. — Clayten
– Clayten, Commented Jun 3, 2017 at 6:14

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

You could also use sort(sortSpecObj):

Sorts the cells in the given range. Sorts the cells in a given range, by column and order specified.

Here is a snippet from the document:

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var range = sheet.getRange("A1:C7");

 // Sorts by the values in the first column (A)
 range.sort(1);

 // Sorts by the values in the second column (B)
 range.sort(2);

 // Sorts descending by column B
 range.sort({column: 2, ascending: false});

 // Sorts descending by column B, then ascending by column A
 // Note the use of an array
 range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);

 // For rows that are sorted in ascending order, the "ascending" parameter is
 // optional, and just an integer with the column can be used instead. Note that
 // in general, keeping the sort specification consistent results in more readable
 // code. We could have expressed the earlier sort as:
 range.sort([{column: 2, ascending: false}, 1]);

 // Alternatively, if we wanted all columns to be in ascending order, we would use
 // the following (this would make column 2 ascending)
 range.sort([2, 1]);
 // ... which is equivalent to
 range.sort([{column: 2, ascending: true}, {column: 1, ascending: true}]);

I also agree with @Dean, using Date.parse() will make it easier to sort.

Hope this helps.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 2, 2017 at 16:53

Mr.Rebot

6,8092 gold badges19 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Clayten Over a year ago

I said in my post I know how to do this already. Need help horizontally sorting dates.

Collectives™ on Stack Overflow

How to sort date strings in accending order in Google Apps Script Javascript

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related