Array output to multiple variables- one per collection

How To
1

Hi. My array output has 2 collections:

image
image432×376 52.1 KB

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.

image
image1190×482 71.6 KB

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
2

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
3

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
4

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.

image
image2222×1016 230 KB

(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:

image
image820×864 57.1 KB

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

1 Like
5

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
6

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)*

image
image676×946 63.7 KB

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

image
image1352×1120 137 KB

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

image
image814×1074 49.4 KB

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

image
image1544×920 116 KB

Thank you! @Bhuvanesh_Patil

1 Like
7

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

1 Like
8

Hey there,

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

9

@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

10

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!