i'w working with a webform that kinda familiar to vlookup: it looks throw a range of values to find a matching number in a row, and returns values of the colums of the same row to a webform. This is a part of inventory management via google-sheets
So the problem is that sometimes execution time is too slow, about 90-300 sec, while average execution time is about 3-6 sec due to large collection (6k rows)
i've tried using .filter() to make the collection shorten (1,5k rows) but it didn't help me (same result same problems)
Here's the google appscript part:
function checkSendInfo(bCode){
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Base");
var data = ws.getRange(1,2,ws.getLastRow(),4).getValues()
var bCodeList = data.map(function(r){return r[0].toString();});
var typeList = data.map(function(r){return r[1];});
var sizeList = data.map(function(r){return r[2];});
var position = bCodeList.indexOf(bCode);
Logger.log(position);
return [typeList[position],sizeList[position]];
}
function checkSendLocation(bCode){
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Base");
var data = ws.getRange(1,2,ws.getLastRow(),4).getValues()
var bCodeList = data.map(function(r){return r[0].toString();});
var locationList = data.map(function(r){return r[3];});
var position = bCodeList.indexOf(bCode);
Logger.log(position);
return locationList[position];
}
Here's the js part:
document.addEventListener('DOMContentLoaded', function() {
var elems = document.querySelectorAll('select');
var instances = M.FormSelect.init(elems);
google.script.run.withSuccessHandler(populateWords).getWords();
// google.script.run.withSuccessHandler(populateWords).getSendWords();
document.getElementById("butn").addEventListener("click",sendToKitchenItems);
document.getElementById("bcdn").addEventListener("input",getStype);
document.getElementById("bcdn").addEventListener("input",getSize);
// document.getElementById("bcdn").addEventListener("input",getPosition);
document.getElementById("bcdn").addEventListener("change",getStype);
document.getElementById("bcdn").addEventListener("change",getSize);
document.getElementById("bcdn").addEventListener("click",getStype);
document.getElementById("bcdn").addEventListener("click",getSize);
// document.getElementById("bcdn").addEventListener("change",getPosition);
});
function getStype(){
var bCode = document.getElementById("bcdn").value;
if(bCode.length === 10){
google.script.run.withSuccessHandler(updateType).checkSendLocation(bCode);
}else{
document.getElementById("location").value = "Не найден в базе";
M.updateTextFields();
} // end Else
} // end getStype
function updateType(cost){
document.getElementById("location").value = cost;
M.updateTextFields();
}
function getSize(){
var bCode = document.getElementById("bcdn").value;
if(bCode.length === 10){
google.script.run.withSuccessHandler(updateSize).checkSendInfo(bCode);
}else{
document.getElementById("size").value = "Не найден в базе";
M.updateTextFields();
} // end Else
} // end getSize
function updateSize(cost){
document.getElementById("size").value = cost;
M.updateTextFields();
}