Split One Google Sheet into Multiple Sheets based on Column Value

in this video let's take this master sheet we have with all of the sales and there's quite a few of them so if i scroll down here about 4 000 lines a little more let's try to separate this into separate sheets tabs whatever you want to call them for each salesperson so let's do an example of that so if i create a new tab here i'm going to give this tab a name matching the salesperson let's take the first one in the list perry nil that should be fine copy that i'm going to rename this and then over here i want to get just sales for perineal so to do that i'm going to use filter function so i'm going to skip the first line first row here because i'm going to put headers there so i'm going to go here to line 2 and do equals filter that's the function we're going to use here and in this function see the first parameter is the range i'm going to close this so it's smaller the range the range is basically the range of our data so we'll go to our sales people tab and select the actual data not including the headers so we need to select this all the way down now you could just select the data but what i'm going to do i'm just going to drop the row reference here in the end see this 13 and that will just pull it all the way down so that will select the entire range of data so i'm going to do comma and then the second parameter here is the condition and for that condition what i'm going to do i'm going to select this column of sales people and again we need to make sure we select from the same area so starting from line two see a2 here c2 here i'm gonna drop the end reference again to go all the way down similar to this left range and in this range i just want to select sales for that sales person so to do that i'm gonna do equals and then in quotations i'm gonna paste that person's name so if i just close this parenthesis right now and hit enter you'll see that we're gonna get just the list for perineal see 243 lines apparently now if we wanted this to be nice now we also probably want to add this column names on top right there so this is going to be the list for perry nil now if we wanted to do another person we basically have to repeat this so we would add another worksheet and go to sales people and grab the next person jerry jefferson and paste the name here and then we're going to do our filter function all over again here it's going to be the same function only instead of perineal it's going to refer to jerry so we'll do equals filter open parentheses we're going to go to sales data again select the data range not including headers drop the end reference comma and then salespeople after comma up to the same line two drop the end reference and i want it to be equal to in quotations i'm gonna do jerry jefferson close parentheses and hit enter and that should get me this and finally we want to do the column names and that should be everything for jerry jefferson now the good thing about this setup or maybe that's not a good thing depending on what you're trying to accomplish is that if i go back to sales data here and add more sales to this list so let's just do 200 more lines here and let's try to add something for sherry jefferson a new one to the bottom and i'm going to change this up a little bit so we can kind of see what's going on here and this should now automatically appear in jerry jefferson tab so if i go back here and take a look see right there it automatically populates because we're using a formula now if you don't want that to happen then at this point you could just take all of this data whatever's left over select it i'm gonna do that using command a really quickly and then just copy with command c and then you could just right click and paste special with values only and this way you'll basically just overwrite all the formulas and it will just be the data now it's not going to be linked to sales data anymore at this point so i do want it to be linked so i'm gonna do command z or control z undo to get back to the previous step and here we go now we have our function so this is great and it works as you can see now the issue is if we have a long list of options here creating all these tabs is gonna be not a lot of fun to do so for that reason why don't we just create a little script to automate that process to just create all these tabs so first of all let's try to think about the steps that we're doing here for each one so what we do we create a new tab we rename the tab then we put this formula in here and then we basically put this headers on top so let's try to do those four steps using a script so let me just pick one of these sales people the next one over and let's just try to create a script to just create this one and then we'll just expand on that and do it for everybody right so i'm gonna go under tools script editor dismiss this let's just give this a name so for now i'm just going to add that person's name as a comment here so let's start by getting that spreadsheet to a variable so i'm going to do constant ss for a variable so we'll get our active spreadsheet so once we have the spreadsheet now in the spreadsheet we want to basically create a new worksheet so i'm going to create ss dot let's see what it's called is it insert or create insert sheet so that should create a new worksheet for us so let's just give this a variable name maybe i'll do let for this one okay so now this is going to be the worksheet that's going to be created at this point so remember we want to create the worksheet then we need to rename the worksheet so let's do that renaming part so we're going to take that variable here that was created and here we're gonna try to set the name of the spreadsheet and we want it to match that person's name so that's gonna be this so once we create the new worksheet and we'll rename the worksheet the next thing we want to do is just put this formula in the cell a two so let me just copy that formula text here hit escape and in a2 cell we'd like to put that formula so we'll take that worksheet and in that worksheet we're going to get that cell and that was a2 cell and we're going to set the formula in that cell and we need to provide the formula as string now because formulas can have double quotes and single codes and all these characters to avoid any conflicts i'm going to use backticks as javascript string for here and that's the key next to one two three on the left so that's gonna be that so in this backticks i'm going to just paste that formula so basically i'm putting this entire formula inside of those quotations or whatever we're calling these things so that should put the formula in there now we want to make sure that we put that formula to use this person's name that and finally after we put the formula if you remember the last step was to make this headers on top here so we're going to get those headers from this sales data worksheet so we need to save that worksheet in a variable so i'm going to copy that worksheet name and let's just do it here on top i'm going to call it source ws for source worksheet and we'll get it from ss from our spreadsheet so we'll get it by the name of the worksheet and i'll just paste the name of the worksheet here see sales data that's the sales data over here and we're getting from this spreadsheet which is this ss variable we have on top here so now from that source worksheet we need to basically grab this headers and put them here in our target worksheet so to do that i'm going to take that source worksheet and here we need to get the range of headers so i'm going to do get range string notation is fine so let's just go check where it is so from a1 through f1 so we'll do that and in here i believe we should have copy 2 method see there it is and it's going to accept the destination where we're copying this to and we're copying it to this ws so i'm going to move this line below because we're going to do that over here after we did the formula and all of that stuff and then the destination is going to come from this worksheet so we'll take ws and it's going to be the same range as this one so i'll just repeat this so we're copying from a1 f1 in the source worksheet to a1f1 to our target worksheet so hopefully this should work now before i run this i want to change one thing since we're repeating this name a couple of times i want to move that to a variable here on top so let's just create a variable and i will move this below this main worksheets that we get here just like this and then i want to use this variable now in here so instead of doing this i'll just use the variable name and then in here i want to use the variable name but this is inside of the string and because we're in v8 engine now we should be able to just take this and use some special notation here so if i do dollar sign this curly brackets then i should be able to just put that variable in here and it would know that this means to grab this variable content and put that wherever this dollar sign and all the stuff goes so just like that we should have now this variable working now i'm gonna save this i'm gonna go ahead and run this now before i do that let's make sure this worksheet doesn't exist see it does not so let's go ahead and run this so some permissions so no errors that's a good sign let's go take a look that's there we have all of this we have the data we have the formula so that worked just fine as you can see so that was good but it just created one so now we need to take this basic concept of creating one and basically just do it for everybody in this list so what that means for us is that we need to first of all get a list of everybody a thing we have to consider is that the same person here repeats bunch of times so we want to make sure we get a nice unique list from here instead of just get this whole list by itself so let's do that so first of all i'm going to leave this function alone for a second let's create a new function and we'll come back to this in just a second we're going to need to get to the spreadsheet and our source worksheet so i'm going to just copy those two lines here paste them here so we'll get our spreadsheet in that spreadsheet we'll get our source worksheet so in the source worksheet let's just get data starting from basically here until the last wrap basically anything from c2 and down so to do that i'm going to take this source ws to get range and starting from row 2 we don't want the headers column abc the third column i'm not going to use string notations i'm just going to use this in this case the number of rows we're going to need is however many rows we have in the spreadsheet so we'll just get the last row minus one minus one because we have headers on top so we're basically offsetting one with this headers if you have more lines above your headers you need to do minus more whatever you have to do if you don't have any headers at all then there's no minus anything and it's just one column i just want to get the names so i'm just going to take this and do get values just like this and this should get us an array of arrays so for right now let me just create a variable here so let me just console log this so you can see what's going on so let's see what's in this variable i'm going to run this function now this function is called create sheets and if we just take a look at our log okay so we had to wait a little bit but here as you can see we basically got that column of values and this column of values comes in this format of array of arrays so this is an array and then we have this perineal in an array because it considers this one of the rows but since it's just one column it's just one thing if we had multiple columns it would be comma separated here and so it goes now i want to get rid of that array of array structures i just want the basic list so to do that i'm just going to take that sales people thing and i'm going to map it so let me just push this i'm going to do that here and in javascript we can do this type of things this is similar to just having one line i'm just doing this so i can kind of keep writing here so after this i'm gonna do dot that's gonna be that array and i'm gonna map that array to a new array and this one i'm going to push to the next line too like this so it looks prettier i guess and for each row or maybe sp for sales person we're going to return that sp in the first position in that array because it's an array of one sales person so i just want to return that sales person without the array so by doing this if i just rerun this so you can see what goes on under this sales people in console log see we have to wait for logs which is annoying sometimes but that's a part of working in a cloud i guess so there we go see we have now just sales people's list comma separated and we don't have those array brackets anymore so we just got a list the only problem with this list is that it has a lot of duplicates of the same person repeating here we just need a clean list of sales people so to do that we're going to use what's called a set in javascript so let's create a variable and here we're going to create a new set and set is sort of like an array type of structure but it can only contain unique values so what we're going to do inside of the set i'm just going to try to deconstruct the array we have on top here so i'm going to take that i'm not sure if we're calling this deconstruct or this structure anyways doesn't matter let's just try to log this set and see what's going on so i'm going to save this and run so that shows it's an object of some sort it seems to be empty in this log from what i can tell maybe i'm doing something wrong but maybe it's just not able to lock the set very well so let's just try to turn that set back into an array and we should be able to do that by doing something like this we'll put it in an array and we'll deconstruct that so we only got the first letters from perry nails so something is wrong from the way i did this so it appears to me we probably didn't need to destructure this thing over here we should be able to just pass the array just like this create a set out of it and then just deconstruct it to an array again let's try to do this and see what happens alright so that's much better let's take a look how long is this list see not too long so that gives us a nice clean unique list of people from that huge column of about 4 000 some rows so now that we have this list of people now we're going to use that list to create all the tabs but before we do that we need to do another thing we want to make sure that before we go ahead and create the tab we want to check if the tab already exists so if i already have perineal i don't want to create another tab that's called perineal as a matter of fact if i try to do that i'm going to get an error and it's going to fail so that means that before we get to creating all those tabs let's just get all the current tabs we have in a list so that we can check against that list so we got this variable that's our unique sales people we're going to need that so that's good but in addition to that let's also get current worksheets and to do that we'll do a variable and we'll take our spreadsheet and we'll use this get sheets that returns an array of sheet objects now we don't want sheet objects we just want the names so what i'm gonna do i'm gonna take that and i'm gonna map through that list again and from that list for each i'm gonna call it s for sheet we're going to return i think it's just get name it could be get sheet name but let's try this and see what happens so i'm going to get just the name of the worksheet let's try to console lock this and see what it looks like excellent that's actually the names from here so now that we have the names to check against and we have the list of names that we have to use to create those tabs for we're gonna go ahead and do the next step so what we'll do we'll take that list of unique sales people and we'll just loop through them with a for each loop and inside of that we're going to do a callback function so for each salesperson and i'm going to call it sales rep we're going to first check if that sales person is already in the list of our current sheet names if it is we don't want to create another sheet for that sales person so we'll do an if statement so we'll take our current sheet names and check if that includes includes method on an array in javascript we'll check if it contains that name pretty much and sales rep that so this is going to check if it includes that sales person's name and if it does it's going to give us true that means that it's going to try to do whatever we do inside of here inside of this if statement when this is true but that's the opposite of what i'm trying to do i want to create it when it's not already in the list so i'm gonna do an exclamation sign in front to do the opposite of this let me just do a couple of comments here all right so just some comments to make sure we know what's going on what this brackets are for and at this point now here we're gonna do the part where we actually create the worksheet and lucky for us we've already kind of done that so i'm gonna just scroll down now these two lines i don't really have to do because we already have them here on top we get the spreadsheet and the source worksheet so there's no reason for me to repeat those two sales rep this is where we define who the sales person is it's going to be defined by this loop in this array sales rep and i did name it the same already so i don't have to redo anything but there's no reason for us to do this line anymore because it's already going to be defined and it's going to be one of those names in the loop and this is pretty much the rest of the thing that's going to happen here so i'm going to copy that and paste it right inside of this if statement now since this is going to be in a loop over and over again let me actually just tap this one in one more level here so again since this is going to be inside of the loop i don't want to redeclare the variable multiple times so what i'm going to do instead i'm going to move this declaration of let outside of this for each loop just like this and then over here we're going to just remove the let so it doesn't complain about redeclaring the variable so the rest it should just create the worksheet rename the worksheet with that sales rep which is the current sales wrap then create the formula with that sales rep as the filter and do the copy as it did before so i don't think we need to change anything else in here and just for a good measure let's just set the ws to null every time in this loop before we actually do all of this so i'm going to save this and we're not going to be using this so i'm going to get rid of that one and this should be our function create sheets so let's go ahead and run this and see what happens so right now we have these three worksheets and let's run this it should not create those three because we already have those three but hopefully it's going to create all the new ones so that took a little bit of time let's take a look and see what this looks like so as you can see we got a bunch of tabs now let's go to this one that's the correct thing that's also correct this seems pretty good see we have all of these sales people and because of the way we did this if i go back and rerun this nothing should happen because we already have everybody see it went through really fast because there was no need to create any worksheets anymore and we should only just have every salesperson just once very nice and if we just go ahead and delete one of these so let me just go to this let's say perinil and let's go ahead and delete that one so now we don't have perineal in the list if i go back and re-run it it should recreate that tab there's perineal now and that should do it for this video thanks for watching please subscribe and i'll see in the next one

Channel: Learn Google Spreadsheets

Views: 39,602

Keywords: Google, Sheet, Split, Multiple, Sheets, separate, filter, spreadsheet

Id: QTySwuhpHG0

Length: 26min 9sec (1569 seconds)

Published: Mon Sep 21 2020