I have a script embedded into a spreadsheet. The purpose of the script is to fill a range with some formulas in a specific sheet every time a row is inserted. I installed a change trigger manually. The problem is that trigger runs all the time, even when the spreadsheet is not in use. Is this the correct behavior of the change installable trigger?
Code:
const ss = SpreadsheetApp.getActiveSpreadsheet();
const current_sheet = ss.getActiveSheet();
const current_sheetName = current_sheet.getName();
const R = current_sheet.getCurrentCell().getRow();
function onChange(e) {
let
rows,
formula_insercao,
formula_subcategoria,
formula_cliente,
formula_produto,
formula_categoria
;
if(e.changeType === 'INSERT_ROW' && (current_sheetName === 'music' || current_sheetName === 'press')) {
rows = current_sheet.getMaxRows();
for(let i = R; i <= rows; i++) {
formula_insercao = `IFERROR(IF(K${i}=""; ""; FILTER(JOB_abertura; JOB_numeracao=K${i}; JOB_parcela=L${i})))`;
formula_subcategoria = `IF(K${i}=""; ""; "INFLUENCER")`;
formula_cliente = `IF(K${i}=""; ""; IFERROR(FILTER(JOB_cliente; JOB_numeracao=K${i}; JOB_parcela=L${i})))`;
formula_produto = `IF(K${i}=""; ""; IFERROR(FILTER(JOB_acao; JOB_numeracao=K${i}; JOB_parcela=L${i})))`;
formula_categoria = `IF(K${i}=""; ""; "CUSTO")`;
current_sheet.getRange(i, 4).setFormula(formula_insercao);
current_sheet.getRange(i, 10).setFormula(formula_subcategoria);
current_sheet.getRange(i, 13).setFormula(formula_cliente);
current_sheet.getRange(i, 14).setFormula(formula_produto);
current_sheet.getRange(i, 15).setFormula(formula_categoria);
}
} else {
console.log(
R,
current_sheetName,
e.changeType,
e.user,
);
}
}
I could see in the executions that when the trigger runs automatically, by no one user at spreadsheet, the changeType is other, the user is undefined and the change is always made in the first row of the first sheet of the spreadsheet.