0

I am trying to convert my DataTable in Powerautomate to Json format.

My Datatable from power automate after run will be like this:

DataTable

I run this code in power automate

SET NewVar TO {{ }}
SET NewVar['CSVTable'] TO CSVTable
Variables.ConvertCustomObjectToJson CustomObject: NewVar Json=> SpreadsheetData

The DataTable converted to Json in this format.

{"CSVTable":[
{
"Column#1":"Account Number:","Column#2":"???","Column#3":null,"Column#4":null,"Column#5":null,"Column#6":null,"Column#7":null,"Column#8":null,"Column#9":null,"Column#10":null,"Column#11":null,"Column#12":null
},
{...}
]

I was trying to convert to Google Sheets API JSON format like this.

{
"range":"Sheet1A1:D5",
"majorDimension":"ROWS".
"values":[
["Account Number","???",null,null,...],
["Account Name","???",null,null,...]
],
}

This code is from Google Sheets API

My code is wrong, how to modify it?

SET NewVar TO {{ }}
SET NewVar['CSVTable'] TO CSVTable
Variables.ConvertCustomObjectToJson CustomObject: NewVar Json=> SpreadsheetData
Ken White
  • 123,280
  • 14
  • 225
  • 444
yeat
  • 1
  • 1

1 Answers1

0

I found the answer myself, although it wasn't a direct solution. I used excelVBA to help me. The main thing is to start the ExcelMacro function and organize the data into the way I want

The following is part of the PAD code：

Excel.LaunchExcel.LaunchAndOpen Path: CurrentItem Visible: True ReadOnly: False LoadAddInsAndMacros: True Instance=> ExcelInstance2
Excel.RunMacro Instance: ExcelInstance2 Macro: $'''vba_merge'''
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance2
File.ReadFromCSVFile.ReadCSV CSVFile: CurrentItem Encoding: File.CSVEncoding.UTF8 TrimFields: True FirstLineContainsColumnNames: False ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault CSVTable=> CSVTable2
SET SpreadsheetData TO $'''{
  \"range\": \"%CurrentItem2[2]%!A1:K999\",
  \"majorDimension\": \"COLUMNS\",
  \"values\": [
    [%CSVTable2[0]['Column#1']%],
    [%CSVTable2[0]['Column#2']%],
    [%CSVTable2[0]['Column#3']%],
    [%CSVTable2[0]['Column#4']%],
    [%CSVTable2[0]['Column#5']%],
    [%CSVTable2[0]['Column#6']%],
    [%CSVTable2[0]['Column#7']%],
    [%CSVTable2[0]['Column#8']%],
    [%CSVTable2[0]['Column#9']%],
    [%CSVTable2[0]['Column#10']%],
    [%CSVTable2[0]['Column#11']%]
  ]
}'''

Below is the code for Excel VBA：The main function of this is to merge all rows into columns and arrange them in Json format. Of course this ExcelVba is too simple and brutal, it can be continued to optimize and reduce the code.

Sub vba_merge()

Application.DisplayAlerts = False

Dim val As String
Dim rng As Range
Dim Cell As Range
Set rng = Range("A1:A999")
For Each Cell In rng
    val = val & Chr(34) & Cell.Value & Chr(34) & ","
Next Cell
With rng
    .merge
    .Value = Trim(val)
End With

Dim val2 As String
Dim rng2 As Range
Dim Cell2 As Range
Set rng2 = Range("B1:B999")
For Each Cell2 In rng2
    val2 = val2 & Chr(34) & Cell2.Value & Chr(34) & ","
Next Cell2
With rng2
    .merge
    .Value = Trim(val2)
End With

Dim val3 As String
Dim rng3 As Range
Dim Cell3 As Range
Set rng3 = Range("C1:C999")
For Each Cell3 In rng3
    val3 = val3 & Chr(34) & Cell3.Value & Chr(34) & ","
Next Cell3
With rng3
    .merge
    .Value = Trim(val3)
End With

Dim val4 As String
Dim rng4 As Range
Dim Cell4 As Range
Set rng4 = Range("D1:D999")
For Each Cell4 In rng4
    val4 = val4 & Chr(34) & Cell4.Value & Chr(34) & ","
Next Cell4
With rng4
    .merge
    .Value = Trim(val4)
End With

Dim val5 As String
Dim rng5 As Range
Dim Cell5 As Range
Set rng5 = Range("E1:E999")
For Each Cell5 In rng5
    val5 = val5 & Chr(34) & Cell5.Value & Chr(34) & ","
Next Cell5
With rng5
    .merge
    .Value = Trim(val5)
End With

Dim val6 As String
Dim rng6 As Range
Dim Cell6 As Range
Set rng6 = Range("F1:F999")
For Each Cell6 In rng6
    val6 = val6 & Chr(34) & Cell6.Value & Chr(34) & ","
Next Cell6
With rng6
    .merge
    .Value = Trim(val6)
End With

Dim val7 As String
Dim rng7 As Range
Dim Cell7 As Range
Set rng7 = Range("G1:G999")
For Each Cell7 In rng7
    val7 = val7 & Chr(34) & Cell7.Value & Chr(34) & ","
Next Cell7
With rng7
    .merge
    .Value = Trim(val7)
End With

Dim val8 As String
Dim rng8 As Range
Dim Cell8 As Range
Set rng8 = Range("H1:H999")
For Each Cell8 In rng8
    val8 = val8 & Chr(34) & Cell8.Value & Chr(34) & ","
Next Cell8
With rng8
    .merge
    .Value = Trim(val8)
End With

Dim val9 As String
Dim rng9 As Range
Dim Cell9 As Range
Set rng9 = Range("I1:I999")
For Each Cell9 In rng9
    val9 = val9 & Chr(34) & Cell9.Value & Chr(34) & ","
Next Cell9
With rng9
    .merge
    .Value = Trim(val9)
End With

Dim val10 As String
Dim rng10 As Range
Dim Cell10 As Range
Set rng10 = Range("J1:J999")
For Each Cell10 In rng10
    val10 = val10 & Chr(34) & Cell10.Value & Chr(34) & ","
Next Cell10
With rng10
    .merge
    .Value = Trim(val10)
End With

Dim val11 As String
Dim rng11 As Range
Dim Cell11 As Range
Set rng11 = Range("K1:K999")
For Each Cell11 In rng11
    val11 = val11 & Chr(34) & Cell11.Value & Chr(34) & ","
Next Cell11
With rng11
    .merge
    .Value = Trim(val11)
End With

Application.DisplayAlerts = True
End Sub

This idea is provided here. If there is a simpler way welcome to leave a comment

yeat
  • 1
  • 1