SubSpyHub Subreddit Scanner

Intermediate

About this Tutorial

I built a subreddit search tool to keep track of subreddits with keywords and interesting conversations.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

HYPERLINK() IF() ISBLANK() SUBSTITUTE()

Video Transcript

<div>0:00 So I'm building a subreddit scanner. This is a Google Sheet that I started using because I figured out a little URL trick to Reddit. 0:10 So if you go to Reddit and you search for, in a particular subreddit, so let's say we go to Google Sheets or Spreadsheets, let's actually spell that correctly, Spreadsheets, and we want to search here for a term like template , or even, like, a multiple word term. 0:38 We're looking for people here, like, where can I find free templates?

And I want to sort by newest, actually, because old posts sometimes are, uh, I can't comment on them, they're archived. 0:50 So here's a bunch of zero comments . Here's someone looking for help. Seeking suggestions on how to create a form or dashboard . 1:02 Like, these are helpful for me to create content for, or even just to go and answer this question directly here. 1:09 Maybe link to a video that I already made. Thank you.

Um, sometimes in some subreddits, links to external resources are banned. 1:20 And there's sort of lots of notes and things that I would want to keep around subreddits. Like, I want to keep this search, for example, how do I do that? 1:29 Well, up here in the URL is it's reddit.com slash r slash slash the name of the subreddit slash search slash question mark q, which is query , equals template . 1:43 And then it has later on and sort equals new. The, the stuff to the right of it is unnecessary.

And if I want to search for, let's say, I want to sort in a different way. 2:00 I want to. Actually, let's see hot, right? The sort equals hot and the URL. And so this URL is pretty cool. 2:07 I can actually save this exact URL to a sheet. However, I can generate this URL from a sheet. So we have a list of subreddits here on the left. 2:18 We'll have small business. And then I will create, actually I will delete all of this stuff. So we only see one. 2:26 So I put a link here and we're just using equals reddit.com slash r slash ampersand a3.

So let's actually zoom in a little bit. 2:38 And now this a3 is just the subreddit name. And so this is a link directly to the subreddit itself, which is cool. 2:45 I can now keep a link directly to the subreddit by just typing in the subreddit subreddit. But also I've made it so that I take that URL, add slash search, add question mark q equals, and then I take any term that I write in the C2, this, uh, second row, SEO tools perhaps, and it will automatically substitute 3:10 any spaces for the plus sign.

And The and add ampersand type equals posts and sort equals, and it will take whatever's in C1. So I have now a dropdown menu of new hot top relevance comments . 3:24 I want to see the most commented post in, in small business for the term SEO tools. Let's go see that. 3:32 It'll redirect me. And here it is. This is the most post, uh, most comments . Uh, and I can start searching through here and see what do people mention, what do people want? 3:44 Or I can say, hey, I want the newest ones or SEO tool, perhaps instead of tools, click it.

And now I see 21 minute hours ago, two days ago, any easy to use SEO tools for small businesses. 3:59 This is good for researching. This is good for posting. This is good for backlinks. If you're like, hey, I want to put a link here. 4:04 I have to go figure it out. And so this is a really cool tool. I can even in the same exact subreddit have multiple searches here that are saved. 4:15 Well, what if I want to find other subreddits similar to small business? There is a github let's see let's just go look at that. 4:27 It's anavakagithub.io slash say it.

And I can type in small businesses, small business, and it'll give me a list of all the related, actually not a list, but a graph of all of the related subreddits. 4:44 So I can go say, oh, entrepreneur ride-along, cool. That's an interesting subreddit. Subreddit that I might not have found. Startup, sweaty startup, side project. 4:53 Okay. These are really cool other subreddits. So let's go back to our sub hub and type that in.

So startup, let's put in product management or entrepreneur ride-along or entrepreneurship or just start and let's see, just start. 5:12 And now, I need to take all of my URLs and copy and paste them down. But now I can search the same thing, SEO tool for just start. 5:26 There we go. We got the newest posts. We can obviously go and read this and find out what the actual conversation is about.

But I want to add a few more things cause I want to make this sub-spy hub useful for you even if you didn't don't know how to use Google Sheets . 5:45 These URLs are already baked in but they're really hard to see. Like, this link will probably have to keep but like this URL here we probably don't need the entire URL in this cell. 5:57 We probably just need a link to it. Especially this related subreddit's one. As well, it's just this query of github. 6:04 Again, it's a really cool URL trick.

We're just replacing the text at the end with whatever the subreddit is here and we get a link directly. 6:12 Okay, now I can see startup. What are the related subreddits? There we go. Here's digital marketing, my combinator, fintech. It'll highlight the closest subreddit. 6:25 This one's grow my business. That's pretty cool. So now I can go back, say grow my business, and now I can search for SEO tool in there. 6:34 Really cool, right? Makes it easy.

And I want to add a couple more things like ratings or notes here, but I want to clean this up so it makes it look really cool. 6:42 And so you can download this, by the way, if you are a member watching this on BetterSheets.co. Down below, just get there. 6:49 This, uh, sheet, all sheets are available at BetterSheets.co for any videos that I'm making. If you're not a member, you're watching this somewhere else, you can go to BetterSheets.co slash sub spy hub. 7:04 And here it will bring you to get this sheet exactly $0. It's totally free. Get it right now.

Just get it. 7:15 It'll be on gum road, but get it at BetterSheets.co slash sub spy hub, but let's make it better. I want to make this look really cool. And so let's clean up these URLs. I'm going It's, and we're, take that hyperlink . We're going to label it. And now the label could be something like this. 7:35 Search, and we'll put that in quotes. And now that looks a lot better, right? We'll do the same over here. 7:46 Hyperlink that around here and put a link search. Pretty cool, right? Let's find related. So we'll do the same thing. 8:01 Link.

But we'll call this related in all caps. Easy, right? Cleans this up a lot. This link we will, we'll have to leave as is. 8:16 We might be able to make it a little nicer to view here. Show no grid lines. Let's, uh, just leave it as that action. 8:25 Actually, we want a little bit of color here. Gonna use some kind of red here. But these two, this is the problem with having these headers here. 8:35 These two are useful to change. So I'm gonna make it a different color. And I'm gonna take these drop-downs and edit them to only be an arrow. 8:49 All, yes.

And I will take the same background. So that will give a little indication that you can edit that. 8:59 Gonna make everything else dark red. And again, this is nicer to see. We have our URLs. We can just click and search. 9:08 But let's edit this name. I don't want just the text. Actually, I would search. Space, uh, search space ampersand C2. So now I have the term here. 9:26 Let's make this a little bit smaller. Need to make C2, uh, not C2. I'm going to hold it. 9:38 Uh, there, because it's just adding search. That's pretty cool.

I don't know if I really like that, but it helps you see that. 9:51 I mean, you can see the header . Uh, SEO help, something like that would, okay. Yes and no. I'm sort of 50-50 on, on, on, having that there. 0:02 If you don't like it, we can always delete that space and delete the end D2. But let's make this easier to maintain over time. 0:12 If we're adding our subreddits here, we're finding really cool comments . We're finding really cool subreddits that we want to come back to. 0:20 Let's add a rating or rating ranking.

Here we are going to insert a drop-down and we're just going to insert a star. 0:34 Let's do two stars, add another item, three stars, four stars, and then five stars. There is another way to do this. 0:48 It was at F. Three colon F. I want it to be an arrow as well, but now I can say, oh, that was a good one. 1:01 That was a pretty dang good one. Oh, that was the best one, right? There is another way to do this. 1:07 We can right-click smart chips rating and here. We have pretty much the same thing.

However, there is an underlying number . 1:22 So, if we change this to two stars, the actual text in the cell is two. So, up to you which rating you want to use. 1:33 I sort of like seeing these yellow stars. Don't know if about how you feel about this, but it has an underlying number . 1:42 So, we can actually rank these. Now, now we can re- sort these. So, for users of this sub-spy hub that are not necessarily knowledgeable about adding these links, I'm going to make it real easy. 2:02 you just have to enter text in the A column, or, and enter text up in the C and D.

I do want to give the ability to add more columns. 2:14 I will do that in a second, but right now, I want this to have, like, a hundred potential subreddits. And here, right at the beginning, We're going to add another image . 2:26 If, another formula , if is blank, a six, and two commas, and, and now, I'm going to copy paste that all the way down. 2:42 We don't see anything right away, but if we add digital marketing, right away, we have a link.

So, I'm going to add that, if is blank, a six, two commas, and parentheses, I'm going to add that all the way up to the top, and all the way to the bottom. 3:11 So this needs to be. There we go.

And also on the related, if is blank, dollar sign , a six, two commas, copy paste all the way down, all the way to the top. 3:45 And now, again, we can just add another subreddit, let's say, I don't know if this actually exists, business tools, boom, right away, we have our links. 3:58 Super easy to do, super easy to use, as a user, just add your subreddit here, and all these links are created. 4:06 Really cool, right? But now, let's make it possible to create a new column.

A new column for new keywords, because maybe we find some really cool keywords that we want to keep adding more keywords to our searches for the same subreddits. 4:23 I'm going to do extensions app script , I'm going to close a few of these. I'm going to go to bettersheets.co slash snippets. 4:31 We're going to get a little bit of code. This on open allows us to have a menu. So we'll have And we'll just say one word, sub-spy hub, create new sub-spy. 4:50 Column is the function . And for the user, they'll say, add or insert new column.

So we're going to create a program here, a little function , function , create new column, that anytime a user clicks that, we will add a new column. 5:10 What do we need to do? We need to find, And basically, we have- have two columns here.

Let's insert it just before the C column. 5:20 Get active sheet , get name we're going to make sure variable sheet equals this if sheet if sheet equal is equal to hub this is the sheet we're on hub not setting so if we're on anything else I want to make sure we are on there we're going to do add a column and if we're not else spreadsheet get active 6:00 need to be on hub tab so let's actually go test this we will create sub spy hub save that close it refresh our sheet so that up in the top menu now we have sub spy hub if we are on the settings let's double check this in certain column we need to authorize the first time we run it use it need to be on 6:33 the hub tab so we are going to be on the hub tab here let's go back and open Apps Script that is correct let's add a column we're going to do sheet no spreadsheet app dot get active sheet insert column before three so let's see what that does let's save it and just do it right now insert new column so 7:17 we get our drop down menu cool we get this text we need to put in this url so let's actually undo that and grab a not url sorry this formula we need to put it in c3 so we're gonna go spreadsheet app dot get active sheet get range row three column three set value and is it going we can put this in backticks 7:55 or let's see if backticks works yeah backticks so that oh we have this dollar sign don't want to use backticks let's just do single quotes yeah that should work because we have double quotes in here so we can't use double quotes to wrap the whole formula this is what right now is in c3 if we add a new 8:22 column we also probably want to put it in every single row but let's see if we can do array formula with hyperlink then we only need one ah we shouldn't do that because we wanted to sort if we want to let's leave it as is so let's save this and make sure that this is correct. 8:49 Insert new column.

And we have our search here we're going to sort by top for SEO help we now see that let's make sure it works we're searching in startup for SEO help pretty cool right but how do we now get this formula all the way down let's see so now what we need to do is create a for loop that goes 9:25 all the way down the sheet so we'll do for row dot equals 4, row is less than or equal to last row so we need to get the last row variable last row equals get last row actually we want to do get max rows and then row plus plus so we're going to iterate through all of these rows starting at four because 0:22 we already have three and we'll do spreadsheet active dot get range it'll be row column three set value and here So we're going to copy this whole thing. 0:51 whole formula again, but wherever there is a three here, we're going to add single quotes and inside plus plus and inside that row. 1:06 So we're going to take the variable of this row that we're iterating through in this for loop.

And each time we have to have a number . 1:15 There's B3 and is that the only ones think that's it? That's the only thing we have to do. So let's try this. 1:28 Let's go. So I have insert new column, get range is not a function . Oh, I spelled range with a E. 1:39 R-E-N-G, so it's range . Let's try that again. Delete that column. Let's spy, sub spy hub, insert new column. There we go. 1:52 And all the way down on exactly the row that we're supposed to be on, we have all of our URLs. 2:00 Pretty cool, right? Pretty awesome little tool we can create here.

Again, if you want to get this template yourself, this subreddit scanner, go to bettersheets.co slash sub spy hub. 2:13 Or if you are a member and watching this on bettersheets.co down below, get this sheet completely for free. And actually it's all, it's free for everyone. 2:22 But I thought this would be a really cool way to search and scan through subreddits different subreddits. Different keywords, ratings, we can add even, let's go with the rating there. Let's go with some notes here and let's delete all of the other columns. 2:43 There we go.

And you can always do something like this is freeze this first column and move stuff Except, I would not recommend moving anything around these because we are just inserting that new column. 3:00 We haven't really made this super foolproof because if somebody put something else in the C column other than this, it'll just, it won't copy this, uh, uh, drop-down menu. 3:14 So maybe that's a good little feature to add Later, if you actually think of some features you want to add to this, some really cool URLs you want to add to this, maybe some other tools like this related subreddit one is pretty cool. 3:24 If you have other tools you want to add to this, let me know.

Email me. Happy to answer. Happy to update this later on. 3:32 Enjoy. Bye.</div>