Sort Range Values without sorting the spreadsheet

0
votes

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.

3
google-apps-scriptgoogle-spreadsheet-apigoogle-forms
what is exactly "formulari" ? what argument does it take ? – Serge insas
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

3 Answers

2
votes

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.

1
votes

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();
0
votes

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