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.
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
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
Just wondering, am i able to do the inverse of this? Such as send an email via python automation script with data in the email body to update a cell in the google sheet?
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.
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?
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, 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?
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?
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?
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)
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.
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.
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 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?
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.
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
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.
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.
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.
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!
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.
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
what if we have two tab sheet? how to make variable for one of that tab sheet
what if you have different sheets, and only on one of them is where this action will happen?
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
Just wondering, am i able to do the inverse of this? Such as send an email via python automation script with data in the email body to update a cell in the google sheet?
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.
The trigger emails sent based on condition met are in loop. The emails are not sent separately. Please advise.
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?
this was really useful, thank you for creating it
Hello Sir, Can I make a request? Can you make the triggers based on expired date? Thanks...
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
do you have to add the gmail api under services? I have similar code but dont receive an email. did all the normal troubleshooting.
hello sir how to add multiple receiver email
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?
Dear Jospeh,
Would it be possible to do this but with a pdf attachment? Thanks
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.
THANK YOU SO MUCH FOR YOUR VIDEO!
It was extremely helpful!
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
is there a way to combine the content in two rows to create the project name?
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?
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?
I have a doubt i need to clarify can we connect is there any possibility?
Great!
Love this. Thank you!
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)
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 for sharing this script but I couldn't get the message box to appear
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!
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 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.
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
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.
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, 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
thanks
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!