@SheetsNinja wow. I am not sure why such an incredible piece of "how to software" isn't amongst the first in searches. You are good Mr Ninja. I use Google workspace and app script to develop solutions but no where close to your capabilities. I am pleased and hope to meet you someday.
I have this edited script actively running on my sheet now and it is fantastic. Quick question though, how would I add an automatic reminder email that sends X number of days after the specific status drop down was selected (i.e. I select the dropdown "Request Sent" and it sends an initial email (already set up in script) and then 7 days later it send a reminder email automatically)? I would also like to set up a similar automatic email when we are "X" number of days away from a specified due date I entered on the row if possible.
Hey very helpful thank you for taking the time. Question if have multiple tabs, is it possible to have this script specific for each tab? I have multiple project and I want to have tasks per project which are per tab.
Yes, you can essentially use an if block for each tab, so the if with the curly braces, you can do one for each tab, e.g. If (tabName == tab1) { // Tab one notifications } If (tabName == tab 2) { // Tab 2 notifications }
Now that Google Business has conditional notifications, can this be implemented easier without the script, but still using specifics from the dropdown menus?
So the conditional notifications that Google implemented for the business accounts can make simple notifications easier, but it doesn't give you any customization on what gets included in the notification or how it's formatted. You can see a quick walkthrough of how to set up Google's notifications here: support.google.com/docs/thread/278360807/google-sheets-new-feature-conditional-notifications?hl=en
E is the event object so when you use the native onEdit(e) function or something else like checkMySheet(e) and add a trigger, the event object is what contains some information about the edit, for example, the cell that got updated, the sheet, etc. When you authorize the script, it's normal to have that error because the script is expecting 'e' to contain some things but when you run it in the script editor, it won't.
Hi. How to add a notification that says "Please enter email" before sending the email? Condition is if the cell is set to YES, then it sends an email. but would like to not proceed to send an email if the email address is empty.
With the way it's set up now, I would add this piece of code right in each of the email sections (e.g. follow up email & ready to start email) before anything else in that if statement that would look like this: if (data[6] == '') { SpreadsheetApp.getUi().alert("Please enter email and try again."); sheet.getRange(row,7).clearContent(); return; } I add the line to clear the status so that way after they enter the email, they can select that status again and then it will run correctly.
The link provided in the description allows you to make a copy of the Google Sheet to your Google Drive. If you are on Google Workspace, you may have an error if custom scripts are blocked. If you sign into your personal Google Account, you can get around the restriction that way.
Wow. This worked like magic. Super thanks. Do you have a video on mail merge?
Yes, I have a video on mail merge here using Google Docs as the mail template: ua-cam.com/video/npQLogxgJrw/v-deo.html
@SheetsNinja wow. I am not sure why such an incredible piece of "how to software" isn't amongst the first in searches.
You are good Mr Ninja.
I use Google workspace and app script to develop solutions but no where close to your capabilities. I am pleased and hope to meet you someday.
@SheetsNinja can I get your email from your website for personalised conversations?
I have this edited script actively running on my sheet now and it is fantastic. Quick question though, how would I add an automatic reminder email that sends X number of days after the specific status drop down was selected (i.e. I select the dropdown "Request Sent" and it sends an initial email (already set up in script) and then 7 days later it send a reminder email automatically)?
I would also like to set up a similar automatic email when we are "X" number of days away from a specified due date I entered on the row if possible.
This video just saved me alot of stress.. Thank you.. I have liked and subscribed😊
Awesome! Thank you!
Hey very helpful thank you for taking the time. Question if have multiple tabs, is it possible to have this script specific for each tab? I have multiple project and I want to have tasks per project which are per tab.
Yes, you can essentially use an if block for each tab, so the if with the curly braces, you can do one for each tab, e.g.
If (tabName == tab1) {
// Tab one notifications
}
If (tabName == tab 2) {
// Tab 2 notifications
}
This is great! What do I need to remove if I don't want any data from the columns pulling into the email, just the notification email sent?
You can just type what you want in the htmlBody part of the sendEmail section, and don't need to include any of the template literals: ${}
But first of all do I have to do that table or not ? They are giving me error in codes
Now that Google Business has conditional notifications, can this be implemented easier without the script, but still using specifics from the dropdown menus?
So the conditional notifications that Google implemented for the business accounts can make simple notifications easier, but it doesn't give you any customization on what gets included in the notification or how it's formatted. You can see a quick walkthrough of how to set up Google's notifications here: support.google.com/docs/thread/278360807/google-sheets-new-feature-conditional-notifications?hl=en
checkMySheet(e) - what "e" mean? tried to apply to my sheet but there's an error in e.range?
E is the event object so when you use the native onEdit(e) function or something else like checkMySheet(e) and add a trigger, the event object is what contains some information about the edit, for example, the cell that got updated, the sheet, etc. When you authorize the script, it's normal to have that error because the script is expecting 'e' to contain some things but when you run it in the script editor, it won't.
It's Tooooooooooooo awesome, it solves my problem now.
Awesome, so glad this video was helpful for you!
Hi. How to add a notification that says "Please enter email" before sending the email?
Condition is if the cell is set to YES, then it sends an email. but would like to not proceed to send an email if the email address is empty.
With the way it's set up now, I would add this piece of code right in each of the email sections (e.g. follow up email & ready to start email) before anything else in that if statement that would look like this:
if (data[6] == '') {
SpreadsheetApp.getUi().alert("Please enter email and try again.");
sheet.getRange(row,7).clearContent();
return;
}
I add the line to clear the status so that way after they enter the email, they can select that status again and then it will run correctly.
@@SheetsNinja works like a charm. thank you so much.
how about if there's another column where another criteria needs to be met?
On my sheet, we have hidden columns. Would you count the hidden columns in the array?
Very cool video, this is exactly what we were looking for.
Yes, you would need to count the hidden columns as well.
Not able to download
The link provided in the description allows you to make a copy of the Google Sheet to your Google Drive. If you are on Google Workspace, you may have an error if custom scripts are blocked. If you sign into your personal Google Account, you can get around the restriction that way.