Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Use a Google Script to display a dropdown menu in a HTML form in alphabetical order from a Google Sheet Doc

I have a successful Google script that allows me to grab the contents from a Google Sheets Doc and display it in a dropdown menu. This works perfectly but I don’t like the order it’s displayed in, I need to list the menu alphabetically but for some reason I can’t work it out.

Here’s the script:

function getColors() { 
  const sheet = SpreadsheetApp.openById("1234567").getSheetByName("tab");

  const colName = 'Media Type';
  const colNum = getColNumByName(colName);
  if (colNum === null) {
    Logger.log('Column ' + colName + ' was not found!');
    return [];
  }

  const firstRow = 2;
  const lastRow = sheet.getLastRow();

  // get all values from column
  const columnData = sheet.getRange(firstRow, colNum, lastRow).getValues().flat();

  // filter values on duplicates
  return columnData.filter((el, i) => i === columnData.indexOf(el) && el !== '');
}

function getColNumByName(colName, row = 1) {
  const sheet = SpreadsheetApp.openById("1234567").getSheetByName("tab");
  
  const [data] = sheet.getRange(row, 1, row, sheet.getLastColumn()).getValues();
  const col = data.indexOf(colName);

  // adding 1 because column nums starting from 1
  return col === -1 ? null : col + 1;
}

Can someone guide me please

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

Try

return columnData.filter((el, i) => i === columnData.indexOf(el) && el !== '').sort()
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading