Creating a formatted/merged email from a grid

jmaxbr

I have a complicated workflow that I'd like to significantly reduce, in terms of time spent. Working from a standard grid, I need to format the email, pulling some summary information for a project and then adding specifics for each individual tied to each project.

A few points:

1. the summary information is currently in a parent row
2. the specifics for each person (first name, last name, start date, room assignment etc) are the child rows
3. the specifics have been previously copied and pasted into the template email as a table from a separate spreadsheet, which is visually impactful
4. this process is going to repeat 50-60 times a year.

I have started to look at Power Automate, but I'm not sure I can absorb it in a short period of time. Inserting the table from point 3 above is a challenge, although I am sure there must be a way to access it as a report or some other export?

If it were not for the specific information outside of the project summary info - which is simply name, date range, a summary of the project, etc - I could run it as an automation once the info is compiled and a box is checked. However, it is the insertion of the table that is holding me up.

Any advice is welcomed!

Paul Newcome

In that case, you would need to use the API or some other similar third party app. There is no way to do this cleanly wihtin the core app.

TECHNICALLY, you could use some formulas to replicate a grid in a single cell, but it may not come out very cleanly.

You would insert a helper text/number column with a formula that formats each row across the various columns how you would want them. There are a ton of different ways to do this, but the most straightforward is to just create your string.

="Column 1: " + [Column 1]@row + " / Date is: " + [Date Column]@row

Once you get that on every row, you can use a JOIN/COLLECT to pull in the non-blank rows into a single cell.

=JOIN(COLLECT([Helper Column]:[Helper Column], [Helper Column]:[Helper Column], @cell <> ""), CHAR(10))

That CHAR(10) there at the end is a line break, so it puts each "row" on its own line within the cell which you would then use as a {{Placeholder}} in your automation.