Google Spreadsheet Values Push to Form with Google Apps Script, but Sort Alphabetically

Question

I have a Google Apps Script that fetches the data in a specific column in a spreadsheet and pushes that into a drop down menu in a Google Form.

This all works perfectly.

However, I'm trying to sort the data via the script so that before pushing it to the form, it sorts it alphabetically (A --> Z).

The data in the spreadsheet is not sorted, and I cannot just sort it in the spreadsheet because data is constantly being added.

Here is the Google Apps Script that works, except for the sorting:

function updateClientForm(){
  // call your form and connect to the drop-down item
  var form = FormApp.openById("1qszM48QILtnf-2QZa078ypqhjYHdB-VK2c0VMJDrsXo");

  //Inspect the element on the form to find the ID value
  var clientnamesList = form.getItemById("449574637").asListItem();


// identify the sheet where the data resides needed to populate the drop-down
  var ss = SpreadsheetApp.getActive();
  var clientnames = ss.getSheetByName("Client Names with ID");

  // grab the values in the first column of the sheet - use 2 to skip header row 
  var namesValues = clientnames.getRange(2, 1, clientnames.getMaxRows() - 1).getValues();

  var customerNames = [];

  // convert the array ignoring empty cells
  for(var i = 0; i < namesValues.length; i++)    
    if(namesValues[i][0] != "")
      customerNames[i] = namesValues[i][0];

  // populate the drop-down with the array data
  clientnamesList.setChoiceValues(customerNames);

}

Any ideas? Thanks in advance!

I don't see any code that sorts anything.. review your JavaScript reference of choice for how to sort arrays. — tehhowch

Aidan Aidan · Accepted Answer · 2018-05-25T08:37:36

Google Apps Script is based on javascript, so you have access to pretty much everything you can do in javascript.

namesValues is an array and javascript arrays come with a built-in sort function

sortedNamesValues = namesValues.sort();

Google Spreadsheet Values Push to Form with Google Apps Script, but Sort Alphabetically

1 Answers