So you’ve organized a hackathon, and now want to email your participants to make sure everyone is taken care of and likely to submit an entry. But, your marketing automation email software is not very practical, as you’d like to adjust the message based on the variety of folks who attended: friends, coworkers, prospects, different kinds of developers from different kinds of companies.  

In this post, we'll walk you through building an email manager that accomplishes sending a bunch of easily customizable emails—fast. It’s a simple front-end app on top of a Google Sheet that contains hackathon participant data. The app will take your contact list out of Google Sheets, and allow you to quickly send out emails to different people on that list.

If you prefer to follow along visually, check out the video here.

Technical overview

Let's start with a really high-level view of this project. For the purpose of this post, we’ve created a Google Sheet with mock data resembling your typical hackathon attendee data: names, emails, segments (whether they’ve been emailed or not), etc. For this, I used Retool’s mock data generator.  

From there, we will create a resource which allows us to pull in the Google sheet data into our Retool app. We can then start adding additional functionality for taking action on the rows in our Google sheet. This will allow for quickly modifying the body of the email and sending the email via a “Send Email” button that will link to an SMTP resource connected to your Gmail account.

Schematic overview of the hackathon email app

At a high level, we’ll have a really simple but powerful email blaster, and we’ll build it in about 10 minutes.

Before we get started, sign up for a free Retool account here.

Build the UI

From the Retool home screen, choose “Create new” to create a new blank app and give it a name. From there, you’ll be taken into the Retool builder, where you’ll see on the right hand side a bunch of UI components that you can drag and drop to start laying out your user interface.

(On the bottom, you’ll also see a set of queries to reference different resources—in our case, Google Sheets and SMTP—but we’ll get to that further down in the post).

For now, we’ll minimize the bottom panel, and start building the UI by:

  • Giving our app a title using a text component
  • Leveraging the default table component for our participant data (which comes pre-placed and pre-populated with mock data in every new Retool app)
  • Inserting a container component which will serve as our email manager area

To have a useful container for sending emails, we know we’ll need some inputs for fields like “To”, “Subject line”, the email body, and, of course, a “Send” button.

Let’s insert the relevant components for these: “text input” components for shorter fields, a “text area” component for the email body, and, of course, a “button” for the “Send” button.  

Now, you’ll likely have some repetitive email copy based on different types of your hackathon participants or relationships with them. In that case, you’ll probably want to create a few templates.

To be able to choose between those templates, you can start by inserting a drop-down menu in our email sender UI by dragging the “selector” component. This will be your template selector.

Next you can create multiple “text area” components to contain the values for each unique email body you want to send. Lastly, you will set the hidden property for each text area to only display when the appropriate template is selected in the dropdown.

Now that we have our email section completed, let’s give some attention to our table, and view our Google Sheet data.

Get the data from Google Sheets

To get data into our table, we’ll now focus on the bottom panel of the Retool app:

The bottom panel  of the Retool app showing the default query named “query1”
The bottom panel showing the default query named “query1”

We’ll need to create a query—or we can simply rename the default one. Let’s name it “listSheetsContacts”, select “Chris’s Google Sheets” as a resource, select the relevant Google Sheet—in my case, the “Hackathon Participant list”—then preview it.

You’ll see a little sample of data which will confirm this worked:

Creating and running a new query in Retool

As you can tell from the GIF,  I had already connected my Google Sheets to Retool; if you are connecting your Google Sheets (or another resource) for the first time, select “Create a new resource” from the Resources drop=down.

This will take you to the resource creation screen, from which you can choose the Google sheets connector:

Selecting a resource in Retool

From here, you’ll be taken to the configuration screen, where you can authorize Retool to access your sheets via OAuth by clicking the “Connect to Google Sheets” button:

Once you’ve successfully authorized Retool, return to your email manager app where the resource will show up in the bottom panel. You can follow the steps above, selecting your resource and the appropriate GSheet.

Now, let’s move this data to our table. We'll select the table, and use a little bit of JavaScript to reference our query and populate the table data:

As you can see, when you start typing the name of a query in curly braces with the goal of entering this syntax:

`{{ }}`

Retool will auto-suggest your queries and let you simply select the one you want.

Tip: When managing lots of data in a table, it can be helpful to toggle the “compact mode” option in the table settings in order to show more data on a single page of the table component.

Now that we see hackathon participant data in our table, let’s start referencing it in our email component. We want to give our user the ability to select a row in the table and update our new email fields with appropriate values.

Starting with the “To:” field, we’ll want it to reference the email from the user selected row in our table:

`{{ }}`

Let’s add in a standard subject line, and type up some test copy in the email body, including a dynamic reference to the participant’s/recipient’s name:

`{{ }}`

Now, as you scroll through the table, you see the data for each person showing in the email manager container:

The missing part? We need to be able to send each of these folks an email via our send button, but for that we need to link our app to our email account. Let’s do that.

Add your Gmail account

We'll go back to our query field and create a new resource query called “send email”, and use the Smtp connector as a resource:

To create the connection from scratch, choose “Create new resource”, select “SMTP”, and enter the following data but insert your own username and password:  

Don’t forget to test your connection!

Once you’ve successfully added this resource, Retool can send emails on your behalf.

Returning to our app, open the “sendEmail” query and enter the email that you want the emails to come from, and fill out relevant fields (to:, subject:, and body: ) with JavaScript to reference relevant fields from the email manager container:

We’ll also want to have a success notification come up after each email sent, so that we don’t send duplicate emails. In order to do this, we’ll go to the response section and set up a query success message:

Lastly, we’ll need to link the button in the email manager to the Gmail SMTP resource, and our “sendEmail” query. Event handlers to the rescue!

Select the “Send Email” button and go to the inspect panel on the right hand side. Scroll down to the interaction section and add an event handler. In the event handler modal pop-up, create a “Click” event with a “Trigger Query” action so when the button is clicked, it triggers the “sendEmail” query.

Tip: Setting a debounce value will help to prevent duplicate button click consequences.

And voila—your hackathon email manager app is ready!

Perfecting it with JavaScript

There are many small modifications you could do to make this app more exciting and useful:

  • You could modify the email copy based on what segment the hackathon participant is in: for example, a customer or a prospect, a user on a free or paid plan, a personal contact, etc. (hint: you’ll be using ternary operators)
  • You could build out more specific email templates to help engage more personally with different users
  • You could change the style and colors of the app to be more visually appealing
  • You could automatically log email sending activity back to the Google Sheet and create alerts if the user has already been emailed

If you are more of a visual learner — and to learn some of these extra steps—check out this video on our YouTube channel: