Apps Script to fill blanks with cell value above running very slowly

Question

I am currently using the code below to fill blank cells with cell value above:

function fillBlanks() {
  
  for (var r = 3; r <= sheetLR; r++) {
    for (var c = 2; c <= 8; c++) {
      
      if(sheet.getRange(r, c).getValue() == "") {
        
        var valInsert = sheet.getRange(r-1, c).getValue();
        sheet.getRange(r, c).setValue(valInsert);
        
      }   
    }
  }
}

The code works fine, however, it is very slow and keeps exceeding time limit. Any idea on how I can speed it up?

Thanks

Fazila

question from:https://stackoverflow.com/questions/65904918/apps-script-to-fill-blanks-with-cell-value-above-running-very-slowly

Answer 1

Methods that access the sheet like getRange(), getValue() and setValue() have a lot of execution time, thus they are not recommended to be executed inside loops.

The best practice is to get all the values you need from a range to an array, manipulate that array in a loop, then write the array back to the range.

Sample Code:

function fillBlanks() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var sheetLR = sheet.getLastRow();
  var range = sheet.getRange(2,2,sheetLR-1,7);
  var values = range.getValues();
  
  for (var r = 1; r < sheetLR-1; r++) {
    for (var c = 0; c < 7; c++) {
      
      if(values[r][c] == "") {
        
        values[r][c] = values[r-1][c];
        
      }   
    }
  }

  range.setValues(values);

}

This should do the same thing but with only two executions on the range regardless of size.