Script for Automatic Email Notification with Google Docs

Script for Automatic Email Notification with Google Docs

Ever wanted to have Google magically send emails to remind people about things?

Well here’s an example of how we did just that to have the Blog Post Scheduler send emails to go!-Mates who have a blog post due the following week with the subject line being “Blog Post Due in 1 Week,” and their corresponding topic (Column B) as the message of the email.

We wrote the formulas in A4:A7 to show the email address of the row below the date, when the Due Date = Today’s date (but that is irrelevant to creating the script).

  1. Create a Google Spreadsheet
  2. Click on “Tools –> Script manager…” in the toolbar
  3. Click on “New”
  4. Paste the following script in the box and adjust each line based on the notes describing how each line works and comparing it to the Blog Post Schedule:
var Yes = “Yes”;
 function sendEmails() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0]; //”0″ is the first sheet
 var startRow = 4; // First row of data to process-actual row# (this is cell A4)
 var numRows = 4; // Number of rows to process (goes from A4-A7)
 var dataRange = sheet.getRange(startRow, 1, numRows, 11) //no idea
 // Fetch values for each row in the Range.
 var data = dataRange.getValues();
 for (i in data) {
 var row = data[i];
 var emailAddress = row[0]; // First column
 if (emailAddress == “”) continue;
 var message = row[1]; // pulls the topic from the second column for the email message
 var subject = “Blog Post Due in 1 Week”; //makes the subject what is in quotes
 var email = row[0]; // first column list of email addresses
 if (email = Yes) ;{
 MailApp.sendEmail(emailAddress, subject, message);
 }
 }
 }

5.  Once there are no bugs, run the script by hitting the play button or clicking on the name of the script.  Make the formulas in your spreadsheet send a test to you before you test it on other people…they will appreciate not having all of the clutter showing up in their inbox.

6.  If you want the script to run repeatedly, click on “Resources –> Current project’s triggers –> No triggers set up. Click here to add one now.”

7.  Adjust the settings to fit your needs; if you want it to run at a specific time every day/week, change the Event’s drop-down to “Time-driven” and then set accordingly and hit save.

8.  Save the file code and you should be ready to rock and roll.

What are you using Google scripts to automate? How are you doing it? How’s it worked or not worked for you? Let us know in the comments, or reach out via email!

Enjoying our insights? Enter your email below to subscribe to our monthly newsletter.
Previous
Next