Google Apps Script: Maintain Row Heights When Copying and Pasting Data in Google Sheets

Maintaining Row height in copy and paste in Google Sheets with Google Apps Script

Google Apps Script: getRowHeight, setRowHeight

Copying and pasting data while maintaining row heights and widths can be a frustrating business in Google Apps Script. You see, there is a difference in how to set column width and set row heights.

Let’s say we have already copied our data over with something like:

sourceDataRange.copyTo(destination)

Google Apps Script provides us with a great set of enumerators for copying and pasting, the CopyPasteType.

To ensure that we maintain the same column width in our destination that was copied from our source, we need to run another copyTo() method of our sourceDataRange with the CopyPasteType.PASTE_COLUMN_WIDTHS. So all together it might look a little like this:

Okay, okay, a little extra work, but pretty straight forward. We get our column width in the end.

Now by this stage, you might be thinking, Yagi, why are you even blogging about this. Seems pretty straight forward, ya just gotta do the same with heights!

Nope. This is the point where we just have to remind ourselves that we love Google Apps Script for her flaws and all. Right? Right?!

For, I am sure, a very logical reason for the developers of Google Apps Script, copying and pasting the width has to be done row-by-row.

Here’s how you would do it the long way.

Scroll down to The Shortcut if you just want to copy and paste the functions into your code.

The getRowHeight() method is part of the sheet class. getRowHeight(arg) takes one argument, the row number. To get all the row heights of your selected data, you must:

  1. Get the start row and row length of your range of data.
  2. Iterate through each of the selected rows.
  3. Store the heights in an array for pasting later.

setRowHeight()

Once you have all the row heights you can then use the setRowHeight() method of the sheet class to changes the heights in the selected rows. setRowHeight(arg, arg2) takes two arguments, the row number, and the height.

To set all the row heights, you must:

  1.  Get your destination ranges
  2. Iterate through each row of the destination range
  3. Use the setRowHeight to add the row height from the array you created from getRowHeight.

It’s a bit of a pain in the bum.

The Shortcut

 

The following is a quick script designed to help maintain the row height of copy and pasted data.

Quick use

The script above has two functions:

  1. getRowHeights(range)
  2. setRowHeights(row heights, destination range)

The two functions are designed to be used in conjunction with your own copy and paste script.

I have separate this into two different functions in case there is a need to adjust the heights once they have been collected from the source range.

These function can be used when copying in one Sheet tab, between sheet tabs on a spreadsheet or into another spreadsheet.

getRowHeights(range)

The getRowHeights(range) function takes your source range and iterates through each row returning the height value in an array.

The function takes one argument, the range of the source data you want to get the height values for.

setRowHeights(row heights, destination range)

The setRowHeights(rowHeights, dest range) function takes two arguments. The first one is taken from the array of heights you returned from the getRowHeights() function. The second argument is the destination range.

You will need to ensure that the destination range matches the source range otherwise Google Apps Script will throw an error.

As the name suggests the function sets the heights of the destination range using the source range heights.

The example

In the below image I have some formatted data in my Google Sheet Sheet1 tab that I want to copy into my Sheet2 tab, but have it paste from cell “C5”.

copy and paste Google Sheets

First, let’s take a look at our basic code to copy and paste everything except the row height. Then we can see later how getRowHeights() and setRowHeights() all fit into the code.

In our run() function we set our Spreadsheet, sheet and range variables in lines 1-4. We then identify our destination cell on line 6. The destination cell can be on the same sheet, or a different sheet in the spreadsheet or on another spreadsheet.

Line 10 then gets our destination sheet, in our case Sheet2 of the same spreadsheet.

We then grab the range of the start destination (Line  11).

To make things a bit tidier, we create a variable to hold our copyPasteType which will set our column width.

Finally, on lines 13-14 we first paste the data including the formatting and then paste in the column widths.

The result? It’s…okay. It does expand to fit the larger text and all, but it definitely does not have the row height.

copy and paste Google Sheet maintain column width

No problem we can fix this with our two new functions.

This is how we added them:

The highlighted parts are new code added to the run() function. On line 8 we call the getRowHeight() function, feeding in the data range from the source sheet.

Then once we have applied our paste of data with formatting and column width to the destination sheet, we then get the destination range. To do this we get the row of the destinationStart variable and then its column. Finally, we add the height of the original range of the source sheet (Lines 18-20).

Row 22 then runs the setRowHeight() function taking the row_heights we generated back in line 8 from our getRowHeight() function.  In the second argument of the setRowHeight(), it takes the newly created destinationRange variable created back on line 18.

When we run() our function we will now also copy and paste the row height.

Google Apps Script copy paste maintain row height

Conclusion

One thing to be wary of is that these two functions have to iterate through each row to get and set the row heights. For larger copy and paste documents, this might be quite costly as we are continually making calls to the Google Sheet (see: Iterating through Google Sheets The right and Wrong Way). This will then make the program run significantly longer.

So did you find a use for these two handy functions? It’s always nice to hear what people are using things for.

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

 

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

~Yagi

Leave a Reply