I Built a Subreddit Scanner in Google Sheets
In this video, learn how to build a powerful subreddit scanner using Google Sheets, leveraging URL tricks to efficiently search and track subreddit posts. Discover how to create a user-friendly interface for managing subreddit searches and extracting valuable insights.
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. 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, it's actually spell that correctly. Spreadsheets. And we wanna search here for a term like template or even like a multiple word term. We're looking for people here, like, where can I find free templates? And I wanna sort by newest actually, because old posts sometimes are, uh uh, you can't comment on them. They're archived. So here's a bunch of zero comments .
Here's someone looking for help. Seeking suggestions on how to create a form or dashboard like these are helpful for me to create content for, or even just to go and answer this question directly here, maybe link to a video that I already made. Um, sometimes in some subreddits, links, links to external resources are banned 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? Well, up here in the URL is it's reddit.com/r/the name of the subreddit slash search slash question mark q, which is query equals template , 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 wanna sort in a different way. I wanna actually see hot, right? The sort equals hot and the URL. And so this URL is pretty cool. 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. We'll have small business and then I will actually, I will delete all of this stuff so we only see one, so I put a link here and we're just using equals reddit.com/r/ampersand A three. So let's actually. Zoom in a little bit, and now this A three is just the subreddit name,
and so this is a link directly to the subreddit itself, which is cool. I can now keep a link directly to the subreddit by just typing in the 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 C two, this, uh, second row SEO tools perhaps, and it will automatically substitute any spaces for the plus sign and then add ampersand type equals posts and sort equals, and it will take. Whatever's in C one. So I have now a dropdown menu of new hot top relevance comments . I wanna see the most commented post in in small business for the term SEO tools.
Let's go see that. It'll redirect me. And here it is. This is the most post, the most comments . Uh, and I can start searching through here and see what do people mention? What do people want? 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. This is good for researching. This is good for posting. This is good for backlinks. If you're like, Hey, I wanna put a link here, 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. Well, what if I wanna find other subreddits similar to small business?
There is a GitHub and let's just go look at that. It's of anna github.io/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. So I can go say, oh, entrepreneur, ride along. Cool. That's an interesting subreddit that I might not have found. Startup, sweaty startup side project. Okay, these are really cool. Other subreddits. So let's go back to our SubHub 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.
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. There we go. We got the newest posts. We can obviously go in. S read this and find out what the actual conversation is about. But I wanna add a few more things 'cause I wanna make this sub spy hub useful for you. Even if you didn't, don't know how to use Google Sheets . These URLs are already baked in, but they're really hard to see, like. This link, we'll probably have to keep, but like this URL here, we probably don't need the entire URL in this cell. We probably just need a link to it, especially this related
subreddits one as well. It's just this query of GitHub. Again, it's really cool. URL Trick. We're just, we're replacing the text at the end with whatever the subreddit is here and we get a link directly. 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 ones, 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. 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. And so you can download this, by the way, if you are a member watching this on. Better sheets.co
down below. Just get this, uh, sheet. All sheets are [email protected] for any videos that I'm making. If you're not a member and you're watching this somewhere else, you can go to Better sheets.co/sub spy hub and here it will. I bring you to get this sheet exactly $0. It's totally free. Get it right now. Just get it. It'll be on Gum Road, but get it Better sheets.co/sub spy hub. But let's make it better. I wanna make this look really cool. And so let's clean up these URLs. I'm gonna put something around this called hyperlink . It's, and we're take that hyperlink and we're gonna label it. And now the label . Could be something like search and we'll put that in quotes.
And now that looks a lot better, right? We'll do the same over here, hyperlink . Put that around here and put a link search. Pretty cool, right? Let's find related. So we'll do the same thing, hyperlink , but we'll call this related. In all caps easy, right? Cleans this up a lot. This link, we will, we will have to leave as is. 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. Actually, we want a little bit of color here. I'm gonna use some kind of red. Here. But these two, this is the problem with having these headers here. These two are useful to change, so I'm gonna make it a different color and
I'm gonna take these dropdowns and edit them to only be an arrow apply to all. Yes, and I will take the same background, so that will give a little indication that you can edit that. I'm gonna make everything else dark red. And again, this is nicer to see. We have our URLs. We can just click in search, but let's edit this name. I don't want just the text. Actually. I want search space. Uh, search space ampersand, C two. So now I have the term here. Let's make this a little bit smaller. Need to make C two, uh, not C two. I'm gonna hold it, uh, there 'cause it's just adding search. That's pretty cool. I don't know if I really like that, but it helps.
You see that? 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 having that there. If you don't like it, we can always delete that space and delete the and D two, but let's make this easier to maintain over time. 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. Let's add a rating or a rating ranking. Here we are going to insert a dropdown and we're just gonna insert a star. And let's do two stars. Add another item, three stars, four stars, and then five stars. There is another way to do this. It was it f. Three colon FI want it to be an arrow as well, but now I can
say, oh, that was a good one. That was a pretty dang good one. Oh, that was the best one. Right. There is another way to do this. We can right click smart chips rating, and here we have pretty much the same thing, however. There is an underlying number , 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. I sort of like seeing these yellow stars. Don't know about how you feel about this, but it has an underlying number so we can actually. Rank these now. Now we can resort these. So for users of this sub spy hub that are not necessarily knowledgeable about adding
these links, I'm gonna make it real easy. You just have to enter text in the A column or and enter text up in c and d. I do want to give the ability to add more columns. 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 gonna add another, if another formula if is blank, a six. And two com commas at an end. And now I'm gonna copy paste that all the way down. We don't see anything right away, but if we add digital marketing right away, we have a link. So I'm gonna add that if is
a six. I'm gonna add that all the way up to the top and all the way to the bottom. 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. 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. Super easy to do, super easy to use as a user, just add your subreddit here and all these links are created. Really cool, right? But now let's make it possible to create a new column, A new column for new keywords.
'cause may maybe we find some really cool keywords that we want to keep adding more keywords to our searches. For the same subreddits, I'm gonna do extensions app script . I'm gonna close a few of these. I'm gonna go to Better sheets.co/snippets. We're gonna get a little bit of code this on open. It allows us to have a menu. So we'll have sub spy hub and we'll just say one word, sub spy hub. Create new column is the function , and for the user they'll say Add or insert new column. So we're gonna create a program here, a little function , function , create new column. That anytime a user clicks that we will add a new column.
What do we need to do? We need to find, basically we have two columns here. Let's insert it just before the C column. Get active sheet , get name. We're gonna 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 wanna make sure we are on there. We're gonna do add a column and if we're not else, spreadsheet , get active. Toast 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. Insert new column. We need to authorize the first time we run it, use it need to be on the hub tab. So we are gonna be on the hub tab here. Let's go back and open app script . That is correct. Let's add a column. We are going to do sheet, no spreadsheet app, get active sheet get 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 we get our dropdown 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 back ticks, or let's see if back T ticks works. Yeah, back ticks. So that, oh, we have this dollar sign . Hmm. Don't wanna use back ticks. Let's just do single quotes. Yeah, that should work. 'cause 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 column, we also probably want to put it in every single row, but let's see if we can do a array formula with hyperlink , then we only need one. Ah, we shouldn't do that because we want to sort . If we want to, let's leave it as is. Let's save this and make sure that this is correct. Insert new column and we have our search here.
We're gonna 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? Uh. Formula all the way down. Let's see. So now what we need to do is create a four loop that goes all the way down the sheet. So we'll do four row equals four. 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 wanna do get Max Rose. And then row plus plus. So we're gonna iterate through all of these rows starting at four.
'cause we already have three. Then we'll do spreadsheet active. Do get range . It'll be row column three set value. And here we're going to copy this whole formula again, but wherever there is a three here, we're gonna add single quotes and inside plus, plus. And inside that row. So we're gonna take the. A variable of this row that we're iterating through in this for loop and each time we have to have a number . There's B three. And is that the only ones? I think that's it. That's the only thing we have to, uh, do. So let's try this. Let's go. I have insert new column. GI range is not a function .
Oh, I spelled range with A-E-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. And all the way down on exactly the row that we're supposed to be on, we have all of our URLs. Pretty cool, right? Pretty awesome little tool we can create here. Um, again. If you want to get this template yourself, this sub Reddit scanner, go to Better sheets.co/sub spy hub. Or if you are a member and watching this on Better sheets.co down below, get this sheet completely for free. And actually, it's all, it's free for everyone, but I thought this would be a really cool way to search and scan through 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. There we go. And you can always do something like this is freeze this first column and move stuff around, except I would not recommend moving anything around these. Because we are just inserting that new column. We haven't really made this super foolproof because if somebody puts something else in the C column other than this, it'll just, it won't copy this, uh, uh, dropdown menu. 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 that's pretty cool. If you have other tools you wanna add to this, let me know. Email me. Happy to answer. Happy to update this later on. Enjoy. Bye. SubSpyHub Subreddit Scanner