Array output to multiple variables- one per collection

Hi. My array output has 2 collections:

What I’d like to do is - for every collection, I’d like use its values and concatenate them into a string variable - one per collection. However, when I add a Set Multiple Variables module to the scenario, I no longer have the ability to parse between bundles.

When I tested the flow once, it appears that only the first bundle’s values were populated into the string.

In this case, how can I create 2 variables? I had aggregated the prior bundles into single arrays to begin with because I need to use both variables in the final module.

Thank you!

1 Like

Exported blueprint with some info blinded (PII): {
“name”: “TEST Google Sheets”,
“flow”: [
{
“id”: null,
“module”: “placeholder:Placeholder”,
“metadata”: {
“designer”: {
“x”: -191,
“y”: -38
}
}
},
{
“id”: 4,
“module”: “google-sheets:filterRows”,
“version”: 2,
“parameters”: {
IMTCONN”: 8862
},
“mapper”: {
“limit”: “49”,
“filter”: [
[
{
“a”: “K”,
“b”: “{{emptystring}}”,
“o”: “text:equal”
},
{
“a”: “A”,
“b”: “”,
“o”: “exist”
}
]
],
“sheetId”: “Registrations”,
“sortOrder”: “asc”,
“spreadsheetId”: “",
“tableFirstRow”: “A1:CZ1”,
“includesHeaders”: true,
“valueRenderOption”: “UNFORMATTED_VALUE”,
“dateTimeRenderOption”: “FORMATTED_STRING”
},
“metadata”: {
“designer”: {
“x”: -12,
“y”: -89
},
“restore”: {
“orderBy”: {
“mode”: “chose”,
“label”: “”
},
“sheetId”: {
“mode”: “chose”,
“label”: “Registrations”
},
“sortOrder”: {
“mode”: “chose”,
“label”: “Ascending”
},
IMTCONN”: {
“label”: "G
"
},
“spreadsheetId”: {
“mode”: “chose”,
“label”: "W
*****”
},
“tableFirstRow”: {
“label”: “A-CZ”
},
“includesHeaders”: {
“mode”: “chose”,
“label”: “Yes”
},
“valueRenderOption”: {
“mode”: “chose”,
“label”: “Unformatted value”
},
“dateTimeRenderOption”: {
“mode”: “chose”,
“label”: “Formatted string”
}
},
“parameters”: [
{
“name”: “IMTCONN”,
“type”: “account”,
“label”: “Connection”,
“required”: true
}
],
“expect”: [
{
“name”: “spreadsheetId”,
“type”: “select”,
“label”: “Spreadsheet”,
“required”: true
},
{
“name”: “valueRenderOption”,
“type”: “select”,
“label”: “Value render option”,
“validate”: {
“enum”: [
“FORMATTED_VALUE”,
“UNFORMATTED_VALUE”,
“FORMULA”
]
}
},
{
“name”: “dateTimeRenderOption”,
“type”: “select”,
“label”: “Date and time render option”,
“validate”: {
“enum”: [
“SERIAL_NUMBER”,
“FORMATTED_STRING”
]
}
},
{
“name”: “limit”,
“type”: “number”,
“label”: “Maximum number of returned rows”
},
{
“name”: “sheetId”,
“type”: “select”,
“label”: “Sheet”,
“required”: true
},
{
“name”: “includesHeaders”,
“type”: “select”,
“label”: “Table contains headers”,
“required”: true,
“validate”: {
“enum”: [
true,
false
]
}
},
{
“name”: “tableFirstRow”,
“type”: “select”,
“label”: “Column range”,
“required”: true,
“validate”: {
“enum”: [
“A1:Z1”,
“A1:BZ1”,
“A1:CZ1”,
“A1:DZ1”,
“A1:MZ1”,
“A1:ZZ1”
]
}
},
{
“name”: “filter”,
“type”: “filter”,
“label”: “Filter”,
“options”: {
“store”: “rpc://google-sheets/2/rpcGetFilterKeys?includesHeaders=true”
}
},
{
“name”: “sortOrder”,
“type”: “select”,
“label”: “Sort order”,
“validate”: {
“enum”: [
“asc”,
“desc”
]
}
},
{
“name”: “orderBy”,
“type”: “select”,
“label”: “Order by”
}
],
“interface”: [
{
“name”: “IMTLENGTH”,
“type”: “uinteger”,
“label”: “Total number of bundles”
},
{
“name”: “IMTINDEX”,
“type”: “uinteger”,
“label”: “Bundle order position”
},
{
“name”: “ROW_NUMBER”,
“type”: “number”,
“label”: “Row number”
},
{
“name”: “SPREADSHEET_ID”,
“type”: “text”,
“label”: “Spreadsheet ID”
},
{
“name”: “SHEET”,
“type”: “text”,
“label”: “Sheet”
},
{
“name”: “0”,
“type”: “text”,
“label”: “Timestamp (A)”
},
{
“name”: “1”,
“type”: “text”,
“label”: “Name (B)”
},
{
“name”: “2”,
“type”: “text”,
“label”: “Phone (C)”
},
{
“name”: “3”,
“type”: “text”,
“label”: “Date of Birth (D)”
},
{
“name”: “4”,
“type”: “text”,
“label”: “E-mail (E)”
},
{
“name”: “5”,
“type”: “text”,
“label”: “Note (F)”
},
{
“name”: “6”,
“type”: “text”,
“label”: “Photo consent (G)”
},
{
“name”: “7”,
“type”: “text”,
“label”: “Location (H)”
},
{
“name”: “8”,
“type”: “text”,
“label”: “Event Date (I)”
},
{
“name”: “9”,
“type”: “text”,
“label”: “Enter your full name (J)”
},
{
“name”: “10”,
“type”: “text”,
“label”: “Notified (K)”
}
],
“advanced”: true
}
},
{
“id”: 5,
“module”: “google-sheets:filterRows”,
“version”: 2,
“parameters”: {
IMTCONN”: 8862
},
“mapper”: {
“spreadsheetId”: “1njC0FdpWoLzHtg8sI4HCq0WmM2mk9mgT7ureaQzzsr8”,
“valueRenderOption”: “UNFORMATTED_VALUE”,
“dateTimeRenderOption”: “FORMATTED_STRING”,
“limit”: “49”,
“sheetId”: “FacilitatorDates”,
“includesHeaders”: true,
“tableFirstRow”: “A1:CZ1”,
“filter”: [
[
{
“a”: “C”,
“o”: “text:equal”,
“b”: “{{4.7}}”
},
{
“a”: “D”,
“o”: “date:equal”,
“b”: “{{4.8}}”
}
]
],
“sortOrder”: “asc”
},
“metadata”: {
“designer”: {
“x”: 225,
“y”: -107
},
“restore”: {
IMTCONN”: {
“label”: "Google Sheets 2024 "
},
“spreadsheetId”: {
“mode”: “chose”,
“label”: “WOG Second Sunday Registration”
},
“valueRenderOption”: {
“mode”: “chose”,
“label”: “Unformatted value”
},
“dateTimeRenderOption”: {
“mode”: “chose”,
“label”: “Formatted string”
},
“sheetId”: {
“mode”: “chose”,
“label”: “FacilitatorDates”
},
“includesHeaders”: {
“mode”: “chose”,
“label”: “Yes”
},
“tableFirstRow”: {
“label”: “A-CZ”
},
“sortOrder”: {
“mode”: “chose”,
“label”: “Ascending”
},
“orderBy”: {
“mode”: “chose”,
“label”: “”
}
},
“parameters”: [
{
“name”: “IMTCONN”,
“type”: “account”,
“label”: “Connection”,
“required”: true
}
],
“expect”: [
{
“name”: “spreadsheetId”,
“type”: “select”,
“label”: “Spreadsheet”,
“required”: true
},
{
“name”: “valueRenderOption”,
“type”: “select”,
“label”: “Value render option”,
“validate”: {
“enum”: [
“FORMATTED_VALUE”,
“UNFORMATTED_VALUE”,
“FORMULA”
]
}
},
{
“name”: “dateTimeRenderOption”,
“type”: “select”,
“label”: “Date and time render option”,
“validate”: {
“enum”: [
“SERIAL_NUMBER”,
“FORMATTED_STRING”
]
}
},
{
“name”: “limit”,
“type”: “number”,
“label”: “Maximum number of returned rows”
},
{
“name”: “sheetId”,
“type”: “select”,
“label”: “Sheet”,
“required”: true
},
{
“name”: “includesHeaders”,
“type”: “select”,
“label”: “Table contains headers”,
“required”: true,
“validate”: {
“enum”: [
true,
false
]
}
},
{
“name”: “tableFirstRow”,
“type”: “select”,
“label”: “Column range”,
“required”: true,
“validate”: {
“enum”: [
“A1:Z1”,
“A1:BZ1”,
“A1:CZ1”,
“A1:DZ1”,
“A1:MZ1”,
“A1:ZZ1”
]
}
},
{
“name”: “filter”,
“type”: “filter”,
“label”: “Filter”,
“options”: {
“store”: “rpc://google-sheets/2/rpcGetFilterKeys?includesHeaders=true”
}
},
{
“name”: “sortOrder”,
“type”: “select”,
“label”: “Sort order”,
“validate”: {
“enum”: [
“asc”,
“desc”
]
}
},
{
“name”: “orderBy”,
“type”: “select”,
“label”: “Order by”
}
],
“interface”: [
{
“name”: “IMTLENGTH”,
“label”: “Total number of bundles”,
“type”: “uinteger”
},
{
“name”: “IMTINDEX”,
“label”: “Bundle order position”,
“type”: “uinteger”
},
{
“name”: “ROW_NUMBER”,
“label”: “Row number”,
“type”: “number”
},
{
“name”: “SPREADSHEET_ID”,
“label”: “Spreadsheet ID”,
“type”: “text”
},
{
“name”: “SHEET”,
“label”: “Sheet”,
“type”: “text”
},
{
“name”: “0”,
“label”: “facLocDate (A)”,
“type”: “text”
},
{
“name”: “1”,
“label”: “Facilitator (B)”,
“type”: “text”
},
{
“name”: “2”,
“label”: “Location (C)”,
“type”: “text”
},
{
“name”: “3”,
“label”: “Event Date (D)”,
“type”: “text”
},
{
“name”: “4”,
“label”: “autofillE-mail (E)”,
“type”: “text”
},
{
“name”: “5”,
“label”: “autofillPhone (F)”,
“type”: “text”
},
{
“name”: “6”,
“label”: “(G)”,
“type”: “text”
},
{
“name”: “7”,
“label”: “(H)”,
“type”: “text”
},
],
“advanced”: true
}
},
{
“id”: 6,
“module”: “builtin:BasicAggregator”,
“version”: 1,
“parameters”: {
“target”: “10.variables”,
“feeder”: 5
},
“mapper”: {
“name”: “String1”,
“value”: “{{5.1}}at {{5.2}} on {{5.3}}. {{5.1}}can be reached at {{5.4}}/{{5.5}}.”
},
“metadata”: {
“designer”: {
“x”: 439,
“y”: -131
},
“restore”: {
“feeder”: {
“label”: “Google Sheets - Search Rows [5]”
},
“target”: {
“label”: “Variables”
}
},
“expect”: [
{
“name”: “name”,
“label”: “Variable name”,
“type”: “text”,
“required”: true
},
{
“name”: “value”,
“label”: “Variable value”,
“type”: “any”
}
],
“advanced”: true
},
“onerror”: [
{
“id”: 7,
“module”: “builtin:Ignore”,
“version”: 1,
“metadata”: {
“designer”: {
“x”: 659,
“y”: 13
}
}
}
]
},
{
“id”: 10,
“module”: “util:SetVariables”,
“version”: 1,
“parameters”: {},
“mapper”: {
“variables”: [
{
“name”: “CONCATSTRING1”,
“value”: “…”
},
{
“name”: “CONCATSTRING2”,
“value”: “{{6.array.1}} at {{6.array.2}} on {{6.array.3}}. {{6.array.1}} can be reached at {{6.array.4}}/{{6.array.5}}.”
}
],
“scope”: “roundtrip”
},
“metadata”: {
“designer”: {
“x”: 686,
“y”: -152
},
“restore”: {
“variables”: {
“items”: [
“undefined”,
“undefined”
]
},
“scope”: {
“label”: “One cycle”
}
},
“expect”: [
{
“name”: “variables”,
“label”: “Variables”,
“type”: “array”,
“spec”: [
{
“name”: “name”,
“label”: “Variable name”,
“type”: “text”,
“required”: true
},
{
“name”: “value”,
“label”: “Variable value”,
“type”: “any”
}
]
},
{
“name”: “scope”,
“label”: “Variable lifetime”,
“type”: “select”,
“required”: true,
“validate”: {
“enum”: [
“roundtrip”,
“execution”
]
}
}
],
“interface”: [
{
“name”: “CONCATSTRING1”,
“label”: “CONCATSTRING1”,
“type”: “any”
},
{
“name”: “CONCATSTRING2”,
“label”: “CONCATSTRING2”,
“type”: “any”
}
]
}
}
],
“metadata”: {
“instant”: false,
“version”: 1,
“scenario”: {
“roundtrips”: 1,
“maxErrors”: 3,
“autoCommit”: true,
“autoCommitTriggerLast”: true,
“sequential”: false,
“confidential”: false,
“dataloss”: false,
“dlq”: false
},
“designer”: {
“orphans”:
},
“zone”: “xxxx.com
}
}

1 Like

Hey @flowcooker Here is what you can do -

  • Use iterator after aggregator or directly from google sheet
  • Then use set variable where you will build final string
  • and then use another aggregator to bring all strings in single array

Make sure you set the iterator as source module in aggregator so you don’t have multiple operations. Let me know if this solves your problem. Would love to help if needed.

Thank you

1 Like

Thank you. I’m sorry, I don’t think I understood your recommendation. Here’s how I interpreted your instructions, and after I added an iterator after the aggregator , I ended up with 2 bundles again, which looked a lot like the output of the Google sheet module before the Array Aggregator.

(The requirements changed slightly) and what I’d really like is to somehow get to an output of 6 variables like this in a single bundle:

Could you please help me a little further? I really appreciate your help here.

1 Like

I believe you are doing same operation in Set Variable as mentioned in your original message -
{} at {} on ....

Now you need to aggregate the set variable operations so you can have an array of strings.

Here is my understanding -

  • You got some bundle, in bundle you have an array of two collection.
  • Now you iterate over the array in bundle, as there are two values in array, there will be two operations (originated from iterator)
  • Now for each value you will use set variable to convert the collection into string ({} at {} on ....)
  • Now you want both strings in an array, you will use aggregator for that. And source module will be iterator

Let me know if you understand solution.

1 Like

Hi. Thanks again.
So I am trying to follow along step by step and please tell me where I got it wrong.

** Now you iterate over the array in bundle, as there are two values in array, there will be two operations (originated from iterator)*

** Now for each value you will use set variable to convert the collection into string ({} at {} on ....)*

** Now you want both strings in an array, you will use aggregator for that. And source module will be iterator*

Selecting the Iterator as the source, I ended up with empty arrays. Was I supposed to select anything specific under Aggregated Fields?

Thank you! @Bhuvanesh_Patil

1 Like

@flowcooker You will have to select result of set multiple variables operation in aggregated fieldd

1 Like

Hey there,

you need the set variables module before the aggregator, then aggregate the resulting text. So Search the sheet → Set Variable → Aggregator

@flowcooker Let me know if this is solved and if yes you can mark it as a solution so people with same issue can get help

This solution worked to create two strings when I had no iterator module in my scenario. My structure was only Search > Set Multiple Variables > Array Aggregator.

If I added an Iterator module and selected the iterator as source in the Aggregator module, I did not have the option to select output of Set Multi Vars module as the Aggregated field(s).

Thank you for your help!