Move Rows With Different Tab Names - Dropdown

[@starnovie-gc7vd]

hello! i used this for a google sheet i made for a watchlist, so once we see a movie on the list i wanted it to go to the watched tab, and that part works great! but i cannot figure out how to delete the range that was moved, it just leaves blank rows behind where i would want them to just disappear. hopefully this makes sense lol, the range i'm moving is (r.rowStart,1,5,13) so there are more rows than in your video that need to be deleted if that makes a difference

[@SpencerFarris]

Use deleteRows() developers.google.com/apps-script/reference/spreadsheet/sheet#deleteRows(Integer,Integer)

[@sujalpatel4599]

Hello Spencer firstly great video glad i came Across this. I have a question how would i make it so that on edit it only goes to one sheet?

[@SpencerFarris]

What do you mean "only goes to one sheet?" Is it going to more?

[@Troy-ii5vj]

Hi Spencer! thanks again for all these amazing videos. I could use a little help though. I am trying to run the script but I can only get it to partially work. I need "Active" and "Inactive" from a dropdown to both go the the "Contacts" sheet. My Graveyard to the Graveyard sheet works correctly though. Any advice? I also don't know if it's an issue that that data to sort in contacts doesn't start until row 16. I know this is wrong but it's to give an idea function onEdit(e){ let r = e.range; if (r.columnStart != 1 || r.rowStart == 1 || e.value == null) return; const sh = SpreadsheetApp.getActive(); const valArray = ["Active","Inactive","Graveyard"]; const destArray = ["Contacts","Contacts","Graveyard"]; let dest = sh.getSheetByName(destArray[valArray.indexOf(e.value)]); let src = sh.getActiveSheet(); if (dest.getName() == src.getName()) return; src.getRange(r.rowStart,1,1,10).copyTo(dest.getRange(dest.getLastRow()+1,1,1,10)); src.getRange(r.rowStart,1,1,10).clearContent(); }

[@Troy-ii5vj]

NVM. I solved it using /* @OnlyCurrentDoc */ let tabs = { 'Active':'Contacts', 'Inactive':'Contacts', 'Graveyard':'Graveyard' } function onEdit(e){ let r = e.range; let val = r.getValue(); if (r.columnStart != 1 || r.rowStart == 1 || e.value == null) return; const sh = SpreadsheetApp.getActive(); let dest = sh.getSheetByName(tabs[val]); let src = sh.getActiveSheet(); if (dest.getName() == src.getName()) return; src.getRange(r.rowStart,1,1,10).copyTo(dest.getRange(dest.getLastRow()+1,1,1,10)); src.getRange(r.rowStart,1,1,10).clearContent(); }

[@SpencerFarris]

I love seeing people learn and solve

[@knottaustin92]

Hey Spencer, If I wanted to move this row and either the one above or below it utilizing just the one drop down. So I guess they would be linked in some way. how would I go about that. Ive got this working as is thanks to you but am needing to group rows together.

[@SpencerFarris]

You would just change the number_of_rows parameter of getRange() from 1 to 2.

[@sheetswithsajid5158]

If column C Heading "ARC,Caption" without dropdown from pasted through anther master sheet .how row shift anther sheet because here column C didnot edit.

[@SpencerFarris]

I'm sorry, I don't understand the question

[@elenab638]

Hi, ty for this video. May I ask you how can i do if i wanna share a sheet with this automation and I wanna make the automation working also for the others editors of the sheet?

[@SpencerFarris]

Since this is a simple onEdit trigger it should work for all users.

[@tamirizhak1453]

Hey Spencer - thank you for this video. I'm trying to figure out how to do the same only that I have some merged rows to move. I thought about using row groups on the merged rows so that I can identify the row range to be move and use it in the getrange method - but no entirely sure if it's possible. Hope you can help me out.

[@SpencerFarris]

Merged rows should work normally without issue

[@nidhi0588]

Hi Spencer, thank you for making this video. I spent hours looking for this. but unfortunately it is not working for me. it doesn't do anything for me. can you please help? I wanted to use this on my workload management sheet for my team. when they click on complete it should go to another tab in the same sheet. I was able to copy it automatically using filter but I want to move it to different tab. let me know if you want me to share the sheet with you. thank you!

[@SpencerFarris]

Sorry it's been SO LONG. Do you still need help with this?

[@kgayken0121]

Hey Spencer - I am having issues with my Open Tab. When I move something from Open to closed it goes to the top of the page, but if I move it from Closed to Open it automatically goes to the bottom of the page const valArray = ["Open","Waitlist","Closed","PM"]; const destArray = ["WOs","Waitlist","Closed","PMs"]; let dest = sh.getSheetByName(destArray[valArray.indexOf(e.value)]); let src = sh.getActiveSheet(); if (dest.getName() == src.getName()) return; src.getRange(r.rowStart,1,1,8).moveTo(dest.getRange(dest.getLastRow()+1,1,1,8)); src.deleteRow(r.rowStart); }

[@SpencerFarris]

Sorry it's been SO LONG. Do you still need help?

[@macmoulson]

Hi Spencer, this tutorial has been a life saver. I just have one question about the output of information. Is there a way once we get the range to only transfer over certain row information. For instance if we get the range src.getRange(r.rowStart,1,1,9) is there a way to only output the info in columns A, B, C, H, and I to the new sheet?

[@SpencerFarris]

let vals = src.getRange(r.rowStart,1,1,9).getValues(); let indices = [0,1,2,6]; let outVals = indicies.map(i => vals[i]); dest.getRange(dest.getLastRow()+1,1,1,4).setValues(outVals);

[@mauryhernandez2297]

For some reason the script forces me to leave the sheet, and I know it could be caused due a big amount of rows, however, the script is currently working perfectly with another bigger sheet... why is that??

[@mauryhernandez2297]

const VAL_ARRAY = ["Remove","READY TO INVOICE","COLLECTED","ARCHIVE"]; const DEST_ARRAY = ["DEFLEETED","INVOICED","ARCHIVE","ARCHIVE"]; const ss = SpreadsheetApp.getActive(); const sheets = {}; for (let i = 0; i < DEST_ARRAY.length; i++) { sheets[DEST_ARRAY[i]] = ss.getSheetByName(DEST_ARRAY[i]); } function onEdit(e) { const r = e.range; if (r.columnStart !== 24 || r.rowStart === 1 || e.value == null) return; const dest = sheets[DEST_ARRAY[VAL_ARRAY.indexOf(e.value)]]; const src = r.getSheet(); if (dest.getName() === src.getName()) return; const data = src.getRange(r.rowStart, 1, 1, 25).getDisplayValues(); dest.getRange(dest.getLastRow() + 1, 1, 1, 25).setValues(data); src.deleteRow(r.rowStart); }

[@SpencerFarris]

Sorry, what do you mean "forces me to leave the sheet?"

[@kathrinablack7405]

I've been trying this with my data sheet and I can't see to get it to work. I'm trying to move student data rows from the Master Data sheet to the Withdrawn sheet with a drop down menu using A (active) and w (withdraw). I don't think I am doing the code correctly.

[@SpencerFarris]

I replied to your email

[@dethered86]

Spencer, Thank you for this! I do have a question though. Is there a way to delete the values only from the main sheet? My main sheet is a project log and when I drop down and select "completed" it does exactly what I want but if I want to add something in its place on the main sheet I have to recreate all of the formatting, colors, data validation, drop down menus, etc. Thanks so much for your help!

[@SpencerFarris]

Try using src.getRange(r.rowStart,1,1,3).copyTo(dest.getRange(dest.getLastRow()+1,1,1,3)); src.getRange(r.rowStart,1,1,3).clearContent()

[@caitlyncarter6915]

I have followed this to a T and this is not working for me. I have not coded in about 8 years and im working on something for my company and i can not seem to get this code to work can you help?

[@SpencerFarris]

Can you add me as an editor? [e-mail]

[@ridrajas]

Thank you so much my dear favorite sir🥰🥰💚💚💚💚

[@SpencerFarris]

So very welcome

[@Jack-fx2jf]

Hey! Me again haha. Is there a way to get the row that's moving to a new sheet to always go to the 2nd row on the different tab instead of becoming the last row in the table? I thought it would have something to do with this line of script: src.getRange(r.rowStart,1,1,10).moveTo(dest.getRange(dest.getLastRow()+1,1,1,10)); but not entirely sure what to edit

[@SpencerFarris]

Sorry it's been SO LONG. Do you still need help?

[@Jack-fx2jf]

My drop down has an extra 12 columns after it. How do I get those to also move over to a different sheet?

[@SpencerFarris]

The .getRange() method takes (start_row, start_column, number_of_rows, number_of_columns) so you can just adjust the number_of_columns

[@Jack-fx2jf]

@@SpencerFarris Thanks for the reply mate! Another quick question. So in the column before the drop down I have another drop down/ Both of these are colour coded for each value. For some reason when the trigger column is changed it also changes the colour of the column next to it. Any ideas? Thanks :)

[@SpencerFarris]

@@Jack-fx2jf Not sure, I'd need to see the sheet/code

[@mauryhernandez2297]

Do you have a script to do this... by the way, this is beautiful... anyways, do this, but it also can be done from two different columns?? I mean, can the trigger be done in two separate columns?

[@SpencerFarris]

if ((r.columnStart != 3 && r.columnStart != 7) || r.rowStart == 1 || e.value == null) return;

[@josuecenteno731]

Hello Spencer, hope you are doing good. I would like to ask you how to move only once cell to another tab using the dropdown option. I learned with you how to move the entire row but I would like to move only one cell to another tab.

[@SpencerFarris]

You would just do something like e.range.moveTo(dest.getRange(dest.getLastRow()+1,1))

[@danielautoechipat3938]

Hello. How can i move row ignorig the formula as last row? It move to another sheet but put data after the formula not after the last row.

[@SpencerFarris]

It's doing exactly what it's supposed to - ss.getLastRow() gets the absolute last row with any data, it doesn't know that you have a formula that you want to move down. You'll need to insert a row after the penultimate row then move the data there.

[@danielautoechipat3938]

@@SpencerFarris Thanks for answering so quickly. Here an example of my problem: docs.google.com/spreadsheets/d/1qowADCPYiyej25ezXtjVLO5fvg9Gr9rolX3bh2-ZAG4/edit?usp=sharing

[@auxineblairepahilmagan5022]

Hi Spencer, I am try to move data to another sheets and it works but it keeps moving 2 rows to my target sheet.

[@SpencerFarris]

please add me as an editor to the Sheet

[@elizabethmacmahon6241]

Hi Spencer, this is a great video. How do you move rows if you don't want them to automatically go to the last row of the new sheet?

[@SpencerFarris]

Where do you want them to go?

[@elizabethmacmahon6241]

@@SpencerFarris thank you very much for your reply! I want the rows to go to the other sheet based on a specific column name (and it could go under the last row that has that specified column). Hope that makes sense. Is it possible to get your email? Or maybe what I wrote is enough.

[@elizabethmacmahon6241]

For instance, say if I were moving tasks based on a drop down (in progress, done, etc.) and want to move them to the next sheet under the last row that has column name, Chicago or New York

[@SpencerFarris]

@@elizabethmacmahon6241 [e-mail] (it's in the video description)

[@peterzahran5451]

@@SpencerFarris Thanks for the great work! Could the row be moved to the first available row instead? In my case, it would be row 2 under the column headings.

[@Orcadreams33]

Hi Spencer, me again! Thank you so much for directing me to this video, you are great help! It's now working perfectly, except that it takes 2 rows instead of 1, the one I am wanting and then the one below it. I am wondering if there is something I can do to stop this? Any help is much appreciated!

[@SpencerFarris]

Did you create a trigger?

[@Orcadreams33]

@@SpencerFarris Yes I have an OnEdit trigger in place!

[@SpencerFarris]

@@Orcadreams33 Delete that. Naming a function "onEdit" is what is called a "simple trigger." By having the named simple trigger and the installed trigger it's running twice.

[@hg7791]

Hello Spencer, great video. It is working perfectly however, I have a 7 column table and it doesn't move all of them. It just moves the fisrt 3 columns int the row. I would appreciate your help, thank you.

[@SpencerFarris]

The parameters of .getRange are (start_row,start_column,number_of_rows,number_of_columns). In my code I have number_of_columns set to 3. Change that to 7 and it should work.

[@hg7791]

@@SpencerFarris You saved me. Thanks a lot!

[@ZabuArts]

Really great! Thanx man 💪💪

[@SpencerFarris]

Glad it helped

[@nextlevel8991]

Hey Spencer - Thank you for this video. I have tried your code and it works fine. but I need to set column dynamically. like: const numColumns = r.getLastColumn(); this instead of setting column length. Please help me to use const numColumns = r.getLastColumn();

[@SpencerFarris]

I'm sorry, it's unclear to me what you want. Can you please add me as an editor on the sheet? [e-mail]

[@TheKawasaki500s]

Is there a similar macro code for excel instead of google sheets, I love what it does but I'm trying to do something similar in excel

[@SpencerFarris]

I'm sure there is, but I abandoned Excel years ago. I used to do quite a bit of VBA, but haven't been involved there in a while. You can check out StackOverflow, Reddit, excelforums, or Discord for help

[@TheKawasaki500s]

@@SpencerFarris thank you

[@sheetswithsajid5158]

very amazing and useful. thanks thanks thanks😊

[@jodie-louisetheyer395]

Hello, similar to a response below I am also finding that this isn't happening automatically. Did you manage to find out their problem?

[@SpencerFarris]

I don't know which response you're referring to, sorry. I'm happy to look at your sheet and see what's going on, though.

[@jodie-louisetheyer395]

@@SpencerFarris I think I have found the error - The data in the sheet is coming from a google form. If I manually enter a line the script will work but any data pulled through from the form won't. Do you know if it is possible to make it work with the google form responses?

[@SpencerFarris]

@@jodie-louisetheyer395 Is the column/value being populated by the form, or is it not moving the row created by the form when you enter data manually?

[@jodie-louisetheyer395]

@@SpencerFarris the form populates a row of data, leaving column 1 empty. Column 1 gets manually updated based on whether is task is new, in progress or resolved. If I change this cell to resolved the row doesn't move to the resolved tab. However if I manually enter a full row of data and then change the first column to resolved this will move the full row to the correct tab.

[@jodie-louisetheyer395]

I've managed to resolve this by using an array forumla to copy the form responses into a new tab & then run the script from there instead. I'm not sure if this is correct way but it does also enable me to format the data. Thank you so much for your tutorial & responses!

[@socomau21]

How to do this but not deleting from the first tab… Just sending copies to other tabs…

[@SpencerFarris]

Change .moveTo to .copyTo and remove the .deleteRow call

[@DeanONeill-gp4cl]

HI Spencer - thanks a mill on this video its really helped my a lot. i have been working with this for a few months now and its been going great. i only have one problem, ive been trying to load it on a new sheet but i keep getting TypeError : Cannot read properties of undefined (reading 'range' ) onEdit @ Code.gs:2 please help

[@SpencerFarris]

Don't run the script. It runs automatically whenever an edit occurs and needs information from the edit to function

[@farkhadhuseinov5246]

Code occured an error as: 4:09:36 PM Error TypeError: Cannot read property 'range' of undefined onEdit @ Code.gs:2

[@farkhadhuseinov5246]

function onEdit(e){ let r = e.range; if (r.columnStart != 22 || r.rowStart == 1 || e.value == null) return; const sh = SpreadsheetApp.getActive(); const valArray = ["Approved","Not approved"]; const destArray = ["Complete","QAC"]; let dest = sh.getSheetByName(destArray[valArray.indexOf(e.value)]); let src = sh.getActiveSheet(); if (dest.getName() == src.getName()) return; src.getRange(r.rowStart,1,1,22).moveTo(dest.getRange(dest.getLastRow()+1,1,1,22)); src.deleteRow(r.rowStart); }

[@SpencerFarris]

Don't run the script - it runs automatically when an edit is made and needs the data from the 'e' to work.

[@farkhadhuseinov5246]

@@SpencerFarris unfortunately it doesn't work automatically why?

[@SpencerFarris]

@@farkhadhuseinov5246 No idea. Can you add me as an editor?

[@farkhadhuseinov5246]

@@SpencerFarris gave you access