From Google Form to Automatic Newsletter with Google Apps Script

Example data and output

When my collegiate cross country team was suddenly spread out across the country due to the Coronavirus pandemic, we wanted a fun way to keep up with what everyone was up to week-to-week. Gathering responses from a Google Form was easy, but shaping those responses into an aesthetically pleasing newsletter required a lot of copy and pasting, highlighting, clicking, and repeating.

Aware that Google Apps Script existed, I thought, there must be a way to automate this. Turns out there is, and I tinkered around until I made it work. Now my teammate who’s in charge of the newsletter can click a button and have the formatted components of the newsletter in her inbox. Instead of hundreds of clicks to make the newsletter formatted as desired, 2–3 total copy-and-pastes are all that’s needed to insert the automatically formatted newsletter components into our template.

I made this with a very specific use case in mind, so it wasn’t until after the fact that I did a more thorough search to see if anyone had created something similar before. Only one blog post struck me as somewhat similar. However, the author was quick to turn away from the route I took:

“One way is to programmatically create the document using code, meaning we would add the different document elements one by one using a script. While that is possible, I found it very difficult to do, especially when trying to create a document with an [sic] kind of sophistication or styling.”

I am here to tell you that it’s not too hard. I never used Apps Script before this project and, in fact, I don’t know JavaScript if I’m being honest. With just a handful of lines of code, you can go from a Google Form (or Google Sheet) to a beautifully—and automatically—formatted Google Doc.

Creating a UI

Perhaps the biggest advantage of Apps Script is the ability to easily and seamlessly create a UI so that you never have to touch the script editor once the code is complete.

A preview of the UI I created for the newsletter generator. All the user has to do is input the starting row for the current week’s responses and their email address so that the resulting document lands in their inbox.

The Apps Script documentation is impressively good, so be sure to reference that when customizing your UI. With just one line of code using `ui.prompt`, you’ll be well on your way to having a functional UI.

Some code samples: listing out responses & creating bios for each person who submitted a response

Lists of responses

With a very simple for loop (and potentially an extra styling function or two, if desired), you can automatically create a formatted list with the question listed as a header followed by each response as a bullet point. Additionally, each response is followed by the name of the person who submitted it, in brackets.

Bios

Another main feature of my newsletter generator is creating the bio components (as pictured at the beginning of this post). This took a bit more code, but rightfully so, as it includes extra styling, image handling, etc.

Moral of the story

Google Apps Script is very easy to pick up, while offering an amazing amount of room for creativity for customizable, fun projects like this. And a bonus? Now that I’m familiar with Apps Script, I intend to use that knowledge to automate other more menial tasks in the future. One could imagine using Apps Script to create automatic personal finance reports, or even personalized grocery lists— the opportunities are endless.

You can check out my complete code on GitHub. It isn’t designed to be 100% reproducible, but is a good guide for creating a similar project.