Converting an array of data into columns in a spreadsheet

How To
,
1

Say you have an array of data like so

[
    {
        "Value": 300,
        "Qty": 1,
        "Notes": "Membership application. Level: PSA Member",
        "OrderDetailType": "MemberLevel",
        "Taxes": null,
        "__IMTINDEX__": 1,
        "__IMTLENGTH__": 2
    },
    {
        "Value": 500,
        "Qty": 1,
        "Notes": "Extras: One-Time Initiation Fee - $500",
        "OrderDetailType": "ExtraCost",
        "Taxes": null,
        "__IMTINDEX__": 2,
        "__IMTLENGTH__": 2
    }
]

This array comes as an output bundle for an intederminate set of operations – the Notes and Value keys are the key fields in question.

I’d like to create a spreadsheet where the Notes text become a column and the Value is added as a row under the appropriate column of data (related to the Notes of the bundle of course)

There will be other operations though that will continue to get new Notes fields or repeat existing ones and I want the value to go into a new row under the correct column. If new data is reached in Notes in a subsequent operation I’d like a new column to be created.

Here’s another operation through the execution of the scenario with a different output bundle in terms of Notes and Value

[
    {
        "Value": 300,
        "Qty": 1,
        "Notes": "Membership renewal. Level: PSA Member. Renew to 11/01/2024",
        "OrderDetailType": "MemberLevel",
        "Taxes": null,
        "__IMTINDEX__": 1,
        "__IMTLENGTH__": 1
    }
]

and another

[
    {
        "Value": 150,
        "Qty": 1,
        "Notes": "Membership renewal. Level: PSA Member. Renew to 11/01/2024",
        "OrderDetailType": "MemberLevel",
        "Taxes": null,
        "__IMTINDEX__": 1,
        "__IMTLENGTH__": 1
    }
]

and another

[
    {
        "Value": 200,
        "Qty": 1,
        "Notes": "Another Notes",
        "OrderDetailType": "MemberLevel",
        "Taxes": null,
        "__IMTINDEX__": 1,
        "__IMTLENGTH__": 1
    }
]

The end result is a table that would look like this. The notes generate the header dynamically, and each array in each output bundle is generated in the right columns. Each row is another bundle.

Membership application. Level: PSA Member Extras: One-Time Initiation Fee - $500 Membership renewal. Level: PSA Member. Renew to 11/01/2024 Another Notes
300 500
300
150
200

This sort of transformation is a bit like a pivot table, I guess. Anyone do this?

2

Hi @alex.newpath

Identifying data in Google Sheets dynamically can be a bit complex. However, you can use a switch module to sort cells based on header names, which will help identify the column dynamically.

image
image610×746 42.2 KB

If you require additional assistance, please don’t hesitate to reach out to us.
MSquare Support
Visit us here
Youtube Channel

3

Ok so when I have column names like A and B how would that map into Google sheets app? I can’t really switch on static values either. The incoming bundles may have a new set of column values for each execution.

4

Hi @alex.newpath
You can map the output of switch module. The output will be either A or B depending on the input

1 Like
5

Yes and how do I use this info in a google sheet module?