How to Automate Emails Through your Google Sheet

[@BootstrappingTools]

🎉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

[@cr7mlc]

What a modest introduction about himself man said I am a guy called Joe... Nooo Joe YOUR ARE "THE GUY" ! Thanks for the vid

[@BootstrappingTools]

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.

[@vascheduler6543]

This is a great video! Thank you so much! You saved me from the hassle. :)

[@BootstrappingTools]

Great to hear you found it helpful!

[@user-gw4lz6ln1h]

How can I add the info from multiple columns to the email ? I cant seem to figure that out.

[@yianniv6130]

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!🤔

[@BootstrappingTools]

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)

[@fadighassan111]

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

[@BootstrappingTools]

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})

[@cristinapajarillo6956]

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"})"

[@Bthaler09]

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?

[@BootstrappingTools]

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 }

[@Bthaler09]

@@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?

[@BootstrappingTools]

@@Bthaler09 you would just have to name the variables differently. E.g. - emailA vs emailB

[@user-hp1hs7oo9z]

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!

[@mathewgarrett814]

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.

[@BootstrappingTools]

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?

[@naveenofficial8754]

How to send Google sheet table particular range into the auto email body.

[@BootstrappingTools]

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.

[@nate86diy43]

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?

[@BootstrappingTools]

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?

[@aayasir217]

Can we do follow ups?

[@BootstrappingTools]

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!

[@billblakely9304]

Do you have this code in a location where I can cut and paste it?

[@kirbynmalabanan2683]

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?

[@BootstrappingTools]

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!

[@kirbynmalabanan2683]

@@BootstrappingTools looking forward to it... :)

[@magdalenelawson]

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

[@BootstrappingTools]

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.

[@magdalenelawson]

@@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

[@BootstrappingTools]

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

[@Mrator2010]

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

[@BootstrappingTools]

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.

[@Mrator2010]

@@BootstrappingTools Hello. I tried to connect notifications but the link to the script does not connect

[@BootstrappingTools]

Are you connecting to a separate service that handles push notifications for you via an api request?

[@timadesanya387]

Can you send HTML emails using this same method?

[@BootstrappingTools]

Yup, sending raw HTML through the sendEmail() method works just fine.

[@tomasgic5857]

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.

[@BootstrappingTools]

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.

[@tomasgic5857]

@@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.

[@BootstrappingTools]

@@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?

[@timenglish8047]

Can the the trigger be a "Yes" in a cell?

[@BootstrappingTools]

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"

[@eyeoncars762]

how to get this code?

[@BootstrappingTools]

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!

[@appleswagonbrotherealone5903]

My email is not going

[@BootstrappingTools]

Thanks for reaching out. What kind of error are you receiving when running the script?

[@EvannDAdamo]

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)

[@BootstrappingTools]

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.

[@EvannDAdamo]

@@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.

[@BootstrappingTools]

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.