0

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();
  }
Nov1zar
  • 1
  • 2
  • You removed the slowest code, but you didn't change the execution time you recorded, in your question. It's highly unlikely that the current script you're showing in the current revision is slow. If that's indeed the case, add `console.time()` to each block and show the slowest part of the code with **logs** along with your sheet size(number of rows) - preferably as a new question. Also just use a plain loop with `break` instead of `.map` thrice with `indexOf` – TheMaster Dec 13 '21 at 18:22
  • i think i've fixed it. Changed thrice `indexOf` to `getRange` with defined `position` and also merged similar funcs to one returning object. Become more stable with 3-4 sec of execution – Nov1zar Dec 20 '21 at 12:26

0 Answers0