Make Unsplash in Google Sheets - Part 2

Most Popular Intermediate

About this Tutorial

Fix the case sensitive search with lower(). Add quick links to prefilled results. Create another sheet with ImportRange().Protect the sheet from unnecessary edits.

Video Transcript

<div> Two part two of Sunsplash making an Unsplash in a Google sheet. I am going to go through four different things with you today. One, we're gonna fix a case sensitive search issue and we're gonna use that lower formula . I'm gonna add some quick links to make it interesting to have some prefilled response results to lead the user somewhere. I'm gonna, Then the second half of this video, we're gonna create another sheet and we're gonna use import range . I'm gonna show you how to use that in order to pull the data .

So maybe we wanna have one spreadsheet , separate as a database and upload that and give access only to a public sheet or spreadsheet and. Then I'm also going to protect the the sheet with pro protect cells . You'll see that. So first, let's fix the lowercase or case sensitive issue. And let me remind you what that is. When we create this search if we did a capital J jungle, it didn't search. If you look at our database, we're looking for keywords. Our keywords are all in lowercase. We don't want to Have to make the user understand what our database is.

So we just all we have to do, actually, it's pretty simple, is put around this beef. This cell here, just put it lower, lower. And now we know, okay, every key word is going to be lowercase. And so now no matter what somebody puts in here it shows up cuz it lowers it, it turns all these letters into lowercase. And then we search fine. We have all the search results. The next thing we're gonna do is we're gonna add these links so that we can add pre-filled results.

So if we have, say we already know, let's look at our database. We already know there's like a pool here, a bicycle, motorcycle, rainbow. Maybe we have, 10 rainbow photos. We have 10 motorcycle photos. We wanna showcase those. Couple ways you can do that. You can absolutely. Here, let's delete this. You can absolutely put a little like list here and be like, these are great keywords to put to search for and find. But we'd rather give one click results. So what we do here, I'll show you how this works, is we put these links and we can actually, let's go to edit link.

What the link is actually to the sheet, so we say, Spreadsheets in the spreadsheet , and we can actually search tole and apply. And that's the link. The link is to another tab in this spreadsheet . How we do that. Let me just grab the style here. Let me do this. So let's go to search. I'm gonna add to rose. I'm going to paste just the format . We have a format here and I wanna say rainbow. Okay. That's it. And then we just do command K or just add a link. And then we pick, oh, what we need to do first is pick our our things. We'll do motorcycle or bike, and we'll do girl.

Now we need to get those, we need to create those sheets. But we already have this data here. So all we do is duplicate and then rename it. So we'll rename it Rainbow look, capital hard. Okay, nice looking. And now on this rainbow, we put link sheets , pick rainbow, apply. And on Rainbow we can do the same thing. Insert link sheets. We can do the same thing here. Insert link sheets in the sheet, in the spreadsheet . Pick Rainbow. It's the same thing. So now we can duplicate it again for each one. And that's going to give us the other ones.

So we just have a click here and it takes us directly to Rainbow. So we have these, can even put this in here. And so now we have pre-filled results and they just flip through here. They can even go through here and look through them and say, oh, here, I wanna look through this. You have a couple options to be able to share people with. Show them, different search results.

The next feature we're going to do is we are going to actually create a whole nother sheet, and we're gonna use import range . So again, sometimes you may want a separate spreadsheet to share publicly and keep this database, this db on one sheet. So here's how we do it. So we're gonna make a copy. We're gonna say public suns. It's a time to make a new sheet. So I paused the video now. Now we have this new sheet public sunsplash. We want to just delete, let's just delete a few things here.

We don't need this. We do want the database, but we don't want any of these this data , right? We want to be able to protect this data . And how we do this is now we want the data . We have a database sheet we are gonna do equals import range . And this is gonna take two arguments . One is the first thing is going to be the other spreadsheet , and you can use the entire url. So you can put the entire UL URL here. Or just the id. So ID is the. Part after the D here.

The second part is going to be the sheet name , exclamation point, and what we want, we probably want A to d I think. So this is the entire range that we're looking at in the sheet, but we now need to go grab the sheet here. So we'll go here. We can grab the entire U url. We can put that in there. And what's gonna happen the very first time we connect these sheets, we get this data , it's going to give us an error and it's gonna ask us to connect these sheets. So it'll load the data , reference error and says, you need to connect these sheets.

This happens only the first time you do it. So you just click that button and now we have this data and that should get everything from the other. So one issue you're gonna get is that this import range doesn't support the images. If you see these thumbnails are blank, and because it's an import range , it's importing the entire range . So if we want to replace these with this data , you're gonna see what happens. We want two, three. It gives you an error . It says it'll actually tell you what's going on.

It says the array result was not expanded because it would override date in c4. So C4 was where we wrote that formula . So we're not able to get this thumbnail, except we can, because we have this link. We have the backup way, we have image , and that won't work, but it'll be this one. So outside of the range , We can add these images here with this simple formula image equals this. Make sure we get the right one. So when adding these images over here on a separate sheet, the images that are inserted into the sheet do not transfer over with this import range .

And actually of the two that are an image , so the actual formula here on the sheet is just image for here that this one is actually transferring, but one is not. So this might be one of those. Sort of a buggy issue with import range . Import range in the past has been a rather buggy situation where sometimes it refreshes or doesn't refresh and you have to go here and do command art or refresh it.

What I would suggest is that if you do have these images, get the get a very static url like these Google Drive images are not gonna be a static url. They're gonna be served from whatever server is closest, I think. Cause you see this like LH five and LH three are changing. Host your own images somewhere not on Google Drive. And you can get them anytime you want to fidget with this, you don't have to mess with this. But then you can just use image on this column here Now. Step is we want to protect the sheet.

So we, even though we only have this formula here, the import range one, no one can actually delete any of this. If it's a public sheet and a public thing. But we still don't want someone to come in here and delete a one. So we go to this down arrow, we say protect sheet, and we have some options. We can set the entire sheet or arrange, we say set permissions sheet. And then o only I can edit it and you can save it. And now you can make this entire sheet public, but no one can edit this on the search. As well, we have an issue where we want someone to be able to edit B five.

This search terms, right? But we don't want them to be able to edit anything else. We don't want them to come down here and edit this so we can protect sheet and then we can pick a range and we say B nine to B set permissions . And we can say, only I can edit this. So now they cannot edit every anything here. Actually we to even do. Everything, b2, D right? Because we don't want anyone to edit any of this so it's protective and someone won't be able to edit this, but they can edit this search bar.

So that's a really cool way to be able to give public access to certain sheets that you don't want them to have access to the entire sheet. And now we have our own Unsplash all in Google Sheets. Thanks for watching. Bye.</div>