ExcelScript: getNumberFormat from column A to C

user_11103916 0
Viewed 252

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?

office-scripts power-automate excel
2 Answers

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.

enter image description here

=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\")");
}
user_120146060

Here is my VBA code where I use a for loop:

Sub GetHierachyParents()
    Set appExcel = holeAnwendung("Excel.Application")
        
    Set wbkExcel = appExcel.Workbooks.Open("C:\impport.xlsx")
                    
    Set wksExcel = wbkExcel.Sheets("Hierachy")                      
    countRows = wksExcel.Range("A1").End(xlDown).Row
    
    For i = 2 To countRows
        Node = 0
    
        If (Len(wksExcel.Range("A" & i).NumberFormat) - Len(Replace(wksExcel.Range("A" & i).NumberFormat, "[-]", "")) > 0) Then
            IndentLevel = (Len(wksExcel.Range("A" & i).NumberFormat) - Len(Replace(wksExcel.Range("A" & i).NumberFormat, " ", "")) - 1) / 2
            Node = -1
        Else
            IndentLevel = (Len(wksExcel.Range("A" & i).NumberFormat) - Len(Replace(wksExcel.Range("A" & i).NumberFormat, " ", "")) - 5) / 2
            Node = 0
        End If
        
        sql = "INSERT INTO impTemp0 (F1, F2, F3, F4) VALUES ('" & wksExcel.Range("A" & i) & "', '" & Replace(wksExcel.Range("B" & i), "'", "") & "', " & IndentLevel & ", " & Node & ")"
        CurrentDb.Execute sql
    Next i
    
    CurrentDb.Execute "UPDATE [impTemp0] SET Parent = DMAX('id','[impTemp0]','[impTemp0].id<' & [impTemp0].id & ' AND [impTemp0].Level=' & [impTemp0].Level-1 & '')"
     
End Sub

And here is an example for the first 8 rows:

"  [-] "@
"    [-] "@
"      [-] "@
"        [-] "@
"          [-] "@
"                 "@
user_111039160
Related