Utterly amazed. I usually have to search and piece together bits from different videos and then only sometimes can I get the code to work. You have made a video on the exact thing with everything I needed. Even using the time function. Made my life so much easier!
Thanks for your sharing, my case I have 40 different google sheet files with sheets named ABC I want to put together a file that joins these data into sheets called EFG and every day the data is automatically updated without me having to open the file. Thanks for your help
You’re good, thanks for the great tutorial! I’d like to suggest a subject for a future video. Since Google Sheets do not allow to use formulas that returns a list of items in data validation, I have a LOT of work on this specific matter. I think a script that gets a comma separated list in a cell (e.g. D3) and apply them to a range all the way down (e.g. D6:D) as data validation - and then repeat that on all columns to right - would be really great. Thanks!
Very nice. This is what I was looking for. But I have a question. You use a fixed ID for the source ID. Is it possible to place a changing ID here? My source file changes every week and so does the source ID. You choose the file and get the ID that can then be used as the source ID. Thanks
Thanks for the video. I am fairly new to this. Basically i am trying to find instructions on how to do a copy script for a specific set of cells (H7:J7) and then copy them to the next available row in columns C,D,E all on the same sheet. Do you have a video that covers that?
Hi Dean. I don't have anything that specific. You can use the getRange() and then getValues() methods like in the video. To paste them in the new location, you safest approach would be with this tutorial, yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
thats great video for learning and i have learned thourgh it i wants to ask like if we wants to copy the data many times and wants to put them one below other (append) so how it will be done by this script.
Hi DME YUFTA, thanks for the kind words. That is definitely the topic of another video tutorial. The short answer is that you can use something like the appendRow() method ( developers.google.com/apps-script/reference/spreadsheet/sheet#appendRow(Object) ). The long answer is that there are other things that may impact the way you append a row. If you might have multiple users who may be appending the row all at once, then you should probably implement something like LockSerivce ( developers.google.com/apps-script/reference/lock?hl=en ). Also append will fail if you have hidden formulas in your sheet that appear when there is data in a row. So you might need to get the first emply row in a column with ( yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/ ) Hope this helps. ~Yagi
thank you very much for this video. it helps me a lot. is it possible to paste the data to different sheets in one spreadsheet every time we run it and non syncing it from the data source? im trying to make a monthly database with clear spreadsheet source every month so the pasted data cannot be synced with the origin database. do you have any idea how to do that?
Hi rifdah, I don't think that I am 100% sure what you you mean in the second part of your first question, sorry, but I will try to answer. Yes you can copy and paste date to other sheet tabs in the same spreadsheet. You can set up different scenarios or conditions by which to update them and even use simple triggers like onEdit or onChange or time triggers to schedule events to up update your sheet tabs. I have some more tutorials in the links in the description on my website and also info on triggers. I hope this helps a little.
Hey Yagi, great tutorial - thank you so much, made my task much easier! :) Do you think you could prepare a tutorial for a case when you need to copy specific cells in a column (as in getRangeList or something along the lines) and copy them to a different Google Spreadsheet, also passing a list of cells to be pasted into? That would be wonderful as I cannot find any solution online that works for my case. Have a great day!
Hi Igor, I can add this to the list. It might take a little time to get to though. A couple of clarifying questions. So you are looking to copy values from selected cells in a range or the entire column? How do you want to determine which cells/columns that will needed to be selected?
@@yagisanatode Hi, thanks for replying - I want to copy a selected range of cells (eg. I25, I67, 68, etc.) from workbook A sheet 1 (by ID and sheet name) to a selected range of cells (eg. B12, B25, B40) to a different workbook and sheet (by ID and name). In my case I determine which cells I need to copy fromto empirically :) Copying an entire column, or a fixed range, is very straightforwadly explained in this tutorial, my case however, seems a bit complicated as I have searched numerous stack threads and was not able to come up with a solution that would fix my problem. Hope that clears it up a bit. Best to you!
This is so useful, my boss will like it! For this case: Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another, how can you select cells out of a column, full of hidden formulas, which are not hidden?
Hi Papi, You might find this tutorial helpful: yagisanatode.com/2018/11/17/google-apps-script-copy-selected-columns-in-one-sheet-and-add-them-to-the-bottom-of-different-selected-columns-in-another/
Thank you for your video. I watched till the end. And now I feel curious. Can the user copy paste data to other Google sheet (as destination) but only if tick box is check box. And data which pasted to the Destin is editable
Thank you very much for this tutorial! I was wondering, can this script also be applied to other sheets in the same google sheet file? and if yes, could it be manipulated also within each individual sheet while it updates the master sheet? or is it only possible to manipulate the master file and it updates in the destination/sheet?
Yes you can! You can use the getRange() method or the copyTo() methods locally to do this. Check our the other tutorial links in the description for some more guides on working with data in Google Sheets with Apps Script.
Hi! This tutorial is the best! I have encountered an issue with big data and the appscript is timing out... Can you give us a resolution for this, please? Thanks a lot!
Without seeing how you retrieve your data, there are a few things you can do. 1. You made find that the Google Drive Advanced is a little more performant by sending a batchupload request. 3. Grab the data by n length of rows at a time. Keep track of the time it takes to complete each process and keep track of the last row of each range that you collected using PropertiesService. After a few test runs you will figure out an optimal range to copy and paste at a time. As you approach your time-out period (6 minutes), create a time trigger to run ever minute and every minute repeat the process until the script is complete. Hope this helps.
Not directly but Csaba Csonka has a pretty innovative workaround for this: github.com/cscsonka/Parsing-MS-Excel-file-with-Google-Apps-Script Buy him a coffee while you are there! 😉
If I basically want to copy and based information inside selected cells from one sheet to another what would be the code .I need to be able to select what information I want to transfer to the other sheet and need to select certain columns where the information would go
Nice content thank you so much! I'm trying to append data from two different to single sheet and without blank rows. can you please provide solution here? Thanks in Advance!🤞
Hi Palyam, I haven't got a tutorial specifically to your needs just yet, but I can point you in the right direction. Your best bet here is to gather the data from both sheets with something like .getRange() or getRangeByName() methods then get the values with .getValues(), find thein common column, e.g. an id or a name and combine them together with a loop like for or forEach or use the map funcitonal method. Next, you can get the last row a number of ways. The easiest being getLastRow(). If you also have a bunch of hidden formulas in other columns you might be better off using the approaches in the link below. Then use getRange(your last row, your col start, yourData.length, yourData[0].length),setValues() to append your data. yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
Thanks, Scott; This program is very useful. Can you please suggest how to copy the selected columns or copy the entire sheet except for a few columns by modifying the given program? Can this program be modified to..1.Only changed row gets updated, Append /copied rows in destination based on some condition in the source file. ;
Hi, you might find this tutorial helpful: Google Apps Script - Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another [updated Feb 2022] yagisanatode.com/2018/11/17/google-apps-script-copy-selected-columns-in-one-sheet-and-add-them-to-the-bottom-of-different-selected-columns-in-another/
This video was amazing! At the end of the video you mention going over filtering empty spaces in future videos but I can't seem to find them. Have you made them yet or are they still in the works?
Hi Justin. Unfortunately I haven't yet. There are a few similar written tutorials on my site (Links in description), that might point you in the right direction for the time being. I've added this to my priority list, but projects have kept me pretty busy, hopefully get a video out in the next few months.
Not sure if you have a video like this but here goes..... We have in one sheet a row with a "work order number" . We want to copy this row to a master spreadsheet. However, I want to add a row if the number is not in column B. If the number exists then just update that row. Is this possible with script? I know your example is sheets within the same spreadsheet.... so mine is a little different.
Hi Michael. Yes is it possible, The closest I have tutorial-wise is this: yagisanatode.com/2019/02/13/google-apps-script-when-i-add-a-value-to-a-cell-in-a-selected-column-i-want-it-to-move-to-another-google-sheet/ You might be able to modify it to your own needs. It might be worth having a chat with a developer to see what they can do to solve your specific problem. I'll keep your request in mind though and added to my tutorial list.
Thanks for sharing such a wonderful script!! I have a query to ask in this, if we want to import same sheet but different columns how do we do that?? is there a way to add multiple columns from the same Sheet ( Source & destination Sheets are same)
Thanks Yagi. This is helpful. :) Now, I would like to set the destinationRangeStart as a variable based on a first empty row available in the destination sheet. This will let me save the historical data log (aka screenshots) of the input data rows which are changing daily. Could you please help with that? Thanks!
Hi Balbina, Yes in short take a look at either the .getLastRow() method or .getNextDataCell() methods. I have some tutorials in the description of the videos to help you go a little further, but this one will probably be the best for your needs, yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
Is there a way to import data into the destination and the destination to not change on the existing data ? So if I delete something from the Source to still have it on the Destination
You sure can. I have done something similar to this in this tutorial: yagisanatode.com/2019/02/13/google-apps-script-when-i-add-a-value-to-a-cell-in-a-selected-column-i-want-it-to-move-to-another-google-sheet/
Can this be extended/modified such that the source sheet's script creates the destination sheet, reads its I'd, and uses that id for the destination I'd?
Absolutely. You could generate a new sheet tab in the existing Google Sheet workbook or create a new Google Sheet. developers.google.com/apps-script/reference/drive/file#makeCopy(String,Folder) developers.google.com/apps-script/reference/spreadsheet/spreadsheet#copy(String)
Very nice video. but i have 1 question you used "Sheet1!B2". but how can i define that using a Cell. For example i have multiple sheets. (Sheet1, Sheet2......and so on) but by using a Cell. and in that cell i type Sheet2. and it goes to Sheet2 B2 etc?
You might find this series useful: ruclips.net/p/PLSDEbLgMgqvrwnVekQKlrx1Zyi1tUFsyY Particularly the third tutorial: ruclips.net/video/AgwnbVM8-kw/видео.html
Nice content really useful! Can we apply filters on the imported data? I have been searching on this, how to apply filters on the imported data, but we are not able to do so since the data imported is dynamic which means the data will not be filttered it will automatically convert back to its original imported form. Any ideas how to do that?
Hi Sanjay, yes we can use the filter class to apply new filters. developers.google.com/apps-script/reference/spreadsheet/filter Just keep in mind that this Apps Script approach is different to Google Sheets IMPORTRANGE in that it can be filtered directly.
Hello! I really liked your script and this is exactly what I needed, but I have only one problem. I lack script skills (I'm sorry) and I have searched already for a way to delete only rows A1 and B2 before pasting the data on destsheet but even trying a lot before without help I only got errors, can you please help me with this issue?
Hi Maze, once you getValues() you can use JavaScript slice or splice to remove those selected rows before using setValues() to paste it into your new sheet. developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/slice developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/splice Have a try using these methods and if you get stuck feel free to add a copy of your attempt and either I (time permitted) or a reader can guide you in the right direction.
Hi, I'm getting an error that's mentioned below. Exception: the parameters (number, number, number, null) don't match the method signature for SpreadsheetApp. Sheet. getRange. Please can someone help me out here.
Hi Inge. You sure can. You can use the getRowHeight() getColumnHeight() and setRowHeight() setColumnHeight() methods here. developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#setrowheightrowposition,-height You can find a similar example of this in action in this tutorial here: yagisanatode.com/2019/03/22/google-apps-script-maintain-row-heights-when-copying-and-pasting-data-in-google-sheets/
Excellent and wonderful explanation I have a question Excellent and wonderful explanation. I have a question. Can I add LAST ROW to the migration code without doing CLEAR?
@@As-rb5ij There are a number of additional methods that you can deploy to copy over formatting and formulas. These methods will start with `get`. Within the same Google Sheet you might want to look at the `copy` method. ruclips.net/video/34ozx-6sv34/видео.html Links to the docs: developers.google.com/apps-script/reference/spreadsheet/range
How can ee do this import in append way...means i don't want to overwrite the data...everytime when I will run the script then data needs to copy paste in append way means the last row of that destination sheet..and later i will romove duplicate data via using the duplicate script functions
Hi Kamalz, Apologies for the delay. I took a little time of a busy work schedule. Yes this tutorial is designed for those cases where you want to overwrite data in a range. You can use the SpreadsheetApp appendRow() method for adding individual rows of data to the bottom of the sheet. Likewise you can use the getLastRow() method to find the last row of the sheet and start your range from there. If you have a busy Google Sheet with lots of users you may also need to use LockService to prevent users from adding to cells simultaneously. I'll be covering these approaches in future videos. Here are some other tutorials is you have slightly different needs: - Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes [updated Dec 2021] - yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/ - Google Apps Script - When I add a value to a cell in a selected column, I want it to move to another Google Sheet [update Jan 2022] - yagisanatode.com/2019/02/13/google-apps-script-when-i-add-a-value-to-a-cell-in-a-selected-column-i-want-it-to-move-to-another-google-sheet/ - Google Apps Script - Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another - yagisanatode.com/2018/11/17/google-apps-script-copy-selected-columns-in-one-sheet-and-add-them-to-the-bottom-of-different-selected-columns-in-another/
Great video, but I can't seem to find the code for the video. It is exactly what I've been looking for. Can you point me to the download for the code. Thanks
@@everettpeschke286 The first link should be the starter sheet for you to follow along and write the code following the video. The next link is for the written tutorial. The full code is there if you get stuck along the way.
Thank you. But i have a question that how to put one defined ID in the position of DestinationID. I want use 1 DestinationID for multiple sourceID when i publish the function. Tks.
Great stuff! Thank you for this. Is there a way to rather set a specific destination sheet to create a new sheet named as date and paste the data there? I've adjusted the code so it pastes the data to next empty row but the new sheet thing is way too advanced for me :( thanks for any ideas!
Great work modifying the code, Michael. Yes you can check out SpreadsheetApp.create() developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#createname,-rows,-columns You can generate a date with something like const myDate = new Date() developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/Date You can even change the date to a specific format with .toLocalDateString() developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString Hope this helps!
You will need to get the range first using getRange("B2:G") on your `destSheet`. The auto suggestions will list a number of clear options like clear(), clearConditionalFormatRules(), clearContents(), clearFormats(), clearNotes()
Hi sir, i would like to thank you for this very wonderful guide. Its really amazing and i really helped me a lot. Now, my problem is we have added 2 different g sheet files. Will you be able to make a video like this, that shows us how to import data from 5 different g sheets file and consolidate it to 1 file. For example: There are 5 different places/site that is doing an attendance for employees, so i need to consolidate them in my file by using appscript.
And also, if possible please make it user friendly as i have my team mates also using the masterfile, so it will be very nice if all users in the masterfile have the ability to run the code by just clicking the button.
In this specific case of copying between different sheet you will also need to use formatting collectors like .getBackgroundColor() => setBackgroundColor(), and .getRichTextValues() => .setRichTextValues() is a pretty good catchall. developers.google.com/apps-script/reference/spreadsheet/range
I have a Question: Great Video. My sourceRange"XYZ! A2: G" is only generating 1 value out of the multiple data in the source sheet what am I doing wrong?
@@yagisanatode, I feel like Giving you are Great Hug Right Now....That was exactly the Problem. it's working now with getValues(). 100% Appreciation to you Bro!! 🤪
Hi Scot and thanks for this great script. I've been searching the internet for something like this for days. I've try to modify your script for my real working scenario and i got stuck. My question is... can this script to copy ONLY the range values from today's date and later?. If is helpful i can add formatted dates in row 1 in all columns in all the sleets in both spreadsheets.
After collecting your data try using a loop or the JS .filter() method to iterate through your values in your range to find dates greater than or equal to what you need.
I am having an issue where it's copying the date and is minus one day. I've tried changing cell format and validating cells to make it quit. Been on it about an hour and I can find no earthly reason why it is subtracting one day from any date that is copied. Anyone run into this or have an idea? ***EDIT** Solved. The sheet itself, under settings, was set to a different time zone. Not sure how that happened, but once I made them match there was no more issue.
Hmm... is it precisely one day, do you know? If so this might suggest a calculation error. Alternatively sometimes timezones of your Spreadsheets can be different and even your Apps Script project. You can check by going to File > Settings in your source and destination sheets. In your apps script project go to Project Settings > General Settings. Hopefully this helps troubleshoot your issue. yagisanatode.com/2020/11/17/google-sheets-beginners-change-your-current-workbooks-timezone-date-currency-16/ yagisanatode.com/2021/03/10/help-my-times-triggers-are-not-in-sync-how-to-update-your-google-apps-script-project-time-zone/
Hey ! That was deeply helpfull thaks lot ! 😊 Atm im stuck with and additonal step i would love to implement (since few hours, editing your script). I try to remplace the "destinationRangeStart" with some kind of "MySheet.getLastRow()+1" but i really don't know where i can implement it. Could you help ? ❤
edit: nope, does not work, resp. looks like I have to run the original code, then change it to the below and it works. If I run it in an empty file, it does not work. I AM CONFUSED! sorted it this way and seems to work // Get the full data range to paste into next empty row after start range. const destRange = destSheet.getRange( destStartRange.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()+1, destStartRange.getColumn(), sourceVals.length, sourceVals[0].length
@@michaelhaj1 seems my first comment was deleted due to the link of the pastebin ^^ share me a trash mail i send you what i have done. I have commented it a bit with what i remembered. 😊I think if you have followed this video you could easily understand what i have added and edit it for yours ! 😉
This is quite helpful. How can i make the destination range starting point (in the code it's "Input Data!A1") to be a variable and to always be last row in the sheet name Input Data? function runsies() { importRange1( "4uyrjP5dfsdfaWm_LG09nRwkVFTMuRPK", //not real Id "Dataset1", "87mkpYvpR02oyOYvhhgJOptNFZjtESGEje25A", //not real Id "Input Data!A1" ); }
Hi Timothy, your best bet would be to do this inside the getRange() function once you get the spreadhseet. SpreadsheetApp.openById(--THE SPREADSHEETSHEET ID--).getSheetByName(--SHEET TAB NAME--).getLastRow(). There are some situations where this will not be 100% successful however, you can check out this tutorial for more information: yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
@Yagisanatode -Scott- Thanks so much Scott. I have a question though, I tried modifying the script to paste the data in the last row of my destination sheet using the getLastRow function but it returns an error. "Final_collated_analysis!A2" ---- the destination range const destStartRange = destinationSS.getRange(destinationrangeStart.getLastRow) destStartRange.getLastRow() Please help and let me know if you have a better idea Thanks in advance : )
Hi Bariyal, have a look at the written tutorial in the link in the description, it might give you a better understanding on what is going on. In the example, the getRange() method takes 4 arguments, .getRange(start Row, start Col, row depth, col width) Notice that all these arguments are contained in braces "()" To apply a value to the last row try something like this: // Get the full data range to paste from start range. const destRange = destSheet.getRange( destStartRange.getLastRow(), destStartRange.getColumn(), // OR col 1 or for col A sourceVals.length, sourceVals[0].length ); Hope this helps.
Hi there, First of all: thank you so much for your videos! They are really really helpful! I just subscribed to your channel :) It has been days (and nights) and weeks since I have been trying to find a solution to my issue, but unfortunately I did not find anything around :( This is my situation: I have several sheets with a list of the events (date, time, event name, description...). I would like to create a script that imports the data of each sheet in one sheet (called Master Calendar). I need this one Master sheet to have the data imported from the three sheets in chronological order (sorted first by the date column, then the time column), and that updates onEdit, too, when someone makes a change on one of the three sheets. I know how to do it with a formula: =QUERY({IMPORTRANGE("id1","sheetName1!A1:D"); IMPORTRANGE("id2","sheetName2!A1:D"); IMPORTRANGE("id3","sheetName3!A1:D")}, "select * order by Col2 asc")'); , but I need a script. I don't want any formulas on the sheet. I tried your solution, but it only imports from one sheet at a time. Even when I write: importRange( ... ); importRange( ... ); as you suggest, it only shows me the result from one sheet copied and not from the other sheets (because the functions overlap). Please, I know that you can help me out with that. I am still learning and I hope to learn from you as well. Again, THANK YOU!
Utterly amazed. I usually have to search and piece together bits from different videos and then only sometimes can I get the code to work. You have made a video on the exact thing with everything I needed. Even using the time function. Made my life so much easier!
Thanks Allen. I know the feeling. I'm glad you found a match for the project you are working on. Happy coding!
Very nicely done-clear, concise, and well organized.
Glad it was helpful!
This was SO helpful, thank you! Very methodical and easy to follow. Exactly what I was looking for.
you are so easy to follow along with. Great tutorial I appreciate it
Awesome to hear. Glad you found the tutorial useful!
Appreciations from Pakistan :) Thanks for giving such awesome work👍
This came timely! Thanks a lot.
Great to hear.
I am from indonesia. Great tutorial I appreciate it
Glad it was helpful!
Thanks Scott. Nice content and very helpful. New subscriber here! :)
You are the man! Thanks so much, great video!
Thanks for your sharing, my case I have 40 different google sheet files with sheets named ABC
I want to put together a file that joins these data into sheets called EFG and every day the data is automatically updated without me having to open the file. Thanks for your help
You're welcome! Interesting use-case. Thanks for sharing!
This is awesome. How would we go if you'll only filter what to transfer to the destination sheet based on a criteria or keyword?
Are you looking to copy only the values displayed in the filter across to the destination sheet?
You’re good, thanks for the great tutorial! I’d like to suggest a subject for a future video. Since Google Sheets do not allow to use formulas that returns a list of items in data validation, I have a LOT of work on this specific matter.
I think a script that gets a comma separated list in a cell (e.g. D3) and apply them to a range all the way down (e.g. D6:D) as data validation - and then repeat that on all columns to right - would be really great. Thanks!
Hi Marcos, great idea. I have quite a backlog of videos ahead, but that's definitely one to put on the list.
Very nice. This is what I was looking for.
But I have a question.
You use a fixed ID for the source ID. Is it possible to place a changing ID here? My source file changes every week and so does the source ID. You choose the file and get the ID that can then be used as the source ID.
Thanks
Yes you can. You could add the IDs to reference cells or an input dialogue and reference them. Use the openById() method in this case.
This was so useful today thank you very much.
Great to hear! Thanks!
Thanks for the video. I am fairly new to this. Basically i am trying to find instructions on how to do a copy script for a specific set of cells (H7:J7) and then copy them to the next available row in columns C,D,E all on the same sheet. Do you have a video that covers that?
Hi Dean. I don't have anything that specific. You can use the getRange() and then getValues() methods like in the video. To paste them in the new location, you safest approach would be with this tutorial, yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
thats great video for learning and i have learned thourgh it
i wants to ask like if we wants to copy the data many times and wants to put them one below other (append) so how it will be done by this script.
Hi DME YUFTA, thanks for the kind words. That is definitely the topic of another video tutorial. The short answer is that you can use something like the appendRow() method ( developers.google.com/apps-script/reference/spreadsheet/sheet#appendRow(Object) ).
The long answer is that there are other things that may impact the way you append a row. If you might have multiple users who may be appending the row all at once, then you should probably implement something like LockSerivce ( developers.google.com/apps-script/reference/lock?hl=en ). Also append will fail if you have hidden formulas in your sheet that appear when there is data in a row. So you might need to get the first emply row in a column with ( yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/ )
Hope this helps.
~Yagi
Scott thanx for video. Good job..
Glad you liked it!
Wow you rule 💪 I was able to do it, thank you very much!
You're welcome!
thank you very much for this video. it helps me a lot.
is it possible to paste the data to different sheets in one spreadsheet every time we run it and non syncing it from the data source?
im trying to make a monthly database with clear spreadsheet source every month so the pasted data cannot be synced with the origin database.
do you have any idea how to do that?
Hi rifdah, I don't think that I am 100% sure what you you mean in the second part of your first question, sorry, but I will try to answer. Yes you can copy and paste date to other sheet tabs in the same spreadsheet. You can set up different scenarios or conditions by which to update them and even use simple triggers like onEdit or onChange or time triggers to schedule events to up update your sheet tabs.
I have some more tutorials in the links in the description on my website and also info on triggers.
I hope this helps a little.
Hey Yagi, great tutorial - thank you so much, made my task much easier! :) Do you think you could prepare a tutorial for a case when you need to copy specific cells in a column (as in getRangeList or something along the lines) and copy them to a different Google Spreadsheet, also passing a list of cells to be pasted into? That would be wonderful as I cannot find any solution online that works for my case. Have a great day!
Hi Igor, I can add this to the list. It might take a little time to get to though. A couple of clarifying questions.
So you are looking to copy values from selected cells in a range or the entire column? How do you want to determine which cells/columns that will needed to be selected?
@@yagisanatode Hi, thanks for replying - I want to copy a selected range of cells (eg. I25, I67, 68, etc.) from workbook A sheet 1 (by ID and sheet name) to a selected range of cells (eg. B12, B25, B40) to a different workbook and sheet (by ID and name). In my case I determine which cells I need to copy fromto empirically :) Copying an entire column, or a fixed range, is very straightforwadly explained in this tutorial, my case however, seems a bit complicated as I have searched numerous stack threads and was not able to come up with a solution that would fix my problem. Hope that clears it up a bit. Best to you!
Great video. Thank you
This is so useful, my boss will like it!
For this case: Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another, how can you select cells out of a column, full of hidden formulas, which are not hidden?
Hi Papi,
You might find this tutorial helpful:
yagisanatode.com/2018/11/17/google-apps-script-copy-selected-columns-in-one-sheet-and-add-them-to-the-bottom-of-different-selected-columns-in-another/
tq very much for the knowledge 👍🏻
You're very welcome.
Thank you for your video. I watched till the end.
And now I feel curious. Can the user copy paste data to other Google sheet (as destination) but only if tick box is check box.
And data which pasted to the Destin is editable
Yes, you can for both.
Thank you very much. Is it possible to import only particular columns? For example "A, D, CJ"?
This is amazing. Will this work if you have a table format and you wanna copy the values only on a specific cells in transpose format?
Thank you very much for this tutorial! I was wondering, can this script also be applied to other sheets in the same google sheet file? and if yes, could it be manipulated also within each individual sheet while it updates the master sheet? or is it only possible to manipulate the master file and it updates in the destination/sheet?
Yes you can! You can use the getRange() method or the copyTo() methods locally to do this. Check our the other tutorial links in the description for some more guides on working with data in Google Sheets with Apps Script.
Hi! This tutorial is the best! I have encountered an issue with big data and the appscript is timing out... Can you give us a resolution for this, please? Thanks a lot!
Without seeing how you retrieve your data, there are a few things you can do.
1. You made find that the Google Drive Advanced is a little more performant by sending a batchupload request.
3. Grab the data by n length of rows at a time. Keep track of the time it takes to complete each process and keep track of the last row of each range that you collected using PropertiesService. After a few test runs you will figure out an optimal range to copy and paste at a time. As you approach your time-out period (6 minutes), create a time trigger to run ever minute and every minute repeat the process until the script is complete.
Hope this helps.
would this work if the source sheet is an .xlsx file - that is stored in a google drive?
Not directly but Csaba Csonka has a pretty innovative workaround for this: github.com/cscsonka/Parsing-MS-Excel-file-with-Google-Apps-Script
Buy him a coffee while you are there! 😉
If I basically want to copy and based information inside selected cells from one sheet to another what would be the code .I need to be able to select what information I want to transfer to the other sheet and need to select certain columns where the information would go
Nice content thank you so much!
I'm trying to append data from two different to single sheet and without blank rows. can you please provide solution here?
Thanks in Advance!🤞
Hi Palyam, I haven't got a tutorial specifically to your needs just yet, but I can point you in the right direction. Your best bet here is to gather the data from both sheets with something like .getRange() or getRangeByName() methods then get the values with .getValues(), find thein common column, e.g. an id or a name and combine them together with a loop like for or forEach or use the map funcitonal method.
Next, you can get the last row a number of ways. The easiest being getLastRow(). If you also have a bunch of hidden formulas in other columns you might be better off using the approaches in the link below.
Then use getRange(your last row, your col start, yourData.length, yourData[0].length),setValues() to append your data. yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
Thanks, Scott; This program is very useful. Can you please suggest how to copy the selected columns or copy the entire sheet except for a few columns by modifying the given program?
Can this program be modified to..1.Only changed row gets updated, Append /copied rows in destination based on some condition in the source file. ;
Hi, you might find this tutorial helpful:
Google Apps Script - Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another [updated Feb 2022]
yagisanatode.com/2018/11/17/google-apps-script-copy-selected-columns-in-one-sheet-and-add-them-to-the-bottom-of-different-selected-columns-in-another/
This video was amazing! At the end of the video you mention going over filtering empty spaces in future videos but I can't seem to find them. Have you made them yet or are they still in the works?
Hi Justin. Unfortunately I haven't yet. There are a few similar written tutorials on my site (Links in description), that might point you in the right direction for the time being. I've added this to my priority list, but projects have kept me pretty busy, hopefully get a video out in the next few months.
Not sure if you have a video like this but here goes..... We have in one sheet a row with a "work order number" . We want to copy this row to a master spreadsheet. However, I want to add a row if the number is not in column B. If the number exists then just update that row. Is this possible with script? I know your example is sheets within the same spreadsheet.... so mine is a little different.
Hi Michael. Yes is it possible,
The closest I have tutorial-wise is this: yagisanatode.com/2019/02/13/google-apps-script-when-i-add-a-value-to-a-cell-in-a-selected-column-i-want-it-to-move-to-another-google-sheet/
You might be able to modify it to your own needs.
It might be worth having a chat with a developer to see what they can do to solve your specific problem.
I'll keep your request in mind though and added to my tutorial list.
Thanks for sharing such a wonderful script!! I have a query to ask in this, if we want to import same sheet but different columns how do we do that?? is there a way to add multiple columns from the same Sheet ( Source & destination Sheets are same)
Thanks Yagi. This is helpful. :) Now, I would like to set the destinationRangeStart as a variable based on a first empty row available in the destination sheet. This will let me save the historical data log (aka screenshots) of the input data rows which are changing daily.
Could you please help with that?
Thanks!
Hi Balbina, Yes in short take a look at either the .getLastRow() method or .getNextDataCell() methods. I have some tutorials in the description of the videos to help you go a little further, but this one will probably be the best for your needs, yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
Is there a way to import data into the destination and the destination to not change on the existing data ? So if I delete something from the Source to still have it on the Destination
You sure can. I have done something similar to this in this tutorial: yagisanatode.com/2019/02/13/google-apps-script-when-i-add-a-value-to-a-cell-in-a-selected-column-i-want-it-to-move-to-another-google-sheet/
Can this be extended/modified such that the source sheet's script creates the destination sheet, reads its I'd, and uses that id for the destination I'd?
Absolutely. You could generate a new sheet tab in the existing Google Sheet workbook or create a new Google Sheet.
developers.google.com/apps-script/reference/drive/file#makeCopy(String,Folder) developers.google.com/apps-script/reference/spreadsheet/spreadsheet#copy(String)
Very nice video. but i have 1 question you used "Sheet1!B2". but how can i define that using a Cell. For example i have multiple sheets. (Sheet1, Sheet2......and so on) but by using a Cell. and in that cell i type Sheet2. and it goes to Sheet2 B2 etc?
You might find this series useful: ruclips.net/p/PLSDEbLgMgqvrwnVekQKlrx1Zyi1tUFsyY
Particularly the third tutorial: ruclips.net/video/AgwnbVM8-kw/видео.html
Thanks Yagi. can help me.... post coding in here please....
very helpfull, dude, thanks for the video
Glad it helped!
Nice content really useful!
Can we apply filters on the imported data?
I have been searching on this, how to apply filters on the imported data, but we are not able to do so since the data imported is dynamic which means the data will not be filttered it will automatically convert back to its original imported form.
Any ideas how to do that?
Hi Sanjay, yes we can use the filter class to apply new filters. developers.google.com/apps-script/reference/spreadsheet/filter
Just keep in mind that this Apps Script approach is different to Google Sheets IMPORTRANGE in that it can be filtered directly.
Hello! I really liked your script and this is exactly what I needed, but I have only one problem. I lack script skills (I'm sorry) and I have searched already for a way to delete only rows A1 and B2 before pasting the data on destsheet but even trying a lot before without help I only got errors, can you please help me with this issue?
Hi Maze, once you getValues() you can use JavaScript slice or splice to remove those selected rows before using setValues() to paste it into your new sheet.
developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/slice
developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/splice
Have a try using these methods and if you get stuck feel free to add a copy of your attempt and either I (time permitted) or a reader can guide you in the right direction.
Hi,
I'm getting an error that's mentioned below.
Exception: the parameters (number, number, number, null) don't match the method signature for SpreadsheetApp. Sheet. getRange.
Please can someone help me out here.
What do you do if you are given a time delay when filling in the next data?
I don't quite understand what you are asking here. Could you expand on your questions, please?
Can I copy the column width and row hight to destination sheet too?
Hi Inge. You sure can. You can use the getRowHeight() getColumnHeight() and setRowHeight() setColumnHeight() methods here.
developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#setrowheightrowposition,-height
You can find a similar example of this in action in this tutorial here:
yagisanatode.com/2019/03/22/google-apps-script-maintain-row-heights-when-copying-and-pasting-data-in-google-sheets/
Excellent and wonderful explanation
I have a question
Excellent and wonderful explanation. I have a question. Can I add LAST ROW to the migration code without doing CLEAR?
Yes, you can
@@yagisanatode
How is it done?
I have an archive in a Google Sheet file. I want to migrate the data with cell formatting and usage The last row with an equation importrang
@@As-rb5ij Experiment. What would happen if you comment out or delete the destSheet.clear() ?
@@As-rb5ij There are a number of additional methods that you can deploy to copy over formatting and formulas. These methods will start with `get`.
Within the same Google Sheet you might want to look at the `copy` method. ruclips.net/video/34ozx-6sv34/видео.html
Links to the docs: developers.google.com/apps-script/reference/spreadsheet/range
How can ee do this import in append way...means i don't want to overwrite the data...everytime when I will run the script then data needs to copy paste in append way means the last row of that destination sheet..and later i will romove duplicate data via using the duplicate script functions
any update
Hi Kamalz, Apologies for the delay. I took a little time of a busy work schedule. Yes this tutorial is designed for those cases where you want to overwrite data in a range. You can use the SpreadsheetApp appendRow() method for adding individual rows of data to the bottom of the sheet. Likewise you can use the getLastRow() method to find the last row of the sheet and start your range from there. If you have a busy Google Sheet with lots of users you may also need to use LockService to prevent users from adding to cells simultaneously. I'll be covering these approaches in future videos.
Here are some other tutorials is you have slightly different needs:
- Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes [updated Dec 2021] - yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
- Google Apps Script - When I add a value to a cell in a selected column, I want it to move to another Google Sheet [update Jan 2022] - yagisanatode.com/2019/02/13/google-apps-script-when-i-add-a-value-to-a-cell-in-a-selected-column-i-want-it-to-move-to-another-google-sheet/
- Google Apps Script - Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another - yagisanatode.com/2018/11/17/google-apps-script-copy-selected-columns-in-one-sheet-and-add-them-to-the-bottom-of-different-selected-columns-in-another/
Great video, but I can't seem to find the code for the video. It is exactly what I've been looking for. Can you point me to the download for the code. Thanks
Hi Everett, you can find the code in the written tutorial. It's the second link in the description.
@@yagisanatode sorry - it doesn't seem to download - All I get is a "function MyFunction()"
@@everettpeschke286 The first link should be the starter sheet for you to follow along and write the code following the video. The next link is for the written tutorial. The full code is there if you get stuck along the way.
@@yagisanatode OK thanks - I just typed it in from the video. Great work. You'll get a cup of coffee if I get it working...
@@everettpeschke286 Awesome! Happy coding!
Thank you. But i have a question that how to put one defined ID in the position of DestinationID. I want use 1 DestinationID for multiple sourceID when i publish the function. Tks.
Sure, you could user an interator like a 'for' loop or forEach to iterate through the importRange() function each time changing the sourceID.
Great stuff! Thank you for this. Is there a way to rather set a specific destination sheet to create a new sheet named as date and paste the data there? I've adjusted the code so it pastes the data to next empty row but the new sheet thing is way too advanced for me :( thanks for any ideas!
Great work modifying the code, Michael.
Yes you can check out SpreadsheetApp.create() developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#createname,-rows,-columns
You can generate a date with something like const myDate = new Date() developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/Date
You can even change the date to a specific format with .toLocalDateString() developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString
Hope this helps!
My Friend,
in Line 21. to Clear Specific Cell Ranges like B2 to G2 downwards, can I modify it to : destSheet.Clear (B2: G)
or what command do i use?
You will need to get the range first using getRange("B2:G") on your `destSheet`. The auto suggestions will list a number of clear options like clear(), clearConditionalFormatRules(), clearContents(), clearFormats(), clearNotes()
@@yagisanatode . ok Great. Thanks alot
Hi sir, i would like to thank you for this very wonderful guide. Its really amazing and i really helped me a lot.
Now, my problem is we have added 2 different g sheet files.
Will you be able to make a video like this, that shows us how to import data from 5 different g sheets file and consolidate it to 1 file.
For example:
There are 5 different places/site that is doing an attendance for employees, so i need to consolidate them in my file by using appscript.
A tutorial on collecting data from multiple sheet and storing it in a destination sheet sounds like a good tutorial. I'll add it to my list.
And also, if possible please make it user friendly as i have my team mates also using the masterfile, so it will be very nice if all users in the masterfile have the ability to run the code by just clicking the button.
What's the best way to copy and paste the formatting as well?
In this specific case of copying between different sheet you will also need to use formatting collectors like .getBackgroundColor() => setBackgroundColor(), and .getRichTextValues() => .setRichTextValues() is a pretty good catchall. developers.google.com/apps-script/reference/spreadsheet/range
It have problem wheb i have large value ## it run out of time litmit appscript and data not transfer finish
Roughly, what size range are you looking at?
I have a Question: Great Video.
My sourceRange"XYZ! A2: G" is only generating 1 value out of the multiple data in the source sheet
what am I doing wrong?
Line 4 in the code is Returning only 1 cell data instead of a Range of data
Hmm are you using getValue() | setValue() or getValues() | setValues()?
@@yagisanatode, I feel like Giving you are Great Hug Right Now....That was exactly the Problem. it's working now with getValues(). 100% Appreciation to you Bro!! 🤪
Hi Scot and thanks for this great script. I've been searching the internet for something like this for days. I've try to modify your script for my real working scenario and i got stuck. My question is... can this script to copy ONLY the range values from today's date and later?. If is helpful i can add formatted dates in row 1 in all columns in all the sleets in both spreadsheets.
After collecting your data try using a loop or the JS .filter() method to iterate through your values in your range to find dates greater than or equal to what you need.
How to add many rows data to existing sheet (data).. In a same googlesheet..
Hi Awwal, you can find links to more ways of adding data to Google Sheets in the links in the description above. Hope this helps.
Awesome
Exactly what I needed
Great to here.
I am having an issue where it's copying the date and is minus one day. I've tried changing cell format and validating cells to make it quit. Been on it about an hour and I can find no earthly reason why it is subtracting one day from any date that is copied. Anyone run into this or have an idea?
***EDIT**
Solved. The sheet itself, under settings, was set to a different time zone. Not sure how that happened, but once I made them match there was no more issue.
Hmm... is it precisely one day, do you know? If so this might suggest a calculation error.
Alternatively sometimes timezones of your Spreadsheets can be different and even your Apps Script project. You can check by going to File > Settings in your source and destination sheets. In your apps script project go to Project Settings > General Settings.
Hopefully this helps troubleshoot your issue.
yagisanatode.com/2020/11/17/google-sheets-beginners-change-your-current-workbooks-timezone-date-currency-16/
yagisanatode.com/2021/03/10/help-my-times-triggers-are-not-in-sync-how-to-update-your-google-apps-script-project-time-zone/
@@yagisanatode Solved - Time zone was exactly it. Thanks for reply.
Hey ! That was deeply helpfull thaks lot ! 😊 Atm im stuck with and additonal step i would love to implement (since few hours, editing your script). I try to remplace the "destinationRangeStart" with some kind of "MySheet.getLastRow()+1" but i really don't know where i can implement it. Could you help ? ❤
Cheers ! With some tryhard and a lot and hour i finally made it by myself ! Thanks both to this video and some of your website exemple. 🥳
Great to hear. That's always more satisfying figuring it out your self. Nice work, Orakur!
@@orakur8242 hello, could you please share the bits of the code that you needed to adjust? Looking exactly for this but I am stuck. Thank you!!
edit: nope, does not work, resp. looks like I have to run the original code, then change it to the below and it works. If I run it in an empty file, it does not work. I AM CONFUSED!
sorted it this way and seems to work
// Get the full data range to paste into next empty row after start range.
const destRange = destSheet.getRange(
destStartRange.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()+1,
destStartRange.getColumn(),
sourceVals.length,
sourceVals[0].length
@@michaelhaj1 seems my first comment was deleted due to the link of the pastebin ^^ share me a trash mail i send you what i have done. I have commented it a bit with what i remembered. 😊I think if you have followed this video you could easily understand what i have added and edit it for yours ! 😉
This is quite helpful. How can i make the destination range starting point (in the code it's "Input Data!A1") to be a variable and to always be last row in the sheet name Input Data?
function runsies() {
importRange1(
"4uyrjP5dfsdfaWm_LG09nRwkVFTMuRPK", //not real Id
"Dataset1",
"87mkpYvpR02oyOYvhhgJOptNFZjtESGEje25A", //not real Id
"Input Data!A1"
);
}
Hi Timothy, your best bet would be to do this inside the getRange() function once you get the spreadhseet. SpreadsheetApp.openById(--THE SPREADSHEETSHEET ID--).getSheetByName(--SHEET TAB NAME--).getLastRow().
There are some situations where this will not be 100% successful however, you can check out this tutorial for more information: yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
@Yagisanatode -Scott-
Thanks so much Scott. I have a question though, I tried modifying the script to paste the data in the last row of my destination sheet using the getLastRow function but it returns an error.
"Final_collated_analysis!A2" ---- the destination range
const destStartRange = destinationSS.getRange(destinationrangeStart.getLastRow)
destStartRange.getLastRow()
Please help and let me know if you have a better idea
Thanks in advance : )
Hi Bariyal, have a look at the written tutorial in the link in the description, it might give you a better understanding on what is going on.
In the example, the getRange() method takes 4 arguments, .getRange(start Row, start Col, row depth, col width) Notice that all these arguments are contained in braces "()"
To apply a value to the last row try something like this:
// Get the full data range to paste from start range.
const destRange = destSheet.getRange(
destStartRange.getLastRow(),
destStartRange.getColumn(), // OR col 1 or for col A
sourceVals.length,
sourceVals[0].length
);
Hope this helps.
@@yagisanatode Thanks, it really helped.
Hi there,
First of all: thank you so much for your videos! They are really really helpful! I just subscribed to your channel :)
It has been days (and nights) and weeks since I have been trying to find a solution to my issue, but unfortunately I did not find anything around :(
This is my situation: I have several sheets with a list of the events (date, time, event name, description...). I would like to create a script that imports the data of each sheet in one sheet (called Master Calendar). I need this one Master sheet to have the data imported from the three sheets in chronological order (sorted first by the date column, then the time column), and that updates onEdit, too, when someone makes a change on one of the three sheets.
I know how to do it with a formula:
=QUERY({IMPORTRANGE("id1","sheetName1!A1:D"); IMPORTRANGE("id2","sheetName2!A1:D"); IMPORTRANGE("id3","sheetName3!A1:D")}, "select * order by Col2 asc")'); , but I need a script. I don't want any formulas on the sheet.
I tried your solution, but it only imports from one sheet at a time.
Even when I write:
importRange( ... );
importRange( ... );
as you suggest, it only shows me the result from one sheet copied and not from the other sheets (because the functions overlap).
Please, I know that you can help me out with that. I am still learning and I hope to learn from you as well.
Again, THANK YOU!
You'll need to append the data. Take a look at some of the other tutorial links in the description, they should help guide you to what you need.