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
Пікірлер: 176
@ailearning-k5nАй бұрын
You don't know how much time you save me. Thank you so much!
@cwlindWX4 жыл бұрын
I kinda knew how iterators worked intuitively, but this is the best explanation I've seen. Thanks!
@mask752604 жыл бұрын
Please don't stop web app series. Keep on adding!
@ryandibley6078 Жыл бұрын
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!
@rmtgbesds13 жыл бұрын
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.
@thinhse4 жыл бұрын
I recommend this channel to my students not only for learning scripts but also for listening your English.
@tanveerhussain38813 жыл бұрын
World's Best Teacher Best Quality Learn Easy and Understand I Love And Like All Video
@tomsawkins98284 жыл бұрын
the way script rolls of his tongue - like listening to Mozart !!
@kevpkelley4 жыл бұрын
Amazing! He anticipates functionality you are likely to need and adds it seamlessly.
@bdonato114 жыл бұрын
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.
@RiyazAhmed-dk4em4 жыл бұрын
Please upload the script in a text file with the videos. Thanks.
@Eteesh3 жыл бұрын
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
@bobykumar39603 жыл бұрын
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
@zzzzzz...12213 жыл бұрын
this video saved my life!
@Ladiesman77903 жыл бұрын
How to if your files is in folder>subfolders>subfolders>file excel(1), file excel (2), ... , file excel (10)?
4 жыл бұрын
Keep going the great work!
@wilbertbaldillo91724 жыл бұрын
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
@omerdahan9977 Жыл бұрын
Amazing video.
@ExcelGoogleSheets Жыл бұрын
Thanks!
@anjana19474 жыл бұрын
Thank you so so very much for sharing.. Really appreciate it bro.. God bless
@vukin3 жыл бұрын
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.
@TicoMansur4 жыл бұрын
this video helped me a lot! you're awsome!
@xitcode3 жыл бұрын
What to do if I have excel file on the folder and I want to merge them in google sheets?
@jhicinternational4 жыл бұрын
you are the best !!!!
@bjprado2 жыл бұрын
This helped me a lot. Thank you!
@ExcelGoogleSheets2 жыл бұрын
👍
@MattHarris34 жыл бұрын
Great work - nice explanation for a beginner. Thanks!
@HenrievanderMeer4 жыл бұрын
Awesome video, thanks!!!
@surajmaishery42453 жыл бұрын
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 🤗
@jovianchan40794 жыл бұрын
Please keep making videosss
@wagnerbarbosa18942 жыл бұрын
Fabulous! Thank you!
@ScottHebert-gr8vo Жыл бұрын
If I add a Google sheet in a separate file and insert that in this file, will still sum it in the combined data?
@batangrabbit4 жыл бұрын
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
@vodfactorymarketing12143 жыл бұрын
Same
@einarparker3 жыл бұрын
You got to look at the rest of your code and look for a surplus bracket “)” or missing one.
@rmu19854 жыл бұрын
I want to vlookup for a Value across spreadsheets in a google drive and return value. Is it possible??
@liamosullivan8312 Жыл бұрын
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.
@brighnquisitive62172 жыл бұрын
Bravo! Thank you much
@scottcardais3560 Жыл бұрын
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?
@BS-ey2tw Жыл бұрын
Very helpful video, however I cannot get around the getDataFromSpreadsheet error: Exception: Invalid argument: id
@bdonato114 жыл бұрын
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?
@ExcelGoogleSheets4 жыл бұрын
The easiest fix for this is to add another script in the end to apply those formulas in your combined spreadsheet.
@robertdonato54444 жыл бұрын
@@ExcelGoogleSheets Thanks! That was the conclusion I was coming to, but nice to get your thoughts.
@upxup4 жыл бұрын
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 !
@h.m.moniruzzaman93433 жыл бұрын
Thank you sir for your brilliant lecture. Would you please explain, how we can refer specific sheets of a workbook (Contain Multiple Sheets)?
@mbguantay Жыл бұрын
Excelent Video, thank you!
@ExcelGoogleSheets Жыл бұрын
👍
@agazaman4 жыл бұрын
what about folder and subfolder?
@50sidu4 жыл бұрын
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?
@techometer82083 жыл бұрын
Did you find the answer to this by any chance? I'm having the same issue, thanks
@djlslinet2 ай бұрын
may I ask which one is better? importrange or this?! tks!
@ExcelGoogleSheetsАй бұрын
IMPORTRANGE gives you constant updates, this is one time import.
@wilfredocontreras13102 жыл бұрын
Hi, thanks for the Code. Great work. How about if My spreadsheet is a live form which contains formulas.
@JorgeMoreno Жыл бұрын
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!
@yungkuan12604 жыл бұрын
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!
@yungkuan12603 жыл бұрын
Emma Karlsson I was thinking a ping pong effect. I can edit either one and it’ll keep the corresponding sheet up to date
@GouthamRam3 жыл бұрын
How to combine data with the colors filled in certain cells.? as the data is color coded sometimes.
@mohamedmontaser28824 жыл бұрын
what about if i want the script to check for new added sheets then run on those new sheets not from scratch again
@techometer82083 жыл бұрын
Did you find an answer to this? I'm curious cause I have the same issue here. Thankss
@panjimustaqiem90133 жыл бұрын
Hi, Pardon for noob questions. 1. What is the programming language use for google script? 2. Any tutorials to learn it for beginner? Thanks.
@ExcelGoogleSheets3 жыл бұрын
JavaScript. You could start here kzbin.info/aero/PLv9Pf9aNgemv62NNC5bXLR0CzeaIj5bcw
@mohamedmontaser28824 жыл бұрын
do we need function getDataFromSpreadsheet() after we finish all the script or we can delete it?
@ExcelGoogleSheets4 жыл бұрын
yes you do need it.
@kishlaysingh5581 Жыл бұрын
thanks
@pattilange61563 жыл бұрын
How do I add the "same cell" from multiple worksheet files into a new one...thus combining multiple employee hours for a specific function?
@icwcsltdjabalpur Жыл бұрын
sir while adding additional column it is not not syncing. kindly advice
@UntalentedThespian4 жыл бұрын
Lifesaver thank you so muchhhhhhhhhhhhhhhhhhhhhhhhhhhhh
@louiesumalinog75424 жыл бұрын
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.
@vikyadav63 Жыл бұрын
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?
@ExcelGoogleSheets Жыл бұрын
Just format that column as date?
@meghnaashish Жыл бұрын
Wow!
@neelgugale8754 жыл бұрын
what about combing folder sheets into seperate sheets on recurring basis?
@wojciechczuba93853 жыл бұрын
What should I do if in folder I have more folders?
@salvacionrotolo77649 ай бұрын
No script.editor in my google sheet
@agazaman4 жыл бұрын
i got this error Feb 17, 2020, 5:34:47 PM Error ReferenceError: file is not defined at myFunction(Code:9:12)
@ExcelGoogleSheets4 жыл бұрын
That means something failed in previous lines.
@ruadoncoi12 жыл бұрын
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
@iori32002 жыл бұрын
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 ).
@andrewatterbury82453 жыл бұрын
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
@tcrlrunner11283 жыл бұрын
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.
@markyoshizawa61413 жыл бұрын
Can you provide a copy of the script for our reference? Great video
@spongewirbb3 жыл бұрын
Whenever i import multiple files, it imports the data randomly. How can i import the data as sorted by filename? My filename are dates.
@ronelarinal18652 жыл бұрын
hi sir, can you also access if excel mimetype and google sheets mimetype combine together in a master combined sheet?
@kerrietownsend59334 жыл бұрын
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.
@ExcelGoogleSheets4 жыл бұрын
It's complicated, not something I can show within a minute or two.
@ExcelGoogleSheets4 жыл бұрын
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.
@kerrietownsend59334 жыл бұрын
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.
@ExcelGoogleSheets4 жыл бұрын
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.
@alanamorales77334 жыл бұрын
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.
@Danielle-rl8il2 жыл бұрын
When I do this it gives me an error that states, #VALUE! Filter range must be a single row or column.
@evilgenius747 Жыл бұрын
Thank you very much. Will the same script work for CSVs?
@evilgenius747 Жыл бұрын
"Exception: Service Spreadsheets failed while accessing document with id xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx. (line 25, file "Code")." :( Script is exact except the sheet address. Any ideas?
@ExcelGoogleSheets Жыл бұрын
No, you'll need to convert CSVs to Sheets first. stackoverflow.com/questions/38151430/bulk-convert-csv-files-in-google-drive-to-google-sheets
@evilgenius747 Жыл бұрын
@@ExcelGoogleSheets ❤ Thank you so so much. I been learning so much from you through the years. You're better than my college years😁.
@matt17104 жыл бұрын
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?
@einarparker3 жыл бұрын
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.
@mohamedmontaser28824 жыл бұрын
can you give us the script written?
@luiginica3 жыл бұрын
@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?
@luiginica3 жыл бұрын
Thank God I did this comment because I saw the typo error (length instead of length)
@FJDamitTV3 жыл бұрын
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!
@MrHanumathr3 жыл бұрын
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
@ExcelGoogleSheets3 жыл бұрын
Maybe timezone differences between spreadsheets?
@aathifts2804 ай бұрын
can i get the script?
@pipeliner89694 жыл бұрын
can I create a button in a cell?
@rogermartin87074 жыл бұрын
How can I copy the script you created in the video?
@xitcode3 жыл бұрын
Can you also please add the scripts along with the video?
@burinboonwatcharapai51064 жыл бұрын
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.
@ExcelGoogleSheets4 жыл бұрын
setValues
@Rinlyn4 жыл бұрын
Yeh!! It work thanks
@SRDFTM3 жыл бұрын
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
@ExcelGoogleSheets3 жыл бұрын
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.
@santiagofernandez7338 Жыл бұрын
Hi! Did you solve that problem? I'm having the same difficulty and I can't solve it
@klkfdt4 жыл бұрын
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
@ExcelGoogleSheets4 жыл бұрын
Yes, it's possible. I have a video covering how to do this in Excel. kzbin.info/www/bejne/eJ7bo6Z6o9mFapY I haven't done a video for Google Sheets.
@conteudoanimal4 жыл бұрын
why this code ... why not use import into spreadsheet ?
@faizaziz9283 жыл бұрын
Can you Suggest to combine data in One file by .getbySheetname with sheetname
@vodfactorymarketing12143 жыл бұрын
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
@lucgelinas94404 жыл бұрын
Excellent script - worked with 40 spreadsheets that I combined into 1 - any way to automate the merging of files?
@ExcelGoogleSheets4 жыл бұрын
I don't know what you're asking but there probably is a way.
@lucgelinas94404 жыл бұрын
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. ?
@ExcelGoogleSheets4 жыл бұрын
Yes, you can schedule the script. There is an example of scheduling in this video kzbin.info/www/bejne/nKmZoqiNh6hnopI
@lucgelinas94404 жыл бұрын
Hey thanks - Adding a trigger was the answer - Now the MERGE happens every hour as my people populate their spreadsheets! You are awesome!
@lenhu70893 жыл бұрын
8:01
@RafsanTanvir-jc5gc2 ай бұрын
google sheet 1200 tabs combine 1 tabe all data merge not formula
@cherd02sdfsd724 жыл бұрын
How to Combine Data from Multiple Google Sheets Documents with Contion?? (example. Select Col5 = 'E')
@admindrive29944 жыл бұрын
SIR I WANT TO TRANSPOSE BEFORE COMBINE PLEASE HELP
@RiyazAhmed-dk4em4 жыл бұрын
Can we combine multiple sheets in the spreadsheets with the sheet names ? Or does this combine only the first sheet ?
@ExcelGoogleSheets4 жыл бұрын
This script will just combine the first worksheet. You can of course modify it to do multiple sheets.
@RiyazAhmed-dk4em4 жыл бұрын
@@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.
@TheRangeControl4 жыл бұрын
@@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?
@edaretmotabaa88653 жыл бұрын
can you plz share the code as a txt ?
@Nagitiveplus3 жыл бұрын
i want it's opposite, how it can be?
@ExcelGoogleSheets3 жыл бұрын
kzbin.info/www/bejne/m4eqdoF-mNeCmZY
@diozhics264 жыл бұрын
omg! I can't keep up. Hahaha jwd!
@udhayasankar24594 жыл бұрын
Sir pls Tamil languages ley speech pannuinga sir please sir
@JB-bx5dp4 жыл бұрын
Does not work for me the "SpreadsheetApp.openByID". Error message: "You do not have permission to call SpreadsheetApp.openByID". Any solution please?
@ExcelGoogleSheets4 жыл бұрын
Do you have edit rights for those spreadsheets?
@JB-bx5dp4 жыл бұрын
@@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.
@ExcelGoogleSheets4 жыл бұрын
@@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.
@JB-bx5dp4 жыл бұрын
@@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."
@ExcelGoogleSheets4 жыл бұрын
@@JB-bx5dp Yea, I don't think so. I use it all the time. You did use openById right, not openByID?
@juliekirvin44212 жыл бұрын
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
@ExcelGoogleSheets2 жыл бұрын
kzbin.info/www/bejne/noGmeoKlnatjfMk
4 жыл бұрын
There is a blank screen between 15:58 and 16:25.
@ExcelGoogleSheets4 жыл бұрын
Damn it! I don't know what happened. Hopefully it still makes sense, I don't have the source files for this anymore.
@StevenSeiller4 жыл бұрын
Surely Google does... 🌐 😜
4 жыл бұрын
Learn Google Spreadsheets We appreciate every second of your videos!