Combine Data from Multiple Google Sheets Documents (Workbooks) in Google Drive into One Workbook

alright so in this video I'm gonna show you how we can take multiple spreadsheets that are all listed in a single folder in our Google Drive and just combine the data from those to one final file so in this case if you look here I have in my drive this folder called lesson and in that folder I have another folder called worksheets and if I open that I have three spreadsheets here if I open this file one you'll see it's basically some data if I open file - it's some data on this one and if I open another file it's basically another file with some data so what is this probably just gonna get rid of this but we could just keep it it doesn't matter it's not gonna make any difference so now we want to just make one more file that will be all of these combined together so I'll go here and I want to make sure that I don't do this in the same folder where my source files are so I'm gonna go above this in this lesson folder and create my new spreadsheet so right-click Google sheets blank spreadsheet and I'm gonna call this master combined from G Drive so let's get right to it so I'm gonna go to tools script editor to open the script editor and the first thing we need to do here we need to read the drive to get access to this worksheets folder and see what files we have here so to do that we'll go here I'm gonna zoom in a little bit more and rename this project so to access our drive we need the drive app and this needs to be in uppercase a so here we need to see how we're gonna get to our folders see if I scroll down there's get get files by name we're not gonna do that we can do get folder by ID or get folders or get folders by name so the best method would be to get the folder by its ID so if I just look here this is that worksheets folder and if I go and right-click on this or even better if I go to lessons this is that or cheats this is the folder I need to get to if I right-click on this I'm gonna go under share and I'm not really trying to share this I'm just trying to get the ID so if I go on their advanced here see here we have this long link whatever that is I'm just gonna copy that whole link right there I'm not changing any settings here I'm not sharing with anybody else I'm just getting out of here go back to my script let's just quickly just type it in here as a comment so we just need the ID so I don't need this beginning part I just need starting from the slash after folders and then going here until this question mark and I don't need that so that's the ID part for that folder so now I'm gonna go back here and do get folder by ID and the ID is gonna be whatever this was so I'm gonna copy this and paste it right here good so I'm gonna save this in a variable I'll call it var folder equals to this so that will give us the folder so now if we take that folder that folder has different methods one of which is get files and that will give us an iterator so I'll show you what that means in a second so I'm gonna run that and call this var files eater rater equals to that and what this files iterator is it's basically gonna have this method next and when you run this it's gonna give you a file so let me show you so if I just do var file equals that if I just take that file and see there are different things about the file let's just get the name of the file to make it easy for us to read so that should just get us the name of the file so I'll just say VAR FN equals to that and then let's just log that out so we can see what that FN holds after we do this so I'm gonna save this I'm gonna run this it's gonna ask for permission stuff which is normal we're gonna grant those permissions really quickly here so that's that advanced is this now let's go and look at our lock so we can find it under view logs or control enter or command enter and see it says another file so if you remember in that folder in worksheets that's one of the files another file but that's just one of them so what's gonna happen every time we do this next again so if I just do this for the second time here so I'm just copy pasting now we're doing that next twice and if I run this same code now you'll see that's one of the other files and then if I've run it again it's gonna go to the next one so basically if we keep repeating this code it's gonna keep going to the next one but at some point we need to know to stop because we're gonna run out of files so to do this what we're gonna do we're gonna go here after we this file iterator and create a while loop and we'll say while that iterator has actually an argument that can check if it still has more files and this is this has next method so this is gonna basically check if the file iterator still has more files in it and if it does we're gonna just run what's in this look and it's gonna keep doing this until we run other files so I'm gonna move all of this code now and put it over here format this a little better so that's our while loop so I'm gonna just get this declaration out of that look you know it cools here just file and get this out of here otherwise it's gonna keep annoying me it doesn't make much difference so we're gonna run this and if we look at this see we have first file second file third file they may or may not be in the same order as they are in here but that's the list of files so now that we have this going for us once we get the file this file other than the name has bunch of methods on it see we can do get ID which is something we're actually gonna need we can also get the mime type which might also be useful because if we decide to put some other files in that folder that are not spreadsheets we might want to just ignore those files so let's just try this get mime type let's just see what this gets us so I'm just gonna rename this to type and let's actually not use the keyword type let's the file type and that's gonna be here and here let's just run this and see what we get so see that's the type we got that's basically a spreadsheet from our Google sheets I'm gonna copy that type because I want to be able to use that so once I get the type I want to do an if statement and I'm gonna say if that file type equals to that particular type then we're gonna go ahead and do what's in this loop or in the safe statement more accurately spreadsheet okay so now let's just run this so that's nothing new it should run the same way and it should just add another line that says spreadsheet spreadsheet spreadsheet because we're checking if it's this and if it is we're doing that text as well but let's go back and add something that's not a spreadsheet to see what's gonna happen so I'm gonna go here new Google Docs blank document and do other stuff here doesn't matter it's just some document so now there is that new file if I go back and run the same code see the type for that document is document here and that's why for the first one we're not saying spreadsheet in a beginning only for these three we're saying spreadsheet because we're only getting inside of this if statement when it's matching the type of file we're looking for so this way we can avoid touching other file types okay so now when we got here we have the spreadsheet now instead of doing the spreadsheet let's actually do the ID of the spreadsheet so we'll do VAR SS ID declare it there and then let's just get it in here so we'll say SS ID equals 2 we'll take that file that we've already done above and get the ID so where is it get ID and let's just lock that out so if we do this that should just print out the ID for all those files and we need those IDs because we're gonna use them to actually open these other spreadsheets and get the data from them all right so now let's do a quick setup here first of all let's just put this column names copy them and put them in here so we have them so the column names are the same in all of these and that's gonna help us so we have to do less work so now let's try to read those files get the data and bring that over to do that first of all let me do that in a separate function first so I'm gonna leave this first function alone and go back and do another function and I'm gonna call it get data from spreadsheet so here I'm gonna start by getting my spread shaped app and we'll do get actually we need to do open not yet and we're gonna open it by ID because we're gonna have the ID so that's good so when we have the ID we should be able to open it now let's get one of the ideas that we had above mmm let's actually do it like this so it lets us read the log so I'm gonna copy one of these IDs for my spreadsheets go back here and paste it in here so that's gonna open that particular spreadsheet using this spreadsheet app then once we have that spreadsheet we need to open the first tab and if you look the tab names aren't really named this same in this different spreadsheets so if we take that spreadsheet we're gonna get well we could do sketch sheet by name if all of them had the same name so if the name was the same for all them and they were all called like partial data three we could do this and just do the name which was partial data three but in this case we can't do that because it's not gonna be the same for all of them so instead what we're gonna do we're just gonna get all of the worksheets which is this get sheets and that will give us an array of all tabs and to get the first one we'll just say that index 0 which is the first worksheet so that should give us the worksheet we'll save it in a variable like this now once we have the worksheet which is gonna be the first one let's see which file this is ending with QM think it's this one yeah there it is QM so that's this so now we need to grab the data so we're gonna grab the data starting from this a2 going all the way through G wherever the end is so a2 through G the end so I'm gonna go back to this here we go and I'll take that worksheet and say get range and the range that I'm gonna get is gonna be from a2 through G and I want to just find the last row and add it in here so I'm gonna concatenate that here and take that worksheet and say get last row so that should just figure out which is the last row and end up saying a2 through G in this case 14 so that's good and that should get us that range now we're gonna take that and get the values to an array let's save that to a variable so I'm gonna save our data equals this let's just log this to make sure this works so I'm gonna do log of log and data save that go back to this and run that function so I'm gonna switch to that and run it's gonna ask for more permissions here because now we're trying to get to a new file which can be annoying so we were able to grab the data now what I want to do I want to convert this to a function that's gonna just return that data and to do that I'll just say SS ID as a spreadsheet ID here and I'll pass that ID here instead of this hard-coded ID and then we'll just return the data which is that array so now we have a function that can just get the ID of the spreadsheet go get the data and return the data back so now we can use that function in our other function so I'm gonna switch to this so here instead of logging and logging this I'm gonna get the data from spreadsheet and that's the SSID that coincidentally is matching the variable name I use there but it's really like this going here so that's good so now we can say var data it's gonna be basically whatever data we get from that spreadsheet it's gonna return back to this so what we need to do we have to just combine all of these datasets from this different files so to do that let's go to the top and create an initial variable so we'll call it combined data and initially it will just be an empty array there will be nothing in it and then let's just define this variable data on top here too so we don't have to do this var every single time so we'll just do this so now we should be able to say our data this time is gonna be let's just get this function and run that ID and get the data from that ID and remember this ID every time was the ID of that current file from that folder so now once we get that data we basically just gonna put that inside of this combined data and we can do that by simply using can cat mat on this combined data we're gonna concatenate that data array to our array and what I'm gonna do because this returns a new array we'll just have to say this equals to this can cat data all right so now after this while loop is over which is ending here so let's just comment that and this one is if statement so after that while loop let's just try to log the data after we combine it so I'm gonna run now this function that's called my function which is right here I'm gonna run this it's probably gonna ask for more permissions well apparently did not let's go and check the log all right so we got an array and I think that's a combination of all of them but it's hard for me to tell there is a lot going on here so what I'm gonna do I'm just gonna do dot lengths to get how many rows we have in this to see if that's accurate so it says 41 let's go check that so it should be zoom out here so it's 13 here then we have 14 there 13 14 and 14 again not including the top right 14 14 and 13 so that makes it correct that makes it 41 good that's good news now let's take that new combined array and put it back in our main spreadsheet the master so all we have to do we have to again access our spreadsheet app we're gonna get our active spreadsheet because this script is tied to this because I started that from going tools script from this file and I'm gonna get this worksheet so I'm gonna just call this combined copy this go back here and get sheet by name the name of that worksheet will be combined so we'll just save our WS and we'll say WS dot get range and that's gonna be starting from the second row and the first column so row 2 column 1 the number of rows is gonna be however many rows we end up having in that combined data and remember I can get that by doing combined data dot length and then number of columns well in this case it's kind of gonna be fixed so we could just say 7 but we can also just calculate that by taking that array and getting the first row and then calculating the length of the first row which is gonna be number of columns so that should figure out how large that data is and then we're gonna do set values our values are gonna be coming from that combined data array so this should take care of it let's go ahead and run this to see what happens so I'm gonna go ahead and run this okay done let's go and take a look there's our combined data we probably need to just reformat this to date and if you want to format this to currency you can - and we have everything combined in one spreadsheet let's also test this by adding another file to that folder so I'm gonna go here and do well actually let's just open one of these and make a copy of it in a new spreadsheet open it I'm gonna make sure I move that to that same folder but let's first rename this yet another file something like that and that should go to that lessons worksheets folder there it is good let's change this up a little bit so we can tell that it's a different data set okay good so we have another file you don't have to have this files open like this so I'm just gonna close this let's just confirm that sitting in here yet another file so now I'm gonna just go and run this now it might be a good idea to clear this data from here before we put the new data in here just for good practice so I'm gonna add that to my code so once I got the worksheet before I do this and put the data in will do a worksheet and we'll do clear yeah before we clear we have to get the range we're clearing so we'll do get range and I'll do string notation here to make it easy so that's gonna be from a2 through G all the way down so I can do that by doing a to G and then I'll do clear content so that should just clean the worksheet before it puts the new data in there so let's run that my function thing right here which is this we should probably rename this to make it nicer but anyhow I'm gonna run this and now we have all of them combined including that new file that was a B and all this stuff so I want to add just one more thing to this before I finish this video so in addition to having all of this it would be probably nice to also have a column here that says file and gives us the file name where this data is coming from so let's do that as well I'm gonna go back to this and see here when we get the data instead of just taking that data and combining that let's add the name of the file as an extra column in that data so what I'm gonna do I'm gonna say data equals two and I'll take that same data and fit and that map will accept a callback function and we'll say our four row so that will be one of the rows in the data so we need to return that same row and to that bro we're gonna add that extra thing to the end so I'm gonna do can catch and that can cat is gonna be an array and that array should be just one value and that value is gonna be the name of the file and we can get that name of the file by taking this file variable and doing get name if you remember so that should create a new array from that data with the name of the file as the last column and then we'll just put it back into our spreadsheet let's run this and make sure it works let's go back and check it out nice so now we have the name of the file here as well in addition to just having the data and we can take this to a final step by just adding a button in our spreadsheet so we can just click on it and run it so just rename this function we'll call it combined data to master save that copy this this is all case sensitive by the way so be careful here I'm gonna go back here and add a button a drawing here we're gonna do this some background color of the font that's good save and close move this someplace helpful I guess click on this little side of this a sign script and paste the name of our function hit okay now we have this let's just clear everything from here and run it again good that works reasonably well I also want to just test this what if we don't have enough rows would it just automatically resize this and add it or are we gonna have a problem so let's say we have less rows nope it figures it out for us good this works and that should do it thanks for watching please subscribe and I'll see you in the next one

Channel: Learn Google Spreadsheets

Views: 43,578

Keywords: combine, data, multiple, workbooks, sheets, google sheets, Documents, Google Drive, one

Id: tmKqry4EKdQ

Length: 28min 32sec (1712 seconds)

Published: Mon Sep 23 2019