This post includes the Google Sheets interface and Apps Script code discussed in the YouTube video. For detailed explanations on how to use Apps Script and other specifics, please refer to the YouTube video below. By setting up triggers, you can automate the execution of Apps Script code to update Google Sheets or send emails automatically.
Google Sheet
Create Columns:
Create columns to organize the necessary information for sending emails. For example, include columns labeled “Name”, “Email”, and “Sent”. Additionally, add a “Paydate” column to represent the due date for each bill.
In this example, no manual input is required.
- The “Add Rows” trigger in Apps Script will automatically add rows with the bill name (column A), email address (column B), and paydate (column F).
- The “Send Emails on Paydate” trigger will send an email on the specified paydate and update the “Sent” status in column E.
Apps Script code – Add rows
You can customize the bills, email addresses, and days in the code to suit your specific needs. In the provided script, they are highlighted in red in the code. After setting up the Apps Script code, configure the addRowsToSheet_semimonthly() function as a trigger to add the bills each month.
function addRowsToSheet_semimonthly() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
if (!sheet) {
Logger.log("Sheet not found. Please check the sheet name.");
return;
}
const startDate = new Date();
// Function to format dates
const formatDate = (date) => Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd");
// Data rows with unique dates
const data = [
["Water Bill", "[email protected]", "", "", "", formatDate(addDays(startDate, 4))],
["Phone Bill", ".....@gmail.com", "", "", "", formatDate(addDays(startDate, 21))],
["Electric Bill", ".....@gmail.com", "", "", "", formatDate(addDays(startDate, 27))],
];
// Append each row
data.forEach(row => sheet.appendRow(row));
Logger.log("New rows added.");
}
// Helper function to add days to a date
function addDays(date, days) {
const newDate = new Date(date);
newDate.setDate(newDate.getDate() + days);
return newDate;
}
Apps Script code – Send emails on Paydate
You can further customize the subject (the subject line of the email) and body (the content of the email) in the code as per your specific needs. In the provided script, subject and body are highlighted in red in the code, and you can replace the text inside them with your own custom message.
Once the Apps Script code is set up, configure the PaydatesendEmail() function as a trigger to send emails daily. After the daily trigger is configured, the script will automatically run every day and send emails for entries with a matching paydate.
function PaydatesendEmail() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with the name of your sheet
if (!sheet) {
Logger.log("Sheet not found. Please check the sheet name.");
return;
}
var nameRange = sheet.getRange("A2:A" + sheet.getLastRow()); // Names
var names = nameRange.getValues();
var emailRange = sheet.getRange("B2:B" + sheet.getLastRow()); // Email addresses
var emails = emailRange.getValues();
var msgRange = sheet.getRange("D2:D" + sheet.getLastRow()); // Message content
var messages = msgRange.getValues();
var emailSentRange = sheet.getRange("E2:E" + sheet.getLastRow()); // Sent status
var emailSentValues = emailSentRange.getValues();
var PaydateRange = sheet.getRange("F2:F" + sheet.getLastRow()); // Payment dates
var paydates = PaydateRange.getValues();
const subject = "Important: Payment Due"; // subject
const timeZone = Session.getScriptTimeZone();
// Get today's date formatted to match spreadsheet date format
const today = Utilities.formatDate(new Date(), timeZone, "MM/dd/yyyy");
for (var i = 0; i < emails.length; i++) {
const name = names[i][0];
const email = emails[i][0];
const message =messages[i][0];
const paydate = paydates[i][0];
const sentStatus = (emailSentValues[i][0] || "").toLowerCase();
// Format paydate to match today's format
const formattedPaydate = Utilities.formatDate(new Date(paydate), timeZone, "MM/dd/yyyy");
// Skip if email is invalid, already sent, or not payment date
if (!email.includes("@") || sentStatus.includes("sent")) continue;
if (today !== formattedPaydate) continue; // Skip if not payment date
// Define the HTML message with personalized name and payment date
const htmlMessage = `
<div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto;">
<h1 style="background-color:DodgerBlue;text-align:center;border: 2px solid black;color:white;margin: 20px;padding: 20px;">
Payment Due Notice
</h1>
<h2>Payment Reminder</h2>
<p>Hello ,</p>
<p>This is a reminder that your payment of ${name} is due today (${formattedPaydate}).
${message}. Thank you for your attention to this matter.
</p>
<p style="color: #666; font-size: 12px;">
This is an automated reminder. Please do not reply to this email.
</p>
</div>
`;
try {
// Send the email
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: htmlMessage
});
// Log the timestamp in the sheet, marking it as sent
const timestamp = Utilities.formatDate(new Date(), timeZone, "MMM dd, yyyy HH:mm");
sheet.getRange("E" + (i + 2)).setValue(`Sent at: ${timestamp}`);
Logger.log("Email sent to: " + email);
} catch (error) {
Logger.log("Error sending email to " + email + ": " + error.message);
}
}
}