Search for Photos in a Google Sheet by Keyword

Beginner

About this Tutorial

Add one more feature to our Photo search inside of Google Sheets.

Video Transcript

<div> Hey, sheeters sheet heads and cheat shakers. This video, I'm gonna add one more thing to search for photos in a Google sheet. We're gonna add the ability to search by keyword. So last sheet we made you might have seen the video. I'll go over it really quickly now, is you can insert your photos into a cell here, you can give them an ID here and then you. Search through those IDs or skip through those IDs here and get them on another sheet. We're gonna add a keyword search to that right now. And this is how it works.

So I can type in, I know the keyword, so if like you don't, if there's probably nothing for bat, it'll give you an na. But if you put in album there, if you put in pink, there you go. So pink. Then I think black also works. Nope. Doesn't, let's see. Here's all the keywords. Not very many albums. Oh, I misspelled albums. It's actually fine. Oh, desk, that's the other one. So if you type in desk, there you go. So some creative uses for this is say you want to obviously sell some photos if you want to make them searchable. Something like Unsplash.

I created a Google sheet called Sunsplash, which is basically. Unsplash in a Google sheet. You can go check that out. I think I'll provide a link somewhere here. And then also like internally if you are an art designer, creative director and you're like showing off, some types of illustrations or some mockups, you can, you might forget if you list, 10 to 20 or 30 options, you might forget like their ID number . And the ID numbers are pretty. Machin ish. Like they're not very human. Oh, what about number three or four or five?

You don't really know the difference between them, but you might be able to label them as a key word or add in keywords that you want to be able to search for later, knowing oh these three are all named after, Presidents or these five are named after trees. Like you can actually combine them with whatever your needs are, instead of just an ID number . You can use any kind of words you want. What might be cool too is you might wanna show off different aspects of the same thing. So if oh, this one is, let's see.

This is a light and this is dark, and okay. I want to be able to quickly, I don't want to have to remember those ID numbers. I can just quickly go dark or light and quickly bring up that image . Very. So how did we make this so this, the keyword. Cell has nothing special. It's all here in the search result. What we use are two functions , index and query . And I'll write them out right now for you so you can see what they look like. So I'll do it over here. So right first off, let's do the query cuz that's probably the most, the wor the hardest part. The index is pretty simple.

So query function , we're going to find our data . Our data is going to. Here in the keywords. And what are we looking for? Actually, we want to e because I made actually here, let me do this first. This is gonna air out. I'll show you what I did here. So instead of having to figure out what Rowe this is on, I literally just wrote Rowe equals row as a formula next to it to. Okay, whenever I get grab this one, I can just grab whatever row it's in and then I can index to the in image , if that makes sense. We'll get to that in a second. I'll show it to you so it makes more sense.

So basically query says we're gonna look through photos, columns, D through E. What is the query ? It's always gonna be in like quotation marks, and we can do it like this. We can select d. Where , no, we wanna select E, the one with the rose. We wanna select E where D contains, we have to write this all out in single quotation marks. We can do disk, it's a word well used. And then n quotation marks. Let's see what happens there. And right now we get actually the header and. The number, but we don't want the header.

So what we have to do is go back to our range and just put D two and that will take away the header . And now we have a number . So how do we convert that number into this image ? We'll use index and I'll come back to the query to make it a little better in a second. But index , we go to photos. Exclamation point D to D. Okay. And we're gonna do comma, and we're gonna get the row. But we don't want D to D actually. We want C to C. So we're grabbing the column that has the photo in there, and then we get the photo. But how do we search for it?

This word desk, we can come in here and we can just retype ping, but this is an awful way to search for. The image , right? We want to put it into another cell. So we want to refer to a cell. Here's what happens When we just do D two, this will not work, right? So we need to actually end the quotation marks, then add a am ampersand that's above the seven around this D two, which is our search cell. And doubly, we have to end the double quote here.

Start the keyword search here, then end the single quote over here. So another double single quote, then double quote to end the quotes cuz the actual query function still needs those single quotes. And we have it here. So now it refers to the. Now they both work. Here's what happens when you don't use those single quotes. I'll show you real quick. You get a value error . So it says, unable to parse query string for function query parameter two, no column dark.

So what it's saying is it's actually looking for a column, but you've entered this word at dark and we don't really know what that means. So what you need to do is literally. Put a single quote around it. And so you have to put it in those double quotes inside a single quote. Inside the double quotes gets you outside the quotes. That's, you're never gonna remember that. Don't worry about that. And one more thing too hopefully you don't fall into this trap, is you can, you will have to do lowercase if you do upper. Query doesn't deal with upper or lowercase.

So what I've done in the past is I just make sure all keywords are lowercase. This is how I get around it. There's some other ways to get around it, but this is, I'm just gonna tell you how I get around it, is I just make sure that all the keywords are lowercase. Then in the search function in here, let's delete this one and just go to this one. And what I will do is actually put lower around this. There you go. So now it's not case sensitive. So I can put anything in there and it will automatically lower lowercase every, that, that query string that you're looking for.

And because I know that every one of my keywords is lowercase, it works. That's how I get around it. You're more than welcome to find other. But so that's how you search for photos in a Google sheet. So we've in another pre previous video, we inserted these images in a cell, then we added an ID number , and in this video we added this keywords column. And instead of looking for searching for the id, we actually are searching for a keyword here. So let me color that in the garish blues, we know that's how we search. And this is how to search for photos with a keyword. Thanks.

Bye.</div>