Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet [update Feb 2022]

GAS onEdit move to new sheet

Google Apps Script: onEdit, Google Sheets

Sometimes you want to be able to automatically move a row from one sheet to another based on the value of a certain cell.

The Example

One of the first things that come to mind, and I am sure it does for you dear reader, is when I took advantage of the Great Chicken Transformation back in, oh, 2019, I believe.

Folk kept turning into chickens, while other folk were wanting eggs. It just so happened that I had the farm to make it all happen.

First, though, I needed to keep a tab of every person I knew and if they turned into a chicken. If they did, then they were destined for the pen.

…Note to self: it may be late at night, but dam Yagi, your analogies are tight!

Google Sheets and Google Apps Script to the rescue.

So first off I set up a sheet named: Plague. Here I put all the people I knew, so I could watch em good and propper.

Next, I set up a sheet named Farm. These are for the people who turned into chickens. No harm in profiting from a few newly formed egg layers, right?

Whenever a new transformation occurs, I find the person on the Plague sheet and then select “Yes”  to say that they have turned into a chicken and will now be spending their days on the farm. Upon editing (onEdit) this cell to “Yes”, the row is copied and pasted to the Farm sheet.

Just like this:

move to another sheet onEdit Google Apps Script

The Code

You can copy the code below by hovering over the top right of the code and selecting  ‘copy’. Then copy the code with the ctrl + c shortcut.

In your Google Sheet, got to Extensions > Apps Script. Use Ctrl + V to paste in your code.

You can find out more about using Google Apps Script here:

https://yagisanatode.com/2017/11/03/can-i-modify-google-sheets-with-code/

Hire a Google Workspace Developer for your Business Needs

Quick Use Guide

In the global variables at the top of the code (lines 5-26), you have the ability to customize certain parameters.

Lines 6 and 7 provide you with a space to add the sheet name you are copying from (sourceSheet) and pasting to (destinationSheet).

The check object variable (lines 13-17) is the column you are going to check. It takes 3 objects:

  1.  col: the column number you want to check.
  2. changeVal: the value that onEdit will listen for to initialize the copy, paste [and delete] of your selected row. If you are using check boxes to achieve this, you can replace this with a true value.
  3. del: gives you the option to delete the row after you have copied and pasted or keep it there in the original sheet.

Lines 23-26, is the pasteRange object. This object lets you choose what you want to paste into your source sheet. It provides a start column number and a number of columns from the start that you want to paste (col).

Once you have updated these fields, you are good to go.

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

Read on to learn how it all works.

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


Code Breakdown

onEdit()

On lines 30-31 we select the active spreadsheet and sheet.

We then compare the name of the active sheet to the sourceSheet name (Line 33) to make sure it matches. This ensures we are on the correct sheet and prevents onEdit from doing any unnecessary work.

  if(sheet.getName() === sourceSheet)

Next, we grab the active cell – the one we just modified or added data to. We will go ahead and grab the cell column number and row number here as well.

//Get active cell
var cell = sheet.getActiveCell();
var cellCol = cell.getColumn();
var cellRow = cell.getRow(); 

Back up at the top of our page, we have the check object variable where we have set the column that will initialize the changes in column 3.

The if statement on line 39 compares this object (check.col) with the currently active cell value’s column (cellCol), and if they match, we proceed.

if(cellCol === check.col)

On the next line, we check the current cell value against our stored value we will use to initiate a change back in our check object. In our case, it is “Yes” (check.changeVal = “Yes”).

  if(cell.getValue() === check.changeVal)

Once all our checks are done, we can now really get started copying, pasting and deleting our code.

Copying the row

Line 43 starts this process by selecting the current row of the active cell with the getRange method. This method can take 4 arguments:

  1. Start row – for us, this is the cellRow.
  2. Start column – for us, we refer to our pasteRange.start object at the top of the page which we have set to 1.
  3. The number of rows to select – for us, we will always only want to remove 1 row, so this is set to a row length of 1.
  4. The number of columns to select – we go back to the pastRange.cols at the top of the page here where we have selected to copy and paste 2 columns of data.

var exportRange = sheet.getRange(cellRow,pasteRange.start,1,pasteRange.cols);

Pasting the row

To paste the selected row, our first task is to grab the destination sheet. We do this on line 46 with the pasteDestination variable.

Practice! Take a look at that “ss” variable. Do you think you could swap out that variable somehow to make the paste destination another spreadsheet?

We also want to know the row number of the first empty row at the bottom of the sheet we plan on pasting into.

  var pasteDestination = ss.getSheetByName(destinationSheet);
  var pasteEmptyBottomRow = pasteDestination.getLastRow() + 1;

Practice! What if you want to paste your data into empty cells that have data beside them? the get last row approach won’t quite work like this. What would you do to find the first empty cell in this scenario?

Line 50 actually copies the data. Here we use the copyTo method. Just like a normal copy and paste, all you need to do here is set the location of the first cell that you want to paste into. It will then paste the range of the copied values.

The copyTo method only needs a range argument, but I like to ensure that I am pasting just the values so I have set the second argument to past values with the CopyPasteType set to PASTE_VALUES. So our copyTo arguments are as follows:

exportRange.copyTo(paste range cell location, CopyPasteType);

In our code it looks like this:

exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
                           SpreadsheetApp.CopyPasteType.PASTE_VALUES);

 

Optional Delete

Finally, if we have set the check.del object to true , then we want to delete out the row after we have copied it.

We simply do this by using the deleteRow method on our active sheet.

if(check.del){
          sheet.deleteRow(cellRow);
};

 

Conclusion

My new chicken empire is under control thanks to Google Sheets and Google Apps Script.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

You can now simply copy and paste the code into your own Google Sheet and modify the global variables at the top. If you don’t want those top variables to be global, then chuck them inside the onEdit function. Easy!

You can modify the check.changeValue to whatever you need. Checkboxes are just a true or false boolean, so they are pretty easy to implement too. Give it a try!

Let me know what you decide to implement this on. It is always cool to hear what people do with my tutorials.

A special thanks to Nina for providing the inspiration for creating this post.

~Yagi

Further Reading

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Changelog

  • 13 Jan 2022 – 
    • Updated instructions for accessing Apps Script
    • Added a not on how to use this code with checkboxes.
    • Corrections to lines.
    • Added table of contents

Thanks Mrs Yagi for the feedback.

  • 23 Feb 2022 – Add further reading section and table of contents. 

38 thoughts on “Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet [update Feb 2022]”

  1. Hi Yagi,

    This is super helpful! Now, what if the condition is a date? Suppose i am looking to move the rows of data that is past expiration, meaning past today and beyond. How can i modify that?

    Thanks in advance!

    Reply

    1. Hi Mila,

      Thanks for the kind words.

      You could certainly change the check.changeVal to your desired date or today’s date (eg. new Date). Then compare that with the date of the cell. Then if the active cell value with the date is greater than or equal to the date then move it.

      Have a look at these links:
      https://stackoverflow.com/questions/37485744/javascript-how-to-check-if-a-date-is-greater-than-or-equal-to-a-date-value
      https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript

      See if you piece it together from there.

      Reply

  2. Hi Yagi,

    Appreciate your sharing. It is super helpful.

    Noticed the transmission across tabs in the same sheet. Just wonder what if I need to copy and paste data to another sheet. To doing so, what modification should I do?

    Thanks in advance for helping a fresh beginner on Google Apps Script.

    Reply

    1. Hi Lena,

      Thanks for the kind words.

      That’s actually one of my practice questions I had in the post.

      Have a look at the ss variable on line 31 and then at the pasteDestination variable on line 46. What could you modify on line 46 to achieve this? Maybe using this id of the destination spreadsheet SpreadsheetApp might have something to help you…

      Feel free to have a go and paste your attempt.

      ~Yagi

      Reply

  3. If i want Update or copy records with quanlity:

    Sheet1: Name | Address | Quanlity | ETC |
    Adss | Addr | 100 -> Copy 20 (80)

    Sheet2: Name | Address | Quanlity | ETC |
    Adss | Addr | 20

    Reply

    1. Hi Thomas,

      I’m not 100% certain what you mean. My guess is that if you have in sheet 1 your quantity of 100 items and you want to push 20 of those items to Sheet 2 while subtracting them from sheet 1. Yes this is achievable with Google Apps Script but it a little outside the scope of this tutorial.

      ~Yagi

      Reply

  4. What would be the cleanest way to work with not a boolean but with a dropdown with multiple values and going to multiple sheets?
    Is there a way to do something like a loop over all the options and put them in the matching sheet?

    Excellent work, I’ve been learning a lot here.

    Reply

    1. Hi Thomas,

      Yes, I think you are on the right track by looping through all dropdown options and then move the row to the relevant Sheet.

      Give it a try and if you get stuck feel free to post your code below.

      Cheers,

      Reply

  5. Hi Yagi,
    It’s simply superb.
    I am struggle to remove row after changed value to particular cell.
    It’s is because of, the sheet data are pulled data from ‘Form Response’ after some filtration of some column using ‘query’ function.
    Query function :-
    =query(‘Use This Sheet For Data’!$A$5:$O, “Select A, B, C, D, G, N, O where D = ‘MIMP’ “)
    There is any way to pull data from another sheet and able to update and auto delete row and move to another sheet (Completed Sheet)?

    Thanks in advance…

    Reply

    1. Hi Sathianarayanan,

      You can’t use the onEdit function on formula changes unfortunately. Your best bet is to use a time trigger for updates like this or set the onEdit condition on the for data sheet.

      Reply

  6. nice post, i want to make 2 script like this in 1 sheet. I have 2 destination of data, when i change value to Yes data go to 1 sheet, change value to No data go to different sheet. Can you help me with that

    Reply

    1. Hi Steven,

      Thanks for your kind words.

      I try to keep this site education focussed. If you have the time to try your own attempt, feel free to post your code if you run into any issues. I’m guessing you would want to remove the data from the ‘no’ sheet if ‘yes’ is checked in the main sheet and vice versa. This would add a little more complexity to the code. You would also need to have a second destination sheet variable. You could then add your new copy and paste instructions under line 51, basing it on the code from lines 45-51.

      If you don’t think you will have the time to work on this on your own, I would recommend you look to Fiverr if you need to get some custom Google Apps Script written well and quickly. A project like this might cost you around $200 US depending on your specific needs. Here’s a link to the Fiverr page for Google Apps Script developers. The first one is an affiliate link if you want to support me – it won’t cost you any extra, but the little commission I get helps to pay for the running of this site. Otherwise, feel free to click that direct link if you want to go that direction:
      Fiverr (Support Yagi)
      Fiverr

      Reply

      1. Thank for the instruction but i mean that I will have 2 sheet other than the main sheet which is Yes sheet and No sheet. When i change the value to “Yes” it will move data from main sheet to Yes sheet. Change value to “No” it will move data from main sheet to No sheet. I try to write 2 different code and 1 combine code like you suggest but only the transfer to Yes sheet function, the transfer to No sheet don’t.

        Reply

        1. Hi Steven,

          It is difficult to see where I need to guide you without viewing your attempted code. Feel free to paste it below and if I have time, I will take a look at it.

          Yagi

          Reply

          1. I’m try to make a script that copy the data from 1 sheet in 1 file but the destination is 1 sheet in another file (same Google drive) based on your script. Do you have any suggestion on that ?

          2. Hi Steven.

            Yeap you can do that quite simply with this code.

            Just update this:
            var pasteDestination = ss.getSheetByName(destinationSheet);
            to this:
            var pasteDestination = SpreadsheetApp.openById(destination spreadsheet id).getSheetByName(destinationSheet);

  7. Hi, I tried using your code to export data from each line to PDF. Everything ran well to where the PDF was created. It stopped there. Is it possible that the OnEdit method cannot handle an external Application? I’m sending a piece of code with printing. The code ran, but then simply ended (without reporting an error). When I call it by default, it normally works. (var response=UrlFetchApp.fetch(URL,params).getBlob() //HERE IT’S STOPED)

    Reply

  8. Thank you so much. The script is working . Awesome!!!!!!!!!!

    Reply

  9. Yagi, thank you for this!

    I’m wondering, is there a simple tweak I can make to this script to keep the selected range on the same sheet? I don’t want to move the cells to a separate sheet, just move them down instead (still deleting them from their original range).

    Any advice would be greatly appreciated!

    Reply

    1. Hi Grayson A,

      Not really a 100% simple tweak of this code, however it is quite possible.

      You will need to do a number of things:
      1. Update row 46 and 47. You won’t need to apply the destination sheet. You will just be using the source sheet destination.
      2. You will also need to identify the row location you want to paste your deleted row to. If this is the last row then you could use it getLastRow. Otherwise you may need to store the target row in something like a PropertiesService.
      3. You will need to keep in mind that one row is subtracted before pasting in your row below where you have deleted it.

      Hopefully, that will get you in the right direction experimenting with your code.

      Regards,

      Reply

      1. Thank you for the fast response! I’ll play around with it and let you know if it’s a success.

        Best,
        Grayson

        Reply

  10. Hi, how do I do this:

    Practice! What if you want to paste your data into empty cells that have data beside them? the get last row approach won’t quite work like this. What would you do to find the first empty cell in this scenario?

    I’m trying to copy/paste rows over to another sheet which has two separate tables in it and your practice question above is exactly what I want to do but don’t know how! Can you help?

    Reply

    1. Love to find out how to do this as well!
      Love the site too btw!

      Reply

  11. Hey Yagi,
    A great resource for us. Thank you. Just what I needed but I did tweaks for checking multiple columns and moving same row to multiple sheets with the columns I wanted. It worked. Appreciate it. Thank you

    Reply

    1. Great work Nish!

      I love to hear these success stories.

      Happy coding, mate!

      ~Yagi

      Reply

  12. Yagi,

    This is exceptional. (And I love the chicken transformation example!!)

    I’m trying to adapt this to a tracking sheet. The idea is that when someone enters a status update for a specific row in column C, it shifts the old information (columns D through K, L, however many updates there are) I end up totally lost though because simply adding a column doesn’t work and its a challenge to know how many columns of data to shift.

    Any thoughts of brilliance?

    Reply

  13. Hi Yagi and thank you for a code that works!!!

    I’m new to google sheets and it was not easy to find the solution you generously provided, you are awesome! thanks again!

    If I can ask one thing is to be able to move the row that was deleted from the 1st sheet and pasted to the second sheet back when when I change a value, say “pending” in the second sheet. I hope I am making sense..

    Looking forward to your insight!!!

    Ian

    Reply

    1. Hi Ian,

      If you wanted to simply paste the row back to the original document at the bottom you should be able to reuse the code in this tutorial with the new set of Global Variables. If you want to put it back exactly where it came from, it will be a little more tricky.

      Cheers,
      Yagi

      Reply

  14. A great write up! Thank you!

    Reply

  15. I’m try to make a script that copy the data from 1 sheet in 1 file but the destination is 1 sheet in another file (same Google drive) based on your script. Do you have any suggestion on that ?

    Just update this:
    var pasteDestination = ss.getSheetByName(destinationSheet);
    to this:
    var pasteDestination = SpreadsheetApp.openById(destination spreadsheet id).getSheetByName(destinationSheet);

    Exception: Target range and source range must be on the same spreadsheet.
    at onEdit(Code:53:21)

    Reply

    1. I am also having this issue when trying to copy the rows to another spreadsheet.
      Seems to be something with the copyTo settings?!
      exportRange.copyTo(pasteDestination.getRange(pasteEmptyBottomRow,1),
      SpreadsheetApp.CopyPasteType.PASTE_VALUES);

      Any idea?

      Reply

      1. Hi Martin,

        Yes, using copyTo between two Google Sheet workbooks will not work. You should get an error like this:
        Exception: Target range and source range must be on the same spreadsheet.

        My recommendation is to get to use the set/get methods. Most commonly used in this case are setValue/getValue(s) and setFormula/getFormula(s).
        Example
        I have an origin spreadsheet named, ‘Origin’, and a destination spreadsheet named, ‘Destination’. In ‘Origin’, I want to copy the range from ‘A2:B7’ (Start row 1, start col 1, row count 6). I want to paste this in my ‘Destination’ spreadsheet so that they start pasting from ‘D10’ down. For convenience, both spreadsheets have a sheet tab named ‘Sheet1’. With the set methods, you need to assign the same range that you are copying. Have a look at the sample code:
        //####GLOBALS####
        const originID = "1gGhNGqywIoI2iASfAUmXZvZzCHiuPNckBztVi3bagfU"
        const destinationID = "1ChpyCJ774H-XUzrgnGUrQ3pQ1Ce3sThnycAPP-Ygo1s";

        const originSS = SpreadsheetApp.openById(originID);
        const destinationSS = SpreadsheetApp.openById(destinationID);

        const originRange = originSS.getSheetByName("Sheet1").getRange(2,1,6);
        const destinationRange = destinationSS.getSheetByName("Sheet1").getRange(9,4,originRange.getNumRows());

        function copyAndPaste(){
        const originVal = originRange.getValues();
        destinationRange.setValues(originVal);
        };

        I hope this help. Let me know if you think this is work a blog post on its own to expand getting and setting values/formulas in new locations (copy and paste), particularly new Google Spreadsheets. If you do, I’ll likely write it soon.

        ~Yagi

        Reply

        1. Thank you very much! It works, great!
          One more question.
          In your script one range will be copied, e.g. i tell it to start in col1 and take the next 4 cols for copying this range (so e.g. A1:E1. So the columns must therefore be directly next to each other
          I am trying to change the script so that only so that columns in the line where the trigger change is made are copied that are not directly next to each other (e.g. A1: B1 and D1: E1, so C will not be copied).
          My only idea now is to define several pasterange variables, each with the range that should be copied, but I lack an idea to somehow incorporate that.
          Do you have a clue? 🙂

          thanks, you are a true genius 🙂

          Reply

          1. Hi Martin,

            I would probably be fastest for Apps Script to select the entire range from A1 to E1 and then in the script isolate only those ranges.

            Your you could then use the splice method to remove the extra column from the row.
            let vals =[["A1","B1","C1","D1","E1"]]

            vals[0].splice(2, 1)
            console.log(vals)

  16. onEdit Trigger is working fine but what if when i have to get the data after col3?

    Reply

  17. Hi Yagi, Im using following code to move a row to bottom of sheet when “Status” is changed to a value. This code i found on one website. can you help me by highlighting which part of this code i should edit so that i will work for my sheet. currently its not working and showing error.

    function onEdit(e) {

    const row = e.range.getRow();
    const col = e.range.getColumn();
    const as = e.source.getActiveSheet();
    if(as.getName() == “Sheet1″ && col == 6 && row > 1 && !as.getRange(row,col).getValue()==”) {
    const row_new = as.getRange(row,1,1,col);
    row_new.copyTo(as.getRange(as.getLastRow()+1,1,1,col));
    as.deleteRow(row);
    }
    }

    Reply

Leave a Reply