Google Sheets for sending automatic Birthday Wishes | Malayalam Tutorial

സുഹൃത്തുക്കൾക്കും, ബന്ധുക്കൾക്കും ഓട്ടോമാറ്റിക് ആയി ജന്മദിനാശംസകൾ അയക്കുന്ന ഗൂഗിൾ ഷീറ്റ്‌സ് ടെംപ്ലേറ്റ് എങ്ങിനെ നിർമ്മിക്കാമെന്ന് വിശദീകരിക്കുന്ന വീഡിയോ
Google Sheets Template for sending Birthday Wishes to friends and relatives. The same template with nominal changes can be used to send Warranty/AMC expiring Alerts to customers.
മൈക്രോസോഫ്റ്റ് എക്സൽ കോഴ്സിൽ എൻറോൾ ചെയ്യുന്നതിന് വേണ്ടിയുള്ള ലിങ്ക് ചുവടെ...
www.udemy.com/course/microsof...
Subscribe to the channel ‪@AjayAnandXLnCAD‬ for more.
/ ajayanandxlncad
Microsoft Word Beginner to Professional കോഴ്സിൽ എൻറോൾ ചെയ്യുന്നതിന് വേണ്ടിയുള്ള ലിങ്ക് ചുവടെ...
www.udemy.com/course/learn-mi...
Download the Apps Script code for sending Birthday Wishes
drive.google.com/drive/folder...
#malayalamtutorial #sendbirthdaywishes #appscript

[@XLnCADMalayalam]

Apps Script Code for sending Birthday Wishes
function sendBirthdayWishes() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
var today = new Date();
var todayDayMonth = today.getDate() + "-" + (today.getMonth() + 1);
for (var i = 1; i < data.length; i++) {
if (data[i][0] === '') {
continue; // Skip empty rows
}
var name = data[i][1]; // Adjust column index based on your sheet
var dobCell = data[i][2]; // Date of Birth column
var dob;
if (typeof dobCell === 'object' && dobCell instanceof Date) {
dob = dobCell.getDate() + "-" + (dobCell.getMonth() + 1); // Format DD-MM
} else if (typeof dobCell === 'string' && dobCell !== '') {
var dobParts = dobCell.split("/");
dob = dobParts[0] + "-" + dobParts[1]; // Format DD-MM
} else {
continue; // Skip rows with invalid or empty Date of Birth
}
if (dob === todayDayMonth) {
var email = data[i][3]; // Email column
var subject = "Happy Birthday " + name + "!";
var message = "Dear " + name + ",

Wishing you a very Happy Birthday! Have a fantastic day!

Best Regards,
Ajay Anand";
MailApp.sendEmail(email, subject, message);
}
}
}

[@techknowtip5835]

Can use this for weekly reminder and the message condent on custom column , take thhat and snd it

[@splendid1963]

👍superb. Thank you

[@XLnCADMalayalam]

Welcome 😊

[@rukrocks9513]

Nice

[@XLnCADMalayalam]

😀👍

[@habeebrahmankoramkulam9911]

nice

[@XLnCADMalayalam]

Thank you

[@murlydas]

Thanks it works now, pls let me know how can I color the script wish message and put a little 💙 or emoji's

[@XLnCADMalayalam]

Check my video on ChatGPT. You can paste the same code in ChatGPT and ask to modify the code for your requirements. It may take some iterations, but you will get the desired result.

[@Insafashraf]

Excel sheetil ഇത് aply ചെയ്യാൻ പറ്റുമോ

[@manojmamachan7607]

made classes for how to use COPILOT in Microsoft.

[@XLnCADMalayalam]

😀👍

[@murlydas]

Good Idea, Tried with your copied script, google sheet showing mail sent, but nothing received at other end respective mail ids. No error showing, pls respond. Thanks

[@XLnCADMalayalam]

You said that 'Mails were sent'. If there is an issue in receiving mails, the recipient should contact the email service provider. Before that make sure to check the Spam folder.

[@murlydas]

In the sheet it is showing executed, but in the respective sending mail it is not showing as sent. The receiving side was also not received, chkd in spam also

[@murlydas]

Thanks for prompt❤

[@navanitkrishnan2280]

Microsoft 365 purchase tutorial please..

[@XLnCADMalayalam]

😀👍

[@ManuMohan-us8px]

ഇതിൽ happy birthday യുടെ ഒരു പോസ്റ്ററിം കൂടി എങ്ങനെ ഉൾപെടുത്തി mail ചെയ്യാം

[@XLnCADMalayalam]

I haven't tested the following code. But I believe it will take care of your requirement. Replace "YOUR_IMAGE_URL" with the URL of your image.
function sendBirthdayWishes() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
var today = new Date();
var todayDayMonth = today.getDate() + "-" + (today.getMonth() + 1);
var imageUrl = "YOUR_IMAGE_URL"; // Replace with the URL of your image
for (var i = 1; i < data.length; i++) {
if (data[i][0] === '') {
continue; // Skip empty rows
}
var name = data[i][1]; // Adjust column index based on your sheet
var dobCell = data[i][2]; // Date of Birth column
var dob;
if (typeof dobCell === 'object' && dobCell instanceof Date) {
dob = dobCell.getDate() + "-" + (dobCell.getMonth() + 1); // Format DD-MM
} else if (typeof dobCell === 'string' && dobCell !== '') {
var dobParts = dobCell.split("/");
dob = dobParts[0] + "-" + dobParts[1]; // Format DD-MM
} else {
continue; // Skip rows with invalid or empty Date of Birth
}
if (dob === todayDayMonth || (dob === "29-2" && (todayDayMonth === "28-2" && !isLeapYear(today.getFullYear())))) {
var email = data[i][3]; // Email column
var subject = "Happy Birthday " + name + "!";
var message = `
Dear ${name},
Wishing you a very Happy Birthday! Have a fantastic day!
Best Regards,[Your Name]
`;
MailApp.sendEmail({
to: email,
subject: subject,
htmlBody: message
});
}
}
}
function isLeapYear(year) {
return ((year % 4 === 0) && (year % 100 !== 0)) || (year % 400 === 0);
}

[@ManuMohan-us8px]

@@XLnCADMalayalam sir, thanku for your reply

[@XLnCADMalayalam]

My pleasure :)

[@joypoulose9080]

Feb 29 DOB എങ്ങിനെ സെറ്റ് ചെയ്യണം.

[@habeebrahmankoramkulam9911]

@xlncad

[@vahidcrl4376]

4 വർഷം ആകുമ്പോഴല്ലേ നിങ്ങൾക്ക് ഒരു വയസ്സ് കൂടുന്നത്. അതുകൊണ്ട് പ്രശ്നമില്ല, അതുപോലെ തന്നെ കൊടുത്താൽ മതിയാകും ☺️

[@XLnCADMalayalam]

Interesting question!
Following code will take care of the people born on February 29.
function sendBirthdayWishes() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
var today = new Date();
var todayDayMonth = today.getDate() + "-" + (today.getMonth() + 1);
for (var i = 1; i < data.length; i++) {
if (data[i][0] === '') {
continue; // Skip empty rows
}
var name = data[i][1]; // Adjust column index based on your sheet
var dobCell = data[i][2]; // Date of Birth column
var dob;
if (typeof dobCell === 'object' && dobCell instanceof Date) {
dob = dobCell.getDate() + "-" + (dobCell.getMonth() + 1); // Format DD-MM
} else if (typeof dobCell === 'string' && dobCell !== '') {
var dobParts = dobCell.split("/");
dob = dobParts[0] + "-" + dobParts[1]; // Format DD-MM
} else {
continue; // Skip rows with invalid or empty Date of Birth
}
if (dob === todayDayMonth || (dob === "29-2" && (todayDayMonth === "28-2" && !isLeapYear(today.getFullYear())))) {
var email = data[i][3]; // Email column
var subject = "Happy Birthday " + name + "!";
var message = "Dear " + name + ",

Wishing you a very Happy Birthday! Have a fantastic day!

Best Regards,
[Your Name]";
MailApp.sendEmail(email, subject, message);
}
}
}
function isLeapYear(year) {
return ((year % 4 === 0) && (year % 100 !== 0)) || (year % 400 === 0);
}

[@murlydas]

tried on two mails not working

[@XLnCADMalayalam]

Use the following code and see whether it's working or not. You will get detailed logging statements which helps in troubleshooting. You can also take the help of ChatGPT or Gemini for debugging.
function sendBirthdayWishes() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
var today = new Date();
var todayDayMonth = today.getDate() + "-" + (today.getMonth() + 1);
Logger.log("Today's date: " + todayDayMonth);
for (var i = 1; i < data.length; i++) {
if (data[i][0] === '') {
continue; // Skip empty rows
}
var name = data[i][1]; // Adjust column index based on your sheet
var dobCell = data[i][2]; // Date of Birth column
var dob;
if (typeof dobCell === 'object' && dobCell instanceof Date) {
dob = dobCell.getDate() + "-" + (dobCell.getMonth() + 1); // Format DD-MM
} else if (typeof dobCell === 'string' && dobCell !== '') {
var dobParts = dobCell.split("/");
dob = dobParts[0] + "-" + dobParts[1]; // Format DD-MM
} else {
Logger.log("Invalid date format or empty Date of Birth for: " + name);
continue; // Skip rows with invalid or empty Date of Birth
}
Logger.log("Checking " + name + " with DOB: " + dob);
if (dob === todayDayMonth) {
var email = data[i][3]; // Email column
Logger.log("Sending email to: " + email);
var subject = "Happy Birthday " + name + "!";
var message = "Dear " + name + ",

Wishing you a very Happy Birthday! Have a fantastic day!

Best Regards,
[Your Name]";
MailApp.sendEmail(email, subject, message);
Logger.log("Email sent to: " + email);
}
}
}

[@murlydas]

@@XLnCADMalayalam 💚