Copy and Paste Range Values from one Google Sheet into another with Google Apps Script
Рет қаралды 46,597
Күн бұрынHere is a link to the starter template:
docs.google.com/spreadsheets/...
You can get the written tutorial here, along with some bonus material on formatting imported data:
yagisanatode.com/2022/02/22/c...
In this tutorial, we cover how to paste a complete set of Google Sheets range values from one Google Sheet to another with Google Apps Script. This tutorial is essentially an Apps Script alternative to the Google Sheets IMPORTRANGE function.
00:00 Intro
00:16 Apps Script over IMPORTRANGE
00:36 Setting up your Google Sheets
02:04 Writing the runsies() function
06:45 The importRange() function
16:53 testing the script
17:26 Applying to multiple import range instances
18:14 Adding a time trigger
19:32 Other approaches
You can find out more about how to append and manipulate Google Sheets data in these tutorials:
- 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/g...
- 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/g...
- Google Apps Script Course - Part 1: 2D Array Data Transformation of Single Question Item Data to Total Count of Choices in Google Sheets - Google Apps Script Course - Intro: 2D Array Data Transformation for Google Sheets - yagisanatode.com/2019/12/22/g...
- Creating Unique Ranges from 2D Arrays in Google Apps Script - yagisanatode.com/2020/10/26/c...
- Google Apps Script: Add and removed Google Sheets columns based on a search array - yagisanatode.com/2020/03/28/g...
If you like the video, check out my website:
yagisanatode.com/
Here is the link to just Google Apps Script tutorials:
yagisanatode.com/google-apps-...
Get your copy of the Google Sheet Project Task Manager here: yagisanatode.teachable.com/p/google-sheets-project-tasks-manager
#GoogleSheets #GoogleWorkspace #GoogleAppsScript #AppsScript #Spreadsheets
@pjohnson21 Жыл бұрын
This was SO helpful, thank you! Very methodical and easy to follow. Exactly what I was looking for.
@Bafa Жыл бұрын
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!
@yagisanatode Жыл бұрын
Thanks Allen. I know the feeling. I'm glad you found a match for the project you are working on. Happy coding!
@HamiltonRichards 9 ай бұрын
Very nicely done-clear, concise, and well organized.
@yagisanatode 9 ай бұрын
Glad it was helpful!
@exceladvisor29 2 жыл бұрын
Appreciations from Pakistan :) Thanks for giving such awesome work👍
@bytetonesbytetones3083 Жыл бұрын
You are the man! Thanks so much, great video!
@MatthewGigantelli Жыл бұрын
you are so easy to follow along with. Great tutorial I appreciate it
@yagisanatode Жыл бұрын
Awesome to hear. Glad you found the tutorial useful!
@sophiauwoziya9765 Жыл бұрын
This came timely! Thanks a lot.
@yagisanatode Жыл бұрын
Great to hear.
@MrChrisgapo 2 жыл бұрын
Thanks Scott. Nice content and very helpful. New subscriber here! :)
@jeremyross7641 Жыл бұрын
Great video. Thank you
@lordAlpargata 2 жыл бұрын
Wow you rule 💪 I was able to do it, thank you very much!
@yagisanatode 2 жыл бұрын
You're welcome!
@KenanAnt 2 жыл бұрын
Scott thanx for video. Good job..
@yagisanatode 2 жыл бұрын
Glad you liked it!
@Allsmort Жыл бұрын
This was so useful today thank you very much.
@yagisanatode Жыл бұрын
Great to hear! Thanks!
@user-py9is1xm9e 4 ай бұрын
I am from indonesia. Great tutorial I appreciate it
@yagisanatode 4 ай бұрын
Glad it was helpful!
@rosebellbicar1125 2 жыл бұрын
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?
@aishahakpk237 Жыл бұрын
tq very much for the knowledge 👍🏻
@yagisanatode Жыл бұрын
You're very welcome.
@gp9651 Жыл бұрын
Thank you very much. Is it possible to import only particular columns? For example "A, D, CJ"?
@user-el2ey6ef3f Жыл бұрын
very helpfull, dude, thanks for the video
@yagisanatode Жыл бұрын
Glad it helped!
@hetalbedwal3988 2 жыл бұрын
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)
@marcosrs75 Жыл бұрын
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!
@yagisanatode Жыл бұрын
Hi Marcos, great idea. I have quite a backlog of videos ahead, but that's definitely one to put on the list.
@justinprimmer2274 Жыл бұрын
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?
@yagisanatode Жыл бұрын
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.
@igorremesz7310 Жыл бұрын
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!
@yagisanatode Жыл бұрын
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?
@igorremesz7310 Жыл бұрын
@@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!
@dmeyufta3415 2 жыл бұрын
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.
@yagisanatode 2 жыл бұрын
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
@balbina5859 Жыл бұрын
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!
@yagisanatode Жыл бұрын
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/
@michaelhaj1 2 жыл бұрын
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!
@yagisanatode 2 жыл бұрын
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!
@sumadoolpeep7171 Жыл бұрын
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?
@yagisanatode Жыл бұрын
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.
@alien3303 Жыл бұрын
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
@SanjaySingh-zp9uv 2 жыл бұрын
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?
@yagisanatode 2 жыл бұрын
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.
@DJJeSta09 Жыл бұрын
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?
@yagisanatode Жыл бұрын
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/
@exultdocs9350 2 жыл бұрын
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. ;
@yagisanatode 2 жыл бұрын
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/
@user-ls2et6th5v 6 ай бұрын
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?
@yagisanatode 6 ай бұрын
Are you looking to copy only the values displayed in the filter across to the destination sheet?
@rifdahfadhilah1137 Жыл бұрын
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?
@yagisanatode Жыл бұрын
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.
@papisnipe3022 2 жыл бұрын
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?
@yagisanatode 2 жыл бұрын
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/
@palyamreddisekhar3475 2 жыл бұрын
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!🤞
@yagisanatode 2 жыл бұрын
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/
@mythstqn Жыл бұрын
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
@yagisanatode Жыл бұрын
You're welcome! Interesting use-case. Thanks for sharing!
@jaypeevicente8224 Жыл бұрын
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!
@yagisanatode Жыл бұрын
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.
@maze7653 2 жыл бұрын
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?
@yagisanatode 2 жыл бұрын
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.
@noemimesesan1054 2 жыл бұрын
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
@yagisanatode 2 жыл бұрын
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/
@muhamadekocahyono2661 28 күн бұрын
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
@yagisanatode 26 күн бұрын
Yes, you can for both.
@buivancao6035 2 жыл бұрын
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.
@yagisanatode 2 жыл бұрын
Sure, you could user an interator like a 'for' loop or forEach to iterate through the importRange() function each time changing the sourceID.
@moses5407 8 ай бұрын
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?
@yagisanatode 8 ай бұрын
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)
@DominiqueMoons-x3i 7 күн бұрын
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
@yagisanatode 4 күн бұрын
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.
@ingewll 2 жыл бұрын
Can I copy the column width and row hight to destination sheet too?
@yagisanatode 2 жыл бұрын
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/
@ajscx 2 жыл бұрын
Awesome
@ajscx 2 жыл бұрын
Exactly what I needed
@yagisanatode 2 жыл бұрын
Great to here.
@MichaelDaniels Жыл бұрын
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.
@yagisanatode Жыл бұрын
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.
@dopdopno2007 2 жыл бұрын
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.
@yagisanatode 2 жыл бұрын
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.
@dopdopno2007 2 жыл бұрын
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.
@IGLESIASVEGA Жыл бұрын
would this work if the source sheet is an .xlsx file - that is stored in a google drive?
@yagisanatode Жыл бұрын
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! 😉
@nursahrum1129 Жыл бұрын
Thanks Yagi. can help me.... post coding in here please....
@edenjobsheet9410 2 жыл бұрын
What's the best way to copy and paste the formatting as well?
@yagisanatode 2 жыл бұрын
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
@RensleyDaalHD Жыл бұрын
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?
@yagisanatode Жыл бұрын
You might find this series useful: kzfaq.info/sun/PLSDEbLgMgqvrwnVekQKlrx1Zyi1tUFsyY Particularly the third tutorial: kzfaq.info/get/bejne/d82nocWHsp6Rm6s.html
@kamalzdaz2395 2 жыл бұрын
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
@kamalzdaz2395 2 жыл бұрын
any update
@yagisanatode 2 жыл бұрын
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/
@MathematicalSeries-be9wc 9 ай бұрын
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?
@yagisanatode 9 ай бұрын
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()
@MathematicalSeries-be9wc 9 ай бұрын
@@yagisanatode . ok Great. Thanks alot
@rahmanpasha5314 2 жыл бұрын
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.
@PaweCzech-ol4xz Жыл бұрын
In last code line I used "lenght" not "length". Try checking out your gramar
@afahruf90 2 жыл бұрын
How to add many rows data to existing sheet (data).. In a same googlesheet..
@yagisanatode 2 жыл бұрын
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.
@everettpeschke286 Жыл бұрын
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
@yagisanatode Жыл бұрын
Hi Everett, you can find the code in the written tutorial. It's the second link in the description.
@everettpeschke286 Жыл бұрын
@@yagisanatode sorry - it doesn't seem to download - All I get is a "function MyFunction()"
@yagisanatode Жыл бұрын
@@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.
@everettpeschke286 Жыл бұрын
@@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...
@yagisanatode Жыл бұрын
@@everettpeschke286 Awesome! Happy coding!
@MathematicalSeries-be9wc 9 ай бұрын
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?
@MathematicalSeries-be9wc 9 ай бұрын
Line 4 in the code is Returning only 1 cell data instead of a Range of data
@yagisanatode 9 ай бұрын
Hmm are you using getValue() | setValue() or getValues() | setValues()?
@MathematicalSeries-be9wc 9 ай бұрын
@@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!! 🤪
@haohuynh464 Жыл бұрын
It have problem wheb i have large value ## it run out of time litmit appscript and data not transfer finish
@yagisanatode Жыл бұрын
Roughly, what size range are you looking at?
@orakur8242 2 жыл бұрын
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 ? ❤
@orakur8242 2 жыл бұрын
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. 🥳
@yagisanatode 2 жыл бұрын
Great to hear. That's always more satisfying figuring it out your self. Nice work, Orakur!
@michaelhaj1 2 жыл бұрын
@@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!!
@michaelhaj1 2 жыл бұрын
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
@bobthevegetables607 2 жыл бұрын
@@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 ! 😉
@Bafa Жыл бұрын
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.
@yagisanatode Жыл бұрын
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/
@Bafa Жыл бұрын
@@yagisanatode Solved - Time zone was exactly it. Thanks for reply.
@timkumwenda 2 жыл бұрын
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" ); }
@yagisanatode 2 жыл бұрын
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/
@bariyatlikita6767 2 жыл бұрын
@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 : )
@yagisanatode 2 жыл бұрын
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.
@bariyatlikita6767 2 жыл бұрын
@@yagisanatode Thanks, it really helped.
@PaweCzech-ol4xz Жыл бұрын
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. Help.
@PaweCzech-ol4xz Жыл бұрын
Ok nevermind. In last code line I used "lenght" not "length" :D
@yagisanatode Жыл бұрын
"lenght" is my arch-nemesis.
@video2532 Жыл бұрын
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!
@yagisanatode Жыл бұрын
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.
@nessus85100 2 жыл бұрын
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.
@yagisanatode 2 жыл бұрын
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.
Work Smarter Not Harder
Рет қаралды 172 М.
Learn Google Sheets & Excel Spreadsheets
Рет қаралды 71 М.
Stereotype Breakers
Рет қаралды 66 М.
TechLever
Рет қаралды 53 М.
Code With Curt
Рет қаралды 36 М.