Naoa Lee
Naoa Lee

Reputation: 155

Lost zero number when appendRow

When I use appendRow in Google Sheets, I lose the leading zero (when my data is a 'phone number), how can I keep it?

I tried getDataRange().setNumberFormat('@STRING@') before append but this did not work.

var rowdata = ["dinh","loc","09182734756"]
ws.appendRow(rowdata );

Here's an image of my current result:

result image

Upvotes: 1

Views: 1629

Answers (1)

0Valt
0Valt

Reputation: 10355

Actually, setNumberFormat() works fine, but you need to apply it via setNumberFormat('@') or setNumberFormats([['@']]) (depends on whether you want to set it on one cell or on a custom Range).

Please, remember to set number formats before setting values to the target Range.

function testZero() {  
  var sh = SpreadsheetApp.getActiveSheet();

  var v = ["dinh","loc","09182734756"]; //do not add the "'";

  sh.appendRow(['']); //no need to do this step, it's an example;

  var rng = sh.getRange(sh.getLastRow()+1,1,1,v.length);
      rng.setNumberFormats([['@','@','@']]); //for test simplicity, set on Range you need to be of 0\d* pattern;
      rng.setValues([v]);

}

UPD: credit goes to Tanaike - a more flexible (and less heavy) solution to accessing the last column needed is via the values Array length.

Upvotes: 3

Related Questions