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
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!
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.
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
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, 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
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 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 !
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?
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?
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 ).
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.
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.
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!
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.
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.
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?
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
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.
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.
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
@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?
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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-BmxrsDntR44.html I haven't done a video for Google Sheets.
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
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.
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.
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!
"Exception: Service Spreadsheets failed while accessing document with id xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx. (line 25, file "Code")." :( Script is exact except the sheet address. Any ideas?
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. ?
@@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?
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.
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.
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?
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
@@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."
