We are making a public sheet and a private sheet with google sheets were going to use import range. Why we want to create a private sheet with some information? Maybe you have that already and youre like. How do i make part of this public without sharing the entire sheet, and one extra thing were gon na do today in this video is, were gon na, create an external system to get information into the sheet were gon na use? Google forms were gon na use. Two sheets uh one public one private and were gon na connect them with import range. So how are we gon na do this? We have a sheet already right now, the sheet youre. Looking at i just went to sheet dot new made a new sheet. We have one private sheet: okay, and then we have one google form and were going to get information with. This, then were going to create one public sheet and were going to give access to the entire internet for this sheet. What were going to do, then, is were going to use import range from the private sheet. We will select the data we want and put it into this. The public sheet using import range thats. What were going to do today, lets get started, lets say, were selling some items, maybe its like a virtual garage sale or something we have some chairs tables desks were selling them. Maybe we submit some ourselves, we have some items we want to sell, but others are going to submit other items they want to sell so lets do that lets get our external information in were going to go and create a google form now its up at tools, Tools create a new form were just going to create three things in our google and its gon na be name price and submitted by so right.
Here we have our form already uh form responses and its name form responses, but were gon na change. That name. We can just double click on the name of the tab and do that. Okay, we have our items that we know we want to show publicly. We have our submissions were going to get some submissions here were going to get name price and then the name of the submitter we can. We definitely have to make this form public itll, be in settings and in responses you can collect email addresses if its like all within one company. If youre all using the same google workspace admin, you can grab all of their email addresses and see who responds. But in this case we want public external people all right now what you were waiting for. So we have our sheet of items. We have a sheet of submissions, but now how do we make this public well were going to have to create a whole new sheet and well do that with sheet.new just go to sheet.new? I use uh slash2 because i have two uh google workspaces, its always gon na, be the second one that i want to make a new sheet on all right. We have a brand new sheet. I havent done anything to the sheet yet and all were going to have to do. Is we double click in a1 and were going to go, equals import range? And now, if you dont know what import range does we have a little helper here that allows us to see whats going on? We have a spreadsheet url and a range string.
What we need is we go back to our other. We can grab this entire url. We dont need to edit it or anything we can just take the entire url and back on our import range were gon na put it in quotes were going to do two quotes and then were going to paste this url, the entire url were not going to Do any edits to it ill show you another trick. Sort of later were going to add another string here, but what is that string? What is the range that we want? Well in this sheet? We have a tab and we have uh name price submitted by and sold. This could be like a drop down button or drop down here view more cell actions, data validation – and here we want a list of lets, just say items we want pending lets say we want these two and maybe everything is available. First, okay, we save that and now we can copy paste that drop down menu and we can quickly say if somethings sold or not or available, and we have power over this. So obviously the reason were not allowing people to have access to this is we dont? Want any necessarily anyone to see the submissions on this page, we also might have some notes here. Maybe we have commissions. Maybe we have like this. One is going to be 10 commission. This will be 50 commission and we dont want people to see that there might be extra information here that we only want to see a through d.
We want to see if something sold or not and who is it submitted and whos the name and price who actually also this submitted by, might not be good either so lets say we dont want to. You know where we are the marketplace. We dont want the buyers knowing who is selling it. We just know theyre buying it from us. We can move this sold over here and we want abc just to clearly say this were looking at items tab and we want the range a b and c. So we go back to our public garage sale page, the one that we know is public in our import range in this last quote. The second quote: we do items uh exclamation point because its the tab and we go a colon c. We could also do like a one if we want only one cell, but we want the entire column, and now we get a message we say you need to connect these sheets allow access. This is this is really really good and very important, because if somebody else ever gets the url of our private sheet, then they need to also allow access just having the url of the private sheet. Doesnt allow someone access clicking this, because im logged in – and i have access to both sheets – allows me to give access to this sheet. Now we have privately our information here on the sheet and publicly. We can share this url with anyone up here.
We can unlock this sheet, we can say, share with anyone with a link and we can click done, and now this url is available for everyone on the internet. What we needed to do in this video was: we wanted to make a private sheet with some information, and we wanted to share that with someone. They could externally give us more information using google form, and we want to display part of that information publicly, that people can see it anywhere and we can maybe embed it in another site or just share this a public sheet with someone. How did we do that? We made list, we created a google form called submissions. Then we also created a new sheet, a brand new other sheet, a second sheet, and we used import range grabbing, the url and the the range. So one thing about this import range. I want to add to anyone that wants a little bonus. You can actually do something else here. If you happen to know just the id you can usually use just the id of the sheet, so were going to take out the entire url and just put the id and see if that also works, and it does see, all we need is the id we Dont need the entire url, but sometimes if youre moving quickly using the entire url is totally okay. Google is like we get it. What happens if somebody sells a product and we say sold, because maybe we only want for sale items, and now you will see? Oh, this one is sold.
Maybe we dont want to see it heres. What i would on our private sheet. I would create a new tab and i would call it filter. I would take the header. I would maybe just write the header here and then in a2. I would go equals filter. The range is going to be items a to c. Actually, we dont even need abc, we need a to b and the condition is that items exclamation, point c to c equals available, and now we have two items here now this filter. All we have to do on our public sheet is change. The range in our import range from items to filter the load there we go and now weve filtered our items.