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 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
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!!!
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 :)
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?
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) }
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.
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
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?
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 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.
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.
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!
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
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?
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.
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?
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, 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.
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!
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!
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?
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.
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.
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?
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 ua-cam.com/video/TH3U8869Jzg/v-deo.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!
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...
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.
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.
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.
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
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: ua-cam.com/video/TH3U8869Jzg/v-deo.html
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 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?
@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
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
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.
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
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.
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?
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 ua-cam.com/video/TH3U8869Jzg/v-deo.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?
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..
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?
@@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
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, 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!
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".
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
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.
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, 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!
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.
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
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?
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
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?
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.
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 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).
Hi Spencer, thank you for this great video, I want this mail to be sent to an email specified in each cell, that if I type "Approved" in Cell E5, it should send mail to mail address in cell A5, if I do same thing for cell E6 it should send mail to another mail address in A6 and on and on like that.
@@SpencerFarris You only specified only one mail address it should send to in this video, I don't know to make it send to different emails as appeared in each cell
@@SpencerFarris so I will have this right? Script: function sendMailEdit(e){ if (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(rData[0][0], "Approved Order", msg) } correct?
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 Spencer, amazing video. It worked on my end. Like many I am looking for a way to send me an email when a specific cell that I use Googlefinance on gives me a specific increase in a currency. Thus that condition true or false would trigger the email. In my case "TRUE" hoping you can help me out :)
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) }
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 🙂
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. ua-cam.com/video/VBzjyQYICNw/v-deo.html
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?
@@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) }
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
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!
Hi Rosemarie! Thank you for your kind words, I'm glad it was useful. Do you have an email group setup for your team? If so, you can use that group email as the recipient "team_email_group@gmail.com"; if not, you can separate the team emails with commas: "email1@gmail.com, email2@gmail.com"
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?
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.
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.
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.
@@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"); } } } }
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?
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
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.
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?
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
Hey man great video. I was just wondering can you have the email sent to a non Gmail account like a business domain. I want to do this for a client but he has a business email that's not with Google
@@SpencerFarris hey man thank you so much for the help I ended up testing this and it works perfectly so I am going to do it for my client you're awesome dude you saved my ass
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 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
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!
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
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
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.
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
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
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?
Thank you Sir!....
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
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
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.
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.
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!
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
ua-cam.com/video/TH3U8869Jzg/v-deo.html
Very useful
Thank you!
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?
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.
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 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
thanks Spencer..very useful video
Glad it was helpful!
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,
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.
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 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
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.
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
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
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)
Thank you, seriously. Thank you!
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
ua-cam.com/video/TH3U8869Jzg/v-deo.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
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...
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
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.
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.
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.
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
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: ua-cam.com/video/TH3U8869Jzg/v-deo.html
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, 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.
PURE GOLD. thanks
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?
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!
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.
Great script. to change date format in automated email would we just change from en-US to en-GB?
Yes!
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
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
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.
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?
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. 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.
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.
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 ua-cam.com/video/TH3U8869Jzg/v-deo.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
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..
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?
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
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?
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.
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
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
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.
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!
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
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
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");
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?"
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...👍👍👍
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
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 ua-cam.com/video/TH3U8869Jzg/v-deo.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?
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;
Are you using Python for that program?
It's all Google Apps Script, derived from JavaScript
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 ua-cam.com/video/TH3U8869Jzg/v-deo.html
Hi Spencer, thank you for this great video, I want this mail to be sent to an email specified in each cell, that if I type "Approved" in Cell E5, it should send mail to mail address in cell A5, if I do same thing for cell E6 it should send mail to another mail address in A6 and on and on like that.
Did you try editing the script at all? That's the same idea as what the original script does.
@@SpencerFarris You only specified only one mail address it should send to in this video, I don't know to make it send to different emails as appeared in each cell
@@oladejiopeyemi5153 So assuming your relevant range is A:E then the email address would be rData[0][0].
@@SpencerFarris so I will have this right?
Script:
function sendMailEdit(e){
if (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(rData[0][0], "Approved Order", msg)
}
correct?
Thank you so much, it worked.
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?
I get the same error: "sendMailEdit @Code:2"
What do you mean "the same error?"
Hi Spencer, amazing video. It worked on my end. Like many I am looking for a way to send me an email when a specific cell that I use Googlefinance on gives me a specific increase in a currency. Thus that condition true or false would trigger the email. In my case "TRUE" hoping you can help me out :)
I made a video about onChange() that helps get there.
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)
}
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
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.
ua-cam.com/video/VBzjyQYICNw/v-deo.html
@@SpencerFarris ohh thank you so much
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
ua-cam.com/video/DgqTftdXkTw/v-deo.html
@@SpencerFarris Wow! Thanks for replying. Big up from Jamaica!
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
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)
}
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
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
Hi, your tutorial was amazing, I'd like to ask how about sending an email to my team when the dropdown change? Thank you
Hi Rosemarie!
Thank you for your kind words, I'm glad it was useful. Do you have an email group setup for your team? If so, you can use that group email as the recipient "team_email_group@gmail.com"; if not, you can separate the team emails with commas: "email1@gmail.com, email2@gmail.com"
@@SpencerFarris Thank you, will try it! Great Video!
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?
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?
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.
Hi i need to open URL or mp3 file when conditions met.
Can you please help me on code
Hello Spencer,
How to use this with getSheetByName?
What /exactly/ do you mean?
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.
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.
@@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");
}
}
}
}
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?
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
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.
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?
You don't have "From spreadsheet" as an option? That should be the default.
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!
@@nickbedard1623 Can you add me as an editor on the Sheet?
I'm dumb - totally forgot to begin the script from Extensions>Script editor.... Sorry to bug you on this! Thanks for the help
@@nickbedard1623 Ok... where were you?? lol
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.
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
Hey man great video. I was just wondering can you have the email sent to a non Gmail account like a business domain. I want to do this for a client but he has a business email that's not with Google
You should be able to send it TO any email, Google or otherwise. Just the From will be the Google address.
@@SpencerFarris hey man thank you so much for the help I ended up testing this and it works perfectly so I am going to do it for my client you're awesome dude you saved my ass