Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another

append Values - Google Apps Script

I recently had a legend (a.k.a reader) ask about how to copy selected columns from one sheet and append them to the bottom of different selected columns in another sheet. 

To me, this seemed like a pretty common task that one might face, so in response I decided to create a template function to easily do this and reuse on the same project or in other projects. 

But first, let’s take a look a the problem a bit more. 

We want:

  1. the freedom to select any number of columns from one sheet.
  2. to select the same number of different columns from the sheet we want to paste to.
  3. to paste to the bottom (append) the receiving sheet.
  4. to take into account varying header row heights.
  5. to have a “Completed” or “Copied” column in our origin sheet so that we don’t continue to paste all the data instead of simply what has been updated.
  6. have the data with formulas that we only want to paste the values for.

The Example

Let’s say we have a sheet that is updated regularly and we want to be able to paste data from columns A,B and C of this source sheet and append it to columns C,E and A of the destination Sheet. 

  • Copy Sheet 1 Col A -> Append Sheet 2 Col C
  • Copy Sheet 1 Col B -> Append Sheet 2 Col E
  • Copy Sheet 1 Col C -> Append Sheet 2 Col A

Take a look at our two Google Sheets 

SourceSheet - Google Sheets
DestinationSheet - Google Sheets

You can see in the Source Sheet (Sheet1) that we have 3 data columns and one column market To Copy. The To Copy column will be updated with “Copied” once the code is run successfully.

In the Destination Sheet (Sheet2) you can see that we already have some data there. We don’t want to overwrite it. We simply want to append our data to the bottom of it. 

The Code

NOTE! On the code snippet there is an option to open in new window. It might help you so you can follow the code as I explain it. 

The main code here is inside the pasteMultiCol function. You can see that the function takes 5 arguments. We run this function in the runsies function which is just a placeholder for whatever you want. The file arguments are:

  1. sourceSheet – the Source sheet in our case Sheet1
  2. destinationSheet – the Destination sheet in our case Sheet2
  3. sourceColumns – selection of source columns in our case col 1,2,3
  4. destinationColumns – selection of destination columns in our case 3,5,1
  5. doneColum – the column that records if the data has already been added to the destination sheet. 

runsies()

In the runsies function (Ln 62) we set all the variables we need to run 
pasteMultiCol.

First we get the spreadsheet and then the two sheets we we will be working in (Ln 63-64).

Next we create an array of columns for the source sheet and the destination sheet respectively. Careful to ensure that the length of both arrays are the same.(Ln 67-68)

It is easier to use columns by numbers in these arrays than the letter notation. 

Our last variable indicates where the column in the source file is that marks the row as already been added to the destination sheet.

Finally we run the pasteMultiCol function with all our variable added. 

pasteMultiCol

Setting up the Main Variables

Lines 10 through to 31 set up our main variables that we will use for the rest of the function. 

First we get the range of all the data from the Source Sheet. We then grab all their values in the next line (Ln 11). This will be a nested array of row values that would look a little like this:

Line 12 Sets the sourcesheetFirstRow(more on this in a moment) to initially zero and sourceSheetLastRow gets the number of the last row. 

We then find the destinationSheetLastRow so we know what row to paste the updated Source Sheet data on. (Ln 15)

Then our pending pendingCount is set to zero (Ln17). The pending count is, in our case, any blank cell in column 4 (D) that has data in the rows in the other columns. This is currently set to zero, but we will update this in the following code.  

On lines 20-27 we use a for loop to loop through the source data by row looking for values in the doneColumn. We have to subtract the doneColumn by 1 because unlike the spreadsheet numbering system where numbering starts at 1 we are now referencing an array that starts its count at 0. 

While we are iterating through each row of the source data we are looking for the word “Copied” in the done column and counting each time it appears. We are also looking for any blank cells in the done column and counting those too. You can change the “Copied” and blank values to True or False or some other  value if you wish. 

Next, we want to determine the header. Header height in rows can change from project to project so to combat this we grab the sum of the first row of the Source Sheet and subtract it from the Source Sheet Last Row.

We then use the header to update the sourcesheetFirstRow variable (Ln 31)

Checking if There is Anything That Needs to Be Appended to the Destination Sheet

On line 34 we check to see if there is anything that needs to be added to the destination sheet. We do this by comparing the length of the sourcesheetFirstRow and see if it matches sourceSheetLastRow. If it does, that means we don’t need to do anything so we should return the function to stop the script. 

Pasting the Data into the Destination Sheet

On line 36 we first need to get the sourceSheetRowLength by subtracting the sourceSheetLastRow from  the sourcesheetFirstRow

Then it’s time to run through each of the destinationColumns (Ln 39-50). Initially we get the range we will be pasting our data into. The row will be the first empty space after last of the data. That’s why we add one (1) to the destinationSheetLastRow (Ln 40). The destinationColumns[i] (Ln 41) is the number that we find in our destinationCols back in line 68. The row length is determined back on line 36. Finally the column width is only 1. 

Lines 44-48 allow us to get the correct column data from the Source Sheet Range Values and put it into an array. Line 44. Slices off all the Copied data and Header information in the array. 

We then create a columnValues variable array (Ln 45). We’ll then loop through our sourceValue adding each value base on our sourceColumn to our ColumnValues array. 

Finally, the moment we have all be waiting for! We add the values to our destination sheet. 

Update the Source Sheet to say “Copied”

Lines 53-57 updates the source sheet column 4 (D) and adds a “Copied” cell to all the cells that were…um… copied over. 

To start we need to create an array the length of the number of rows we just copied across. Then we need to populate that array with the word “Copied”. (If you changed “Copied” to something else on line 21 then you need to change it here. (or you could be awesome and create a variable for this and only change it once ;))

Line 56 then gets the range we need to copy in our data.

Then, we just set the values with our array of “Copied”.

Now, you might be wondering why we don’t just iterate through each cell adding “Copied” as we go. Well, we want to reduce our interaction between the server and Google sheets, because these are quite costly in time. This article explains the method in my madness a little better:

Iterating Through Google Sheets the Right Way and the Wrong Way

Conclusion

Before we finish up, lets look at our script in action: 

append Values - Google Apps Script

Bonza! How about we add some more data.

Yay! it worked!!!

This code is by no means perfect. Ideally, I would like to add some error handling to catch a few things that might occur, such as a missmatch between the number of column to copy from and copy too. 

Have a go. See if you can improve on it or make some changes for your own project.

Good Luck!

~Yagi. 

2 thoughts on “Google Apps Script – Copy Selected Columns in One Sheet and Add Them To The Bottom of Different Selected Columns in Another”

  1. Great, Yagi! Looks great!

    There are two scenarios that I need to figure out:

    1) After you have copied all of the inputs, if you want to copy again any of the middle ones, when you remove “Copied” your script will copy one line but it won’t be the one without “Copied”, it will be the bottom one.

    Example: We have 6 rows input, and the 4th row is missing “Copied”, if you run your script, it will copy the bottom one (6th row) and not the one where “Copied” is missing. Any way to fix this?

    2) Is there any way we can add another IF option? For example, if column T is not empty and column U has no “Copied”, then copy those lines? (“if ((cellT$ !==””)&&(cellU$ ==””)) {“)

    Please find attached my file where you will understand better what I need. Thanks very much for your time again, this is so much helpful!!!

    In the link below, between 1 and 4 there are two hyphens(- -), just when I paste it here, it shows as –:

    (“https://docs.google.com/spreadsheets/d/1–4UL1SjdEv-YT-_gTpvKqy9H4SDEdBm8aubFpUZYuw/edit?ts=5be08e3d#gid=0”)

    Thanks again!

    1. 1) You would have to rewrite your code to deal with this. You will need to iterate through the spreadsheet identifying each line that event you want to copy occurs and push the row and the row number into a new 2d array. Then paste that array, go back and change the fields in the original data.
      2. Yep you are on the right track with your AND statement.

Leave a Reply