Various challenges give rise to various solutions. This was the scenario I encountered while trying to figure out how to send emails to the data collected from a Google Form linked to a Google Sheet. Without further delay, let's dive right in.
To begin, create a Google Form and share the link with individuals for them to complete.
I will enter four demonstration email addresses using a temporary email service known as Yopmail.
Following that, I can access the responses in my Google Sheet by navigating to your email, clicking on the square icon next to your profile picture, and then selecting "Google Form."
Choose the Dev.to option that was previously created and click on "Responses."
Click on link to sheet
Click create
We have another tab opened containing our records
To write the script, click on Extensions
Click on Add scripts
Now, this is the stage where we write our script. Below, I have a script designed to send a reminder email containing the Google link to an event.
function sendReminderEmails() {
// Get the active sheet
let sheet = SpreadsheetApp.getActiveSheet();
// Get the values in the range B2 to B3
let emailAddresses = sheet.getRange("B2:B5").getValues(); // select the emails by column
// Set the reminder date and time
let reminderDate = new Date("2023-09-23T16:00:00"); // Change the date and time accordingly
// Google Meet link
let meetLink = "https://meet.google.com/xpy-deuq-odk"; // Replace with your Google Meet link
// Image URL
let imageUrl = "https://res.cloudinary.com/drsimple/image/upload/v1695292124/RDB101_uy0zjr.png"; // Replace with your image URL
// Loop through the email addresses and send reminder emails
for (let i = 0; i < emailAddresses.length; i++) {
let emailAddress = emailAddresses[i][0]; // Get the email address from the array
// Check if the email address is not empty
if (emailAddress) {
// Compose the subject and body of the email with emojis, Google Meet link, and image
let subject = "RDB101 Reminder 😊";
let body = "Hello,<br><br>This is a friendly reminder for the <b>RDB101</b> course on <i>Saturday, 23 September</i>, from <i>4:00 to 6:00 pm (Africa/Lagos time)</i>.<br><br>Topic: Relational Database 101<br><br>We hope to see you there!<br><br>Here is the Google Meet link to join the session:<br><a href='" + meetLink + "'>" + meetLink + "</a><br><br><img src='" + imageUrl + "' alt='RDB101 Image'><br><br><br>Best regards,<br>Abayomi Ogunnusi";
// Send the email with HTML formatting
MailApp.sendEmail({
to: emailAddress,
subject: subject,
htmlBody: body,
});
// Log the email address to keep track
Logger.log("Reminder sent to " + emailAddress);
}
}
}
The key thing to highlight here is the line: "let emailAddresses = sheet.getRange("B2:B5").getValues();". This is where we grab the email addresses from our spreadsheet, specifically from Column B2 to B5 in our case.
Click save and run
Sometimes a pop-up appears. Simply click on it to review permissions.
Choose the account to send the email
Click advance and untitled project
Click allow
Inspect the execution logs to observe how the emails are being sent.
Let's verify if one of the emails received the message we sent.
Conclusion
I trust that this article has been informative and has presented a straightforward procedure for establishing and running a script to dispatch emails through Google Forms and Sheets.
Top comments (2)
This is really interesting information. I've had success using Google Sheets scripts for email work. This is effective, but managing bulk emails manually can be difficult. To make this process easier, I recommend visiting snov.io/email-finder It's a handy email address search and verification tool that integrates seamlessly with Google Sheets. With it, you can further automate your email campaigns, saving time and improving your customer outreach strategy. Try it and see how it improves your email campaigns!
Thanks for taking time for writing this post. I have better option. What are your views about using existing Mail merge apps like this one?