I've been working on a sheet to make a calculator that I'll use on my work, it has lots of custom functions, it's working fine. But I have like 10 functions that need to run, so I've used triggers to run those functions every time the sheet was edited. But it stopped working, and it was too resource heavy (running all the functions every time something was edited).
So how can I make a trigger function that will run one of my custom functions only if a specific cell value is edited?
In this example I want the function ifEntrada() to run when the value in cell B4 is altered:
function ifEntrada() {
var calcular_range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Apartamentos").getRange("B4");
var calcular = calcular_range.getValue();
if (calcular == "Inserir Valor Manualmente") {
showRows_entrada_manual();
} else {
hideRows_entrada_manual()
}
}Obviously you have declared triggers observing edits on the sheet which can easily run into rate limits. You have to use the built in funcion onEdit() instead:
function onEdit(e) {
var activeRow = e.range.getRow();
var activeCol = e.range.getColumn();
if (activeCol == 2 && activeRow == 4) ifEntrada(); // "2" represents the B
}
function ifEntrada() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Apartamentos');
// do things
}
Mind that edits done over a range with the monitored cell B4 within will trigger the onEdit function only if B4 is the first cell where the edit takes effect.