Retain leading zero(s) after SPLIT function (google-apps-script)

1
votes

I have a column that contains cells with numbers and/or numbers separated by commas. Such as:

123, 456

987

321,5439,807

2024, 007,0193

I have a procedure that SPLITS and TRANSPOSES those cells based on commas: (that portion of the procedure is below)

var lastRow = ss.getDataRange().getNumRows();
var srcRange = ss.getRange(lastRow,2);
var splitCell = '=TRANSPOSE(SPLIT(' + srcRange.getA1Notation() + ',","))'; 
ss.getRange(lastRow,7).setFormula(splitCell);

The problem that I encounter is when one of the numbers contains a leading zero(s). So in other words, this...

2024, 007,0193

becomes this...

2024

7

193

The worksheet is formatted as text, so it's the SPLIT that seems to be interpreting and converting the 007 and the 0193 into integers of 7 and 193.

So how do I retain the leading zero(s) on the SPLIT side?

1
google-apps-scriptgoogle-docs

1 Answers

3
votes

You could try substituting the line:

var splitCell = '=arrayformula(transpose(substitute(split(substitute(' + srcRange.getA1Notation() + ',0,"#"),","),"#",0)))';