Automate emails with Google Sheets
Vložit
- čas přidán 27. 07. 2023
- How to send emails with Google Sheets
This video will teach you how to:
1. Access data from a sheet in a Google Sheets workbook
2. Write Google Script code in Google Apps Script to collect the data
3. Write HTML code to send an email with the data from the sheet
Here is a copy of the workbook used in this video: docs.google.com/spreadsheets/...
The code is this tutorial uses native Google Script methods and functions. If you would like to learn a more stable data collection method, then check out this video: • Automate data collecti...
Hi Matt,
Your videos are great! I like that you also predict errors and explain them at the moment. I would like to automte emails just like this video but I need to send the email to multiple candidates. The email would found at a cell on the same row as the "name", "user" as them. How could I make this work. I know HTML but I'm new to appscript language.
I appreciate the help!
Thank you for such kind words - it's greatly appreciated. I think this is a wonderful idea for another lesson, which I will put together. In the meantime, this video has some code that might help you get what you need. Let me know if it does not and I can tailor the lesson to your situation: czcams.com/video/8VXSXQ-RMI0/video.html
You're the bomb, thanks, I needed this for work
I'm glad to hear this video helped you!
By any chance can you share the code?
Thanks! You rock Sir!
thank you!
By any chance can you share the code?
Thank You!
You're welcome!
Is there anyway you can get the script/function to run, for an example, every third day? Or is google sheet scripting an absolute manual action?
the triggers section does not have that type of option. its functionality is limited to minute, hour, day, week, month, etc.
i have never tried this but you might be able to create a function that runs every day and keeps count of how many consecutive runs it’s made. so it would work like:
var max_run = 3;
var run_count = 0;
if(run_count == max_run) {
sendEmail();
}
else {
run_count += 1
}
i can mess around a bit and follow-up. feel free to email me if you wanna hash it out
@@mattbrigidi How would I make it send every day?
Thanks, Matt! Great stuff.
I am getting an error in the Apps Script that says, "SyntaxError: Unexpected token '='
main @ Code.gs:29" but I cannot find an error. I have copied your code line for line to test it out before making changes suited for my needs. Any idea what might be causing this error?
i’d be happy to take a look - feel free to send me an email or drop your code here
Could you please create a video demonstrating how an HR team establishes automation for sending out National Day email announcements? The goal is to have these emails scheduled automatically throughout the year. Is it feasible to accomplish this using Apps Script? Thank you
This is a great suggestion - I'm happy to look into it
I have some data and i want to send email when there is a new row added i mean want to send only to last row of data. How canni do any suggestions
good question - i’ll play around with it and see if i come up with something. i think it’s viable using the getLastRow() method and the onChange trigger to get the script to run
@@mattbrigidi I'm here for this too! Following thread. 👀
what are you trying to do?
do you want an email with the data from the last row? or do you want to send an email to an address that’s included in the data added to the last row?
@mattbrigidi I'm here to follow this thread, too. Really appreciate these GREAT videos. 👀
FYI, I'm only getting the first row to show up. When I enter the following it only brings up the first row.
var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getDisplayValues();
Maybe there's another way to set the range.
@mattridgidi thank you SO much for the fix. I implemented the code adjustment you suggested, and it worked! Here's the adjustment in case anyone else needs it.
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
Hi, can this email automation be done from google sheets to outlook?
i’m not sure! i’ve never tried sending to outlook - i presume it would work? did you try it and receive an error?
How would you schedule them and have them go row by row?
there are a couple different ways to approach it. one huge consideration is making sure that you do not max out any quotas.
how many lines would you be dealing with? also, would each line have a different email or would this be a mailing list receiving the same info?
@@mattbrigidi 50 rows and 50 different emails
Last part I'm unable to make it close, it kept showing Syntax error: SyntaxError: Unexpected end of input line: 38 file: Code.gs.
Possible to advise?
sure! you can send me an email with your code and i can take a look
Hello, i want to send each column to a different email address, could you please help me?
I'm always happy to help - feel free to reach out using the email in my bio. I would say that using columns instead of rows might cause you longterm problems; but I'd be happy to learn more about what you're trying to do and help as best i can
How about Danny. is it going to send 2 rows?
I believe this code example only sends emails to the first row of data (its been a while since I've recorded this lesson). If you would like to receive the most recent row of data then you can change the getRange portion of the data variable to the following:
// access the data in a variable
var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getDisplayValues();
then you'll have to change the data points to the following:
// define data points
var name = data[0][1];
var inventory = data[0][3];
var grossRev = data[0][4];
var netRev = data[0][5];
let me know if this doesn't answer your question and i'll be happy to work through it with you. my email is in my about section if you want to reach out there
@@mattbrigidi Thank you for your help. I would like to ask, what in case, if I want so send email to the dynamic list of contacts. I have filter in my google sheet and when a new record is created I get the list of values in specific column. When a new record updated, I would like to send email to the dynamic list of recipients ( 3-5 Rows). How to make it?
hey @@veronikaholazova - i wonder if this video on mailing lists is something that might help you? czcams.com/video/nGpOmOif20I/video.html
let me know if that doesn't provide you what you need and we can explore a solution together
No signature with graphic?
you want to add a signature with an image?
@@mattbrigidi obviously...otherwise the email is not complete.
i'll try to make a video about images in email
Can I do follow ups?
depends on what you would like the experience to be - are you envisioning a situation where you:
1. automate an email
2. follow-up if there is no reply
@@mattbrigidi That's exactly what I need.
By any chance can you share the code?
once i figure out a way to do it!