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.
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!!!
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
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 :)
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
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?
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.
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?
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.
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) }
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?
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!
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?
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?
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.
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?
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
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.
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...
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.
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.
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!
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
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.
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) }
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!
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 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!
@@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!
@@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.
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?
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.
@@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?
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.
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.
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!
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
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!
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.
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 !
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.
@@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.
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.
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.
@@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.
@@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.
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, 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)?
@@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: ruclips.net/video/TH3U8869Jzg/видео.html
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".
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.
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 ruclips.net/video/TH3U8869Jzg/видео.html
@@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!
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?
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.
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
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?
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!
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?
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?
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?
@@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.
The fullest sendEmail() method takes recipient, subject, body, and options. Recipient takes a comma-separated string of emails "email1@mail.com, email2@mail.com" and options allows you to use {cc: "email3@mail.com,email4@mail.com"}
@@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
Thank you! Only your script working on youtube. Other are bullshit.But if I change “open” to “approved” by function the script does not work so finally it is unuseful for me
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 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..
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
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;
@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
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?
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 ?
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 ruclips.net/video/TH3U8869Jzg/видео.html
@@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 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?
@@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
This script is exactly what I'm looking for! Thanks for the great video. When I modified to meet my needs it won't save and produces an error message I cannot seem to resolve. Any chance I can share the script and have you take a look? I'm a newbie at this although I understand the logic from my days as a programmer long ago early in my IT career.
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?
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 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
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.
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!
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.
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?
Hi Spencer, this video finally made a successful start to an automated email I wanted to install in a google sheet, so really thank you so much. I have a question about this email being triggered by an automatic change in a spreadsheet. For example: a clock counting down this is automated by an equation in the spreadsheet when the number goes below -12 I would like the email to send. Naturally, this means the change is not an onEdit entry. I tried to adapt the code for onChange, but I think it's having a problem understanding the value change. Any tips?
From the Installable Triggers documentation, "An installable change trigger runs when a user modifies the structure of a spreadsheet itself-for example, by adding a new sheet or removing a column." So the onChange trigger, like onEdit, also only works when a USER makes a manual change, not when something automated (like a formula) changes. The usual workaround is setting up a time trigger sufficiently frequent that it catches everything you need, and runs across the entire range, performing its operation as necessary.
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 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?
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 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 !
@@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?
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
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.
@@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!
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) }
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
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
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(david.mcnulty@gmail.com,"New Edit on V", "Cell V" + e.range.rowStart + " was edited on sheet sheet_name") }
@@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
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.
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!
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 🙂
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
Hello Spencer, Great work!!! I've got a problem. I copied script step by step to try how it work however it shows me constanty an error Error below: TypeError: Cannot read properties of undefined (reading 'columnStart') at sendMailEdit(Kod:2:15) Im running it automatically not manually Would you be so kind to help? BŁ
@@bartekukaszewicz3343 I've never seen that error unless you are manually clicking "Run." I don't believe apps script is different across different locales/languages.
@@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) }
@@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?
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?
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?
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 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:)
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).
Use the advanced Options parameter developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object) to create an htmlbody
@@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.
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?
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.
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)],)
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
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. ruclips.net/video/VBzjyQYICNw/видео.html
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.
Thank you so much!
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!!!
So glad it helped!
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
AMAZING. been using google sheets for about 5 years and never knew this amazing stuff
Glad to help!
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 :)
@Jett Landon yea, I have been using Instaflixxer for months myself :D
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
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?
The method in this video is what I would used, based on your description
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.
Weeeeird
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?
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?
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.
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)
}
@@SpencerFarris Wow you're just the best. Got this to work.
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?
This only sends when a change occurs
@@SpencerFarris That's what I thought. Thank you very much
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!
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?
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)
No, no library or anything extra needed
Thank you Sir!....
Hello Spencer! Can I ask you for this script only in OnChange version? If you find time to modify it appropriately, of course. Regards
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?
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.
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?
@@SpencerFarris yeap tried that but the first code went down. So only the second/latest conditions triger an email.
@@clashoflions7339 So you have a single onEdit() script with multiple conditions, but only the last condition is working?
@@SpencerFarris Spencer thank you for the resolution. I received your response and it is working perfectly now.
@@SpencerFarris When I do this I get "syntax error: unexpected token in like 12: else if". Any idea what I am doing wrong?
Hey thanks Spencer....Im getting an error --
Error
TypeError: Cannot read property 'range' of undefined
sendMailEdit @ Code 2.gs:2
same here
@@fondobursatil8099 Don't run the script, it will fail. This script is designed to only run when triggered by an edit.
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
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.
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.
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...
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.
Hi, Spenser! I am very appreciative of your content. Good job. I have a question though. Is it possible to do it excel online?
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.
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!
Yes, look at my onChange video
Hi i need to open URL or mp3 file when conditions met.
Can you please help me on code
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
Sure, you can just make a time trigger and, at the beginning, check those conditions before continuing.
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.
Yes, but that will depend on your exact setup. Can you add me as an editor? spencer.farris@gmail.com
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)
}
Very useful
Thank you!
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!
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"
@@SpencerFarris Thank you for your reply! I converted the onEdit function to an executable function and it works! :)
@@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!
@@SpencerFarris hi, I am having the exact same obstacle, where should I tie the onEdit function? Can you please help? Thanks in advance
@@vahagnvardanyan6729 I don't understand the question
Thanks a bunch! Not sure how you don't have thousands of subs right now.......
Haha, thank you!
@@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!
@@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.
@@SpencerFarris awesome! I appreciate that bit of information! What's the best way to connect with you?
@@arnellcarmichael635 spencer.farris@gmail.com
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?
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.
@@SpencerFarris Hi again, I receive the error code after making an edit at 5th column in the sheet.
@@fuatadami With that, I'd have to see the actual sheet/code to see why it's failing. spencer.farris@gmail.com
@@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?
@@SpencerFarris it is working amazing! Just need to see if it can send out.
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.
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.
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!
e.value < 21
Hi Spencer. How can I make this function reference my worksheet? (as opposed to the sample you provided). How do I declare that? Thanks!
The script is bound to whatever sheet you make it on.
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
You would have to use onChange instead
ruclips.net/video/TH3U8869Jzg/видео.html
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!
It only doesn't run on a tab if you've told it not to.
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.
Can you email me and we'll work on it? spencer.farris@gmail.com
Thank you for a great guide. How did you make that dark theme on the script editor?
I'm using the AppsScript Color Chrome extension chrome.google.com/webstore/detail/appsscript-color/ciggahcpieccaejjdpkllokejakhkome
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 !
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.
@@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.
@@andrediedericks7767 Combine it with my onChange video ruclips.net/video/TH3U8869Jzg/видео.html
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.
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.
@@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.
@@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.
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
No script came through, but please add me as an editor on the sheet so I may best assist.
@@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)?
@@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: ruclips.net/video/TH3U8869Jzg/видео.html
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
What have you tried? Is B2 set by formula or manually?
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".
I'd have to see exactly what Sheets interprets the barcode insertion as. Can you add me as an editor? spencer.farris@gmail.com
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.
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
ruclips.net/video/TH3U8869Jzg/видео.html
@@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!
@@josephpapino Give it a try yourself and add me as an editor if you can't get it.
@@SpencerFarris wow thanks! Yes I’ve been trying all day. I will make you an editor now. What is your email tho?
@@josephpapino spencer.farris@gmail.com
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?
spencer.farris@gmail.com
Thank you, seriously. Thank you!
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.
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.
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
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?
I am having this issue as well. Any luck?
@@rodneywhite315 What issue, exactly? As I assked above, what constitutes a "new row added?"
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!
I'm confused what you're asking. This video is the tutorial, so what do you want?
what is this e stand for and why we have to use that. Any idea.what is this e.range states for ?
'e' is all the data that is passed to the function by the edit trigger. It contains the source, range, value, and other information.
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?
In the {options} section
developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)
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?
It looks like your question got cut off
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?
@@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.
Great script. to change date format in automated email would we just change from en-US to en-GB?
Yes!
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.
The fullest sendEmail() method takes recipient, subject, body, and options. Recipient takes a comma-separated string of emails "email1@mail.com, email2@mail.com" and options allows you to use {cc: "email3@mail.com,email4@mail.com"}
@@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
@@farkhadhuseinov5246 I do not. I haven't worked with APIs such as WhatsApp, Discord , or Slack
Thank you! Only your script working on youtube. Other are bullshit.But if I change “open” to “approved” by function the script does not work so finally it is unuseful for me
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.
use src.getName() != "sheet name" in the IF statement to return out
@@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..
Hi Spencer, how do I create a syntax to pick/reference more than 1 column for the body of email Please?
This script already does that, unless I misunderstood the ask. The message is built from columns 1-4.
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
With only one condition its working perfectly...👍👍👍
I get the same error: "sendMailEdit @Code:2"
What do you mean "the same error?"
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?
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;
Hi! Thanks a million for posting this video
Just a question: What if I want an email when something isn't a specific value?
What do you mean?
@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
@@36_chambers39 In that case change the initial IF statement to:
if (e.range.columnStart != column_number || e.value -= "Wendy's") return;
@Spencer Farris you rock thanks so much!
Please help, I don't have the option "Google Spreadsheet" from Select Event Source (adding trigger).
Please reply Mr Spencer.
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?
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 ?
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 ruclips.net/video/TH3U8869Jzg/видео.html
@@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!
@@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.
@@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?
@@karim1996k Can you please email me the Sheet?
spencer.farris@gmail.com
Thanks for the vid. It helped a lot. Small tweak. How would I change the e.value != "Approved" to any value greater than 0?
e.value > 0
@@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.
@@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
This script is exactly what I'm looking for! Thanks for the great video. When I modified to meet my needs it won't save and produces an error message I cannot seem to resolve. Any chance I can share the script and have you take a look? I'm a newbie at this although I understand the logic from my days as a programmer long ago early in my IT career.
Certainly! spencer.farris@gmail.com
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?
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.
PURE GOLD. thanks
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"
You would make an HTML body and write the entire thing using HTML markup tags.
@@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
hi Spencer ... very usefull, but i receive a error : TypeError: Cannot read property 'columnStart' of undefined
at sendMailEdit(EmailSendCode:105:15)
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.
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!
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.
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?
function onSelectionChange(e){
const r = e.range;
if (r.rowStart < 2 || r.rowStart > 5 || r.columnStart < 2 || r.columnStart > 5) return
...
...
...
}
@@SpencerFarris Very cool. Works like a charm. Greatly appreciated. What are those two vertical lines after the numbers? does it represent "and"?
@@orkayen || is OR, && is AND
@@SpencerFarris Thanks, Spencer. Can I contact you thru email for google sheet/apps script related questions?
@@orkayen yes
Hi Spencer, this video finally made a successful start to an automated email I wanted to install in a google sheet, so really thank you so much.
I have a question about this email being triggered by an automatic change in a spreadsheet.
For example: a clock counting down
this is automated by an equation in the spreadsheet
when the number goes below -12 I would like the email to send.
Naturally, this means the change is not an onEdit entry. I tried to adapt the code for onChange, but I think it's having a problem understanding the value change. Any tips?
From the Installable Triggers documentation, "An installable change trigger runs when a user modifies the structure of a spreadsheet itself-for example, by adding a new sheet or removing a column."
So the onChange trigger, like onEdit, also only works when a USER makes a manual change, not when something automated (like a formula) changes. The usual workaround is setting up a time trigger sufficiently frequent that it catches everything you need, and runs across the entire range, performing its operation as necessary.
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?
Sure: spencer.farris@gmail.com
@@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?
@@user-id6ob7ey4h Just add another call to GmailApp at the end.
thanks Spencer..very useful video
Glad it was helpful!
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 ?
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]"
@@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 !
@@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?
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
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.
@@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!
@@samichouchane3652 I apologize, I don't know what you're asking
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
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)
}
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
This script is not meant to be manually run, but automatically runs whenever you make an edit
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
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(david.mcnulty@gmail.com,"New Edit on V", "Cell V" + e.range.rowStart + " was edited on sheet sheet_name")
}
@@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
I hit my first snag. I'm getting the error: "TypeError: Cannot read property 'range' of undefined (line 2, file "Code")".
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.
Is there a way to trigger via daily cron, like check the page every 24 hours?
Yes, you can make a time trigger from the Triggers page
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!
Can you make me an editor on the sheet?
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 🙂
You would need to find an Outlook API and use URL Fetch to access and utilize that API
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
You'll include a line ss.getRange(range).setValue(msg)
@@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)
@@kristijan.akmacic So you'll need to define the sheet. So ss = SpreadsheetApp.getActive().getSheetByName("sheet name here");
Are you using Python for that program?
It's all Google Apps Script, derived from JavaScript
Hello Spencer,
Great work!!! I've got a problem. I copied script step by step to try how it work however it shows me constanty an error
Error below:
TypeError: Cannot read properties of undefined (reading 'columnStart')
at sendMailEdit(Kod:2:15)
Im running it automatically not manually
Would you be so kind to help?
BŁ
If it's running automatically with e.range.columnStart that shouldn't happen. What's your code?
@@SpencerFarris Hi, thank you for answer.
I copied your example just to try. I use polish version appscript. Might it cause the error?
@@bartekukaszewicz3343 I've never seen that error unless you are manually clicking "Run." I don't believe apps script is different across different locales/languages.
@@SpencerFarris will it be the problem if I give you the access to the sheet?
@@bartekukaszewicz3343 not at all
Hi Spencer,
Is there a way to read the email from a cell in the current row instead of being hardcoded?
Thanks!
Absolutely! That would just be another rData[0] reference so long as the .getRange() is large enough.
@@SpencerFarris is it possible for you to show how? don't take me wrong, but for me is kind tricky
@@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)
}
can we add multiple email ids
TypeError: Cannot read property 'range' of undefined (line 2, file "Code")
Why?
Because you're running the script manually. It is meant to only run automatically when an edit occurs
@SpencerFarris could you possibly assist me with my sheet setup regarding sending out of automated emails?
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
Sure. Are you saying it would only run if the edit is on a particular sheet/tab?
@@SpencerFarris yes!
@@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?
@@crazylupe101 In the if statement on line 2 add e.source.getActiveSheet().getName() != "sheet name" ||
@@crazylupe101 Yes
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?
To specify a sheet, I normally add to the first IF() statement "e.source.getActiveSheet().getName() != 'sheet name here'"
Here's a video about adding timestamps on edit. You should be able to adapt both scripts together
ruclips.net/video/DgqTftdXkTw/видео.html
@@SpencerFarris Wow! Thanks for replying. Big up from Jamaica!
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?
Happy to - can you add me as an editor?
@@SpencerFarris Yes i would be glad to, what email do i use to grant you access?
@@SpencerFarris Please still waiting for your support. I am not sure how to add you yet or do i post the link here?
@@jumaimuhammed9012 Spencer.farris@gmail.com
@@jumaimuhammed9012 spencer.farris@gmail.com
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?
Absolutely! Check out ui.prompt() from the Developer Resource:
developers.google.com/apps-script/reference/base/ui
@@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:)
@@davidcruz560 Can you make me an editor on the Sheet? spencer.farris@gmail.com
@@SpencerFarris I have added you. Thank you so much.
@@davidcruz560 I didn't receive anything
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).
onEdit scripts only trigger on manual edit. I have another video on using onChange ruclips.net/video/TH3U8869Jzg/видео.html
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?
Use the advanced Options parameter developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object) to create an htmlbody
@@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.
@@zeza2217 If you know how to write html it's pretty simple
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?
I'm not well-versed in AppSheets => Sheets, so I'm not sure how to get the trigger to work, tbh
I am having this issue as well. Any luck?
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.
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)],)
@@SpencerFarris That's perfect. Thank you so much for the quick response!
Hello Spencer,
How to use this with getSheetByName?
What /exactly/ do you mean?
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
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.
ruclips.net/video/VBzjyQYICNw/видео.html
@@SpencerFarris ohh thank you so much