That means you have to select only the relevant rows for your range. But there are multiple ways to do this and it depends very much on your specific situation. Without looking at the sheet I couldn't tell you what the best option for your specific situation is. But I'm sure you'll figure it out. 😀
Thank you saperis! Your video, and a particular comment of yours below got me out of a problem that I've been dealing with for a long time. Thank you!! Thank you!!
Hi, I don't remember if you have mentioned that, for me, the most important difference between both method is that only the first method you will be able to run between two different spreadsheets.
Thank you that's really helpful, I'm using a random generator which I want to paste the values from one tab to another, however, I want it to paste to the next available blank rows on the target spreadsheet rather than saving over them. How would I do this? Thank you.
You would have to write a script that checks what the last row is and then write the data in the next row. Check how to find the last row: developers.google.com/apps-script/reference/spreadsheet/sheet#getlastrow
Really loved the simplicity of this guide. Thank you lots! Quick question. Suppose I wanted to tell appscript that whenever I input new data in a new cell, that it should copy it across other tabs. This is assuming the other tabs have the same data but of different objects. How would I go about doing this?
As always when coding there are many ways to go about this. Without seeing the data and understanding the process exactly it's very difficult to make a valid suggestion. But, generally speaking, I guess you could either a) write a function that you run manually and it get's whatever value is in the cell you specify or b) write a function that runs periodically and get's whatever is in the cell you specify.
Hi Chanel! I enjoyed this video and was easy to follow along with. Thank You! Question: I would like to modify this script to select specific cells (i.e. Sheet1, A3) and copy that data to other cells (i.e. Sheet 2, A3). The Copy To Method almost does what I need it to do except that, for me, it copies the entire (source) sheet and pastes all of the values to the (target) sheet. Do you have a video to accomplish the task of copying/pasting data from and to specific cells using App Script?
No, I don't have such a video. If you check the documentation you'll see that you can define the range that should be copied: developers.google.com/apps-script/reference/spreadsheet/range#copytodestination
Could you please assist me with my issue? I need to transfer data from one Google Sheet to another. The complication arises because my source data contains columns in both French and English. For example, I need the "Name" column data to be copied into the "Nom" column in the target sheet. Similarly, the "Nom" column (French version) in the source data should also be copied into the "Nom" column in the target sheet. I would like to know if this is feasible.
Transposed should help with this. Have a look at the documentation: developers.google.com/apps-script/reference/spreadsheet/range?hl=en#copyTo(Range,CopyPasteType,Boolean)
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.
Yes, that is possible. I don't have a video about that so I can't guide you with anything I've published. Also, I don't do any new Apps Script videos anymore.
The online course is made up of 1) videos 2) Google Slides 3) quizzes at the end of every module. So it will be you *watching videos* and following along as you watch me explain and code.
Sure, all you have to do is not include the first row when you are getting the data from the source. Learn more about selecting a range in Sheets: developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column,-numrows,-numcolumns
I modified your script to update entire row records using Excel as UI and 2 Gsheets as server. Searched high and low to find this solution. Thank you so much.
I really loved your teaching!! I just got to know your channel. Nice to meet you, I'm Brazilian. I'm looking for some tutorial that explains how to copy some data from one workbook to another workbook, is it possible? I have two files in Excel online (two workbooks) and all I wanted is to copy some data (they are in a table) from one file to the other :( I'm desperate hahaha I've been looking for several weeks... can you help me pleaseeeee? I've tried "recording actions", trying to edit the script, and even creating two scripts (one in each file) and trying to use the same one in another file. But I don't know if it's possible and what I can do...
Thanks for watching our videos. The tutorials we make are for Google Workspace apps like Google Sheets. We don't do any videos about Microsoft Excel or other Microsoft apps.
Thank you for this video, but I have one Q that, Can I have data, and condition is If user select YES from downlist list than only copy data to next sheet, If select NO than not copy the data to next sheet . This is possible if yes How? any idea? Pls Help.
Hi Chanel, Thanks for the great video! I would like to add a little note about how copyTo works with cell merging. One of the reasons for using copyTo is to copy the source format as well, but once there are merge cells in the source you need to open the source range with getRange (and specify: row, column, numRows and numColumns) instead of just getDataRange. The latter looks only for the cells with the value and the merged cells have no value in the last merged cell (the merger acts as a "space" holder). Therefore, using getDataRange is not always a good idea when using copyTo. Good luck!
You can define the range you want to write the data to. Check out this: developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getrangea1notation
I was planning to learn VBA or Python to automate staff within Excel, but your tutorial opens a lot of possibilities to put Google Sheets on crack! thanks a lot. Greeetings from Switzerland as well :)
A script can never choose to do something without you giving it the command. So you need to add a way to decide to which target you want to write the values.
Well, using a condition is exactly that: you tell the script what to do if a specific condition is met. I meant to say in my first response: The script won't decide on it's own. You have to provide the conditional checks and what the script should do once the conditions are met.
I'm not quite sure but I think it won't give you back the values from the formula. You might have to set the formula again in the target range to where you have copied the data. See how to set formulas with Apps Script: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-aYG55AlprHE.html
@saperis You're correct it doesn't return the values. I will try what you recommend today. Then, I only have to figure out how to have it ammend the data to the target sheet. I have a lot of respect for those of you who do this for a living. For me, the average Joe it is a touch towards overwhelming.
Is it possible to combine this with my script automatically hiding rows if value is the cell is "Complete"? I've put the Filter function formula but i doesn't work if rows are hidden. Thanks in advance!
Hi Sapiers! I've a sheet that automatically every day update the NAV of a list with different Investment funds. What I want to make it's to create different sheets (one for each fund) and copy automatically for example one time per month the NAV updated of all the investment funds to her corresponding sheet. Do you know if I can put in practice this idea en google sheets? Thanks!!!
Yes, that is possible with Google Apps Script. I could set up a time driven trigger that launches the script to copy the data from the source sheet to the target sheet. Find out more about triggers in this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-KC7pBjD3GGw.html
You will need to select the correct data range (column) for the source and the target. The source and target data range need to be of the same dimension.
Great video! I was wondering if you do 1 on 1 tutorials? I have a macro that I have been stuck on for a while now, that I think you could solve very quickly.
Thank you very much, this video helped me wrap my head around some of the scripting. I do have a few questions on how to fine tune this for my usage. I'd really appreciate a bit of help if you could spare the explanation. using copyTo method, how can I set a checkbox as the trigger to copy? example: Checkbox in M4 copies row 2 to target sheet. I also can't seem to figure out how to make the copies not overwrite each other. Guessing there's some command that would insert rows for the next copy to write on.
If you want to use a checkbox you can do something like this: if (cellToCheck === 'false') { // do whatever you code needs to do} Checkboxes have either the value of 'true' when they are ticked or 'false' when they aren't. You could look into using the method getLastRow() to identify the last row witch content. Then you could copy your data below that row. developers.google.com/apps-script/reference/spreadsheet/sheet#getlastrow
Thank you for your videos !! I was wondering if you have a video that shows how to copy cells from sheet1 to sheet2 and then print sheet2. I can't seem to find any videos that does printing to a local printer. Thanks
You would have to check every line to see if it is empty. At least, that's the theory. I found something that might be useful: support.google.com/docs/thread/120740305/google-script-skip-empty-rows?hl=en
Your getRange returning null could be due to many different things. Usually it's an indication that something went wrong before your executing the getRange method; it's trying to get a range of something that doesn't exist. Here's my file so you can check your code against it: docs.google.com/spreadsheets/d/12faOzI-Fw0-5H5BowsHC_avTtc_yD5UzScqUp1r-U94/edit?usp=sharing
Hi , I'm new to sheets script. I want to Loop through each row on one sheet(Driver List) while copying specific cells of each row to a second sheet(Driver List2) until an empty row(last row) is found. I'm able to do one row at a time and just copy the code for the next row but this is clearing not the right way to loop through data. Any help would be greatly appreciated. This is what I have now: function copycells() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('A4').activate(); spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver list2'), true); spreadsheet.getRange('A4').activate(); spreadsheet.getRange('\'Driver List \'!A4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver List '), true); spreadsheet.getRange('B4').activate(); spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver list2'), true); spreadsheet.getRange('B4').activate(); spreadsheet.getRange('\'Driver List \'!B4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver List '), true); spreadsheet.getRange('C4').activate(); spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver list2'), true); spreadsheet.getRange('C4').activate(); spreadsheet.getRange('\'Driver List \'!C4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver List '), true); spreadsheet.getRange('M4').activate(); spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Driver list2'), true); spreadsheet.getRange('M4').activate(); spreadsheet.getRange('\'Driver List \'!M4').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); };
Some suggestions - loop over the rows and add the rows you want to copy to the second sheet to an array. - then write that array in one go to the destination sheet. - Have a look at my videos about looping over data ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vbu_ujCLbEk.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-JKsZoGcYGFk.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-6Lu3HEuMOdE.html
Loved the vid, How would I be able to copy a row using the getRange('H12:O12') vs row1 column 1 method you are using. Also need this specific getRange('H12:O12') to copy to the last row of my other sheet
Hi Raul. It doesn't matter if you are copying a range or a cell (which is also a range but just of one cell), it works the exact same way. If you want to paste to the last row of any given sheet you first identify that last row with the method sheet.getLastRow(). Here is the documentation on that method: developers.google.com/apps-script/reference/spreadsheet/sheet#getLastRow()
Hello mam your videos are always nice thanks a lot , There a question for you is it possible that we could keep source data range in one sheet and target data range in another sheet , means can we get data from a spreadsheet and copy in a different spreadsheet ?
Hey, really love the video and all the explanations, however I do have a specific problem that kind of has to use these functions but in a more specific way. I want to copy a row of data based on the value in column J and paste it into my target sheet. After that i want the script to remove the information from my original sheet to keep it clear. Think of it as "not completed, done, waiting" and i just want all the "done" moved to another tab/sheet. Is this something you'd be able to help out with?
Hi Linus. I'm happy to hear that you enjoyed the video. 😀 What you're looking for is a Google Apps Script developer to help you with your automation script. That's something I currently don't offer. But here you can find freelancer's who offer exactly that service: www.upwork.com/hire/google-spreadsheet-freelancers/
The way to go about this depends on wether you are using "copyTo" or "setValues". I found a Stackoverflow discussion that should help you find the best solution for your case: stackoverflow.com/questions/44130809/google-apps-script-copy-and-paste-formulas-only
Hi madam.. can u do the same with time interval. What i mean is each 5 mnt i want to copy a1 to c5 data from source sheet to target sheet a1 to c5 , then a1 to c5 data to target sheet a6 to c10 and continues. Please help
Hey there. You can use a time driven trigger to execute your script after every 5 minutes. See here how to do so: developers.google.com/apps-script/reference/script/clock-trigger-builder
Hi Saperis! Thank you so much for your video. It is really easy to understand. However, I would like to ask what if I would like to only copy those which meet the criteria (eg. let's say I have a list of data, i only want to copy those already remarked "Closed".) How can I modify the script in this case? Thanks, looking forward to hearing from you soon
I probably would have a check before taking the data and copying it to another sheet. With check I mean something like if(cellYouAreChecking === "Closed") {and then the code to copy that cell}
how to make a script to add data from sheetname "Add" to sheetname "Result" by pressing the Add button or add automation Menu Likewise for the addition of the next data will add to the position after the last row in the sheetname "Result"docs.google.com/spreadsheets/d/1Vij49vjiy96DiopDeUxCPzLpPEbIeHw_4getrrGYr-s/edit?usp=sharing
Well, I can't really write the code for you so you will have to base your own code of this video and the official Apps Script documentation: developers.google.com/apps-script/reference/spreadsheet
hi! thank you for the guide, I have 3 questions: 1. how can I paste as values the data? 2. how can I only paste a specific range? for example in a Sheet I only want to copy columns B:H, even though there is data beyond column H 3. how can I copy data below an existent dataset. thank you
Here some quick answers to your questions: 1. Check the setValue method to write data to a sheet: developers.google.com/apps-script/reference/spreadsheet/range#setValues(Object) 2. You define the range where you want to write your data. Check the different options you have to select a range: developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer) 3. How about checking where the last row is and writing your data below it? When using Apps Script you have to become well acquainted with Google's reference document. That's where you learn how to write any automation script you want. 😀
Hi Saperis, Thanks a lot for this wonderful video! Quick question. Is there any way to copy the notes attached to a cell value while copying the contents of a sheet to another sheet. How can i do this ?
I found a blog post that should help you copy the note of the cell including the value: yamm.com/blog/extract-cell-notes-into-another-column-using-google-apps-script/
You can write (almost) everything. You simply have to check how to write it in the official Google Apps Script documentation: developers.google.com/apps-script/reference/spreadsheet
@@saperis Awesome. Thank you. I saw your course. It looks like the basics (which I need) but I wish it was project based. Feels like I learned better with projects. I'll play with this one. Thank you. You're a great teacher. Also how long do you think it'll take to get this? I tried python for a year. Got the basics but could never write advanced stuff on my own. Lots of copying/pasting from around the web.
It's really difficult to give an average time it takes to learn any given programming language. I know this sound stupid but think of it as a hike up a beautiful mountain trail. It doesn't really matter how long it takes you. What matters is that you enjoy the hike and make it to the summit. 😀
Just two questions: If I let this script run at a selected time, will the data copied to the target sheet be overwritten automatically? If the source data does not provide any information e.g. webscraping unsuccessfull what is gonna happen to the data in the target tab?
If you were to take my exact same script and simply execute it multiple times: you would always overwrite the target. If whatever you are trying to copy isn't existing the whole script would fail.
@@saperis how do you prevent it from overwriting everything? The problem i'm having is when people delete data from the source sheet is also deleting it from the target. But the target is used as a data backup.
If you are using this copying as a backup my script won't work because it deletes whatever the person deletes from the source sheet. In the target sheet you would always have to write the new data entry from the source sheet to a new range in the backup sheet. And you would also have to only add the new changes and not the entire content of the source sheet.
Very nice.... but how to reference both spreadsheets in its code if I want to use setValues to copy data from one sheet to another sheet which is located in a different document ?
If you want to copay data (a.k.a. values) from one spreadsheet to another you need to use the copyTo method. Just the way I demonstrate it the the video. The only difference is that you will be using two separate spreadsheets and not two separate sheets in the same spreadsheet. The setValue method you use to insert data in the boundaries of the same spreadsheet on the other hand.
Or maybe the copyValuesTo might be interesting too: developers.google.com/apps-script/reference/spreadsheet/range#copyValuesToRange(Integer,Integer,Integer,Integer,Integer)
Hi Chanel, I'm very new to App Script, I find your tutorial great to follow, well demonstrated and easy to follow. You are an excellent teacher in your field. I have a large spreadsheet that I would like to remove data that, I no longer need for the time period. Do you have a video on "How to Move rows in google sheet to another tab" please? Thanks again
Thanks for watching our video. The principal of copying data is the same no matter if you do so from one spreadsheet to another or within a spreadsheet from one sheet (tab) to another sheet (tab). So you get your data from one tab and copy it to the other just like in the video. You will have to use the method getSheetByName() to actually get the different sheets within your file. developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getSheetByName(String)
Hi Saperis, your video is extremely clear. You mange to simplify everything. thanks a lot. A question please: some of my cells have inserted links in them, and I want these links to be copied as well. How can I do that?
Hi Saya. You can get the background color of any given cell. So theoretically you could use this information to create a condition and control the flow of the script. Have a look at some of the methods you might be using. developers.google.com/apps-script/reference/spreadsheet/range#getbackground developers.google.com/apps-script/reference/spreadsheet/range#getbackgroundobject developers.google.com/apps-script/reference/spreadsheet/range#getbackgroundobjects developers.google.com/apps-script/reference/spreadsheet/range#getbackgrounds
Thank You very much for your brilliant lecture. For the first time (as a coder) I have enjoyed your video and success. Now I want to transfer multiple sheets data of a workbook in a sheet by using setValue method. Is is it possible??? If yes and you have any video, please share the video link.
Thanks for the feedback! I think you should be using the copyTo method for what you're trying to achieve. Check out the official documentation on how to use this method: developers.google.com/apps-script/reference/spreadsheet/sheet#copyTo(Spreadsheet)
Wonderful ♥️. I will try this and let you know the outcome.(I just want that to format every new sheet I upload with the predifined script ) Thank you so much
This should be possible. You would need to check for what value you chose from the dropdown. You probably could use an onEdit trigger that runs your code once you've changed the value of that cell. Have a look at my video all about Apps Script triggers: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-KC7pBjD3GGw.html
great effort. But what if i want to export same cell value(which is changing every 15min) to another sheet every 1 hour time period(like a list of record ). solution will be highly appreciated.thanks.
You probably only wood need a trigger to run every 15 minutes that gets the new value and then write it to the target sheet. If you need help on this you could reach out to an Apps Script developer: www.upwork.com/hire/google-spreadsheet-freelancers/
@@saperis thanks for the reply. I already have a cell that is changing evry 15 min. But what I want is script for logging those values in an another sheet so that I can shedule trigger..
Hello! Your videos are always helpful to make everyone's task easier. However I have some problem when using copyto function to copy data (specific range) from sheet A to sheet B. Since the data from sheet A is integrated from Google Form input, there is a "timestamp" on the first row. Whenever i use copyto function to copy from sheet A to B (including the timestamp, the timestamp pasted on sheet B will be different from those that pasted on sheet A. Is there a better solution to this? Or is that timestamp cannot be pasted while maintaining its original value?
Thanks for the feedback! Interesting situation with the timestamps. I'm guessing the format of sheet B for those specific rows are different. That might cause the timestamp to be displayed incorrectly. Maybe you could use the copyFormatToRange method to deal with this problem. Here the official documentation. developers.google.com/apps-script/reference/spreadsheet/range#copyFormatToRange(Integer,Integer,Integer,Integer,Integer)
In that case you would have to add the data from source 1 and then from source 2. In between you make sure to select a different range write the data into. For example, if your range in the target was "A1" you have to make sure that when you copy the data from source to the range is "A2".
@@saperis oh ok thank you ☺️ i'll try, do you have any link or reference for this? ..may i ask what to do if i want to copy the data that doesn't include the title. For example in source A1 i want to copy the data that start in A4 and paste it to the target sheet that start in A2.
I don‘t think I understand the question. But maybe a general tip: you decide which ranges in the source sheet you want to copy and where to write them. It‘s all a matter of selecting the right ranges.
Is is possible to edit this so the data you want to copy moves to another spreadsheet (as in a spreadsheet that has a different url) from the spreadsheet that has the source data?
Yes, that's possible. To copy rows from one Sheets document to another you have to do two things: 1. Identify the target document. You could try something like this: let targetSheet = SpreadsheetApp.openById(id).getSheetByName(sheet); 2. You have to use the setValues() method as the copyTo() method only works within the same document.
@@saperis Is there a way to for the target sheet to keep any data that's copied from the source sheet and just puts in any new data below what's already there? Thanks for all your help!
You could try using the getCurrentCell() method to identify the currently selected cell. And then you can copy it or do any other manipulation. Here the Apps Script docs to see how to use the above mentioned method: developers.google.com/apps-script/reference/spreadsheet/sheet#getCurrentCell()
If you need to help someone else to edit a macro in Google Sheets they need to make you editor for that file. If you are asking us to help that we can't provide that service.
Hi , Can you copy a row with formulas from the Source sheet and dynamically paste it to a variable row within the range in the Target Sheet. Note: The target row is variable because data are constantly being added.
Hi Jason. I think it should work as long as the source range and the target range correspond. You'd probably would have to use something like .appendRow(). Keep in mind if you dynamically copy row by row and then also write row by row to the target that's going to make your script super slow. That's why it's better to copy everything and then write it all in one go.
Hi Jason. No, I don't have such a script myself. And as I said I suggest not writing line by line due to performance issue. Due to that it wouldn't make sense to publish a video with such a script as it's not best practice.
Sounds you need to run nested for loops and check cell by cell, pick a column, check every row (single column && single row = single cell), and set values within this condition whenever the string contains certain value.
@@saperis Thanks for answer my question Teacher. The same document, but save a row with 5 coluns (Date, Volume, breaks, %, labor work), under by the last row, when I execute the script another day...because a need create a data base.
In that case you would need to: 1. identify the last row with content in the target sheet 2. make that your starting point for the range to copy the rows
Hi, thanks for the great video. Is there any way to copy the values to the last columns that doesn't have data ? For example, each week I need to copy some data. W1 I want it to be added in column A, but then the next week I need the new data in column B, then Week 3 = column C, and so on. How can I "navigate" to that last column ? Thanks
It depends on your exact situation. A possibility is to access that exact column by using the getColumn() method. Here is the documentation about it: developers.google.com/apps-script/reference/spreadsheet/range#getColumn() Another way would be to get the data range and then offset 1 for the column next to the data range. Here is the documentation for getDataRange() : developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()
@@saperis btw do you have a video that kind of explains the general principles and main code lines to know for google apps script ? I kind of feel like I'm diving into something too complicated for me and I've missed the beginner class
I know it can be overwhelming when getting started. Here a video for beginners: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Nd3DV_heK2Q.html I also have an online course you can check out: courses.saperis.io
Hi Jonathan To copy rows from one Sheets document to another you have to do two things: 1. Identify the target document. You could try something like this: let targetSheet = SpreadsheetApp.openById(id).getSheetByName(sheet); 2. You have to use the setValues() method as the copyTo() method only works within the same document.
Hi Saperis. Great work. thanks! Wonder if you could give me a Quick hand. Here is the thing. From Sheet1 which receives data from a GG form, I want to copy the last row of data from column 2 to let’s say column 6. Go to Sheet2 and paste « Values » in the first available empty row of that sheet. I will run this script from a button. Hope you find the time to give me a quick heads up. Best. Jacques
Hi Jacques. Maybe have a look at using getLastRow() to identify which is the last row: developers.google.com/apps-script/reference/spreadsheet/spreadsheet?hl=en#getLastRow() This, combined with the video, should be a good starting point to create your script.
