Can you show me a screen shot of your Google Sheet, please?
Make sure the column headings are visible, and point out the column that contains the birthday date.
Yes, I have done this type of solution in AppScript for generating alert emails like âProposal is pending for more than <number> daysâ to one of my clients.
Are you combining phone numbers and email addresses into one column?
No
Okay, so itâs fairly obvious why it isnât working.
The date of birth is in column AI, and your script is looking for it in column B.
There are other issues as well.
Iâll come back a bit later with some code for you that will work.
The index column youâve selected is not quite right. Please follow the code that Darren will provide.
@Benjamin_Strickland - the below should work for you.
/*
Change the below to match the values in your Google Sheet.
Note that the column names must be an EXACT match, including any spaces
*/
const usersSheetName = 'CHANGE ME';
const emailColumnName = 'CHANGE ME TWO';
const dateOfBirthColumnName = 'CHANGE ME THREE';
const usersNameColumnName = 'CHANGE ME FOUR';
function getUsersWithBirthdayToday() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(usersSheetName);
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues().shift();
const emailIndex = headers.indexOf(emailColumnName);
const dobIndex = headers.indexOf(dateOfBirthColumnName);
const nameIndex = headers.indexOf(usersNameColumnName);
const today = (new Date().getMonth() + 1) * 100 + new Date().getDate();
const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
while (data.length > 0) {
const thisRow = data.shift();
const dob = thisRow[dobIndex];
const dobDate = (new Date(dob).getMonth() + 1) * 100 + new Date(dob).getDate();
if (typeof dobDate !== 'undefined' && dobDate === today) {
const email = thisRow[emailIndex];
const name = thisRow[nameIndex];
console.log("%s has a birthday today, sending email", name);
sendBirthdayGreeting(email, name);
}
}
}
function sendBirthdayGreeting(email, name) {
const body = `Happy Birthday, ${name}`;
const subject = 'A Birthday Greeting';
MailApp.sendEmail(email, subject, body);
}
- If you want to run it on a schedule, your trigger should be configured to call the
getUsersWithBirthdayToday()
function - Note that you must modify the constants at the top to match the Sheet and Column names in your own Google Sheet
- You can customise the email subject and body by modifying the following two lines:
const body = `Happy Birthday, ${name}`;
const subject = 'A Birthday Greeting';
Darren, I finally was able to get it to work from the original Chatgpt I used. I had to tell the script the name of my sheet, then tell it where my email and birthday columns are located. It finally ran and I was able to set a trigger for the script. I did not expect Chatgpt to also help me with correcting my errors. Thanks so much, but I will be back.
Thatâs great.
However, please be aware that the code that you got from ChatGPT isnât very robust.
It uses hard-coded column index references, which is generally a bad practice and should be avoided. For example, if you were to insert a new column somewhere in your sheet, it would almost certainly break.
My version avoids that problem.
Just for giggles, I asked ChatGPT to review my code and make suggestions for improvement. It tried to point out a number of issues that werenât present, and re-wrote my code, breaking it in the process
ChatGPT is great for helping with writing code (and I do use it), but itâs not wise to take everything it gives you as being 100% correct
I sometimes wonder about ChatGPT, which often refuses to use indexOf to refer to column names, even though itâs not like it doesnât know how. Itâs even worse when asking about CSS.
Thanks to all who gave your time to assist me. I had never done any scripting before and this is new to me. What I like about Chat is that it never gave up on me like a human would. Thatâs what I like about it because every error, mistake, or misunderstanding Chatgpt gave me a response to help or correct what I was attempting to do. I will be challenged in the future when my mind takes me there. I was just wondering about something else, however.
With your clients, how do you handle the situation with the Google Sheets and the Glide database, itself? Do you give them the Glide system, along with the Google sheet? How do you contract the work and ownership of all related pieces? Thanks
@Darren_Murphy I feel like the robot knows something about you that the rest of us donât. Maybe youâve made past mistakes and it wonât let you forget about them.
Gotta say though, a while back I had a week long conversation with ChatGPT and it helped me build an entire custom jukebox style audio player (styling included) with some unique requirements that I had, and that I integrated into a Glide app. About 3/4 of it is ChatGPT and the rest were my changes, bug fixes, and various other additions. Worked on it in my free time during the evenings. What would have probably taken me a couple of months to build from scratch, took me less than a week for the bulk of it. Whenever I need to add something major, I can just ask and it will tell me what to change and where. If I make major changes on my own, I can feed my code back into ChatGPT, tell it what I want to change, and it will tell me almost exactly where to make changes.
When it comes to coding, itâs actually quite impressive. Even started using it at work to help build code for our mainframe system.
I discovered this same thing.
I must admit, Iâve had some frustrating exchanges with ChatGPT in the past. More than once Iâve found myself going around in circles getting nowhere. I ask it a question and it gives me an answer which isnât quite what I need. So I explain why the answer it gave wonât work for me, and rephrase my original question slightly, or provide some additional context. It apologises profusely, then gives me another answer that wonât work. So I explain again, it apologises again, and regurgitates the original answer. Sometimes it just ignores everything that I say and keeps repeating variations of the same answer (whilst continually apologising).
For coding, Iâve found it most useful for help with very specific algorithms, for example âwhatâs a good way to sum a list of numbersâ, or âgive me a regular expression to match xyzâ, etc.
For a lot of other stuff⌠meh. I think Iâm just not good at asking questions the right way
Iâve had pretty good luck. A majority of what Iâve done is little javascript functions which are pretty straight forward. âI need javascript that does X. Iâm passing in two parameters A and Bâ. My requests are pretty concise and I only include details that might be relevant from a programming standpoint. Whenever it gets something wrong, I donât re-ask my question. I just say âthis part of the function doesnât workâ, or âthis part should so something differentlyâ. Otherwise, it gives me a pretty good base to modify as needed.
I did not mean to create this type of conversation but you must remember, Chatgpt is not a human, and even sometimes humans donât respond in a manner we would like or prefer. With this type of technology, I try to keep it basic, as opposed to carrying on a conversation.
Itâs all good
One of the great things about the Glide Community is that we all get to learn from the open exchange of experiences and ideas