performanceloopsfor-loopgoogle-apps-scriptcoding-efficiency

Can this appscript run faster/be rewritten to run faster when I have 200 rows?

1. Code description: I wrote this app script that for each row, colors the cell in column A the same color as the last cell of that row with text in it. Additionally, I use an onEdit trigger, so whenever I edit a row, the script runs. This worked alright when I had about 20 rows and 20 columns (2-3 seconds).

2. Problem: I now have a sheet with about 200 rows and 20 columns and the code is extremely slow (3-4 minutes or more).

3. QUESTION: How to make it run faster, or, given what I need, should I write this task in another way?

4. Solutions I thought about but don't like:

5. Code:

 function colorFirstCell() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName('courseX');
 var lastRow = sheet.getLastRow();
 var lastColumn = sheet.getLastColumn();
 var columnFirstCells = 1; // column of cell to be colored
 var dataRange = sheet.getRange(1,1, lastRow, lastColumn + 1).getValues(); 

for(var i = 0; i < lastRow; i++) 
  {   
  for(var j = 0; j < lastColumn; j++) { 
      if(dataRange[i][j] != '' && dataRange[i][j+1] == '') { // cell not empty and cell to the right is empty
         var backgroundColor = sheet.getRange(i + 1, j + 1).getBackground(); // get color
         sheet.getRange(i + 1, columnFirstCells).setBackground(backgroundColor); // set color 
         of first col cell
          } 
       } 
     } 
  }
Solution

  • I believe your goal is as follows.

    In this case, how about the following modification?

    Modified script:

    function colorFirstCell2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('courseX');
  var range = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn());
  var backgrounds = range.getBackgrounds();
  var colors = range.getDisplayValues().map((r, i) => {
    for (var j = r.length - 1; j >= 0; j--) {
      if (r[j] != "") {
        return [backgrounds[i][j]];
      }
    }
    return [null];
  });
  sheet.getRange(1, 1, colors.length).setBackgrounds(colors);
}

    References: