Extract Ranges and add to new sheet

[SerenityAdmin]

I would like to extract 3 columns from an google sheet and add them into a new sheet in a new workbook.

The two issues I’m having are:

1. The ranges successfully extract each cell on the third column but not on the other two, it only takes the first column and iterates through that
2. The iteration keeps going even after the total number of bundles. it for some reason duplicates the extraction of the third column (I also cant seem to limit it with a filter)

Ive tries, creating JSON, Parsing Json, iterating and aggregating.

I’ve also tried using one aggregator but it wont allow me to select multiple modules as inputs. and when I chain them together it cost me 20,000 operations…

Not sure which method is the best but none seem to work. The current methods I’ve tried requite 2-4 modules to iterate the same amount of times which seems quite costly for such a simple automation.

The extraction from the sheet seems to be good, even the iteration of the numbers(except for the issue of duplicate execution), but it wont do it for the name or amount column.

blueprint (3).json (64.1 KB)

Column 1:

Column 2:

Column 3:

The over execution issue:

First oppression is correct, but second doesn’t properly iterate. Only the Number (Numero) column iterates thought the array not the Name(Destinaire) and Amount (Montant):

The first iterator after the “create JSON”

The Parse JSON into “ROW”:

The last aggregator on the “ROW”:

Sheet output:

Ideally id like
NUMBER ID | NAME | AMOUNT|

Ive been stuck on this for a while, thank you.

[IOA_Brett]

Hello,

I would highly recommend using the Google Sheets “Get Range Values” and “Make an API Call” Modules for this process.

Step 1: Get the data from your source sheet using the “get Range Values” module. This will return n bundles for each record returned.

Step 2: use a “text aggregator” to parse each bundle into your desired data. This will use 1 operation. Separate by a comma, and format the data in a array of strings. E.g [“data1”,“data2”,“data3”]

Step 3: Use the “Make an API Call” Module to append data from step 2 in one operation.

Here are the results using a test sheet I put together:

Source Sheet:

Result Sheet:

Using this method I can extract thousands of records from the target sheet, and append them to the result sheet in 3 operations.

This link will send you to the Google Sheets documentation for more details:

Google Sheets API Overview  |  Google for Developers

Hope that helps!

[SerenityAdmin]

Worked beautifully thank you, Honestly would’ve taken me a while to get there on my own. Very much appreciated for the quick and clear help.