1

enter image description here

I am using apps script in a google sheet, and I've added the lodash library. I have the following code:

  var sheet =    SpreadsheetApp.openById(id).getSheetByName('mysheet');
  var my_rows = sheet2Json(sheet) // USE THIS RATHER THAN OTHERS - BETTER JSON PRODUCED

  var data = _.sortBy(my_rows, 'Index');

where:

function sheet2Json(sheet) { 

  var HeaderRow = sheet.getFrozenRows(); // THIS SHOULD GET HEADER ROW   
  var titleColumns = sheet.getDataRange().offset(HeaderRow-1, 0, 1).getValues()[0];

  var lastRow = sheet.getLastRow();
  var rowValues = [];


  var colStartIndex = 1;
  var rowNum = 1;
  //    var range = sheet.getRange(rowIndex, colStartIndex, rowNum, sheet.getLastColumn());
  var range = sheet.getRange(HeaderRow, 1, sheet.getLastRow(), sheet.getLastColumn());

  var rowValues = range.getValues();

  var jsonArray = [];
  for(var i=0; i<rowValues.length; i++) {
    var line = rowValues[i];
    var json = new Object();
    for(var j=0; j<titleColumns.length; j++) {
      json[titleColumns[j]] = line[j];
    }      
    json['relativeRow'] = i; //DATA AFTER HEADER ROW
    jsonArray.push(json);
  }

  return jsonArray;

}

The sheet2Json function creates an array of objects with one object per row. I want to sort the rows by index number. If I format the 'Index' column to text, it works. However, if I format the column to Number format, (which I need for proper sorting), I get:

Comparison method violates its general contract. (line 953, file "logashgs", project "LodashGS")

How can I get the sortby function working for a key containing numbers?

edit: an example of my_rows is:

[{relativeRow=11.0,  Index=2115.0, Email=  dfgdfg@dsfsd.com , completed=, Name=  dfdsf dsfdsfdsf , last_contacted=INVALIDNUM, date_time=Thu Dec 06 00:30:16 GMT-05:00 2018,  campaign=, Phone Number=1},{..},..]

edit2:

https://github.com/lodash/lodash/blob/3ac4b261e4a920a07673d49edfb84bd317f449f7/dist/lodash.js

enter image description here

user1592380
  • 34,265
  • 92
  • 284
  • 515
  • Perhaps [this](https://stackoverflow.com/questions/8327514/comparison-method-violates-its-general-contract) will help. I'm guessing that you have to provide your own compare function. – Cooper Feb 16 '19 at 18:16
  • @user61629 In order to correctly replicate your situation, can you provide the sample values of ``my_rows``? By the way, LodashGS that you are using is [this](https://github.com/oshliaer/lodashgs)? – Tanaike Feb 16 '19 at 23:54
  • I am using lodash 4.17.11, a my_rows sample is above. – user1592380 Feb 17 '19 at 02:43
  • 1
    @user61629 Thank you for replying and updating it. But I have to apologize. Because unfortunately, I couldn't replicate your error using your additional value. Can I provide the sample script for replicating your situation? By the way, can I ask you about the script of ``line 953, file "logashgs", project "LodashGS"``? – Tanaike Feb 17 '19 at 04:13
  • @Tanaike, Looking at the source code at line 953, it looks like the definition of sortBy, please see edit 2 – user1592380 Feb 17 '19 at 16:31
  • @user61629 Thank you for adding the information. I posted an answer from your additional information. Could you please confirm it? If this was not the result you want, I apologize. – Tanaike Feb 17 '19 at 22:14

1 Answers1

1

How about this modification?

Modification points:

  • I thought that the reason of error might be sheet2Json(sheet).
    • When I see var range = sheet.getRange(HeaderRow, 1, sheet.getLastRow(), sheet.getLastColumn()), the data is retrieved by including the header row.
    • How about retrieving only the main data range?

Modified script:

Please modify sheet2Json(sheet) as follows and try again.

From: 
var range = sheet.getRange(HeaderRow, 1, sheet.getLastRow(), sheet.getLastColumn());
To: 
var range = sheet.getRange(HeaderRow + 1, 1, sheet.getLastRow(), sheet.getLastColumn());

Note:

  • Of course, I think that the for loop can be also modified for this situation. But I thought that retrieving the main data might be suitable for your script, because titleColumns has already been retrieved.
Tanaike
  • 181,128
  • 11
  • 97
  • 165