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

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:

split my sheet into several sheets (not as helpful for my use case)
add a button to run the app only when I make an edit (not as nice as an onEdit)

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
          } 
       } 
     } 
  }

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

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);
}

In this case, first, the values and background colors are retrieved from the data range. And then, an array including the background colors is created. And, the created array is put to the column "A".

Modified script:

References: