Google Sheets: Send Email When Condition Met

[@AH-zy2df]

Hello Spencer. Let me start off by saying you are a genius when it comes to script. Thank you so much for working directly with me and creating a script that was customized to our specific needs. We are truly grateful for you to take time out of your day to help a team of strangers with our script. Plus, you took the time to explain the logic behind it. Every single person I know that works in google sheets will hear about you and how helpful both your videos are as well as you personally. Thank you for all your help.

[@SpencerFarris]

Thank you so much!

[@PSiMetronome]

I'm super unfamiliar with google sheet scripting but you made everything simple to understand! I was able to repurpose this for use on a schedule I need for a project. Thanks so much!!!

[@SpencerFarris]

So glad it helped!

[@---ml4jd]

AMAZING. been using google sheets for about 5 years and never knew this amazing stuff

[@SpencerFarris]

Glad to help!

[@jettlandon6283]

You prolly dont give a shit but if you guys are bored like me atm you can watch all the new movies on InstaFlixxer. I've been binge watching with my brother recently :)

[@marcelobodhi1692]

@Jett Landon yea, I have been using Instaflixxer for months myself :D

[@mtwerder]

Spencer, what method have you found to be the most successful to send an email based on a change or value to an individual based on a certain condition. For example, if the order status turns APPROVED for a specific REGION, it send an email to that specific REGION MANAGER?

[@SpencerFarris]

The method in this video is what I would used, based on your description

[@framecountlaw1227]

My form was integrated with google sheets. In my google sheets I've 5 vendors to notify when they is a form submission. I wanted the trigger in a cell to be a text value. Different text values trigger different vendors' email. For example, text 1 will send to vendor1 and text 2 will send to vendor2 and so on... I just don't want each vendor to know what other vendor submission. Any Help PLEASE

[@radhikasingh7477]

i must pass on my appreciation to the one of the most humble and genuinely helping guy spencer, who has spent lot of his time in helping me out when i wasnt able to execute the code shown the vedio.. You are Gem Spencer.. thanks for making my code work which was shared in the vedio and proving me that you are a strong techie Guy indeed..Best wishes. Looking forward for more such videos

[@iagolinscavalcanti2507]

Thanks! Thats what I needed. Buf one question. In my case, I already have a sheet with the inventory of products from my store. I want to receive an email whenever an item is over in my inventory. As I already have hundreads of items missing, will I receive hundreads of emails for all of those missing products after implementing the script? Because I don't need most of those items missing in my inventory. I would like to receive an email only when there is a change in the quantity and it turns out to zero , not when this is already zero. Do you understand?

[@SpencerFarris]

This only sends when a change occurs

[@iagolinscavalcanti2507]

@@SpencerFarris That's what I thought. Thank you very much

[@massyunited6923]

Good Day Spencer I really need your help. I am using a Separate sheet for the data entry with assigned buttons that transfers information to required the cells on the data sheet. I set up the trigger however, it only works when change the data directly on the Data sheet and not through the data entry page. Can you advise on what to do? I was thinking maybe the trigger may be running too early? would there be a way for the email trigger script to run after the data is Transferred into the data sheet?

[@SpencerFarris]

So do you have a single trigger setup to move-and-email? Does an email send, but without the right data, or does it not send at all?

[@RobisyPL]

Hello Spencer! Can I ask you for this script only in OnChange version? If you find time to modify it appropriately, of course. Regards

[@SpencerFarris]

Have you tried the modification? You need to store the current status in Properties then check against it whenever a change occurs. Have you watched my video about On Change?

[@moadbouanani5813]

Thank you, question please : Let s say that cell with "approved" is a formula based on other cell (date for example). what it the code for this case? Thank you

[@SpencerFarris]

You would have to use onChange instead kzbin.info/www/bejne/inmWhmtua559sMk

[@khnaz35]

Hi Spencer, got a question in this video, you are sending email to yourself but what if i have list (let say 100 people) how to send them in one shot.

[@Raviklama]

Thank yo so much for this great video Spencer. Just a quick note; I was having some issues while running this code, however when I replaced "var" in place of "let", it worked perfectly. Not sure why this is the case, but it solved my issue. Thanks again.

[@SpencerFarris]

Weeeeird

[@willcasey4423]

Thank you very much Spencer! is it possible to also add charts generated from the data in the email as well, or even generic images? any pointers in the right direction would be amazing!

[@resiliencyteam4021]

I have a question: I have an IF statement to check if someone left a message on a form submission, if so then i want it to send an email to a specific address based on a response. So Person A submits a form it logs the data in an excel file. On that form based on certain selection they make, this generated email will go to variable Person B. I wrote another IF statement with several nestled in there to determine that particular email address). But the email is only sent if a message is left in the form. Using your example, I got it all to work until i added Formulas... can you assist me? My Code is as follows: function sendMailEdit(e){ if (e.range.columnStart != 6 || e.value !="Need to Send Mail") return; const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,4).getValues(); let service = rData[0][3]; let d = new Date(rData[0][1]).toLocaleDateString("en-US"); let services = rData[0][4]; let loc = rData[0][2]; let now = new Date().toLocaleString("en-US"); let mail = rData[0][0]; let msg = "Attention, " + loc + " Has left a message on " + d + " for the service of " + service + " The Message reads: " + services + " The time stamp for this submission is ("+ now +")"; Logger.log(msg); GmailApp.sendEmail(mail, "Message Left for " + service, msg) }

[@designerdeck4042]

Hi Spencer, I'm far from a coder but the more videos I watch I think this particular tutorial you've shared may be what I'm looking to achieve with my spreadsheet. As the admin, I'm looking to send off an automatic email to specified users when a change or edit takes place on my sheet. I'm coded this successfully and it works. The problem I run into, is it does it for every cell when I make a change. while I have the doc open, I made need to make a change to multiple cells but I'm essentially only looking to fire off one email notifying others a change/edit has been made. Is this what you are referring to in this video? I also want to give other users, the ability to interact with my sheet and make changes only to the ranges/cells I've given permission to edit. Can you advise if this is what you are talking about here? Would it possible for me to share my sheet with you to have a look and get some feedback?

[@SpencerFarris]

[e-mail]

[@arnellcarmichael635]

Thanks a bunch! Not sure how you don't have thousands of subs right now.......

[@SpencerFarris]

Haha, thank you!

[@arnellcarmichael635]

@@SpencerFarris I just discovered Google App Script a few days ago, and understanding what you're teaching is what I'm looking to get proficient in for the year! I signed up for code academy yesterday, but this stuff looks Chinese (lol). I'm willing to struggle through it, but do you have any recommendations on learning the logic of what you're sharing with with world! P.s. finding this content was like getting an early birthday present (lol). Thanks again for what you do!

[@SpencerFarris]

@@arnellcarmichael635 Any Javascript couse, since GAS is a subset of JS. Ben Collins courses are excellent for GAS, and I also do 1-on-1 training when needed.

[@arnellcarmichael635]

@@SpencerFarris awesome! I appreciate that bit of information! What's the best way to connect with you?

[@SpencerFarris]

@@arnellcarmichael635 [e-mail]

[@magdalenelawson]

Hi Spencer if there are duplicate orders for example can I use code to identify them and send as one email as opposed to several emails. I am using a checkbox trigger by the way.

[@SpencerFarris]

Yes, but that will depend on your exact setup. Can you add me as an editor? [e-mail]

[@anuarsyzdykov2021]

Hi, Spenser! I am very appreciative of your content. Good job. I have a question though. Is it possible to do it excel online?

[@SpencerFarris]

I don't use or have Excel. However, I did need to use Excel for my previous job (2 years ago) and found that Excel online is probably the most lackluster spreadsheet product when it comes to scripting features.

[@krishnapurkar]

Hi Spencer, Many thanks for super informative videos. Is there any way to trigger email for expiry of date like passport in advance say 15 days after scanning entire sheet? Thanks in advance.

[@SpencerFarris]

Yes. You need to use new Date() to get today's date and loop through (or filter, if you want to try that method) all the dates and compare them.

[@larcenciel1547]

Hello, thank you for the video. I just have one question: How come you are getting the email yet you have put "return" just after the if statement. Doesn't that mean after return, the code below is not ran. Because what is happening in my case. Sorry I am still new to app script.

[@SpencerFarris]

That 'return' is meant to stop the script as early as possible if it's the wrong type of edit. So in this case it's saying "if I edited a column other than E or if the value entered wasn't 'APPROVED' stop running the script." But the edit was an APPROVED value in E, so it runs.

[@emair8385]

Your videos are some of the best I've ever seen for explaining stuff like this. I'm very very new at coding and I'm trying to have a different email sent depending on which column the value appears in. Is this doable? I'm having a hell of a time with it.

[@SpencerFarris]

Absolutely! Here's a modification that assumes the edited column needs to be between D and F (4-6). There are better ways to do this with either a SWITCH statement or 2 arrays, but this way is a really clear modification of the script I used in the video. function sendMailEdit(e){ if (e.range.columnStart < 4 || e.range.columnStart > 6 || e.value != "Approved") return; const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,4).getValues(); let n = rData[0][2]; let d = new Date(rData[0][0]).toLocaleDateString("en-US"); let items = rData[0][3]; let loc = rData[0][1]; let now = new Date().toLocaleString("en-US"); let msg; if (e.range.columnStart == 4) msg = "Message 1"; else if (e.range.columnStart == 5) msg = "Message 2"; else if (e.range.columnStart == 6) msg == "Message 3"; Logger.log(msg); GmailApp.sendEmail("receipient_email_address", "Approved Order", msg) }

[@emair8385]

@@SpencerFarris Wow you're just the best. Got this to work.

[@hfalcon77]

Spencer, can this similar thing be done without the edit or a trigger? I want to send an email every day at 6:30 AM if one of two conditions is true, and not send it if they're both true. Thanks

[@SpencerFarris]

Sure, you can just make a time trigger and, at the beginning, check those conditions before continuing.

[@HinaJoshi-n5i]

Hello Spencer, Thank you so much. Can you please help with this? I have a column that included recipients' emails and each row has unique info. I want to send mail to that recipient if status is "Approved". Appreciate your help.

[@kateburnier9299]

hey Spencer!! GREAT video!! wondering if there's a way to set this trigger based on a DATE in a certain column? i.e. when the due date is 7 days from now, the email will be triggered. I've got quite a bit of conditional formatting setup using "=now()+7" to highlight rows that are closing in on their due date, but would love a function that sends an email with the data from that row, when the due date for the row is 7 days out. Any idea if that's possible? I've been digging around on youtube and your video is the closest I've gotten to a possible solution!

[@SpencerFarris]

Yes, look at my onChange video

[@egreen-simstrucking]

Can you show us how to send an email to whatever email is in the corresponding box? So say I have a google form that submits data to its corresponding spreadsheet. When I mark an item as "approved" it will send a notification of "approved" to the email in the cell for that submission? Not just to one person. This would be meant as a response email for approval from a submitted request via Google Forms. Hope that makes sense...

[@435Sander]

Thank you for a great guide. How did you make that dark theme on the script editor?

[@SpencerFarris]

I'm using the AppsScript Color Chrome extension chrome.google.com/webstore/detail/appsscript-color/ciggahcpieccaejjdpkllokejakhkome

[@taylorpearson299]

Hi spencer. I am looking to do a similar sheet that will send a message of approved or denied based on the conditional formatting I have setup within my sheet. i also need it to send to the email address of the employee who put in the request. Can you help me with this?

[@kevinleary3410]

Hi Spencer. Great video and very clear. When i input data manually it works perfectly however is there a way for it to accept data from arrayformula or imported ranges. I wish to automate the process and have certification expiry dates set to present in a separate column as green tick box and red x as appropriate,have added additional column with IF function to change icons to text however it is not accepting this. Any advice would be much appreciated.

[@SpencerFarris]

What do you mean "accept data from arrayformula or imported ranges?" The script only runs when a user changes a value, but it can pull data from anywhere.

[@kevinleary3410]

@@SpencerFarris I have it set with some formula's to auto input text if expired dates. So no direct value change. Is there a way for this to be identified? To be clear one formula is =IF(C22G21,"Valid") G21 =TODAY() C Column being a list of dates. Currently it is not recognizing Expired if auto inputted,however it is if i manually input it. Hope that makes since.

[@SpencerFarris]

​@@kevinleary3410 Right, so that's explicitly what doesn't work. There are 2 routes you can take: If there is a manual edit further down the line (like you edit G which modifies F which modifies P) then you can watch for an edit on G. Otherwise, if it just changes automatically each day with the =TODAY() formula, you can write it as a time-based trigger that runs everyday and performs the correct function for each day.

[@Youkaizim]

Great video. Thank you! One question though, if there was another column first with the same text, "approved" in this example, what should I do so the email would be triggered only by the second column and not also by the first one?

[@SpencerFarris]

It looks like your question got cut off

[@Youkaizim]

In this case i'm trying do adapt the script so it gets triggered when a low number is achiveded. However it is getting triggered also by another columm before. How do I specify only one columm to activate the script?

[@SpencerFarris]

@@Youkaizim If I understand correctly you have a setup like there is a formula in column F looking at column E and if you edit column E such that F drops below a certain number you want it to trigger? The way I have gone about that is to still trigger the SCRIPT based on column E, then check column F for the value.

[@christophpohl8479]

Hi Spencer, thank you for the amazingly useful video. Simple question for a noob: To make this function fully automatically, I use an IF condition in my data range on the spreadsheet. It shows "Approved" if the condition is met. Unfortunately nothing happens, only when I hardcode "Approved" manually in the cell (like you do in the video) the email is sent. So I think my problem is here (second line of your script): e.value != "Approved". This has to be the exact value of the cell, not a formula, right? Any idea? Thank you!

[@SpencerFarris]

An onEdit script *only* triggers when a MANUAL edit is made. The best move usually is to tie the onEdit function to wherever the edit IS made that eventually changes the cell to "Approved"

[@christophpohl8479]

@@SpencerFarris Thank you for your reply! I converted the onEdit function to an executable function and it works! :)

[@periwinkook3037]

@@christophpohl8479 hi, I'm experiencing the same obstacle as you, do you mind sharing what you did to finally get it work? Thank you so much in advance!

[@vahagnvardanyan6729]

@@SpencerFarris hi, I am having the exact same obstacle, where should I tie the onEdit function? Can you please help? Thanks in advance

[@SpencerFarris]

@@vahagnvardanyan6729 I don't understand the question

[@keepsakesolutions7959]

Hi Spencer, I'm using your script as a way to notify my employer when inventory on a certain item goes below a certain number. I've been able to get the script to work if it is an exact number, but is there a way to have it send an email whenever it falls below the number? Ex. when the cell has any number between 0 and 20 I want an email sent. Thank you!

[@SpencerFarris]

e.value < 21

[@oscarl.3934]

Hey Spencer! Do you think this is doable on Apps Script nowadays? I tried to get the code from Bard but it resulted on a waste of my time since all codes provided resulted in errors. If you can do the same trick you did in this video in Apps Script, can you do a little tutorial on that? Thanks buddy!

[@SpencerFarris]

I'm confused what you're asking. This video is the tutorial, so what do you want?

[@adopsguy]

I have special case where, I am checking status of url using HTTPResponse, Instead of approved, I want to trigger the Email edit if HTTPResponse is 404 for rest no email trigger should be fired. Since value are dynamically calculated, OnEdit trigger might not work, How can write custom trigger which will fire if status of url changed from 200 to 404.

[@SpencerFarris]

Can you email me and we'll work on it? [e-mail]

[@liamnichols8963]

Great script. to change date format in automated email would we just change from en-US to en-GB?

[@SpencerFarris]

Yes!

[@dansaleemdo7893]

Thanks so much. How can I trigger it if I have a column that included recipients' emails? Pretending I have a column of emails and each row has unique info for that person that I want to trigger the info for to send it to him.

[@garihamcao4436]

Hi Spencer, I just started to use Google app script and this must be a silly question - I want to run this function in a specific tab as there are multiple tabs in the spreadsheet. How should I modify the code to realise this? I’m still trying to better understand row 2 and 3 in your coding. But anyway this video is very helpful! Thank you.

[@SpencerFarris]

use src.getName() != "sheet name" in the IF statement to return out

[@Jan_ggv]

​@@SpencerFarris Hey Spencer thanks for that awesome Video! I've tried this solution but get an error.: ReferenceError: src is not defined sendMailEdit @ Code.gs:2 Code: function sendMailEdit(e){ if (src.getName() != "E-Mail" || e.range.columnStart != 4 || e.value != "Bereit") return; const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,3).getValues(); let sku = rData[0][0]; let match = rData[0][1]; let movestock = rData[0][2]; let now = new Date().toLocaleString("de-DE"); let msg = "SKU: " + sku + " Matchcode: " + match + " -> " + movestock + " | festgestellt am " + now; Logger.log(msg); Completly new to Apps Scripts.. sorry if i miss something obvious..

[@clashoflions7339]

Hello Spenser, I m curious, what do you do if we are to triger emails for 2 different conditions met. Let say that once you put Approved an email is generated with a specific Subject is generated but if you put Open another email is generated with a different subject. I am currently able to use what you mentioned in the video but once I copy paste the script in a second code but just change the conditions' and information includet in the email. The second code goes to effect but the first one doesn't work anymore.

[@SpencerFarris]

You'll need it as a single script with multiple conditions. So you'll have something like: if (e.value == "Approved"){ // code to send email } else if (e.value == "Open"){ // code to send email } Does that makes sense?

[@clashoflions7339]

@@SpencerFarris yeap tried that but the first code went down. So only the second/latest conditions triger an email.

[@SpencerFarris]

@@clashoflions7339 So you have a single onEdit() script with multiple conditions, but only the last condition is working?

[@clashoflions7339]

@@SpencerFarris Spencer thank you for the resolution. I received your response and it is working perfectly now.

[@jakebramwell5218]

@@SpencerFarris When I do this I get "syntax error: unexpected token in like 12: else if". Any idea what I am doing wrong?

[@manuelmedels2624]

Hei i'm trying to do this now but it doesn't seem to be recognizing .range or .source. Am I missing a library or something (i haven't added any)

[@SpencerFarris]

No, no library or anything extra needed

[@fuatadami]

Hi, a very useful topic and very clear telling, thanks much! Nevertheless, I can not receive an email due to "TypeError: Cannot read property 'range' of undefined at sendMailEdit(Code:2:9)". Do you have any idea why?

[@SpencerFarris]

Hi there! This function is not designed to be run manually, but runs automatically whenever you make an edit. That creates and passes the 'e' variable necessary.

[@fuatadami]

@@SpencerFarris Hi again, I receive the error code after making an edit at 5th column in the sheet.

[@SpencerFarris]

@@fuatadami With that, I'd have to see the actual sheet/code to see why it's failing. [e-mail]

[@hfalcon77]

@@SpencerFarris i’ll also run some test but I’m having the same issue. Looking to have this auto email a few folks even outside of gmail, is that possible?

[@hfalcon77]

@@SpencerFarris it is working amazing! Just need to see if it can send out.

[@orkayen]

Hello Spencer, I greatly appreciate your valuable comments thru this video on the Apps script and sending email from GS. I am working on a similar apps script but with "onSelectionChange" trigger. I need this script to trigger only in B2:D5 range. What would be the code ( line 2 and 3) for this trigger?

[@SpencerFarris]

function onSelectionChange(e){ const r = e.range; if (r.rowStart < 2 || r.rowStart > 5 || r.columnStart < 2 || r.columnStart > 5) return ... ... ... }

[@orkayen]

@@SpencerFarris Very cool. Works like a charm. Greatly appreciated. What are those two vertical lines after the numbers? does it represent "and"?

[@SpencerFarris]

@@orkayen || is OR, && is AND

[@orkayen]

@@SpencerFarris Thanks, Spencer. Can I contact you thru email for google sheet/apps script related questions?

[@SpencerFarris]

@@orkayen yes

[@nikolasscevko6874]

Hello Spencer, Thank you for the video it was really helpful. I was wondering, however if you could help me as I struggle to see email being triggered when the Approved message is not directly typed into the cell (like demonstrated on the video) but instead is part of conditional statement within that cell and only appear based on a value after specific calculations related to that row dataset. Let's say column F in Sheet A have conditional that string "Approve" only appear based on multiple calculations from sheet B. Then, I need email to be sent once Approve is trigger via conditional in that cell. How should I go about this? FYI - I tried switching trigger type from On Edit to On Change however in that case I experienced TypeError with columnStart.

[@SpencerFarris]

In those cases I prefer to tie the onEdit event to where the manual edit would be made then check the cell where Approved will appear. Otherwise you should follow this other video to trigger based onChange kzbin.info/www/bejne/inmWhmtua559sMk

[@josephpapino]

@@SpencerFarris Makes sense. Would you be able to show us how to modify the first two lines of the demo script to check for a different cell changing, but then still also checking the value in column e is approved? Thank you!

[@SpencerFarris]

@@josephpapino Give it a try yourself and add me as an editor if you can't get it.

[@josephpapino]

@@SpencerFarris wow thanks! Yes I’ve been trying all day. I will make you an editor now. What is your email tho?

[@SpencerFarris]

@@josephpapino [e-mail]

[@36_chambers39]

Hi! Thanks a million for posting this video Just a question: What if I want an email when something isn't a specific value?

[@SpencerFarris]

What do you mean?

[@36_chambers39]

@Spencer Farris sorry I was quite vague, I wanted to get an email whenever a cell is updated with something other than what I need so if it says McDonald's or anything else instead of Wendy's that's when I would get the email

[@SpencerFarris]

@@36_chambers39 In that case change the initial IF statement to: if (e.range.columnStart != column_number || e.value -= "Wendy's") return;

[@36_chambers39]

@Spencer Farris you rock thanks so much!

[@tarafrancini5680]

Hi Spencer. How can I make this function reference my worksheet? (as opposed to the sample you provided). How do I declare that? Thanks!

[@SpencerFarris]

The script is bound to whatever sheet you make it on.

[@maddmarddigann]

Thanks for the vid. It helped a lot. Small tweak. How would I change the e.value != "Approved" to any value greater than 0?

[@SpencerFarris]

e.value > 0

[@maddmarddigann]

@@SpencerFarris thanks. i did try that but it wouldnt work. ended up putting !=null and worked. thanks again for the vid. was a big help.

[@SpencerFarris]

@@maddmarddigann Interesting, I've used that before myself. Glad you got it working, though! Oh - I just realized I probably wrote that backwards. If you want it to run whenever the value is greater than 0 you would use e.value

[@karim1996k]

Hi Spencer, Thank you for this amazing video. I have a question Please, how to trigger if the change or the condition is met automatically by formula not manually ?

[@SpencerFarris]

Hi Karim, So there are 2 methods. 1 - (Usually preferred) continue to use onEdit, but set to whatever cell will be edited to perform the change. That is, if editing something in C causes a formula to update in F, use an onEdit on C and check F for condition; 2 - Use an onChange trigger "hacked" as an onEdit. I made a video here kzbin.info/www/bejne/inmWhmtua559sMk

[@karim1996k]

@@SpencerFarris Hi Spencer, I would like to give you my case which is the following : I have 2 columns : First column : contain fixed date. Second column : contain formula that is if (fixed date - today() )=180 then an email should be sent automatically such as you video. So, I didn't have possibility to edit cells of dates because the dates are fixed in advance, each product has a date of reception fixed. What do you think? Thank you so much Spencer!

[@SpencerFarris]

@@karim1996k Oh! Then I think you'll be best off just setting up a time trigger for once a day and if the date is correct, send it.

[@karim1996k]

@@SpencerFarris so how can I combine this script of sending email when condition met ( date-today = 180) with this time trigger. Can you help me please? And for example if in the first day the script trigger the condition and send email. If in the second day trigger the condition in other cell. It will send mail of the second day condition met ot both first and second day?

[@SpencerFarris]

@@karim1996k Can you please email me the Sheet? [e-mail]

[@TheAmby200200]

Hi Spencer, I'm working with different barcodes. Is there a way make a condition where the email sends it the cells contains a specific text string? For example, send an email if the first 5 characters in the cell are "abcde".

[@SpencerFarris]

I'd have to see exactly what Sheets interprets the barcode insertion as. Can you add me as an editor? [e-mail]

[@bryangood9326]

Spencer, this is a great help! I had it work in one sheet but in the other I keep getting the following: Syntax error: Missing ; before statement. line: 7 file: Untitled Any suggestions? Bryan

[@andrediedericks7767]

Hey Spencer. So this code pretty much does exactly what I wanted. BUT the code is written to send an email "on edit", but in my situation I have a sheet that automatically updates the "Approved" cell when a certain item expires. This automatic update is not seen as an "edit" and the email is never sent. Do you have a way of the email being sent on change, instead of on edit ? Thanks !

[@SpencerFarris]

What makes it expire? You can either trace back to whatever manual edit causes that (if there is one), use my other video about onChange to work it that way, or just have it run daily/bi-weekly/weekly/whatever and do that for each valid row.

[@andrediedericks7767]

@@SpencerFarris I'm keeping track of employees training cards, different cards expire yearly others every 2-5years. I set the sheet up to look at todays date and compare it to the expiration date of a card, when the card expires it automatically updated the "expired" cell. I want to be able to drive the email from that expiration.

[@SpencerFarris]

@@andrediedericks7767 Combine it with my onChange video kzbin.info/www/bejne/inmWhmtua559sMk

[@silvestrecamposano6317]

Thank you Sir!....

[@rameshgopalakrishnan5595]

Great Video... One problem though tried to insert multiple conditions using the logic which you had given in comment " Approved " and "Active" but it seems to not work. It starts sending email for each and every status change instead of only Approved and Active. Could you pls suggest a different syntax....TIA

[@rameshgopalakrishnan5595]

With only one condition its working perfectly...👍👍👍

[@perceptualexperience]

Hi Spencer, I've got this working on the first tab of my sheet, but it doesn't work on the other tabs. Is there an easy way for this script to apply to all tabs? Thank you!

[@SpencerFarris]

It only doesn't run on a tab if you've told it not to.

[@sara.guzman920]

Hi Spencer, Thank you so much for this video it was so extremely helpful! I am wanting to know how to send the email and cc other emails when the condition is changed to approve. For ex, I would want it to email myself and cc the CEO. Where would I add the CC function?

[@SpencerFarris]

In the {options} section developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)

[@magdalenelawson]

Hi Spencer, how do I create a syntax to pick/reference more than 1 column for the body of email Please?

[@SpencerFarris]

This script already does that, unless I misunderstood the ask. The message is built from columns 1-4.

[@brianswp]

Hi Spencer, Thank you for the video. I keep getting an error saying that everything is undefined. The only change I've made to the script is adding e.source.GetActiveSheet().getName != 'Sheet name' || e.range.columnStart etc... since I want to specify a specific Sheet. Do you have any idea why I'm getting this issue?

[@SpencerFarris]

Don't run the script. It runs automatically whenever you make an edit and requires the information passed by the edit in order to run.

[@HarpreetSingh-sm8vr]

what is this e stand for and why we have to use that. Any idea.what is this e.range states for ?

[@SpencerFarris]

'e' is all the data that is passed to the function by the edit trigger. It contains the source, range, value, and other information.

[@fralb7465]

Hello, even if I am not familiar with coding, it was easy with this video, so thank you for that. I have one problem. In my gsheet I want to send an email everytime '6' days are missing from the deadline. So your "status" culumn is my "missing days" culumn. Inside this culumn I put a formula (deadline - today), so it is modified automatically everyday. But This is not seen as an edit by the script. But if I edit manually putting '6 'it works. Is there a solution? thank you!

[@SpencerFarris]

onEdit only works when a manual edit is performed, as per its documentation. You can either use an onChange and I have another video for that or a time trigger and loop through all the rows each morning.

[@fionamoonan9623]

Hi - great tutorial - Thx. However, is there a way to send an email using Outlook rather than gmail when using googlesheets? Trying to convert a VBA script to one that will work on googlesheets using officescript - only small script but taking me forever 🙂

[@SpencerFarris]

You would need to find an Outlook API and use URL Fetch to access and utilize that API

[@farkhadhuseinov5246]

hello. thanks to share like this information. but I need to send an email to multiple receipents as to and cc, how can do that.

[@SpencerFarris]

The fullest sendEmail() method takes recipient, subject, body, and options. Recipient takes a comma-separated string of emails "[e-mail], [e-mail]" and options allows you to use {cc: "[e-mail],[e-mail]"}

[@farkhadhuseinov5246]

@@SpencerFarris hello again bro! do you know, how can I create any whatsapp group and send group email based on date in google sheet with formula or script. have any plan share youtube video on this topic. TA

[@SpencerFarris]

@@farkhadhuseinov5246 I do not. I haven't worked with APIs such as WhatsApp, Discord , or Slack

[@hellcat247]

If you have more than one sheet in a google sheets workbook, will this script only apply the sheet youre on when you start working on it?

[@SpencerFarris]

This script works always, on all sheets. If you want to restrict the sheets it runs to a single one, you can change the "if(...) return" statement to include the sheet name: if (SpreadsheetApp.getActive().getActiveSheet().getName() != "sheet_name_here" || e.range.columnStart != 5 || e.value != "Approved") return;

[@soumyadiptaroy13]

Hey thanks Spencer....Im getting an error -- Error TypeError: Cannot read property 'range' of undefined sendMailEdit @ Code 2.gs:2

[@fondobursatil8099]

same here

[@SpencerFarris]

@@fondobursatil8099 Don't run the script, it will fail. This script is designed to only run when triggered by an edit.

[@princessgosh]

I am just stumped! I'm trying to script: if 'Dates!B2' shows "expired" i want an email but I do not want an email if it is blank

[@SpencerFarris]

What have you tried? Is B2 set by formula or manually?

[@zeza2217]

Using your example, which is my favorite, how do I make parts of the email body message change to bold, underline, font size, color, etc?

[@SpencerFarris]

Use the advanced Options parameter developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object) to create an htmlbody

[@zeza2217]

@@SpencerFarris Thanks. I guess that's the only way. I was hopping that it could be as simply as adding inline to what you already gave us. I was already suspecting that it was not possible. Thanks again.

[@SpencerFarris]

@@zeza2217 If you know how to write html it's pretty simple

[@closse4]

Hi Spencer, I have a question for you, I've tried to find this answer over all the web but wasn't able, please help me out on this: I have used your code and everything is working well, but my issue, is I want to send an email notification as soon as a new row is added to the sheet. I got it working for modification but I would need it to be sent as soon as a raw is added. Please let me know if you can help. Thanks

[@SpencerFarris]

What constitutes "a new row is added" in your case? Are rows coming in from a Form? Is it just manual data but always in the same order? Is there a "complete" column to be filled out as the last column for the row?

[@rodneywhite315]

I am having this issue as well. Any luck?

[@SpencerFarris]

@@rodneywhite315 What issue, exactly? As I assked above, what constitutes a "new row added?"

[@venkatesanb2242]

Hi i need to open URL or mp3 file when conditions met. Can you please help me on code

[@RajRoldanTV]

Please help, I don't have the option "Google Spreadsheet" from Select Event Source (adding trigger).

[@RajRoldanTV]

Please reply Mr Spencer.

[@SpencerFarris]

I just checked again to make sure it's not an IDE issue. From the spreadsheet I went to Extensions > Apps script > Triggers > New Trigger. The pre-selected option in "Select event source" is "From spreadsheet." What are you seeing?

[@dncoe]

Hello Spencer, I am very appreciative of your content, you are amazing! I have a question, I am looking to make a script JUST like the one you made, that does the exact same thing, but triggers based off a form submit trigger. I have a form connected to a sheet, right now, I can only get this script to work from an On edit trigger event type. Any ideas? Thank you!!! Here is my script, works perfect when I manually edit a cell

[@SpencerFarris]

No script came through, but please add me as an editor on the sheet so I may best assist.

[@elsonguila9555]

​@@SpencerFarris, Thank you so much for sharing the content. I have the same question as @Michael, I wonder if it possible to make it work based on the IF condition or formula (on the column E)?

[@SpencerFarris]

@@elsonguila9555 That's actually a very different question. Michael's just needs to be setup with an onFormSubmit trigger rather than onEdit. You need to use onChange and check every change against a range: kzbin.info/www/bejne/inmWhmtua559sMk

[@kristijan.akmacic]

Great tutorial. Adapted the entire script to my needs. How do you make something go in a new row in an email? I want part of the "msg" to go in a new row for better legibility. Thanks

[@SpencerFarris]

You'll include a line ss.getRange(range).setValue(msg)

[@kristijan.akmacic]

@@SpencerFarris if I write just that in a new line, i get the following error: ReferenceError: ss is not defined at sendMailEdit (Code:6:3)

[@SpencerFarris]

@@kristijan.akmacic So you'll need to define the sheet. So ss = SpreadsheetApp.getActive().getSheetByName("sheet name here");

[@deepakrao2867]

hey there! Thanks for the video. I was able to set up the trigger and the ran the script successfully. However, I haven't received an email nor am I able to see the details of the execution. Your help with this will be much appreciated!

[@SpencerFarris]

Can you make me an editor on the sheet?

[@dunaadventure6261]

Hi Spencer, Is there a way to read the email from a cell in the current row instead of being hardcoded? Thanks!

[@SpencerFarris]

Absolutely! That would just be another rData[0] reference so long as the .getRange() is large enough.

[@dunaadventure6261]

@@SpencerFarris is it possible for you to show how? don't take me wrong, but for me is kind tricky

[@SpencerFarris]

@@dunaadventure6261assuming the checkbox in F and the email in E: function sendMailEdit(e){ if (e.range.columnStart != 6 || e.value != "Approved") return; const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,4).getValues(); let n = rData[0][2]; let d = new Date(rData[0][0]).toLocaleDateString("en-US"); let items = rData[0][3]; let loc = rData[0][1]; let email = rData[0][5]; let now = new Date().toLocaleString("en-US"); let msg = "Order number " + n + " (" + d + ") containing " + items + " items from " + loc + " Approved at " + now; Logger.log(msg); GmailApp.sendEmail(email, "Approved Order", msg) }

[@thebigbigdaddy]

Is there a way to trigger via daily cron, like check the page every 24 hours?

[@SpencerFarris]

Yes, you can make a time trigger from the Triggers page

[@StacyBragaw-b4x]

I am trying to adapt this script to be used for a vacation request. I want the requestor to be emailed when their request has been approved or denied. I have tried to adjust the code, but I keep getting an error. Could I add you as a collaborator so you can see what I am doing wrong?

[@SpencerFarris]

Sure: [e-mail]

[@StacyBragaw-b4x]

@@SpencerFarris 1 more question/scenario came up. I would like my assistant to update our Out of Office Calendar when an event is approved. Is there a way to send an additional email to her so she knows that it has been approved?

[@SpencerFarris]

@@StacyBragaw-b4x Just add another call to GmailApp at the end.

[@brennanmason9274]

Hi guys, does anyone know if there's a way to modify this script so that it will work if a cell value is updated to say "Approved" based on an existing IF statement in that cell? The current script only seems to be working for me if I manually go in an update the cell to say Approved (as opposed to working with my IF statement).

[@SpencerFarris]

onEdit scripts only trigger on manual edit. I have another video on using onChange kzbin.info/www/bejne/inmWhmtua559sMk

[@yohancerodriguez6600]

Thank you for the video! If I have to send from one sheet with multiple tabs, how do I specific the sending sheet in the code? And can I place a sent timestamp in the sheet? Please?

[@SpencerFarris]

To specify a sheet, I normally add to the first IF() statement "e.source.getActiveSheet().getName() != 'sheet name here'"

[@SpencerFarris]

Here's a video about adding timestamps on edit. You should be able to adapt both scripts together kzbin.info/www/bejne/epjUhZmqmb2eitk

[@yohancerodriguez6600]

@@SpencerFarris Wow! Thanks for replying. Big up from Jamaica!

[@emmytheaward]

How do you make this work for only one sheet, currently it's working for two of mine but I really just need it for one

[@SpencerFarris]

In the top IF statement add a sheet name check: function sendMailEdit(e){ if (e.source.getActiveSheet().getName() != "Sheet Name" || e.range.columnStart != 5 || e.value != "Approved") return; const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,4).getValues(); let n = rData[0][2]; let d = new Date(rData[0][0]).toLocaleDateString("en-US"); let items = rData[0][3]; let loc = rData[0][1]; let now = new Date().toLocaleString("en-US"); let msg = "Order number " + n + " (" + d + ") containing " + items + " items from " + loc + " Approved at " + now; Logger.log(msg); GmailApp.sendEmail("receipient_email_address", "Approved Order", msg) }

[@rodneywhite315]

I am using google forms to auto populate my google sheets. Once each entry is submitted in google forms, it creates a new row with the inputs from the form. Now, using your code, it only worked while inputting the word "approved", and pressing enter on the keyboard within the sheet. Is there a way I can get an email notification if I receive say "634" in column B in the sheet as soon as it auto populates the google sheet. Any help on this would be very much appreciated. Thank you!!!

[@rodneywhite315]

In other words if that does not make sense. I just need a instant notification that a specific number or phrase is input by google forms into a google sheets column.

[@SpencerFarris]

@@rodneywhite315 You want to use a trigger when a form is submitted, rather than a simple onEdit() trigger

[@rodneywhite315]

​@@SpencerFarris I am not concerned specifically to when the form is submitted. I am concerned with when the form is submitted to the row in sheets, a number will auto populate in column A and in column B I have a formula in place where it calculates if my number in column A is in tolerance. For instance, it outputs "out of tolerance" in column B and I need that email notification instantly. This is for my business where the operators are inputting parameters into google forms which inputs into google sheets and I need sheets to store the inputs and provide me a notification if the inputs are out of tolerance. Hopefully that gives you a greater idea of the purpose I am trying to accomplish.

[@SpencerFarris]

@@rodneywhite315 It sounds like an onFormSubmit() that checks column B is your best option

[@rodneywhite315]

@@SpencerFarris what would my code look like for that? I am new to coding

[@jumaimuhammed9012]

Hello Spencer, thanks so much for the video. I am trying to adapt the script to some work such that when something gets flagged as 'DUE', it triggers an email to a particular recipient. I have tried to tweak the code but i can't seem to get it right, can you assist me?

[@SpencerFarris]

Happy to - can you add me as an editor?

[@jumaimuhammed9012]

@@SpencerFarris Yes i would be glad to, what email do i use to grant you access?

[@jumaimuhammed9012]

@@SpencerFarris Please still waiting for your support. I am not sure how to add you yet or do i post the link here?

[@SpencerFarris]

@@jumaimuhammed9012 [e-mail]

[@SpencerFarris]

@@jumaimuhammed9012 [e-mail]

[@AH-zy2df]

Great video! Is it possible to create a conditional email by utilizing a checkbox? If I click on a checkbox and make it True, can an email be generated? Thank you again

[@SpencerFarris]

Yes! The statement would be (if e.range..... || e.value != "TRUE") return

[@AH-zy2df]

@@SpencerFarris Hello Spencer. Sorry but I have limited knowledge of script editor. Wanted to see if you and I can speak offline and discuss getting direct help from you. Thank you

[@SpencerFarris]

@@AH-zy2df [e-mail]

[@samichouchane3652]

Hi Spencer, Thank you for the clear video and explaination! Cool :). One question, I get the following error: TypeError: Cannot read property 'columnStart' of undefined at sendMailEdit(Code:2:15) How can I solve this? Thank you! KR< Sami

[@SpencerFarris]

This script, and most onEdit scripts, are not meant to be run manually. Create the trigger as shown in the video and it will work whenever the proper edit is made.

[@samichouchane3652]

@@SpencerFarris ah I found it. My account already send to many emails. Tried it with another account and it worked perfectly :) Thanks! One more question. I ve now got it running in a multiple tab sheet but is it better to put the right sheet in the script itself? And if so, how can I do that? THanks!

[@SpencerFarris]

@@samichouchane3652 I apologize, I don't know what you're asking

[@joseluisledesma3940]

Hello, copy your sheet, also your script, when I run it it gives this error, please help me, TypeError: Cannot read property 'range' of undefined (línea 2, archivo "Code") thank you

[@SpencerFarris]

This script is not meant to be manually run, but automatically runs whenever you make an edit

[@davidcruz560]

Is there a way to have this script run based on a pop up window? Say a person selects a value in a cell say "Complete" a box pops up and askes are you sure? if you select yes then send the email with all of that information, if NO then do nothing?

[@SpencerFarris]

Absolutely! Check out ui.prompt() from the Developer Resource: developers.google.com/apps-script/reference/base/ui

[@davidcruz560]

@@SpencerFarris Thank you for this! So I have a dialogue box already popping up. I have it set to where if the BUTTON YES is selected to then run my other function, this function runs the email script. My script calls for specific fields to be placed into the email. Unfortunately I keep getting a range error. The email script runs when the word "completed" is selected, the problem is that is the rule for the pop up box. So when the email function gets called, the word "completed" is already there and seems to negate the onEdit function. I am new to this and still learning sorry! But thank you so much for your time:)

[@SpencerFarris]

@@davidcruz560 Can you make me an editor on the Sheet? [e-mail]

[@davidcruz560]

@@SpencerFarris I have added you. Thank you so much.

[@SpencerFarris]

@@davidcruz560 I didn't receive anything

[@ellenang2077]

Hi Thank you for the video. I trying to make something similar but I hit a road block. Could you help me out? I have a list of data and I want to automatically send a reminder to the person, when the date reaches. For example: payment due: 19 Jan 2021,. 30 day before the due date, my code will send an email to the person reminding him that the payment is going to due. could you help me with this? I tried using some code but I keep getting this error: The parameters (String,String,String) don't match the method signature for Utilities.formatDate. Where by both my google sheet and script time zone is the same. Please help me out.

[@SpencerFarris]

Hi Ellen, What's your full code? If it's something that automatically counts days (using TODAY() or something like that) then you'll want to time trigger, rather than an edit trigger, checking today's date against the desired date.

[@ellenang2077]

@@SpencerFarris function sendAssignment() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PE"); var startRow = 2; // First row of data to process var numRows = sheet.getLastRow()-1; // Number of rows to process var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn()); var data = dataRange.getValues(); var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1,1).getValue(); //Get template text from first cell in Template sheet var EMAIL_SENT = 'EMAIL_SENT'; for (var i = 0; i < data.length; ++i) { var row = data[i]; var date = new Date(); var sheetDate = new Date(row[0]); Sdate = Utilities.formatDate(date, 'GMT-0700','EEE, MMM dd, yyyy'); SsheetDate = Utilities.formatDate(sheetDate, 'GMT-0700','EEE, MMM dd, yyyy'); if (Sdate == SsheetDate){ if (row[8] != EMAIL_SENT) { // Prevents sending duplicates var emailAddress = row[1]; var subject = "PE Due Reminder"; var Name = row[4]; var Due = row[7]; var Course = row[6]; var Due = Utilities.formatDate(Due,'GMT-0700','MMM d'); var emailText = templateText.replace("{Name}",Name).replace("{Course}",Course).replace("{Due}",Due); Logger.log(emailText); MailApp.sendEmail(emailAddress, subject, emailText); sheet.getRange(startRow+i,8).setValue("EMAIL_SENT"); } } } }

[@lyinglegalbum]

This is great - thanks for sharing! How would you adjust the code for the email message to have formatting such as paragraphs, bold, etc instead of just a one-liner? for example: "Hello Person A, Your Order number AN336319 containing 57 items from Location 1 has been approved. Regards, Person B"

[@SpencerFarris]

You would make an HTML body and write the entire thing using HTML markup tags.

[@lyinglegalbum]

@@SpencerFarris Sorry I'm fairly new at programming html. I got the translation to the above email template from another site so wondering how I can incorporate this code into your code? Once I see how it's done, I think i can apply the same concept to my own code. Thank you! Hello Person A, Your Order number AN336319 containing 57 items from Location 1 has been approved. Regards,Person B

[@samloyer2547]

TypeError: Cannot read property 'range' of undefined (line 2, file "Code") Why?

[@SpencerFarris]

Because you're running the script manually. It is meant to only run automatically when an edit occurs

[@karl1236958]

​@SpencerFarris could you possibly assist me with my sheet setup regarding sending out of automated emails?

[@nickbedard1623]

When I am trying to select my trigger the only sources I have are Time-driven and Calendar, do you happen to know what I messed up?

[@SpencerFarris]

You don't have "From spreadsheet" as an option? That should be the default.

[@nickbedard1623]

No - I've been stackoverflow shopping for a bit to see why that event is not available to me with no conclusion. Thanks for the response!

[@SpencerFarris]

@@nickbedard1623 Can you add me as an editor on the Sheet?

[@nickbedard1623]

I'm dumb - totally forgot to begin the script from Extensions>Script editor.... Sorry to bug you on this! Thanks for the help

[@SpencerFarris]

@@nickbedard1623 Ok... where were you?? lol

[@crazylupe101]

Can the script run based on sheet name? I have two sheets that I will need to notify me to order product based on what a user enters in a cell

[@SpencerFarris]

Sure. Are you saying it would only run if the edit is on a particular sheet/tab?

[@crazylupe101]

@@SpencerFarris yes!

[@crazylupe101]

@@SpencerFarris also if I add a link from my drive to the excel spreadsheet that's a PDF file how can I have the script pull that link and send as an attachment?

[@SpencerFarris]

@@crazylupe101 In the if statement on line 2 add e.source.getActiveSheet().getName() != "sheet name" ||

[@SpencerFarris]

@@crazylupe101 Yes

[@citationcj471]

hi Spencer ... very usefull, but i receive a error : TypeError: Cannot read property 'columnStart' of undefined at sendMailEdit(EmailSendCode:105:15)

[@SpencerFarris]

Do not run the script manually. It must be setup as an Installable Trigger and only run via the automatic trigger. A manual attempt will throw the error because the 'e' object passed to the function only exists from the edit.

[@ajiredale789]

Thank you, seriously. Thank you!

[@youcefkaddour01]

hi ! I have a quesiton. I want to whrite a script like you. But in my case I want to send a email to a specific mail who is in the same row of all the information I want to send. But i want to send the mail only when a specific cell of this row is edited (because basic it is empty). Do you think you can help me ?

[@SpencerFarris]

Just make sure that is in the array of data pulled (in this case it's the const rData) and define "let email = rData[0][index]"

[@youcefkaddour01]

@@SpencerFarris okay, but my question is. How can I send a email just after a cell is updated ? Do you have a script to send a email with information of the row when a cell of this row is updated ? In my case it is a empty cell. Please !

[@SpencerFarris]

@@youcefkaddour01 I'm not sure I follow. The script I use here only runs when you edit column 5, or E. So which column do you want to track edits from?

[@davidmcnulty6681]

Hi, I am new to google sheets and script have a sheet that I would like to get an email notification if any cell in column V2:V is changed or edited, can you tell me how to do this? Ta

[@SpencerFarris]

The actual code to send an email when V:V is edited is simple. That's just this: function sendMailEdit(e){ if (e.range.columnStart != 22 ) return; GmailApp.sendEmail(email_address, subject, msg) } Then it depends what you want to put in that email. So you could do: function sendMailEdit(e){ if (e.range.columnStart != 22 ) return; GmailApp.sendEmail([e-mail],"New Edit on V", "Cell V" + e.range.rowStart + " was edited on sheet sheet_name") }

[@davidmcnulty6681]

@@SpencerFarris Thanks for the reply and solution ! Will it work if the data is pulled into the cell via a formula ? I have a sample sheet if you could have a look at ? docs.google.com/spreadsheets/d/1KZcimzAAKL90yUMJMS-BdCdopk0Fmy1kRX9Ko2Ilivs/edit#gid=687632304

[@sararaygor3419]

This was super helpful and I was able to run the trigger. It worked perfectly. I am wondering how I can write code for a pull down menu. So I have four options and I want to send an email when one option is chosen and send another email when that option is changed to something else. To be exact, in my column I have Client Ready to be Invoiced, Client Invoiced, Supplier Ready to be Invoiced, and Supplier Invoiced. When someone changes the pull down to Client Ready to be invoiced, I need an email to be sent to accounts payable. When someone changes the pull down to Supplier Ready to be Invoiced, I need an email sent to accounts payable. Is this possible? And if so, how would I do that. Thanks so much.

[@SpencerFarris]

I would make an array of inputs = ["Client Ready to be Invoiced","Client Invoiced","Supplier Ready to be Invoiced","Supplier Invoiced"] and an array of emails = ["email1","email2","email3","email4] then in the send to GmailApp.sendEmail(emails[inputs.indexOf(e.value)],)

[@sararaygor3419]

@@SpencerFarris That's perfect. Thank you so much for the quick response!

[@MFTW]

I get the same error: "sendMailEdit @Code:2"

[@SpencerFarris]

What do you mean "the same error?"

[@ninad200178]

thanks Spencer..very useful video

[@SpencerFarris]

Glad it was helpful!

[@joseparedes2391]

Hey man, thanks so much for sharing. I have a weird problem though, the function executes with no problem, or so says the record. But there's no email, not in the sent tray from the sender nor in the receiving end. Any thoughs on what could be the problem?

[@SpencerFarris]

In the logs/record are you seeing the Logger.log(msg) displayed?

[@SpencerFarris]

​@Elliott Smith Best I can do without having access is that you should put Logger.log() calls throughout to see how far the script is getting before stopping. I just used this script last week to send several personalized emails without issue.

[@morrisravis1435]

@@SpencerFarris I've also added the Logger.log() calls throughout... Logger.log("sendEmailsapp ran!"); I put this after every line to check and finally saw something in the logs. However, when I switched it back to Logger.log(msg); I still am getting the former message in the logs of "sendEmailsapp ran!" and am still getting no email. Not sure if I have some sort of setting configured wrong; lmk!

[@SpencerFarris]

@@morrisravis1435 So how far is it getting? What's the first line that doesn't run?

[@morrisravis1435]

@@SpencerFarris that's the thing; every line ran after putting the log after it; it just wouldn't print the message; now I've recreated the scenario though and while it says complete, I am no longer seeing anything in the Logs. Does it have to do w/ trying to send it to my own email as opposed to someone else's email?

[@ernestasraudys2968]

Hello, maybe know why i got this erro on your copy file when try test. If you can please help [21-04-15 12:32:04:543 PDT] TypeError: Cannot read property 'range' of undefined at sendMailEdit(Code:2:9)

[@SpencerFarris]

Hi Ernestas, This type of script is not meant to be run manually. Create the trigger and it should run automatically. It fails because in the function declaration is passed a variable 'e' and that variable only exists when the function is triggered automatically; it doesn't exist in a manual call.

[@ernestasraudys2968]

@@SpencerFarris oh yes i see, but now i see not all time got result . Some times when try cheking result is completed but write it "No logs are available for this execution For recent executions there may be some delay in log availability." and no got email and don't see like in your video

[@SpencerFarris]

@@ernestasraudys2968 Can you share a file with me? [e-mail]

[@ernestasraudys2968]

@@SpencerFarris i fix my problems and make file like you show . thx you :) Maybe know how need make cell copy when A3 cell changet? If cell A3 changet save new data to cell A4 and all time save new data in cell A4 with out delete old ?

[@SpencerFarris]

@@ernestasraudys2968 I responded to your email. Thanks!

[@bdabtv]

I hit my first snag. I'm getting the error: "TypeError: Cannot read property 'range' of undefined (line 2, file "Code")".

[@SpencerFarris]

Don't run the script, it will fail. Go watch my onEdit Basics video for more about it, and my newer videos talk about it explicitely. But that 'e' value comes from an edit. This will only work when an edit happens, not from a manual run.

[@samloyer2547]

Hello Spencer, How to use this with getSheetByName?

[@SpencerFarris]

What /exactly/ do you mean?

[@georgina5511]

can this be edited to trigger a mail merge in Gmail? I want to maintain the email layout which contains a lot of information and some images. Currently, I use an Add on but it isn't working how id like because i have more than one sheet in the workbook.

[@SpencerFarris]

You can write out a full HTML email, but I don't know about accessing a pre-made mail merge

[@georgina5511]

@@SpencerFarris thank you for your response. Ill have to keep searching for a solution.

[@SpencerFarris]

@@georgina5511 Sorry about that

[@georgina5511]

@@SpencerFarris So essentially, I have now moved on to trying to send an email link and brief message using google script. I am struggling to understand and adapt your script so any help would be appreciated. I am wanting to set an IfThen function that if Column W = 'Yes'; an email is sent to the address which can be found in column AB on the same row (so for the first entry it will be row 2). This is working off of a spreadsheet that is already in use and the person I am working with needs it to stay the same structurally. I've been trying to work it so I can input the message into the code as opposed to having to reference another column in the sheet. Any help or ideas would be greatly appreciated. Maybe I'm just imagining an impossible concept?

[@SpencerFarris]

​@@georgina5511 So it's going to start with onEdit(){ if (e.range.columnStart != 23) return; let emailAddress = e.range.offset(0,5).getValue(); } You can write the message directly into a 'message' variable using either javascript String formatting or HTML.

[@abhishekkashyap2455]

can we add multiple email ids

[@haphamthai6878]

Great article. I have an appsheet application for data entry I also follow your instructions Each time the application is imported, a new row is created in the sheet. I want to send notification email when new row is created but it is not working. Can you guide me?

[@SpencerFarris]

I'm not well-versed in AppSheets => Sheets, so I'm not sure how to get the trigger to work, tbh

[@rodneywhite315]

I am having this issue as well. Any luck?

[@judequinston]

Hi, Spencer Thank you for this video. I have a type error it says Cannot read property 'range' of undefined (line 2, Please kindly help me to sort this out. Thanks

[@SpencerFarris]

Here's a video about onEdit basics. Essentially, the (e) variable in the onEdit() only exists when the script runs from an automatic trigger. They are NOT meant to be run, it will fail. kzbin.info/www/bejne/jHPdm6yHjq52hNk

[@judequinston]

@@SpencerFarris ohh thank you so much