Sort Range Values without sorting the spreadsheet

Question

I catch an unsorted range from a spreadsheet to create a ListItem in google forms. I want this list item to be sorted alphabetically but once I get the range, if I use range.sort() it also sorts the data on the spreadsheet. Any suggestions on how to sort the values but not the spreadsheet?

So far I have this code:

//Import Clients List
var sheetClients = ss.getSheetByName("Clients");
var range = sheetClients.getRange(1, 1, sheetClients.getLastRow());
range.sort(1);
var values = range.getValues();
var item = formulari.addListItem();
item.setTitle("Client");
item.setChoiceValues(values);

Thanks.

I imagine it is the form itself but you should show every variable definition when presenting a code sample... to avoid confusion and bad answers. — Serge insas, Sep 02 '14 at 15:18

score 2 · Answer 1 · edited Sep 01 '16 at 17:07

2

I'm sorry for the misinformation about the "formulari". That is a form class.

the values.sort(); doesn't work as sort(); only works in a range class not in a string array as the values is.

I found out the solution using this new libraries https://sites.google.com/site/scriptsexamples/custom-methods/2d-arrays-library

The new code would be like:

//Import Clients List
var sheetClients = ss.getSheetByName("Clients");
var range = sheetClients.getRange(1, 1, sheetClients.getLastRow());

// CHANGE HERE from range.sort( 1 ) 
var new_range = ArrayLib.sort( range , 1 , true );

var values = new_range.getValues();
var item = formulari.addListItem();
item.setTitle("Client");
item.setChoiceValues(values);

Thanks.

Marc.

edited Sep 01 '16 at 17:07

inye

1,786
1
23
31

answered Sep 03 '14 at 14:31

MarcJordi

33
2
6

1

Glad you got it working however, sort should definitely work on a string array so perhaps you had an array issue(logger.log helps here). See Serge's(Gapps script Legend) comment above...Google apps is a blend of javascript and some of their own libraries and I know it 100% without a doubt it absolutely would be.sort() in a javascript string array that would do the trick. – Kevrone Sep 05 '14 at 21:36
@ Kevrone: thanks :-) @MarcJordi: please have a look at this recent post, it shows how to sort 2 D arrays: http://stackoverflow.com/questions/26300610/google-script-sort-2d-array-by-any-column (read also the comments) – Serge insas Oct 10 '14 at 22:45

score 1 · Answer 2 · answered Sep 02 '14 at 13:27

1

You should have an array named values from the getValues() method. Just sort the values and not the range....

var values = range.getValues();
values.sort();

answered Sep 02 '14 at 13:27

Kevrone

614
2
11
22

1

[this doc](https://developer.mozilla.org/en/docs/Web/JavaScript/Reference/Global_Objects/Array/sort) can help to go further ;-) – Serge insas Sep 02 '14 at 16:17

score 0 · Answer 3 · answered Oct 09 '14 at 21:02

I suggest you add to your project a code file with underscore.js then use the features to sort and filter (and many more great stuff).

Exemple

function getListClients() {
   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Clients');
   var range = sheet.getRange( "A1:A" + sheet.getMaxRows()).getValues();

   // using Underscore.js
   var arrayClients = _.chain(range).flatten().uniq().without('').value();

   return arrayClients;
}

Sort Range Values without sorting the spreadsheet

3 Answers3

Linked