Google Apps Script Tutorial: Send Yourself an Email with the onEdit() Trigger
HTML-код
- Опубликовано: 14 июн 2022
- In this video I show you how easy it is to trigger an email to be sent when a spreadsheet is updated, even with a specific value, using Google Apps Script's onEdit function.
You can review the code I wrote in this video at github.com/palumbo/RUclips-Sc...
WANT TO SAVE MONEY USING GOOGLE WORKSPACES?
If you're interested in maximizing the capabilities of Google Workspace to help organize and run your small business, non-profit, charitable organization, or even your family, you can use my affiliate link - referworkspace.app.goo.gl/ZkK1 - along with one of my promotional codes to get 10% off of your first year!!
Business Starter Promotion LCWXMA4CWAEM6XN
Business Standard Promotion P7Y7AMGTQ64KP3V 
Наука
What I liked about this is that you didn't talk about a bunch of fluf before getting to the point, you were very clear and direct in your approach, I appreciate that.
this was really useful, thank you for creating it
THANK YOU SO MUCH FOR YOUR VIDEO!
It was extremely helpful!
Great!
Love this. Thank you!
Dear Joseph, the script works perfectly when someone (a person) manually edits the spreadsheet, however i can't make the onEdit trigger to activate when the row is added via an HTML formular (web app) that i've deployed. thank you very much for your valuable knowledge.
Hello Joseph,
I love how useful and helpful this video is but is there any way to get it all done (like sending the mail) using onChange trigger rather than onEdit.
I would like to receive e-mail if there's a change through a formula/function.
If so kindly let me know
thanks
Thank you for making this video. But I wanna ask you something. If I want delete the specific group of cells data with the help of onedit function and one cell as trigger to run the script, what i need to write in Appscript. Please help in this regard.
Thanks in advance
Hi Joseph, thanks for this.
I have replicated the same for my sheet but the problem is, even if I update the cell value corresponding to the column where I have mentioned delayed, it triggers an email to the respondent. Any suggestions?
Hi Joseph, thank you for this! When I run my script it all works according to plan, however the execution log shows "Timed Out" in one row, as well as "Completed" in the next row. Do you know why? Is this a problem?
Thank you.
Can you please show us how to upload images and text from sheet to Facebook or Twitter automatically.
Thanks
hello @joseph, the script will send only to one (1) user, my question is, is there anyway that i can send the email TO at least 6 emails (user) and CC to at least another 10 email (user) thank you for your video, yo the best
what if we have two tab sheet? how to make variable for one of that tab sheet
Hey Joseph, I’m getting the error: “Exception: The parameters ((class, String, String) don’t match the method signature for MailApp.sendEmail.” Because I’m trying to reference another a cell to grab an email located in the spreadsheet (coming from forms). Is there a different script I should be using to perform this?
what if you have different sheets, and only on one of them is where this action will happen?
If I have a colleague that triggers the edit, therefore they would be the active user, is there a way for the email to come from the active user instead of me? Or is it set that it will only be sent from the person who created the script?
is there a way to combine the content in two rows to create the project name?
do you have to add the gmail api under services? I have similar code but dont receive an email. did all the normal troubleshooting.
In line 5, it's saying that range is not being used in the actual call. What is your reasoning for including that line?
Hello Joseph,
Can we update many google sheets using only one google sheet's app script? If yes then how?
Thanks Joseph! I want to add some more text to the body in the mail and tried just adding it in quotation marks + the variables I want to insert but I get prompted an "missing ) after argument" error. Do you have a quick fix for this?
Hi Timon, sounds like you have at least value or parameter out of order. Your best bet is to follow the documentation for sendEmail here - developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object)
The trigger emails sent based on condition met are in loop. The emails are not sent separately. Please advise.
Can you create a time-driven trigger to send emails? I tried using your code using time-driven trigger but it fails becuse of the e.range
Dear Jospeh,
Would it be possible to do this but with a pdf attachment? Thanks
Thanks Joseph, is there a way to trigger an email when an edit matches a cell in a list on a different tab? I am attempting to create a log for students to type their name in column1 (population of 500 students), I have a list of 50-60 students that will periodically change on a separate tab. Is it possible that when one of those 50-60 students names are entered on my main sheet in column1 that it triggers an email for me?
Hi James, the easiest way to accomplish this is to put all the names you want to check against into an array, which can either be done manually in the code editor or pulled from values in a column.
From there, you can use the array.indexOf function to see if another value is present in that array.
So it would look something like this:
var names = ["John", "Tom", "Mary", "Susie"];
if ( names.indexOf(nameToBeChecked) > 0) { send email }
It's difficult to provide a clear explanation in a comment, but I'll try to do a video example of how to work with Arrays very soon.
Hello Joseph, I made a fillup form website that is connected to my spreadsheet.
What it does is when I (myself) put a value "New Appointment" on a cell in the 6th. column, an email will be sent on my GMail address.
WHAT I WANT is when ANYONE (who submitted a form using my website and a value: "New Appointment" is automatically put on a cell in the 6th. column was put on, an email will be sent on my GMail address.
Please tell me what should I change/do. Thank you.
Hello Sir, Can I make a request? Can you make the triggers based on expired date? Thanks...
hello sir how to add multiple receiver email
I have a doubt i need to clarify can we connect is there any possibility?
This is a fantastic video! Is there a way to have this script run for copies of my original spreadsheet? Is this related to the answer you gave below to create a project and use deploy?
For reference, when I create a copy, it alerts me it is going to copy the App Script for the copied file, but it does not seem to work.
Hi Tiffany, thanks for the comment and question. If you make a copy of the original spreadsheet it will retain the app script in the background. However, you will need to run it manually at least once to ensure that it has permissions to continue running.
@@jsphpalumbo Thank you for the response. At first that didn't work, but then I realized that apparently triggers do not copy over, so I had to recreate that part and it worked.
@@tiffanybyrd8485 Good call out! Yes, I guess you would have to create a new trigger. Great job, Tiffany!! You taught me something new, thanks!
Thank you for sharing this script but I couldn't get the message box to appear
This is great, but I want the trigger a specific email address in each line when I make a change to a dropdown status. For example, I have form data to reserve a bus and I need to let the person requesting the bus know I reserved a bus for them. I can’t find anywhere on how to do this.
This is easy to do with if statements specifying, either the column in which the event takes place, or the value of the event variable. It is very common to have emails go to specific people based on different types of events. Happy to provide more information if needed.
Hi Joseph, it works when I directly input/type Keyword like you did.
But when I use a function like =IF(D8 > 100, "Delayed", "In time") to au-to generate the keyword "Delayed" based on a condition -> then it's not working anymore, although that cell still shows the value as "Delayed".
Any suggestions?
Thank you so much.
Hey thangnguyen, that's right, I'm using an "onEdit" trigger which means the email is triggered only when a cell is edited. Sounds like you might want to use a calendar or time-driven trigger where every day it reviews specific ranges and if a cell/range matches a value, it will trigger an email.
@@jsphpalumbo Hi Joseph, I had the same issue as thangngyen. I changed the trigger to time (every minute). I get a failure email every time the trigger executes stating that "cannot read properties of undefined..." the same error you see when you run the script. Any suggestions?
I have to send bulk emails but I just found out it appscript has 50 emails per day limit. How can I fix it? I appreciate your reply, thank you
Hi Diock, Google imposes a limit of 100 email recipients per day using Apps Script for free accounts. If you upgrade to paid Google Workspaces account that quota is raised to 1,500 per day.
What to do if I tag someone from contacts in the spreadsheet and I want them to receive email with the assigned task details if the task is delayed. Thankyou in advance.
Hey Gunjan, I think you've given me a new idea for a video!!!
This one can be a little tricky since there are a few ways to do it. I would recommend looking up calendar or time triggers in the Apps Script documentation. But this topic is on my list of future videos.
Also, if this is for a work project, you're welcome to reach out to me about enlisting my consulting company to help do any kind of automation or implementation for you.
Thank you.
I can run it for one sheet，how can it use for
another？
Deploy？
You can create a project and use deploy. I tend to keep a Github repository of my most used code which allows me to quickly search for the function I need then copy/paste into a new sheet. Not the most elegant, but it's been the easiest way for me to manage and track when I update and make changes to a script.
@@jsphpalumbo Thank you！Another problem is when the sheet is edit by webhook, onedit() can't trigger。。。。
Hi joseph, im have an answer for onEdit function, every time im try to use it, it doesnt work, i dont know why, im using the same code but am editing the sheet and it just doesnt work, i cant find the answer can u help me?
Hi Bruno, you might have forgotten to set a trigger for the onEdit() function. Please watch the video again starting at 10:10 where I explain how to define the trigger.
@@jsphpalumbo Thanks for the answer Joseph but the problem its before, the first test of onEdit with the message it is work doesnt work ahahha, but i try it in another sheet without anything and it works, just doesnt work in the first sheet with anothers funtions
how would i change this to a specific cell and value
Hi Boyd, the easy answer to your question is to define both the cell and value in your script, but can you give me a little more information as to what you want to do?
hi, how to solve the cannot read property "range"
Hi Nurul. Thanks for your comment.
That error means that getting range from the event object e failed.
If you are running the code from inside the script editor, you will get a range error. In the case of a simple onEdit() trigger, you can only test the code by actually editing a cell. The e event variable will not have anything assigned to it unless you actually edit a cell. That e variable will be assigned an event object when the event (a cell edit) happens.
@@jsphpalumbo okie thanks joseph! have a nice day
how to publish this app to other people man ?
Check out the Deployment feature for Google Apps Script - developers.google.com/apps-script/concepts/deployments
Covering deployment of Apps Script is on my list of videos to make, so make sure you're subscribed!