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:

sourceDataRange.copyTo(destination);
sourceDataRange.copyTo(destinationStart,
                       SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTH);

sourceDataRange.copyTo(destination);

sourceDataRange.copyTo(destinationStart,

SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTH);

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:

Get the start row and row length of your range of data.
Iterate through each of the selected rows.
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:

Get your destination ranges
Iterate through each row of the destination range
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.

/**
 * GET THE ROW HEIGHTS OF A SELECTED RANGE OF A SOURCE SHEET
 * @param {object} range - Selected source range
 * @returns {Array.<number>}  Array of row heights for each row
 */
function getRowHeights(range) {
 
 var rngRowStart = range.getRow();
 var rngRowHeight = range.getHeight() + rngRowStart;
 
 var rowHeights = []
 var rangeSheet = range.getSheet()
 
 for( var i = rngRowStart; i < rngRowHeight; i++){
   var rowHeight = rangeSheet.getRowHeight(i);
   rowHeights.push(rowHeight);
 };
 return rowHeights;
};


/**
 * SET THE ROW HEIGHTS OF A SELECTED RANGE OF A DESTINATION SHEET
 * @param {Array.<number>} rHeights - row heights from getRowHeights(rng);
 * @param {object} destRange - destionation range of copied data.
 */
function setRowHeights(rHeights,destRange){
 
 var rngRowStart = destRange.getRow();
 var rngRowHeight = destRange.getHeight() + rngRowStart;
 
 var destSheet = destRange.getSheet();
 Logger.log(destSheet.getName());
 var count = 0;
 for( var i = rngRowStart; i < rngRowHeight; i++){
   destSheet.setRowHeight(i,rHeights[count]);
   
   count+=1
 };
};

/**

* GET THE ROW HEIGHTS OF A SELECTED RANGE OF A SOURCE SHEET

* @param {object} range - Selected source range

* @returns {Array.<number>} Array of row heights for each row

function getRowHeights(range) {

var rngRowStart = range.getRow();

var rngRowHeight = range.getHeight() + rngRowStart;

var rowHeights = []

var rangeSheet = range.getSheet()

for( var i = rngRowStart; i < rngRowHeight; i++){

var rowHeight = rangeSheet.getRowHeight(i);

rowHeights.push(rowHeight);

};

return rowHeights;

};

/**

* SET THE ROW HEIGHTS OF A SELECTED RANGE OF A DESTINATION SHEET

* @param {Array.<number>} rHeights - row heights from getRowHeights(rng);

* @param {object} destRange - destionation range of copied data.

function setRowHeights(rHeights,destRange){

var rngRowStart = destRange.getRow();

var rngRowHeight = destRange.getHeight() + rngRowStart;

var destSheet = destRange.getSheet();

Logger.log(destSheet.getName());

var count = 0;

for( var i = rngRowStart; i < rngRowHeight; i++){

destSheet.setRowHeight(i,rHeights[count]);

count+=1

};

Quick use

The script above has two functions:

getRowHeights(range)
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.

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

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

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.

function run(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var rng = sheet.getDataRange();
  
  var destCell = "C5"

  //Copy and paste range into new location
  
  var destinationSheet = ss.getSheetByName("Sheet2");
  var destinationStart = destinationSheet.getRange(destCell);
  var columnWidths = SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS
  rng.copyTo(destinationStart);
  rng.copyTo(destinationStart,columnWidths,false); 

  
};

function run(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("Sheet1");

var rng = sheet.getDataRange();

var destCell = "C5"

//Copy and paste range into new location

var destinationSheet = ss.getSheetByName("Sheet2");

var destinationStart = destinationSheet.getRange(destCell);

var columnWidths = SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS

rng.copyTo(destinationStart);

rng.copyTo(destinationStart,columnWidths,false);

};

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.

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

This is how we added them:

function run(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var rng = sheet.getDataRange();
  
  var destCell = "C5"

  var row_heights = getRowHeights(rng);
  
  //Copy and paste range into new location
  
  var destinationSheet = ss.getSheetByName("Sheet2");
  var destinationStart = destinationSheet.getRange(destCell);
  var columnWidths = SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS
  rng.copyTo(destinationStart);
  rng.copyTo(destinationStart,columnWidths,false); 
  
  var destinationRange = destinationSheet.getRange(destinationStart.getRow(),
                                                   destinationStart.getColumn(),
                                                   rng.getHeight());
  
  var set_row_heights = setRowHeights(row_heights,destinationRange);
  
};
 
 
/**
 * GET THE ROW HEIGHTS OF A SELECTED RANGE OF A SOURCE SHEET
 * @param {object} range - Selected source range
 * @returns {Array.<number>}  Array of row heights for each row
 */
function getRowHeights(range) {
 
 var rngRowStart = range.getRow();
 var rngRowHeight = range.getHeight() + rngRowStart;
 
 var rowHeights = []
 var rangeSheet = range.getSheet()
 
 for( var i = rngRowStart; i < rngRowHeight; i++){
   var rowHeight = rangeSheet.getRowHeight(i);
   rowHeights.push(rowHeight);
 };
 return rowHeights;
};


/**
 * SET THE ROW HEIGHTS OF A SELECTED RANGE OF A DESTINATION SHEET
 * @param {Array.<number>} rHeights - row heights from getRowHeights(rng);
 * @param {object} destRange - destionation range of copied data.
 */
function setRowHeights(rHeights,destRange){
 
 var rngRowStart = destRange.getRow();
 var rngRowHeight = destRange.getHeight() + rngRowStart;
 
 var destSheet = destRange.getSheet();
 Logger.log(destSheet.getName());
 var count = 0;
 for( var i = rngRowStart; i < rngRowHeight; i++){
   destSheet.setRowHeight(i,rHeights[count]);
   
   count+=1
 };
};

function run(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("Sheet1");

var rng = sheet.getDataRange();

var destCell = "C5"

var row_heights = getRowHeights(rng);

//Copy and paste range into new location

var destinationSheet = ss.getSheetByName("Sheet2");

var destinationStart = destinationSheet.getRange(destCell);

var columnWidths = SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS

rng.copyTo(destinationStart);

rng.copyTo(destinationStart,columnWidths,false);

var destinationRange = destinationSheet.getRange(destinationStart.getRow(),

destinationStart.getColumn(),

rng.getHeight());

var set_row_heights = setRowHeights(row_heights,destinationRange);

};

/**

* GET THE ROW HEIGHTS OF A SELECTED RANGE OF A SOURCE SHEET

* @param {object} range - Selected source range

* @returns {Array.<number>} Array of row heights for each row

function getRowHeights(range) {

var rngRowStart = range.getRow();

var rngRowHeight = range.getHeight() + rngRowStart;

var rowHeights = []

var rangeSheet = range.getSheet()

for( var i = rngRowStart; i < rngRowHeight; i++){

var rowHeight = rangeSheet.getRowHeight(i);

rowHeights.push(rowHeight);

};

return rowHeights;

};

/**

* SET THE ROW HEIGHTS OF A SELECTED RANGE OF A DESTINATION SHEET

* @param {Array.<number>} rHeights - row heights from getRowHeights(rng);

* @param {object} destRange - destionation range of copied data.

function setRowHeights(rHeights,destRange){

var rngRowStart = destRange.getRow();

var rngRowHeight = destRange.getHeight() + rngRowStart;

var destSheet = destRange.getSheet();

Logger.log(destSheet.getName());

var count = 0;

for( var i = rngRowStart; i < rngRowHeight; i++){

destSheet.setRowHeight(i,rHeights[count]);

count+=1

};

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.

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

When we run() our function we will now also copy and paste the 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.

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.

~Yagi

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

setRowHeight()

The Shortcut

Quick use

getRowHeights(range)

setRowHeights(row heights, destination range)

The example

Conclusion

Like this:

Related

Leave a ReplyCancel reply

setRowHeight()

The Shortcut

Quick use

getRowHeights(range)

setRowHeights(row heights, destination range)

The example

Conclusion

Share this:

Like this:

Related

Leave a ReplyCancel reply