I have created a dropdown list using the item and description column of 'stock' sheet, which will be used to select an item in my 'RIS Form' sheet.
Now, I wanted to display the stock number and unit of an item every time an item is selected from the dropdown list. Right now, I am using the query function of google sheet but I wanted to have a appscript code for it.
Here is my current code, I have created arrays for the stock number, unit and items. I am thinking of getting the index of the item selected from the itemList and use it to get the stockNumber and unit of the item.
function dropItem() {
var sSheet = SpreadsheetApp.getActiveSpreadsheet();
var dropList = sSheet.getSheetByName('Stocks');
var range = dropList.getRange('A2:E161').getValues();
var risSheet = sSheet.getSheetByName('RIS Form');
var dropdownlist = risSheet.getRange('C11:C40');
var itemList = [];
var stockNumber = [];
var unit = [];
for (var i = 0; i < range.length; i++) {
if (range[i] != ''){
stockNumber.push(range[i][0]);
unit.push(range[i][1]);
itemList.push(range[i][4]);
} else {
break;
}
}
Logger.log(stockNumber)
Logger.log(unit)
Logger.log(itemList)
var valList = SpreadsheetApp.newDataValidation().requireValueInList(itemList);
dropdownlist.setDataValidation(valList);
//get index of each item in Column C
//set stock number and unit of items that matches the index of
Column C
for(var j = 0; j <= item.length; j++){
var matchedItem = itemList.indexOf(item);
sSheet.getRange(11, 1, item.length, 2).setValues([stockNumber[matchedItem],unit[matchedItem]]);}}
item
is not declared.setValues
is used in a loop, the process cost will become high. Ref (Author: me)sSheet
of sSheet.getRange(11, 1, item.length, 2).setValues([stockNumber[matchedItem],unit[matchedItem]])
might be risSheet
.When these points are reflected in your script, how about modifying as follows?
function dropItem() {
var sSheet = SpreadsheetApp.getActiveSpreadsheet();
var dropList = sSheet.getSheetByName('Stocks');
var range = dropList.getRange('A2:E161').getValues();
var risSheet = sSheet.getSheetByName('RIS Form');
var dropdownlist = risSheet.getRange('C11:C40');
var itemList = [];
var stockNumber = [];
var unit = [];
for (var i = 0; i < range.length; i++) {
if (range[i] != '') {
stockNumber.push(range[i][0]);
unit.push(range[i][1]);
itemList.push(range[i][4]);
} else {
break;
}
}
Logger.log(stockNumber)
Logger.log(unit)
Logger.log(itemList)
var valList = SpreadsheetApp.newDataValidation().requireValueInList(itemList); // Is this required to be used?
dropdownlist.setDataValidation(valList); // Is this required to be used?
// I modified the below script.
var values = dropdownlist.getDisplayValues().map(([c]) => {
var matchedItem = itemList.indexOf(c);
return [stockNumber[matchedItem] || null, unit[matchedItem] || null];
});
risSheet.getRange(11, 1, values.length, 2).setValues(values);
}
When this modified script is run, the values of "C11:C40" of "RIS Form" sheet are retrieved, and the values of columns "A" and "B" for "RIS Form" are created using the values of "C11:C40" of "RIS Form" and the values of "A2:E161" of "Stocks". And then, the result values are put into columns "A" and "B" for "RIS Form".
If my understanding is correct, I guessed that the following modification might be able to be also used.
function dropItem() {
var sSheet = SpreadsheetApp.getActiveSpreadsheet();
var risSheet = sSheet.getSheetByName('RIS Form');
var srcObj = new Map(sSheet.getSheetByName('Stocks').getRange('A2:E161').getValues().map(([a, b, , , e]) => [e, [a, b]]));
var values = risSheet.getRange('C11:C40').getDisplayValues().map(([c]) => srcObj.get(c) || [null, null]);
risSheet.getRange(11, 1, values.length, 2).setValues(values);
}
About I wanted to display the stock number and unit of an item every time an item is selected from the dropdown list.
, if the column of "Quantity" of "Stocks" is column "F", how about the following modification? By this modification, the value of column "F" of "Stocks" sheet is also put in to column "D" of "RIS Form" sheet.
function dropItem() {
var sSheet = SpreadsheetApp.getActiveSpreadsheet();
var risSheet = sSheet.getSheetByName('RIS Form');
var srcObj = new Map(sSheet.getSheetByName('Stocks').getRange('A2:F161').getValues().map(([a, b, , , e, f]) => [e, [a, b, e, f]]));
var values = risSheet.getRange('C11:C40').getDisplayValues().map(([c]) => srcObj.get(c) || [null, null]);
risSheet.getRange(11, 1, values.length, values[0].length).setValues(values);
}