Move Rows of Data in Google Sheets - Apps Script Tutorial
ฝัง
- เผยแพร่เมื่อ 26 ก.ย. 2024
- In this video, I quickly demonstrate and explain how to put together a Google Sheet and the App Script to be able to quickly move rows of data between tabs using a dropdown status.
If you want to make a copy of the Google Sheet and script I used in the video, check it out here: bit.ly/3MAGKqI
If you just want to move the data up or down to a different section in the sheet, check out this video: • Move Row Between Secti...
If you're ready to take it to the next level, check out Part 2 here: • Move Rows of Data in G...
If you need to move both formulas and data, check out this video: • Video
If you're looking at moving multiple rows of data at once, check out this video: • Move Multiple Rows of ...
If you want to move a row of data to a completely different spreadsheet, check out this video: • Move A Row to Differen...
![Move Row Between Sections Automatically in Google Sheets - Google Apps Script Tutorial [SheetsNinja]](http://i.ytimg.com/vi/HJlpzGvzw30/mqdefault.jpg)
![[🔴LIVE] งานประกาศรางวัล MAYA TV AWARDS 2024 : มายามหาชน](http://i.ytimg.com/vi/nwmWYXXVwBY/mqdefault.jpg)
+ 1 like and + 1 subscriber. You made everything look so simple. Well done!
Awesome, thank you!
This is great, super close to what I was trying to do. Now to brush up so I can make the edits I need, haha.
Awesome!
Thanks for this! It's working except that it is not moving the dropdown selection. I click the dropdown in column A, select the appropriate category (that corresponds exactly to a tab), it moves the row to that tab but the drop down selection is returned to no selection. All the other info is intact. I saw a comment about the top row being frozen causing problems so I unfroze my top row but it is still not moving the drop down selection. Any advice would be welcomed and appreciated!
I don't know if you have a line of code in your script that's something like:
data[0][0] = ''
But if you do, you will want to delete that line because it would be clearing the value out of column A in the data getting moved.
Otherwise, it should be working if your script looks like what we built in the video.
@@SheetsNinja I had something like that, deleted it and now it works. Amazing how quickly you responded! Truly impressed but the content and support you are providing.
As for that piece of code, I copied and pasted the entire code you linked to in the description. Just double checked and confirmed that line is in your code as well. Guessing you know that but just in case thought I would share that info.
Thanks again!
You're such a big help! 🤩 Quick question, why other dropdowns and checkboxes in other columns don't copy as is? It changes into the "word without dropdown" and "TRUE."
So it's just copying the text. You can either copy the data validations as well (a little more complicated), or you can add the dropdowns and checkbox validation on the target sheets and then it will fill in that way.
So helpful and easy to follow--thanks.
Awesome, so glad this was helpful for you!
Hi, it works, but it does not transfer the links as well. For ex: each person's name is linked to a *SHEET* and any other contact details including the company website link
Thank you, this is very helpful! You have a new subscriber.
One question, if I want to clear the transferred row instead of delete it, what is the proper modification to the code?
Copy the row of code that ends with ".getValues()", paste it below, and then replace ".getValues()" on that new line with ".clearContent()", then make sure to delete the row with "sheet.deleteRow()".
Hey Ninja, I'm trying to set up multiple charts on the same sheet side by side with their own drop downs. I've figured out how to change the script to grab just the info from the selected chart (For instance if I have 3 charts side by side and I select a drop down for the middle one) however it copies it onto the destination sheet in the first available slots (The left chart instead of staying in the the middle chart). I also can not get just the info from one chart to delete after moving, it still wants to delete the entire row. Please help if you can! Much appreciated thank you!
HUGE time saver for work!
Awesome, glad this was helpful!
Thanks.. i was looking for that solution a long time .. great job👏👏😃😅
Happy to help
Thanks so much for putting this together!! Can you help with what I would need in order to make this work where the row only moves if two conditions are met? I don't want a row to move to a different sheet unless two or three cells have specific values.
I just added a video covering using multiple criteria: th-cam.com/video/KekdeXn8P5Y/w-d-xo.html
L-O-V-E THIS! Thank you so much!!!
Awesome, so glad this was helpful! Check out our other videos for more tutorials on all kinds of stuff!
Hi Ninja,
This worked great for me. I need to add something into this, do you mind helping me?
I want the data to remain on the original sheet when moving it over to another tab. But when on the tab, if it then moved to another tab, I want it to be removed from there.
So When I move the status to "Prospect" it is copied there. But when I move the status from Prospect to "Sales" it remains in both prospect and sales. I want it to be removed from prospect, but remain always in "Lead"
Thanks.
You just have to wrap the line "sheet.deleteRow(row)" like this:
if (source.getName() == "Prospect") {
sheet.deleteRow(row);
}
That will only delete the data when the data is getting moved from Prospect to another tab. You can wrap any other tab-specific logic in an if statement like this.
This is exactly what i needed it for, thank god
Awesome, so glad it was helpful!
@@SheetsNinja
What can i add to the code to make the script ignore Sheet 1 for example? This is the last bit i need to make my pipeline work, because this is my master sheet where all the data needs to go
I have other videos linked in the description that go deeper into this and give examples, but essentially you wrap the rest of the function in an if condition that excludes the first tab.@@SJUS-uw8bg
Not working with my sheet can u help me out
There are a couple of common errors;
1. Are you referring to the correct column in the script (where it says "if (col == 1..."). If you are using column E for the status selection instead of A, you would want that to be col == 5 instead.
2. Do the tab names match the drop-down selections? If not you will need to map that in the script, so make sure you check out the next video (linked in the description above)
3. Did you authorize the script? If you have not authorized the script it will not run.
@@SheetsNinja I named the column as 7 but I don't want drop-down I want it to be entered manually.... Wouldn't that work if I enter manually instead of drop-down... I've authorised and ran too but didn't work
The problem with entering manually is if what you enter doesn't match the tab name or your reference then it will have an error. An error here can be as simple as an extra space in what you type in.
Hi, I use this code but it doesn’t keep the format between the tabs. Is there a code line that I should add besides “Rich Text Value” ex, it copies hyperlink but it has black letters instead of the blue letters underlined
You can copy formats as well, but the method is a lot more complicated.
It's something like:
sheet.getRange(row,col,numRows,numCols).copyFormatToRange(targetSheetId, startRow,endRow,startCol,endRow)
I've been trying this formula but when I execute it, it deletes both the correct row and the one underneath it. I have the exact script you used pasted into Apps Script, so not sure what the problem is
Is there a way I can add in two scripts that is looking at criteria in two different columns? Meaning I move row 1 to its selected tab first. Then based off of the criteria I select in Column 2 it'll move to its corresponding tab as well.
You can nest if statements if you want multiple criteria... something like this:
if (val == 'tab name') {
let column2Text = sheet.getRange(row,2).getValue();
let data = sheet.getRange(row,1,1,10).getValues();
let targetTab = ss.getSheetByName(val);
targetTab.appendRow(data[0]);
if (column2Text == 'tab 2 name') {
// copy row to other tab as well
let sheet2 = ss.getSheetByName("tab2");
sheet2.appendRow(data[0]);
}
}
If you notice, the ending bracket for the first if statement is OUTSIDE of the second if statement. This is how you nest them, which allows you to do something if the first condition is true, but have a nested action that only applies if something else if the case.
Hi, any help pls! I tried using the getRichTextValues as I have data with hyperlink but the number values/data within the row (for example, dates and simple 1,2,3, numbers) disappear. What should I do?
So you can use the same logic as this video: th-cam.com/video/E1w7VnFWsZA/w-d-xo.html
Just swap out where it uses .getFormulas() and .setFormulas() for .getRichTextValues() and .setRichTextValues()
Thank you so much for this video. I do have a question - how will I use this App script on a google sheet that have protected ranges in it? Will it still work?
If there are protected ranges that are warning only, this will still work. If it prevents people from editing, then you will need to make sure you use an installed trigger and that the user who installs the trigger has edit access everywhere the script will need access.
Would You be able to help me with a similar project? I am trying to use your script and getting an error with the range. I added tow additional sheets to the workbook but they will not cycle, please help if you can!
So if you have the same columns on the different sheets, just make sure the drop-down values are the same as the tab names, and then everything should work the same.
Hi I have done this but for some reason anytime I do any edit and change the status drop down to match what another tab is, there is no change and the script doesn't even run. Please help me understand what might be wrong? Thanks!
If you have not authorized the script, that would be the #1 reason why this wouldn't work. Other than that, what you would want to check is when you pull up the Script Editor, on the left-hand side, check the icon that looks like three lines and an arrow, labeled "Executions", and see if there are any executions happening for the script, and if they completed successfully or if there are errors.
@@SheetsNinja Got it thanks! I will look into those.
Hi, it works but the problem is it does not transfer the links as well. For ex: each person name is linked to a folder and any other contact details including the company website link
Are you talking about smart chips? If so, Google has not updated apps script to be able to pull data from smart chips.
Otherwise if you're talking about links that you added, you can switch out .getValues() for .getRichTextValues(), so something like this:
**replace the .getValues() and .appendRow() lines with these:
let data = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getRichTextValues();
targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, data[0].length).setRichTextValues(data);
Thankyou so so so muchh...for the video.
+1 subscriber.
You have our heart🎉❤
Awesome! So glad this video was helpful!
Incredibly helpful! Similar to some comments here, I followed your exact method, deleted the data[0][0] line, my data is moved to the right tab but all my drop-down menus have been reset as just text. So I'm unable to select the next step after "prospects" because it's now just text. Can you please advise?
So what you'll want to do is set up the drop-down menus in any other tabs where you will be moving the data and then you will be ready to roll.
@@SheetsNinja I did just that and it worked. Duplicated the format of the first tab across all tabs. Thanks a lot!
Thank you so much for this video. Everything works perfect for me, however, I would like the rows with a status change added to the top of the new sheet rather than the bottom. Is this possible?
I am also wondering this!
So here's the updated code that will add the new rows to the top instead of the bottom:
/*
@OnlyCurrentDoc
*/
function onEdit(e){
let range = e.range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();
if (col == 1 && val != ''){
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(source.getName());
let targetSheet = ss.getSheetByName(val);
let data = sheet.getRange(row,1,1,sheet.getLastColumn()).getValues();
targetSheet.insertRowBefore(2);
targetSheet.getRange(2,1,1,data[0].length).setValues(data);
sheet.deleteRow(row);
}
}
Just added a comment with the updated code to add new rows to the top instead of the bottom.
thank you!!!@@SheetsNinja
Thanks for sharing this solution. I tried it and it worked for me, the only thing is every time I selected the tab name from the status column, the row moved to the proper tab, but it was not in row 1 instead it was in the last row, row 1000. How can I changed that?
Usually that is because you have check boxes in your target tabs. The simplest solution is to delete the checkboxes and have the script add them.
Say the checkboxes are in column E, you can add this code to the script after it pastes the data:
targetSheet.getRange(targetSheet.getLastRow(),5).insert checkboxes()
Extremely helpful. What additions to the script would be needed to have formulas in the row be able to mover over as well?
So you can move formulas with the .getFormulas() method and .setFormulas() method, and you use it just like .getValues() and .setValues()
@@SheetsNinja I appreciate the quick response. I'm entirely new to all of this so I'm unsure how to implement .getFormulas() or .setFormulas() within the script you have written in the video.
I created a new video showing how to copy formulas: th-cam.com/video/i2VgEclS4ps/w-d-xo.html
Very helpful Ninja. Thanks. I just don't know why when I click on the drop-down, the row moves perfectly but when another user does it, The row info is copied but it does not disappear from the main sheet. Any suggestions?
It sounds like the other user may not have restricted permissions on the main sheet. If you do not want to modify the permissions, you may want to do an installed onEdit trigger instead. (Change the function name from "function onEdit(e) {" to something like "function checkMySheet(e) {"
And then on the left hand side, you will see a clock icon, click on that, then the plus sign on the lower right on the new screen, then on the popup, you'll want to select the new function name on the first drop-down, then for the the next few fields, you'll want this:
Head (for deployment)
Spreadsheet
On edit
Then click save and that new trigger will run the same script for everyone but with your permissions so it will work even if you are restricting access for certain users.
@@SheetsNinja is kind of strange 'cause the other users are "editors" too.
@@SheetsNinja I started from scratch and did what you suggested (installed onEdit trigger) It works like a charm.. Ninja is the best! thanks again.
Good day
firstly, thanks for your assistance
I'm at a roadblock how do I allow my google account to verify the app
Here is a quick video explaining how script authorization works: th-cam.com/video/t0yUc11aFeU/w-d-xo.html
If that method does not work and you are using Google Workspace, 3rd party scripts may be blocked on your domain. If so, you will need to contact your administrator/IT person to see if that can be modified.
Thanks a lot for the super helpful video! I do have a question though. Is it possible to move a row from one group to another on the same tab after a box is checked?
That is definitely possible to do but can be tricky! For example, if you have a section for Pending, Sold, and Cancelled on that tab, I would put the section header text for those sections in column A for example, and have the rest of the the data starting in column B. Then assuming the status drop-down is on column B, I'd have the script then pull the values from column A and use index of to figure out what row those section headers are in, and then use .insertRow() method to add a new row in that section and then paste the data.
Depending on your comfort level with scripts, that may sound intimidating, but I did add your project to my list of videos to make in the future.
I just recorded that video today! th-cam.com/video/HJlpzGvzw30/w-d-xo.html
Thank you so much for this! I've been searching everywhere for this!
How do you move it back and forth between the sheets, once its been moved?
The script works perfectly to move it from main sheet to the other sheets but I can't move it back or between the sheets
I tried one of the other scripts from another comment but it doesn't seem to be working and I'm not sure where to add it in to the original script (i just put it at the bottom)
My Original Tab is "PENDING ORDERS" and the drop down is "NOT STARTED"
" if (val == "NOT STARTED") {
let targetSheet = ss.getSheetByName('PENDING ORDERS");
targetSheet.appendRow(data[0]); "
So the easiest way is to have the drop-down match the tab name and then you don't have to add anything extra. Otherwise you will want to add this part in before the closing bracket.
This has worked great, my only struggle is that when i move the row it leaves behind the notes that where in a cell and the links linked to a work or number set
So before you delete the row, you can copy the .getValues() and .setValues() rows and change those additional rows to .getNotes() and .setNotes()
If you're using smart chips, unfortunately I do not think apps script supports grabbing the data from those yet, but I'm expecting they will soon.
Nice Video, the only issue i am facing is the status is not getting send to the new sheet. all data is being moved except the status. please suggest what can be the possible issue.
Just remove this line from the code:
data[0][0] = ''
Hi buddy, the sheet was working fine but now I am facing issue that the data is getting deleted after shifting to done sheet from my id. But when my team is doing done, it is being shifted to the done sheet but is not being deleted from master sheet. Please help!!!
thanks for the tutorial, how do you move to another worksheet? did try with oneedit(e) function but failed
So make sure you do not have @OnlyCurrentDoc in your script, secondly, for the target sheet, you need to do
let targetSS = SpreadsheetApp.OpenById("target_sheet_id");
let targetSheet = targetSS.getSheetByName('targetTab');
And then you can use targetSheet for the .setValues() method
Hi Sheets Ninja!! I managed to follow your tutorial and it works nicely! Thank you so much for the useful information. I have a question, completed projects are being moved from one tab to another when the status changes to 'completed', but can I make it so that once they've been moved to the other tab, I can move them back to the previous one using the same column? But by selecting a different state? This is the current script I am using, but is only working for moving to the first tab to the second one, not the other way when I need to. Thank you in advance! :)
function onEdit(e){
let range = e.range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();
if (col == 1 && val != ''){
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(source.getName());
let targetSheet = ss.getSheetByName(val);
let data = sheet.getRange(row,1,1,sheet.getLastColumn()).getValues();
data[0][0] = '';
targetSheet.appendRow(data[0]);
let formulas = sheet.getRange(row,1,1,sheet.getLastColumn()).getFormulasR1C1();
for (i=0;i
So if you have the name of the original tab in the dropdown then you can use the script as is, otherwise you will want to add a section in there to check if it's that tab name.
For example, if the original tab is called "Active Projects" and you have a dropdown selection called "Back to Active" then you could do something like this:
if (val == "Back to Active") {
let targetSheet = ss.getSheetByName('Active Projects");
targetSheet.appendRow(data[0]);
}
How do you copy a row to a different tab instead of deleting the information from the original sheet/tab? I.e.: Master sheet has names/demographics, Column has dropdown categories of sports, Sport is selected and copied to Tab with sport of same name WITHOUT deleting from master list. These tabs will be protected to certain viewers. Is it as simple as "copyRow" instead of "deleteRow"?
Just delete the row of the script with .deleteRow() and it will copy instead of move.
Love it I used it on my google sheet but i have a form linked and google form overrides the script. Any suggestions?
So you can't copy data into a tab where Google Form is dumping responses. Best practice is to use another tab for your process.
Thank you for your help. one more question. my dropdown is in column "C". So i changed the script to: if (col == 3 && val != ''){
( My sheet columns are: "A"= Room#, "B"= Date, and "C"= Satus ) When applying the script everything is moved except values in column "A". The column is empty. Is there a way to fix this issue?
You'll just need to delete one row in the code, which looks like this:
data[0][0] = '';
@@SheetsNinja Thank you, it works perfectly.
Hey,
Thanks for this. I have a sheet that in the status columnin sequence moves through 4 tabs and then it’s finished with. However on tab 3 it would depend on status and another answer on column F drop down.
So if Status = complete and column F was blank it goes to the complete tab.
If column F Said YES it would copy the row to 2x other tabs but also delete from the current tab
I just created a new video showing how to use multiple criteria: th-cam.com/video/KekdeXn8P5Y/w-d-xo.html
Combined with my part 2 to this video, that will explain all the concepts needed to complete your project: th-cam.com/video/0Up8bqnfttA/w-d-xo.html
Hi! I messaged you on Facebook but not sure if you check FB, so I’ll try my luck here!
I watched your videos on youtube about how to make a script that transfers a row from one sheet to another with a drop-down selection. i have struggled a little bit because i need a code where the row is transferred to another sheet when for example in column L a name sheet is selected (i have name sheets and weekday sheets) so it would only transfer to the name sheet selected, but it would only get copied to the sheet selected. but when in column P a weekday is selected, i want the row to be transferred to the sheet selected and be deleted from the original sheet. I also have a formula in column E, that has a google maps link, where every time an address is typed in column D, the link for the google maps for that address will show up in column E. So far Ive made it work, but theres a glitch, everytime i select something in column L, it will transfer to the sheet selected but it would show up twice or more times, when i select a sheet in column P, it transfers the row selected and the next one. I havent found a way to fix this and don't even know if its possible. Would you be able to help me? I would appreciate it so much!!!
Basically I have two dropdown columns on each weekday sheet
Hey there, from what it sounds like, you may not have restrictive enough if statements, or you may have two onEdit triggers. This can happen if you either add a trigger multiple times or your main script is onEdit(e) and then you also add a trigger for that function.
If you're in the script editor, double-check your function name, and then click on the clock icon on the left-hand side, and see if there are any triggers listed. If there is any triggers listed and your function name in the script is onEdit, then you should remove any triggers tied to it. Otherwise, if your function name is something like checkMySheet(), then just ensure there is only one listed.
If this isn't your issue, then my guess would be that your if conditions to determine where the row goes both allow the row to go through, so you need to make them restrictive enough that only one will be true... e.g.:
// name sheet
if (col == 12 && val != ''){
// rest of name sheet part of the function
}
// weekday sheet
if (col == 16 && val != '') {
// rest of the weekday target part of the function
}
This is awesome, thanks!!!!
Just doing my job of spreading awesomeness! Stay tuned for more!
Hi. I tried it but it is not moving Completed to the Completed tab, here is the code I used, do I have an error?
function onEdit(e){
let range = e.range();
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();
if (col == 1 && val != ''){
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(source.getName());
let targetSheet = ss.getSheetByName(val);
let data = sheet.getRange(row,1,1,sheet.getLastColumn()).getValues();
targetSheet.appendRow(data[0]);
sheet.deleteRow(row);
}
}
Two possibilities... 1) the script is not authorized (click "Run" in the script editor and follow that process (make sure to click "Advanced" on the safety warning).
2) the drop-down value does not match the tab name. Sometimes you might have an extra space at the beginning of end of either the drop-down value or the tab name, so double-check those.
This would be a huge help for my workflow but after following all the steps for some reason it’s not working when I change the status drop down. My drop-down and tab names are different but since there is not specifics verbiage about “prospects” etc I figured as long as the drop down names correspond to the tab names It would work. Please let me know if you have any ideas why this might not be working for me
So the simplest solution is to make the dropdowns match the tab names exactly. If that's not possible, then you would have to add more in the script to handle the difference (e.g. "if (val == 'Prospects') { let tab = ss.getSheetByName('Prospects Tab'); } )
awesome video! just wondering what if my column b is a hyperlink? would that affect the code?
You'll just need to swap out .getValues() for .getRichTextValues() and .setValues(data) for .setRichTextValues(data).
Yeh, couldn't get this to work. Tried everything but the line of data just wouldn't transfer onto the other sheet. Thanks for the tutorial though. Much appreciated.
Typically the issues with the simplest version here is that the tab names don't match exactly (sometimes tab names have a space at the beginning or end, if you try renaming it see if there is a blank space), otherwise if you have dropdowns with different values on the other tab, it may be returning an error if you're trying to move data that doesn't match the drop-down options. If you are using appendRow(data[0]) you need to make sure you use the [0], and finally, if you haven't authorized the script, it won't work either.
@@SheetsNinja Thanks for your reply, greatly appreciated! I have double checked the naming of the sheets and trigger word but still cant seem to get it to work. All I was trying to do is create a simple 1 column drop down with the word "Archive" in it to move that line of information into an Archive tab sheet. The only difference I can see between your coding and mine is that where the drop down lives is on column 9 instead of 1. So the source sheet is Form responses 1 and the Archive sheet is simply Archive. I select Archive from a dropdown on the line of information I want to send to the Archive sheet and it deletes it from Form responses 1 sheet and places it in the Archive sheet.
I have followed your coding with great appreciation and given authorisation, double and triple checked the spelling and made sure there were no spaces at the beginning, but I still cant seem to get it to work.
/*
@OnlyCurrentDoc
*/
function onEdit(e){
let range = e.range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActivesheet();
if (col == 1 && val !=''){
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetbyName(source.getName());
let targetSheet = ss.getSheetByName(val);
let data = sheet.getRange(row,1,9,sheet.getLastColumn()).getValues();
targetSheet.appendRow(data[0]);
sheet.deleteRow(row);
}
}
Am I missing something?
Hey Thank you for this amazing tutorial, I am using the Query (importrange) function to acquire data on my sheet, now when i am using this script, it is making a copy of the row instead of moving it. Is there anything that can be done to rectify that?
So you can't move data when you're using importrange, because the data is just being displayed in those cells. You could potentially use a filter formula to exclude any data from showing up there that you're copied to another tab.
Hi!! Thank you so much, I have a question, I was able to copy and paste the rows into another sheet, but the hyperlinks I had didn't get copied, only the names did. Do you know how I can make sure the hyperlink gets copied and pasted as well?
replace the .getValues() and .appendRow() lines with these:
let data = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getRichTextValues();
targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, data[0].length).setRichTextValues(data);
targetSheet.getRange(targetSheet.getLastRow(), 1).clearContent();
@@SheetsNinja It isn't working... not sure if I am copying wrong!
function onEdit(e){
let range = e.range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();
if (col == 1 && val != ''){
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(source.getName());
let targetSheet = ss.getSheetByName(val);
let data = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getRichTextValues();
targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, data[0].length).setRichTextValues(data);
targetSheet.getRange(targetSheet.getLastRow(), 1).clearContent();
sheet.deleteRow(row);
}
}
That should do it if you're doing direct links. If you're doing a hyperlink formula (e.g. =hyperlink("www.google.com","Google"), you'll want to follow the modifications from this video: th-cam.com/video/E1w7VnFWsZA/w-d-xo.html
@@SheetsNinja the appscript in the youtube you shared is still not copying over the URL in the hyperlinked text from one sheet to the other
@@YOLOPRODUCTI0NS So I proposed two different solutions depending on what you're doing. If you're doing formulas (e.g. =hyperlink() , you need to follow the instructions in this video to merge the data and the formulas: th-cam.com/video/E1w7VnFWsZA/w-d-xo.html
Otherwise if you're using the link shortcut direclty in the cell, then you need to use the .getRichTextValues() as suggested in the comment above.
How do I in another scenario, after ticking a check box (Example row 20) then it will proceed to move up to row 2?
The scenario is that I will have lets say 30 row of random data, and a check box on each row, after I check, it will rearrange to the top.
So if I check row 20 now, it goes to row 2, I check row 15, it goes to row 3, I check row 21, it goes to row 4 and so forth?
Cause I want to keep track in order the task I did after checking everything.
Here is the apps script method: developers.google.com/apps-script/reference/spreadsheet/sheet#moverowsrowspec,-destinationindex
So where they do
.getRange("A2:A3"),
you could do
.getRange(row,column) and then set the new index to 2.
@@SheetsNinja Ty so much!
Hello and thank you for sharing th knowledge. If I May ask is there à way to check that all the cells in row are filled prior to send date to another sheet ?
Thank in advance for your reply
You'll need to layer in an if statement with probably a for statement to loop through that row.
For example:
let ss = SpreadsheetApp.getActiveSpreadsheet();
let rowData = valuesFromRow:
let check = true;
for (i=0;i
Thank you for the reply I found à way. How cours one handle this when data get filled in from webapp. I made on but still need to reconfirm from dropdown in order to make it work. Is there a workaround in order to copy data automatically to coresponding sheet?
Hi Ninja!
I got the sheet running and everything was going wonderfully but now random drop downs won’t transfer sheets. 95% of the drop downs work as intended. It seems completely random and it’s on multiple sheets
So often on a case like this, there might be a slight mismatch between the drop-down and the tab name. Most common is a space (which obviously is invisible unless you edit the value or the tab name).
I will try and see if that is what happened!!!
I did try and match everything up and it does match. What it turns out is the problem is it IS MOVING the data but it isn’t deleting the row once it’s done. Again it does appear random, at least as far as I can tell.
@@brianhickey7968 One thing that can happen is if you have the header row frozen and the script is trying to delete the only row in the tab below the frozen header, it will error out and not delete that row. In that case, I have to add an if/else statement to the script, something like this:
if (row == 2) { sheet.getRange(2,1,1,sheet.getLastColumn()).clearContent() }
else { sheet.deleteRow(row) }
Do we have a similar feature in excel?
There is a similar feature using VBA code: answers.microsoft.com/en-us/msoffice/forum/all/move-a-row-based-on-drop-down-list-to-another/5b0a19e8-e753-4538-ae15-e6b3d5e349b6
I am facing a small issue that when the data is transferred the date format is changed from 6-May to 6/05/2024 and i have to change that again to 06 may everytime and for every column.. can you please provide me a solution for this??
So what you'll want to add into the script is a line to add the number format to change the format into the desired date format. Something like this (assuming column with the data is 6)
targetSheet.getRange(2,6,targetSheet.getLastRow()-1,1).setNumberFormat("d-MMM");
@@SheetsNinja thanks a lot brother.
You are truly an genius and way too helpful for me...
@@SheetsNinja Keep on growing brother 😇🫡🫡
is there a way for me to add another row once I transfer a row into a new one?
I'm not quite sure what you mean? Are you talking about using the script to move a row or are you talking about entering data?
Thank you so much for the Awsome & very helpful vdo... Can u plz help me, one more thing when I create google form in the same format and I used this code then it's working or not ???
So when Google Form adds a response, it will not make the "onEdit" trigger run. You would need to use the "On form Response" trigger and then scan the last row for whatever condition you're looking for
Thank you so much sir 😊
Glad it was helpful!
This is awesome, where did you learn to create apps script?
I've been building Google Sheets full-time for 2 years now, so it's been on-the-job learning. Many late nights and lots of frustration!
Big help! Thanks!!
Awesome, so glad this was helpful!
That's an awesome table ... where did you learn about this? I am really interested in it...
Lots of trial and error, reading apps script documentation, watching TH-cam videos, and taking Javascript tutorials.
THANKS SIR
Awesome, so glad this was helpful!
Many thanks for providing such a simple script! BTW, I'm having trouble adding checkboxes to a row. After moving it, it becomes a plain text line True/False😥😥
So usually I will just add a line to the script after the new data gets added, something like this:
targetSheet.getRange(targetSheet.getLastRow(),checkbox_column).insertCheckboxes();
Hey Ninja, thanks for the video. I used your script exactly and Im having an issue with the data jumping to the bottom of the new tab. Any advice?
So that is because there is something on the tab that doesn't look like blank rows to the script. Common things that cause that is checkboxes or formulas that have blank spaces as part of the formula (e.g. something like =IF(A2="","",rest_of_formula). The "" will still read as a row containing data for the script.
You were right, I had a sum function in there. When removed it jumped to to the top. Thanks!
hey ninja first of all thank for this script,i got an issue when i select lead the whole row went to sales.i have importrange data.
So if you have importrange data, this won't work the same as the importrange data is dynamic. So you could use the importrange for your initial stage, you just won't be able to delete the row when it copies the data to the new status tab.
Thank you so much , this is awesome .. it worked great..you got one subscriber........
just one question that I have put =Sequence(counta(B:B)) in A2, and as soon as I select the option in last column 17 the whole row get deleted and move to target sheet ... and with it my sequence formula also get deleted.. so what changes do i need to make in app script.. to keep that formula in place.. or the script don't delete the first column A?
A prompt response will be much appreciated..
one more thing the rows that are moving to new sheet is leaving so many rows blank up , why is that happening??
So you can put formulas in the header like so:
={"Column A Title";sequence(counta(B:B))}
@@ankitashastri4101 So this will happen if you have something in the target sheet that the apps script will read as a non-blank cell--for example, checkboxes, or a formula that you've already dragged down in other columns.
@@SheetsNinja Thank you for the replies, will try for sure 🙏
Hello !! Thanks for the Script. Just one challenge that i am facing is when the row shifts to the target sheet it is deleting the subsequent row. Please guide. Thanks
So if you're using the script as-is, it will work fine, but if you're incorporate some other things in the script it may be shifting things before it deletes the row.
@@SheetsNinja
This is the Script that i am using there is no change that i have done other than the Column number
/*
@OnlyCurrentDoc
*/
function onEdit(e){
let range = e. range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();
if (col == 19 && val != ''){
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(source.getName());
let targetSheet = ss.getSheetByName(val);
let data = sheet.getRange(row,1,1, sheet .getLastColumn()).getValues();
targetSheet.appendRow(data[0]);
sheet.deleteRow(row);
}
}
Thank you very much!
You're welcome! Glad it was helpful!
Tried it, it works but the hyperlinks are not copied :( can you help?
I have another video showing how to copy hyperlinks: th-cam.com/video/E1w7VnFWsZA/w-d-xo.html
The short version is using .getRichTextValues() and .setRichTextValues() instead of .getValues() and .setValues()
@@SheetsNinja Thank you so much!
@@SheetsNinja hi sorry but im confused where should i put this short version? there's only .getValues() but there's no .setValues() in the script, even on the video you linked and the updated script.
@@ReaElaenaGalang
so the script I did above uses targetSheet.appendRow(data[0]) so what you would do instead is this:
targetSheet.getRange(targetSheet.getLastRow()+1,1,1,data[0].length).setRichTextValues(data);
hey great videos, i tried it and it work well but not as i wanted. the data i copied is many (more than 10000 rows) and when i pasted it, it only move the first data i pasted. the values is on coloumn 2 too for my data, i changed the code to coloumn 2 but i have to pasted again the data to coloumn 2 and like i said it just move the data of the first row i copied. how can i make it to move all the data i pasted on coloumn 1 (A) based on the value on coloumn 2 (B) to another tab that have the same name as the value? and on excell i use a button to start the script for looking the value on coloumn 2 (B) then it move the data to the tab/sheet that have the same name as value, how can i do that on google spreadsheet?
So if you're needing to handle multiple rows, you can't use an onEdit trigger as it can't properly handle input from multiple rows. So you have to write the script to scan multiple rows and determine the destination. You could use a button to trigger when this happens, or you could try using an OnChange trigger. One thing to keep in mind is that you will probably need to use a column to mark what rows have been processed so you don't keep copying the same rows of data again every time the script runs.
@@SheetsNinja ah yes it only have to scan the coloumn 2 (B) for a values that is same as another sheet and after i pasted the data, it can move all the data row to a specif sheet that have the same name as the value. it can be triggered by a button or move the data instantly after i pasted it, but i dont know coding and so far searched the internet i have no success to find someone that making that code. can you assist me by making a video about it or guide me of how the code should be? i can show you the google spread that i use with the data too if you want
I just recorded a new video to explain how to move multiple rows of data at once: th-cam.com/video/NwULKMo7ygs/w-d-xo.html
@@SheetsNinja wow thanks dude you really are a life saver! I will try the code later. Thank you very much 🙏
This is exactly what I've been searching for. Thanks so much for sharing your knowledge.
Awesome, so glad this was helpful for you! Glad to share.
Same🎉
Is it possible to do this between 2 different spreadsheets?
Yes, instead of just referencing a different tab for the target, you would use something like:
let targetSS = SpreadsheetApp.openById('SS_ID');
let targetSheet = targetSS.getSheetByName('TARGET_TAB');
@@SheetsNinja incredible, I just started to learn javascript so I can learn google app script easier.
Hi @Sheets Ninja, this should work but for some reason mine didnt. I have same script from the vid
great i have the code, now how to applay the code???
You can download the template from the link, if you search my channel for apps script, I have videos covering many things from authorizing the script to various applications.
hey sheets ninja good work.., can you please add a code in this for move drop down tooo.... thanks waitng for your rply
Hey, so all you have to add is two lines, one to get the data validations and the other to set them. E.g. something like this:
let validations = sheet.getRange(row,1,1,sheet.getLastCplumn()).getDataValidations();
targetSheet.getRange(newRow,1,1,validations.length).setDataValidations(validations);
@@SheetsNinja thank you for your reply i will testifies it and rply you soon thanks for your help
It difficult
Apps Script can be confusing when you first get started. You can try downloading the template and checking out the completed code there and hopefully that helps you to start getting a better grasp on it.
I'm getting this error, I just copied and paste the script you had
"Execution log
9:21:30 PM Notice Execution started
9:21:30 PM Error
TypeError: Cannot read properties of undefined (reading 'range')
onEdit @ Code.gs:6
So you always get that error the first time you try to run the onEdit trigger directly, but as long as the script is working when you use the dropdowns, it will be fine.
I tried this, my stats Col= 7 as my sheet Col is G that's why I wrote if (col==7 && val!=' ') but doesn't move my data can you suggest me. or Send me your email address so that I can send you SS or Recording
Did you try using the script as is first before you modified it? Just to make sure you got everything working first.
@@SheetsNinjaI am also having this problem. I tried to move my data to column one to see if that would work and it doesn’t. So I went back through the code looking for errors. The only thing I could see is line 14 and 16. The parenthesis inside the close parenthesis are blue not black. I’m not sure why or if that is a problem.
@@SheetsNinjaI checked on edit and I get two errors. Value is not defined Code:12:19 and Cannot read properties of undefined reading range Code:6:17
Hi there! You videos are helpful! BUT Do you have a video about How to move a row to bottom of sheet when "Status" is changed to a value???
I have a similar video here: th-cam.com/video/HJlpzGvzw30/w-d-xo.html
But instead of the finding section header, you could just change the target to:
sheet.getRange(sheet.getLastRow()+1,1,1,data[0].length).setValues(data);
@@SheetsNinja Thank you!
these videos are super helpful!! do you know if it's possible to move the row to the other sheet but only in a specific columns range? So the row data would only start showing up in column C instead of A as an example, without adding more empty columns in the origin sheet. Thanks!
For sure!
Instead of the line with .appendRow(), we would do this:
targetSheet.getRange(targetSheet.getLastRow()+1,3,1,data[0].length).setValues(data);
Thanks for the help here- How can I move a row down to the bottom based on the results of a drop down? I have Paid and Unpaid invoices and want to move all unpaid rows to the bottom of the tab I am working on?
Hey, so you can use the same logic but instead of the target being a different sheet, use the same sheet, so everything would be the same except for that line.
can you provide a code for moving copying/moving row to a different spreadsheet ? now just a sheet within the spreadsheet ?
I just recorded a new video showing how to move to a different spreadsheet: th-cam.com/video/9xDQhwtkBWM/w-d-xo.html
Thanks, I've subbed
Glad this was helpful, thank you and see you around!
Awesome ! Can you make another case. To automatically delete entire row once the one cell has been delete?
This would be tricky, detecting a deleted cell isn't always reliable, it's better to use a checkbox or a dropdown to trigger deleting a row.
How do make it so it copies the function inside the cell and not only the value?
You can copy formulas using .getFormulas and .setFormulas instead of .getValues() and .setValues()
@@SheetsNinja Hi again, Thanks for the answer.
Now i can make it move the formulas, but only the formulas. The rows i have is both Values and Formulas. I can see other people in the comments are looking for the same thing.
Is it something u can help us with ? :)
Thanks alot again. Very nice video
You're in luck, I had time to record another quick video on this topic, you can see it here: th-cam.com/video/i2VgEclS4ps/w-d-xo.html
Can't get it to work - Not sure why. This is my script:
/*
@OnlycurrentDoc
*/
function onEdit(e){
let range = e.range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();
if (col == 1 && val != ''){
let ss = Spreadsheet.getActiveSheet();
let sheet = ss.getSheetByName(source.getName());
let targetSheet = ss.getSheetByName(val);
let data = sheet.getRange(row,1,1,sheet.getLastColumn()).getValue();
targetSheet.appendRow(data[0]);
sheet.deleteRow(row);
}
}
A couple of reasons why this could be... Is your drop-down in column A? And do the drop-down options match the tab names exactly? And finally, did you authorize the script?