🎉Are you interested in a Bootstrapping Tools Community? Let me know! forms.gle/4tzvffHiRqS7pVZY6 🎉 For those who want the source code - I made a new version of it that will be much closer to a plug n' play style. Here's a link on how to access it: bootstrappingtools.gumroad.com/l/google-sheets-email-trigger-checkbox UPDATE - July 2024 -- A new video on sending email sequences is out. Make sure to check it out! kzbin.info/www/bejne/hZbNY52GqtljbbM
@cr7mlc2 жыл бұрын
What a modest introduction about himself man said I am a guy called Joe... Nooo Joe YOUR ARE "THE GUY" ! Thanks for the vid
@BootstrappingTools2 жыл бұрын
Haha! Thanks, Christin. Glad you're enjoying the content. Feel free to reach out if there's a topic you would like me to cover.
@vascheduler65432 жыл бұрын
This is a great video! Thank you so much! You saved me from the hassle. :)
@BootstrappingTools2 жыл бұрын
Great to hear you found it helpful!
@user-gw4lz6ln1h10 ай бұрын
How can I add the info from multiple columns to the email ? I cant seem to figure that out.
@yianniv61302 жыл бұрын
I've been looking for this for a long time! Thanks! One question though. What do I change to make it affect any row in the sheet?? It's only sending e-mail if I check of the box in the first row!🤔
@BootstrappingTools2 жыл бұрын
Hi Yianni! Thanks for reaching out. Sounds like you're trying to send bulk emails... in which case, you will need to use a loop to iterate through each row to set up the data and make the send email call. When you do getRange().getValues(), it returns a nested array that you can iterate through to grab the info you need. You may want to filter it down to just the rows you want to send an email for, which you can use myArray.filter() for --- if the checkbox is in the first row, then the filter code might look like this: myArray.filter(row => row[0] == true)
@fadighassan1112 жыл бұрын
This is great.. First tike for me doing such thing. I do have 2 things: What if I have nore than 1 column? Like in my case I have 10 columns that needs to be included and not just 1.. what should I do? 2nd thing I want to add the date of the ticking.. Thanks and Appreciated
@BootstrappingTools2 жыл бұрын
Hi Fadi! If you have a bunch of data you want to include in the email - my suggestion would be to pull the entire row's data and then pick out what you need. For example: let rowData = sheet.getRange(row, 1, 1, sheet.getLastColumn()) ^ what this example basically does is pulls data for the entire triggered row, by referencing the sheet's last column that has data inputted. From there, just pick out what you need by using Array positions - e.g. rowData[0], rowData[1]. rowData[2], etc For getting the date, you can always use new Date() to generate the current date/time.
@fadighassan111 Жыл бұрын
@@BootstrappingTools that's is working but I have one last issue.. For each ticking column there's a purpose column.. so that's 10 ticking columns with 10 purposes.. the issue is that when I tick in any column its always giving the purposes of the first column.. so what I need is this: when tick column #3 I want the purpose in culomn #3 to be sent in the email.. I hope that's understandable and I'd appreciate it if you answer as soon as possible
@BootstrappingTools Жыл бұрын
@@fadighassan111 It sorta sounds like you need to make the columns you're referencing as the "purpose" to be dynamically set based on the tick column. There's probably several different ways to do it, but I would suggest creating a mapping of "tick column" to "purpose column" so that when the script gets triggered and you know which ticked column it is, you can use the tick column to reference the mapped purpose column and then use that to reference the data you want to be send in the email. ^ hope that makes sense!
@malinjayasuriya974 Жыл бұрын
very useful thank you very much also can i know, instead of sending the msg in one cell , can i send data on multiple cells and maybe some as a table ? is it possible to make a template for the email ?
@BootstrappingTools Жыл бұрын
Hi Malin! Yes, it's totally possible to grab multiple cells of data and also send tables in an email. For grabbing multiple cells, you just need to reference the data using the event object returned in the triggered event. e.range.getRow() will tell you which row corresponds to the triggered event. From there, just parse through the array for what you need. My most recent video will teach you how to do that: kzbin.info/www/bejne/nKS4YqSFnbKhd6s For the email, you'd have to throw in a 4th parameter into the sendEmail() function to tell the email that there's HTML in the body. For example: GmailApp.sendEmail(recipient, subject, body, {htmlBody: body})
@cristinapajarillo69565 ай бұрын
Hello! Please I hope you can help me, I can’t get the codes to work: I am working on contracts, where if they meet a certain date (say contract expiration, or 30 days before due ,or 60 days) then custom alert email will be sent to team members once criteria is met. I don’t know if the loop or the if statement is the error here.
@BootstrappingToolsАй бұрын
Ahhhh didn't see this comment. Do you still need help?? You'll basically just want to get the current date with new Date() and then compare it to the 2 dates you want emails to be sent out on. Assuming you already calculate that in the spreadsheet, you can just take the current date and then use a == to compare it against the date value in each row/col you want. Make sure to take the row/col value and wrap it with a new Date() though! Also, I recently released a video that covers this but with dynamic date setting based on a form submission. It should help you here! kzbin.info/www/bejne/hZbNY52GqtljbbMsi=GdasGvYETwoziaSF
@IndianAnimeWeeb Жыл бұрын
Hi, I wanted to use time based trigger so have made some changes, how can I send multiple emails when condition is fulfilled function certificationEmail(e) { var source = SpreadsheetApp.getActiveSpreadsheet(); var sheet = source.getSheetByName("Training Schedule") ; var range = sheet.getRange("F2:F1000"); var row = range.getRow(); var column = range.getColumn(); console.log("column:: ", column); var targetRange = sheet.getRange(row,1,1,10); var targetValues = targetRange.getValues(); console.log("targetRange:: ", targetValues); var recipient = targetValues[0][6]; var dayvalue = targetValues[0][5]; var body = targetValues[0][8]; var subject = "New Certification Request"; if (column = 6 && dayvalue ==1){ console.log("remind qa team") GmailApp.sendEmail(recipient,subject,body); } else if (column = 6 && dayvalue !=1){ console.log("not required") } else {console.log("no clue") } }
@BootstrappingTools Жыл бұрын
If you're trying to send multiple emails meaning more than one - then you just have to call "GmailApp.sendEmail()" multiple times. However, if you're looking to send multiple emails meaning you want more than one recipient, you'll need to add in another parameter and include "cc" in it. Ex: "GmailApp.sendEmail(recipient, subject, body, { cc: "email1,email2,email3"})"
@Bthaler092 жыл бұрын
SOOO helpful! Thank you! I need mine to go a step further and looking for help. I have two checkboxes on my sheet, I applied your script to the first column of checkboxes without issue; however I now need a second email sent to a different email once the second checkbox is marked without the first email sending again. Is that possible?
@BootstrappingTools2 жыл бұрын
Hi Brittney! Yeah, that's totally possible by using some IF statements. For example: if (e.range.getColumn() == your_column1_number && e.value == true) { // only send first email } else if (e.range.getColumn() == your_column2_number && e.value == true) { // only send second email }
@Bthaler092 жыл бұрын
@@BootstrappingTools Thank you! How do I distinguish between the two seperate emails within the code so the right one gets sent once the corresponding checkbox is marked?
@BootstrappingTools2 жыл бұрын
@@Bthaler09 you would just have to name the variables differently. E.g. - emailA vs emailB
@user-hp1hs7oo9z10 ай бұрын
Is there a way to keep formatting from the cell in the email? I typed a message and made the font color on one word red. When the email was sent, all of the font was black. The amount of red font and the message will always be different so it doesn't work to set the font color. Any advice would be greatly appreciated!
@mathewgarrett8142 жыл бұрын
You're the man! Been watching a few of your videos and learnt so much, thank you. A question I do have though; If I had a dropdown instead of a checkbox, and the dropdown consisted of two options, 'Request' and 'Complete', and I only wanted an email triggered when 'Request' has been selected, what would I need to change within the code? I tried changing the 'if' options to this: if(column = 4 && dropdownValue == "Request"), but this hasn't worked.
@BootstrappingTools2 жыл бұрын
Glad you're finding the content useful! An if statement is the definitely the right way to go, but I'd have to see what you have for the rest of the code to see why it isn't working. How are you setting the dropdownValue variable?
@naveenofficial87542 жыл бұрын
How to send Google sheet table particular range into the auto email body.
@BootstrappingTools2 жыл бұрын
Hi Naveen, thanks for reaching out! When you use getRange().getValues() - you can get all of the values from the range you want. From there, getting it into the email body as a table will require you to build a table in HTML using the , , and elements.
@nate86diy432 жыл бұрын
Hey great vid, I love this method of trigger to send an email. I was thinking of having one that specify particularly to a cell that has a YES or NO. Ofcourse if it is yes I want it to send an email automatically, however I have 2 data that are from 2 different Sheets, Can i have 1 function having 2 sheets in it?
@BootstrappingTools2 жыл бұрын
Hi Nate! Thanks for reaching out. You can totally have a function that references 2 sheets - you just need to make sure that you assign them to different variables and use openById() / openByUrl() instead of getActiveSpreadsheet().
@OnlyAgencia Жыл бұрын
Unfortunately it doesn't works anymore. I tried with the new script, with the explaining video, when displaying appsscript.json, can't go back to the main script...:(
@BootstrappingTools Жыл бұрын
When you're viewing the appsscript.json manifest file - do you see other files displayed under it on the left-hand menu/sections?
@aayasir2174 ай бұрын
Can we do follow ups?
@BootstrappingTools2 ай бұрын
You mean like a drip campaign? You could.... it's not covered in this video, but it could be done. Maybe I can make a video on that. Everyone - like this comment a bunch if you'd like to see a video on that!
@BootstrappingToolsАй бұрын
I just released a video on email follow-ups (aka drip campaigns or email sequences). Hope it helps! kzbin.info/www/bejne/hZbNY52GqtljbbMsi=wuelqjx0nCMWDUFn
@marycatherinenaguit669 Жыл бұрын
Hi @Bootstrapping Tools . How do you create an email notification (to a dedicated email) when the cell value is Less than safety stock in the gsheet and it will include thru the notification what item is/are less than safety stock. The gsheet is actually a gform response which we have expanded (2 gforms where responses are combined in one spreadsheet but different tabs). Hope you can help me. 0 knowledge on coding here. Thanks!
@billblakely930411 ай бұрын
Do you have this code in a location where I can cut and paste it?
@kirbynmalabanan26832 жыл бұрын
Hi, do you also have a tutorial on receiving an email notification when a condition is met in a google sheet form responses? let's say when a form responses received a new submission and the value in column A is "New" it goes to a specific email?
@BootstrappingTools2 жыл бұрын
I don't have a specific video on that exact workflow, but there's something similar that has some of the core concepts you'll need to do what you're trying to do. It focuses on triggering a slack message based on a specific field. Here's the link to that video: kzbin.info/www/bejne/n2Kcdp2Pj5ykmM0 I'll also add your request to our content queue -- should be able to get something out for you in a couple of weeks. Please stay tuned!
@kirbynmalabanan26832 жыл бұрын
@@BootstrappingTools looking forward to it... :)
@magdalenelawson2 жыл бұрын
Hi I just purchased your sheet and I'm getting errors. I also got a bounce back when I tried to contact your support team. 'Cannot read property 'source' of undefined' is the error
@BootstrappingTools2 жыл бұрын
Hi there! That sounds like an error you would get when trying to run the script directly instead of through a trigger. Be sure to set up the trigger to execute on an onEdit event type. It'll run off of the checkbox interaction on the sheet from there.
@magdalenelawson2 жыл бұрын
@@BootstrappingTools thanks will give it another go. Would this code work for if I want to trigger an email for unticked checkboxes? also would it possible to reference more than one columns for the body of the text. Won’t mind purchasing another video for this :) Thank you lots
@BootstrappingTools2 жыл бұрын
To have it trigger when you untick a checkbox, you'd have to update the IF statement slightly - e.g. changing "checkboxValue == true" to be "checkboxValue !== true" or "checkboxValue == false" instead. Totally possible to reference more than one column for the body of the text as well. You'll just want to combine it together in the code to have it create your message.
@mahimanasa1 Жыл бұрын
Please guide me how to filter data and delete filtered data using appscript. I tried !isrowshidden method. But due large data taking too much time. Pls teach me
@Mrator20102 жыл бұрын
Good afternoon. Is it possible to make a push notification in the script? I have a table that I am updating. The script sees an unlimited number of faces
@BootstrappingTools2 жыл бұрын
Hi Mrator2010! Thanks for reaching out. Push notifications are tricky since the end-user needs to opt-in to receive those. Do you already have the backend code to handle push notifications? If not, there's a few services that you can use for it. I think that airship handles both native and web app push notifications and they offer a free plan up to 1000 users.
@Mrator20102 жыл бұрын
@@BootstrappingTools Hello. I tried to connect notifications but the link to the script does not connect
@BootstrappingTools2 жыл бұрын
Are you connecting to a separate service that handles push notifications for you via an api request?
@timadesanya3872 жыл бұрын
Can you send HTML emails using this same method?
@BootstrappingTools2 жыл бұрын
Yup, sending raw HTML through the sendEmail() method works just fine.
@tomasgic58572 жыл бұрын
Great content as always :) Question: would it be possible to automate e-mails through the google sheets but in a shared inbox? More specifically, I'm looking to automate forwarding and moving e-mails to folders based on a label assigned to them, but in a shared inbox. I couldn't find any information whether this can be done in the google documentation.
@BootstrappingTools2 жыл бұрын
A shared inbox like a Google Group? I don't think those have their own folders, but each individual person that's a member of the group can set up auto-filters for their gmail inboxes.
@tomasgic58572 жыл бұрын
@@BootstrappingTools yeah like a collaborative inbox in a delegated account shared between roughly 15 people. Not sure how to access it with apps script since for example the MailApp class only takes info from my main personal inbox.
@BootstrappingTools2 жыл бұрын
@@tomasgic5857 Hmm... yeah, the GroupsApp service only lets you manage the group itself and its members but not reference the emails going into the group to auto-apply labels. It might not be possible at this time to automate labels and filters in a shared inbox. If I'm able to figure out a way to do this, I'll let you know!
@billypanaggio1357 Жыл бұрын
Hi, I am having some issues when using checkboxes to send emails. I have multiple checkboxes located in different locations on my sheet that are used to trigger various actions. The problem I am running into is when I have other checkboxes checked and try to check a new one the system defaults to the first box check to engage the trigger. My checkboxes are in fixed locations which I tell the script, and I'm using the onCheckbox edit function, with the conditions being if checkbox value equal true. Is there a way to have the checkbox act more like a "button" to only be check for a short amount of time? Or is there some code to override what's happening with the triggers?
@timenglish80472 жыл бұрын
Can the the trigger be a "Yes" in a cell?
@BootstrappingTools2 жыл бұрын
Hi Tim! Yes, you can totally base the trigger off of the cell being set to "Yes" - you would basically want to modify the If statement so that it checks to see if the value is equal to "Yes" instead of "true"
@eyeoncars7622 жыл бұрын
how to get this code?
@BootstrappingTools2 жыл бұрын
By following along in the video, you'll have all the code you need to send emails through your Google Sheet. ✌️
@zaklara Жыл бұрын
Yall got like a Fiverr link so I can pay you to do this for me???
@BootstrappingTools Жыл бұрын
Hi Zak! I don't do freelancing work, but I totally have faith that you can do it on your own using my guides. Lemme know if you get stuck somewhere!
@appleswagonbrotherealone59032 жыл бұрын
My email is not going
@BootstrappingTools2 жыл бұрын
Thanks for reaching out. What kind of error are you receiving when running the script?
@EvannDAdamo2 жыл бұрын
Hi, I have other scripts that I run that other people use, and I use /** * @OnlyCurrentDoc */ to force allow the script to be trusted. When I add your new code, this doesn't work so I thought to use the Importrange function to get the data in another sheet and run the trigger and script there. But when I run it I get the following error: TypeError: Cannot read property 'getRow' of undefined at onCheckboxEdit. Any suggestions on how to fix this? ( I am using your new version of the code linked in another comment)
@BootstrappingTools2 жыл бұрын
Are you executing the script by making a change on the spreadsheet or through the editor? Since the onCheckboxEdit() function utilizes the event object, you need to trigger it by making a change on the spreadsheet so that the event object is passed through.
@EvannDAdamo2 жыл бұрын
@@BootstrappingTools Thank you for a quick response! I am not super knowledgeable in this area so bare with me... I have everything set up the same as you explain in your video except the trigger is set to "on Change" because "on Edit" wouldn't even show that it was doing anything. When it is set to "on change" I got the error. When I bring it into another sheet using IMPORTRANGE the check boxes are labeled with their text version "TRUE" and "FALSE". When I check the box, FALSE changes to TRUE and it runs, it just fails with the error: TypeError: Cannot read property 'getRow' of undefined at onCheckboxEdit.
@BootstrappingTools2 жыл бұрын
Hmm... onEdit is the event type you'll need for this since the onChange event comes with a different set of information than the onEdit does. If your onChange is firing off, then the onEdit should also. Give that another try.