ฝัง
- เผยแพร่เมื่อ 24 พ.ค. 2024
- Want to copy a row using Google Apps Script? Let's say you want to copy it from one sheet to another in the same Google Sheets document?
In this video tutorial I'll show you how you can get the rows in question and then copy them to another sheet.
0:00 How to copy a row to another Sheet with Google Apps Script
0:20 Use the setValues method
8:13 Use the copyTo method
10:41 setValues vs. copyTo
14:28 When to use the setValues method
#GoogleAppsScript #GoogleAppsScriptCopyRow #AppsScript #GoogleSheets #automation #ChanelGreco #saperis
-----
saperis - Helping you become a Google Workspace Pro!
Want to learn how to automate Google Sheets? Check out our online course: courses.saperis.io/
Sign up to our TH-cam channel for more Google Workspace, Gmail, Google Docs, Google Drive, etc. videos: / saperis
saperis website: saperis.io/
saperis Linkedin: / saperis
saperis Twitter: / saperis_io
![[LIVE] : ONE ลุมพินี 64 วันนี้!! คู่เอก "พาร์แฮม vs จอร์แดน"](http://i.ytimg.com/vi/yg2mS5U2aB8/mqdefault.jpg)
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!!
Happy to hear that! 😀
Your video, combined with another, has really helped me! Thank you so much!
Glad it helped!
Hi Saperis, Excellent clear presentation. You are a very talented educator. Cheers
Thank you! Cheers! 😀
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.
Nice work!
Thank you so much for a very simple tutorial.
You are welcome!
Great tutorial, thank you. Cleared up a misunderstanding on my part.
Great to hear! 😀
Lovely ❤. Thanks for sharing!
Thanks for watching!
Thank you for this video, we really require this types of Tutorials.
Happy you liked it. We have an entire playlist with more than 40 such videos: th-cam.com/play/PLNwCcck1-mNgASuUVvHeK282pvHzgWP-6.html
Thank you. It was really helpful
Happy to hear that! 😀
I honesty wish your were my programming teacher 😊 nice explanation
Had a question what if i don't want to select all the data on a sheet but just like 5 rows ?
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. 😀
Finally I know how to refference to shhet by its name. Thank You!
You're welcome 👍
Thank you Mam, this is exactly what I needed
Most welcome 😊
Thank you! 😊
You're welcome 😊
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 :)
Hi Emmanuel, nice to hear you see the potential of Google Sheets and Apps Script! 👍
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!
Thanks for adding your insight. 👍
Thanks!
Thank you for supporting this channel. 😀
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 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?
Thanks for the feedback.
When you copy the content of any given cell the link is also copied.
Hi Chanel,
Thanks for the great video! I would like to copy a column and paste in next available empty column in another sheet. plz suggest
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.
Thanks a lot
Most welcome 😀
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.
Thanks for watching. I don't offer any coding or mentoring services.
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)
@@saperis Thank you so much for getting back to me. I will give it a short.
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
Hey, I made also this kind of script but your script is easy one... Thanks for it and i will use with my projects. Again Thanks!
Great 👍 Check out my Google Sheets Automation online course for even more Google Apps Script: courses.saperis.io
@@saperis I'm little confuse, i will have the class on the video call or will get the recorded videos?
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.
@@saperis Cool:)... I will join very soon!
Awesome!
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
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)
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/
Thank you, it's working.
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
Since Google Print was discontinued there is no way of triggering anything to be printed through Google Apps Script.
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
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/
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}
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()
Hi thank you for the explanation! What if there's a condition to only setValue if a certain string is found in a certain column?
In that case you need a conditional before copying the rows. Kind of like
if (cell === "some string")
{ // the code to execute }
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.
Very nicely explained, Can we convert this code to Add-on or a menu that should be available to each google sheet as button or menu item?
Yes you can. Check this video where I add a custom menu to run the code: th-cam.com/video/hoCht3kzN7s/w-d-xo.html
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
So good. Can you write a script that filters first before copying as you mentioned in your video. Thx
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. 😀
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.
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 thanks a lot for your reply, I'll have a look !
@@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: th-cam.com/video/Nd3DV_heK2Q/w-d-xo.html
I also have an online course you can check out: courses.saperis.io
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)
@@saperis thanks
hi! thank you for the guide, hmmm,.. how to transpose row to column values?
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)
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 ?
Should be possible.
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: th-cam.com/video/KC7pBjD3GGw/w-d-xo.html
Hoping you can help:
I currently use importrange and have it skip empty rows. How do I do that as a script?
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
Is there a way to have the condition based on color of the row/cell (like text or fill color) rather than value of the cell?
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
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! 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. 😀
That's super thanks... but what if you want to copy from one Sheet doc to another Sheet doc?
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.
@@saperis That's great thanks!
You're welcome Jonathan. 😀
hi is there a way to do this base on the currently selected cell instead of a specific cell?
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()
@@saperis thanks really helpful!
Nicely done but one question, Anyway to show me how to do the same thing except with out loosing the formulas when copying over to another sheet
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
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!
@@crashcompilations2352 Yes if you set a trigger to run every 5 minutes it will keep adding the data from the source sheet in rows below
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!
I don't understand the question. But in general you decide on what your script should do.
Would like to ask, it's creating new rows every time there's a new response, how can it just overwrite the existing columns? Thank you.
You can define the range you want to write the data to. Check out this: developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getrangea1notation
Could you please help me to have Apps script to auto copy a row to another sheet when value on column B is changed to "Completed". Thank you.
In that case you should work with a conditional. Something like this:
if (cell === "Completed) {// then copy the row}
Hi, how would I copy a row from one sheet to another sheet's next available row, please?
You would have to identify the next available row on the target sheet. Maybe something like sheet.getLastRow() + 1.
hi there , we need your help to make our google spreadsheet with completed tick box can you help ?
I don't offer any coding services and I don't do any Apps Script videos anymore on this channel.
which book to recommend to learn excel 365?
I don't recommend Excel 365. I only recommend Google Sheets. That's why this channel is dedicated to Google Workspace.
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.
@@saperis Any scripts for the above request? Thanks a lot!!
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.
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.
Great point!
Hello Master, do you know how to copy data to another sheet but i want to exclude first row.
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
When copying my data ragne that contains formulas, what do I need to do in order for it to return the value from the formula?
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: th-cam.com/video/aYG55AlprHE/w-d-xo.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.
my last line of code seems to fail? The getRange returns as null. and I'm not sure what I'm doing wrong. It happened when trying both methods
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
what if there's 2 source sheet and one target, what part should i change when sending data (2 source sheet) to the target so that it will not overlap?
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.
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.
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.
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.
Yes, this is possible. But you would have to change the entire script. That's not something I can tell you how to do in a comment.
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
I want to copy a row to a different sheet depending on what sheet I choose in the dropdown list. Is this possible?
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: th-cam.com/video/KC7pBjD3GGw/w-d-xo.html
@@saperis thank you for the quick response. I will look at your video
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..
We don't program any script for customer projects. It's best you contact a developer to help you on this script.
@@saperis that's ok..any way I have sorted that out and it's working perfect..
if i have another sheet like target2, target3 etc. How to make script to choose between the target that i want to copy to?
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.
@@saperis so the target can't be decide by condition? if i have condition target2 how to input in the .getSheetByName(), is it manually?
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.
Can it be transferred in intervals?
Theoretically yes, if you were to add a a time-out.
developer.mozilla.org/en-US/docs/Web/API/setTimeout
@@saperis Super helpful
Hello Teacher !!! please, how can I capture a values for unique row and copy another sheet??
Hi Eduardo. With "another sheet" do you mean another Google Sheets document?
@@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 ,
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
th-cam.com/video/vbu_ujCLbEk/w-d-xo.html
th-cam.com/video/JKsZoGcYGFk/w-d-xo.html
th-cam.com/video/6Lu3HEuMOdE/w-d-xo.html
@@saperis Thanks I finally got it working...
please make advance videos ... please make videos on facebook IP and whatsapp
We only do videos on TH-cam that are beginner friendly. Other channels and educators do more advanced videos.
what if i want to copy where the target is the last empty row??
I'm sorry, I don't understand your question. You want to copy an empty row?
@@saperis thank you for your answer.. I want copy data day by day in one sheet.. I have solved the question by modified your script.. thanks
Hi, but this method will copy all rows and not only the single row as you write in the video title
Correct, I'm demonstrating how to copy multiple rows. If you only want to copy one row, change the source and target selection.
i need to help to eidt in macro
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.
@@saperis YOUR EMAIL PLEASE 😍
@@AmrEID-rd3rf We don't offer any coding or editing services.
Cant watch on mob. Due to background
Interesting. It works fine on my mobile.
What's wrong on your mobile?
How to copy entire column values from muliple sheet to a single sheet
I would get those data ranges from every separate sheet and then add it to the target sheet.
web app script :creates buttons to save data to source sheet and target sheet
Sure, you can achieve that with Apps Script
share the script?
You can copy the script by typing along. But I don't make the script available other than through the video.
If you like to copy again and need last row.
I'm sorry, I don't know if this is a question or a statement.
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 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.
@@saperis Thanks for taking the time to reply! It did the trick! All Good!! Merci!