Office Script Text to columns very slow

Question

I made a script that applies a formula in a column, then, copy as values and later remove the alpabetic characters, so for example 15T -> 15.

The problem is that the office detect them as number stored as text, and if I run a protion of the code to make them stored as number it is extreamly slow. If I do it as Data -> Text to Columns it takes only a couple of seconds.

Can anyone help me?

The code I am using is:

for (let row = 0; row < selectedSheet.getRange("V1:V23328").getRowCount() ; row++) {
let sourceRange = selectedSheet.getRange("V1:V23328");
let destinationRange = selectedSheet.getRange("W1");
let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[\t]/)
destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]);

The numbers of row are ok by the way.

I even tried with this too:

//cc is current column
for (let rowv = 0; rowv < selectedSheet.getRange(cc + firstrow + ":" + cc + lastRow).getRowCount(); rowv++) {
        let sourceRange = selectedSheet.getRange(cc + firstrow +    ":" + cc + lastRow);
        let destinationRange = selectedSheet.getRange(cc  + row);
        let sourceRangeValues = sourceRange.getRow(rowv).getValues()[0][0].toString().split(/[\t]/)
        destinationRange.setNumberFormat;
        destinationRange.getOffsetRange(rowv, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]);
    }

Answer 1

Using Record Actions is a useful starting point for generating script code, but it may need to be optimized for efficiency.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let sourceValues = selectedSheet.getRange("V1:V23328").getTexts();
    let resultValues = sourceValues.map(x => x[0].split(/[\t]/));
    let destinationRange = selectedSheet.getRange("W1")
        .getResizedRange(resultValues.length - 1, resultValues[0].length - 1);
    destinationRange.setNumberFormat("0");
    destinationRange.setValues(resultValues);
}