ExcelScript: getNumberFormat from column A to C

Question

I need to get the NumberFormat from an excel file (appr. 3000 lines) and I wrote a simple Script which copy it from column A to column C each row by row. I tried to use this script with Power Automate after 10 minutes.

I already set the timeout on 60 minutes, but it seems that my tenant configuration abort it always after exactly 10 minutes.

Here is my script:

    function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getWorksheets()[0]

    let range = sheet.getUsedRange(true);
      let rows = range.getUsedRange(true).getRowCount();
      let cols = range.getUsedRange(true).getColumnCount();
      
      for (let row = 2; row <= rows; row++) {
        sheet.getRange("C" + row).setValue(sheet.getRange("A" + row).getNumberFormat())
      }
}

Is there an easier way to copy the NumberFormat from one column to another and does anybody know a better way?

Answer 1

Please make sure when you ask these questions to put the Office-Scripts tag since this is Office Scripts. You don't need to loop to achieve this. You can use getNumberFormats() like in the code below.

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getWorksheets()[0];
  let rowCount = sheet.getUsedRange(true).getRowCount();
  let numFormats = sheet.getRange("A1:A" + rowCount).getNumberFormats();
  let newRng = sheet.getRange("C1:C" + numFormats.length).setValues(numFormats);
}

EDIT 4/23/21

If you just want to get the indent levels, then you could just insert this formula in column C rather than getting the number formats.

=IFERROR(LET(indentLevel,FIND("[",FILTER(A:A,A:A<>""),1),SWITCH(indentLevel,4,1,6,2,8,3,10,4,12,5)),"Other Formula Here")

Here is the Office Scripts

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getWorksheet("Sheet1");
  sheet.getRange("C1")
    .setFormulaLocal("=IFERROR(LET(indentLevel,FIND(\"[\",FILTER(A:A,A:A<>\"\"),1),SWITCH(indentLevel,4,1,6,2,8,3,10,4,12,5)),\"Other Formula Here\")");
}