Never Forget Happy Birthday Bot

Chapter 1: Introduction

Welcome to our class on building your very own Birthday Reminder Bot! This course is designed for those interested in automation, beginner programmers, and anyone who is tired of forgetting birthdays. If you've ever found yourself rushing to find a last-minute gift or scrambling to send a belated birthday message, this class is for you. We'll be exploring Google Sheets and Google Apps Script to create a handy bot that reminds you of birthdays, so you'll never forget another special day.

Purpose of the Course

The purpose of this course is to guide you through the process of creating an automated Birthday Reminder Bot. Not only will this help you keep track of important dates, but it will also give you an introduction to the world of automation and coding. Automation can save you time and prevent simple mistakes. As we progress through the course, you'll see just how powerful these tools can be.

The Birthday Reminder Bot

Our bot's functionality is simple but powerful. We'll use a Google Sheets document to store names and corresponding birthdays. The bot will then check this document daily and send an email if there's a birthday that day.

Real-World Applications and Benefits

The principles you'll learn in this course extend far beyond just birthday reminders. The skills of reading from a database (in this case, Google Sheets) and performing actions based on that data are foundational to many programming and automation tasks. Additionally, you'll learn about Google Apps Script, a versatile tool for automating various Google apps like Docs, Sheets, and Gmail. Once you understand these basics, you'll unlock countless opportunities for automation in your personal and professional life.

By the end of this course, you'll not only have a functioning Birthday Reminder Bot but also the knowledge and skills to explore other automation projects. You'll be well on your way to growing your army of robot minions! Let's get started.

In our next chapter, we'll go over setting up your environment, including creating a new Google Sheets document and organizing your data. Stay tuned!

Chapter 2: Setting Up Your Environment

Welcome to the second chapter of our course. Before we delve into building our Birthday Reminder Bot, it's crucial to have a well-organized workspace. This chapter will guide you through setting up your environment, introducing Google Sheets, and showing you how to create a new document and arrange your data.

An Overview of Google Sheets

Google Sheets is a web-based application that allows you to create, edit, and store spreadsheets online. You can use it to keep track of data, perform calculations, and even create charts and graphs. For our course, we'll be using it to store names and corresponding birthdays.

One of the many benefits of Google Sheets is that it easily integrates with Google Apps Script, allowing us to automate tasks directly from the data in our spreadsheet.

Creating a New Google Sheets Document

Let's start by creating our Google Sheets document:

Open Google Sheets (sheets.google.com).
Click on the "+ Blank" button to create a new blank spreadsheet.
Name your spreadsheet. You can do this by clicking on the "Untitled Spreadsheet" text at the top left and entering your preferred name, like "Birthday Reminder Bot".

Organizing Data in Google Sheets

For our Birthday Reminder Bot, we'll need two columns: one for names and one for birthdays.

Click on the first cell (A1) and type "Name". This will be our column for names.
Click on the second cell in the first row (B1) and type "Birthday". This will be our column for birthdays.

Next, start filling in the data:

Under the "Name" column, start entering the names of the people whose birthdays you want to remember.
In the "Birthday" column corresponding to each name, input their birthday. Make sure to format it as a date (mm/dd/yyyy). Google Sheets should automatically detect this, but if not, you can manually change the format.

By the end of this process, you'll have a neatly organized list of names and birthdays for our bot to check.

Great job setting up your environment! In the next chapter, we'll introduce Google Apps Script and guide you on accessing the Apps Script editor. We'll also cover basic syntax and structure.

Chapter 3: Introduction to Google Apps Script

Having set up your environment in the last chapter, we're ready to step into the world of automation with Google Apps Script. In this chapter, we'll introduce you to Apps Script, show you how to access the Apps Script editor, and help you understand its basic syntax and structure.

What is Google Apps Script?

Google Apps Script is a cloud-based scripting language for light-weight application development in the G Suite platform. It provides easy ways to automate tasks across Google products and third-party services.

Using Apps Script, you can create, access, and modify Google Calendar events, Google Sheets data, Gmail emails, and much more. Today, we will focus on its ability to interact with Google Sheets and Gmail to create our Birthday Reminder Bot.

Accessing Google Apps Script Editor

Accessing the Apps Script Editor from Google Sheets is straightforward. Here are the steps:

Open your Google Sheets document.
Click on "Extensions" from the menu bar at the top.
From the drop-down list, navigate to "Apps Script."

This action will open the Apps Script Editor in a new tab. Here is where we'll write the code for our Birthday Reminder Bot.

Basic Syntax and Structure

Google Apps Script is based on JavaScript, a popular language used for web development. If you're new to coding, don't worry; we'll walk you through each step.

Here's a simple example of what a Google Apps Script might look like:

function myFirstFunction() {
  var message = "Hello, world!";
  Logger.log(message);
}

In this script, function is a keyword indicating that we're defining a function, which is a reusable block of code. myFirstFunction is the name of the function, and () is where we'd put any inputs to the function (we don't need any for this script).

Inside the {} brackets is the code that runs when the function is called. We first define a variable message with the text "Hello, world!", then log this message to the Logger, a tool in Apps Script that helps us see what our code is doing.

We'll get into more details as we start to build our bot, but this gives you a basic idea of how Apps Script works.

Congratulations on completing Chapter 3! Coming up in Chapter 4, we'll build upon what you've learned and write your first function in Apps Script. We'll also explore how to run and debug your function. See you there!

Chapter 4: Building Your First Function in Apps Script

Welcome to Chapter 4! Now that you've learned the basics of Google Apps Script, it's time to start coding. In this chapter, we'll guide you through writing your first function in Apps Script. By the end of this lesson, you'll know how to create a basic function, run it, and debug it.

Writing a Basic "Hello World" Function

Let's start by creating a simple function that logs the message "Hello, World!" to the Apps Script console. This is a common practice when learning a new programming language, as it helps you understand the basic structure of the code.

Here's how you do it:

Open your Google Apps Script editor (go to Extensions > Apps Script in Google Sheets).
Delete any code that is already there and replace it with the following code:

function helloWorld() {
  var message = "Hello, World!";
  Logger.log(message);
}

In this code, helloWorld is the name of the function, var message = "Hello, World!"; is a line of code that creates a variable named message and assigns it the string value "Hello, World!". Logger.log(message); is a command that sends the value of message to the log, which we can view in Apps Script.

Running the Function

Now let's run our function and see the result:

Click on the small dropdown near the bug icon (it's the "Select function" dropdown) in the toolbar and select helloWorld.
Press the play icon (right to the bug icon) to run the function.
Go to "View > Logs" in the menu or press Ctrl + Enter to open the log.

You should see "Hello, World!" in the log. This means your function ran successfully!

Debugging the Function

Coding can be complicated, and it's common to make mistakes or encounter issues. This is where debugging comes in. Debugging is the process of identifying and fixing errors in your code.

For our "Hello, World!" function, we used Logger.log to print the message. This logging feature is a simple but powerful tool for debugging in Google Apps Script. If your function isn't running correctly, you can use Logger.log to print out values and see what's happening at each step of your code.

Great job on completing Chapter 4 and running your first function in Google Apps Script! In the next chapter, we will teach you how to access and read data from your Google Sheets document using Apps Script.

Chapter 5: Accessing Spreadsheet Data with Apps Script

Welcome to Chapter 5. By now, you have learned how to write and run your basic function in Google Apps Script. In this chapter, we'll introduce the Spreadsheet Service in Apps Script and show you how to access and read data from your Google Sheets document. We'll also guide you on handling dates in JavaScript.

Introduction to Spreadsheet Service in Apps Script

The Spreadsheet Service in Google Apps Script provides methods and classes that allow you to interact with Google Sheets. With it, you can access data in your sheets, write data, format cells, and more. For our Birthday Reminder Bot, we'll mainly be using it to read data from our sheet.

Reading Data from the Spreadsheet

Let's start by writing a function that reads and logs the data from our spreadsheet:

function readSheetData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 2);
  var values = range.getValues();
  
  Logger.log(values);
}

In this script, SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); gets the active sheet in your open spreadsheet. sheet.getRange(1, 1, sheet.getLastRow(), 2); gets the range of data from the first row, first column to the last row, second column. range.getValues(); retrieves the data in this range as a two-dimensional array.

Handling Dates in JavaScript

Working with dates can be tricky in any programming language, and JavaScript is no exception. However, Google Apps Script provides several handy methods for working with dates.

When you read a date from Google Sheets using getValues(), it's returned as a JavaScript Date object, which allows you to access the day, month, and year, among other things.

Here's an example of how you might work with dates:

var date = new Date(); // gets the current date and time
var day = date.getDate(); // gets the day of the month (1-31)
var month = date.getMonth() + 1; // gets the month (0-11, so we add 1 to get 1-12)
var year = date.getFullYear(); // gets the year

Logger.log(month + '/' + day + '/' + year); // logs the date in MM/DD/YYYY format

You've now learned how to access and read data from your Google Sheets document and how to handle dates in JavaScript. Great job! In the next chapter, we will start building the Birthday Checker function using these principles.

Chapter 6: Building the Birthday Checker Function

Congratulations on making it to Chapter 6! With the foundation laid, it's time to start building the core function of our Birthday Reminder Bot – the Birthday Checker function. This function will read the data from our Google Sheet and check if today is anyone's birthday.

Creating the Birthday Checker Function

Let's dive right in and create our checkBirthdays function. Follow these steps:

Open your Google Apps Script editor.
Replace any existing code with the following script:

function checkBirthdays() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(2, 1, sheet.getLastRow()-1, 2);
  var values = range.getValues();
  
  var today = new Date();
  today.setHours(0, 0, 0, 0); // This sets the time to 00:00:00, allowing us to ignore the time when comparing dates
  
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    var name = row[0];
    var birthday = new Date(row[1]);
    
    if (birthday.getMonth() == today.getMonth() && birthday.getDate() == today.getDate()) {
      Logger.log("It's " + name + "'s birthday today!");
    }
  }
}

Understanding the Code

This script might look complicated, but let's break it down:

We're still using the Spreadsheet Service to get our data, just like in Chapter 5.
today.setHours(0, 0, 0, 0); sets the time for the today variable to 00:00:00. This allows us to compare dates without worrying about the time.
for (var i = 0; i < values.length; i++) {...} is a loop that goes through each row in our data.
var row = values[i]; gets the current row in each iteration of the loop.
var name = row[0]; and var birthday = new Date(row[1]); get the name and birthday from the current row.
The if statement checks if the month and day of birthday are the same as today. If they are, it logs a message saying it's that person's birthday.

Running the Function

Just like in Chapter 4, you can run your function by selecting checkBirthdays from the dropdown and clicking the play icon. If today is anyone's birthday according to the data in your spreadsheet, you should see a message in your logs!

Great job on completing Chapter 6! You have now created a bot that can check if today is anyone's birthday. In the next chapter, we will add the capability for our bot to send an email when it's someone's birthday.

Chapter 7: Sending Emails with Apps Script

Welcome to Chapter 7! Having built the birthday checker function, let's now add an action for the bot to perform when it's someone's birthday - sending an email. We'll be using the Gmail Service in Apps Script to accomplish this.

Introduction to Gmail Service

The Gmail Service in Google Apps Script provides methods to control and interact with Gmail. This allows us to automate email-related tasks such as sending, reading, and replying to emails. For our Birthday Reminder Bot, we'll use it to send a birthday reminder email.

Modifying our Birthday Checker Function

To send an email when it's someone's birthday, we'll need to modify our checkBirthdays function. Here's how you do it:

Open your Google Apps Script editor.
Replace the checkBirthdays function with the following script:

function checkBirthdays() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(2, 1, sheet.getLastRow()-1, 3); // Changed to 3 columns to include email
  var values = range.getValues();
  
  var today = new Date();
  today.setHours(0, 0, 0, 0);
  
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    var name = row[0];
    var birthday = new Date(row[1]);
    var email = row[2]; // Added email
    
    if (birthday.getMonth() == today.getMonth() && birthday.getDate() == today.getDate()) {
      var subject = "Happy Birthday, " + name + "!";
      var body = "Happy Birthday, " + name + "! This is a reminder from your Birthday Reminder Bot.";
      
      GmailApp.sendEmail(email, subject, body); // Added this line to send an email
    }
  }
}

Understanding the Changes

Here's what we changed:

We changed sheet.getRange(2, 1, sheet.getLastRow()-1, 2) to sheet.getRange(2, 1, sheet.getLastRow()-1, 3). This includes the third column (the email column) in our range.
We added var email = row[2]; to get the email from the current row.
We defined subject and body variables to specify the subject and body of our email.
We added GmailApp.sendEmail(email, subject, body); to send an email with the subject and body to the email address.

Running the Function

Run your function as before. If today is anyone's birthday according to your spreadsheet, the bot will send an email to the respective person's email address!

Congratulations on completing Chapter 7! Your Birthday Reminder Bot can now send birthday emails. In the next chapter, we'll learn how to schedule our bot to run automatically every day.

Chapter 8: Automating the Bot with Triggers

Welcome to Chapter 8! You've built a fantastic Birthday Reminder Bot that checks birthdays and sends out emails. But wouldn't it be great if the bot could run by itself every day without manual intervention? That's what we're going to do in this chapter, using Apps Script's Triggers.

Understanding Triggers

In Google Apps Script, a trigger is an event that starts the execution of a script. Triggers can be based on various events, like opening a document, editing a cell, or at a particular time. For our Birthday Reminder Bot, we'll set a time-driven trigger to execute our checkBirthdays function once a day.

Setting a Daily Trigger

Here's how you can set up a trigger for your bot:

Open your Google Apps Script editor.
Click on the clock icon in the toolbar (it's the "Current project's triggers" button).
Click on the "+ Add Trigger" button at the bottom-right of the page.
In the function dropdown, select checkBirthdays.
In the event dropdown, select "Time-driven".
In the next two dropdowns, select "Day timer" and the hour interval when you'd like the trigger to run (for example, "6am to 7am").
Click "Save".

Understanding the Changes

What you have done is setting a time-driven trigger that will automatically run the checkBirthdays function once a day, during the hour interval you specified. This means the Birthday Reminder Bot will check birthdays and send emails every day, all by itself!

Finalizing and Testing

It's always a good idea to test your automation before considering it final. Run the checkBirthdays function manually one more time and check that everything works as expected. If today is anyone's birthday according to your spreadsheet, you should receive an email!

If you encounter any errors or unexpected behavior, review your code, make sure the data in your spreadsheet is correct, and try again.

Congratulations on completing Chapter 8 and the final chapter of this course! Your Birthday Reminder Bot is now fully automated and will run every day. You've done a fantastic job!

In future courses, we'll explore more advanced topics and build more complex bots.

Chapter 9: Bonus - Sending Birthday Wishes via Text Messages

Congratulations on successfully creating and automating your Birthday Reminder Bot! As a bonus chapter, we're going to discuss how you can extend the functionality of your bot to send birthday wishes via text messages using third-party services like Twilio.

Please note, using third-party services often involves additional costs and their usage is subject to the terms and conditions of the provider.

Introduction to Twilio

Twilio is a cloud communications platform that allows software developers to programmatically make and receive phone calls, send and receive text messages, and perform other communication functions using its web service APIs.

For this bonus chapter, we'll be using Twilio to send birthday wishes via SMS.

Setting up Twilio

To get started with Twilio, follow these steps:

Go to the Twilio website and sign up for an account.
After confirming your email and phone number, go to the dashboard.
Get a trial phone number or upgrade your account and buy a phone number.
Note down the "ACCOUNT SID" and "AUTH TOKEN" from the dashboard.

Using Twilio in Apps Script

Unfortunately, Google Apps Script doesn't support sending SMS directly. Therefore, we'll have to use the UrlFetch service to send HTTP requests to Twilio's API.

Add the following code to your checkBirthdays function in Apps Script:

if (birthday.getMonth() == today.getMonth() && birthday.getDate() == today.getDate()) {
  var subject = "Happy Birthday, " + name + "!";
  var body = "Happy Birthday, " + name + "! This is a reminder from your Birthday Reminder Bot.";
  var twilioNumber = "+1234567890"; // Your Twilio number
  var mobileNumber = row[3]; // Receiver's number
  
  GmailApp.sendEmail(email, subject, body); // Send an email
  
  // Send an SMS via Twilio
  var url = "https://api.twilio.com/2010-04-01/Accounts/Your_Twilio_Account_SID/Messages.json";
  var payload = {
    "To": mobileNumber,
    "From": twilioNumber,
    "Body": body,
  };
  var options = {
    "method": "POST",
    "payload": payload,
    "headers": {
      "Authorization": "Basic " + Utilities.base64Encode("Your_Twilio_Account_SID:Your_Twilio_Auth_Token")
    }
  };
  UrlFetchApp.fetch(url, options);
}

Understanding the Changes

The added code uses UrlFetchApp.fetch() to send an HTTP POST request to Twilio's Messages API. To is the receiver's phone number, From is your Twilio number, and Body is the message you want to send.

The Authorization header is needed for authentication with Twilio's API. We use Utilities.base64Encode() to encode your Twilio Account SID and Auth Token in the format required by Twilio.

Remember to replace "Your_Twilio_Account_SID" and "Your_Twilio_Auth_Token" with your actual Account SID and Auth Token.

Testing Your Bot

Run your checkBirthdays function as before. If today is anyone's birthday according to your spreadsheet, the bot will send an email and a text message to the respective person!

Congratulations on extending the functionality of your bot in this bonus chapter! This shows how versatile and powerful bots can be with the integration of external services. You've truly built an impressive Birthday Reminder Bot!

Chapter 10: Final Thoughts and Next Steps

Welcome to the final chapter of the course! By now, you've built an automated Birthday Reminder Bot that sends emails and SMS reminders on people's birthdays. Congratulations on your achievement! Before we wrap up, let's take a look back at what we've learned, and discuss some ideas for further expansion and learning.

Recap

You've learned how to:

Access and manipulate data in Google Sheets with Google Apps Script.
Utilize JavaScript's built-in Date object to check birthdays.
Send automated emails with the GmailApp service.
Set up time-driven triggers to automate script execution.
Extend the functionality of Apps Script by integrating it with third-party services like Twilio.

Beyond the Course

Your learning doesn't have to stop here. Google Apps Script and the broader Google Workspace platform offer countless opportunities for automation and bot creation. With what you've learned in this course, consider trying the following:

Modify the bot to handle different types of reminders, such as anniversaries or appointments.
Explore other types of triggers, like on-edit triggers or form-submit triggers.
Try using other services in Apps Script, like the CalendarApp service or the DriveApp service.
Experiment with other third-party services and APIs.

Conclusion

The goal of this course was to help you learn to start automating with Google Apps Script. Automation is all about saving time, reducing mistakes, and focusing on the tasks that matter. We hope that this course has not only taught you how to build a Birthday Reminder Bot, but also inspired you to explore more automation possibilities.

Thank you for choosing RobotAcademy and completing this course. We hope to see you in our upcoming courses!