Please don't stop web app series. Keep on adding!

I kinda knew how iterators worked intuitively, but this is the best explanation I've seen. Thanks!

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.

I recommend this channel to my students not only for learning scripts but also for listening your English.

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!

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

the way script rolls of his tongue - like listening to Mozart !!

Keep going the great work!

Great work - nice explanation for a beginner. Thanks!

this video helped me a lot! you're awsome!

Awesome video, thanks!!!

Thank you so so very much for sharing.. Really appreciate it bro.. God bless

Fabulous! Thank you!

this video saved my life!

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

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

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 !

you are the best !!!!

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 🤗

Bravo! Thank you much

This helped me a lot. Thank you!

@ExcelGoogleSheets

Жыл бұрын

👍

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

Thank you sir for your brilliant lecture. Would you please explain, how we can refer specific sheets of a workbook (Contain Multiple Sheets)?

Excelent Video, thank you!

@ExcelGoogleSheets

Жыл бұрын

👍

Please upload the script in a text file with the videos. Thanks.

I want to vlookup for a Value across spreadsheets in a google drive and return value. Is it possible??

How do I add the "same cell" from multiple worksheet files into a new one...thus combining multiple employee hours for a specific function?

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!

@yungkuan1260

3 жыл бұрын

Emma Karlsson I was thinking a ping pong effect. I can edit either one and it’ll keep the corresponding sheet up to date

Lifesaver thank you so muchhhhhhhhhhhhhhhhhhhhhhhhhhhhh

can I create a button in a cell?

hi sir, can you also access if excel mimetype and google sheets mimetype combine together in a master combined sheet?

Please keep making videosss

Hi, thanks for the Code. Great work. How about if My spreadsheet is a live form which contains formulas.

do we need function getDataFromSpreadsheet() after we finish all the script or we can delete it?

@ExcelGoogleSheets

4 жыл бұрын

yes you do need it.

what about combing folder sheets into seperate sheets on recurring basis?

what about folder and subfolder?

What to do if I have excel file on the folder and I want to merge them in google sheets?

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?

@einarparker

3 жыл бұрын

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.

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 you provide a copy of the script for our reference? Great video

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?

@ExcelGoogleSheets

4 жыл бұрын

The easiest fix for this is to add another script in the end to apply those formulas in your combined spreadsheet.

@robertdonato5444

4 жыл бұрын

@@ExcelGoogleSheets Thanks! That was the conclusion I was coming to, but nice to get your thoughts.

What should I do if in folder I have more folders?

Amazing video.

@ExcelGoogleSheets

Жыл бұрын

Thanks!

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.

How to combine data with the colors filled in certain cells.? as the data is color coded sometimes.

what about if i want the script to check for new added sheets then run on those new sheets not from scratch again

@techometer8208

2 жыл бұрын

Did you find an answer to this? I'm curious cause I have the same issue here. Thankss

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.

Wow!

If I add a Google sheet in a separate file and insert that in this file, will still sum it in the combined data?

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?

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?

@techometer8208

2 жыл бұрын

Did you find the answer to this by any chance? I'm having the same issue, thanks

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

@ExcelGoogleSheets

3 жыл бұрын

Maybe timezone differences between spreadsheets?

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.

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

How can I copy the script you created in the video?

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

@vodfactorymarketing1214

3 жыл бұрын

Same

@einarparker

3 жыл бұрын

You got to look at the rest of your code and look for a surplus bracket “)” or missing one.

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😁.

How to if your files is in folder>subfolders>subfolders>file excel(1), file excel (2), ... , file excel (10)?

thanks

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

@ExcelGoogleSheets

4 жыл бұрын

Yes, it's possible. I have a video covering how to do this in Excel. kzread.info/dash/bejne/dKGs1NV9nte4ZJc.html I haven't done a video for Google Sheets.

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

@luiginica

3 жыл бұрын

Thank God I did this comment because I saw the typo error (length instead of length)

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.

Can we combine multiple sheets in the spreadsheets with the sheet names ? Or does this combine only the first sheet ?

@ExcelGoogleSheets

4 жыл бұрын

This script will just combine the first worksheet. You can of course modify it to do multiple sheets.

@RiyazAhmed-dk4em

4 жыл бұрын

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

@TheRangeControl

4 жыл бұрын

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

may I ask which one is better? importrange or this?! tks!

@ExcelGoogleSheets

9 күн бұрын

IMPORTRANGE gives you constant updates, this is one time import.

Excellent script - worked with 40 spreadsheets that I combined into 1 - any way to automate the merging of files?

@ExcelGoogleSheets

4 жыл бұрын

I don't know what you're asking but there probably is a way.

@lucgelinas9440

4 жыл бұрын

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

4 жыл бұрын

Yes, you can schedule the script. There is an example of scheduling in this video kzread.info/dash/bejne/mKxq09eQgqaanJM.html

@lucgelinas9440

4 жыл бұрын

Hey thanks - Adding a trigger was the answer - Now the MERGE happens every hour as my people populate their spreadsheets! You are awesome!

Hi, Pardon for noob questions. 1. What is the programming language use for google script? 2. Any tutorials to learn it for beginner? Thanks.

@ExcelGoogleSheets

3 жыл бұрын

JavaScript. You could start here kzread.info/head/PLv9Pf9aNgemv62NNC5bXLR0CzeaIj5bcw

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

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

@ExcelGoogleSheets

3 жыл бұрын

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

i got this error Feb 17, 2020, 5:34:47 PM Error ReferenceError: file is not defined at myFunction(Code:9:12)

@ExcelGoogleSheets

4 жыл бұрын

That means something failed in previous lines.

can you give us the script written?

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.

@ExcelGoogleSheets

4 жыл бұрын

setValues

@Rinlyn

4 жыл бұрын

Yeh!! It work thanks

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?

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.

Can you also please add the scripts along with the video?

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.

@ExcelGoogleSheets

4 жыл бұрын

It's complicated, not something I can show within a minute or two.

@ExcelGoogleSheets

4 жыл бұрын

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.

@kerrietownsend5933

4 жыл бұрын

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.

@ExcelGoogleSheets

4 жыл бұрын

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.

Whenever i import multiple files, it imports the data randomly. How can i import the data as sorted by filename? My filename are dates.

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!

Very helpful video, however I cannot get around the getDataFromSpreadsheet error: Exception: Invalid argument: id

Can you Suggest to combine data in One file by .getbySheetname with sheetname

sir while adding additional column it is not not syncing. kindly advice

can i get the script?

When I do this it gives me an error that states, #VALUE! Filter range must be a single row or column.

SIR I WANT TO TRANSPOSE BEFORE COMBINE PLEASE HELP

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

No script.editor in my google sheet

can you plz share the code as a txt ?

hello! I need the same but with csv files :-)

@MattHarris3

4 жыл бұрын

Just tried this, it works with CSV files no problem.

@goukisan2869

3 жыл бұрын

@@MattHarris3 wait !! i tried it and it didn't work. what changes did you make ? i would greatly appreciate it if you can share how you did it

why this code ... why not use import into spreadsheet ?

i want it's opposite, how it can be?

@ExcelGoogleSheets

3 жыл бұрын

kzread.info/dash/bejne/l4p7p7CBk9W1k5c.html

How to Combine Data from Multiple Google Sheets Documents with Contion?? (example. Select Col5 = 'E')

8:01

Does not work for me the "SpreadsheetApp.openByID". Error message: "You do not have permission to call SpreadsheetApp.openByID". Any solution please?

@ExcelGoogleSheets

4 жыл бұрын

Do you have edit rights for those spreadsheets?

@JB-bx5dp

4 жыл бұрын

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

@ExcelGoogleSheets

4 жыл бұрын

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

4 жыл бұрын

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

@ExcelGoogleSheets

4 жыл бұрын

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

@ExcelGoogleSheets

2 жыл бұрын

kzread.info/dash/bejne/moR3q7GomKmWdso.html

Sir pls Tamil languages ley speech pannuinga sir please sir

omg! I can't keep up. Hahaha jwd!

There is a blank screen between 15:58 and 16:25.

@ExcelGoogleSheets

4 жыл бұрын

Damn it! I don't know what happened. Hopefully it still makes sense, I don't have the source files for this anymore.

@StevenSeiller

4 жыл бұрын

Surely Google does... 🌐 😜

4 жыл бұрын

Learn Google Spreadsheets We appreciate every second of your videos!

@ExcelGoogleSheets

4 жыл бұрын

lol

@ExcelGoogleSheets

4 жыл бұрын

@ Steven Seiller

google sheet 1200 tabs combine 1 tabe all data merge not formula