Insert multiple rows in Google Spreadsheets from HTTP request that give xml of products

I like very much this tool but I don’t know how to resolve some issue that I have.
I Make an HTTP request that return me a data containing xml with products.
Then I parse it to XML parser.
Then I add Google Spreadsheet - Add a row.

The result I have is only a new row added in the specific spreadsheet with the first product of the HTTP.

What I want is to insert all the rows in automatically from the HTTP intro the spreadsheet.

Thank you very much!

Note: The Batch Update Rows like in

not exist…

I will divide my answer into 2:

  1. Your data is probably in an array, that means you will need to use the array iterator in order to generate a bundle per item (product in you case).
    Lets say you have this XML:

This shows you have multiple products via the Products array, if you take this XML and parse it using the XML parser in make it will look like this:

Now we need to map the array into an iterator like so:

Once you run this scenario, it will return a bundle for each item, or product like so:

At this point if your next step is creating a new row in Google Sheets you can just add that module and it will write a new row for each item.

  1. If you wish to write this data in bulk, that is also possible. You will need to use the Google Sheets “Make an API call” module.
    So if in the example above we would result in a scenario that looks like this:

It will end up looking like this:

What we want to do is get all the individual items, aggregate them and send them all in one go (and one task). The text aggregator configuration looks like this:

Notice I mapped each individual field according to the order I want it to be mapped in the Google Sheet. Product ID will be mapped into column A, SKU will be mapped into Column B and so on…

the template is as follows:

[“{{mapped_variable1}}”,“{{mapped_variable2}}”,“{{mapped_variable3}}”,“{{mapped_variable4}}”]

Which results in something like this:

We then need to map this into the body of the “Make an API Call” module.
The configuration should look like so:

URL: you need to copy the sheet id, heres a URL template for you - /spreadsheets/sheetid/values/a:z:append
Please notice, if you have more columns than z then just change the z value to whatever column your sheet ends at.

Headers: Add Content-type as a key and application/json as a value.

Query String: set valueInputOption as the key and RAW as value (please notice the uppercase letters).

Body:
image

You will need to send the output of your aggregator inside a values key like so:

{
“values”: [{{your_aggregated_data}}]
}

This must be way too much for that question but I just thought it would be a good thing to explain :).
Let me know if something isnt clear enough.

1 Like