Hi everyone,
I am pretty new to PowerAutomate and have a use-case where I would need to build to a flow that does the following:
- reads a CSV file from my emails
- saves the CSV file to sharepoint
- converts the CSV into JSON (directly from the attachment, not from the file saved on sharepoint)
- loops through the 'email' column
- checks the @domain.com value and skips certain domains
- merges the remaining email addresses with an email template and sends out an email to each email address in the array
Since this is something beyond my capabilities, I outsourced this to a freelancer who all-in-all did a decent job and managed to get the flow "somewhat" working. Somewhat in the sense that it works fine in a staging environment, but fails in a live production environment. Here the symptoms we spent over a week debugging:
Error:
Sequence:
0. JsonArray output
Note: I am not overly familiar with CSV manipulation in PA, but the addition of double quotes (") before every value seems a little odd to me, but since it works fine with the edited CSV I dont see an issue
1. Extract email address column from JSON Array
Note: its a bit funny to me that this is not `outputs('JsonArray'[5])` but I am guessing PA can address items by the header value?
2. Split email domain for whitelisting/blacklisting
Observations:
- PA says there is no 'Email' column, however I can confirm that the JSON array does contain the 'Email' column and all records have a valid email address on file
- the output of the JSON array looks fine, no invalid characters, no extra commas, no funny things in there that would cause a miscalculation of columns or something like this
- I have a "Comments" column that at times has "/" or "\" characters, hence have opted to replace their original value with "None" during the JSON creation process, still the same error remains
- variable sanitisation is in place for all columns (escaping of ", ', /, \, etc.)
The flow works fine IF:
- I download the CSV file to my computer, open it in Excel, change every value in the "Comments" column to "None" manually, save the CSV file, and then send the update CSV to myself for processing
I am now at the stage where I am thinking that there is a problem with the CSV, although it's kind of unlikely since the JSON output has accurately capture all the columns and all the values. The error makes no sense and we have now spent 10 days on this project with the freelancer ultimately giving up since he couldn't find the root cause.
I have attached the flow and a sample CSV file (edited and saved, hence likely to work), I would love to get some feedback from the community on how I could best debug this, what kind of conditional statements I should add to verify data exists and determine where the flow "really" breaks. I develop mostly in PHP / JS / SQL and am missing var_dump(); a lot 🙂
Appreciate any guidance from the PA gods in here, thank you!

Report
All responses (
Answers (