Who Can I Email From a Google Sheet

0:00 One of the most interesting things you can do from a Google Sheet is you can email people. You can go to Extensions, Apps Script , open up Apps Script , and we can create an email function . This uses gmailapp.sendemail, and there's three things we need. We need someone to send it to, we need a subject, hi, and a body, hello. Could be very few text, or it could be even text from a . The sheet itself, if you want to call any particular cell or reference , I wanna focus on this email or two and say, who can you email? You could email the owner of the sheet. We get that by going to spreadsheet app dot get active spreadsheet , get owner , get email.

0:49 because I created this spreadsheet , I'm the owner , but if you're creating Apps Scripts in, say, your boss's email or a company sheet that someone else made, this is going to be the owner , instead of just sending an email, you could just log that to see who the owner is, and select email here, click run. And it'll log the owner of the spreadsheet itself. With Apps Script we can also get the active user. So the active user is whoever is actively using the script at the time, or if you create a trigger, then whoever created that trigger. So that's session.getActiveUser.getEmail. So right now it's going to be me, because I'm actively running this script. Right now there you see

1:34 but also you could type any email you want. We can just say variable email equals Andrew at better sheets Taco and that will email whoever you write their email address here We can also email multiple email addresses How we do that is just add a comma in here and we can say Andy at gmail.com Any number of Andrew at campy.com Anywhere here. Just in text, in quotes, all as one. You can email everyone there. And that would send an email to all three of those recipients in the to field . You could also reference any email in a cell. So we can create a sheet called emails. And we can go andrew at bettersheets.co And we're going to want to go back here, go to emails.

2:21 Spreadsheet app dot get active spreadsheet . Get sheet by name. Emails. dot get range A1 dot get value. We can shorten this spreadsheet app here to variable SS equals this and then do SS. So there we get the value of whatever's in that cell. Let's save it and run it and we get the value in that cell on the emails tab in A1. Another thing we can do is get all of the editors of the spreadsheet . Variable emails equals ss.getEditors. And this will give us a list of editors. So what we need to do is go emails.forEach. And create a little function here, editor is logger.log editor .getEmail. There, I'm just going to comment out the actual sending of the email now. And so for each of the items in this array, we're going to log each of the emails to run, and it's only me right now, but that's all of the editors. In addition to that, you can get all of the viewers and this. is up to certain permissions. Sometimes we don't have permissions, obviously as the owner of the sheet. And if I share it with maybe only people in my domain, I'm going to be able to see all of the emails of every viewer , but that's not the case all the time. It's pretty complicated set of permissions, but we'll do get viewers emails for each. And we'll just change this to viewer , viewer , get email. And I think in this sheet, I've shared it with another

3:52 account on a different domain. So let's hit run and you'll see there's two people here. So the editors didn't include the second email, but the viewers does. And I think one of the most interesting ways to get emails and email someone from a Google sheet is to , get them in a form response. So let's go up to tools, create a new form. I can create a question here, it's called email, and I can add a description if I want to add more information. Add your email address to get a free download by email. So like maybe you create a form and you want to email those people that give you their email address. We can create a form response here called email. We'll publish this

4:38 and go into our function . Actually, we're going to create a new function here. Let's call this function emailFromForm. And we're going to need some type of variable here that's going to be the form information. Because we're going to add this as a trigger . We're going to say form, this is just this text here, dot Named values and in brackets, email. This text email is exactly the name I put here. And we can say, logger log this email or we can email here. We can say Gmail app. Do send email. I can send an email to that person if we want. I'm gonna comment that out right now. But in order for this to work, this function to work, we need to do one

5:25 more thing, which is set up a trigger over on the left side, triggers. Just takes a couple of clicks, add trigger . We're going to choose which function to run, email from form. We're going to select event source from spreadsheet , but the event type is on form submit , click save. We'll have to authorize it once we add this trigger . And now we have our trigger . I want to see our executions. We will go and enter an email address here, hit submit. On our sheet, in form responses one, there's our email address. And in our executions, we have a function email from form, and there's our email address. So now we can email, on form submit, anyone who has given us their email address. I think that's really fun to give Google Sheet access to, to give extra

6:15 information, to ask more questions if you're doing a survey response. Really cool stuff to do from form responses to emails. And that was all. All of the people you can email from a Google Sheet. Hope this was helpful and interesting to you. If you're looking for more in automations, Apps Script , or just getting more out of your Google Sheets, subscribe here on YouTube to better sheets.