Go from nothing to a working PR agency. Capture sites, and Send email via script
Academy ↓
Hey there stranger!
Sign up to get access.
Create a PR Agency From Scratch
About this Tutorial
Sheet Resources
Featured Formulas
Video Transcript
0:00:00 All right. So this is going to be a really fun, interesting video. Oh my P fun. For some people I'm going to build from scratch a brand new sheet, just started sheet.new here wrote down some notes here for this little intro, and we're going to create a PR agency from scratch.
0:00:22 We're going to create basically a sheet that allows us to capture publications, uh, sort of a modern publications we're going to, which are newsletters, uh, podcasts, uh, blogs, interview, uh, websites.
0:00:42 We're going to be able to catch, capture the title and the URL of those very easily. As we go around and find them we're going to curate them, meaning we're going to categorize them, add categories to them based on our clients, uh, or even potentially future clients, because there are two business models that I can perceive from this one.
0:01:00 You can sell this as a search. You can use this kind of sheet as a service where you are a PR agent, or you are someone who curates content or curates contacts.
0:01:12 Uh, but you can also sell, uh, the sheet access to the sheet itself. So instead of doing sort of the creative brief and figuring out, you know, how do people, um, fit and do the manual work of, uh, creative ideation fixing, uh, putting the puzzle pieces together and then doing the outreach, you could actually just sell access to the sheet and you could say, Hey, I update this, you know, once a month.
0:01:39 Um, and you know, for a subscription of 50 to a hundred bucks, you get access to the sheet, uh, all the time.
0:01:48 So we might be doing things. We might be adding categories that we don't necessarily work in, but others might want to know about.
0:01:56 So we can do that. Uh, we'll also contextualize, which is meaning we'll add an area for notes. Uh, we also need to, uh, create a way for clients to work with us.
0:02:10 So we'll get something like, we'll start doing this, we'll get the title here, we'll get a URL. We'll get also the type with this.
0:02:20 We'll use a bookmarklet for this curate. We'll add categories and I'll show you a really cool thing here, basically. Um, there's a certain way people will think of adding categories and I'm going to show you a really fun other way to do it.
0:02:37 Uh, and it's going to be pretty complicated, but it's going to be really cool. Basically. We're going to join. We're going to, um, the basic idea is it's going to be a huge dropdown menu, but not the way you think of it.
0:02:48 I think, uh, and we're going to use join as well. I think here, um, to make it easy to read.
0:02:56 Alright, contextualize, we're going to add note, section add notes, and we also definitely need, we might add, sorry, add, uh, clients.
0:03:11 We might add clients here in this section, in the contact. We definitely want to add a contact info. We might want to keep track of, uh, if it's up to date up to date question mark, um, and we want to deal with outreach.
0:03:31 Now, this is a hard one to think about. Now at the beginning of this, uh, this will probably be about an hour long that I'm working on this and we're only about four minutes into it.
0:03:43 So this outreach part, we may or may not get to find out. You might already know if you're watching this somewhere else.
0:03:52 And there was an intro before I started talking, Ooh, this is exciting. What are we going to get to? All right, how are we going to do the capture?
0:03:59 So that's, that's the idea. Um, let's go through that again. Summarize, we're creating a PR agency where we can capture anything like newsletters that do interviews or podcasts that do interviews.
0:04:10 We can capture them into a sheet, tag them as what their type is. If it's a podcast newsletter, we can then add categories based on the type of content they do, or the type of interviewees they have, or the kind of people they're looking for.
0:04:24 We might also actually add here, uh, let's do this add submit button and link. So we'll need a link to say, Hey, yes, here's the main page of the podcast or the newsletter, but here's their submit.
0:04:40 But here's how to actually submit them just in case one. If we are running this as an agency, we want this information easy for us to access, but if we're selling access to this sheet in this other business model, we want someone else to be easily.
0:04:53 Click that button. Um, contextualize, add some notes, add clients. If we are using this as our internal command center or database, we want to add clients as they come.
0:05:05 We might want to actually also add a client page checklist. So in that case, what we would do is say, create a new tab in my mind.
0:05:17 Now we would create a new tab and then be able to add these newsletters, podcasts, and press from the main database into this checklist, with all that information.
0:05:32 We'll see how that goes. And then we definitely want to add contact information. Once we capture our, uh, press, we want to make sure we have contact information.
0:05:43 We want to make sure it's up to date and potentially a checklist to outreach. So what you might think of as the first step, here's our roadmap.
0:05:55 And here's where we're going to start. The very first step most people will take is they'll start writing title, URL type.
0:06:06 They might add a drop down menu here, list of items that might be okay. We're going to look at podcast newsletter, blog, text, interview, that kind of thing.
0:06:23 They'll add all these kinds of stuff, right? Round up, maybe, uh, I don't know, say, okay, here are, here's our type, here's our title.
0:06:31 We'll just copy and paste titles and URLs in here. We can do this better. What we can do is use a bookmarklet.
0:06:37 We don't even need this page yet. You can go delete this page. What we need to do first is go up to tool, create a new form.
0:06:50 We're going to call this submit to PR. First question we're going to have is title. It is great. It automatically makes it a short answer.
0:07:01 We're going to duplicate this because it's easy URL. It should add. We don't need our data validation. We could do response validation.
0:07:12 We don't need that right now. And we need type. Now this is going to be dropped down. We're going to do podcast newsletters.
0:07:28 Um, blog interview is going to be different than like a text interview. I think, um, we might want to add a few other things we might add, come up with other ideas, like article or feature article.
0:07:44 If it's a feature article about a person, and then we just want to take, uh, whoever that person writes about.
0:07:49 We have some clients that are like them, and we want to outreach to that writer. We also might just find one writer or author who Does our industry.
0:08:01 So we might want to add them. These are the sort of the types of things that we're going to be rounding up.
0:08:05 Okay. That's it for the form. If we go back to our sheet, we have a form responses. I'm going to call this submissions.
0:08:15 Now what you could do is this could be great for crowdsourcing. If you wish to open this up to other people, if you have maybe a group of people who are trying to create a little PR agency and you want to capture information on capture these, uh, types of press, um, that's it press, okay.
0:08:39 Where does the easy part come in? How, how is this at all easier? How is this at all better? Um, and I'm going to show you through creating a bookmarklet.
0:08:49 Okay. The first step we need to do is there is get prefilled link. We need to get a prefilled link.
0:08:56 We want title to be, you know, something like the hustle, your L of the hustle, something like this. So we're just going to do this once.
0:09:12 We're going to choose type pod. No, not pockets newsletter. We're going to get link now. Copy link. Okay. We have this link.
0:09:21 We're gonna do let's see text. Okay. Actually, we can do it in Google docs. Let's just paste this link. I'm going to show you, there's a URL here before.
0:09:39 There's also this entry. We need this entry, um, and this entry. So this is the entry we're going to do this right here.
0:09:47 We're going to do entry for title. We're going to do entry for link, URL, and entry for type right there.
0:10:04 Okay. We got that. Now we get our bookmarklets. So if you are a better Sheetz member, there is a video called bookmarklet or it with the word bookmark, Linnet.
0:10:14 This is, uh, for available for every, um, this code and how to use it is available to every member. So go ahead and check out the video called bookmarklet.
0:10:29 All right. We want our URL here. One to replace our URL. We want, uh, the HRF is going to be the URL.
0:10:51 And again, I go over this in the video. The title is this so entry and that should be everything that should work now.
0:11:06 All right, let's see. Um, one second. He checks him. All right. Again, I go over the video in the video.
0:11:15 Bookmarklet how to do this, but we're going to go to our bookmark bar. Can I add this up here at it?
0:11:35 Okay. Now we should be able to go to the hustle. I spelled that wrong. And if we hit the press button, there we go.
0:11:48 We now have the name and the URL immediately in there. And we can edit this. If want the hustle. This is a newsletter.
0:11:56 We hit submit. That was easy. Hit submit. I don't know if that button or go back to our PR agency and there we go.
0:12:04 We have the hustle. So if we, we can close all of these windows and now anytime we go to say, um, morning brew for like, oh, we can access the more we can submit press to the morning brew.
0:12:22 We hit press there's morning brew. There's the URL. It's a newsletter we hit submit. And then we go back to our sheet and there it is.
0:12:32 So this makes it super easy to capture this kind of URLs into a Google sheet and then contextualize them with the type.
0:12:41 All right, let's put a little check box to make sure we're getting all our boxes in a row and increase the size of this one.
0:12:53 A big old check box. We captured now to curate. So now that we have our submissions, one, a couple of problems come up very consistently in Google sheets.
0:13:07 One, we might want to add our information right along here and that's okay. Uh, just know that every time we add a new entry, it will insert a row.
0:13:19 So actually you can see this by deleting these rows And then going to something like let's find another newsletter. The land of random is a fun, random newsletter that Dave Nichols runs very cool.
0:13:36 I'm always wanting to submit stuff to him. I like this kind of stuff. You're doing anything weird and nerdy. That's my press agency, helping people do weird and nerdy stuff, LAN and random.
0:13:50 This is a newsletter. Uh, we definitely want to start our story. Let's get some more in here. I already have better.
0:13:58 Sheets is a story on here that you can check out how I'm doing. Uh, we want to add to press.
0:14:10 This is we will call it a text interview. We know what they do, not really a blog. See, now we have these rows here where we had no rows before.
0:14:24 So what a form is doing is inserting a row. So just knowing that will help us understand what we need to do next.
0:14:33 We need to, I'm going to do a little quick and dirty way of doing this. Um, I don't really recommend it, but what we, what I do recommend is that you create another sheet and we use a REA formula to get submissions, Um, B to D, there we go.
0:14:59 And this is how I started recommended is always having a separate page. Let the form be its own page. Um, for a variety of reasons.
0:15:10 Um, you, you might want this data in two places, this array formula, you might want to do things like sorting.
0:15:22 You want to do that separately. You want to always add things on other pages. I don't know. It's not sure exactly how to describe why this is the way it is.
0:15:35 Um, what we want to do here is we want to add categories. We want a dropdown menu and join. So the number one way that people are going to add a category is this, they're going to grade category.
0:15:49 They're going to say, um, data validation, you're going to do lists of items. And they're going to say a startup.
0:15:58 These are sort of key. Keywords are ways that we can categorize this based on our clients. If we, if we get a new client, that's a startup, we're going to want to filter this list by just startups.
0:16:08 And we want to know who works well with that financial finance, financial, new news, um, Funding. We might have a category of founders are women only, or feature women.
0:16:30 We might want to. Sheriff's sort of the different categories here. We can add more, but this is the typical way.
0:16:38 This is the typical way people do it. They will say, save, they'll take this drop down menu. And now we have the one problem we run into is that there's only one, there's only one category here and great.
0:16:50 We can sort of, this gets really messy. If we take this drop down menu and my arrow key doesn't work, we paste copy and paste.
0:17:05 Um, and we have category two, right? This, this gets really, really messy. We don't know. Should we put start-ups here?
0:17:15 Should we put blanks? Um, should we, yeah, this gets really messy. And I don't like it. And also it's very constricting because we can only have one category per thing, but we really have, it's really keywords.
0:17:31 This is really keywords that we want to be able to filter and sort through. So what we need to do is we need to turn this on our, on its head.
0:17:41 I'm going to delete this column actually, and do something else. What we will do is add a new page. We will call it keywords or categories.
0:17:54 We can it's either one. We will write startup up here. We'll write, um, news, maybe daily news, even. Cause there might be a niche news.
0:18:07 I don't know. There might be others that we want to do that have submissions. And now this is going to be the fun part.
0:18:15 We are going to do the same thing we did before data validation, but we're going to do list from a range.
0:18:21 We're going to go back to our original sheet and use titles as our range. We're going to hit. Okay. We are going to definitely have a down menu.
0:18:29 We're going to hit save. Let's add a few more. Nope. We don't need to add a few more. We're going to copy this dropdown menu all the way down and think it's going to be okay if we let's see.
0:18:50 Yeah. Perfect. So now what this does, we try to just copy paste it all the way down, just getting it all across.
0:18:59 So now as we have these categories, we can add as many categories as we want. It's very easy to just add a new, um, A new category here.
0:19:11 We can also delete all of these columns. We can delete this column. And now when we insert a new row, let's see it has the data validation in it.
0:19:23 Great. Let's say from the friendly now, how do we find the titles? Well, one, we can look at these titles and say, okay, the hustle.
0:19:35 This is for the hustle. We can select it, but we can also just start typing. And we can say morning, there we go.
0:19:45 It's morning brew. So one cool thing about data validation that I found is that it's actually like a auto-fill so we can go here.
0:19:55 We say niche news. Um, the hustle loves niches. Land of random, love it, founder friendly. Um, start our story. And now we can just start typing.
0:20:08 If we just remember the name, we don't have to copy paste. We don't have to move anything from here. And any time we add a new one, it'll show up in our dropdown.
0:20:18 Let's test that out. Um, let's add a pot, some podcasts. So smashing the plateau. It's a podcast that I will be on soon.
0:20:33 We've already recorded. Our episode should be out soon. Um, here's the podcast And we can just add to the press.
0:20:44 We have smashing the plateau and we know it's a podcast. Click submit. We know it's founder friendly. I was on it.
0:20:53 So we go to our keywords. Oh, wait, let's see. It's already there. It's already on our list of press. So we named that press instead of sheet one it's founder friendly.
0:21:02 So we can go and say, smashing the plateau done. That's awesome. Isn't this fun. I find this fun. I hope you find this fun too.
0:21:11 All right. Now the key thing here, the thing that we were missing before the coup d'etat was that we want to have keywords here or categories and we don't want just one.
0:21:27 We want everything that matches. How are we going to do this? This is a tough, tough problem. Okay. This gets a little difficult because what we need to do is we need to create another table, which shows, which, uh, categories is the hustling.
0:21:49 All right, we can do this a few ways. I'm going to do it a really roundabout way, but it'll work.
0:21:54 Um, we're going to join with the top category, every single row. Then we're going to join these altogether. We're going to actually delete a bunch of rows, just so that we, we are not going to get more than like a few hundred of these.
0:22:10 We are a boutique PR agency here. Let's say a hundred. If we ever have to add more, we can add more later, but let's say we only have a hundred rows.
0:22:24 We might only have, you know, 5, 10, 15, 20 columns. We can always fix that later here. We want to add one to get rid of these refs, Just going to delete these rows.
0:22:40 Okay. We have now here. Okay. So this is what I did. I joined each of these, uh, cells with a pipe and the key, uh, key word at the top.
0:22:51 So if we show, show you this, we have the hustle daily news, the hustle, niche news, tartar story, founder friendly, smashing the plateau founder-friendly so these are categories and I did this with join pipe and then keywords B two.
0:23:09 And then I did the locking sort of with the one column here so I can copy and paste this all the way down.
0:23:19 So I'll show you how that works. So I did equals join. The eliminator is going to be this pipe key above the return.
0:23:27 Key on the right. The value is going to be key words, uh, B to the same cell that this is on the other page.
0:23:36 And we want to combine it with key words, be one, but we don't want just be one. We want be dollar sign one.
0:23:46 And now it'll ask us to auto-fill. We do want to autofill, but I actually want to show you, we can just copy and paste this all the way down.
0:23:52 We have a bunch of daily news. We have a, but we don't care about this yet. We want it to fill in.
0:23:56 If we get more, what we do want now is we want all of these on a new, uh, in one column.
0:24:06 So we'll add, do that. Well, we'll call it column. We are going to delete everything other than these two columns we're going to do equals, uh, equal.
0:24:21 And we're going to use curly brackets here. We could also do a Ray formula, but I like doing this pretty simple, uh, curly brackets or do concat.
0:24:33 We're going to use a T a, we can do a semi-colon concat B B semi-colon. Concat a C colon C.
0:24:46 And that's the name of this? Uh, actually I probably should call it join. Uh, concat we'll rename it in a second D to D and now we should get every single thing that's in.
0:25:02 Um, this concat and I'm going to rename it. Join. If we go back to our column, it renamed a join.
0:25:09 Cool. Now what we actually need two columns here. We're going to split everything. Split. The text is a one to the delimiters the same thing that we use for join.
0:25:25 And we get, what do we get? We get a bunch of values there. We have our hustle daily news. Perfect.
0:25:36 It's actually really, really perfect. We have everything in the place. It needs to be. I know we have like these in the wrong column, but we usually can sort of fix that.
0:25:47 Um, I don't know if we really need to, right now, we don't at this moment, but now we have the hustle.
0:25:57 If we search command F the hustle, we can see there's four of them, but it's actually two rows. And we see this niche news and this daily news.
0:26:07 So we go back to our press and we say, equals filter. And what are we filtering? We're filtering the column, uh, sheet B, colon C.
0:26:23 Our condition is that column B B is equal to this. And let's see what happens. We now have all of the rows that were in there, so we can go.
0:26:38 We don't want B to C. We actually just want to see, to see now we have all the categories, but you see here there's morning brew.
0:26:44 Oh, we don't want that. So we'd want to join by a comma, use a comma in this case. And there we go.
0:26:55 Now it's all in one, one cell. And I think what we can do is we don't have to lock anything, but I'm going to do it just in case we move this for any reason we want call them BNC an 82.
0:27:14 Yeah. So we're going to copy and paste this all the way down. And we now see here's all the here's all of the categories or key words in one place.
0:27:22 This is really cool. So I want to take a break right now, just to explain a little bit, review what we've done and explain how you can get this much faster.
0:27:31 So if you're watching this video and you're like following along, you've got the bookmarklet. You figured out how to get the information in.
0:27:36 You got the keywords set up and you're categorizing things perfectly. And you're like, okay, great. I know these like formulas now, what is this for?
0:27:46 Well, there is two things available. Members get a bookmarklet. So there's a video that I go over exactly how to use it, how to set it up, uh, where to get the information from.
0:27:59 But two, I have created a, uh, a script, um, a tool called better letters. And actually I've used this bookmarklet for like three years to run a newsletter, to capture content every single week, and to end to put it into a newsletter every single week.
0:28:16 Um, and I've created for you an entire bundle that actually creates the bookmarklet automatically with just a few clicks, um, much, much easier.
0:28:27 Go and check out better letters. I'll put it somewhere. I have a link somewhere to it, maybe in the description of this video.
0:28:35 Um, but if you're a member of better, uh, sheets, uh, you can check out better letters and see what it's like.
0:28:46 And let's review what we have here. We have submissions, which we can gather from a, uh, Google form and, or a bookmarklet with one click from any website we can put here, we have a page in which we combine the title and keywords, and we have keywords here that we can also just see a list of all the keywords.
0:29:08 We can assign things here, like, uh, morning brief it's founder-friendly I don't know if it is, so I'm going to add them.
0:29:15 So we have Curry. Now we need to actually to finish this up before we check this off, we need to add a submit button.
0:29:22 We want to add two columns to our press. We want a submit URL. Yeah. And we want a button. So this is going to be pretty simple.
0:29:43 Any w if we find, I don't know if actually I know starter story has one, so starter story, we can submit, um, where is it?
0:29:54 Oh, there's my face. There I am. There's better sheets by the way, check out the, uh, video on, uh, not video the interview I did with them.
0:30:04 Share your story. There it is. Share your story. So this is a story.com/share. We can submit any time we want our business name there.
0:30:14 So we go to our submit, your L copy it there, but let's say we want to, so a couple of things, why we might want to add a button and not just a URL is yeah, great URLs are easy to see and I click on it.
0:30:30 Great. But if we are selling this, not just as a service, but as a sheet that we want to, um, sell access to, we can make this a little bit better, a little more vanity here, and we can hit, we can do equals hyperlink.
0:30:51 Let's move my face down. We can do the URL is here. And the link label is submit, and we get just this text.
0:31:01 Right. But we can also make it really cool. We can make it look nice. We can also change the row height.
0:31:15 So we get a little more height out of all this. We can. Now I'm getting into a little bit of design, but it's all right.
0:31:22 We can and underline it. So now, too, this brings our eye directly to, okay. I can submit to this. Great.
0:31:31 Yes. URLs are perfectly fine. And text is perfectly fine, but in sheets and spreadsheets and Google sheets and Excel, we get a little data heavy.
0:31:40 We get a little too much text. We want a little visualization. So in fact, we don't even need the submit text.
0:31:50 We can use something like an emoji here. Let's do an arrow. Let's do, let's see what this arrow is like, hit enter.
0:32:01 We can change the, there we go. This is pretty cool. We got a little arrow, same link, same, same URL works perfectly fine with a little arrow instead of a button that says submit.
0:32:15 So again, just adding a little bit more oomph to this. We can show unsure the grid lines. We can make it a little nicer.
0:32:25 We might want to add something like a dashed gray line everywhere looks okay. Not the best, but we're doing better than we did before.
0:32:41 Great. Let's go back to our roadmap and we have done the correct. So now we really have the engine, the non-creative engine, right?
0:32:52 The, the, the guts of APR agency. But we really have like a lot of other stuff like PR agencies do a lot of client briefs.
0:33:01 They add notes, they create relationships. Um, they add checklists to themselves, their own processes of like social. And, uh, they might reach out in many different ways.
0:33:11 There might be contact information, um, beyond a submit page. So that's where we're going to do in this final two sections.
0:33:20 We're going to add some notes, add some clients at a cha client checklist, but first some more coffee. So this may seem simple.
0:33:28 We're going to let's just go and do it. We're going to add a note section. We're going to insert a column to the right.
0:33:36 We're going to actually delete all these other columns. We have this weird button thing here. We're just going to write notes.
0:33:45 And this is always good to have. Um, as we increase our database, we might want to say, you know, submission notes here.
0:33:54 We might have internal notes for our agency. We might have submission notes, or let's call this before you submit, read before you submit great.
0:34:14 Um, we have these notes. We have internal notes, and let's say, we're doing this all. Like if we are running a PR agency and we're doing the work for others, this is all perfect.
0:34:27 This is great. We don't need to do much else, but let's say the business model is we are selling access to the sheet and specifically the press sheet, maybe there is a, we want to add over here to the right, uh, email contact, right?
0:34:46 Contact correctly. We have internal notes. We have, um, email last updated. This is like a date column. And we might want it to do this in case the contacts go bad or not bad, but like a writer moves away.
0:35:12 Um, the submit button doesn't work anymore. Um, they're sort of internal notes that might need refreshing that we might need it.
0:35:20 We might not want to share certain email contacts. For instance, for instance, uh, pat walls run starter story. And, uh, he used to use his Gmail.
0:35:30 So there's like a lot of people who know his Gmail account, but like right now, if you submit to start a story, I think you get like pat@starterstory.com.
0:35:38 So I would email him on his Gmail, but like most people now would email him on pat, start a story.com or someone else that started her story.
0:35:45 So you're the press. The PR agency might have more contact information or different contact information. Then you might want to admit or share.
0:35:56 So, and also like, do we really want to share this submissions page? Do we really want to make it complicated for others?
0:36:03 Not necessarily. So what we can do, we don't want to make a copy. We want to create a new sheet.
0:36:13 Let's close these. We want to create a new sheet. We're going to call this, um, press for public, like for public consumption.
0:36:24 And all we're going to do is do equals import range. We want the spreadsheet. You are L here, copy and paste this in quotes.
0:36:34 We want the, uh, in quotes, we want the press, uh, tab exclamation point a. I forgot the two G a colon G.
0:36:48 It's going to ask us to allow access. Yes. Cause we can allow access to another sheet and bam, there we go.
0:36:55 Now we can design this as much as we want. We can make these a little nicer to read, but all of our information, if we ever want to sell, this is here.
0:37:06 We have private information here. We have public information here. Then we can sell access to via Gumroad. You can even use only sheets, only sheets.xyz.
0:37:15 If you want to give access to this individually. So you don't have to share with the world and anyone with the link, we don't have to do that.
0:37:24 We can keep it restricted, add only sheets and bam. We are rocking and rolling with a, uh, data, uh, startup business.
0:37:34 This is really cool. And we can even use the data business to say, Hey, buy the data for 50 bucks a month.
0:37:41 And if you want our services, it's like 500 bucks a month, right? We can go through it and do the work for you.
0:37:47 Or you can see the list or you can do both. You can get the data. And then you're like, ah, I don't really want to do this.
0:37:52 Get our services. And we've created a PR agency in a Google sheet here. This is really cool. But um, we need to create a cha a chant, a client checklist.
0:38:05 We've added the notes to the page. We want to add clients. And again, this is one reason why we had that public sheet is because if we have a clients here, then, uh, w we don't necessarily want to reveal that even to people who are our clients, we want one place where it's just ours.
0:38:27 Only we have access to, and we can list all of our clients in one place and map their progress through our agency, right?
0:38:34 So we can say, you know, business name, Contact, which could be like better sheets is one of our clients. Who's our contact there, Andrew, our email, andrew@bettersheets.co email.
0:38:55 Me, anytime we might have a URL, better sheets.co so simple, right? And we might have, uh, some ideas notes, right?
0:39:08 Um, concepts, creative, brief. We might have a checklist of things, right? We might say, Hey, um, this might be not just sections to fill in, but it might be progress.
0:39:23 So create brief, create a list. Concepts, send list, capture new leads, send leads. This might be an entire process.
0:39:42 So we want to do right here. Let's do insert boxes and let's make these a little bit bigger. 15. It's probably going to 14 though.
0:39:54 Three 20. I like, I like them big. Let's do something cool up here. Let's actually change this to here like that.
0:40:06 That's super cool. Right? Nice. Now we can see our progress throughout. We can create a brief list, concepts and lists, capture new leads and leads.
0:40:19 Boom. We're rocking and rolling. We have a PR agency. We have a client list. We have a checklist in a very different way, right?
0:40:26 You might think, oh, we want to do a vertical checklist, but this might be a checklist or our progress. Our pipeline here.
0:40:32 This would be pretty cool. I like this, this big old check box. We can't miss these check boxes. Nice. So go back to our roadmap.
0:40:42 We have contextualize, we'd add a client checklist, which is our progress bar or our pipeline contact. All right. This is a fun one.
0:40:51 I hope you've stuck around for this entire video to get the, you might not know what's about to happen. Kind of write some script.
0:40:59 Um, I sorta like this. What we want to do is we have context information, right? We have this press. We have press here.
0:41:09 We have internal notes. We have an email contact. We have pat@startourstory.com. Um, We have our client better sheets, but what we're going to do is we want to email on, uh, on behalf, right?
0:41:26 What are we going to do? Well, let's write some email templates. Some email templates might be just like, hi, I'm wondering, let's see.
0:41:38 Just nice. Oh, thing. Wondering if you're still taking submissions. Let me know. I might have like a wonder. We might have like update.
0:41:58 Can we update an old listing? That kind of thing. Like we might have some email templates we want to send, why do we do this in Google sheets is we may want to track how these are doing.
0:42:12 We might want to say sent and responded so we can do that pretty easily by adding like a incrementing number here, like five sent and responded to where he sent 10 of these.
0:42:23 And we got like nine responses and we can always see do some really simple math and add to this. Anytime we have something, right.
0:42:34 Um, we can also make sure we copy and paste it. Few are, if you have, if you're running an agency and you have outsourced like VAs, you might not want to give them access to your say inbox or your email, but you do want them to send email.
0:42:51 How are we going to do that? How do we send this template to an email address? We can do that with code.
0:42:57 We can do that. Uh, in let's let's start doing this. Let's do app script. I want to share this with you.
0:43:04 This is a really fun, oh man, this is gonna be complicated. Let me show you the core of the email function.
0:43:09 So email, uh, send we'll call it. What do we want? We have any, I don't know if we have, yeah.
0:43:17 Email saying we have mail app in here can send email and it tells us what we need to get here.
0:43:28 We have a two subject I'm going to go. And there's a simpler one. Actually. No, this is perfectly fine. Nope, but there's a simpler one, one second.
0:43:44 All right, here we go. So this is the fun thing. We can send an email from Google sheets. Here's our script that we need to do.
0:43:51 We mail app.email send, and we only need is a recipient, a subject line and a body. That is it. That's all we really need.
0:43:58 We can do as well. Some options like there's a reply to, um, somewhere here send, ah, I like this one.
0:44:07 So if we are sending emails on client's behalf, so one, we are doing a pressing where we are doing a press agent, a PR agency right now, and we are going to send emails, but we want those replies where we're the ones that have the relationships with the press.
0:44:20 But if you're creating some kind of other business where you're just doing the outreach yourself, but the replies, the, the like say you're trying to book on podcasts and you own, you really don't want to do the outreach.
0:44:33 What you can do is send email as someone else and as yourself, but they reply to your client or a reply to your boss.
0:44:42 Something like that. This is how you do it. Reply to this is really fun. Okay? So we're gonna send re we are going to send emails on our behalf.
0:44:52 So we don't want to do this, but I want to point this out that you can do this. Great. We need, we need all we need is these three things we need send email recipient body.
0:45:02 Let's just copy this and put it in here. Send email recipient, subject body. Okay. Variable equals uh, variable re sippy and equals.
0:45:18 Um, in this case, I actually, I just want to do Andrew, a better sheet stock. Oh, for now our subject variable subject equals, um, we're going to get this in a second.
0:45:32 We're going to use actually subject body. Okay. This is going to be confusing, but this is going to take two things.
0:45:43 Then take the subject in the body and we're just going to call it subject and body variable body equals body.
0:45:48 I don't think we necessarily need this, but we're gonna just do it. And we'll, you'll see what happens. Okay. Are whenever we send an email, it's going to be to Andrew.
0:45:58 We're going to change that. Eventually. We're going to send a subject the body. Okay. Let's see how this goes. First.
0:46:06 I think we need to run it and we're going to need to authorize something. I think we're going to need to authorize stuff.
0:46:14 Let's see if it here. Um, ah, we can kill this. Let's go back here. This is our subject. This is our body.
0:46:28 And if we do equals email, send a to colon, comma, B2 hit enter. I think it's going to keep loading error.
0:46:40 You do not have permission. This is so good. This is cool. So we go back review permissions. We're going to get permission to do this.
0:46:48 Then it's going to be so much fun. We give it permission. Give it permission to allow. Allow. Yes. Yes. All right, let's go back and try it again.
0:46:57 Let's I'm going to hit enter again. We're going to copy and paste error. Oh, we need the permission. Oh, all right.
0:47:07 Let's figure this out. We need more permission. We need permissions here. Required permissions. We'll get through this outside the domain of legacy API.
0:47:20 All right, we're going to need to do this a different way. We need to do this from a menu item.
0:47:25 So, uh, what we need is, um, I, this is gonna get you a little more complicated menu. I always need to do custom menu, Google script.
0:47:39 I always have to Google this or just going to copy paste this. Get a custom menu, use all of this and put it at the top.
0:47:51 Now we can delete these menu items here. We only need one menu item. We're going to add a email. Can I hit command S now what we need to do is go back to our sheet and save it and make sure the project saves.
0:48:15 <inaudible>. We're going to get these variables in a different way. So now that we have our on open and we're going to get a custom menu, I'm going to call this actually email menu, send email menu and hit command S save.
0:48:32 We're going to go back to our sheet. Now we can't do it this way. So we're gonna refresh and it will open now eventually a menu next to this help.
0:48:44 It will be really cool. See, now it has sent email menu and we have email send here. All right, let's go to our extensions, back to our script, check it out.
0:48:55 Let's see what I think I did something wrong here. I think this first item should be send email. And the second item should be send email, send the name of the here this here.
0:49:07 Okay. We have two things we need. We don't need these anymore. All we need is a prompt to Oregon to do is we're going to say who's our recipient.
0:49:17 We're going to get a email address. We're going to copy paste an email address. We need to get that as a prompt.
0:49:24 We're going to get the subject and the body from a row. So looking at this, we want to send this subject, wondering this body here to a recipient while our recipients are going to be our press contacts.
0:49:42 So if we know this, we know the email address we want to send to. We don't want to go over to our Gmail app and have to type out this email.
0:49:50 We don't have to call. We don't want to have to copy paste all this stuff. We just want to go here.
0:49:54 Email send. Actually we can refresh and fix that. Okay? So it's loading now and it's still let's come back to our extensions.
0:50:04 Let's fix this for once. And for all, it must not have saved. So we'll do that begins in email. And the second part here has to be email, send command S but we also want to delete, oh, this let's add our prompt.
0:50:26 Um, we want prompt in Google script. We always have to Google everything PR response. So here we have our get response text.
0:50:42 We will do all of this. Cool. So now what happens is our function, email send is going to get a UI.
0:50:53 And it's going to ask this question. Uh, we want to say, actually not, may I know your name. We want sending email.
0:51:05 You want to whom to which email address. Now, the response that we get is going to be the email address, email app.
0:51:21 We want to log this just to check that we're doing everything okay. Um, but now our, we have a variable.
0:51:32 We have this variable response, got get response text. This is going to be cool. Variable recipient. It's going to be that text.
0:51:46 Whoops. We also need one more thing. We need to get another, uh, prompt Row, Which email to get. She said what's wrong.
0:52:09 And we want the response. This is going to be response. Row response row. Just have to change all these to response row, to make them different than response up here.
0:52:31 Okay. Now we get response road, text variable response road Get Row equals. Okay. How do we get the subject? Oh, this is the F this is a fun one.
0:52:47 We have to do this. Let's uh, add punches. Let's get here. Okay. We have a row. We know the row number.
0:52:56 We know who we're sending to. We got to get this subject. What do we do? We do variable S S equals spreadsheet, app dot get active spreadsheet.
0:53:11 Di uh, then we do variable. That's a start get range. What is the range? It's going to be the row.
0:53:29 It should tell us what we need. All right, this should be row number. So this should just be row. Here.
0:53:45 It is. So get ranges of row. Any column, the column we want, the subject is going to be number one.
0:53:50 The first column we want to get one row. We want to get one column. We only want one thing. And then when we want to do dot get value.
0:54:02 Cool. Now the body is going to be the exact same thing. We just copy and paste this, except we don't want the first column.
0:54:09 We want the second column. All right, now let's test this. Let's we don't really want to send any. Actually, let's try to send an email.
0:54:16 We're sending an email to ourselves, going to hit save. What I like to do is I like to open this in another tab and just look at the execution.
0:54:27 This is the executions. We'll close a bunch of other tabs where we staked. All right, here we are. This is great.
0:54:36 We show our failures. We show our completions. We show it completed this unopened. It's created this, uh, our menu. We want to double check that our menu is correct.
0:54:46 So we will refresh all the stuff we'll close. We will go back to extensions app script. Let's look, our menu, send email.
0:55:00 We have a space. Perfect. We are on our way. Rocking and rolling. All right. So on open, we have our emails.
0:55:06 And when we click this button, this will occur. Now what should happen? And anything that happened doesn't happen this way.
0:55:14 We'll fix, but what should happen? Let's look at our, we want to do that in another tab. What should happen?
0:55:23 As a prompt should come up and say, Hey, you're sending an email. Who should we send it to? Which email address?
0:55:28 Then another prompt should come up and say, what role are we? What email are we sending? Give us a row number.
0:55:33 And then that email should be sent. Let's see if this is going to happen. This is really exciting. Okay. First we need to authorize.
0:55:41 This is what we needed. We want to authorize our emails. Great, great, great. Yes. Now we authorize authorize. You're going to have to go through this.
0:55:49 Let's do it again. Let's see what happens. Don't cannot call document. I get it. Oh shoot. Alright, we'll fix this.
0:55:58 All right. I made the, the stupidest blender. This is document app. We need spreadsheet app. Uh, we need, yeah, so that's a stupid blender on my part.
0:56:09 Let's dismiss this. If we go back to our executions, we see failed PR. This is actually great. It failed. We know why we did it.
0:56:14 We had document app, not spreadsheet app. Let's try this again. Which email address? Great. Let's do andrew@bettersheets.co. Yes. Which row we want to do?
0:56:28 Row two. Oh, this is exciting. We're going to hit. Yes. Did it send, finish? It said finished script. Let's check our mail.
0:56:38 So it did send, but we have a little error. We have just the word wondering, um, let's go figure out why that happened.
0:56:48 Um, we've got column two row is two column, two number of rows. This body didn't work. Why didn't it? Oh, because it did work.
0:56:58 The body was wondering one. If you're still taking submissions. Oh, it worked actually. Okay. Let's send email again. Let's try that again.
0:57:09 Let's do, I'm gonna do campy@gmail.com. That's my personal email. You ever care? Click. Yes. Which row to yes. Finished script.
0:57:21 We have sent the email. Let's go double check. And they're in our Gmail app is the send email. Wondering if you're still taking submissions.
0:57:31 We have sent an email from Google sheets. This is really cool. This is really fun. So now we don't have to take the time to open our Gmail app.
0:57:43 We don't have to take the time to add it to a pipeline anywhere else we can do that from this script.
0:57:50 So cool. So there is a little bit of a feedback loop. We have not told ourselves in sheets that we've sent that email.
0:57:59 So what I'm going to do is I'm actually going to make a whole video about logs elsewhere, but we're going to create an email log, email log, all caps.
0:58:11 Um, and we want to know what emails we send to home so that we can track this information inside the Google sheet.
0:58:18 We don't have to scrape our Gmail. We can do this all with code. Let's go back to our code. Um, super simple to do this at the end.
0:58:27 Once we've sent that email and we know it's sent meaning we don't get an error. If we get an error on this line 38, and nothing else will run.
0:58:35 So we want to go to S S what do we want to do? We want to, let's just write it out.
0:58:42 We want to put in email address, uh, two column one. We want to put in subject in column two. Oh, actually we don't want column one.
0:58:59 We want, this is column two. We want column three. We want timestamp in column one. We want to put in a body of email in column four, just to make sure we got everything right.
0:59:20 Okay. So we have a very, we know the subject. We know the body, uh, we know the recipient is going to be this.
0:59:28 So we go, uh, actually we can really sort of write it out, especially to uptight active it's active spreadsheet dot get sheet by name.
0:59:39 We want to add it to email, log dot, get range. Sorry, typing this all out. We might put on separate lines in the second.
0:59:52 The row is going to be last row. Um, yeah, actually I will do this on separate, okay. Variable log equals this.
1:00:08 Now we're going to do variable last row equals log dot. Get last row. That's easy. And we want to do timestamp actually, before we do the 10th, sometimes a little weird.
1:00:25 We want to do, um, Log dot, get range. We want the range to be last row. Plus one column two, and one dot set value is going to be recipient.
1:00:51 So what's this doing? This is doing something really cool. This is setting the value of the recipient in column two when we execute this code.
1:01:01 So let's save this and see if this happens. Um, we go back to our one second. There's something weird. Email templates.
1:01:14 Let me rewrite this email templates. It's one word, email templates. And I think there's something wrong in it. I feel like there's something wrong in our code because we grabbed the row, um, of the spreadsheet that we're on.
1:01:33 So we actually want to get sheet by name. So this is active by name. That's perfect. Now we want to only do it on email templates.
1:01:49 That's what we want. We want to make sure that our subject and our body are coming from the, the row on email templates, no matter where we are in the sheet.
1:01:57 So let's go to, like, if we go to email log and we send email, which email address let's do, and we see our recipient shows up here on our email log.
1:02:22 That's the first step. If we go back to our sheet, let's go see if I sent that. And there we got the email.
1:02:28 That's really awesome. We can keep testing that. All right. We want more than just the recipient though. Now that we've got the recipient, we need to add in log.
1:02:41 We want to do the same exact thing, but the subject, And instead of column two on column three, same thing, we want the body of the email column four, and we want body.
1:02:58 So let's save that and let's go back and do that again. Let's see what happens. Let's send a different email.
1:03:10 Let's send the third one. And there we are. Andrew campy. We got our Sipion. We got our update. Can we not listen to be updated?
1:03:19 We probably have it in our inbox. Bam, it's working. That's pretty cool. But what about that? Timestamp? Let's go. I'm going to Google it and we'll figure it out what it should be.
1:03:30 And I always sometimes get this wrong, but we do the exact same thing here. Copy this. But instead of recipient, we're going to do new date and let's see what happens when we do that.
1:03:57 We don't get anything. Ah, it was a silly mistake. It's because it's rewriting column one, column two. So now we just change it to column one.
1:04:05 Now we should get it fine. We gotta do this all again to test it. It's okay. Let's send it to, there we go.
1:04:19 We have a date. Now we have date and time. We have a timestamp of when we sent these emails. We know when we're sending emails, who we're sending them to, what are we sending them?
1:04:29 This is really cool. I'm so excited about this. All right, now that is our final section of what we plan to do, but I want to review what we've done.
1:04:40 We have created a PR agency from scratch inside a Google sheet. We can capture any kind of press. We want the title, the URL in one click with our bookmarklet.
1:04:48 We can execute the type. Also we can add more types if we want. Uh, we also curated. We add categories.
1:04:55 We have a dropdown menu. We have a whole sheet just for categories. That then is filled in on our main page.
1:05:01 Um, with the join, we've added a submit button, submit link. We can submit directly to these press. Um, but sometimes we have emails which we'll get to later.
1:05:12 We can contextualize, we can add notes. We can add clients. We can actually have a client sheet, uh, and we can share the sheet of press publicly or, um, sell it separately.
1:05:26 So we have two businesses we've created within this one sheet, right? We have a public sheet, um, that we can say, Hey, you can gain access to this pay 20 bucks a month, $50 a month, a hundred dollars a month.
1:05:37 And we'll be updating this. We'll send you emails when we have our update. And then we can also run the agency with that same list.
1:05:44 We did that then inside of our sheet, we wrote code so that we can send an email and we can send email templates based on what we write here.
1:05:53 And we can capture who we're sending to at what time we're sending to them when, and we can capture it.
1:05:59 Did we ever send it? Here we go. We can look at our email log and we can see, this is really, really powerful stuff.
1:06:05 We can execute our entire PR agency from this Google sheet. I'm so glad you stuck around to the end. Thank you so much.
1:06:14 Make sure you let me know if you can use this. If you can't, if you can't use it or whatever, it's really fun.
1:06:21 All right, bye.
0:00:22 We're going to create basically a sheet that allows us to capture publications, uh, sort of a modern publications we're going to, which are newsletters, uh, podcasts, uh, blogs, interview, uh, websites.
0:00:42 We're going to be able to catch, capture the title and the URL of those very easily. As we go around and find them we're going to curate them, meaning we're going to categorize them, add categories to them based on our clients, uh, or even potentially future clients, because there are two business models that I can perceive from this one.
0:01:00 You can sell this as a search. You can use this kind of sheet as a service where you are a PR agent, or you are someone who curates content or curates contacts.
0:01:12 Uh, but you can also sell, uh, the sheet access to the sheet itself. So instead of doing sort of the creative brief and figuring out, you know, how do people, um, fit and do the manual work of, uh, creative ideation fixing, uh, putting the puzzle pieces together and then doing the outreach, you could actually just sell access to the sheet and you could say, Hey, I update this, you know, once a month.
0:01:39 Um, and you know, for a subscription of 50 to a hundred bucks, you get access to the sheet, uh, all the time.
0:01:48 So we might be doing things. We might be adding categories that we don't necessarily work in, but others might want to know about.
0:01:56 So we can do that. Uh, we'll also contextualize, which is meaning we'll add an area for notes. Uh, we also need to, uh, create a way for clients to work with us.
0:02:10 So we'll get something like, we'll start doing this, we'll get the title here, we'll get a URL. We'll get also the type with this.
0:02:20 We'll use a bookmarklet for this curate. We'll add categories and I'll show you a really cool thing here, basically. Um, there's a certain way people will think of adding categories and I'm going to show you a really fun other way to do it.
0:02:37 Uh, and it's going to be pretty complicated, but it's going to be really cool. Basically. We're going to join. We're going to, um, the basic idea is it's going to be a huge dropdown menu, but not the way you think of it.
0:02:48 I think, uh, and we're going to use join as well. I think here, um, to make it easy to read.
0:02:56 Alright, contextualize, we're going to add note, section add notes, and we also definitely need, we might add, sorry, add, uh, clients.
0:03:11 We might add clients here in this section, in the contact. We definitely want to add a contact info. We might want to keep track of, uh, if it's up to date up to date question mark, um, and we want to deal with outreach.
0:03:31 Now, this is a hard one to think about. Now at the beginning of this, uh, this will probably be about an hour long that I'm working on this and we're only about four minutes into it.
0:03:43 So this outreach part, we may or may not get to find out. You might already know if you're watching this somewhere else.
0:03:52 And there was an intro before I started talking, Ooh, this is exciting. What are we going to get to? All right, how are we going to do the capture?
0:03:59 So that's, that's the idea. Um, let's go through that again. Summarize, we're creating a PR agency where we can capture anything like newsletters that do interviews or podcasts that do interviews.
0:04:10 We can capture them into a sheet, tag them as what their type is. If it's a podcast newsletter, we can then add categories based on the type of content they do, or the type of interviewees they have, or the kind of people they're looking for.
0:04:24 We might also actually add here, uh, let's do this add submit button and link. So we'll need a link to say, Hey, yes, here's the main page of the podcast or the newsletter, but here's their submit.
0:04:40 But here's how to actually submit them just in case one. If we are running this as an agency, we want this information easy for us to access, but if we're selling access to this sheet in this other business model, we want someone else to be easily.
0:04:53 Click that button. Um, contextualize, add some notes, add clients. If we are using this as our internal command center or database, we want to add clients as they come.
0:05:05 We might want to actually also add a client page checklist. So in that case, what we would do is say, create a new tab in my mind.
0:05:17 Now we would create a new tab and then be able to add these newsletters, podcasts, and press from the main database into this checklist, with all that information.
0:05:32 We'll see how that goes. And then we definitely want to add contact information. Once we capture our, uh, press, we want to make sure we have contact information.
0:05:43 We want to make sure it's up to date and potentially a checklist to outreach. So what you might think of as the first step, here's our roadmap.
0:05:55 And here's where we're going to start. The very first step most people will take is they'll start writing title, URL type.
0:06:06 They might add a drop down menu here, list of items that might be okay. We're going to look at podcast newsletter, blog, text, interview, that kind of thing.
0:06:23 They'll add all these kinds of stuff, right? Round up, maybe, uh, I don't know, say, okay, here are, here's our type, here's our title.
0:06:31 We'll just copy and paste titles and URLs in here. We can do this better. What we can do is use a bookmarklet.
0:06:37 We don't even need this page yet. You can go delete this page. What we need to do first is go up to tool, create a new form.
0:06:50 We're going to call this submit to PR. First question we're going to have is title. It is great. It automatically makes it a short answer.
0:07:01 We're going to duplicate this because it's easy URL. It should add. We don't need our data validation. We could do response validation.
0:07:12 We don't need that right now. And we need type. Now this is going to be dropped down. We're going to do podcast newsletters.
0:07:28 Um, blog interview is going to be different than like a text interview. I think, um, we might want to add a few other things we might add, come up with other ideas, like article or feature article.
0:07:44 If it's a feature article about a person, and then we just want to take, uh, whoever that person writes about.
0:07:49 We have some clients that are like them, and we want to outreach to that writer. We also might just find one writer or author who Does our industry.
0:08:01 So we might want to add them. These are the sort of the types of things that we're going to be rounding up.
0:08:05 Okay. That's it for the form. If we go back to our sheet, we have a form responses. I'm going to call this submissions.
0:08:15 Now what you could do is this could be great for crowdsourcing. If you wish to open this up to other people, if you have maybe a group of people who are trying to create a little PR agency and you want to capture information on capture these, uh, types of press, um, that's it press, okay.
0:08:39 Where does the easy part come in? How, how is this at all easier? How is this at all better? Um, and I'm going to show you through creating a bookmarklet.
0:08:49 Okay. The first step we need to do is there is get prefilled link. We need to get a prefilled link.
0:08:56 We want title to be, you know, something like the hustle, your L of the hustle, something like this. So we're just going to do this once.
0:09:12 We're going to choose type pod. No, not pockets newsletter. We're going to get link now. Copy link. Okay. We have this link.
0:09:21 We're gonna do let's see text. Okay. Actually, we can do it in Google docs. Let's just paste this link. I'm going to show you, there's a URL here before.
0:09:39 There's also this entry. We need this entry, um, and this entry. So this is the entry we're going to do this right here.
0:09:47 We're going to do entry for title. We're going to do entry for link, URL, and entry for type right there.
0:10:04 Okay. We got that. Now we get our bookmarklets. So if you are a better Sheetz member, there is a video called bookmarklet or it with the word bookmark, Linnet.
0:10:14 This is, uh, for available for every, um, this code and how to use it is available to every member. So go ahead and check out the video called bookmarklet.
0:10:29 All right. We want our URL here. One to replace our URL. We want, uh, the HRF is going to be the URL.
0:10:51 And again, I go over this in the video. The title is this so entry and that should be everything that should work now.
0:11:06 All right, let's see. Um, one second. He checks him. All right. Again, I go over the video in the video.
0:11:15 Bookmarklet how to do this, but we're going to go to our bookmark bar. Can I add this up here at it?
0:11:35 Okay. Now we should be able to go to the hustle. I spelled that wrong. And if we hit the press button, there we go.
0:11:48 We now have the name and the URL immediately in there. And we can edit this. If want the hustle. This is a newsletter.
0:11:56 We hit submit. That was easy. Hit submit. I don't know if that button or go back to our PR agency and there we go.
0:12:04 We have the hustle. So if we, we can close all of these windows and now anytime we go to say, um, morning brew for like, oh, we can access the more we can submit press to the morning brew.
0:12:22 We hit press there's morning brew. There's the URL. It's a newsletter we hit submit. And then we go back to our sheet and there it is.
0:12:32 So this makes it super easy to capture this kind of URLs into a Google sheet and then contextualize them with the type.
0:12:41 All right, let's put a little check box to make sure we're getting all our boxes in a row and increase the size of this one.
0:12:53 A big old check box. We captured now to curate. So now that we have our submissions, one, a couple of problems come up very consistently in Google sheets.
0:13:07 One, we might want to add our information right along here and that's okay. Uh, just know that every time we add a new entry, it will insert a row.
0:13:19 So actually you can see this by deleting these rows And then going to something like let's find another newsletter. The land of random is a fun, random newsletter that Dave Nichols runs very cool.
0:13:36 I'm always wanting to submit stuff to him. I like this kind of stuff. You're doing anything weird and nerdy. That's my press agency, helping people do weird and nerdy stuff, LAN and random.
0:13:50 This is a newsletter. Uh, we definitely want to start our story. Let's get some more in here. I already have better.
0:13:58 Sheets is a story on here that you can check out how I'm doing. Uh, we want to add to press.
0:14:10 This is we will call it a text interview. We know what they do, not really a blog. See, now we have these rows here where we had no rows before.
0:14:24 So what a form is doing is inserting a row. So just knowing that will help us understand what we need to do next.
0:14:33 We need to, I'm going to do a little quick and dirty way of doing this. Um, I don't really recommend it, but what we, what I do recommend is that you create another sheet and we use a REA formula to get submissions, Um, B to D, there we go.
0:14:59 And this is how I started recommended is always having a separate page. Let the form be its own page. Um, for a variety of reasons.
0:15:10 Um, you, you might want this data in two places, this array formula, you might want to do things like sorting.
0:15:22 You want to do that separately. You want to always add things on other pages. I don't know. It's not sure exactly how to describe why this is the way it is.
0:15:35 Um, what we want to do here is we want to add categories. We want a dropdown menu and join. So the number one way that people are going to add a category is this, they're going to grade category.
0:15:49 They're going to say, um, data validation, you're going to do lists of items. And they're going to say a startup.
0:15:58 These are sort of key. Keywords are ways that we can categorize this based on our clients. If we, if we get a new client, that's a startup, we're going to want to filter this list by just startups.
0:16:08 And we want to know who works well with that financial finance, financial, new news, um, Funding. We might have a category of founders are women only, or feature women.
0:16:30 We might want to. Sheriff's sort of the different categories here. We can add more, but this is the typical way.
0:16:38 This is the typical way people do it. They will say, save, they'll take this drop down menu. And now we have the one problem we run into is that there's only one, there's only one category here and great.
0:16:50 We can sort of, this gets really messy. If we take this drop down menu and my arrow key doesn't work, we paste copy and paste.
0:17:05 Um, and we have category two, right? This, this gets really, really messy. We don't know. Should we put start-ups here?
0:17:15 Should we put blanks? Um, should we, yeah, this gets really messy. And I don't like it. And also it's very constricting because we can only have one category per thing, but we really have, it's really keywords.
0:17:31 This is really keywords that we want to be able to filter and sort through. So what we need to do is we need to turn this on our, on its head.
0:17:41 I'm going to delete this column actually, and do something else. What we will do is add a new page. We will call it keywords or categories.
0:17:54 We can it's either one. We will write startup up here. We'll write, um, news, maybe daily news, even. Cause there might be a niche news.
0:18:07 I don't know. There might be others that we want to do that have submissions. And now this is going to be the fun part.
0:18:15 We are going to do the same thing we did before data validation, but we're going to do list from a range.
0:18:21 We're going to go back to our original sheet and use titles as our range. We're going to hit. Okay. We are going to definitely have a down menu.
0:18:29 We're going to hit save. Let's add a few more. Nope. We don't need to add a few more. We're going to copy this dropdown menu all the way down and think it's going to be okay if we let's see.
0:18:50 Yeah. Perfect. So now what this does, we try to just copy paste it all the way down, just getting it all across.
0:18:59 So now as we have these categories, we can add as many categories as we want. It's very easy to just add a new, um, A new category here.
0:19:11 We can also delete all of these columns. We can delete this column. And now when we insert a new row, let's see it has the data validation in it.
0:19:23 Great. Let's say from the friendly now, how do we find the titles? Well, one, we can look at these titles and say, okay, the hustle.
0:19:35 This is for the hustle. We can select it, but we can also just start typing. And we can say morning, there we go.
0:19:45 It's morning brew. So one cool thing about data validation that I found is that it's actually like a auto-fill so we can go here.
0:19:55 We say niche news. Um, the hustle loves niches. Land of random, love it, founder friendly. Um, start our story. And now we can just start typing.
0:20:08 If we just remember the name, we don't have to copy paste. We don't have to move anything from here. And any time we add a new one, it'll show up in our dropdown.
0:20:18 Let's test that out. Um, let's add a pot, some podcasts. So smashing the plateau. It's a podcast that I will be on soon.
0:20:33 We've already recorded. Our episode should be out soon. Um, here's the podcast And we can just add to the press.
0:20:44 We have smashing the plateau and we know it's a podcast. Click submit. We know it's founder friendly. I was on it.
0:20:53 So we go to our keywords. Oh, wait, let's see. It's already there. It's already on our list of press. So we named that press instead of sheet one it's founder friendly.
0:21:02 So we can go and say, smashing the plateau done. That's awesome. Isn't this fun. I find this fun. I hope you find this fun too.
0:21:11 All right. Now the key thing here, the thing that we were missing before the coup d'etat was that we want to have keywords here or categories and we don't want just one.
0:21:27 We want everything that matches. How are we going to do this? This is a tough, tough problem. Okay. This gets a little difficult because what we need to do is we need to create another table, which shows, which, uh, categories is the hustling.
0:21:49 All right, we can do this a few ways. I'm going to do it a really roundabout way, but it'll work.
0:21:54 Um, we're going to join with the top category, every single row. Then we're going to join these altogether. We're going to actually delete a bunch of rows, just so that we, we are not going to get more than like a few hundred of these.
0:22:10 We are a boutique PR agency here. Let's say a hundred. If we ever have to add more, we can add more later, but let's say we only have a hundred rows.
0:22:24 We might only have, you know, 5, 10, 15, 20 columns. We can always fix that later here. We want to add one to get rid of these refs, Just going to delete these rows.
0:22:40 Okay. We have now here. Okay. So this is what I did. I joined each of these, uh, cells with a pipe and the key, uh, key word at the top.
0:22:51 So if we show, show you this, we have the hustle daily news, the hustle, niche news, tartar story, founder friendly, smashing the plateau founder-friendly so these are categories and I did this with join pipe and then keywords B two.
0:23:09 And then I did the locking sort of with the one column here so I can copy and paste this all the way down.
0:23:19 So I'll show you how that works. So I did equals join. The eliminator is going to be this pipe key above the return.
0:23:27 Key on the right. The value is going to be key words, uh, B to the same cell that this is on the other page.
0:23:36 And we want to combine it with key words, be one, but we don't want just be one. We want be dollar sign one.
0:23:46 And now it'll ask us to auto-fill. We do want to autofill, but I actually want to show you, we can just copy and paste this all the way down.
0:23:52 We have a bunch of daily news. We have a, but we don't care about this yet. We want it to fill in.
0:23:56 If we get more, what we do want now is we want all of these on a new, uh, in one column.
0:24:06 So we'll add, do that. Well, we'll call it column. We are going to delete everything other than these two columns we're going to do equals, uh, equal.
0:24:21 And we're going to use curly brackets here. We could also do a Ray formula, but I like doing this pretty simple, uh, curly brackets or do concat.
0:24:33 We're going to use a T a, we can do a semi-colon concat B B semi-colon. Concat a C colon C.
0:24:46 And that's the name of this? Uh, actually I probably should call it join. Uh, concat we'll rename it in a second D to D and now we should get every single thing that's in.
0:25:02 Um, this concat and I'm going to rename it. Join. If we go back to our column, it renamed a join.
0:25:09 Cool. Now what we actually need two columns here. We're going to split everything. Split. The text is a one to the delimiters the same thing that we use for join.
0:25:25 And we get, what do we get? We get a bunch of values there. We have our hustle daily news. Perfect.
0:25:36 It's actually really, really perfect. We have everything in the place. It needs to be. I know we have like these in the wrong column, but we usually can sort of fix that.
0:25:47 Um, I don't know if we really need to, right now, we don't at this moment, but now we have the hustle.
0:25:57 If we search command F the hustle, we can see there's four of them, but it's actually two rows. And we see this niche news and this daily news.
0:26:07 So we go back to our press and we say, equals filter. And what are we filtering? We're filtering the column, uh, sheet B, colon C.
0:26:23 Our condition is that column B B is equal to this. And let's see what happens. We now have all of the rows that were in there, so we can go.
0:26:38 We don't want B to C. We actually just want to see, to see now we have all the categories, but you see here there's morning brew.
0:26:44 Oh, we don't want that. So we'd want to join by a comma, use a comma in this case. And there we go.
0:26:55 Now it's all in one, one cell. And I think what we can do is we don't have to lock anything, but I'm going to do it just in case we move this for any reason we want call them BNC an 82.
0:27:14 Yeah. So we're going to copy and paste this all the way down. And we now see here's all the here's all of the categories or key words in one place.
0:27:22 This is really cool. So I want to take a break right now, just to explain a little bit, review what we've done and explain how you can get this much faster.
0:27:31 So if you're watching this video and you're like following along, you've got the bookmarklet. You figured out how to get the information in.
0:27:36 You got the keywords set up and you're categorizing things perfectly. And you're like, okay, great. I know these like formulas now, what is this for?
0:27:46 Well, there is two things available. Members get a bookmarklet. So there's a video that I go over exactly how to use it, how to set it up, uh, where to get the information from.
0:27:59 But two, I have created a, uh, a script, um, a tool called better letters. And actually I've used this bookmarklet for like three years to run a newsletter, to capture content every single week, and to end to put it into a newsletter every single week.
0:28:16 Um, and I've created for you an entire bundle that actually creates the bookmarklet automatically with just a few clicks, um, much, much easier.
0:28:27 Go and check out better letters. I'll put it somewhere. I have a link somewhere to it, maybe in the description of this video.
0:28:35 Um, but if you're a member of better, uh, sheets, uh, you can check out better letters and see what it's like.
0:28:46 And let's review what we have here. We have submissions, which we can gather from a, uh, Google form and, or a bookmarklet with one click from any website we can put here, we have a page in which we combine the title and keywords, and we have keywords here that we can also just see a list of all the keywords.
0:29:08 We can assign things here, like, uh, morning brief it's founder-friendly I don't know if it is, so I'm going to add them.
0:29:15 So we have Curry. Now we need to actually to finish this up before we check this off, we need to add a submit button.
0:29:22 We want to add two columns to our press. We want a submit URL. Yeah. And we want a button. So this is going to be pretty simple.
0:29:43 Any w if we find, I don't know if actually I know starter story has one, so starter story, we can submit, um, where is it?
0:29:54 Oh, there's my face. There I am. There's better sheets by the way, check out the, uh, video on, uh, not video the interview I did with them.
0:30:04 Share your story. There it is. Share your story. So this is a story.com/share. We can submit any time we want our business name there.
0:30:14 So we go to our submit, your L copy it there, but let's say we want to, so a couple of things, why we might want to add a button and not just a URL is yeah, great URLs are easy to see and I click on it.
0:30:30 Great. But if we are selling this, not just as a service, but as a sheet that we want to, um, sell access to, we can make this a little bit better, a little more vanity here, and we can hit, we can do equals hyperlink.
0:30:51 Let's move my face down. We can do the URL is here. And the link label is submit, and we get just this text.
0:31:01 Right. But we can also make it really cool. We can make it look nice. We can also change the row height.
0:31:15 So we get a little more height out of all this. We can. Now I'm getting into a little bit of design, but it's all right.
0:31:22 We can and underline it. So now, too, this brings our eye directly to, okay. I can submit to this. Great.
0:31:31 Yes. URLs are perfectly fine. And text is perfectly fine, but in sheets and spreadsheets and Google sheets and Excel, we get a little data heavy.
0:31:40 We get a little too much text. We want a little visualization. So in fact, we don't even need the submit text.
0:31:50 We can use something like an emoji here. Let's do an arrow. Let's do, let's see what this arrow is like, hit enter.
0:32:01 We can change the, there we go. This is pretty cool. We got a little arrow, same link, same, same URL works perfectly fine with a little arrow instead of a button that says submit.
0:32:15 So again, just adding a little bit more oomph to this. We can show unsure the grid lines. We can make it a little nicer.
0:32:25 We might want to add something like a dashed gray line everywhere looks okay. Not the best, but we're doing better than we did before.
0:32:41 Great. Let's go back to our roadmap and we have done the correct. So now we really have the engine, the non-creative engine, right?
0:32:52 The, the, the guts of APR agency. But we really have like a lot of other stuff like PR agencies do a lot of client briefs.
0:33:01 They add notes, they create relationships. Um, they add checklists to themselves, their own processes of like social. And, uh, they might reach out in many different ways.
0:33:11 There might be contact information, um, beyond a submit page. So that's where we're going to do in this final two sections.
0:33:20 We're going to add some notes, add some clients at a cha client checklist, but first some more coffee. So this may seem simple.
0:33:28 We're going to let's just go and do it. We're going to add a note section. We're going to insert a column to the right.
0:33:36 We're going to actually delete all these other columns. We have this weird button thing here. We're just going to write notes.
0:33:45 And this is always good to have. Um, as we increase our database, we might want to say, you know, submission notes here.
0:33:54 We might have internal notes for our agency. We might have submission notes, or let's call this before you submit, read before you submit great.
0:34:14 Um, we have these notes. We have internal notes, and let's say, we're doing this all. Like if we are running a PR agency and we're doing the work for others, this is all perfect.
0:34:27 This is great. We don't need to do much else, but let's say the business model is we are selling access to the sheet and specifically the press sheet, maybe there is a, we want to add over here to the right, uh, email contact, right?
0:34:46 Contact correctly. We have internal notes. We have, um, email last updated. This is like a date column. And we might want it to do this in case the contacts go bad or not bad, but like a writer moves away.
0:35:12 Um, the submit button doesn't work anymore. Um, they're sort of internal notes that might need refreshing that we might need it.
0:35:20 We might not want to share certain email contacts. For instance, for instance, uh, pat walls run starter story. And, uh, he used to use his Gmail.
0:35:30 So there's like a lot of people who know his Gmail account, but like right now, if you submit to start a story, I think you get like pat@starterstory.com.
0:35:38 So I would email him on his Gmail, but like most people now would email him on pat, start a story.com or someone else that started her story.
0:35:45 So you're the press. The PR agency might have more contact information or different contact information. Then you might want to admit or share.
0:35:56 So, and also like, do we really want to share this submissions page? Do we really want to make it complicated for others?
0:36:03 Not necessarily. So what we can do, we don't want to make a copy. We want to create a new sheet.
0:36:13 Let's close these. We want to create a new sheet. We're going to call this, um, press for public, like for public consumption.
0:36:24 And all we're going to do is do equals import range. We want the spreadsheet. You are L here, copy and paste this in quotes.
0:36:34 We want the, uh, in quotes, we want the press, uh, tab exclamation point a. I forgot the two G a colon G.
0:36:48 It's going to ask us to allow access. Yes. Cause we can allow access to another sheet and bam, there we go.
0:36:55 Now we can design this as much as we want. We can make these a little nicer to read, but all of our information, if we ever want to sell, this is here.
0:37:06 We have private information here. We have public information here. Then we can sell access to via Gumroad. You can even use only sheets, only sheets.xyz.
0:37:15 If you want to give access to this individually. So you don't have to share with the world and anyone with the link, we don't have to do that.
0:37:24 We can keep it restricted, add only sheets and bam. We are rocking and rolling with a, uh, data, uh, startup business.
0:37:34 This is really cool. And we can even use the data business to say, Hey, buy the data for 50 bucks a month.
0:37:41 And if you want our services, it's like 500 bucks a month, right? We can go through it and do the work for you.
0:37:47 Or you can see the list or you can do both. You can get the data. And then you're like, ah, I don't really want to do this.
0:37:52 Get our services. And we've created a PR agency in a Google sheet here. This is really cool. But um, we need to create a cha a chant, a client checklist.
0:38:05 We've added the notes to the page. We want to add clients. And again, this is one reason why we had that public sheet is because if we have a clients here, then, uh, w we don't necessarily want to reveal that even to people who are our clients, we want one place where it's just ours.
0:38:27 Only we have access to, and we can list all of our clients in one place and map their progress through our agency, right?
0:38:34 So we can say, you know, business name, Contact, which could be like better sheets is one of our clients. Who's our contact there, Andrew, our email, andrew@bettersheets.co email.
0:38:55 Me, anytime we might have a URL, better sheets.co so simple, right? And we might have, uh, some ideas notes, right?
0:39:08 Um, concepts, creative, brief. We might have a checklist of things, right? We might say, Hey, um, this might be not just sections to fill in, but it might be progress.
0:39:23 So create brief, create a list. Concepts, send list, capture new leads, send leads. This might be an entire process.
0:39:42 So we want to do right here. Let's do insert boxes and let's make these a little bit bigger. 15. It's probably going to 14 though.
0:39:54 Three 20. I like, I like them big. Let's do something cool up here. Let's actually change this to here like that.
0:40:06 That's super cool. Right? Nice. Now we can see our progress throughout. We can create a brief list, concepts and lists, capture new leads and leads.
0:40:19 Boom. We're rocking and rolling. We have a PR agency. We have a client list. We have a checklist in a very different way, right?
0:40:26 You might think, oh, we want to do a vertical checklist, but this might be a checklist or our progress. Our pipeline here.
0:40:32 This would be pretty cool. I like this, this big old check box. We can't miss these check boxes. Nice. So go back to our roadmap.
0:40:42 We have contextualize, we'd add a client checklist, which is our progress bar or our pipeline contact. All right. This is a fun one.
0:40:51 I hope you've stuck around for this entire video to get the, you might not know what's about to happen. Kind of write some script.
0:40:59 Um, I sorta like this. What we want to do is we have context information, right? We have this press. We have press here.
0:41:09 We have internal notes. We have an email contact. We have pat@startourstory.com. Um, We have our client better sheets, but what we're going to do is we want to email on, uh, on behalf, right?
0:41:26 What are we going to do? Well, let's write some email templates. Some email templates might be just like, hi, I'm wondering, let's see.
0:41:38 Just nice. Oh, thing. Wondering if you're still taking submissions. Let me know. I might have like a wonder. We might have like update.
0:41:58 Can we update an old listing? That kind of thing. Like we might have some email templates we want to send, why do we do this in Google sheets is we may want to track how these are doing.
0:42:12 We might want to say sent and responded so we can do that pretty easily by adding like a incrementing number here, like five sent and responded to where he sent 10 of these.
0:42:23 And we got like nine responses and we can always see do some really simple math and add to this. Anytime we have something, right.
0:42:34 Um, we can also make sure we copy and paste it. Few are, if you have, if you're running an agency and you have outsourced like VAs, you might not want to give them access to your say inbox or your email, but you do want them to send email.
0:42:51 How are we going to do that? How do we send this template to an email address? We can do that with code.
0:42:57 We can do that. Uh, in let's let's start doing this. Let's do app script. I want to share this with you.
0:43:04 This is a really fun, oh man, this is gonna be complicated. Let me show you the core of the email function.
0:43:09 So email, uh, send we'll call it. What do we want? We have any, I don't know if we have, yeah.
0:43:17 Email saying we have mail app in here can send email and it tells us what we need to get here.
0:43:28 We have a two subject I'm going to go. And there's a simpler one. Actually. No, this is perfectly fine. Nope, but there's a simpler one, one second.
0:43:44 All right, here we go. So this is the fun thing. We can send an email from Google sheets. Here's our script that we need to do.
0:43:51 We mail app.email send, and we only need is a recipient, a subject line and a body. That is it. That's all we really need.
0:43:58 We can do as well. Some options like there's a reply to, um, somewhere here send, ah, I like this one.
0:44:07 So if we are sending emails on client's behalf, so one, we are doing a pressing where we are doing a press agent, a PR agency right now, and we are going to send emails, but we want those replies where we're the ones that have the relationships with the press.
0:44:20 But if you're creating some kind of other business where you're just doing the outreach yourself, but the replies, the, the like say you're trying to book on podcasts and you own, you really don't want to do the outreach.
0:44:33 What you can do is send email as someone else and as yourself, but they reply to your client or a reply to your boss.
0:44:42 Something like that. This is how you do it. Reply to this is really fun. Okay? So we're gonna send re we are going to send emails on our behalf.
0:44:52 So we don't want to do this, but I want to point this out that you can do this. Great. We need, we need all we need is these three things we need send email recipient body.
0:45:02 Let's just copy this and put it in here. Send email recipient, subject body. Okay. Variable equals uh, variable re sippy and equals.
0:45:18 Um, in this case, I actually, I just want to do Andrew, a better sheet stock. Oh, for now our subject variable subject equals, um, we're going to get this in a second.
0:45:32 We're going to use actually subject body. Okay. This is going to be confusing, but this is going to take two things.
0:45:43 Then take the subject in the body and we're just going to call it subject and body variable body equals body.
0:45:48 I don't think we necessarily need this, but we're gonna just do it. And we'll, you'll see what happens. Okay. Are whenever we send an email, it's going to be to Andrew.
0:45:58 We're going to change that. Eventually. We're going to send a subject the body. Okay. Let's see how this goes. First.
0:46:06 I think we need to run it and we're going to need to authorize something. I think we're going to need to authorize stuff.
0:46:14 Let's see if it here. Um, ah, we can kill this. Let's go back here. This is our subject. This is our body.
0:46:28 And if we do equals email, send a to colon, comma, B2 hit enter. I think it's going to keep loading error.
0:46:40 You do not have permission. This is so good. This is cool. So we go back review permissions. We're going to get permission to do this.
0:46:48 Then it's going to be so much fun. We give it permission. Give it permission to allow. Allow. Yes. Yes. All right, let's go back and try it again.
0:46:57 Let's I'm going to hit enter again. We're going to copy and paste error. Oh, we need the permission. Oh, all right.
0:47:07 Let's figure this out. We need more permission. We need permissions here. Required permissions. We'll get through this outside the domain of legacy API.
0:47:20 All right, we're going to need to do this a different way. We need to do this from a menu item.
0:47:25 So, uh, what we need is, um, I, this is gonna get you a little more complicated menu. I always need to do custom menu, Google script.
0:47:39 I always have to Google this or just going to copy paste this. Get a custom menu, use all of this and put it at the top.
0:47:51 Now we can delete these menu items here. We only need one menu item. We're going to add a email. Can I hit command S now what we need to do is go back to our sheet and save it and make sure the project saves.
0:48:15 <inaudible>. We're going to get these variables in a different way. So now that we have our on open and we're going to get a custom menu, I'm going to call this actually email menu, send email menu and hit command S save.
0:48:32 We're going to go back to our sheet. Now we can't do it this way. So we're gonna refresh and it will open now eventually a menu next to this help.
0:48:44 It will be really cool. See, now it has sent email menu and we have email send here. All right, let's go to our extensions, back to our script, check it out.
0:48:55 Let's see what I think I did something wrong here. I think this first item should be send email. And the second item should be send email, send the name of the here this here.
0:49:07 Okay. We have two things we need. We don't need these anymore. All we need is a prompt to Oregon to do is we're going to say who's our recipient.
0:49:17 We're going to get a email address. We're going to copy paste an email address. We need to get that as a prompt.
0:49:24 We're going to get the subject and the body from a row. So looking at this, we want to send this subject, wondering this body here to a recipient while our recipients are going to be our press contacts.
0:49:42 So if we know this, we know the email address we want to send to. We don't want to go over to our Gmail app and have to type out this email.
0:49:50 We don't have to call. We don't want to have to copy paste all this stuff. We just want to go here.
0:49:54 Email send. Actually we can refresh and fix that. Okay? So it's loading now and it's still let's come back to our extensions.
0:50:04 Let's fix this for once. And for all, it must not have saved. So we'll do that begins in email. And the second part here has to be email, send command S but we also want to delete, oh, this let's add our prompt.
0:50:26 Um, we want prompt in Google script. We always have to Google everything PR response. So here we have our get response text.
0:50:42 We will do all of this. Cool. So now what happens is our function, email send is going to get a UI.
0:50:53 And it's going to ask this question. Uh, we want to say, actually not, may I know your name. We want sending email.
0:51:05 You want to whom to which email address. Now, the response that we get is going to be the email address, email app.
0:51:21 We want to log this just to check that we're doing everything okay. Um, but now our, we have a variable.
0:51:32 We have this variable response, got get response text. This is going to be cool. Variable recipient. It's going to be that text.
0:51:46 Whoops. We also need one more thing. We need to get another, uh, prompt Row, Which email to get. She said what's wrong.
0:52:09 And we want the response. This is going to be response. Row response row. Just have to change all these to response row, to make them different than response up here.
0:52:31 Okay. Now we get response road, text variable response road Get Row equals. Okay. How do we get the subject? Oh, this is the F this is a fun one.
0:52:47 We have to do this. Let's uh, add punches. Let's get here. Okay. We have a row. We know the row number.
0:52:56 We know who we're sending to. We got to get this subject. What do we do? We do variable S S equals spreadsheet, app dot get active spreadsheet.
0:53:11 Di uh, then we do variable. That's a start get range. What is the range? It's going to be the row.
0:53:29 It should tell us what we need. All right, this should be row number. So this should just be row. Here.
0:53:45 It is. So get ranges of row. Any column, the column we want, the subject is going to be number one.
0:53:50 The first column we want to get one row. We want to get one column. We only want one thing. And then when we want to do dot get value.
0:54:02 Cool. Now the body is going to be the exact same thing. We just copy and paste this, except we don't want the first column.
0:54:09 We want the second column. All right, now let's test this. Let's we don't really want to send any. Actually, let's try to send an email.
0:54:16 We're sending an email to ourselves, going to hit save. What I like to do is I like to open this in another tab and just look at the execution.
0:54:27 This is the executions. We'll close a bunch of other tabs where we staked. All right, here we are. This is great.
0:54:36 We show our failures. We show our completions. We show it completed this unopened. It's created this, uh, our menu. We want to double check that our menu is correct.
0:54:46 So we will refresh all the stuff we'll close. We will go back to extensions app script. Let's look, our menu, send email.
0:55:00 We have a space. Perfect. We are on our way. Rocking and rolling. All right. So on open, we have our emails.
0:55:06 And when we click this button, this will occur. Now what should happen? And anything that happened doesn't happen this way.
0:55:14 We'll fix, but what should happen? Let's look at our, we want to do that in another tab. What should happen?
0:55:23 As a prompt should come up and say, Hey, you're sending an email. Who should we send it to? Which email address?
0:55:28 Then another prompt should come up and say, what role are we? What email are we sending? Give us a row number.
0:55:33 And then that email should be sent. Let's see if this is going to happen. This is really exciting. Okay. First we need to authorize.
0:55:41 This is what we needed. We want to authorize our emails. Great, great, great. Yes. Now we authorize authorize. You're going to have to go through this.
0:55:49 Let's do it again. Let's see what happens. Don't cannot call document. I get it. Oh shoot. Alright, we'll fix this.
0:55:58 All right. I made the, the stupidest blender. This is document app. We need spreadsheet app. Uh, we need, yeah, so that's a stupid blender on my part.
0:56:09 Let's dismiss this. If we go back to our executions, we see failed PR. This is actually great. It failed. We know why we did it.
0:56:14 We had document app, not spreadsheet app. Let's try this again. Which email address? Great. Let's do andrew@bettersheets.co. Yes. Which row we want to do?
0:56:28 Row two. Oh, this is exciting. We're going to hit. Yes. Did it send, finish? It said finished script. Let's check our mail.
0:56:38 So it did send, but we have a little error. We have just the word wondering, um, let's go figure out why that happened.
0:56:48 Um, we've got column two row is two column, two number of rows. This body didn't work. Why didn't it? Oh, because it did work.
0:56:58 The body was wondering one. If you're still taking submissions. Oh, it worked actually. Okay. Let's send email again. Let's try that again.
0:57:09 Let's do, I'm gonna do campy@gmail.com. That's my personal email. You ever care? Click. Yes. Which row to yes. Finished script.
0:57:21 We have sent the email. Let's go double check. And they're in our Gmail app is the send email. Wondering if you're still taking submissions.
0:57:31 We have sent an email from Google sheets. This is really cool. This is really fun. So now we don't have to take the time to open our Gmail app.
0:57:43 We don't have to take the time to add it to a pipeline anywhere else we can do that from this script.
0:57:50 So cool. So there is a little bit of a feedback loop. We have not told ourselves in sheets that we've sent that email.
0:57:59 So what I'm going to do is I'm actually going to make a whole video about logs elsewhere, but we're going to create an email log, email log, all caps.
0:58:11 Um, and we want to know what emails we send to home so that we can track this information inside the Google sheet.
0:58:18 We don't have to scrape our Gmail. We can do this all with code. Let's go back to our code. Um, super simple to do this at the end.
0:58:27 Once we've sent that email and we know it's sent meaning we don't get an error. If we get an error on this line 38, and nothing else will run.
0:58:35 So we want to go to S S what do we want to do? We want to, let's just write it out.
0:58:42 We want to put in email address, uh, two column one. We want to put in subject in column two. Oh, actually we don't want column one.
0:58:59 We want, this is column two. We want column three. We want timestamp in column one. We want to put in a body of email in column four, just to make sure we got everything right.
0:59:20 Okay. So we have a very, we know the subject. We know the body, uh, we know the recipient is going to be this.
0:59:28 So we go, uh, actually we can really sort of write it out, especially to uptight active it's active spreadsheet dot get sheet by name.
0:59:39 We want to add it to email, log dot, get range. Sorry, typing this all out. We might put on separate lines in the second.
0:59:52 The row is going to be last row. Um, yeah, actually I will do this on separate, okay. Variable log equals this.
1:00:08 Now we're going to do variable last row equals log dot. Get last row. That's easy. And we want to do timestamp actually, before we do the 10th, sometimes a little weird.
1:00:25 We want to do, um, Log dot, get range. We want the range to be last row. Plus one column two, and one dot set value is going to be recipient.
1:00:51 So what's this doing? This is doing something really cool. This is setting the value of the recipient in column two when we execute this code.
1:01:01 So let's save this and see if this happens. Um, we go back to our one second. There's something weird. Email templates.
1:01:14 Let me rewrite this email templates. It's one word, email templates. And I think there's something wrong in it. I feel like there's something wrong in our code because we grabbed the row, um, of the spreadsheet that we're on.
1:01:33 So we actually want to get sheet by name. So this is active by name. That's perfect. Now we want to only do it on email templates.
1:01:49 That's what we want. We want to make sure that our subject and our body are coming from the, the row on email templates, no matter where we are in the sheet.
1:01:57 So let's go to, like, if we go to email log and we send email, which email address let's do, and we see our recipient shows up here on our email log.
1:02:22 That's the first step. If we go back to our sheet, let's go see if I sent that. And there we got the email.
1:02:28 That's really awesome. We can keep testing that. All right. We want more than just the recipient though. Now that we've got the recipient, we need to add in log.
1:02:41 We want to do the same exact thing, but the subject, And instead of column two on column three, same thing, we want the body of the email column four, and we want body.
1:02:58 So let's save that and let's go back and do that again. Let's see what happens. Let's send a different email.
1:03:10 Let's send the third one. And there we are. Andrew campy. We got our Sipion. We got our update. Can we not listen to be updated?
1:03:19 We probably have it in our inbox. Bam, it's working. That's pretty cool. But what about that? Timestamp? Let's go. I'm going to Google it and we'll figure it out what it should be.
1:03:30 And I always sometimes get this wrong, but we do the exact same thing here. Copy this. But instead of recipient, we're going to do new date and let's see what happens when we do that.
1:03:57 We don't get anything. Ah, it was a silly mistake. It's because it's rewriting column one, column two. So now we just change it to column one.
1:04:05 Now we should get it fine. We gotta do this all again to test it. It's okay. Let's send it to, there we go.
1:04:19 We have a date. Now we have date and time. We have a timestamp of when we sent these emails. We know when we're sending emails, who we're sending them to, what are we sending them?
1:04:29 This is really cool. I'm so excited about this. All right, now that is our final section of what we plan to do, but I want to review what we've done.
1:04:40 We have created a PR agency from scratch inside a Google sheet. We can capture any kind of press. We want the title, the URL in one click with our bookmarklet.
1:04:48 We can execute the type. Also we can add more types if we want. Uh, we also curated. We add categories.
1:04:55 We have a dropdown menu. We have a whole sheet just for categories. That then is filled in on our main page.
1:05:01 Um, with the join, we've added a submit button, submit link. We can submit directly to these press. Um, but sometimes we have emails which we'll get to later.
1:05:12 We can contextualize, we can add notes. We can add clients. We can actually have a client sheet, uh, and we can share the sheet of press publicly or, um, sell it separately.
1:05:26 So we have two businesses we've created within this one sheet, right? We have a public sheet, um, that we can say, Hey, you can gain access to this pay 20 bucks a month, $50 a month, a hundred dollars a month.
1:05:37 And we'll be updating this. We'll send you emails when we have our update. And then we can also run the agency with that same list.
1:05:44 We did that then inside of our sheet, we wrote code so that we can send an email and we can send email templates based on what we write here.
1:05:53 And we can capture who we're sending to at what time we're sending to them when, and we can capture it.
1:05:59 Did we ever send it? Here we go. We can look at our email log and we can see, this is really, really powerful stuff.
1:06:05 We can execute our entire PR agency from this Google sheet. I'm so glad you stuck around to the end. Thank you so much.
1:06:14 Make sure you let me know if you can use this. If you can't, if you can't use it or whatever, it's really fun.
1:06:21 All right, bye.
Topics