App Script - How can I Speed up My setBackground() function?

Question

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

Answer 1

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 to onEdit as a parameter by default (e in the sample below), but you should pass it to your changeColor 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 and totalCellCol.
• 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);
  }
}