Convert Google Sheet Rows data into Xero Line items efficiently

[zmk]

I have the below scenario where I create a new Quote in Xero from data load from Google Sheet.

Xero LineItems expect multiple entry (Array) with specific required fields (JSON?) The field name must match what Xero expects. I have managed to make it work using below flow.

However it is using too many operations. For example if there are 30 LineItems. The “Aggregator Order Line Items” will use 30 operations.

I can get the data from Google Sheet already. Problem is it doesn’t create an array mapping field name to actual value. Xero Module fails due to no missing data (aka missing field names).

Is there a better way of doing the above using less operations?

An actual example from my output Bundle from Google Sheet.

Can I go from that to a Array or Array to be able to use in the Xero LineItem like below, without using the Array Aggregator which uses too many operations.

Example of valid LineItem data:

“LineItems”: [
{“Description”: “AVOCADO 8PC”",
“UnitAmount”: 10.24,
“ItemCode”: “7001074”,
},
{“Description”: “CHESE 8PC”,
“UnitAmount”: 10.24,
“ItemCode”: “7001081”,
}
]

I need to be able to select from only the fields I need for LineItem and then some how swap out the column names (that came from Google Sheet) to the name the Xero LineItem expects like above.

Thank you.

[ImMichaelCannon]

Hi @zmk; it sounds like your new friendly Make module should be the Text Aggregator. This way, you can convert the Google Sheets bundles into a JSON structure that you define.

Pretending that our Iterator is creating bundles like Google Sheet, we use Text Aggregator to create the JSON structure that Xero expects one line item at a time, then we convert that to the JSON array.

blueprint (3).json (8.1 KB)

[Donald_Mitchell]

Hello @zmk,
Array Aggregator only uses 1 Op when used immediately after an iterator or any module that outputs multiple bundles.
If you can, you should remove the Tools Set Multiple Variables module between Google Sheets and the Array Aggregator.
Then, in the Array Aggregator, select the Xero Line Items option from the Data Structure dropdown box.
Once you select that Data Structure, it should give you the fields that need to be filled from the Iterator/Google Sheets module.

[zmk]

Thank you @ImMichaelCannon I was thinking of something like that but did not know how to do it.

[zmk]

@Donald_Mitchell

Array Aggregator only uses 1 Op when used immediately after an iterator or any module that outputs multiple bundles.

I didn’t know that. Your solution is exactly what I need!

Thank you.

This is what I ended up with now