Combine Multiple Spreadsheets Into One in Google Sheets
Тәжірибелік нұсқаулар және стиль
Sure, you can use ChatGPT to write you a script to combine all these together, but where's the fun in that? Well, I guess prompt generation is fun. But still, this is where Google Sheets blows its competition out of the water - especially if you're still using Excel 2016 like I am!
----------------------------------------------------------------------------------------------------
⏳ TIMESTAMPS
----------------------------------------------------------------------------------------------------
0:00 Intro
0:50 Structures
4:00 Combining the data
8:30 Combine multiple workbooks
----------------------------------------------------------------------------------------------------
💬 GOT A QUESTION?
↓↓↓ Leave a comment down below ↓↓↓
Пікірлер: 85
Did you spot the uncorrected error? It takes a good eye! Let everyone know what you saw!
Thank you very much! Very easy to follow and I liked your screen casting as well🎉
Thank you for simple explanation. You make me happy!
I love you! Thank you for making my life easier lol I didn't want to be forced to learn how to use scripts to connect different spreadsheets together :))))) I just came here to say that.
@HashAliNZ
Жыл бұрын
Always happy to help!
Thank you very much! Very helpful!
Wow this was truly helpful as I’m building out a product that incorporates multiple members to keep me updated on the status of our business. I’ve incorporated this and I must say that it work flawlessly. Every day I learn something new and I am glad that I came across your channel. Thanks again for your tutorial.
@HashAliNZ
28 күн бұрын
Love it! Keep up the hard work, Michael!
Great !!!! this is cool as heck
Thank you so much, this help my life a lot better 😄
Thank you, very useful.
FABULOUS!!!! Thank you very much!
@HashAliNZ
Жыл бұрын
Happy to help!
14:07-14:33 is my favorite part of this video That shocked realisation 🤣🤣
@HashAliNZ
3 ай бұрын
😂😂😂
@layymer
3 ай бұрын
@@HashAliNZ totally awesome, but how to make it auto without handle Ctrl+C/V ? how to for combine sheets list(dynamic) of sheets just by formulas?
@HashAliNZ
3 ай бұрын
@@layymer once this is setup, it is fully automated and dynamic because of the importrange functions. To make it more dynamic, let's say by automatically adding extra tabs from files in the same folder, would require a script. A video for that process is currently in the works
thank you very helpful
Thank you so much.
GREAT STUFF !
Broh, You are outstanding 🎉
awesome thanks!!!
Wow! Great tricks to cut tedious work in the end 😍
@HashAliNZ
Жыл бұрын
Yep - I've got some tips coming up to make it even more automated.
@uberalles6878
Жыл бұрын
@@HashAliNZ ready to learn more so 😎
Sir ur really great... Amazing teaching and tricks... Thank you so much for very helpful video.. m big fan u sir..❤
@HashAliNZ
Жыл бұрын
It's my pleasure!
Game changer!!! thank you!!
@HashAliNZ
7 ай бұрын
Happy to help!
Hi, thank you for your video, may I know how I can separate each IMPORTRANGE data by one extra row?
Thank u very much! Because there are many people teaches how to combine 3 sheets , but no one tell them how to combine more sheets
@HashAliNZ
Жыл бұрын
Happy to help, Ashley!
My Favourite
Hi, I have a question. If I edit on one of the workbook, will the combine workbook also update that edit? Thankiu
Will be work to combine multiple workbooks if it have different columns?
You are great😊
@HashAliNZ
Жыл бұрын
Thank you so much 😀
Great video thanks - as this is dynamic ie the master changes then the copy updates - is it possible to rework this so it only imports once - maybe on a button press - so it does not slow down each time a colleague changes anything in the master - but can at least make 1 / 2 copies per day?
@HashAliNZ
2 ай бұрын
Yeah that's doable with a script. You could either set it up as a button or you could put a time trigger on it.
Brilliant, thank you very much. One question please. If I am bringing in 4 sheets and wish the sheet name (or associated text relating to that sheet) to be shown in new column after the imported data is there a way to do that? I hope that makes sense but if not: if each sheet relates to an area then I would like to import the standard columns in each of the sheets and then add the area that the sheet refers to?
@HashAliNZ
7 ай бұрын
Sorry, I'm not sure what you're asking. I think I'd need to see an example
This video was amazing.
@HashAliNZ
19 күн бұрын
Thanks so much, Eric!
@ericaunzo717
19 күн бұрын
@@HashAliNZ I became an instant sub. I've watch countless KZread videos but yours was the best. Thank you for sharing your knowledge. Looking forward to more amazing videos.
@HashAliNZ
19 күн бұрын
Thanks for the support! I'll get them out when I can ❤️
Really helpful video, Subscribed right away! Wanted to ask - my headings in the source spreadsheets are in rows, but I am looking to list them into columns in the master spreadsheet. Is there a way to do that? Sorry if I sound rude in any way, I have less than 12 hours and have more than 500 sheets of data, your prompt response/help would be a massive help. Thanks in advance!
@HashAliNZ
Жыл бұрын
Absolutely! We can use the =TRANSPOSE() function to turn the data from rows into columns. For the 500 sheets, it's best to do it with a script similar to the second half of this video, but you might need to make a few adjustments based on your specific case.
@OnceUponATechie
Жыл бұрын
@@HashAliNZ thank you! Really appreciate your response on this
At 7 minutes I followed how to combine sheets (which is awesome) into a master sheet & avoid the blanks....but then how can I sort the master without messing up the formulas? Exmple....then in the master I want the master sheet to always sort by dollars sold highest to lowest that it pulled from all the sheets....is that possible
hello! if for instance, I want to have an extra column to note what months these data were from, is there an easy way of doing it?
Question: Using your example at 16:02 in the video, lets say I have additional data to add to each row on this master sheet after you have combined the workbooks....each time a new sheet (month/year) is added the new data in the master sheet will not correspond to the correct row. How can this be fixed?
Is there a way to 'allow access' for all the sheets in one go? I had 579 sheets and it took a lot of time(an hour maybe) to allow access for every single one individually. It would be really helpful for those who work with a lot of sheets on a regular basis (if they want to combine them).
@HashAliNZ
Жыл бұрын
Yep! Pop all the files into one folder in your Drive, then set the permission of that file to allow access to whatever you want: view only or Edit access
This is great, What if we want to sort the master sheet by Date or Number ascending/descending in a specific column? can it be done?
@HashAliNZ
3 ай бұрын
Absolutely! At the end of the query, just add 'order by Col2 desc/asc'. For example: =QUERY({Datasets},"Where Col1 is not null order by Col2 desc") to sort by column 2 from biggest to smallest. If you want from smallest to biggest, use asc instead of desc. You can have multiple sorts in there too: =QUERY({Datasets},"Where Col1 is not null order by Col2 desc, Col1 asc")
Would it be possible to combine multiple sheets into one master sheet. And the master sheet auto update when those multiple sheet is updated?
@HashAliNZ
Жыл бұрын
Yep, this method does exactly that. Whenever something changes in the independent tabs or workbooks, the Master (which I labelled 'Raw Data') will automatically update
Hi! I have an app script that automatically creates new tabs based on data inputs and was wondering if there’s a more dynamic formula that is able to merge data from new tab into the master sheet as well. thanks!!
@HashAliNZ
7 ай бұрын
It wouldn't be fully dynamic because you'd need to reference the name of the new tab. I guess you could do it with an indirect function after listing out all the tab names.
👍💯
I'm trying to combine sheets where the new data is entered in columns rather than rows but when I'm following along using what youve shown abve its just putting the next data set into new rows so throwing everything out. hope that makes sense
@HashAliNZ
Жыл бұрын
Hi Michael, It's generally better to add new data in rows instead of columns, but if that's not possible for whatever reason here's a fix. At 3:19 I use A2:G. For columns, you would use A2: 7, or however many rows you have. The way when a new column is added, it'll copy of the every cell from A2 down to row 7.
Can I change the data in the combined sheet? For example, if I have some errors in one table and I cant go to that specific table, can I do it in the combined table so the changes reflec everywhere?
@HashAliNZ
Жыл бұрын
Nah, make the changes to your data in the original spreadsheet. Combining all your data should be kinda like a data dashboard for reviewing information
sir how we take its total ?
Hello, thnk you for this - the query function is working, but it is not pulling all of the data from the other sheets, only some of the data. I am not sure why?
@HashAliNZ
Жыл бұрын
Hey Jerome, It's difficult to tell without seeing the spreadsheet or the formula. Feel free to paste your formula here and I'll see if I can spot any problems
Hi, Thank you so much for your videos they are great! However, I have a question. I'm trying to combine different sheets into one master sheet, but this sheets have tabs how would and in the example that you give you don't mention that, how would that work?
@HashAliNZ
4 ай бұрын
Hi Silvia, Do you mean you have lots of tabs and one master sheet you want to collate everything into? As long as the structures on all the tabs are the same, you can do =query({tab1!A1:K;tab2!A1:K, tab3!A1:K},"Where Col1 is not null") Copy and paste that formula into your spreadsheet, then replace the tab names and the ranges with your needs
@silviasoria2374
4 ай бұрын
Hi @@HashAliNZ , thanks for responding, I have 10 sheets with different names (1,2, 3, etc..)each sheet have different tabs (a, b, c, ect..) about 6-10 each, some same name some different. I want to have a master sheet using only one of the tabs that are the same name from all the sheets. Let's say tab b is the same name in all sheets, that's the one I want to combine would it be possible with that query?
Thank You, So, when I do the second sicario and iportrange I get the #REF! and I get a ERROR "cannot find rang or sheet for imported range."
@shedhead
3 ай бұрын
Never mind this comment I found my issue, I didn't understand that it had to be 'TAB' name and not any other reference
Very Handy. For me i like the Sheet per month limiter, but you say google sheets is more effecient with pulling data from a single sheet. That seems to be a tip that underlies efficient spreadsheets. I guess the question is to broad, but how in the world is a medium user to know what is an effiecient way to set up a spreadsheet project?
can you combine different combine multiple sheet into one,but have different column?
@HashAliNZ
Жыл бұрын
Sure. Just tell the query which column you want. For example =query({Sheet1!A2:F;Sheet2!A2:F},"Select Col3 where Col3 is not null")
@ryuxereganharagato6752
Жыл бұрын
@@HashAliNZ like sheet 1!a2:f;sheet2!a2:g it is possible?
Is there an easier way to copy the links of the worksheets? I have more than 500 sheets and copying their links one by one will going to take me ages! Please help!
@HashAliNZ
Жыл бұрын
Absolutely! Open up a new spreadsheet within the folder that has all your Google Sheets. Click on Extensions > Apps Script. Delete the default function, then paste this in: function getSSIDs() { const folderID = '1CVEzhKbeupe_6zP4uoRnMhPNcoMFUFWD'; const folder = DriveApp.getFolderById(folderID); const files = folder.getFilesByType(MimeType.GOOGLE_SHEETS); let spreadsheetIds= []; while (files.hasNext()) { let file = files.next(); spreadsheetIds.push(file.getId()); } return spreadsheetIds; } function writeSSIDs() { let spreadsheetIds = getSSIDs(); let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.clear(); for (let i = 0; i
@HashAliNZ
Жыл бұрын
If you need to see exactly how to extract the IDs, I made this video just for you: kzread.info/dash/bejne/X5enuLJqf824c9o.html
@OnceUponATechie
Жыл бұрын
@@HashAliNZ can't thank you enough for this! I am glad that I stumbled upon your channel. If you ever need help related to smartphones, earphones or smartwatches just let me know, I'll help you out.
SIR HOW CAN WE ADD SAME ITEMS PURCHASE AND SALE FROM DIFFERENT SHEETS TO ONE MASTER SHEET TO GET THE ACTUAL STOCK OF ITEMS ?
@HashAliNZ
Жыл бұрын
Use a sumif function to add together products with the same SKU
Any way you can make this even easier, especially if you have > 80 sheets?
@HashAliNZ
3 ай бұрын
Well, I find this super easy and I can get this done within five minutes, but I've been working on client's spreadsheets like these for years so have the experience. There is a way this can be done with a script and it's very simple if you know how to write scripts, but it'll need to be customised for your setup.
13:30 Allowing access for each link individually takes tons of time. What I do instead is putting all the excel files into one folder, setting the folder's Sharing on "Everyone can Edit" Then the importrange() automatically has access to all of the spreadsheets Put your saved time/money here -> BE45000439748789 Communication: THX G