CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
German Estrada Pacheco
German Estrada Pacheco

Reputation: 27

How to show left zeros in a cell ? '001 +1 = '002 via Google Script App

I need to do in code anything as simple as this in VBA it would be

n="'001"

n= "'" +right("000" + str((val( rigth(n,3))+1)),3)

Final result n="'002"

So, I need to add 1 in a column that previous cell has value = "'001" . This is not working

  var n= parseInt(s.getRange(row-1,9).getValue().substring(2,3),10)+1;
  console.log("n " + n);

   s.getRange(row,9).setValue("'"+("000"+ n.toString()).substring(-4,3));

Upvotes: 0

Views: 1196

Answers (2)

Cooper
Cooper

Reputation: 64100

Try this:

function showLeftZeroes() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(1,1,10,5);
  var vA=rg.getValues();
  var fA=rg.getNumberFormats();
  for(var i=0;i<vA.length;i++) {
    vA[i][0]=i+1;
    fA[i][0]="000";    
    vA[i][1]='+';
    vA[i][2]=1;
    fA[i][2]="###";
    vA[i][3]=" = ";
    vA[i][4]=vA[i][0]+vA[i][2];
    fA[i][4]="000"
  }
  rg.setNumberFormats(fA);
  rg.setValues(vA);
}

Number formats

Upvotes: 0

Tanaike
Tanaike

Reputation: 201553

  • You want to achieve 002 by adding 1 to 001.
    • For example, when the cell "I1" has '001, you want to put `'002' to the cell "I2".
  • You want to use ' of the top of characters.
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

In this modified script, it supposes that the cell "I1" has '001.

Pattern 1:

In this pattern, the value retrieved from the cell "I1" is converted to the number and add 1, and then, the zero padding is run with the 3 digits. The value is put to the cell "I2" by adding '.

Sample script:

// var row = 2; // For cell "I1"
var n = parseInt(s.getRange(row-1,9).getValue(), 10) + 1;
s.getRange(row,9).setValue("'" + ("000" + n).slice(-3));

  • If you don't want to reflect the zero padding When the previous value is more than 1000, you can use the following script.

    s.getRange(row,9).setValue("'" + (n.toString().length <= 3 ? ("000" + n).slice(-3) : n));

Pattern 2:

In this pattern, the value retrieved from the cell "I1" is converted to the number and add 1. The value is put to the cell "I2" by changing the format to 000. In this case, the put value can be used as the number.

Sample script:

// var row = 2; // For cell "I1"
var n = parseInt(s.getRange(row-1,9).getValue(), 10) + 1;
s.getRange(row,9).setNumberFormat("000").setValue(n);

Note:

  • In your script, if row is 1, an error occurs at s.getRange(row-1,9) because row-1 is 0. Please be careful this.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 2

Related Questions