text to columns very slow in office scripts

Question

I used the Record Actions to do Text to Columns and used that in my code. I get problems listed in the log as:

"Invoking read methods inside of loop conditions could lead to slow performance of the script"

The script is so slow, and I know it can be made more efficient. I'd appreciate any tips. Thank you.

Here is my code:

function main(workbook: ExcelScript.Workbook) {  
               	let sheet1 = workbook.getWorksheet("Sheet1");  
          
          
        	// Text to columns on range A1:A60 on sheet1  
        	for (let row = 0; row < sheet1.getRange("A1:A60").getRowCount() ; row++) {  
        		let sourceRange = sheet1.getRange("A1:A60");  
        		let destinationRange = sheet1.getRange("A1");  
        		let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[ ]/)  
        		destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]);  
        	}  
          
        }  
          
      
  
  

Answer 1

@Suzi -

You can find some general guidance/tips to improve the performance of your scripts here: https://learn.microsoft.com/en-us/office/dev/scripts/develop/web-client-performance

Specifically, below is my attempt to optimize the text-to-columns. I did some testing on a sample workbook with 60 rows of data. The old script took almost 8 seconds and the new script took only 0.3 seconds!

I feel there could be some room for improvement for the action recorder to generate more performant script for the "Text to Columns" action ...

   function main(workbook: ExcelScript.Workbook) {  
     let sheet = workbook.getWorksheet("Sheet1");  
     let sourceRange = sheet.getRange("A1:A60");  
     let sourceValues = sourceRange.getValues();  
     let rowCount = sourceRange.getRowCount();  
     let destinationValues: string[][] = [];  
     
     for (let row = 0; row < rowCount; row++) {  
       destinationValues[row] = sourceValues[row][0].toString().split(/[ ]/);  
     }  
     
     let maxColumnCount = destinationValues.reduce((prev, current) => Math.max(prev, current.length), 0);  
     destinationValues.forEach(row => row.length = maxColumnCount);  
     
     let destinationRange = sourceRange.getResizedRange(0, maxColumnCount - 1);  
     destinationRange.setValues(destinationValues);  
   }