Academy ↓
Hey there stranger!
Sign up to get access.
Tag Emails From A List of Members in Google Sheets
About this Tutorial
Sheet Resources
Video Transcript
00:13 Now this is a pretty cool script that I didn't even come up with this idea. A friend of mine was looking for an app to help him basically manage his subscribers.
00:23 Like to know if someone is emailing him are they a customer or not, is there a way to tag them?
00:31 And they were looking for assess, they were looking to pay for a service that does this. And I was like, I think you can do this in Google Sheets and you can, I proved it by actually doing it.
00:43 So right now it's running again. I wanted to show you again this script in actuality and what I did to do it.
00:52 So let's start with my inbox <laugh>. I'll show you that. So this is my inbox. Now, I snooze a lot of emails, but here you can see this one is marked.
01:02 Member Chris here may be watching this video. Chris, Jack, you might have you might be a member or not, I don't know.
01:09 By the end of this video, we'll find out if you are spoil alert. I know he is a member. All right what do you have to do to set up a Google sheet with this?
01:20 You need literally an one <laugh>, one column sheet with the named members. You need to do that. And then you need to run the app script that is in this sheet.
01:32 You can copy this sheet down below. All members can copy this sheet and into your Google Drive, and you'll have the app script to run this.
01:40 And I'll show you exactly how to run this. There are two scripts here. One called Function All Members, and then there's another function called add Label to email.
01:49 Okay? So all you have to do is go up here to all members, select all members here, and then click Run.
01:57 You may have to authorize spreadsheet app. You may authorize, you have to authorize Gmail app. That will be in a, a little bit of a few, a couple screens, right?
02:07 Just like you would authorize any other app script. You're gonna have to authorize it. It might not run right away, but once it run, so you might have to hit run again once it's authorized.
02:19 And here's what it'll look like. It'll say how many emails are it's checking. So that's gonna be the email addresses in this member's list.
02:29 So I have 2300, no more than 2300 pay lifetime members. And then there's like 2500, 2700. So there's like almost 5,000 members total.
02:43 But I'm just putting a list together. I'm just tagging those who are lifetime members, right? So we will see how this goes.
02:51 This is already 1700 done. So if I refresh this inbox, there it goes. I don't know if you can tell right Here, here, Chris Jack is a member.
03:00 He's tagged with member. Now, what you might think is you have to create a label member, and you could do that, but in the app script I've put, if the label doesn't exist, create the label.
03:14 So here's something interesting. You can actually change the label if you don't want member. Maybe you wanna tag one, maybe you have two or three lists.
03:25 Maybe you have free members and recurring members, right? Or paid users. Or you have like a blacklist maybe. Maybe you're tagging people that you don't want to talk to anymore.
03:38 That is okay, right? So on this line 19, it says, member, you can change that and you don't have to necessarily create the label, or you could use a label that already exists in your Gmail and just write it here between the quotes, change that.
04:00 The other thing that I would do is if you want to change the label the name of the tab in which you have the list and you literally just need email addresses if an email address doesn't exist, meaning they never emailed you or you never emailed them nothing will happen.
04:19 They, it'll just skip it. It's in here. You'll see. I'll go through the code a little bit and you'll see it doesn't do anything.
04:28 That is it <laugh>. I, if you have any other questions, feel free. Email me, ask me about this members' tag in Gmail.
04:39 I'm gonna walk through with the rest of this video, this script, and share with you line by line what it does.
04:46 If you are more, if you are anxious to try it out, I would try it out maybe with like 50 or, or you could actually try it out with one email address.
04:54 If you're like, I got one email address, does this actually work? Go ahead and try it again. The one thing you would run into is once you hit run, you will have authorization to do, and then you might have to hit run again.
05:07 You will see this ex execution log pop up here. Once you're there again, you're gonna go to Extensions app script.
05:15 Once you copy this email <laugh> email, copy this spreadsheet into your drive. And yeah, make sure you have a list of actual email addresses that are some list you wanna tag, right?
05:28 Again, you might have a, a dummy list or, or a little bit list. It is, let's see. All right, let's go through the, let's go through the abs script now.
05:39 So all members runs, it's looking for the sheet name members. It gets that here. So we just use spreadsheet app, get active spreadsheet, get sheet by name, what's the name members, we are getting the last row.
05:54 And then why we get the last row is we wanna know one, how many emails we have to go through and we present that to the user in this logger.
06:01 That's why it shows up this execution log. And here it's executed. So that took seven minutes, seven minutes, and 10 seconds, which is strange cuz I thought there was like a six minute time limit.
06:13 Okay? So it will literally just give you a message and say, we will check this many emails. You might get an error here if you have zero emails, like if you didn't, if you forgot to put in any values.
06:27 This next line, line six is going from row one to the last row, and it's getting all of the values.
06:34 It is finding how many emails there are, and it's then going through a for loop. And each of those emails that exist, it'll go to the function add label to email.
06:47 It will then, if it's a if it's the hundredth, 200th, 300th, it'll let you know that many done. As you can see here, it shows up zero done, 100 done about 14 seconds later, about 15 seconds later, 200 done 15 seconds later.
07:06 So it's taking about 15 seconds to do each hundred emails. So yeah, I have a lot of 2300 emails in here.
07:14 And it's doing in about seven minutes. All right. What is the ad label to email function? All right. What it does first is it grabs, it goes to Gmail.
07:24 That's the Gmail app right here. And it's searching from, so any emails you get from them, so not if you're sending emails to them, but if you're getting emails from them it's looking, literally it's searching email for that email.
07:43 It's also first, it's doing, it's not doing that yet. It's just a variable right here. We are grabbing the label.
07:51 We're checking if the label exists. So if you rename this for some reason and you want to change it, and you can change it right here, and it's looking for the label, if that label does not exist, meaning this if exclamation point label, then it will actually just go and create the label for you.
08:11 If the messages dot length, so messages is how many emails, how many emails exist from that person? So when it, when it's searched, what is the result?
08:22 If that result is over zero, right? You have a message from them. If it's zero or or less than zero, for some reason, you don't have a message for them.
08:30 So it if, if it's not, if, if you have no messages from this person, it will literally do nothing. It'll just end and go to the next email.
08:40 But if it does have an email from that person, it goes through all of those messages and it adds a label.
08:48 So this is messages J here, this j is iterating through however many messages are from That person, and it adds a label.
08:57 Gmail app dot get user label by name tag is here, remember? And that's it. That is the entire script here.
09:05 That is the entire script that's running to do this. And we're just iterating through however many you have. So you have an entire list.
09:12 You can have the, you can type 'em by hand. What I do is I just download a CSV file of all of my paid members lifetime members, and I put it here.
09:23 I copy, I copy paste that csv or upload that csv or import that CSV here. You can do it a number of ways.
09:30 If you have, if you have say, new a newsletter and you're like, I want to tag any newsletter subscribers that, that email me back.
09:37 You know, a lot of times I'm using this because I just wanna show up in my email who's, who's a lifetime member so I can reply to them if I want to prioritize that, right?
09:50 If I'm running a little behind on the day or the week and I'm like, ah, I'd rather prioritize lifetime members in my responses.
09:57 If it gets a little much then I'll go and tag 'em and I'll see, oh, then I can reply to all of them.
10:05 Or if I'm doing market research, if I'm wondering like, oh, are people asking for this kind of thing? Like, oh, are are free members asking for one thing versus lifetime members asking for a different thing?
10:17 That does happen, by the way. I get a lot of reque, I get an incredible amount of requests for beginner content, but I find that the beginner content is a hundred percent, not, not even 99%, like a hundred percent geared towards people who are not yet willing to pay or willing to like, do much, I don't know how to say this.
10:40 Studying like, they want to be pointed in. They don't want to just be pointed in the right direction. They want everything spelled out for them.
10:46 And Better Sheets, I don't think is really meant for that. I think Better Sheets is meant for like, you've been using Google Sheets a long time and here's some more creative ways to use it, right?
10:54 Here's some deeper ways to use it. That goes beyond what you already know. You have to already know something <laugh>.
11:00 So I'm not trying to go to beginners and that was really helpful to know in my emails. Oh yeah, like this con the kind of content or the kind of questions I'm getting from you all from members is very different than what I'm getting from people who are not members yet, right?
11:18 On forums we have I love Google Sheets over, we're on Facebook group, there's Reddit communities. Very different questions are coming there than through, through email.
11:29 So that was really helpful to know. So hopefully you have a use case for this as well. If you have a use case beyond what I just told you of like tagging paid members let me know.
11:40 I'm happy to share that and, and, and share whatever you're doing with the greater community with other better sheet members.
11:47 Again, all you have to do is, let's just summarize this video. You just have to go to extent, Copy the sheet, go to Extensions app script.
11:56 You can run the all members. That's all you have to do. Maybe ha you have to authorize you have to put a list of email addresses in the a column of the members tab.
12:05 If you want to rename the label, you can do so on line 19 here. If you wanna rename the sheet, you're gonna have to rename it on line two.
12:14 So 19 line 19 is to rename the label. Line two is to rename the sheet. So maybe you have Lifetime here instead of members or paid or or some email newsletter, right?
12:29 Newsletter, subs, maybe of subscribers instead of members or users, right? Rename that on two and then rename the label that you apply to them in your Gmail over on 19.
12:42 That's it. Let me know what, let me know what issues you run into, what problems you run into. I'm happy to help you walk through them and get better at this bite.
Courses
Add Icons To Your Sheets With a Domain Name
00:04:21
Convert Google Sheets into a REST API
Add Tasks to Google Tasks From Google Sheets
Build a Book Recommender with AI in Google Sheets
Build a License Key Generator and API in 20 Minutes
Create Your Own API by Deploying a Web App
Access Free API's with Google Sheets
Use Random Quote API in Google Sheets
OpenSea Data Inside Sheets
Tag Emails From A List of Members in Google Sheets
Spreadsheet Automation 101 Lesson 4: Access APIs Introduction
How to Validate Email Addresses
Build Your Own Free Domain Availability Checker
Build a Job Status Checker API in 15 Minutes
Spreadsheet Automation 101 Lesson 4: UrlFetchApp
Spreadsheet Automation 101 Lesson 4: OmdbAPI get ApiKey, get Data in URL
Spreadsheet Automation 101 Lesson 4: OmdbAPI Parameter Picker
Spreadsheet Automation 101 Lesson 4: OmdbAPI get data in Apps Script
Spreadsheet Automation 101 Lesson 4: JSON (beautifier) and OmdbAPI parameters
Quickstart Tutorial OpenAI API in Google Sheets
Shaman: AI Blog Writer
ChatGPT Clone in Google Sheets Part 1
ChatGPT Clone in Google Sheets Part 2
How To Set Up Stripe Webhook to Google Sheets with Google Script
00:22:10
Create Instant QR Codes
Access the Dictionary API to get Definitions for Words in Google Sheets