I'm struggling with my setBackground()
function on App script. How can I speed it up? It's working but the execution is very slow.
I have written this:
function changeColor(sheetName, startColorCol, sizeCellCol, totalCellCol) {
var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
for (z = startColorCol; z <= totalCellCol; z = z sizeCellCol) {
// As this is called onEdit() we don't want to perform the entire script every time a cell is
// edited- only when a status cell is mofified.
// To ensure this, before anything else we check to see if the modified cell is actually in the status column.
if (sSheet.getActiveCell().getColumn() == z) {
var row = sSheet.getActiveRange().getRow();
var value = sSheet.getActiveCell().getValue();
var col = "white"; // Default background color
var colLimit = z; // Number of columns across to affect
switch (value) {
case "fait":
col = "MediumSeaGreen";
break;
case "sans réponse":
col = "Orange";
break;
case "proposition":
col = "Skyblue";
break;
case "Revisions Req":
col = "Gold";
break;
case "annulé":
col = "LightCoral";
break;
default:
break;
}
if (row >= 3) {
sSheet.getRange(row, z - 2, 1, sizeCellCol).setBackground(col);
}
}
}
}
I saw I might need to use batch operations but I have no idea how to make it works.
The thing is, I need to color a range of cells when the value of one is changed. Any ideas ?
Thanks
CodePudding user response:
Issues:
- You only want to check a single cell, the cell that was edited (
var value = sSheet.getActiveCell().getValue();
). Therefore, it doesn't make sense to use a loop.
Solution:
- Use the event object to get the data regarding the edited cell (sheet, column index, row index, etc.), instead of using a loop and checking
getActiveCell().getColumn()
each time. This event object is passed toonEdit
as a parameter by default (e
in the sample below), but you should pass it to yourchangeColor
function as an argument. - Before doing anything else, check whether the edited cell is one of the edited cell is in the range you are tracking (correct sheet, row over 3, column between
startColorCol
andtotalCellCol
. - If the edited cell is in the proper range, update the background colors.
Code sample:
function onEdit(e) {
// ...Some stuff...
changeColor(e, sheetName, startColorCol,sizeCellCol, totalCellCol);
}
function changeColor(e, sheetName, startColorCol,sizeCellCol, totalCellCol) {
const range = e.range;
const column = range.getColumn();
const row = range.getRow();
const sSheet = range.getSheet();
if (sSheet.getName() === sheetName && column >= startColorCol && column <= totalCellCol && row >= 3) {
const value = range.getValue();
let col = "white"; // Default background color
switch (value) {
case "fait":
col = "MediumSeaGreen";
break;
case "sans réponse":
col = "Orange";
break;
case "proposition":
col = "Skyblue";
break;
case "Revisions Req":
col = "Gold";
break;
case "annulé":
col = "LightCoral";
break;
default:
break;
}
sSheet.getRange(row, column-2, 1, sizeCellCol).setBackground(col);
}
}