azureazure-storageazure-logic-apps

Is it possible to grab a value from a blob (uploaded csv to container) and pass it to a Logic App?

I'm trying to add a dynamic distribution list to an email I send out with a logic app. It's triggered whenever a blob is uploaded into a container it is watching.

Is it possible to:

  1. grab a value from the csv and enter it into the email that is being composed.
  2. Is it possible to exclude this column when it is being attached in the email?

For example if the csv were to be composed like so:

+----+---------+---------+---------+--------------------+
| ID | Column1 | Column2 | Column3 | Distribution List  |
+----+---------+---------+---------+--------------------+
| 1  | 500     | R       | 500     | fakeemail@fake.com |
+----+---------+---------+---------+--------------------+
| 2  | 600     | R       | 600     | fakeemail@fake.com |
+----+---------+---------+---------+--------------------+
| 3  | 700     | DED     | NULL    | fakeemail@fake.com |
+----+---------+---------+---------+--------------------+

I need the value in the distribution list column to go here: enter image description here

The distribution list would only be there for the purpose of building the email, is there a way to remove it before the blob content is attached?

EDIT: Parse_JSON Schema:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "ID": {
                "type": "string"
            },
            "Column1": {
                "type": "string"
            },
            "Column2": {
                "type": "string"
            },
            "Column3": {
                "type": "string"
            },
            "DistributionList": {
                "type": "string"
            }
        },
        "required": [
            "ID",
            "Column1",
            "Column2",
            "Column3",
            "DistributionList"
        ]
    }
}
Solution

  • I have used given workflow to pass the distribution list name in the email and then removed the column before attaching it to the email.

    enter image description here enter image description here

    I have used below JavaScript code to parse the csv data to JSON.

    Parsed the JSON array and then used Select action to fetch the required columns and lastly used Create CSV table action to form a CSV table which will be used as an email attachment.

    const csvData = workflowContext.actions.Get_blob_content_V2.outputs.body; // Blob content as a string


const lines = csvData.trim().split('\n');
const result = [];

if (lines.length === 0) {
    return result; 
}

const headers = lines[0].split(',').map(header => header.trim());

for (let i = 1; i < lines.length; i++) {
    const obj = {};
    const currentLine = lines[i].split(',').map(field => field.trim());

    for (let j = 0; j < headers.length; j++) {
        obj[headers[j]] = currentLine[j];
    }
    result.push(obj);
}

return result;

    Code-

    {
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Create_CSV_table": {
                "inputs": {
                    "format": "CSV",
                    "from": "@body('Select')"
                },
                "runAfter": {
                    "Select": [
                        "Succeeded"
                    ]
                },
                "type": "Table"
            },
            "Execute_JavaScript_Code": {
                "inputs": {
                    "code": "const csvData = workflowContext.actions.Get_blob_content_V2.outputs.body; // Blob content as a string\r\n\r\n\r\nconst lines = csvData.trim().split('\\n');\r\nconst result = [];\r\n\r\nif (lines.length === 0) {\r\n    return result; \r\n}\r\n\r\nconst headers = lines[0].split(',').map(header => header.trim());\r\n\r\nfor (let i = 1; i < lines.length; i++) {\r\n    const obj = {};\r\n    const currentLine = lines[i].split(',').map(field => field.trim());\r\n\r\n    for (let j = 0; j < headers.length; j++) {\r\n        obj[headers[j]] = currentLine[j];\r\n    }\r\n    result.push(obj);\r\n}\r\n\r\nreturn result;\r\n"
                },
                "runAfter": {
                    "Get_blob_content_V2": [
                        "Succeeded"
                    ]
                },
                "type": "JavaScriptCode"
            },
            "For_each": {
                "actions": {
                    "Send_an_email_(V2)": {
                        "inputs": {
                            "body": {
                                "Attachments": [
                                    {
                                        "ContentBytes": "@{base64(body('Create_CSV_table'))}",
                                        "Name": "@triggerBody()?['Name']"
                                    }
                                ],
                                "Body": "<p class=\"editor-paragraph\">Test Email.....</p>",
                                "Importance": "Normal",
                                "Subject": "CSV file",
                                "To": "@items('For_each')['Distribution List']"
                            },
                            "host": {
                                "connection": {
                                    "name": "@parameters('$connections')['outlook']['connectionId']"
                                }
                            },
                            "method": "post",
                            "path": "/v2/Mail"
                        },
                        "type": "ApiConnection"
                    }
                },
                "foreach": "@outputs('Parse_JSON')['body']",
                "runAfter": {
                    "Create_CSV_table": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Get_blob_content_V2": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['azureblob']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/files/@{encodeURIComponent(encodeURIComponent(triggerBody()?['Path']))}/content",
                    "queries": {
                        "inferContentType": true
                    }
                },
                "metadata": {
                    "JTJmdGVzdCUyZlRlc3QxMi5jc3Y=": "/test/Test12.csv"
                },
                "runAfter": {},
                "type": "ApiConnection"
            },
            "Parse_JSON": {
                "inputs": {
                    "content": "@outputs('Execute_JavaScript_Code')?['body']",
                    "schema": {
                        "items": {
                            "properties": {
                                "Column1": {
                                    "type": "string"
                                },
                                "Column2": {
                                    "type": "string"
                                },
                                "Column3": {
                                    "type": "string"
                                },
                                "Distribution List": {
                                    "type": "string"
                                },
                                "ID": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "ID",
                                "Column1",
                                "Column2",
                                "Column3",
                                "Distribution List"
                            ],
                            "type": "object"
                        },
                        "type": "array"
                    }
                },
                "runAfter": {
                    "Execute_JavaScript_Code": [
                        "Succeeded"
                    ]
                },
                "type": "ParseJson"
            },
            "Select": {
                "inputs": {
                    "from": "@outputs('Execute_JavaScript_Code')?['body']",
                    "select": {
                        "Column1": "@item()?['Column1']",
                        "Column2": "@item()?['Column2']",
                        "Column3": "@item()?['Column3']",
                        "ID": "@item()?['ID']"
                    }
                },
                "runAfter": {
                    "Parse_JSON": [
                        "Succeeded"
                    ]
                },
                "type": "Select"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "When_a_blob_is_added_or_modified_(properties_only)_(V2)": {
                "evaluatedRecurrence": {
                    "frequency": "Second",
                    "interval": 10
                },
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['azureblob']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/triggers/batch/onupdatedfile",
                    "queries": {
                        "checkBothCreatedAndModifiedDateTime": false,
                        "folderId": "JTJmdGVzdA==",
                        "maxFileCount": 10
                    }
                },
                "metadata": {
                    "JTJmdGVzdA==": "/test"
                },
                "recurrence": {
                    "frequency": "Second",
                    "interval": 10
                },
                "splitOn": "@triggerBody()",
                "type": "ApiConnection"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "azureblob": {
                    "connectionId": "/subscriptions/0e8****b5e7c/resourceGroups/*****/providers/Microsoft.Web/connections/azureblob",
                    "connectionName": "azureblob",
                    "id": "/subscriptions/0e8*******5e7c/providers/Microsoft.Web/locations/northcentralus/managedApis/azureblob"
                },
                "outlook": {
                    "connectionId": "/subscriptions/0e8****b5e7c/resourceGroups/*****/providers/Microsoft.Web/connections/outlook",
                    "connectionName": "outlook",
                    "id": "/subscriptions/0e89*******8b5e7c/providers/Microsoft.Web/locations/northcentralus/managedApis/outlook"
                }
            }
        }
    }
}

    This gives me expected response.

    enter image description here

    enter image description here

    enter image description here