I have a script that allows me to get the contents of a column from my Google Sheet and display it in my HTML form while removing any duplicates of the same name.
Example: red, red, yellow, yellow, blue, green would show in the dropdown menu as red, yellow, blue, green.
The thing is, I would like to get the column contents by name and not by number i.e 1.
Here is my script:
function getColors() {
var sheet = SpreadsheetApp.openById("1czFXXQAIbW9IlAPwHQ0D5S_a-Ew82p-obBEalJFNJTI").getSheetByName("Vinyl Costs");
var getLastRow = sheet.getLastRow();
var return_array = [];
for(var i = 2; i <= getLastRow; i++)
{
if(return_array.indexOf(sheet.getRange(i, 1).getValue()) === -1) {
return_array.push(sheet.getRange(i, 1).getValue());
}
}
return return_array;
}
I've found a similar question and the accepted answer was this:
function getByName(colName, row) {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var col = data[0].indexOf(colName);
if (col != -1) {
return data[row-1][col];
}
}
But I can't seem to make that work with mine? This is my first ever Google Script so I don't really understand it 100% yet.
I changed the functions a bit.
For one thing, getByName now gets not all values of the sheet, but only the first row.
function getColors() {
const sheet = SpreadsheetApp.openById("1czFXXQAIbW9IlAPwHQ0D5S_a-Ew82p-obBEalJFNJTI").getSheetByName("Vinyl Costs");
const colName = 'your_column_name';
const colNum = getColNumberByName(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("1czFXXQAIbW9IlAPwHQ0D5S_a-Ew82p-obBEalJFNJTI").getSheetByName("Vinyl Costs");
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;
}