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?

Ethan · Accepted Answer · 2021-04-23T09:50:47.853

2

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

edited Apr 23 '21 at 09:50

answered Apr 19 '21 at 11:09

Ethan

808
3
21

1

Wow this is super fast and works as expected. – Paintitblack3k Apr 19 '21 at 20:17
In the next step I need to get the lenght of each number format. Is there a solution like getLength wihich is also as fast as getNumberFormats – Paintitblack3k Apr 19 '21 at 22:12
you could use the getValueTypes() and then that will actually give you a string like boolean, double, empty, error, integer, richValue, string, or unknown. Is that what you are looking for? https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts#getValueTypes__ – Ethan Apr 20 '21 at 12:00
Unfortunately not, the numberFormat is always a string and in this case uses as a kind IndentLevel. So ich level corresponds to on level. Thatswhy I try to get the length of every single NumberFormat e.g. in Column D. Do you know what I am trying to say? – Paintitblack3k Apr 20 '21 at 19:39
do you mean you are using it as a Bill of Materials Cost Rollup with multiple indent levels? – Ethan Apr 21 '21 at 10:57
Yes it's like that. The table includes a hierachy in column A and I need to get the Level of each entry/row. – Paintitblack3k Apr 21 '21 at 12:51
can you add a screenshot of an example of the data? – Ethan Apr 21 '21 at 13:22
Sure. In Column A you will see the hierachie, in B the name and in C the NumberFormats. If it's includes a [+] than the entry is a node and I just count the space (minus 5) to get the hierarchy level. – Paintitblack3k Apr 21 '21 at 19:22
Okay, so what are you trying to accomplish now? if you get the length of every single format, what are you going to do with them? Can you give me the parameters for each number format? – Ethan Apr 21 '21 at 20:29
I added my vba code an answer that you see the correct format. I am using the level than to find you which are the parent entries of each entry. – Paintitblack3k Apr 22 '21 at 12:34

Paintitblack3k · Answer 2 · 2021-04-22T14:51:02.257

0

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:

"  [-] "@
"    [-] "@
"      [-] "@
"        [-] "@
"          [-] "@
"                 "@

edited Apr 22 '21 at 14:51

answered Apr 22 '21 at 12:32

Paintitblack3k

77
8

Add updated the post and simplified the script a bit. – Paintitblack3k Apr 22 '21 at 14:51
So is the first Indent Level 1? And what level would be the last row with no "[-]"? – Ethan Apr 22 '21 at 15:30
The last row with [-] would be Level 5 in this case. I count the spaces minus 1 and than divide with 2 (for all nodes). For all other entries like in row 6 I use a different formula. – Paintitblack3k Apr 23 '21 at 07:37
Check my updated answer, I dont think you need to use Number Formats, you can just use a formula like the one I have above. – Ethan Apr 23 '21 at 09:38
Can you kindly upload the Excel file? I translated into the german formulas and I am getting serveral errros message like the name "indentLevel" in unknown. – Paintitblack3k Apr 23 '21 at 11:23
maybe you don't have the LET function. https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 you need an Office 365 Subscription – Ethan Apr 23 '21 at 12:52

ExcelScript: getNumberFormat from column A to C

2 Answers2