Move Rows of Data in Google Sheets - Apps Script Tutorial 

Awesome, so glad this was helpful for you! Glad to share.

Awesome, thank you!

Awesome!

Awesome, glad this was helpful!

Happy to help

Awesome, so glad this was helpful for you!

Awesome, so glad this was helpful! Check out our other videos for more tutorials on all kinds of stuff!

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

Awesome, so glad this was helpful!

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()".

I just added a video covering using multiple criteria: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-KekdeXn8P5Y.html

Just doing my job of spreading awesomeness! Stay tuned for more!

Glad it was helpful!

Glad this was helpful, thank you and see you around!

You're welcome! Glad it was helpful!

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-i2VgEclS4ps.html

I just created a new video showing how to use multiple criteria: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-KekdeXn8P5Y.html Combined with my part 2 to this video, that will explain all the concepts needed to complete your project: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-0Up8bqnfttA.html

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.

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.

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.

Lots of trial and error, reading apps script documentation, watching RU-vid videos, and taking Javascript tutorials.

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.

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!

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

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.

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.

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! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-HJlpzGvzw30.html

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

You'll just need to swap out .getValues() for .getRichTextValues() and .setValues(data) for .setRichTextValues(data).

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]); }

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.

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'); } )

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!

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?

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 🙏

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()

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

Just delete the row of the script with .deleteRow() and it will copy instead of move.

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.

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!

I have a similar video here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-HJlpzGvzw30.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!

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();

I just recorded a new video showing how to move to a different spreadsheet: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-9xDQhwtkBWM.html

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.

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?

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-E1w7VnFWsZA.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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-E1w7VnFWsZA.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.

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); } }

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.

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.

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!!!

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)

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.

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!

So you can use the same logic as this video: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-E1w7VnFWsZA.html Just swap out where it uses .getFormulas() and .setFormulas() for .getRichTextValues() and .setRichTextValues()

You'll just need to delete one row in the code, which looks like this: data[0][0] = '';

@@SheetsNinja Thank you, it works perfectly.

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-NwULKMo7ygs.html

@@SheetsNinja wow thanks dude you really are a life saver! I will try the code later. Thank you very much 🙏

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) }

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-i2VgEclS4ps.html

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

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

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?

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 😇🫡🫡

I have another video showing how to copy hyperlinks: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-E1w7VnFWsZA.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);

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.

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.

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.

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?

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.

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