Event Reminder using GoogleSheet

Here’s a step-by-step guide on how to create reminders in a Google Sheet using Apps Script:

Step 1: Setup Spreadsheet

• Create a new Google Sheet by going to Google Drive and selecting “New” > “Google Sheets”.
• Rename the sheet to something descriptive like “Event Reminders”.
• Add a new row to the sheet with the following column headings: “Date”, “Event”, “Email”, and “Reminder Sent”.
• Enter the date, event, email, and reminder sent information for the event that you want to create a reminder for in the appropriate columns.

Step 2: Create a Google Apps Script to send email reminders

• Open the Apps Script editor by selecting “Tools” > “Script editor” from the Google Sheets menu.
• In the Apps Script editor, create a new script file by selecting “File” > “New” > “Script file”.
• Name the script file something descriptive like “SendEventReminder”.
• Copy and paste the following code into the script file:

function sendEventReminder() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Event Reminders'); // Replace 'Event Reminders' with the name of your sheet
  var lastRow = sheet.getLastRow();
  var date = sheet.getRange(lastRow, 1).getValue(); // Assumes date is in column 1
  var event = sheet.getRange(lastRow, 2).getValue(); // Assumes event description is in column 2
  var email = sheet.getRange(lastRow, 3).getValue(); // Assumes email address is in column 3
  var reminderSent = sheet.getRange(lastRow, 4).getValue(); // Assumes reminder sent flag is in column 4
  var reminderDate = new Date(date);
  var today = new Date();

  // Check if a reminder needs to be sent
  if (reminderSent != 'Yes' && reminderDate.getTime() - today.getTime() <= 86400000) { // 86400000 is the number of milliseconds in a day
    var subject = 'Reminder: ' + event;
    var message = 'This is a reminder that ' + event + ' is scheduled for tomorrow.';
    MailApp.sendEmail(email, subject, message);
    sheet.getRange(lastRow, 4).setValue('Yes');
  }
}

• Save the script file.
• Set up a time-driven trigger to run the sendEventReminder() function every day. To do this, go to “Edit” > “Current project’s triggers” in the script editor. Click on the “Add Trigger” button in the bottom right corner, set the function to “sendEventReminder”, set the event to “Time-driven”, and choose a time of day to run the script.
• Check your email the day before the event to make sure that the reminder was sent.
• Check the sheet to make sure that the “Reminder Sent” column has been updated to “Yes” for the appropriate row.

That’s it! You can now use this script to create reminders in your Google Sheets and have them automatically added to your calendar.