Combine Multiple Spreadsheets Into One in Google Sheets

[@RadoHudran]

14:07-14:33 is my favorite part of this video That shocked realisation 🤣🤣

[@HashAliNZ]

😂😂😂

[@layymer]

@@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]

@@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

[@nickmikhuta7471]

An add-on Combine Sheets from Ablebits solves that and creates a formula as well. Just a few click and everything's done. BTW, their formula considers the headers and allows to select columns for the result; so the table structure is not supposed to be the same.

[@michaelsglas]

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]

Love it! Keep up the hard work, Michael!

[@Polaynka_Bolgarka]

Thank you for simple explanation. You make me happy!

[@ShawnEngland-u5i]

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?

[@mgrollins]

Thank you very much! Very easy to follow and I liked your screen casting as well🎉

[@Polaynka_Bolgarka]

Will be work to combine multiple workbooks if it have different columns?

[@vinarke]

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

[@cristelasoto-cdle84]

I followed your method but the main document is not updating. Why?

[@BobMiner-u4s]

Excellent example and explanation. Thanks.

[@HashAliNZ]

Did you spot the uncorrected error? It takes a good eye! Let everyone know what you saw!

[@staceyleslie661]

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

[@Rohit.Maurya]

Yes that is absolutely possible , in that case extend query formula at 16:24 like this query(......, "Where Col1 is not null order by Col5 desc") Here i assumed your dollar column is Col5 I hope it solves your problem

[@Rohit.Maurya]

You can also do one thing that is more easy, to give your working formula to chatgpt and ask it to update that formula so that it can sort dollar column in descending order, give your column reference to it and it will give you working formula World of ai 😅

[@uberalles6878]

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 😎

[@bermonalcantara3826]

Thank you very much! Very helpful!

[@ProjectsLeadIamadinkra]

Super helpful. but please do you have a video on an instance where the individual sheets are still being updated. But i still need the consolidated list to be updating without loosing data?

[@HashAliNZ]

This method with the array of importranges will work with data being continually added or updated.

[@Ditebogomojapelo]

Game changer!!! thank you!!

[@HashAliNZ]

Happy to help!

[@conuk35]

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]

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.

[@soulinenguyen]

Hi, I have a question. If I edit on one of the workbook, will the combine workbook also update that edit? Thankiu

[@ddomfire]

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?

[@khunkeng7541]

Thank you so much, this help my life a lot better 😄

[@mrmm1110]

Great !!!! this is cool as heck

[@ChrisCareMatch]

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]

Sorry, I'm not sure what you're asking. I think I'd need to see an example

[@AngelicaKymling]

Wow! Thank you for a really helpful video

[@HashAliNZ]

You're very welcome!

[@shixinn0911]

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]

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.

[@ritaoloughlin3943]

FABULOUS!!!! Thank you very much!

[@HashAliNZ]

Happy to help!

[@josephleslie007]

Broh, You are outstanding 🎉

[@justinco7741]

Dude! thank you for this!

[@HashAliNZ]

Happy to help, Justin

[@krishnatate4765]

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!

[@thrm1991]

Great video, really helpful

[@NirvanMundra]

Hi Hash, I am syncing 12 sheets but the last sheet data is not coming in the query. But when I used query for just that sheet its pulling data. Can you please help? or can someone else?

[@shedhead]

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]

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")

[@silviasoria2374]

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]

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]

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?

[@AshleyJenner-on1tq]

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!

[@kanewei3592]

Hi, thank you for your video, may I know how I can separate each IMPORTRANGE data by one extra row?

[@Зле_Коте]

Thank you - it was very helpful video

[@imaginhype]

Very helpful ❤

[@lemmynganga6857]

GREAT STUFF !

[@jinwong-l3z]

Hi, I'm having issue in syncing all sheets at 15:40, all the sheet doesn't appear even I have put the { }, it shows the error saying the sheets can't be found, is it due to all my sheet having inconsistence title? Or is it because the file it previously under csv and I open it using Google Sheets?

[@kristenroberts962]

Super helpful .. thanks!

[@jeromefurman2930]

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

[@rajmaskay6990]

Thank you, very useful.

[@OnceUponATechie]

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

[@joelutz1863]

thank you very helpful

[@shedhead]

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]

Never mind this comment I found my issue, I didn't understand that it had to be 'TAB' name and not any other reference

[@manishsinghla9348]

can you please help, at 16:24 you mentioned where col1 is not null. in my case every thing is working fine , but when i mention "where col1 is not null" it is giving me error as parameter2 NO column : col1

[@HashAliNZ]

Hi, since it says there's no column 1, that tells me you're using data straight from the spreadsheet without modifying it first. For example, if you're using columns A to F then try "Where A is not null"

[@amelialarasati6719]

Please help me, I tried it but still doesnt work :( ​@@HashAliNZ

[@ericaunzo717]

This video was amazing.

[@HashAliNZ]

Thanks so much, Eric!

[@ericaunzo717]

@@HashAliNZ I became an instant sub. I've watch countless KZbin videos but yours was the best. Thank you for sharing your knowledge. Looking forward to more amazing videos.

[@HashAliNZ]

Thanks for the support! I'll get them out when I can ❤️

[@RyieBerks]

Thank you so much.

[@GoingGreenMom]

🤯 I think you just fixed a lot of issues with a project I'm working on.

[@marcelabrito8248]

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

[@GRAMEENMILK]

sir how we take its total ?

[@yingcao1217]

how do I reference the sheet name on each line of the Master data?

[@HashAliNZ]

Best to reference the url and have the name beside it

[@michaelcooper8255]

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.

[@fahimhussain1495]

You are great😊

[@HashAliNZ]

Thank you so much 😀

[@OnceUponATechie]

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

[@woojinjoo241]

Any way you can make this even easier, especially if you have > 80 sheets?

[@HashAliNZ]

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.

[@OnceUponATechie]

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: kzbin.info/www/bejne/Y5TWh4NnhM-Fedk

[@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.

[@MichelleKathleenKhan]

awesome thanks!!!

[@ryuxereganharagato6752]

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?

[@AM-jw1lo]

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?

[@sonuasnani04]

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

[@nizamuddin8097]

My Favourite

[@Antôniocarlossilva-z1p]

top!!!

[@jktutorial4324]

Getting error Array_Literal

[@HashAliNZ]

The number of columns in the sources need to be the same.

[@jktutorial4324]

​@@HashAliNZ its the same no of column

[@sabirali1492]

👍💯

[@fooballers7883]

Nice one ....Thank you

[@RadoHudran]

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