I need help with my first Google Script. I have a dropdown menu that displays a list of items from my Google Sheets doc which works perfectly.
The first menu displays a list of media types which removes any duplicates and sorts them out alphabetically. I’m now trying to add another dropdown menu that’s dynamic so when the user selects a media type, only the brands for that media type show. Once the user selects from the second menu, I want the price to appear at the bottom of the form that matches the media type and brand.
First dropdown menu (this is working perfect)
function getColors() {
const sheet = SpreadsheetApp.openById("123456").getSheetByName("Vinyl-Prices");
const colName = 'Media Type';
const colNum = getColorColNumByName(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 !== '').sort()
}
function getColorColNumByName(colName, row = 1) {
const sheet = SpreadsheetApp.openById("123456").getSheetByName("Vinyl-Prices");
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;
}
That works as it should.
I now have 2 scripts for the second part of the form. The first displays the list of brands but it’s not linked to the first menu meaning every brand shows whether it matches or not.
Here:
function getFruits(color) {
const sheet = SpreadsheetApp.openById("123456").getSheetByName("media Costs");
const colName = 'Brand';
const colNum = getFruitColNumByName(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 !== '').sort()
}
The second script works as it should but it doesn’t do other stuff that I need it to do such as list the items in alphabetical order or allow me to select the column by name.
function getFruits(color) {
const sheet = SpreadsheetApp.openById("17iaOdOS9N09tFb38w4P9GC8furzy33sXVnQK8dIhPnI").getSheetByName("Vinyl Costs");
var getLastRow = sheet.getLastRow();
var return_array = [];
for(var i = 2; i <= getLastRow; i++)
{
if(sheet.getRange(i, 1).getValue() === color) {
return_array.push(sheet.getRange(i, 2).getValue());
}
}
return return_array;
}
Finally ,I need the form to display the price at the bottom based on what media and brand was selected. I have attached my html for but why can’t I figure the second form out and add the price at the end, this is simple stuff.
HTML form
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function GetFruit(color)
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
fruit.length = 0;
let option = document.createElement("option");
option.value = "";
option.text = "";
fruit.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
fruit.appendChild(option);
});
}).getFruits(color);
};
</script>
</head>
<body>
<h1>Web App Dependent Drop Down</h1>
<?var url = getUrl();?>
<form method="post" action="<?= url ?>" >
<label style="font-size: 20px" >Name</label><br>
<input type="text" name="name" style="font-size: 20px" /><br><br>
<label style="font-size: 20px" >Colors</label><br>
<select name="color" style="font-size: 20px" onchange="GetFruit(this.value)" >
<option value="" ></option>
<? for(var i = 0; i < colors.length; i++) { ?>
<option value="<?= colors[i] ?>" ><?= colors[i] ?></option>
<? } ?>
</select><br><br>
<label style="font-size: 20px" >Fruit</label><br>
<select name="fruit" id="fruit" style="font-size: 20px" >
</select><br><br>
<label style="font-size: 20px" >Blank - Price will got here</label><br>
<select name="fruit2" id="fruit2" style="font-size: 20px" >
</select><br><br>
<input type="submit" name="submitButton" value="Submit" style="font-size: 20px" />
<span style="font-size: 20px" ><?= message ?></span>
</form>
</body>
</html>
