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

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

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

Lines 5 and 6 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.
  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).

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.

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.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get your from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

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

~Yagi

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

2 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”

  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!

    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.

Leave a Reply