I am trying to compare data from two programs (google sheet snad free agent.
I have collected both sets of data
Now I need to compare one list to another and output all the anomolies into one email that asks for manual intervention to check.
I have
but the output is only the first entry or collection.
The filter
I am not sure if it is only comparing the first collection to the first record or comparing them all and I need support on compiling all mismatches and naming them in an email.
{
"name": "Integration FreeAgent, Google Sheets",
"flow": [
{
"id": 1,
"module": "freeagent:ListContacts",
"version": 1,
"parameters": {
"__IMTCONN__": 520270
},
"mapper": {
"sort": "name",
"view": "active",
"limit": "1000"
},
"metadata": {
"designer": {
"x": 0,
"y": 0
},
"restore": {
"expect": {
"sort": {
"mode": "chose",
"label": "Name"
},
"view": {
"mode": "chose",
"label": "Active Contacts"
}
},
"parameters": {
"__IMTCONN__": {
"data": {
"scoped": "true",
"connection": "freeagent"
},
"label": "My FreeAgent connection 1 (XXXXXXXXXXXXXxx)"
}
}
},
"parameters": [
{
"name": "__IMTCONN__",
"type": "account:freeagent",
"label": "Connection",
"required": true
}
],
"expect": [
{
"name": "limit",
"type": "uinteger",
"label": "Limit"
},
{
"name": "view",
"type": "select",
"label": "View",
"validate": {
"enum": [
"all",
"active",
"clients",
"suppliers",
"active_projects",
"completed_projects",
"open_clients",
"open_suppliers",
"hidden"
]
}
},
{
"name": "sort",
"type": "select",
"label": "Sort",
"validate": {
"enum": [
"name",
"created_at",
"updated_at"
]
}
}
]
}
},
{
"id": 2,
"module": "builtin:BasicAggregator",
"version": 1,
"parameters": {
"feeder": 1
},
"mapper": {
"first_name": "{{1.first_name}}",
"last_name": "{{1.last_name}}"
},
"metadata": {
"designer": {
"x": 300,
"y": 0
},
"restore": {
"extra": {
"feeder": {
"label": "FreeAgent - List Contacts [1]"
},
"target": {
"label": "Custom"
}
}
},
"expect": [
{
"name": "__IMTTARGET__",
"type": "collection",
"label": "Array",
"spec": []
}
]
}
},
{
"id": 3,
"module": "google-sheets:filterRowsAdvanced",
"version": 2,
"parameters": {
"__IMTCONN__": 12956187
},
"mapper": {
"select": "list",
"tq": "where E = \"{{formatDate(now; \"MMMM\")}}\"",
"from": "drive",
"spreadsheetId": "/1520T1tiKkQSJLZNDmOytLz5xTsEbt9zBa2xGsJZUwX4",
"sheetId": 655284133
},
"metadata": {
"designer": {
"x": 600,
"y": 0
},
"restore": {
"parameters": {
"__IMTCONN__": {
"label": "XXXXXXXXXXXXXXXXXXXXXXXX",
"data": {
"scoped": "true",
"connection": "google"
}
}
},
"expect": {
"select": {
"label": "Search by path"
},
"from": {
"label": "My Drive"
},
"spreadsheetId": {
"path": [
"Untitled form (Responses)"
]
},
"sheetId": {
"label": "Form responses 1"
}
}
},
"parameters": [
{
"name": "__IMTCONN__",
"type": "account:google",
"label": "Connection",
"required": true
}
],
"expect": [
{
"name": "select",
"type": "select",
"label": "Search Method",
"required": true,
"validate": {
"enum": [
"list",
"fromAll",
"map"
]
}
},
{
"name": "tq",
"type": "text",
"label": "Query",
"required": true
},
{
"name": "limit",
"type": "uinteger",
"label": "Limit"
},
{
"name": "from",
"type": "select",
"label": "Drive",
"required": true,
"validate": {
"enum": [
"drive",
"share",
"team"
]
}
},
{
"name": "spreadsheetId",
"type": "file",
"label": "Spreadsheet ID",
"required": true
},
{
"name": "sheetId",
"type": "select",
"label": "Sheet ID",
"required": true
}
],
"interface": [
{
"name": "__IMTLENGTH__",
"label": "Total number of bundles",
"type": "uinteger"
},
{
"name": "__IMTINDEX__",
"label": "Bundle order position",
"type": "uinteger"
},
{
"name": "0",
"label": "Timestamp (A)",
"type": "text"
},
{
"name": "1",
"label": "Parent Name (FIRST AND LAST) (this must be exactly as entered into your registration form) (B)",
"type": "text"
},
{
"name": "2",
"label": "Child Name (how you want to appear on the invoice) (C)",
"type": "text"
},
{
"name": "3",
"label": "Date (D)",
"type": "text"
},
{
"name": "4",
"label": "Month (E)",
"type": "text"
},
{
"name": "5",
"label": "Sessions (F)",
"type": "number"
},
{
"name": "6",
"label": "Child name 2 (G)",
"type": "text"
},
{
"name": "7",
"label": "Date Child 2 (H)",
"type": "text"
},
{
"name": "8",
"label": "Month Child 2 (I)",
"type": "text"
},
{
"name": "9",
"label": "Sess Child 2 (J)",
"type": "number"
}
]
}
},
{
"id": 4,
"module": "builtin:BasicAggregator",
"version": 1,
"parameters": {
"feeder": 3
},
"mapper": {
"1": "{{3.`1`}}"
},
"metadata": {
"designer": {
"x": 900,
"y": 0
},
"restore": {
"expect": {
"__IMTTARGET__": {
"collapsed": true
}
},
"extra": {
"feeder": {
"label": "Google Sheets - Search Rows (Advanced) [3]"
},
"target": {
"label": "Custom"
}
}
},
"expect": [
{
"name": "__IMTTARGET__",
"type": "collection",
"label": "Array",
"spec": []
}
],
"advanced": true
}
},
{
"id": 6,
"module": "builtin:BasicFeeder",
"version": 1,
"parameters": {},
"filter": {
"name": "",
"conditions": [
[
{
"a": "{{4.array[].`1`}}",
"o": "text:notequal",
"b": "{{2.array[].first_name}} {{2.array[].last_name}}"
}
]
]
},
"mapper": {
"array": "{{4.array[].`1`}}"
},
"metadata": {
"designer": {
"x": 1178,
"y": -62
},
"restore": {
"expect": {
"array": {
"mode": "edit"
}
}
},
"expect": [
{
"name": "array",
"type": "array",
"label": "Array",
"mode": "edit",
"spec": []
}
]
}
},
{
"id": 7,
"module": "microsoft-email:createAndSendAMessage",
"version": 2,
"parameters": {
"__IMTCONN__": 11889084
},
"mapper": {
"subject": "BOOKING SOFTWARE: Name Mismatch",
"contentType": "text",
"content": "The following\n\n {{6.value}}\n\nis a name mismatch against the Invoicing Software\n\nPlease either: \n\n1) Change the name in Freeagent to the name in the above name\n\nor\n\n2) Change the booking spreadsheet to the name recorded in Freeagent.",
"importance": "High",
"toRecipients": [
{
"address": "XXXXXXXXXXXXXXXXXXXXXx",
"name": "G"
}
],
"from": [
{
"address": "XXXXXXXXXXXXXXXXXXXXX",
"name": "PAYMENTS"
}
],
"singleValueExtendedProperties": {}
},
"metadata": {
"designer": {
"x": 1357,
"y": 147
},
"restore": {
"parameters": {
"__IMTCONN__": {
"label": "My Microsoft connection (XXXXXXXXXXXXXXXXXXXXXXXX)>)",
"data": {
"scoped": "true",
"connection": "azure"
}
}
},
"expect": {
"contentType": {
"mode": "chose",
"label": "Text"
},
"importance": {
"mode": "chose",
"label": "High"
},
"toRecipients": {
"mode": "chose",
"items": [
null
]
},
"from": {
"mode": "chose",
"items": [
null
]
},
"ccRecipients": {
"mode": "chose"
},
"bccRecipients": {
"mode": "chose"
},
"replyTo": {
"mode": "chose"
},
"attachments": {
"mode": "chose"
},
"internetMessageHeaders": {
"mode": "chose"
}
}
},
"parameters": [
{
"name": "__IMTCONN__",
"type": "account:azure",
"label": "Connection",
"required": true
}
],
"expect": [
{
"name": "subject",
"type": "text",
"label": "Subject",
"required": true
},
{
"name": "contentType",
"type": "select",
"label": "Body Content Type",
"required": true,
"validate": {
"enum": [
"html",
"text"
]
}
},
{
"name": "content",
"type": "text",
"label": "Body Content",
"required": true
},
{
"name": "importance",
"type": "select",
"label": "Importance",
"validate": {
"enum": [
"Low",
"Normal",
"High"
]
}
},
{
"name": "toRecipients",
"type": "array",
"label": "To Recipients",
"required": true,
"spec": [
{
"name": "address",
"type": "email",
"label": "Email Address",
"required": true
},
{
"name": "name",
"type": "text",
"label": "Name"
}
]
},
{
"name": "from",
"type": "array",
"label": "From",
"validate": {
"maxItems": 1
},
"spec": [
{
"name": "address",
"type": "email",
"label": "Email Address",
"required": true
},
{
"name": "name",
"type": "text",
"label": "Name"
}
]
},
{
"name": "ccRecipients",
"type": "array",
"label": "CC Recipients",
"spec": [
{
"name": "address",
"type": "email",
"label": "Email Address",
"required": true
},
{
"name": "name",
"type": "text",
"label": "Name"
}
]
},
{
"name": "bccRecipients",
"type": "array",
"label": "Bcc Recipients",
"spec": [
{
"name": "address",
"type": "email",
"label": "Email Address",
"required": true
},
{
"name": "name",
"type": "text",
"label": "Name"
}
]
},
{
"name": "replyTo",
"type": "array",
"label": "Reply To",
"spec": [
{
"name": "address",
"type": "email",
"label": "Email Address",
"required": true
},
{
"name": "name",
"type": "text",
"label": "Name"
}
]
},
{
"name": "attachments",
"type": "array",
"label": "Attachments",
"spec": [
{
"name": "filename",
"type": "filename",
"label": "File Name",
"required": true,
"semantic": "file:name"
},
{
"name": "data",
"type": "buffer",
"label": "Data",
"required": true,
"semantic": "file:data"
}
]
},
{
"name": "internetMessageHeaders",
"type": "array",
"label": "Internet Message Headers",
"spec": [
{
"name": "name",
"type": "text",
"label": "Name",
"required": true
},
{
"name": "value",
"type": "text",
"label": "Value",
"required": true
}
]
},
{
"name": "singleValueExtendedProperties",
"type": "collection",
"label": "Extended MAPI Properties",
"spec": [
{
"name": "sensitivityLabel",
"type": "text",
"label": "Sensitivity Label"
}
]
}
],
"advanced": true
}
}
],
"metadata": {
"instant": false,
"version": 1,
"scenario": {
"roundtrips": 1,
"maxErrors": 3,
"autoCommit": true,
"autoCommitTriggerLast": true,
"sequential": false,
"slots": null,
"confidential": false,
"dataloss": false,
"dlq": false,
"freshVariables": false
},
"designer": {
"orphans": []
},
"zone": "eu2.make.com",
"notes": []
}
}
