Email Outreach : Sending Bulk Emails from Google Sheets Using Scripts
Tags: googlecloud, javascript, beginners, webdev
Published: 2023-09-21 11:25:35

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vo0r2gqatyykdutya2s8.png)



I will enter four demonstration email addresses using a temporary email service known as Yopmail.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wdx3lnse9d4j7yr8h151.png)

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."


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p2tmys6hb50v6lciitb9.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wegl3cwgua12sog776cj.png)

Choose the `Dev.to` option that was previously created and click on "Responses."


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8cg37fdqljqmuq0ghkq6.png)

Click on link to sheet

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d18txgtblx4t982evf4x.png)

Click create

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wlp9u0ea85pdnc9qwjcu.png)

We have another tab opened containing our records

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fa4i91jv95taqkgeo7fb.png)

To write the script, click on `Extensions`

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vybt72rfdbewoxfjhfit.png)

Click on `Add scripts`

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0r9yr2vq46avz43udro4.png)


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.

```javascript
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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3qjdkihzatwvdghuedl0.png)


Click save and run

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1v0kp2vzdxi449m2hetk.png)

Sometimes a pop-up appears. Simply click on it to review permissions.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d67vpkjja8dw9fp3mvxy.png)

Choose the account to send the email
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gj0wzlcdcbsvg2950r56.png)

Click advance and untitled project 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tzp8egvkqnoqju5gwg9a.png)

Click allow

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tq525a2wt4o1r26pu9q7.png)

Inspect the execution logs to observe how the emails are being sent.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vuvjxqd71w547zoos871.png)

Let's verify if one of the emails received the message we sent.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z14qju4zfjig6f8m6i2s.png)


##### 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.