Stick around after the 1st portion of the video for bonus content.
Academy ↓
Hey there stranger!
Sign up to get access.
Create a Public Sheet and Private Sheet: Using ImportRange()
About this Tutorial
Sheet Resources
Featured Formulas
Video Transcript
00:00 Hello welcome. Uh, YouTube. We are making a public sheet and a private sheet, uh, with Google sheets, we're going to use import range of why.
00:09 Well, what we want, what we want to do is we want to create a private sheet with some information. Maybe you have that already.
00:16 And you're like, how do I make part of this public without sharing the entire sheet? And one extra thing we're going to do today in this video is we're going to create an external system to get information into the sheet.
00:30 So we're going to use a Google forms. We're going to use two, one in one plus one plus one equals two.
00:38 We're going to use two sheets, a one public, one private, and we're going to connect them with important rain. So how are we going to do this?
00:45 Let me back up a little bit and just discuss how we're going to do this one. We have a sheet already right now, the sheet you're looking at, I just went to sheet.new, made a new sheet.
00:57 So we have one private sheet. Okay. And then we have one Google form and we're going to get information with this.
01:08 Then we're going to create one public sheet and we're going to give access to the entire internet for this sheet.
01:15 And what we're going to do then is we're going to use import range from the private sheet. We will select the type of data we want and put it in into, into the, the public sheet, using import range.
01:33 That's what we're going to do today. So let's get started. We were just going to create that little bit of data and I'll be right back.
01:39 Okay. Let's say we're selling some items. Maybe it's like a virtual garage sale or something. We have some chairs, tables, desks.
01:45 We're selling them. Uh, 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.
01:52 So let's do that. Let's get our external information in. Um, we're going to go and create a Google form now.
02:00 So it's up at tools, uh, tools create a new form. One thing that is going to help us is that we want our Google form to have the same, uh, the same columns as our information.
02:18 So we can just copy paste it. So what we're going to do, and we're just going to create three things in our Google form.
02:25 And to me, name, price, and submitted by, but it allows actually be like your name. So right here, we have our form already a form responses and it's named form responses.
02:34 We're going to change that name. We can just double click on the name of the tab and do that. Okay.
02:39 So we have our items that we know we want to show publicly. We have our submissions and we're going to get some submissions here.
02:46 We're going to get name price, and then the name of the submitter. Um, we can, we definitely have to make this forum public.
02:54 So when using Google workspace, at least when I use it, I have this, uh, it's always restricted to users in my domain.
03:00 So I always UN toggle that. So it looks like this it's purple and I untangle it. Um, it'll be in settings and in responses, uh, you can collect email addresses if it's like all within one company, if you're all using the same Google workspace, um, admin, uh, you can grab all of their email addresses and see who responds.
03:22 But in this case we want public external people. All right. Now, when you were waiting for, so we have our sheet of items, we have a sheet of submissions and we control the submission.
03:34 So if anybody submits, we might be able to get, we do get a notification, we can turn that on. And then we can copy and paste all that information over to items.
03:43 But now how do we make this public? How do we make the items public, but all the submissions private, well, we're going to have to create a whole new sheet and we'll do that with <inaudible>.
03:54 So just go to sheet.new. I use a slash two because I have to Google workspaces. So it's always gonna be the second one that I want to make a new sheet on.
04:06 And then up here, we can always rename it. Toy one will say public garage sale. Alright, we have a brand new sheet.
04:14 I haven't done anything to the sheet yet. And all we're gonna have to do is we double click in 81 and we're going to go equals import range.
04:27 And now if you don't know what important range does, we have a little helper here that allows us to see what's going on.
04:34 So we have a spreadsheet, URL and arrange string. What we need is we go back to our other sheet. We can grab this entire URL.
04:44 We don't need to edit it or anything. We can just take the entire URL. And back on our import range, we're going to put it in quotes.
04:52 We're going to do two quotes, and then we're going to paste this URL, the entire URL. We're not going to do any edits to it.
04:59 Um, I'll show you another trip sort of later, we're going to add another string here. Uh, but what is that string?
05:06 What is the range that we want? Well, in this sheet, we have a tab items and we have a name price submitted by in soul.
05:16 So this could be like a drop down button or a drop down here. We can view more cell actions. Nope.
05:25 We don't want to do conditional formatting. We want to do data validate. Sorry. So right. Click view, more cell actions, data validation.
05:34 And here we want a list of, let's just say items we want, uh, sold, um, pending. Let's say we would want these two.
05:43 Um, and maybe everything is available course. Okay. So let's save that. And now we can copy paste that drop down menu, and we can quickly say if something sold or not or available, and we have power over this.
06:02 So obviously reason we're not allowing people to have access to this as we don't want any necessarily anyone to see the submissions on this page.
06:12 We also might have some notes here. Um, maybe we have commissioned, maybe we have, like, this one is going to be a 10% commission.
06:22 This will be 50% commission. And we don't 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's old or not.
06:32 And who is it submitted by? Um, and who's the name and price who, so actually also this submitted by might not be so good either.
06:40 So let's see. Um, we don't want to, you know, where, where we are the marketplace, we don't want the buyers knowing who is buying that, uh, who is selling it.
06:49 We just know that they're buying it from us so we can move this sold over here. And we want ABC.
06:56 So just to clearly say this, we're looking at items tab, and we want the range, a, B and C. So we go back to our public garage sale page.
07:08 The one that we know is public in our import range in this, uh, last quote, the second quote, we do items, uh, exclamation point because it's the tab and we go a colon C.
07:21 We could also do like a one if we want only one cell, but we want the entire everything that the entire column that, and now we get on message.
07:34 When we say you need to connect, these sheets allow access. So 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 to just having the URL of the private sheet.
07:53 Doesn't allow someone access, clicking this because I'm logged in and I have access to both sheets allows me to give access to this sheet.
08:01 Uh, and we can even test this, right? We can say, um, you can even put this in, use any URL that's public and see if it works.
08:10 All right. So now we have privately our information here on the sheet and publicly we can share this URL, uh, with anyone up here, we can unlock this sheet and we can say, share with everyone.
08:26 So whenever you make a sheet, it'll be restricted and we can say, share with anyone with the link and we can click done.
08:36 And now this URL is available for everyone on the internet. Uh, and also we can share our market, our, our garage sale.
08:46 All right. So what we needed to do in this video was we wanted to make you private sheet with some information, and we wanted to share that with someone so they could externally, uh, give us more information using a Google form, and then wanting to display part of that information publicly so that people can see it anywhere.
09:02 And we can maybe embed it in other site, or just share this, uh, public sheet with someone. So how did we do that?
09:08 We made, uh, items 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 use import range grabbing the URL and the, uh, the range.
09:25 So, one thing about this import range, I want to add to anyone's that one's a little bonus. Uh, if you just want to know in port range, we're done with it, but what's really cool is it did say in this important range sheet URL, but you can actually do something else here.
09:41 If you happen to know just the ID, you can usually use just the ID of the sheet. So we're going to take out the entire URL and just put the ID and see if that also works.
09:59 And it does see, so all we need is the ID. We don't need the entire URL, but sometimes if you're moving quickly and you want to go fast using the entire URL is totally okay.
10:10 Google is like, we get it. We know what we need out of this. But if you're like, Hey, I don't want to really give the entire URL.
10:17 I want to somehow make, for some reason, make this a little bit nicer or quicker, or you might only have the ID.
10:23 Maybe you're doing this with many sheets and you have a list of all the sheets and you have the IDs already.
10:29 And you're like, oh, I need to make this one part of each sheet public, or I need to gather all these sheets into one place.
10:35 Import range is really, really good for that. Um, it is not so good. There's one thing I've seen people use import range for.
10:42 And I don't understand why is they will use import range within the sheet. Now, if you are inside of a sheet and there is another range you want to move to another tab, you can use import range.
10:56 Uh, it is totally possible. It is completely, uh, can do, but what might be more interesting or what might be more easier on your sheet is array formula.
11:09 And you can use a rate formula, a one to C four, and there we go. We have the information. We can move this also to another tab.
11:24 Okay? So we have another tab. We can move it over here. And now we have from sheet one, we have a ready formula.
11:32 We have all the information. So a Ray formula for me has been very good. We can even just do the columns if you want.
11:38 It's going to be the same information. It's going to look the same, but it'll have all this. And here we can even take a look at, let's see, let's add some more information here.
11:50 Let's add a lamp for sale. And let's see if it shows up on our public garage sale. There it is already.
12:01 And on sheet two as well. Cool. And if we don't want the lamp, if we're like, Hey, we just want to see the top three things here.
12:10 So we weren't a one to see for area. We only see the top three. Maybe this is like three, one more thing that I just realized.
12:22 Probably you have a question about, and thank you for watching extra is what happens if somebody sells a product, when we say sold, how do we get that onto the other page?
12:35 Because maybe we only want for sale items, and now you will see, oh, this one is sold. How so? Why is that?
12:44 Maybe we don't want to see it. All right. Here's what I would do. I would create a whole on our private sheet.
12:50 I would create a new tab and I would call it filter. Uh, I would take the header. So I would, uh, maybe just write the header here.
13:15 And then in a two, I would go equals filter. The range is going to be items The, to see actually we don't even need ABC.
13:28 We need a to B. And the condition is that items, a exclamation point C to C equals available. And now we have two items here.
13:47 Now this filter, all we have to do on our public sheet is changed the, uh, range in our important range from items to filter the load.
14:07 There we go. And now we've filtered our items. So great. Thanks for watching. This is part of the video. Um, please feel free to write any questions you have in the comments.
14:19 Thanks for watching better sheets.
00:09 Well, what we want, what we want to do is we want to create a private sheet with some information. Maybe you have that already.
00:16 And you're like, how do I make part of this public without sharing the entire sheet? And one extra thing we're going to do today in this video is we're going to create an external system to get information into the sheet.
00:30 So we're going to use a Google forms. We're going to use two, one in one plus one plus one equals two.
00:38 We're going to use two sheets, a one public, one private, and we're going to connect them with important rain. So how are we going to do this?
00:45 Let me back up a little bit and just discuss how we're going to do this one. We have a sheet already right now, the sheet you're looking at, I just went to sheet.new, made a new sheet.
00:57 So we have one private sheet. Okay. And then we have one Google form and we're going to get information with this.
01:08 Then we're going to create one public sheet and we're going to give access to the entire internet for this sheet.
01:15 And what we're going to do then is we're going to use import range from the private sheet. We will select the type of data we want and put it in into, into the, the public sheet, using import range.
01:33 That's what we're going to do today. So let's get started. We were just going to create that little bit of data and I'll be right back.
01:39 Okay. Let's say we're selling some items. Maybe it's like a virtual garage sale or something. We have some chairs, tables, desks.
01:45 We're selling them. Uh, 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.
01:52 So let's do that. Let's get our external information in. Um, we're going to go and create a Google form now.
02:00 So it's up at tools, uh, tools create a new form. One thing that is going to help us is that we want our Google form to have the same, uh, the same columns as our information.
02:18 So we can just copy paste it. So what we're going to do, and we're just going to create three things in our Google form.
02:25 And to me, name, price, and submitted by, but it allows actually be like your name. So right here, we have our form already a form responses and it's named form responses.
02:34 We're going to change that name. We can just double click on the name of the tab and do that. Okay.
02:39 So we have our items that we know we want to show publicly. We have our submissions and we're going to get some submissions here.
02:46 We're going to get name price, and then the name of the submitter. Um, we can, we definitely have to make this forum public.
02:54 So when using Google workspace, at least when I use it, I have this, uh, it's always restricted to users in my domain.
03:00 So I always UN toggle that. So it looks like this it's purple and I untangle it. Um, it'll be in settings and in responses, uh, you can collect email addresses if it's like all within one company, if you're all using the same Google workspace, um, admin, uh, you can grab all of their email addresses and see who responds.
03:22 But in this case we want public external people. All right. Now, when you were waiting for, so we have our sheet of items, we have a sheet of submissions and we control the submission.
03:34 So if anybody submits, we might be able to get, we do get a notification, we can turn that on. And then we can copy and paste all that information over to items.
03:43 But now how do we make this public? How do we make the items public, but all the submissions private, well, we're going to have to create a whole new sheet and we'll do that with <inaudible>.
03:54 So just go to sheet.new. I use a slash two because I have to Google workspaces. So it's always gonna be the second one that I want to make a new sheet on.
04:06 And then up here, we can always rename it. Toy one will say public garage sale. Alright, we have a brand new sheet.
04:14 I haven't done anything to the sheet yet. And all we're gonna have to do is we double click in 81 and we're going to go equals import range.
04:27 And now if you don't know what important range does, we have a little helper here that allows us to see what's going on.
04:34 So we have a spreadsheet, URL and arrange string. What we need is we go back to our other sheet. We can grab this entire URL.
04:44 We don't need to edit it or anything. We can just take the entire URL. And back on our import range, we're going to put it in quotes.
04:52 We're going to do two quotes, and then we're going to paste this URL, the entire URL. We're not going to do any edits to it.
04:59 Um, I'll show you another trip sort of later, we're going to add another string here. Uh, but what is that string?
05:06 What is the range that we want? Well, in this sheet, we have a tab items and we have a name price submitted by in soul.
05:16 So this could be like a drop down button or a drop down here. We can view more cell actions. Nope.
05:25 We don't want to do conditional formatting. We want to do data validate. Sorry. So right. Click view, more cell actions, data validation.
05:34 And here we want a list of, let's just say items we want, uh, sold, um, pending. Let's say we would want these two.
05:43 Um, and maybe everything is available course. Okay. So let's save that. And now we can copy paste that drop down menu, and we can quickly say if something sold or not or available, and we have power over this.
06:02 So obviously reason we're not allowing people to have access to this as we don't want any necessarily anyone to see the submissions on this page.
06:12 We also might have some notes here. Um, maybe we have commissioned, maybe we have, like, this one is going to be a 10% commission.
06:22 This will be 50% commission. And we don't 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's old or not.
06:32 And who is it submitted by? Um, and who's the name and price who, so actually also this submitted by might not be so good either.
06:40 So let's see. Um, we don't want to, you know, where, where we are the marketplace, we don't want the buyers knowing who is buying that, uh, who is selling it.
06:49 We just know that they're buying it from us so we can move this sold over here. And we want ABC.
06:56 So just to clearly say this, we're looking at items tab, and we want the range, a, B and C. So we go back to our public garage sale page.
07:08 The one that we know is public in our import range in this, uh, last quote, the second quote, we do items, uh, exclamation point because it's the tab and we go a colon C.
07:21 We could also do like a one if we want only one cell, but we want the entire everything that the entire column that, and now we get on message.
07:34 When we say you need to connect, these sheets allow access. So 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 to just having the URL of the private sheet.
07:53 Doesn't allow someone access, clicking this because I'm logged in and I have access to both sheets allows me to give access to this sheet.
08:01 Uh, and we can even test this, right? We can say, um, you can even put this in, use any URL that's public and see if it works.
08:10 All right. So now we have privately our information here on the sheet and publicly we can share this URL, uh, with anyone up here, we can unlock this sheet and we can say, share with everyone.
08:26 So whenever you make a sheet, it'll be restricted and we can say, share with anyone with the link and we can click done.
08:36 And now this URL is available for everyone on the internet. Uh, and also we can share our market, our, our garage sale.
08:46 All right. So what we needed to do in this video was we wanted to make you private sheet with some information, and we wanted to share that with someone so they could externally, uh, give us more information using a Google form, and then wanting to display part of that information publicly so that people can see it anywhere.
09:02 And we can maybe embed it in other site, or just share this, uh, public sheet with someone. So how did we do that?
09:08 We made, uh, items 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 use import range grabbing the URL and the, uh, the range.
09:25 So, one thing about this import range, I want to add to anyone's that one's a little bonus. Uh, if you just want to know in port range, we're done with it, but what's really cool is it did say in this important range sheet URL, but you can actually do something else here.
09:41 If you happen to know just the ID, you can usually use just the ID of the sheet. So we're going to take out the entire URL and just put the ID and see if that also works.
09:59 And it does see, so all we need is the ID. We don't need the entire URL, but sometimes if you're moving quickly and you want to go fast using the entire URL is totally okay.
10:10 Google is like, we get it. We know what we need out of this. But if you're like, Hey, I don't want to really give the entire URL.
10:17 I want to somehow make, for some reason, make this a little bit nicer or quicker, or you might only have the ID.
10:23 Maybe you're doing this with many sheets and you have a list of all the sheets and you have the IDs already.
10:29 And you're like, oh, I need to make this one part of each sheet public, or I need to gather all these sheets into one place.
10:35 Import range is really, really good for that. Um, it is not so good. There's one thing I've seen people use import range for.
10:42 And I don't understand why is they will use import range within the sheet. Now, if you are inside of a sheet and there is another range you want to move to another tab, you can use import range.
10:56 Uh, it is totally possible. It is completely, uh, can do, but what might be more interesting or what might be more easier on your sheet is array formula.
11:09 And you can use a rate formula, a one to C four, and there we go. We have the information. We can move this also to another tab.
11:24 Okay? So we have another tab. We can move it over here. And now we have from sheet one, we have a ready formula.
11:32 We have all the information. So a Ray formula for me has been very good. We can even just do the columns if you want.
11:38 It's going to be the same information. It's going to look the same, but it'll have all this. And here we can even take a look at, let's see, let's add some more information here.
11:50 Let's add a lamp for sale. And let's see if it shows up on our public garage sale. There it is already.
12:01 And on sheet two as well. Cool. And if we don't want the lamp, if we're like, Hey, we just want to see the top three things here.
12:10 So we weren't a one to see for area. We only see the top three. Maybe this is like three, one more thing that I just realized.
12:22 Probably you have a question about, and thank you for watching extra is what happens if somebody sells a product, when we say sold, how do we get that onto the other page?
12:35 Because maybe we only want for sale items, and now you will see, oh, this one is sold. How so? Why is that?
12:44 Maybe we don't want to see it. All right. Here's what I would do. I would create a whole on our private sheet.
12:50 I would create a new tab and I would call it filter. Uh, I would take the header. So I would, uh, maybe just write the header here.
13:15 And then in a two, I would go equals filter. The range is going to be items The, to see actually we don't even need ABC.
13:28 We need a to B. And the condition is that items, a exclamation point C to C equals available. And now we have two items here.
13:47 Now this filter, all we have to do on our public sheet is changed the, uh, range in our important range from items to filter the load.
14:07 There we go. And now we've filtered our items. So great. Thanks for watching. This is part of the video. Um, please feel free to write any questions you have in the comments.
14:19 Thanks for watching better sheets.
Topics