Build A Business: PR Agency in a Google Sheet

In this video I present two potential business models for a PR Agency.

Transcript

Welcome. If you've ever wanted to curate content from the web, start a business, curating that content, and then also selling that content that you're curating, but also create a service upsell in order to continue curating that content. Do you wanna start a business like that? If you do this video, might be perfect for you.

In this video, I spend about one hour creating a sheet that is essentially a PR agency . I've created a PR agency within a Google sheet. Now what that really means. What we can break that down into is one. We curate some content from the web.

We curate, newsletters, podcast websites. We use a bookmarklet to do that. I show you how to do that. If you're not a member of Better Sheets. Now that might be a little confusing because I have an entire other video about that bookmarklet and I give the code away for members. So become a Better Sheets member today.

I also show you how to use that bookmarklet to capture content very easily capture websites, notate them, uh, categorize them and then also create a public sheet. So that's a private sheet. We get them into. We also create a public sheet that you can sell access to that sheet. On an ongoing basis, but now we might have clients say, we do PR we might actually do the outreach by email to these publications, or do the submissions on behalf of our clients.

So we have a submit button, we have contact information. We have email templates that we can write within the sheet. And then the, the ku de ta, the icing on the cake, is that we can send emails from the Google Sheet. So the second half of this video, I go into actually coding and showing you the exact code and actually how it works to send email from the Google Sheet.

This is an amazing video. I think for anyone who wants to start a business online, the only resources you have are like email and Google Sheets. You can start a full on PR agency. You can start a content curation business. There are so many use cases for this. It's so exciting. I really want you to watch this.

Um, if you can send email from a Google sheet, you can do a lot of cool stuff. You can also, uh, use this concept of sending information from a Google sheet to send weekly updates. You can add triggers. I don't do that in this video. In this video, I do not go into triggers, but I do have other video. In Better Sheets, where we go into triggers, daily triggers, weekly triggers, where you can send an email of whatever stats you're keeping track of inside of Google sheets.

There are a few moving parts of this video. When you start watching this video, you will see all the parts.

The second half is definitely very slow, cuz it's just coding. But it is exciting stuff because it's coding an email service inside of Google sheets. You'll see all the errors I make. You'll see the fact that I do document app instead of spreadsheet, app is a pretty silly mistake, but I caught it and we fixed it.

I'm so excited for you to watch this video. It is so long and so in depth and, and it's creating an entire business in one hour. Oh my God. Let's get started.

This is gonna be a really fun, interesting video. Well, might be fun for some people I'm going to build from scratch a brand new sheet. It just started sheet.new here, wrote down some notes here for this little intro. And, um, we're gonna create a PR agency from scratch. We're gonna create basically a sheet that allows us to capture publications, uh, sort of a modern publications which are newsletters, uh, podcasts, blogs, interview.

Uh, websites, we're gonna be able to cap, 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.

You can sell this as a, 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. 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 put, 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. Um, and you know, for a subscription of 50 to a hundred bucks, you get access to the sheet, uh, all the time. 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. 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. 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. 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 do adding categories and I'm gonna show you a really fun other way to do it, and it's gonna be pretty complicated, but it's gonna be really cool.

Basically. We're gonna join. We're gonna, um, the basic idea is it's gonna be a huge dropdown menu, but not the way you think of it. I think, uh, and we're going to use join as well. I think here, um, to make it easy to read. All right, contextualize, we're gonna add note, section, add notes and , and we also definitely need, we might add, sorry, add, uh, clients.

We might add clients here in this section, in the contact. We definitely wanna add, uh, contact info. We might want to keep track of, uh, if it's up, up to date, Up to date question mark. Um, and we want to deal with outreach. 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.

So this outreach part, we may or may not get to we'll find out. You might already know if you're watching this somewhere else. And there was an intro before I started talking, Ooh, this is exciting. What are we gonna get to? All right, how are we gonna do the capture? 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. 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, um, People they're looking for.

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 like main page of the podcast or the newsletter, but here's their submit, 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.

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. We might want to actually also add a client pay checklist. So in that case, what we would do is say, create a new tab in my mind. Now we would create a new tab.

and then be able to add, uh, these newsletters, podcasts and press from the main database into this checklist, with all that information. We'll see how that goes. And then we definitely wanna add contact information. Once we capture our, uh, E uh, press, we want to make sure we have contact information. We wanna 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 and here's where we're gonna start. The very first step most people will take is they'll start writing title, URL type. They might add a dropdown menu here, list of items they might be okay. We're gonna look at podcast newsletter blog. text interview, that kind of thing.

They'll add all these kinds of stuff, right. Round up, maybe. Uh, and they'll say, okay, here are, here's our type, here's our title. We'll just copy and paste titles and new URLs in here. We can do this better. What we can do is use a bookmarklet. We don't even need this page yet. We can go delete this page.

What we need to do first is go up to tool, create a new form.

We're gonna call this submit to PR. First question we're gonna have is title. It is great. It automatically makes it a short answer. We're gonna duplicate this cause it's easy URL. It should add. We don't need our data validation. We could do response validation. We don't need that right now. And we need type.

Now this is going to be drop down.

We're gonna do podcast newsletters. Um, blog interview is gonna be different than like a text interview, I think. Mm. We might wanna add a few other things we might add, come up with other ideas, like article or feature article, if it's a feature article about a person, and then we just want to take, uh, whoever that person writes about.

We have some clients that are like them. We wanna outreach to that writer. We also might just find one writer or author who does our industry. So we might wanna add them. These are sort of the types of things that we're gonna be rounding up. Okay. That's it for the. Form. If we go back to our sheet, we have a form responses.

I'm gonna call this submissions. 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 and capture these, uh, types of press, um, that's it press.

Okay. Where does the easy part come in? How, how is this at all easier? How is this at all better? Um, and I'm gonna show you through creating a bookmarklet. Okay. The first step we need to do is there is get pre-filled link. We need to get a pre-filled link. We want title to be, you know, something like the hustle URL of the hustle, something like this.

So we're just gonna do this once. We're gonna choose type podcast. No, not a podcast newsletter. We're gonna get link now. Copy link. Okay. We have this link. We're gonna do, let's see text. Oh, actually we can do it in Google docs.

Let's just paste this link. I'm gonna show you, there's a URL here. If you form, there's also this entry, we need this entry, um, and this entry. So this is the entry. What we're gonna do this right here. We're gonna do entry for title. We're gonna do entry for link URL. And entry for type right there. Okay. We got that.

Now we get our bookmarklet. So if you are a Better Sheets member, there is a video called bookmarklet or with the word bookmarklet. This is, uh, for available for every, um, this code and how to use it is available to every, uh, member. So go ahead and check out the video called bookmarklet. All right. We want our URL here to, we

want to replace our URL.

We want, uh, the HF is gonna be the URL. And again, I would go over this in the video, the title.

Is this so entry and that should be everything that should work now. All right, let's see. Um, one second. I need to check some, all right. Again, I go over the video in the video. Bookmarklet how to do this, but we're gonna go to our bookmark bar.

Gonna add this up here, edit. 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. We now have the name, the. And the URL URL immediately in there. And we can edit this. If we want the hustle. This is a newsletter we hit submit that was easy. Hit submit. I don't know if that button worked, go back to our peer agency and there we go. We have the hustle.

So if we, we can close all of these windows and now anytime we go to say, um, morning brew, if we're like, oh, we can access the Mor. We can submit press to the morning brew. We hit press there's morning brew. There's the URL, it's a newsletter. We hit submit. And now we go back to our sheet and there it is. So this makes it super easy to capture, uh, this kind of URLs into a Google sheet and then contextualize 'em with the type.

All right. Let's put a little checkbox and make sure we're getting all our boxes in a row.

gonna increase the size of this. Want a big old check box we captured now to curate. So now that we have our submissions, one, a couple problems come up very consistently in Google sheets. 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.

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. It's very cool. Um, always wanna submit stuff to him. I like this kind of. Duff. You're doing anything weird and nerdy.

That's my press agency. I'm helping people do weird and nerdy stuff. Land of random. This is a newsletter. Uh, we definitely want starter story. Let's get some more in here. I already have better. Sheets is a story on here. See, um, can check out how I'm doing. Uh, we want to add to press,

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. So what a form is doing is inserting a row. So just knowing that will help us understand what we need to do next. We need to, I'm gonna 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 array formula to get submissions.

Um, B two D, there we go. And this is how I sort of recommend is always having a separate page, let the, uh, form be its own page, um, for a variety of reasons. Um, you, you might want this data in two places, this array formula, you, you might wanna do things like sorting. You wanna 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. Um, but what we wanna do. Is, we want to add categories. We want a dropdown menu and join. So the number one way that people are gonna add a category is this they're gonna create category. They're going to say, um, data validation, they're gonna do list of items.

And they're gonna say, uh, startup, these are sort of keyword, uh, 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. And we wanna know who works well with that financial, financial, new news, um, funding.

We might have a category of founders, uh, women only, or feature women. We might want to. share sort of, yeah, different categories here. We can add more, but this is the typical way. This is the typical way people do it. They will say, save, they'll take this, uh, dropdown 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.

We can sort of, this gets really messy if we take this dropdown menu and my arrow key doesn't work, we paste copy and paste. Um, and we have category two. right. This, this gets really, really messy. We don't know. Should we put startups here? 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 keyword. This is really keywords that we want to be able to filter and, and, and sort through. So what we need to do is we need to turn this on our, on its head. I'm gonna delete this column actually, and do something else.

What we will do is add a new page. We will call it keywords or categories. We can it's either one. We will write startup up here. We'll write, um, news, maybe daily news, even cuz there might be, uh, niche news. I don't know. There might be others that we wanna do that have submissions. And now this is gonna be the fun.

We are going to do the same thing that we did before data validation, but we're gonna do lists from a range. We're gonna go back to our original sheet and use titles as our range. We're gonna hit. Okay. We are going to definitely have a dropdown menu. We're gonna hit save. Let's add a few more. No, we don't need to add a few more.

We're gonna copy this dropdown menu all the way down and think it's going to be okay if we let's see.

Yeah. Perfect. So now what this does, we try to just copy paste it all the way down, just getting it all across. 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. 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. Great. Let's say frowned or friendly. Now, how do we find the titles? Well, one, we can look at these titles and say, okay, the hustle, this is for the hustle.

We can select it, but we can also just start typing and we can say morning, there we go. 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. We say niche news. Um, the hustle loves niches, land of random, love it. Founder friendly, um, starter story.

and now we can just start typing. If we just remember the name, we don't have to copy paste. We don't have to move anything from here. And anytime we add a new one, it'll show up in our dropdown. Let's test that out. Let's

add a, um, let's add a pod, some podcast. So smashing the plateau. It's a podcast that I will be on soon. We've already recorded. Our episode should be out soon. Um, here's the podcast

and we can just add to the press. We have smashing the plateau and we know it's a podcast, click submit. We know it's founder friendly. I was on it. So we go to our keywords. Oh, wait, let's succeed. It's already there. It's already on our list of press. We name that press instead of sheet one it's founder friendly.

So we can go and say, smashing the plateau. Done. That's awesome. Isn't this fun. I find this fun. I hope you'll find this fun too. All right. Now the key thing here, the thing that we were missing before the, the KU DETA was that we want to have key wordss here categories, and we don't want just one. We want everything that matches.

Hmm. How are we gonna 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 hustle in. All right. We can do this a few ways. I'm gonna do it a really roundabout way, but it'll work. Um, we're gonna join with the top category every single row.

Then we're gonna join these all together. We're going to. delete a bunch of rows, just so that we, we are not gonna get more than like a few hundred of these. 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 roses rows.

We might only have, you know, 5, 10, 15, 20 columns. We can always fix that later here. We want to add, want to get rid of these riffs.

I'm just gonna delete these rows. 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, keyword at the top. So if we show, show you this, we have the hustle daily news, the hustle, niche news daughter, story, founder friendly, smashing the plateau founder friendly.

So these are categories and. I did this with join pipe and then keywords, B2. And then I did the locking sort of with the one column here so I can copy and paste this all the way down. So I'll show you how that works. So I did equals join. Eliminator is gonna be this pipe key above the return. Key on the right.

The value is gonna be keywords, uh, B2, the same cell that this is on the other page, and we want to combine it with keyword B one, but we don't want just be one. We want be dollar sign one. And now it'll ask us to auto fill. We do wanna auto fill, but I actually wanna show you, we can just copy and paste this all the way down.

We have a bunch of daily news. We have a, but we don't care about this yet. We want it to fill in. If we get more, what we do want now is we want all of these. a new, uh, in one column. So we'll at do that. We'll, we'll call it column. We are going to delete everything other than these two columns we're going to do equals, uh, equal.

And we're gonna use curly brackets here. We could also do array formula, but I like doing this pretty simple, uh, curly brackets. We're gonna do concat. We're going to use a, to a, we're gonna do a semicolon. Concat B, B semicolon. Concat uh, C colon C. 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.

Um, this can cat and I'm gonna rename it join. But if we go back to our column, it renamed it, join cool. Now what we actually need two columns here. We're gonna split everything. Split. The text is a one, uh, two, the denominator is the same thing that we use for join

and we get, what do we get? We get a bunch of values. There we go. We have our hustle daily news. Perfect. This is 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 can usually can sort of fix that. Um, I don't know if we really need to, right now, we don't at this moment, but now we have the hustle.

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. 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. 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 we're in there, so we can go. We don't want B to C. We actually just want C to C. Now we have all the categories, but you see here there's morning brew. Oh, we don't want that. So we'd want to join by a comma. We use a comma in this case and there we go.

Now it's all in one, one. and I think what we can do is we don't have to lock anything, but I'm gonna do it just in case we move this for any reason we want colon B and C and a two. Yeah. So we're gonna copy and paste this all the way down and we now see here's all the here's all of the categories or keywords in one place.

This is really cool. So I wanna 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. So if you're watching this video and you're like following along, you've got the bookmarklet, you've figured out how to get the information in you. Uh, 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? 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. but two, I have created a, a, 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, and to put it into a newsletter every single week.

Um, and I've created for you an entire bundle that actually creates the book market automatically with just a few clicks, um, much, much easier. Go and check out Better Letters. I'll put it, um, somewhere, you'll have a link somewhere to it, maybe in the description of this video. Um, but if you're a member of better, uh, sheets, uh, you can check out Better Letters and see what it's like.

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, uh, a page in which we combine the title and keyword, and we have keywords here that we can also just see a list of all the keywords.

We can assign things here, like, uh, morning proof it's fr or friendly. I don't know if it is, so I'm gonna add 'em. So we have cur, now we need to actually to finish this up before we check this off, we need to add a submit buttoning. We wanna add two columns to our press. We want a submit URL.

Yeah. And we want a button. So, this is gonna be pretty simple. Any, 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? Oh, there's my face. There I am. There's Better Sheets by the way, check out the, the, uh, video on and not video the interview I did with them.

Share your story. There it is. Share your story. So this is starterstory.com/share. We can submit any time we want our business name there. So we go to our submit URL. Copy it there, but let's say we want to, so a couple of things, why we might wanna add a button and not just a URL is yeah, great URLs are easy to see and I click on it.

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, uh, a little more vanity here and we can hit, we can do equals hyperlink. 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.

Right. But we can also make it really cool. We can make it look nice. We can also change the row height. 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. We can UN not ununderline it. So now. this brings our eye directly to, okay. I can submit to this.

Great. 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. We get a little, mm. Too much text. We want a little visualization. So in fact, we don't even need the submit text. 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. 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. So again, just adding a little bit more oomph to this.

We can show UNS, show the grid lines. We can make it a little nicer. We might want to add something like a dashed gray line everywhere looks okay. Not the best, but we are doing better than we did before. Great. Let's go back to our roadmap and we have done the cur. So now we really have the engine, the non-creative engine, right?

The, the, the guts of a PR agency. But we really have like a lot of other stuff like PR agencies do a lot of client briefs. 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. There might be contact information, um, beyond a submit page.

so that's what we're gonna do in this final two sections. We're gonna add some notes, add some clients, add a Chi client checklist, but first some more coffee. So this may seem simple. We're going to let's just go and do it. We're gonna add a note section. We're gonna insert a column to the right. We're going to actually delete all these other columns.

We have this weird button thing here. We're just gonna write notes and this is always good to have. Um, as we increase our database, we might want to say, you know, submission notes here. 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. Um, we have these notes, we have internal notes and let's say we are doing this all. Like if we are running a PR agency and we're doing the work for others, this is all perfect. 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, and specifically the press sheet, maybe there is, uh, we want to add over here to the right, uh, email contact.

It's right. Contact correctly. We have internal notes. We have, um, email last updated. This is like a date column, and we might want to do this in case the contacts go bad or not bad, but like, They a writer moves away. Um, the submit button doesn't work anymore. Um, they're sort of internal notes that might need refreshing that we might need it.

We might not wanna share certain email contacts. For instance, for instance, uh, pat walls runs starter story, and, uh, he used to use his Gmail. So there's like a lot of people who know his Gmail account, but like right now, if you submit to starter story, I think you get like pat@starterstory.com. So I would email him on his Gmail, but like most people now would email him on pat@starterstory.com or someone else at starter story.

So you're the press. The PR agency might have more contact information or different contact information than you might want to admit or share. So, and also like, do we really want to share this submissions page? Do we really wanna make it complicated for others? Not necessarily. So what we can do, we don't wanna make a copy.

We want to create a.

Let's close these, we wanna create a new sheet. We're gonna call this, um, press for public, like for public consumption. And all we're gonna do is do equals import range. We want the spreadsheet URL here, copy and paste this in quotes. We want the, uh, in quotes, we want the press, uh, tab exclamation point a, uh, I forgot the two G a colon G it's gonna ask us to allow access.

Yes, cuz we can allow access to another sheet and bam, there we go. 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 wanna sell this is here. We have private information here. We have public information here that we can sell access to via gum road.

You can even use only sheets, only sheets.xyz. 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. We can keep it restricted, add only sheets and bam. We are rocket and rolling with a, uh, data, uh, startup business. This is really cool.

And we can even use the data business to say, Hey, buy the data for 50 bucks a month. 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. 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 wanna do this.

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 Chi, a giant, a client checklist. We've added the notes to the page. We wanna add clients. And again, this is one reason why we had that public sheet is because if we have a client here, then, uh, we don't necessarily want to reveal that even to people who are our clients, we want one place where it is just ours.

Only we have access to, and we can list all of our clients in one place and, and map their progress through our agency. Right? So we can say, you know, business name, uh, contact, which could be like Better Sheets is one of our clients. Who's our contact there, Andrew, our email, Andrew, Better Sheets.co email me anytime we might have a URL, BetterSheets.co so simple.

Right. And we might have, uh, some ideas notes, right? 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. So create brief, create, uh, list concepts, send list, capture, new leads, send leads. This might be an entire process.

So we wanna do right here. Let's do insert check boxes and let's make these a little bit bigger. 15. He's probably gonna 14 now. Let's do 20. I like, I like him big. Let's do something cool up here. Let's actually change this to here. Like that. That's super cool. Right? right. Nice. Now we can see our progress throughout.

We can create a brief list, concepts and list capture new leads and leads. Boom. We are rocking and rolling. We have a PR agency. We have a client list. We have a checklist in a very different way, right? You might think, oh, we wanna do a vertical checklist, but this might be a checklist through our progress.

Our pipeline here. This would be pretty cool. I, I like this, this big old checkbox. We can't miss these check boxes. Nice. So go back to our roadmap. We have contextualized, we'd add a client checklist, which is our progress bar or our pipeline contact. All right. This is a fun one. I hope you've stuck around for this entire video to get to.

You might not know what's about to happen. Gonna write some script. Um, I, I, I sort of like this , what we wanna do is we have context information, right? We have this press. We have. Press here. We have internal notes. We have an email contact. We have pat starterstory.com. Um, we have our client Better Sheets, but what we're gonna do is we want to email on, on behalf, right?

What are we gonna do? Well, let's write some email templates. Some email templates might be just like, hi. Um, wondering, let's see. Just nice. Oh, thing. Wondering if you are still taking some submissions, let me know. Might have like a wonder. We might have like update.

Can we update an old listing? A. That kind of thing. Like we, we might have some email templates we wanna send, why do we do this in Google sheets is we may want to track how these are doing. We might want to say sent and responded. So we can do that pretty easily by adding like a increment number here, like five sent and responded to, we sent 10 of these 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. Um, we can also make sure we copy and paste. Few are, if you have a, 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. How are we gonna do that? How do we send this template?

To an email address. We can do that with code. We can do that. Uh, in let's let's start doing this. Let's do app script. I wanna share this with you. This is a really fun, oh man, this is gonna be complicated. Let me show you the core of the email function. So email, uh, send we'll call it. What do we want? We have any, mm dunno if we have, yeah.

Email send, we have mail app in here can send email and it tells us what we need to get here. We have a two subject. I'm gonna go. And there's a simpler one. Actually. No, this is perfectly fine.

No, there's a simpler one, one second. All right, here we go. So this is the, the fun thing. We, we can send an email from Google sheets. Here's our script that we need to do. We mail app.email send, and we, all we need is a recipient, a subject line and a body. That is it. That's all we really need. We can do as well.

Some options like there's a reply to, um, somewhere here send, ah, I like this one. So if we are sending emails on clients' behalf, so one, we are doing a press. We are, we are doing a press agent, a PR agency right now, and we are gonna send emails, but we want those replies we're we are the ones that have the relationships with the press.

But if you are 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 only, you really just wanna do the outreach. What you can do is send email as someone else and as yourself, but they reply to your.

Client or reply to your boss, something like that. This is how you do it. Reply to this is really fun. Okay. So we're gonna send, we are going to send emails on our behalf, so we don't wanna do this, but I wanna 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.

Let's just copy this and put it in here. Send email recipient, subject body. Okay. Variable equals uh, variable recipient equals. Um, in this case, I actually, I just wanna do Andrew at Better Sheets, doco for now our subject variable subject equals. Um, we're gonna get this in a second. We're going to use actually subject body.

Okay, this is gonna see, be confusing, but this is gonna take two things. Then take the subject in the body and we're just gonna call it subject and body variable body equals body. I don't think we necessarily need this, but we're gonna just do it and we'll, you'll see what happens. Okay. Our, whenever we send an email, it's going to be to Andrew.

We're gonna change that. Eventually. We're going to send a subject in the body. Okay. Let's see how this goes. First. I think we need to run it and we're gonna need to authorize something. I think we're gonna need to authorize stuff. Let's see if it here. Um, uh, we can kill this. Let's go back here. This is our subject.

This is our body.

And if we do equals email, send. A two B2 hit enter. Think it's gonna keep loading error. You do not have permission. This is so good. This is cool. So we go back review permissions. We're gonna get permission to do this. This is gonna be so much fun. We give it permission from we give it permission to allow, allow.

Yes. Yes. All right, let's go back and try it again. Let's we're gonna hit enter again. We're gonna copy and paste it. Error. Ah, we need the permission. Oh, all right. Let's figure this out. We need more permission. We need permissions here. Require permissions. We'll get through this

outside the domain of legacy API. All right, we're gonna need to do this a different way. We need to do this from a menu item. So, uh, what we need is. Um, hi, this is gonna get a little more complicated menu. I always need to do custom menu, Google script. I always have to Google this, but we're just gonna copy paste this.

Get a custom menu, use all of this and put it at the top. Now we can delete these menu items here. We only need one menu item. We're gonna add a email.

Can hit come, gonna hit command. S now what we need to do is go back to our sheet. We're gonna save it, make sure the project saves.

We're gonna get these variables in a different way. So now that we have our on open and we're gonna get a custom menu, we're gonna call this actually email menu. Send. Email menu gonna hit command S save. We're going to go back to our sheet. Now we can't do it this way. So we're going to refresh and it will open now eventually a menu next to this help.

It will be really cool. See, now it has send email menu and we have emails send here. All right, let's go to our extensions, back to our script and check it out. See what I, I think I did something wrong here. Think this first item should be send email. And this second item should be send email, send the name of the here this here.

Okay. We have two things we need, we don't need these anymore. All we need is a prompt. So what we're gonna do is we're gonna say who's our recipient. We're gonna get a email address. We're gonna copy paste an email address we need. to get that as a prompt, we're going to get the subject and the body from a row.

So looking at this, we wanna send this subject, wondering this body here to a recipient while our recipients are going to be our press contacts contexts. So if we ha know this, Hmm. We know the email address we wanna send to, we don't wanna go over to our Gmail app and have to type out this email. We don't have to co we don't wanna have to copy paste all this stuff.

We just want to go here. Email send, actually we can refresh and fix that. Okay. So it's loading now and it's still, let's go back to our extensions. Let's fix this for once and now for all, must not have saved. So we'll do that again. Send email. And the second part here has to be email, send. Command S but we also want to delete all this.

Let's add our prompt. Um, we want prompt in Google's script. We always have to Google everything response.

So here we have our get response text. We will do all of this.

Cool. So now what happens is our function, email send is going to get a UI and is gonna ask this question. Uh, we want to say actually not, may I know your name. We want sending email. We want to whom to which email address.

Now, the response that we get is going to be the email address, email. We wanna log this just to, to check that we're doing everything okay. Um, but now our, we have a variable. We have this variable response dot get response text. This is going to be cool variable. Recipient's going to be that text. Oops. We also need one more thing.

We need to get another, uh, prompt row, which email to get, to send which row. And we want the response.

This is gonna be response row response row. Just have to change all these to response row, to make 'em different. Then response up here. Okay. Now we get response row, text variable response row, row equals. Okay. How do we get the subject? Ooh, this is the, this is the fun one. We have to do this. Let's add a bunch of this.

So here. Okay. We have a row. We know the row number. We know who we're sending to. We gotta get this subject. What do we do? We do variable SS equals spreadsheet, app dot get active spreadsheet. Uh, then we do variable

SS dot get range. What is the range? It's gonna be the row. Actually, it should tell us what we need.

All right. This should be row number. So this should just be row. Here it is. So get range is a row. Any column the column we want, the subject is gonna be number one, the first column we want to get one row. We wanna get one column. We only want one thing. And then when we wanna do dot get value. Cool. Now the body is gonna be the exact same thing.

We can just copy and paste this, except we don't want the first column. We want the second column. All right. Now let's test this. Let's we don't really wanna send an actually let's try to send an email. We're sending an email to ourselves, gonna hit save. What I like to do is I like to open this in another tab and just look at the execution.

This is the executions. We'll close a bunch of other tabs where we mistaked. We have all right here, we, we, this is great. We show our failures. We show our completions. We show it, completed this on open. It created this, uh, our menu. We want to double check that our menu is correct, so we will refresh all the stuff will close.

We will go back to extensions. App script. Let's look, our, our menu send email. We have a space. Perfect. We are on our way rocking and rolling. All right. So on open, we have our email sent, when we click this button, this will occur. Now we, what should happen. And anything that happens doesn't happen this way will fix, but what should happen?

Let's look at our, we wanna do that in another tab. What should happen is a prompt should come up and say, Hey, you're sending an email. Who should we send it to? Which email address? Then another prompt should come up and say, what row are we, what email are we sending? Give us a row number. And then that email should be sent.

Let's see if this is gonna happen. This is really exciting. Okay. First we need to authorize. This is what we needed. We wanna authorize our emails. Great, great, great. Yes. Now we authorize authorized. You're gonna have to go through this. Let's do it again. Let's see what happens. Don cannot call document. I get, oh, shoot.

All right, we'll fix this. All right. I made the, the stupidest blunder. This is document app. We need spreadsheet app. Uh, we need, yeah, so that's a stupid blunder on my part. Let's dismiss this. If we go back to our executions, we see failed part. This is actually great. It failed. We know why we did it. 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 wanna do? Row two. Oh, this is exciting. We're gonna hit. Yes. Did it send, finish? It said finish script. Let's check our mail.

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. Um, we got column to row is two column, two number of rows. This body didn't work. Why didn't oh, because it did work. The body was wondering, wondering if you are still taking submissions. Oh, it worked actually.

Okay. Let's send email again. Let's try that again. Let's do, I'm gonna do Kamphey@gmail.com. That's my personal email. If you ever care click. Yes. Which row two? Yes, finish script. We have sent the email. Let's go double check. and there in our Gmail app is the send email. Wondering if you're still taking submissions.

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. We don't have to take the time to add it to a pipeline anywhere else we can do that from this script. so cool. So there is a little bit of a feedback onlo we have not told ourselves in sheets that we've sent that email.

So what I'm gonna do is I'm actually gonna make a whole video about logs El elsewhere, but we're gonna create an email log, email log, all caps. Um, and we want to know what emails we sent to whom. so that we can track this information inside the Google sheet. 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. Add at the end, 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, nothing else will run. So we want to go to SS. What do we wanna do we want to, let's just write it out. We want to put in email address, uh, to column one.

We wanna put in subject in column two. Oh, actually we don't want column one. We want, this is column two. We want column three. We want timestamp in column one. We want to put in. uh, body of email in column four, just to make sure we got everything right. Okay. So we have our ver we know the subject. We know the body, uh, we know the recipient is going to be this.

So we go, uh, actually we can sort of write it out. Spreadsheet, app.active. I think it's active spreadsheet dot get sheet by name. We want to add it to email log dot, get range,

sort of typing this all out. We might put on separate lines in a second. The row is gonna be last row. Um, yeah, actually I will do this on separate, okay. Variable log equals this

and now we're gonna do. Variable last row equals log dot. Get last row. That's easy. And we want to do timestamp actually, before we do the time timestamps a little weird, we wanna do, um, log dot, get range. We want the range to be last row. Plus one column two, and we want dot set value is going to be recipient.

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. So let's save this and see if this happens. Um, We go back to our one second. There's something weird. Email templates. Let me rewrite this email templates, this one word, email templates.

And I think there's something wrong in, I, I feel like there's something wrong in our code because we grab the row,

um, of the spreadsheet that we're on. 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. That's what we want. We wanna 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. So let's go to, like, if we go to email log and we send email. which email address let's do.

And now we see our recipient shows up here on our email log. That's the first step. If we go back to our sh let's go see if we send that in there. We got the email. That's really awesome. We can keep testing that. All right. We want more than just the recipient though. Now that we got the recipient, we need to add in log.

We wanna do the same exact thing but the subject, and instead of column two, we wanna column three, same thing. We want the body of the email column four, and we want body. 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. Let's send the third one. And there we go. Andrew Kamphey, we got our recipient, we got our update. Can we an old listen, be updated? 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 gonna Google it and we'll figure it out what it should be.

And I always sometimes get this wrong, but we do the exact same thing here. Copy this, but instead of recipient, we're gonna do new date and let's see what happens when we do that. Mm-hmm

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. Now we should get it fine. We gotta do this all again to test it. It's okay.

Let's send the two. There we go. We have a date. Now we have date and time. We have a time stamp of when we sent these emails. We know when we're sending emails, who we're sending them to, what are we sending them? This is really cool. I'm so excited about this. All right. Now that is our final section of what we plan to.

but I wanna review what we've done. 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 URL, and one click with our bookmarklet. We can a execute the type. Also we can say add more types if we want. Uh, we also curate it. We add categories.

We have a dropdown menu. We have whole sheet just for categories that then is filled in on our main page. 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. 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. 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. And we'll be updating this. We'll send you emails when we have update.

And then we can also run the agency with that same list. 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. And we can capture who we're sending to at what time we're sending to them when, and we can capture it.

Did we ever send it? Here we go. We can look at our email log and we can see, this is really, really powerful we can. Execute our entire peer agency from this Google sheet. I'm so glad you stuck around to the end. Thank you so much. Make sure, uh, 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.

📍 Wow. What a sheet. Woo. That was an epic video to share with you. Starting from scratch, literally sheet.new to creating two sheets of private sheet, a public , curation with a bookmarklet and we also sent emails and kept an email log.

I hope you enjoyed that video as much as I enjoyed making it. I hope you can use it. Create a business, create multiple businesses, create new products. , if you are creating sheets to create products and sell things, or even a lead magnet, maybe you already have a PR agency. Maybe you already have a service agency , and you can share a little bit of the information that you're collecting as a product. Now, if you are considering something like that, either as a lead magnet, trip wire, or a brand new product and trying to create a whole new revenue stream, consider getting OnlySheets, . You can find out more information at OnlySheets.xyz

it's something I sell, which adds a script to your Google sheets. That anytime someone buys something on Gumroad from you, you can send them a Google sheet.

Also consider purchasing Better Letters, which automatically gets you that bookmarklet. So I, I showed you the bookmarklet earlier in this video. It was how we captured information. I have a video for Better Sheets members that goes deeper into that and shows you exactly the code and exactly how to deal with that code and, and use it.

But I also created Better Letters, which it runs the script for you and gives you within a few clicks that bookmarklet. I'll put those links in the description below. All right. Comment below how you're going to use this. If you are starting a business.

If you don't even have an idea for a business, I have some other videos on this channel giving you ideas on businesses. You can start with Google sheets with Google sheets and Gumroad. I also have a free database of 100 business ideas.

That you can start with just Gumroad and just Google sheets. So get that that's free. You watched this entire video, so make sure you like it. I would be remiss if you didn't like this video. , thank you so much for watching.

Watch the whole Video: