Lead Notification Generator Using Google Apps Script

Overview

To begin, I work as a Women’s Soccer Recruiter on the side as I look for my new position in tech. I am the Director of Women’s Recruitment for Select Generation which is a new soccer recruitment agency for high school players. I have played soccer since I was 4 years old and I always find a way to be a part of it. I work with three others and I have found myself to be the technologically savvy person out of our groups.

As a young professional who has spent the last six years finding shortcuts with the computer to make my life easier, I found myself doing tedious procedures with managing our players through Google Drive. Don’t get me wrong- I LOVE Google and every component that have gave computer users to utilize. However, as someone who uses Google everyday, I was not utilizing Google App Scripts. With my Bachelors in Comsci, I can almost automate everything in a google app with lines of code. So I did just that and made a notification generator for when my colleagues and I are assigned an athlete to manage.

We have created a database of players across California which is over 1,000 athletes to keep track of. We divide and conquer the database to talk to our athletes. I created a google sheets to track the progress of communicating with the athletes and found many ways to improve the capability and management of the data. The question I asked myself is “What if I get assigned or reassigned a player but someone didn’t communicate who it is?” So I created two functions that sends an email to the assignee (who is picked from the dropdown component of google sheets) of an athlete when the field is edited. The program is ran as soon as the field is edited.

Google Apps Scripts has many Libraries to use to develop a script within their applications. I used the Simple Triggers to send the email when the trigger onEdit(e) is used. The onEdit(e) “runs when a user changes a value in a spreadsheet.”

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dropdownValue = sheet.getActiveCell().getValue(); // Get the value of the active cell
  var rowNumber = sheet.getActiveCell().getRow(); // Get the row of the active cell
  
  var athleteName = sheet.getRange("A" + rowNumber).getValue(); // Get the athletes name from column A
  
  var targetEmail = ""; // Initialize the target email variable
  var targetName = dropdownValue;
  
  if (targetName === "Anna") {
    targetEmail = "email@email.com";
  } else if (targetName === "Sam") {
    targetEmail = "email@email.com"; 
  } else if (targetName === "Amanda") {
    targetEmail = "email@email.com"; 
  } else if (targetName === "Victor") {
    targetEmail = "email@email.com";
  }
  
  if (targetEmail) {
    sendEmail(targetEmail, rowNumber, athleteName);
  }
}

Set Up the Trigger:

To use the Simple Triggers, you must change the settings in the script to “onEdit” and “On Change” to make it work. Follow these steps to do that:

    Close the script editor.
    In your Google Sheets document, click on "Extensions" in the top menu.
    Choose "Apps Script."
    Click on the clock icon ("Triggers") on the left sidebar.
    Click on the "+ Add Trigger" button at the bottom right.
    In the "Run" dropdown, select the function onChange.
    Choose "On change" in the "Deployment source" dropdown.
    Click "Save."

Then I used the MailApp class to send the email with the function sendEmail().

function sendEmail(recipient, rowNumber, athleteName) {
  var subject = "You have been assigned an athlete";
  var message = "You have been assigned " + athleteName + " on row #" + rowNumber + ". Click https://docs.google.com/spreadsheets/d/1mDJ_H0kiHFNcDnX9PZXFNR7iGvcSd38dcN3zf0jEBqk/edit#gid=0' to view the spreadsheet.";
   var options = {
    name: "SG Lead Notification" // Set the sender name
   };

  MailApp.sendEmail(recipient, subject, message, options);
}

Difficulties

I ran into some difficulties where the email was not sending on the trigger. I found using

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

vs. ``` var sheet = e.range.getSheet(); var targetRange = sheet.getRange(“A2:A10”); // Change this to the range of your dropdown

``` Did the job because the “On Change” was not reading the range of the data. I have not came to the conclusion as to why it would but the error says it may have to do with G Suites.

Reflection

I am excited to benefit my work and efficiency by creating more components with Google Apps Script. People need to utilize the tools google is giving to us for free to make our businesses run more effectively and produce more work with automated functions. I think companies should transition from Office apps to Google if they want to have a booming business.