Can you produce a formatted email in a script and gmail it in an automation?

Yeap you can, but the amount of HTML formatting functionality you have might be limited I think.  I'd suggest manually creating the HTML for an email once to see if it formats correctly before putting in the work to set up the script

Is it possible to just set up a formula field for this instead?  Might be less work

Having the new table store all the emails that've been sent out seems like a good idea too for a birds eye view thing, but since it already exists in your Gmail account how much value this brings is really dependent on your business logic

Thanks. I actually got it working last night.  The email table with it's own workflow works perfectly, and I'll be able to leverage it in other ways.  Basically, I add a row, set status to queued. Then, in my script, I build the body of the email with data and links and update the "body" field of the email and set it's status to "Send".  The workflow sends it and then updates the sent date and status to "Sent". Later I'll add an automation that deletes anything over 2 weeks. It'll be a nice way to automate emails from other workflows by just building a record in the emails table. I realize there's a gmail workflow I can use so there's not a huge amount of time savings, but I like this method, as I can do more impressive updates to the body that has more details on it.

As to the email body updates, I have another table called "templates" that I'll be able to define the email body (with HTML) and have substitute tags like <NAME>, <ADDRESS> etc that in the script I can open the specific template for that workflow, and swap the data into the body and update the email body in question.  What does this give me?  It gives me the ability to change the wording of the email easier on a form than having to change it in code. 🙂

Thanks.

Sounds like you have already found one solution to your question. (It sounds very similar to a system that I have created with my Email Table extension and related automation script that I sell.)

I'll just throw out another idea for your consideration. Your current setup requires two different automations--one to run the script and another to actually send the email. You could have the script output the email text using output.set(), followed by the Send Email action in the same automation as the script.