Combine Data from Multiple Google Sheets Documents (Workbooks) in Google Drive into One Workbook
ฝัง
- เผยแพร่เมื่อ 11 ก.ย. 2024
- Learn how to create a script to combine data from multiple google sheets workbooks located in a Google Drive folder into one mater workbook.
#googlesheets #googledrive #combine
I kinda knew how iterators worked intuitively, but this is the best explanation I've seen. Thanks!
Please don't stop web app series. Keep on adding!
Beautiful! You are my new hero. I'm a portuguese teacher and I work in a team that needs to combine multiple information from other teams of teachers and this is the second time that it looks like you can read my mind and grant us our wishes.Thank you for sharing.
Excellent video! Explained very well. Not too fast, not too slow. Everything was explained in a build-up manner with just the right level of detail. At no point in the video did I get lost or have to replay a section. It was just a steady stream of "I get it" from start to finish. Thanks!
I recommend this channel to my students not only for learning scripts but also for listening your English.
the way script rolls of his tongue - like listening to Mozart !!
You don't know how much time you save me. Thank you so much!
Amazing! He anticipates functionality you are likely to need and adds it seamlessly.
Great job!! Really appreciate this web app series. I am using google sheets mostly because it is easier to pull in stock market data. The google finance function only has a couple of historical stock stats available, so I was having to copy/paste values only to build up my historical record. Using these tools saves me a bunch of time and effort. This would likely be a hugely useful tool for many people. Happy to help if you are interested or you can just use the idea on your own.
World's Best Teacher
Best Quality
Learn Easy and Understand
I Love And Like All Video
Hi, awesome video, really help me a lot to understand. However I'm limited with this, I would need the new sheet to be updated with any new data found from any workbooks rather than creating a new one each time. I will be using it with trade statements, and when I generate a new statement(new workbook each time), I get new data and old data too. I need a master workbook that just takes new data from latest statement, and since I will be annotating my transactions in each row in the Mastersheet, I need it to be just updating with new row.
However, another issue is that, it is generated in Microsoft excel format, which I then Sync on my google drive folder.
I would really appreciate if you can help me in any way to setup this automation procedure.
Thanks
Great job, keep it up
Please upload the script in a text file with the videos. Thanks.
Keep going the great work!
Very Helpful and Informative..Quick Question, how to sort the data that the newly added file(workbook/sheet) will be inserted on the last row of the combined sheets
THANK YOU SO MUCH.. FIRST TIME I ABLE TO WRITE SCRIPT ... AND SUCCESS... FIRST TIME BECAUSE OF TELLING SO CLEARLY... BUT THERE ARE 3 (=) WHICH I DID NOT UNDERSTAND
How to if your files is in folder>subfolders>subfolders>file excel(1), file excel (2), ... , file excel (10)?
What to do if I have excel file on the folder and I want to merge them in google sheets?
you are the best !!!!
Thank you so so very much for sharing..
Really appreciate it bro..
God bless
this video saved my life!
this video helped me a lot! you're awsome!
Awesome video, thanks!!!
If I add a Google sheet in a separate file and insert that in this file, will still sum it in the combined data?
I want to vlookup for a Value across spreadsheets in a google drive and return value. Is it possible??
Your videos are awesome. I was wondering if you can upload a special video demonstrating the below problem I'm trying to solve. I'd be very grateful!
Need to create a script that copies some data set (not all columns) from a master data sheet to another sheet (separate workbooks) based on a trigger in master data sheet.
Thanks in advance 🤗
Hi! Good morning!
How can I make it a 2-way update?
Let's take the File 2 in the video as an example.
Cell G2 is $44.75 (COGS).
Let's change the value to $100.
If I click the "Combine All" button in the master file (combined worksheet),
the value of COGS in the master file will change to $100 too (which is great!)
What if I need to change the value in the master file instead?
I'll change the COGS to $120 in the master file.
Then if I click the Combine All button, I hope that File 2 COGS will change to $120 too.
How should I modify the app script code?
By the way, I watched this video several times
and I'm happy with the result when I run your script.
Thank you for sharing
Great work - nice explanation for a beginner. Thanks!
is it possible to get the new entries as soon as we update in multiple sheets automatically to master sheet without a need to use a button?
Did you find the answer to this by any chance? I'm having the same issue, thanks
Hi. First of all, I'd like to thank you for doing this tutorial. You're awesome!. But I'm encountering an error that says "Exception: Invalid argument: id (line 29, file "Code")".
This is the code of Line 29:
var ss = SpreadsheetApp.openById(ssID);
Can't pinpoint the issue. Thanks
Same
You got to look at the rest of your code and look for a surplus bracket “)” or missing one.
Fabulous! Thank you!
why this code ... why not use import into spreadsheet ?
Great video! Love to see if you can solve my problem. Is there a way to use onEdit to automatically read values from the cell/range of a spreadsheet that I'm active on and editing and write those edited values onto a different spreadsheet? Basically, if I edit one spreadsheet, it automatically edits another spreadsheet based on my coding. thanks again!
Emma Karlsson I was thinking a ping pong effect. I can edit either one and it’ll keep the corresponding sheet up to date
Thank you for your sharing!
It is very helpful!
I got a question :
I got sheets containing formulae, but the formulae will be invalid after combining.
So, howto populate data by value?
Thank you !
This helped me a lot. Thank you!
👍
Great Video! I used this script to consolidate task lists from multiple files and it works great!
A Question if I may, can we one up this script to itterate files in mutliple folders?
Sure. I have a video that shows how to iterate through folders and subfolders on my other channel. You should be able to find it in about section.
Bravo! Thank you much
I have a couple columns with formulas. When the formulas come over for the added rows, they still reference the rows above? in the reference sheet, the cells reference f3-g3, but since they are being added to the combined sheet, they should now reference f9-g9, but they still reference f3-g3? How do I get the formulas to reference the new row position?
The easiest fix for this is to add another script in the end to apply those formulas in your combined spreadsheet.
@@ExcelGoogleSheets Thanks! That was the conclusion I was coming to, but nice to get your thoughts.
I got DIZZY that I needed to stop at 12:30 because, it seems I could not handle it anymore. Is there a faster way to do it without having myself overwhelmed and frustrated by doing the script? I swear, I'm gonna die. I am not an IT student. Just a language student working on my thesis and I am now on data analysis that requires me to consolidate all the rating sheets of evaluators into one master sheet. Please help me before I die. I am desperately need your help. Aside from the channel owner, is there somebody else who can help me? THANK YOU!
do we need function getDataFromSpreadsheet() after we finish all the script or we can delete it?
yes you do need it.
Is there a tutorial that shows how to do the same thing but with multiple folders and specific data points within those folders? Very new to this, so bare with me.
Google sheets - prehistoric usability comparing to Power Query in Excel. In power Query merging files - 10 seconds in preset menu - just clicking mouse. Writing code and studuing formulas for such a simple task - mosokhistic nature.
Please keep making videosss
Thank you sir for your brilliant lecture.
Would you please explain, how we can refer specific sheets of a workbook (Contain Multiple Sheets)?
what about if i want the script to check for new added sheets then run on those new sheets not from scratch again
Did you find an answer to this? I'm curious cause I have the same issue here. Thankss
what about folder and subfolder?
Hello, I hope you are well, I have a shared folder of invoices and I've put in googlesheet and tracked them. My boss wants to me to put a link on googlesheet so she can go straight to the certain invoice. My problem is the link will route her to the group of invoices. Can you help me how to have a specific link to each invoice? If only she shared them individually that's not a problem ( but it would be a hassle for her ).
Hola, thank you for your video, it's a very useful video!
I just have one question, how can I do it with multiple files with multiple tabs each one. I will really appreciate your help.
thank you!
can I create a button in a cell?
Hi,
Pardon for noob questions.
1. What is the programming language use for google script?
2. Any tutorials to learn it for beginner?
Thanks.
JavaScript. You could start here th-cam.com/play/PLv9Pf9aNgemv62NNC5bXLR0CzeaIj5bcw.html
How to combine data with the colors filled in certain cells.? as the data is color coded sometimes.
Excelent Video, thank you!
👍
How do I add the "same cell" from multiple worksheet files into a new one...thus combining multiple employee hours for a specific function?
Whenever i import multiple files, it imports the data randomly. How can i import the data as sorted by filename? My filename are dates.
Lifesaver thank you so muchhhhhhhhhhhhhhhhhhhhhhhhhhhhh
What should I do if in folder I have more folders?
I entered everything correctly, but when I run it it only imports the first cell of data and duplicates it for the entire table. So I only get 1 cell and many duplicates
sir while adding additional column it is not not syncing. kindly advice
Can you provide a copy of the script for our reference? Great video
been searching for days and yours is the closest ive found to what i need, my problem is im tracking 1500 lines of data on 14 spreadsheets but on all 14 sheets they have different timers, now if i dont have sheets open they wont update in the cloud so need a script if possible that will run all 14 sheets on to 1 sheet as new to spreadsheets , can this be done , all the pull data is the same on all 14 sheets but its the timers that count as have 1 mins 5 mins 10 mins 15 mins 30 mins 1 hrs 2 hrs 4 hrs 6 hrs 8 hrs 12 hrs and 24 hrs data i would like to collect, how can this be done.
can I do this with csv files stored in a google drive?
hi sir, can you also access if excel mimetype and google sheets mimetype combine together in a master combined sheet?
what about combing folder sheets into seperate sheets on recurring basis?
Amazing video.
Thanks!
Hey. Great video. But I have a question: how can I do the exact same thing, but selecting the columns that I want to combine?.
Example: I have two sheets with different numbers of columns, but they have fields, with differents names, that must be combined. So, it was going to be something like that - WS1 - Column A must be combined with WS2 - Column B. There's someway I can do that?
Have you tried the UNIQUE function? Sometimes its more practical to use IMPORTRANGE, use arrays to get columns you want and use UNIQUE function to combine data.
i got this error Feb 17, 2020, 5:34:47 PM Error ReferenceError: file is not defined
at myFunction(Code:9:12)
That means something failed in previous lines.
QUESTION: How to automatically input the uploaded file link into spreadsheet data? Example: I uploaded .pdf file in google drive then my spreadsheet will automatically detect the link of that new uploaded file and input a new row.
may I ask which one is better? importrange or this?! tks!
IMPORTRANGE gives you constant updates, this is one time import.
Hi, thanks for the Code. Great work.
How about if My spreadsheet is a live form which contains formulas.
When I do this it gives me an error that states, #VALUE! Filter range must be a single row or column.
Can you show me how to get multiple sheets within several workbooks to copy over to one master workbook? I tried following your video but I know that it was only for 1 sheet in each workbook and I want to bring various sheets over into one workbook and keep the sheets properly dividied.
It's complicated, not something I can show within a minute or two.
For now, assuming worksheet names are the same in all of them, a workaround could be to just use this script 5 times for different sheets to combine them to 5 workbooks, and then run another script to combine those 5.
Learn Google Spreadsheets I could try that, however one question I have is does this script static or would it update as information is added to the spreadsheets or does it only update when the script is run? If it only updates when the script is run would it duplicate information that was already entered the last time the script was run?
I run a special olympics program in a school district and I have 150 schools that participate so I would like to give each school either own google sheets to add their athletes name(tab 1), basketball roster (tab2), soccer roster (tab3), we have 7 sports so we would need 8 tabs. All 150 schools would all have the same tabs in their workbooks. Would also need for the information to be updated in the master workbook as updates and changes are made to the individual 150 workbooks. Any help you can provide would be great as I am really struggling to find a solution.
It will not auto regenerate, you will have to manually run again. And it shouldn't make duplicated of old and new, it's just going to be a fresh combined set every time.
Wow!
Please help me, i have some gg sheet with different column, how can i combine it to one sheet order by a requested. Thank u so much
Hello! Can anyone give me a little guidance? I feel like this script will not work with Google's switch to using shortcuts instead of the ability to move shared documents - can anyone confirm if this is the case? I keep getting an error with the spreadsheets I am working with, but when I try it with test spreadsheets that I am the owner of, it works fine.
How can I copy the script you created in the video?
Very helpful video, however I cannot get around the getDataFromSpreadsheet error: Exception: Invalid argument: id
Is there a way to combine multiple tabs from each separate worksheet into one workbook and several sheets? So let's say each workbook has 5 tabs: Tab 1 is athletes, tab 2 is basketball roster, tab 3 is soccer roster and so on. I want to copy all of the tabs from 10 different workbooks into 1 workbook so all the athletes from each book are in tab 1 of the master, all of the basketball rosters from all 10 workbooks are in the 2nd tab of the master workbook and all 10 soccer rosters are in the 3rd tab of the master workbook. Is this possible. Everything I am reading is for copying 1 tab only
Yes, it's possible. I have a video covering how to do this in Excel. th-cam.com/video/BmxrsDntR44/w-d-xo.html I haven't done a video for Google Sheets.
can i get the script?
Hi thanks it worked but I have one column which has date time field and when I combining all the data, that date time column value are getting changed. how to retain the data?
Just format that column as date?
can you give us the script written?
No script.editor in my google sheet
thanks
Can we combine multiple sheets in the spreadsheets with the sheet names ? Or does this combine only the first sheet ?
This script will just combine the first worksheet. You can of course modify it to do multiple sheets.
@@ExcelGoogleSheets Thank you. Could you please show how to do it. Also I would like to know if any particular range of data from the sheets could be combined.
@@ExcelGoogleSheets oh no :-(
1.) How do we soak up the data in ALL of the workbook Worksheets?
2.) How do we add a "unique records only" function in the case of duplicates?
Thank you so much for this!! Saved a lot of time. But somehow wrong dates (in date columns) are getting imported. Any specific reason you know of? Please help
Maybe timezone differences between spreadsheets?
Thank you very much. Will the same script work for CSVs?
"Exception: Service Spreadsheets failed while accessing document with id xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx. (line 25, file "Code")."
:( Script is exact except the sheet address. Any ideas?
No, you'll need to convert CSVs to Sheets first.
stackoverflow.com/questions/38151430/bulk-convert-csv-files-in-google-drive-to-google-sheets
@@ExcelGoogleSheets ❤ Thank you so so much. I been learning so much from you through the years. You're better than my college years😁.
Can't make this work because I have .csv files and not spreadsheets in the google drive folder :(
So i am stuck there:
var ss = SpreadsheetApp.openById("ssID");
Error
Exception: Unexpected error while getting the method or property openById on object SpreadsheetApp.
getDataFromSpreadsheet @ Code.gs:24
Another excellent tutorial. It all worked perfectly for me except that the addition of the filename overwrites the data of last column starting in row 2 instead of adding the source filename as another column. It keeps the original column heading (row 1) but overwrites the data starting in row 2. Has anyone else had the same issue and discovered how to fix?
@Learn Google Spreadsheets, I got it all working with hardcoded values for the combinedData.length (rows and columns). Thank you!!
ws.getRange(2, 1, 7, 7).setValues(combinedData); --> this is working; I have 7 rows and 7 columns in the combined array
//ws.getRange(2, 1,combinedData.lenght, combinedData[0].lenght).setValues(combinedData); --> this is not working
If I Logger.log(combinedData.lenght); I get null
Any ideas why I'm getting null for this length?
Thank God I did this comment because I saw the typo error (length instead of length)
Can you Suggest to combine data in One file by .getbySheetname with sheetname
I have problem at the last step of .setvalue(combiendData). the result in spreadsheet after combined didn't showing all table but only first word (example : Log show [[d, 4.0],[e, 5.0],[f, 6.0]] but spreadsheet only show "d"). I already check source same as VDO.
setValues
Yeh!! It work thanks
Hello! First of all i want to thank you for your job, you helped me a lot in my learning. I cannot solve one problem, if one of the spreadsheets is empty, the script returns 2 rows, 1 row, where the names of columns are, and the next epmty row, can you please help me
Sorry, I don't remember what I did in this video, it was a long time ago. It looks like you need to add an IF statement to check for cases when the spreadsheet is empty.
Hi! Did you solve that problem? I'm having the same difficulty and I can't solve it
Excellent script - worked with 40 spreadsheets that I combined into 1 - any way to automate the merging of files?
I don't know what you're asking but there probably is a way.
To be clearer: This script requires me to press a button on the "combined" sheet for the files to merge. Is there a way to make the files merge automatically on a preset time frame: i.e. every hour. ?
Yes, you can schedule the script. There is an example of scheduling in this video th-cam.com/video/fx6quWRC4l0/w-d-xo.html
Hey thanks - Adding a trigger was the answer - Now the MERGE happens every hour as my people populate their spreadsheets! You are awesome!
Can you also please add the scripts along with the video?
SIR I WANT TO TRANSPOSE BEFORE COMBINE PLEASE HELP
i want it's opposite, how it can be?
th-cam.com/video/eVGENHcrOc4/w-d-xo.html
can you plz share the code as a txt ?
google sheet 1200 tabs combine 1 tabe all data merge not formula
Does not work for me the "SpreadsheetApp.openByID". Error message: "You do not have permission to call SpreadsheetApp.openByID".
Any solution please?
Do you have edit rights for those spreadsheets?
@@ExcelGoogleSheets
I do not change any rights for those files.
Should I do it?. In that case, I ask you kindly how can I do it correctly.
Thank you in advance.
@@JB-bx5dp Whoever runs the script should have permission to edit those files. Ususally the easiest way is to add edit permission for the whole folder where the files are located.
@@ExcelGoogleSheets
I read some foros and this is why do not work:
"This error is due to a change Google recently rolled out prohibiting the use of SpreadsheetApp.openByUrl() (or SpreadsheetApp.openById() in custom functions.
You can still use this method from other contexts like a menu item, trigger, etc. Google had to roll out this change for security reasons and they won't be able to revert back to the old behavior."
@@JB-bx5dp Yea, I don't think so. I use it all the time.
You did use openById right, not openByID?
once you have your master data from the query how do you sort by one column? I tried to sort but it gives me blanks at the top
th-cam.com/video/hPCIOohF0Fg/w-d-xo.html
How to Combine Data from Multiple Google Sheets Documents with Contion?? (example. Select Col5 = 'E')
8:01