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

      }
  }
}

Boris Baublys · Accepted Answer · 2021-07-12T19:17:19.700

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

edited Jul 12 '21 at 19:17

answered Jul 11 '21 at 20:38

Boris Baublys

952
10
22

Thank you for the response. The speed has increased a lot! The result I'm getting with this code is: 1,a,,,1,,1.00000,,1,,,,1,,,1.00000,,,00001,a 22,bb,,,22,,22.00000,,22,,,,22,,,22.00000,,,00022,bb 333,ccc,,,333,,333.00000,,333,,,,333,,,333.00000,,,00333,ccc ,dddd,,,,,0.00000,,,,,,,,,0.00000,,,,dddd 55555,eeeee,,,55555,,55555.00000,,55555,,,,55555,,,55555.00000,,,55555,eeeee ,abcde,,,,,,,,,,,abcde. That is not completely what I was aiming for (I've added an example to my question just now) I only need to adjust the values (text) from the first column (A). – Caramel Jul 12 '21 at 06:55
In [my example](https://docs.google.com/spreadsheets/d/1tPq3XjvZDapbt-_3_OvM_5KxndM3__oKDgk3VZFLiZg/copy), everything works well. If you add a link to your spreadsheet, I can determine what the problem is. – Boris Baublys Jul 12 '21 at 13:20
https://docs.google.com/spreadsheets/d/1iC14zKaEghH-8xZxI6Fe5ZN__Iesxq2bu7J7Os6Pq7w/edit?usp=sharing Perhaps it also has to do with the fact that my values are strings not numbers? – Caramel Jul 12 '21 at 15:02
The original version of the post did not indicate that there are other columns on the sheet besides A. The script was written for this case. Since the situation is now clearer, I corrected the script in my answer. – Boris Baublys Jul 12 '21 at 18:56
1

Yes, that's it! Thank you Boris! Speed is great and so is your code. I'm very grateful. – Caramel Jul 12 '21 at 21:55

Cooper · Answer 2 · 2021-07-12T12:52:31.343

0

function set5Digits() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, 1).getValues();
  let c = sh.getRange(2, 1, sh.getLastRow() - 1, 1).getValues();
  vs.forEach((r, i) => {
    c[i][0] = Number(r[0]).toFixed(5).toString();
  });
  sh.getRange(2, 1, sh.getLastRow() - 1, 1).setNumberFormat('00000X').setHorizontalAlignment('right');
  sh.getRange(2, 1, c.length, c[0].length).setValues(c);
}

edited Jul 12 '21 at 12:52

answered Jul 11 '21 at 20:49

Cooper

59,616
6
23
54

thank you for your reply and solution. However, the result is different compared to the desired result. I'm getting this: 1.00000 22.00000 333.00000 0.00000 55555.00000 0.00000. Can you have another look. I've added an example to my question, hope that helps. Thanks again, I appreciate it. – Caramel Jul 12 '21 at 06:45

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

2 Answers2

Linked