0

I put all the data in my sheet in to an array and assigned it to a variable. I then setup a function that is supposed to help sort my array of arrays by a "column" in the arrays. However, it it doesn't look like it is working.

Here is the sheet, the barcodes with the same value should be next to each other. However, some of the barcode values are not sorting correctly: the second column is the column I want to sort by.

enter image description here

Here is my code:

function rate() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //Get specific sheet
  var sheet = ss.getSheetByName('Sheet3');

  // get the data on the sheet
  var values = sheet.getDataRange().getValues();

  //sort sheet by the barcode column (column B)
  var testIndex = values.sort(sortFunction);

  // time difference between the start and finish time is stored here
  var time = 0;

  //loops through array and calculates the time difference
  for (i = 0 ; i < values.length -1 ; i++){
   if(testIndex[i][1] === testIndex[i+1][1]) {
      if (testIndex[i][2] === 'Start'){
        timeDifference = testIndex[i+1][0].getTime() - testIndex[i][0].getTime();
        time += Math.floor(timeDifference/1000/60);
      }
    }
  }
  Logger.log(time);


}

//sorting function
function sortFunction(a, b) {
  if(a[1] < b[1]) {
    return -1;
  }else if (a[1] > b[1]) {
    return 1;
  }

}

I searched on Google and found the "sortfunction" code here: https://stackoverflow.com/questions/5435228/sort-an-array-with-arrays-in-it-by-string.

I don't fully understand how the compare functions work with sort.

Summery: I can't figure out how to sort an array of arrays by a specific column. Any advice would be helpful.

-Update- I tried checking to see if any values in the array were "undefined" by using the code from the post refereed to in the comments. However, I am still seeing testIndex as undefined. I am not really sure what to do about it.

 var testIndex = values.sort(function (a, b) {
    if ((typeof b[1] === 'undefined' && typeof a[1] !== 'undefined') || a[1] < b[1]) {
        return -1;
    }
    if ((typeof a[1] === 'undefined' && typeof b[1] !== 'undefined') || a[1] > b[1]) {
        return 1;
    }

    return 0;
}); 
Community
  • 1
  • 1
Bokai
  • 107
  • 3
  • 17
  • You never output the sorted array testIndex back on to your google spreadsheet. So how do you know, if it sorted it or not? – Jack Brown May 17 '17 at 04:01
  • I ran it through the debugger, to view the array testIndex. – Bokai May 17 '17 at 11:15
  • You can sort columns by index easily with [ArrayLib](https://sites.google.com/site/scriptsexamples/custom-methods/2d-arrays-library) – Robin Gertenbach May 17 '17 at 11:46
  • Also maybe it's not sorting properly because you are not considering ties? – Robin Gertenbach May 17 '17 at 11:48
  • @RobinGertenbach can you elaborate more on this? My hope was for values that are the same to be listed one after the other. – Bokai May 17 '17 at 12:10
  • The answer you linked returns 0 for equal values, your implementation returns undefined. – Robin Gertenbach May 17 '17 at 12:13
  • 1
    See [here](http://stackoverflow.com/questions/23300682/array-sort-not-working) – Robin Gertenbach May 17 '17 at 12:15
  • @RobinGertenbach I tried using that code to check for "undefined" values, but testIndex is still showing as undefined. I am not really sure where I am going wrong at. – Bokai May 17 '17 at 12:40
  • This should technically work, can you provide a test data where it doesn't work? Like a link to sanitized google sheet with data? – Jack Brown May 17 '17 at 13:11
  • @JackBrown Here is a link to some mock test sheet: [link](https://docs.google.com/spreadsheets/d/17NCOiLEB8lRtWezJrYdkmgWKArCngYpoyBjcpIlS_C0/edit?usp=sharing) please let me know if you would like something else – Bokai May 17 '17 at 13:48
  • 1
    I ran your function on your data and posted the sorted array in a new ["sheet2"](https://docs.google.com/spreadsheets/d/1V_sbXAkSdhv8GIblYVUeeCUz1MpMGNlaflm2V4eUzUA/edit?usp=sharing), the sort function is working as expected – Jack Brown May 17 '17 at 15:45
  • @JackBrown Thanks for your help. I am not sure why when I look in the debugger it is shows the testIndex as undefined. Either way thanks for your help. – Bokai May 17 '17 at 16:14

0 Answers0