00:00 Hi, this video is one of the most exciting videos of the last few years I've ever made because I solve something that has been killing me, which has been just completely out of range and reach for me for years and has been a complete boondoggle and a complete, uh, frustration with Google sheets that I've had for years.
00:27 Um, let me explain a little bit more. Basically, there are two sheets here, and I've always wanted to be able to have two different views of the same data, but be able to edit that data in both views.
00:46 Now, if you're picking up what I'm dropping, you'll know that air table is capable of this, and is one of the reasons why I ended up, um, one year into using Google sheets.
01:02 Full-time in my company that I was working at and getting better and better all the time, a year into it.
01:08 I was, I was frustrated by this. This is about seven years ago. Um, I searched out and I found air table that could do different views.
01:20 There's two tabs here and it could edit. I could edit a record on each one. So essentially it was like keeping a database behind the database, right?
01:29 Spreadsheets are more like a database that we can keep data in here. And so when we create two different tabs, it's like two completely separate different entities and they don't touch each other, but we can use something like import range to pull data from one sheet to another.
01:45 But the issue with Google sheets is that that data goes basically down hit, like what, like a waterfall. You can only then take the data that you got and pull it to another, a third tab.
01:56 So, um, if I wanted to take like this data and she one I'm going to duplicate what it looks like, I'm going to delete the data and I can put in equals import range, import range, and I can do sheet one.
02:13 Uh, I can do this URL, right? And then do sheet one exclamation point a to D in quotes. And now it'll load and I'll allow access.
02:36 It's the same sheet, right? And it's the same data, right? And now I got this sheet one data, and anytime I edit it, I can go like, uh, air, table.com.
02:46 And now in this copy, there's an air table.com particular look of this without import range on sheet two is also updating.
02:55 And it's a different view. So of course I can absolutely within range. I can change the look of this, right?
03:04 And the data stays the same. Right? Um, I can add a name here and that name will show up on the import range one, but check out this other one.
03:14 It also shows up here, how does it do that? I'm not using an import range here. It hits a different view, but the same data.
03:22 What if I delete off sheet to Andrew right now? If I did that with the cheap one, the import range, nothing would happen.
03:31 But look, it worked. She wanted to, ARCA are connected. Look at this import range. If I delete Steve, literally I'm D I'm hitting the delete button.
03:40 I'm hitting the delete, but nothing that's happening right now. I can, in fact, I can make no edits here. I can't add Andrew.
03:47 I get a reference error because it can expand there's data here. Import range. Can't expand through data. So import range is a fairly limited capabilities.
03:59 And I use it all the time. Like I love import range. I think it's great between different sheets, different, um, worksheets, but between different tabs.
04:08 I think it's a very limited, uh, thing. So what happened? How did I do this? Right? I have now been able to sync two tabs and look, I can write Andrew here.
04:19 I can say, oh, I sent that. And in sheet two, it's updated. If I say, oh, I followed up and now we know who the website, right?
04:30 I know, oh, it's better sheets.com or go better sheets.com looking sheet one, it updated what is going on. And we get a little notice here.
04:40 All right. If you haven't noticed yet, I also have opened this tab, which is app script. It's been almost five minutes that you've been watching this video might as well share with you exactly what's going on.
04:52 So I wrote this script, this function on edit. I'm not going to walk through the entire thing, but I will share with you a few things.
04:58 One, you can edit it. One copy and paste, use this on your sheets. If you want to do something similar where you need.
05:06 And I'll talk about some use cases. After I walked through the script, this limit column means it's the first column that you don't want to be copied.
05:16 So every row will be copied, but I don't want like the column number five column like this. I want to keep my notes here.
05:24 Um, my notes are here safe. And if we look over here, they do not copy. So the fifth column does not copy.
05:33 So everything to left, a B, C, D 1, 2, 3, 4, all are copied. So if these notes, this is CNN. Dave's great.
05:43 If we go over to sheet two, it shows up and see, this is labeled Bob's notes. And I will discuss that later, why that is.
05:50 Um, but this, she called them. You can edit this to any number, uh, and it would be everything to the left of that number column will be edited both ways.
06:00 Um, and then online, 11 and 12, if she wants you to, you can rename these sheets, make sure to rename them here.
06:09 So if I change this to Bob, I need to come here, change it to Bob. And Kay, come in S and save that orange dot will go away.
06:21 And now it'll still work. So knowing air table, and that still works right, then I can edit Bob's notes too, just in case.
06:34 But maybe I have like, maybe it's a supervisor manager position, and I have my notes. They have their notes and their notes are shared with me.
06:41 I mean, it's all in the same spreadsheet worksheet. So nothing's really hidden. But if Bob writes a note, I see it here.
06:48 I don't have to check Bob's sheet, but I'm going to need, she renamed the sheet back to sheet one. And if I do that, I have to go here sheet one, hit command S to save, all right, that is for now everything that's edited bowl customizable in this function.
07:10 What this does is it looks at, if there's an edit in any edit, any singular edit to any particular cell, if it's a bulk edit or a group of edits, it doesn't work.
07:20 Only edits that are singular. So watch as if I try to delete everything here, it will only delete the top one.
07:31 But if I delete that, let me go and clean this up. If I delete all that, and then I come back here and I go, or table that come see that works.
07:46 One edit at a time. And this script pretty much says if we're on sheet one and the row is above one.
07:57 So the, oh, and also the hetero does not change, edit doesn't change. So, um, you can rename basically, you see this D column Bob's notes, my notes.
08:07 So this is a Bob's sheet and you can name it, my notes. And on here, I can name it Bob's notes so we can have different, uh, header names.
08:17 And those do not change. Uh, and the column is less than the limit columns. So that in our case, it's column five.
08:26 If all of those are true, then we're gonna take the value that you've said and edit it to its corresponding row and column.
08:38 So the same column, um, and the same cell. So this a two, if I co followed up on 82 here, it changes it to follow up.
08:47 So these two sheets are in sync and they're in sync perfectly with the right, the same cell, same rows and columns.
08:54 You set the limit column on the right. It's always less than the header column or add a row. And now you can sync two tabs.
09:04 This is incredible. Oh, and I also have this little toast. You don't have to continue if you don't want the toast, which is called a toast, this little message on the right bottom, right?
09:14 If you don't want it, just add two back slashes in front of it and hit command S and those toast messages will not show up.
09:25 Um, so what are some use cases for this, my God, nine minutes into this video? And you're wondering like, okay, great.
09:32 You can sink two tabs. You can sink two sheets without import range. So what you might also be thinking, oh my God, there's so many uses for this.
09:42 I'm going to use this right away. And you just you've already stopped the video. And you've already gone into the sheet and you've already gotten the script and you're not listening to me now.
09:49 And it's okay. But if you are asking, what is the use of this? Here's a couple uses one. If you have multiple people working on multiple accounts, like sales accounts, high touch, many touch points.
10:07 Uh, you have people that are emailing. You have people who are on LinkedIn, social media, DMS calls, um, snail mail.
10:18 Uh, if you have multiple touch points and you have multiple people working with multiple clients, right? Maybe you have, um, an account manager and you have a copywriter, um, keeping information together for all of those people and uniquely letting each person add what they need to the process is phenomenally difficult.
10:48 This could help, this could keep sort of some base information, uh, synchronous between many people and then allow individual people to make their own notes that are not easily seen by others, but also note to others, Hey, put flags up, right?
11:09 Raise a flag. Um, add some context, Hey, when I'm set and edit the date, the last date that somebody got contacted, or that they replied, or how, um, encouraging it is, or what percentage possible sale.
11:27 This is, right. Lots of cool little things. So that's for like, if you have outward facing clients, right? You could also use this for product or project management where you have a single entity, say someone in like a manager or a product manager or VP, and you have multiple people that need to work on this project.
11:51 And each of those individual people don't, they need to have some core piece of information about the product or project, but they need to share that information and send it up line.
12:03 Right. It's very difficult to know, like, who does, what, what, where is all this information and how do I edit that information?
12:10 Right? Everybody's going to have their own column. Everybody has their own space, or who edited this last? What, who did what very difficult this might help.
12:20 I'm pretty excited about it. It solves a problem that I had for years. I literally just made this last week.
12:27 I, uh, figured this out and then, um, have been playing around with it and really loving it. And it's working again, if you're only doing single edits, if you're adding information in this particular way with some headers and you have some kind of limit column where over to the right, nothing edits and everything to the left does edit, um, and go grab it.
12:52 Here's the code you can copy and paste this code into your sheet. You can copy the sheet into your Google drive, and I'm really excited to see other people's use cases for this as well.
13:06 Um, I'm really excited to hear back if you are using it, how you use it, and if you have any trouble, roadblocks challenges, I'm happy to help.
13:14 Thank you for watching sync two tabs without importing.