'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 |
|---|
