Hey there stranger!

Sign up to get access.

Make Unsplash in a Google Sheet w/ BuyMeACoffee - Part 1

About this Tutorial

Create a photo search site inside with Google Sheets. Using this template you can empower users to: Enter a search term and get an image without leaving the sheet! And learn how to monetize this Google Sheet.

Featured Formulas

Video Transcript

 Hello, welcome to, uh, Sunsplash, where I show you how to create a Unsplash in a Google sheet. Um, how this works is exactly like Unsplash, where you can enter a search term here. Um, let's see, jungle, and you get images here in this, uh, column. And then also I added a extra special thing. So Unsplash is, Free photos to download.

But if you're a photographer, if you are, um, sharing your photos with people, you can absolutely give them away for free. Or, um, you can use buy me a coffee, uh, to monetize them. So like, if you want to say, Hey, link to buy a high res version, um, you can, and I'll show you that as well. So, um, you don't have to do this and just give away your photos.

If you have some kind of way to have an audience that wants your photos, you can actually make a search. Uh, site for them all in Google Sheets. So let me walk through what we're gonna do today. Um, we're gonna create the database. We're gonna use photo URLs. I'm gonna show you two ways to put the images inside of Google Sheets.

Um, and they each have their issues, so you'll see. , which way you want to do it. And then I'll show you coffee links, uh, how that works over a, buy me a coffee and buy me a coffee, by the way, is free. So if you're looking to monetize your images, that's a great way to do it with these coffee links. And then I'll show you how to do a search by keyword.

We're gonna use the query formula to get this list of results. Um, this is all done for you in this video. If you are watching this and you don't have access to all of the videos at better Sheets, I just wanna take the moment to tell you you should go get access to all of them. Part two of this video is in there.

That doesn't mean you can't do the entire thing in this video. In this video, you're gonna be able to complete this all away. But in part two, I'm gonna just do a couple more things that are very interesting. So one is I'm gonna add quick. We're gonna add quick links to prefilled results. So you can add under here, like, uh oh, you want to go see?

and you can literally click these and get sent to a sheet and you have bubbles there, or you wanna see the jungle. That way you can lead your users, uh, to the places you want to.  lead them. Uh, then in, uh, we're also gonna create another sheet. So sometimes you want to save your database and you don't want people at have access to that.

I'm gonna show you how to do that with import range, where you can give them access to a separate spreadsheet that has the same data and you're gonna have to be able to protect your, the sheet with your protected cells protected sheet. Um, I'm also gonna be able to share, share with you how you protect, uh, all of the other cells on a sheet other than.

in that case, it's the search one. Um, and then we're also gonna fix an issue with case sensitivity, um, in our search, uh, in our second video. But today, right now, I'm gonna show you all everything you need to know in order to be able to create your own Unsplash in Google Sheets, which I call sunsplash, like sheet Unsplash.

And again, it's just basically a search.  and we're searching for these links, and then we're actually using an index match over here. So I'll show you all that. So first, let's create our database and I'll share with you how to get these images. So, um, what happens is you can have a link. You put a link here to the actual, you can, if you have a hosted site and you have the actual link to the actual photo, you can put it here.

And then all you need to do is image and then that. . So you just need the image function. If you, however, don't have a place to store photos, you can store them on Google Sheet, uh, Google Drive, sorry. You can throw them into Google Drive. And then what happens is you can get images two ways into your sheet.

So one is you can literally go up to insert, uh, insert image in cell, and you can choose from Google.  go here. I have it in a folder called Sunsplash. It might take a moment to load. Where is my sunsplash? Here it is. As you can tell, this is a, a bit of a, um, mountainous. If you have, you know, a hundred, 200 photos, uh, you're going to get some issues.

So let's put this one in and so we can get that into a.  and it'll load eventually. Um, and then we can put a link here and then some key words like, man, bike, motorcycle. Just have to, in order to see this, have to open this up. There you go. That's the image, uh, to create the image link. So if we wanna sell the, obviously if you want.

Have a link to the image. You can put this link here over here. But in this case, we're gonna make this link a, um, a monetized link. Like they have to pay $3 to download this. So in that case, what you need to do is get the link to the, here, the shareable link and go to buy me a coffee. And this is my buy me a coffee for my new.

and in products you can enter a reward link, set a price, and create the link. So here we can literally just put that there, create a link. It would be easier. It's just a little bit of a time consuming if, again, if you have like, um, I don't know if you have 200 of these so you can customize your link. This is like Sun Splash example.

Um, and then. , oh, we could have to put in a number, number of coffees. So three or one for $3. You can set it for title is Sun Splash Example,

and then you should be able to save. And now you have a monetized link, as easy as, and it's gonna be in your product there as in your products. You can copy this link. Oh, where? You can copy this link and put it right here.

And so now we have a mono. What's interesting too is we cannot call this, um, with, uh, we cannot call this, uh, column with our normal, um, query. So I'll show you how to fix that real. . So now we have a database, we can create our, our keywords here. Now the issue is getting them search, right? If you have 500 or 200 even, or a hundred or 50 images, it's gonna be hard to find all the images we want.

And if you're selling like one or two images at a time, people really only wanna find the one or two they want. So our database is done and we can add to this as much as we want. Take as long as we want to add to it. But now we're gonna create the search. So all I did was put the uh, head.  of the results up here.

And so as you scroll down the search result, the search bar disappears. This search bar is literally just text. It's just jungle. You see there's nothing special about it. We can add bike there, and now we have our bike we can delete and if we have anything else into your key keyword. I have this here as an if.

If, if there's an error of this query, we're gonna put this. So that's the only complicated part there. But let me delete this and let me show you what the query is cuz this is the probably the most special thing, uh, you'll find. So this is a query, it says it's an error we put in jungle and we get our list.

There are two things actually, I wanna make sure you understand. There are two things here going on. One is there is a query and it's getting this, um, the row.  or all of the filters. This one, uh, for the image is actually an index match. And what that's saying is give me on ro c of our database, or call, sorry, column C of our database, match it to whatever this, uh, text is.

So I wanna tech get this text. So this could be a link to it. This could be, um, any kind of ID number, whatever you want to put in there, but I just put the link to make it easier match.  row. Find that row where this is and match it in column C. So we're gonna go, basically, I'll, I'll walk you through that.

It, it says finding column C, the row that matches the call, the row in column A. So find this, match it. That's it. But we need to get this query first. And this query is probably the most, um, complicated part of this search because we are searching. A single word or a single phrase within these key keywords.

We're not searching for the entire, um, thing. So the entire, uh, cell. So here's what we do. We use query. I'm gonna put this right, delete that. I'm gonna put this right here so we can see it. If we need it, we're gonna rewrite it and I'll walk you through it. So we go equals for the formula query. , uh, the first, uh, argument is going to be where in what range of cells do we need?

So DB is our database. Page A two is the second row to C. We only need A through C. We need to know the keywords. We need to have the, um, here we can even go and see. We need to get the image path, the keywords and the thumbnail. That's sort the three things we. . And so let's keep going and writing this query.

So the next argument we're gonna have is, um, if you know anything about, um, sql, this is very similar to sql. So we want to select or get, um, in the first column. So A, we don't have to do a if, if you do other kinds of formulas, you might have to do a column A in. For query. We don't have to, we just say a, where that's like the key word to find.

Like now we have an argument, now we have like a, a comparison. We need to compare and we'll get all of the a, uh, rows based on what the next comes. So B is the key word column. And it has, we use the key, we use the word contains. Now we do a space, and I end the quote. So this is the query and we're gonna add in.

This B five. Well, if we did B five here, um, and then we ended it, there's gonna be an error. So we need to get B five out of this quotation mark. How we do that is with the and symbol and B five. And now you see it's colored, so it's actually connecting. Gonna run into one more problem. Uh, in just a second.

You'll see it's still an. What we need to do is actually surround this phrase with quote marks. So we add a single quote just before the double quote. Now we do double quotes and inside the double quotes we do a single quote and we have to put an and there as well. So that is a little bit complicated to know, but as long as you get these and symbols and you put these tiny, quote, the single quotes around it, but in double quotes, I don't know if that sounds right.

That should.  and now we need to write this index match. So what is is index equals index? Again, we're doing DB in this case. Just select what we want. We want this and we wanna match. In this case, we're not using query, so we can actually use the real uh, one B seven. We want it to match a. , we want it, um, hard match and that's all we want.

And so we get our image and we can wrap that in an if error, do nothing. And now we can, we have that all the way down and see it's all blank down here. And it's just waiting for these results to come. And once the results are there, there's no error. And we get the thumbnail. What might. You run into problems here is in these images.

Um, you might think, well, what if we just do equals image and the url? Well, depends on what URL you have. If you're doing this in Google Drive, you might have like a drive image here and that doesn't work. So here, I'll, I'll put this here, I'll put this. We have, let's say that and we go image and we. Here, and it just doesn't show up.

Even though this is the, even though this is the image, uh, shared image url, it doesn't show up in Google sheets. So what we need to do now, we're gonna go a little deeper. We need to go find, uh, do this in a incognito window. We need to actually go find the, not the drive image, but the actual image in the source.

So we actually have to inspect the. You have to find the image source. And once you get this, um, uh, URL of the actual image source, let's put that in the URL there. Then image works. So we have that bubble image here now that's here. And see, it's like this google user content.com. It's this like CDN they're using.

And as you can tell, there are some issues with both, right? This is a very, um, manual process to put all these images in. But honestly, like if you are, if you want, if you have to do this for free and you're just trying to make a little bit of money, then this is probably the best way to do it. Just spend some time, um, putting the, getting these URLs.

Again, you can just in insert the image if you want. So there's two ways you can. , uh, image here, or you can, uh, go find the actual cdn, use Google user content and put that in there. Um, if you are just giving away the images, then you don't need to use coffee links. If you wanna make some money and sell these images and maybe high res version, um, or a package of them, uh, you can use coffee links to, uh, monetize that.

So we've made a data. Put, got the photo URLs from uh, Google Drive and we also did an insert image into cell, showed you coffee links, and we did the search keyword. We used the query formula to get a list of results. And then I actually also showed you index match to find, get the, uh, image off of those list of results if you want to, uh, continue and do more interesting things that should get you started.

And that should get you an unsplash of your own photos to sell as long as you. If you want to go further, go to better sheets.co. Uh, get lifetime access right now and I'll share with you. The next thing is like quick links to prefilled results. We'll, uh, create another sheet and do import range so we can, uh, protect our data.

We'll protect this, uh, sheet cells, and we'll also fix an image, uh, a search problem here. Basically there's a case, it's case sensitive, so if we do jungle, We get no results. We can do jungle. We get no results and I'll show you very quickly how to fit, fix that. Actually, it's using the lower uh, formula. If you already know that, then you don't need to know.

Um, but yeah, I'll show you how to fix that over@bettersheets.co. I'll see you there. Bye.