Google Apps Script: Copy Rows To A New Sheet Based on Cell Value
Vložit
- čas přidán 29. 08. 2024
- In this video I show you 3 ways to move or copy a row from one Google Sheet to another Google Sheet based on a cell value in each row.
Method 1 - Copy and Paste
If this is a small, one time task you can always apply a filter to your master sheet and then copy and past the values to other sheets.
Method 2 - Google Query API
The second method is to use Google's Query API to dynamically pull the correct rows into a new sheet based on a cell value. The advantage with this method is that it will continue to capture and copy any new rows added to the master sheet. The downside is that the data on the new sheets are not permanent and will be lost if the master sheet is ever deleted or changed.
Method 3 - Google Apps Script
This is the preferred method if you want to copy the data over in a way that is permanent, but still quick and accurate.
You can review and reuse the script I demonstrate in this video using this Github Gist - gist.github.co...
Thanks for watching! And remember to work SMARTER, not HARDER!
For an extreme novice this video was the most helpful thing I could find! In this example using the apps script, what if you only wanted to copy over the revenue but have it copied to the "Eddie" or "Joseph" sheets based off the name in your master sheet?
Genius! Thank you so much for this. It took the manual process out of copying and pasting data from the master form data to 12 different spreadsheets. Super grateful!
Dude, you are the most "Understandable" code talker - and I've worked with the best of them in my industry. GREAT Job! Thank you!!
I appreciate that!
Thank you for this! Best explanation of moving data permanently to another location that i have found, and I’ve been searching for days!
I enjoy your videos. You do a very good job of explaining each step of your subject. I hope you continue to develop instructional videos for Apps Script
Thanks, will do!
This was super helpful for what I'm trying to achieve. I'm trying to build a 3 tab spreadsheet, where multiple rows on tab 2 are copied onto tab 3 based on data in tab 1. Wish me luck! Thanks again.
Hi Jessica, that sounds very doable. Good luck to you and let me know if I can help.
This helped me to solve a problem I spent a bunch of time on. I needed to copy/paste a cell value if a corresponding cell value was YES. This was in order to determine whether to include that row's data in a loop that replaces string values in a google docs template with pertinent row data. I then use the final google document as a weekly report for my distributor partners. Thanks!
Really Great!
Your way of teaching is really fantastic, This was very helpful. Thanks for the code.
Again thank you...
Thanks and welcome
Nicely done Joseph, clear and to the point. You have a new subscriber hope to see more videos!
Thanks for the kind words! And there is definitely more to come.
Ohmyword! This was fantastic! What great instruction. I love the way you slowly walk through the code and explain what each step did. (I took lots of notes). Again, thank you!
Glad it was helpful!
This such a great, well explained, easy to follow tutorial. Thanks for making my job a little easier.
Thank you for this. This example make easier to move data
Thank you so much for this great video, Your instructions are great!
Glad it was helpful!
Absolutely great lesson! simple and effectively done, subscribe and waiting more Joseph Palumbo!
Dude!! You're amazing!!! Thanks for share this! Hugs to you from Argentina!! 🍻
Excellent presentation. Your example shows copying a full row to a different sheet. What happens if you want to copy the row to another tab within the same sheet?
Hi Alan, it's essentially the same process except you set the target for the tab that you want using the .getSheetByName() function
this is a great video ! thank you! Can you help me with this: how to I automate the sheet so that if within a row, a column dropdown is marked "Yes". the entire row gets moved to a different sheet within the spreadsheet ?
easy to follow, thanks! please share more, looking forward to see more.
Thanks drva, working on a fun new video that should be published this weekend. Especially if you play Pokémon Go
Hy Joseph! Thanks soo mutch for your Guide!!! i was wondering if this method could be used with Many data Source, in brief, i have 3 sheets inside the same spreadsheet, i want to build a 4th Sheet where i can paste Rows from the previous 3 sheets. The rows i will copy/paste must be defined by a certain Value in a certain column of each sheet... basically i need to create a loop that when launched, will check the value presence in each row in each sheet and where it get a macth, copy the row in the 4th sheet
This is so helpful! Quick question, rather then sending the row to a new sheet, what if I wanted to send the row of data to another tab within the same sheet. What modifications would I need to make?
Rather than designate a new Spreadsheet as the destination, designate a new tab in the existing worksheet. This would look like `let targetSheet = ss.getSheetByName('Name of Target Sheet'); ' then use `targetSheet` as the destination variable like `target.getRange(1, targetSheetLastRow);`
Thank you for this. superb help for me. Great tutorial! almost the same thing that I needed. Is there a way that all the moved/copied data to be removed from the source?
I am hoping to get rows copied over to a new tab when a specific dropdown is selected from the second column. For example if details are entered name, website, contact, "furniture" (is selected from a drop down) I am wanting the row defined as "furniture" to copy over to a "furniture" tab. Would this script do the same thing? I went through and edited the script to run like is shown in your video and I ended up deleting it. So frustrated as this is way over my head so I don't want to start again without confirmation that this script will accomplish what I am hoping to accomplish above. I would appreciate your help!
@Joseph Thank you so much. This is really helpful especially to someone like me working as data analyst.
Thank you for a really easy-to-follow tutorial on how to get this done. It was my first time ever using any kind of Script, and even by modifying some values to fit my own customization, it all worked really well!
However, every time I want the items on my "main" sheet sorted, I need to click on the "Automation Tools" button manually. Also, when rows have already been sorted from the main sheet to the correct one, clicking on "Automation Tools" at any point to run the script again will duplicate the already-sorted rows on their corresponding sheet.
Is there any way to (1) automate the script (aka no need to click the button every time to run the script); (2) make sure items don't get duplicated on any given list when the main sheet gets sorted again?
???\
the video was very helpful in easy to understand
Thank You Joseph For such an amazing Idea
Using this method of app script as far as I have seen and worked we cannot transfer live data from the master sheet to another sheet! Can we do that so that every time a New row is added in the master sheet automatically data is fetched and copied to another sheet assigned as per cell without it being dynamic and other than the Google API Query Import Range...... Can You Please Help on that...
Hi Deep Chhabria, thanks for your comment. Yes, I would use the onEdit() function to watch for a certain column is edited and if a cell is that column is edited, then trigger the function to copy the row over. I have a video showing how to trigger an email using onEdit(). You can reference that and instead of triggering an email, you would trigger the function in this video.
Thank you so much sir, for sharing your wonderful knowledge with us, Can you please help me solve my problem as well in this sheet when you enter the new data into the master sheet, and run the function it copies the new data as well the old data with it, so it's very hard to filter out the data filled, as it gets duplicated in the target sheets. I would like to pay for your professional time, But I want to get this problem solved.
What I want to build is that whenever a use selects name from the dropdown, the data of the concerned person should go that person sheet, but only the updated data, not the previous data, I hope we can solve this. I would be waiting for your reply sir!
// Function to add a "Move "Yes" to another sheet" menu item to the UI
function onOpen(e) {
let ui = SpreadsheetApp.getUi(); // Get the UI object
ui.createMenu('🤖 Automation Tools') // Create a menu named "Automation Tools"
.addItem('Move "Yes" to another sheet', 'moveRows') // Add a menu item named "Move "Yes" to another sheet"
.addToUi(); // Add the menu to the UI
}
// Function to move rows from the current sheet to a target sheet
function moveRows() {
const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet
const sheet = ss.getActiveSheet(); // Get the current sheet
const targetSheet = SpreadsheetApp.openById('ENTER_GOOGLE_SHEET_ID').getSheetByName('Sheet1'); // Replace with the actual target sheet ID
const targetLastRow = targetSheet.getLastRow(); // Get the last row of the target sheet
let lastRow = sheet.getLastRow(); // Get the last row of the current sheet
let sortRange = sheet.getSheetValues(2, 1, lastRow, 4); // Get the values from the current sheet, starting from row 2 (excluding header) and including columns 1 to 4
Logger.log(lastRow); // Log the last row number for debugging purposes
let targetCounter = 1; // Initialize a counter for the target sheet
for (var i = 1; i
Joseph, would love to have you create a custom version of this for my business. Are you up for that?
Hi, happy to help. Please reach out to me directly so we can discuss the project.
This was something I was looking for. But I keep getting ReferenceError: name is not defined. I don't understand. I was thinking it Was you search in the first row The name is in the 6th row so I changed 1 to 6. Same message. need help
This is awesome. I would love to get a little help in getting mine to work. Is there any help available? One of my issues is a message. Don't have permission to run.
Thanks.
Great Video, Great description.
Really enjoyed the video. Thanks!
Great video! Almost what I needed. In my case instead of salesman I have a check box that is used to indicate if the data is to be copied to another googlesheet. Would it be possible to bypass creating the menu and when the checkbox is checked the row automatically is copied to the other googlesheet?
Thanks - this script was exactly what needed. Brilliant! So I made the changes and now I am stuck on "This project requires your permission to access your data." This is my first gscript so I don't how to get it to work. When I try things I get to the "$300 credit" message which has scared me off. I am not a business. I am just trying to write a personal script. Am I OK with "No Organization?". Is there a link somewhere whicn explains what I need to do? Thanks Peter
I take it all back - I bumbled around an found how the enviroment works - so just thank you for a great script
Excellent tuts, beautiful code!💖
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.
Great vid! Does the App Script method copy over only the data? or does it preserve the formatting as well? Is there a way to ensure that the copying preserves the exact formatting much like a manual copy-paste would?
Thank you for sharing. Question... how did you insert the icon?
is it possible in a query to grab from a row instead, and can that row be a variable that is named when a sheet is generated by a template? in other words, can i put the query in the template?
really what I need is just to copy from one tab to another and place at the bottom or last row. I can move to another tab and insert rows at the top when you start to build up it runs slow. That is why I would like to put in last row. any help
Is it possible to change the value in the "select *" section to be a value in a newly added cell? What I am trying to do is get responses from a google form into a sheet, then copy those responses to another sheet based on the names of the people submitting responses.
I wonder if I can make this work when I want to copy the last row from different tabs in the same workbook and paste it into a summary sheet within that workbook, all while avoiding duplicates as new data replaces the previous one.
// Function to add a "Move "Yes" to another sheet" menu item to the UI
function onOpen(e) {
let ui = SpreadsheetApp.getUi(); // Get the UI object
ui.createMenu('🤖 Automation Tools') // Create a menu named "Automation Tools"
.addItem('Move "Yes" to another sheet', 'moveRows') // Add a menu item named "Move "Yes" to another sheet"
.addToUi(); // Add the menu to the UI
}
// Function to move rows from the current sheet to a target sheet
function moveRows() {
const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet
const sheet = ss.getActiveSheet(); // Get the current sheet
const targetSheet = SpreadsheetApp.openById('ENTER_GOOGLE_SHEET_ID').getSheetByName('Sheet1'); // Replace with the actual target sheet ID
const targetLastRow = targetSheet.getLastRow(); // Get the last row of the target sheet
let lastRow = sheet.getLastRow(); // Get the last row of the current sheet
let sortRange = sheet.getSheetValues(2, 1, lastRow, 4); // Get the values from the current sheet, starting from row 2 (excluding header) and including columns 1 to 4
Logger.log(lastRow); // Log the last row number for debugging purposes
let targetCounter = 1; // Initialize a counter for the target sheet
for (var i = 1; i
Great video! Thanks!
Great tutorial! Thanks
Hi Joseph! Thank you for the code. It's really helpful.
One issue I am having is I need to get specific columns copied to another sheet for eg columns 3,6,8. How do we do that?
Also, It's copying the same data again & again. not sure where I did wrong with the loop. So if line 36 is already in there it's coping the all the rows again which creating duplicates on the other sheet.
Nice vid, easy to follow. Thank you so much
You're welcome!
@@jsphpalumbo Hey Joseph! Im trying to move the to data to a different tab within the same sheet.
I've replace the following in the 11 and 14 like you advise, it kept giving me error, " ReferenceError: eddieSheet is not defined" any idea?
const Name = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(' Name ')`
hi joseph! thank you so much! i learned so much from this. however, i was testing this out on my own data and it seems to work when data is copied to another sheet within the same workbook, but doesn't work when i use the openbyid function. I might be missing something. is it possible that the apps script should be allowed to make changes on the destination workbook?
thanks!
thanks for the tutorial, how do you move to another worksheet? did try with oneedit function but failed
Hello Joseph. This was very helpful. Thanks for the code. Do you think you can help with the following task: Copy ONLY the last populated row(10 columns wide) from sheet X, and paste to the first free(NOT populated row), again 10 columns wide, to sheet Y.
Hi there Joseph great video subscribed already 😉 . Is there a way to make it automatic as soon as data land on master page. I made a webapp that fill the informations on the master sheet. Thank you in advance for your reply.
Hi Joseph, when I run this script multiple times the information from the master sheet appends each time to the Eddie and Joseph Sheets is there a way to only copy rather than to move or as my Master Sheet continues to grow daily is there a way to only copy over new data for each person. Love your video thank you in advance if you can offer some assistance with this.
Thank you for your very interesting video and your code that works very well. I have a limitation on large datasets, I have to delete all the rows each time I run the script again. Couldn't we add a function to copy only the new rows that appeared in the Master Sheet document?
Thanks for the comment Geoffrey. I think there are 2 ways to handle this requirement.
First, if this an option, you can clear the row from the master sheet after it is copied by adding a line like `sheet.getRange([range]).clear()` to the end of the loop. This would clear all the contents from the row after it's been copied.
Second, if you have dates in a column, you can create logic nested in the loop that says "if date is greater than 6/1/2022, then copy it to the appropriate sheet". That would ensure only rows added after a specified date would be copied over.
@@jsphpalumbo Hello, Great Video btw, do you have a code regarding on your Second option? two based criteria? a date and a cell value?
What if specific row (2, 1, lastRow, 4)? and im using dropdown by name its still the same code?
If you want to copy a specific row or set of rows to a new sheet, I would create a new function and hardcode the specific rows to be copied (e.g. sheet.getRange(2,1,lastRow,4))
Hey, there! thanks for the video first of all. it is usefull so much to me. However, i still have some problem with google sheets app script. I am looking for a script that pull the value according to several conditions. For example , I have about more than 350 list of people with detailed info about them and those lists should be imported to another spreadsheet under certain condition as a value. is there any way to do so? if you can help please, help me! thanks in advance.
Hi there, sorry for the late reply. Yes, this is very easy. All you have to do is add more conditional IF statements to your script to check additional values.
Thank so much for this Class.. If I may, how do we create a protection against duplicated entries ?
Hi Rafael. my first thought to protect against duplicate entries is to store values copied (such as a name or id number) into an array and then checking against the array see if a new value is a duplicate.
So it would look something like this
var currentValues = ["one", "two", "three"];
var newValue = "one";
if ( currentValues.indexOf(newValue) > 0 ) { DO NOT COPY}
Let me know if that helps
@@jsphpalumbo I guess a video on this particular thing should help and give more clarity as many of us have similar doubt.
I am facing the similar issue of data duplication everytime the script is run. I've also set onEdit() which makes it all the more tough.
Hi @@jsphpalumbo , do you happen to have a full code that would protect against duplicate entries? I tried implementing the code below but I am having troubles.
var currentValues = ["one", "two", "three"];
var newValue = "one";
if ( currentValues.indexOf(newValue) > 0 ) { DO NOT COPY}
// Function to add a "Move "Yes" to another sheet" menu item to the UI
function onOpen(e) {
let ui = SpreadsheetApp.getUi(); // Get the UI object
ui.createMenu('🤖 Automation Tools') // Create a menu named "Automation Tools"
.addItem('Move "Yes" to another sheet', 'moveRows') // Add a menu item named "Move "Yes" to another sheet"
.addToUi(); // Add the menu to the UI
}
// Function to move rows from the current sheet to a target sheet
function moveRows() {
const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet
const sheet = ss.getActiveSheet(); // Get the current sheet
const targetSheet = SpreadsheetApp.openById('ENTER_GOOGLE_SHEET_ID').getSheetByName('Sheet1'); // Replace with the actual target sheet ID
const targetLastRow = targetSheet.getLastRow(); // Get the last row of the target sheet
let lastRow = sheet.getLastRow(); // Get the last row of the current sheet
let sortRange = sheet.getSheetValues(2, 1, lastRow, 4); // Get the values from the current sheet, starting from row 2 (excluding header) and including columns 1 to 4
Logger.log(lastRow); // Log the last row number for debugging purposes
let targetCounter = 1; // Initialize a counter for the target sheet
for (var i = 1; i
Nice one!
I was wondering if there is a way to export the data to a new sheet. I have a sheet of data from various regions, the sales reps are defined for each region. I would like to select the region, and have the data export to a new sheet. In the new sheet I would like to have it sort the imported data by sales rep with a new tab for each sales rep with their own corresponding data only. Thoughts?
Hi, thanks for the question. I think the best way to accomplish this is to use QUERY with IMPORTRANGE, like this =QUERY(IMPORTRANGE(url, range_string), "select [columns] where [region = A]", 0)
Documentation is pretty good for both of those functions, but let me know if you need any help.
Hello Sir after Using this getting this "TypeError: Cannot read properties of null (reading 'getLastRow')" error how to solve it
Hi, how to do if i just want copy a certain row like, 1-15 row? pr just row number 2 only. Hope u can help me , thanks
Hi Nurul, if you want to copy a range of row, like 1-15, then I would use a for loop like this:
for (var i = 1; i
@@jsphpalumbo oh wow thanks! Is it same if i want to copy a certain row like B18 to B44, i need to do "B18:B44" or else? Bcs i did try to do this like this -- datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("B18:B44", ).getValue()); but in my form just shown B18 row only
Hey! Thanks much for this great video! Can this be done to move from one tab to another on the same sheet? I want to move Daily Data!A7:M29 to Archive!A1:M23 and don't want to do it one row at a time. Is this possible to be done this way or does it need to be a script? I am really new to trying to make things not preformatted.
Hi Tabetha, yes, this code can easily be updated to move from one tab to another in the same worksheet. It's just a matter of changing lines 11 and 14 in the Gist file I linked to in the description. Rather than create a range on another worksheet, you would create a range on another tab with something like
`const archive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Archive')`
and then define what range you want to set the values to like I did in lines 34 and 40.
Let me know if that helps.
@@jsphpalumbo Thanks so much. I was hoping to figure out how to make a checkbox move a section to another tab on the same sheet via code like this:
function onEdit(e) {
const r = e.range;
if (src.getName() != "Daily Data" || r.columnStart != 3 || r.rowStart == 1) return;
const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Archive");
src.getRange(r.rowStart,1,1,3).moveTo(dest.getRange(dest.getLastRow()+1,1,1,3));
src.deleteRow(r.rowStart);
}
as an archive function but I cant quite get it down. Which automated like this is what I wanted but I am down to give this a try! Your instructions are great!
@@jsphpalumbo I replaced the `const archive = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Archive')` with line 11 and 14 and have two tabs in the same sheet, one named Eddie and the other is Joseph and this is what I got.
Syntax error: SyntaxError: Identifier 'archive' has already been declared line: 14 file: Code.gs
Please help!
¡Gracias!
¡De nada!
can you make a video on how to add sequential id using app scripts?
Can you be more specific as to what you mean by "sequential id"?
Hello Joseph, I have got this error please help
TypeError: Cannot read properties of null (reading 'getLastRow')
Can you show me the line of code you'r using to get the last row?
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 and I even tried to change a few things, but every time I run the script it piles up the result in one sheet, so even if I have 4 rows, it multiples them every time I click on "moveRows"
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!
Hey there, I've done this before, and it can be tricky. What works for me to is write separate scripts for each of the sheets you want to import from (import1.gs, import2.gs, import3.gs) and then write a final scrip that calls each one in the proper order. The final script should also have a section at the bottom that does the necessary cleanup and ordering that you explained, as well.
I know it's difficult to talk through this in the comments, but I hope this points you in the right direction. Please feel free to reply back with any further questions. Or if this is for a business, you can always enlist my consulting company to work through the implementation for you.
Hi, i left you a comment on a Github, i would be really grateful if i can contact you and show you what i have been working on and where i am stuck.