How to make my Google Apps script run faster for spreadsheet multi-color gantt-chart?

Question

I am formatting a google sheet's columns and rows into a big gantt chart.This chart is to display how many weeks each staff person is occupied for each project. Each person has a unique color bar. The position and length of the color bar are based on the start week and duration of the project. I created a "Update All" button and assigned the function update_all() to the button. Once I run this function, I want all the color bars to be updated.

My script works OK. However, it takes 40 seconds to update 70 rows, which makes it difficult to scale it to a larger staff group. The action that takes longest is to set the background color of the cells with a designated color of a staff person. Any advice how to improve the script to make it faster?

Here is what the gantt chart looks like -- goole sheet file https://docs.google.com/spreadsheets/d/1nvnZB62CYUeUUZSkAuHsxMJF5MBr7D1rNG3ffU8jKdI/edit?usp=sharing

Here is my code

function updateall(){

     var sss = SpreadsheetApp.openById("1nvnZB62CYUeUUZSkAuHsxMJF5MBr7D1rNG3ffU8jKdI");
  var ssColor = sss.getSheetByName("2. Color Legend");
  var ssPlanner = sss.getSheetByName("Project Planner");

  // Step 1: clear all the orginal color 
  ssPlanner.getRange("I4:BU120").setBackground('#ffffff');

  // Step 2: create a dictionay {staff name : coloe code}
   var keycolumns = ssColor.getRange(3,2,16,1).getValues();
  var data = ssColor.getRange(3,3,16,1).getValues();
  var dict_data = {};
  for (var keys in keycolumns) {
    var key = keycolumns[keys];
    dict_data[key] = data[keys];
  }

  Logger.log(dict_data["BBB"]);

  //Step3:set background color for each row
    for (var bRow=4; bRow<121; bRow++){
    if (ssPlanner.getRange("E"+bRow).getValue()!=""){

    var start = ssPlanner.getRange(bRow,7).getValue()-ssPlanner.getRange(3,9).getValue()+9;
    var duration = ssPlanner.getRange(bRow,8).getValue();

  ssPlanner.getRange(bRow,start,1,duration).setBackground(dict_data[ssPlanner.getRange(bRow,5).getValue()]);

  }
  }
 }

Answer 1

I believe your goal as follows.

• You want to reduce the process cost of your script.

For this, how about this answer?

Modification points:

• In your script, getRange, getValue and setBackground are used in the loop. I think that in this case, the process cost of the script will be high. In order to reduce the cost of your script, I would like to propose the following flow. In this modification, I modified Step3 in your script.

  1. Retrieve all values from the range of E3:I121.
     • getValues() is used.
  2. Create an array for putting the colors using the retrieved values.
     • In this case, the color you want to set is used from dict_data you created. And, the cells which have no colors are set as null. So in your case, ssPlanner.getRange("I4:BU120").setBackground('#ffffff') might be able to be modified to ssPlanner.getRange("I4:BU120").setBackground(null).
     • getRange, getValue and setBackground are not used.
  3. Set the colors using the created array including the color codes.
     • setBackgrounds is used.

When above flow is reflected to your script, it becomes as follows.

Modified script:

When your script is modified, please modify as follows.

From:

//Step3:set background color for each row
  for (var bRow=4; bRow<121; bRow++){
  if (ssPlanner.getRange("E"+bRow).getValue()!=""){

  var start = ssPlanner.getRange(bRow,7).getValue()-ssPlanner.getRange(3,9).getValue()+9;
  var duration = ssPlanner.getRange(bRow,8).getValue();

ssPlanner.getRange(bRow,start,1,duration).setBackground(dict_data[ssPlanner.getRange(bRow,5).getValue()]);

}
}

To:

// Step3:set background color for each row
// 1. Retrieve all values from the range of `E3:I121`.
const values = ssPlanner.getRange("E3:I121").getValues();
const offset = values.shift()[4];

// 2. Create an array for putting the colors using the retrieved values.
const colors = values.reduce((ar, [e,f,g,h]) => {
  let base = Array(65).fill(null);
  if (e != "") Array.prototype.splice.apply(base, [g - offset, h].concat(Array(h).fill(dict_data[e][0])));
  ar.push(base);
  return ar;
}, []);

// 3. Set the colors using the created array including the color codes.
ssPlanner.getRange(4, 9, colors.length, colors[0].length).setBackgrounds(colors);

References:

• reduce()
• setBackgrounds(color)