in short i'v got a schedule in google sheets using GAS
now everytime i test a slot in sheets i get email saying
Slot Booked! Thank you jay for booking. Your slot is scheduled for SATURDAY, 14:00.
jay
Sat Dec 30 1899 13:00:00 GMT+0000 (Greenwich Mean Time)
could anyone please help with this one
code:
now everytime i test a slot in sheets i get email saying
Slot Booked! Thank you jay for booking. Your slot is scheduled for SATURDAY, 14:00.
jay
Sat Dec 30 1899 13:00:00 GMT+0000 (Greenwich Mean Time)
could anyone please help with this one
code:
JavaScript:
// Function to send email with image and booking details
function sendEmailWithImage(Username, Timeslot, Day) {
var imageObject = {};
var successImageLoading = true;
// HTML format variables for username and timeslot
var emailUsername = `<p>${Username}</p>`;
var emailTimeslot = `<p>${Timeslot}</p>`;
var sheet = SpreadsheetApp.getActive().getSheetByName('Schedule');
var emailAddress = "[email protected]"; // Your email
var subject = "Presenter Booked";
// Use try-catch to handle errors while loading the image
try {
imageObject['myImage1'] = DriveApp.getFileById('1oin8reV7pvZZ9kewuYYw-z4lAFf233YI').getAs('image/png');
} catch (error) {
successImageLoading = false;
}
// Ensure Day is a valid string (e.g., "SUNDAY")
if (typeof Day !== 'string' || Day.length === 0) {
Logger.log("Day is not a valid string: " + Day);
return; // Exit the function if Day is invalid
}
// Format Timeslot correctly (assuming it's being passed as a time value from the sheet)
var formattedTimeslot;
if (Timeslot instanceof Date) {
// If Timeslot is a Date object, format the time part only in GMT+1
formattedTimeslot = Utilities.formatDate(Timeslot, "GMT+1", "HH:mm");
} else {
// If Timeslot is just a time string, use it directly
formattedTimeslot = Timeslot;
}
// Message content with Username, Timeslot, and Day
var message = `Slot Booked! Thank you ${Username} for booking. Your slot is scheduled for ${Day}, ${formattedTimeslot}.`;
// Create HTML content for the email
var htmlStartString = `
<html>
<head>
<style type='text/css'>
table {border-collapse: collapse; display: block;}
th {border: 1px solid black; background-color:blue; color: white;}
td {border: 1px solid black;}
#body a {
color: inherit !important;
text-decoration: none !important;
font-size: inherit !important;
font-family: inherit !important;
font-weight: inherit !important;
line-height: inherit !important;
}
</style>
</head>
<body id='body'>
`;
var htmlEndString = "</body></html>";
var emailBody = `${htmlStartString}<p>${message}</p>`;
// Include image in the email body if image loading is successful
if (successImageLoading) {
emailBody += `<p><img src='cid:myImage1' style='width:400px; height:auto;' ></p>`;
}
emailBody += htmlEndString;
// Debugging log to check the final email body
Logger.log(emailBody);
// Send email
MailApp.sendEmail({
to: emailAddress,
subject: subject,
htmlBody: emailBody + emailUsername + emailTimeslot,
inlineImages: (successImageLoading ? imageObject : null)
});
}
// Trigger function for On Change event
function onChange(e) {
var sheet = SpreadsheetApp.getActive().getSheetByName('Schedule');
var range = e.range;
var row = range.getRow();
var col = range.getColumn();
if (col >= 3 && col <= 9) { // Check if the change is within the schedule columns (C to H)
var emailUsername = sheet.getRange(row, col).getValue(); // Get the presenter's name
var Timeslot = sheet.getRange(row, 2).getValue(); // Get the time from column B
var Day = sheet.getRange(4, col).getValue(); // Get the day (string like "SUNDAY")
Logger.log("Day (string): " + Day); // Log to debug
// If Day is a string (like "MONDAY") and valid, send the email
if (emailUsername && Day) {
sendEmailWithImage(emailUsername, Timeslot, Day);
}
}
}