Change text to fixed number of digits (add zeroes) - enhance speed

Question

Goodday,

I have been using this function to make sure that all values in column A have either 5 digits (add zero's if less than 5) OR remain blank if there is no value in the cell.

It works fine as long as the number of rows is low. My number of rows have increased to almost 10.000 so it takes a really really long time to finish.

Can anyone help me speed this up to get and get results as in the green Col1? (all values are text)

function set5Digits () {
var app = SpreadsheetApp.getActive();
var ws = app.getSheetByName("Sheet1"); 
var values = ws.getDataRange().getValues();

  for (var i = 2; i < values.length; i++) {
      var n = ws.getRange("a"+ i).getValue();
      var format = 0;
      var length = n.toString().length;
      var result = "";
            
      if(length<5 && length>0){    
        format = 5;                
        var z = format - length;

            for(var x = 0; x < z; x++){
              result = result + "0";
            }
      ws.getRange("a" + i).setNumberFormat('@').setValue(result+n.toString());

      }
  }
}
 

Answer 1

Access to the sheet is slow, therefore, with a large increase in the number of rows, the total running time of the script increases significantly. Therefore it is better to read all the data into an array once, then process them (this is fast) and then unload the processed array onto a sheet.

Try it:

function set5Digits() {
  var app = SpreadsheetApp.getActive();
  var ws = app.getSheetByName("Sheet1");
  var lr = ws.getLastRow();
  var values = ws.getRange(2, 1, lr).getValues();
  var len = values.length;
  var res = [];
  var format = 5;
  for (var i = 0; i < len; i++) {
    var n = values[i];
    var length = n.toString().length;
    var result = "";

    if (length >= format) {
      res.push([n.toString()]);
    } else if (length == 0) {
      res.push([""]);
    } else {
      var z = format - length;
      for (var x = 0; x < z; x++) {
        result = result + "0";
      }
      res.push([result + n.toString()]);
    }
  }
  ws.getRange(2, 1, len, 1).setNumberFormat('@').setValues(res);
}