'Google Apps Script became very slow. Some days the script doesn't work

Hi! I use a spreadsheet with dependent dropdown lists. When the sheet was small, everything was fine, but now I even can't run it. There are a lot of Maybe need an optimization to my script. I only started to use Google Apps Script and don't know how to write codes correctly. So if I made a mistake, please let me know or if I made a mistake when posting it.

let colShift = 5;

function onEdit(e) {

  let row = e.range.getRow();
  let col = e.range.getColumn();
  let sheetName = e.source.getActiveSheet().getName();
  let name = e.value;
  let oldName = e.oldValue;
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet_1");
  let mask = JSON.stringify(sh.getRange(row, colShift+1, 1, col-colShift).getValues()[0]);
  let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet_2").getLastColumn();
  
  if(sheetName === "sheet_1" && name !== oldName && col < colMax+colShift) {
    fillColumn(row, col, mask);
  }
}

function fillColumn(row, col, mask) {

  let col_data = col - colShift;

// clear dataVal and Value
  let sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet_1");
  let colMax = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet_2").getLastColumn();
  sh.getRange(row, col+1, 1, colMax-col_data).clearDataValidations().clearContent();

// find data
  let sd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet_2");
  let arrData = sd.getDataRange().getValues();
  let arrData_2 = [];

  let iMax = arrData.length - 1;
  for(let i=1; i<=iMax; i++) {
    if(JSON.stringify(arrData[i].slice(0, col_data)) == mask) {
      arrData_2.push(arrData[i].slice(0, col_data+1));
    }
  }
  arrData_2 = arrData_2.map(item => item.pop());
  let uniqArrData_2 = arrData_2.filter(uniqValues);

// add dataVal
  col++;
  sh.getRange(row, col).setDataValidation(SpreadsheetApp.newDataValidation()
  .setAllowInvalid(false)
  .requireValueInList(uniqArrData_2, true)
  .build());

}

function uniqValues(item, index, arr) {
  return arr.indexOf(item) === index;
}


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source