Trying to create a fancy email with status reports from multiple teams

I'm trying to go a bit above and beyond, using SmartSheets to track the status of multiple projects across multiple teams. Currently, we have links to multiple sheets (one for each team) and they manually go in, add a new column per week, and fill it in with a bunch of text explaining the status of that project this week.

What I'd like to do is:

1) Build a sheet/report that consolidates all of these different sheets on a weekly basis. For example, I would add a 9/14/15 column (after the 9/7/15 column) on each sheet, the teams fill this in, and then on this master sheet that runs in the evening, it would only have the latest column of information.

2) Build a nice-looking email that is sent out to multiple people, using the data in this master sheet. Something that looks like a newsletter, where I can customize how the cell contents are displayed, and the email is built by going line by line. Ideally, I could even include hierarchy here.

I've been experimenting with workarounds; I see that I can export to google sheets, for example. I was hoping to build out an email format in google using their scripting system, then automate an export to that document and have it process a job, but it looks like I need to manually export each time, *and* it creates a new google sheet each time.

I've tried creating both Reports and Parent Sheets, but each one has a few restrictions that prevent this from happening. For example, Reports have no hierarchy, but will import any number of rows based on criteria provided. A parent sheet has custom hierarchy, but will ONLY pull data cell by cell, which means if any team adds a new project, I'd need to manually add it in and re-do the layout of the parent sheet each time.

The closest I've gotten so far:

* Create a report that brings in data from each sheet

* Create a google sheet script that goes line by line and builds a nice-looking email (or uses one from a google doc)

* Each week, manually update the column to be pulled into the report

* Manually export the report to google sheets

* Make sure the new sheet is connected to the google script (Haven't done this yet, so not sure how easy this is)

* Run the script in google to send out the email

Things that would be REALLY helpful:

* The ability to do all of this within smartsheets

* Create a link between google sheets and smartsheets to update a google sheet rather than create a new one

* Automate/schedule the update between the two

* Select a column INDEX rather than just by name (That way, every time I add a new column, I always want to grab column F, since column F will now be the new date, and the old one will now be column G)

Am I missing anything? Has anyone done something snazzy to this effect?