Hello community,
I am trying to build an automation to sync a large dataset (approx. 2,000 rows) from Google Sheets to an external API, and then update the Sheet back with new data. To avoid rate limits and optimize performance, I am processing the rows in batches of 50.
The Workflow:
- Get All Rows: Retrieve ~2,000 rows from Google Sheets.
- Code Piece (Chunker): I use the following code to split the array into chunks of 50:
export const code = async (inputs) => {
const allRows = inputs.filas.filter(row => row.row > 1 && row.values.id);
const size = 50;
const chunks = [];
for (let i = 0; i < allRows.length; i += size) {
chunks.push(allRows.slice(i, i + size));
}
return chunks;
};
The results of that code:
The Loop Structure:
- Big Loop: Iterates through the “chunks” (the blocks of 50).
- Nested Small Loop: Iterates through the 50 individual rows inside each chunk.
- Inside this nested loop, I perform two API calls to fetch external data.
- Then, I have a “Standardizer” Code Step that formats the data into the exact structure needed for Google Sheets:
{ "rowId": X, "values": { ... } }.
The Problem: I want to use the “Update Multiple Rows” action to update the 50 rows at once after the nested loop finishes. Therefore, I placed the “Update Multiple Rows” step outside the nested loop (but inside the Big Loop).
However, I am unable to select or map the results from the “Standardizer” code step that runs inside the nested loop. Since the action is outside the nested loop context, the data picker doesn’t allow me to aggregate those 50 individual outputs into a single array for the batch update.
My specific questions for the community:
- Placement of the Standardizer Code: Should my “Standardizer” code step be located inside or outside the nested loop? My logic says it must be inside to process each row’s unique data, but then I cannot access its results from the “Update Multiple Rows” step outside. If I put it outside, it loses access to the individual row data. What is the correct architectural approach here?
- Mapping to “Update Multiple Rows”: How can I fulfill my goal of updating blocks of 50 rows at once? Even if the nested loop finishes, the “Update Multiple Rows” action doesn’t let me select the array of results from the inner loop iterations in the data picker.
The Goal: I want to avoid updating rows one-by-one (which is slow and hits quotas). I want the nested loop to gather the data for 50 rows and then have the “Update Multiple Rows” step push them all in a single call.
How do I aggregate the inner loop’s output into a format that the “Update Multiple Rows” piece can accept?
@kishanprmr @ashrafsam @abuaboud
Thank you!
If needed: Activepieces