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 a 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 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 is 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 sets 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 the 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 been 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, let’s 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 mismatch between the number of columns 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. 

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you 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. *

 

*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.

26 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.

  2. Hi,
    I am getting following error with the script
    var sourceDataRange = sourceSheet.getDataRange();
    TypeError: Cannot call method “getDataRange” of undefined. (line 9, file “Code”)

    1. Hi pankaj,

      Did you call the source data in the runsies function? I’d have a look there first. Let me know how you wen.

      ~Yagi.

  3. Can you share a sample sheet with the script. Today I am getting following error
    The number of rows in the data does not match the number of rows in the range. The data has 89 but the range has 96. (line 49, file “Code”)

    1. Hi Pankaj,
      It would be more beneficial if you shared your work or your replicated sample with us so we can see where the error lies and guide you.

  4. facing the same issue how we can resolve this ”TypeError: Cannot call method “getDataRange” of undefined. (line 3, file “Code”)”

      1. Now it’s working fine also want to know there is based on the value can we capture selective column value in different spreadsheet.

      2. Thanks Now it’s working fine, Pls suggest there is why where based on the selected value we can copy selected column value in different sheets

        1. Hi Shashank,

          No, not with the current configuration of the function. You would have to create something custom for that purpose.

          ~Yagi

  5. Hi,

    I am new to google script and I am trying to write a script that is able to copy a specified (fixed column) of data from one google sheet to another google sheet’s next available empty row, do you have a script that is able to execute this:

    // function to save data
    function saveData() {
    var sheetFrom= SpreadsheetApp.openById(‘source data sheet ‘).getSheetByName(“XXX “);

    var sheetTo = SpreadsheetApp.openById(‘Destination data sheet’).getSheetByName(“XYZ”);

    sheetFrom.getRange(84, 7, sheetFrom.getLastRow(), 1).copyTo(sheetTo.getRange(1,
    sheetTo.getLastColumn()+1, sheetFrom.getLastRow()-84, 1), {
    contentsOnly: true
    });

    1. Hi Vicks,

      You could use the code provided in this tutorial for your purpose. You will just need to update the variables in the runsies() function.

      Cheers,

  6. Hi Yagi,
    Thank you for this example. It works well and I learned a lot.

    I do have one question, the last line of “runsies” you put the function in a var but you don’t use the var. Line 73.
    ” //Run our copy and append function
    var copyColumns = pasteMultiCol(source,destination, sourceCols, destinationCols, doneCol);
    };

    The var copyColumns is not used anywhere (I can’t find it). Also, the var is greyed out on my editor, I assume because it is not used.

    Can you explain why you did it this way?

    Thank you.

    1. Hi pnnyg,

      Great find. It looks like I had an error in the code from a legacy function there. I’ve updated the tutorial.

      The like should read:
      pasteMultiCol(source,destination, sourceCols, destinationCols, doneCol);
      };

      In order to execute the the function.
      Note the runsies() function is a sample function you could execute pasteMultiCol in any of your functions.

      Thanks again for the heads up.

      Cheers,

      Yagi 🐐

  7. Hello, I’m getting the following error – would you be able to help fix?

    TypeError: Cannot read property ‘getDataRange’ of undefined
    pasteMultiCol @ Code.gs:10

    1. Hi Krystal,

      Try setting up the Globals in your runsies() function first and then run that function instead of the pasteMultiCol() function.

      Cheers,

      Yagi

  8. Hi, Thanks, that’s exactly what I was looking for, except i want to merge two of your examples together.

    This one & when I add a value to a cell in a selected column, I want it to move to another Google Sheet.(https://yagisanatode.com/2019/02/13/google-apps-script-when-i-add-a-value-to-a-cell-in-a-selected-column-i-want-it-to-move-to-another-google-sheet/)

    So if cell = yes, then copy certain columns from row to specific columns in a new sheet

    Thank you!

    1. Hi Arief,

      My guess is that it might be an issue with your other columns containing data like formulas in rows past the row you want to copy your data in the Destination.

      Give this a try:

      You might also find this post useful:
      Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes

      ~Yagi

      1. hey,

        I’m getting the same error as much around here, but I don’t seem capable to sort it.
        Error
        TypeError: Cannot read property ‘getDataRange’ of undefined
        pasteMultiCol @ Untitled.gs:10

        I know you mention something about calling the runsies, but please could you be more specific in what to do? I would appreciate a lot! Thank you in advance for that

        1. Hi Rafael,

          There are two functions in this code the pasteMultiCol (line 8) and the runsies (Line 62). The runsies function is the sample function that grabs all the variables needed and then calls the pasteMultiCol function.

          In the Google Apps Script editor (IDE), to run a different function you can go to the menu bar and between the ‘Debug’ and ‘Execution log’ buttons, there is a drop-down of all the functions in your project. Select runsies, and then hit run and see how if this fixes your error.

          Cheers,

          Yagi

Leave a Reply