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.
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;
});