Google Apps Script – Copy and Paste Cells While Retaining Column Width in Google Sheets.

Paste without column widths.

Google Apps Script

Have you ever copied and pasted something in Google Sheets only to be frustrated with the fact that it stubbornly refuses to paste the column widths? I mean, everything else is perfect, the formatting, the formulas the comments, they all were pasted across just fine. However, that dastardly column width just does not budge.

copied Google Sheet
When you want to copy and paste this…
Paste without column widths.
…but you get this.

The same is true when you use the copyTo method in Google Apps Script.  copyTo does essentially the same thing as copy and paste within the spreadsheet UI.

It takes the range you are copying from and pastes it the range you are, erh…pasting it into.

copyRange.copyTo(pasteRange);

This essentially takes all the values, formulas and formatting in the range and plonks it into the target location. Of course, without that much prized column width.

To add the column width we need to also include a separate copyTo that adds the column width separately.

copyTo can take 3 arguments:

copyTo(destination, copyPasteType, transposed)

We can use the copyPasteType to call an enumeration to get the column width of the selected range. So the second argument after the destination argument would look like this:

SpreadsheetApp.copyPasteType.PASTE_COLUMN_WIDTHS

Enumerations are identified in Google Apps Script by their all-caps, cause they’re yelly.

So to copy and paste all the ranges formatting and column width, your code might look a little like this:

The Example

I have a selected range from a Stock Orders list I want to copy and paste across programatically to a new sheet. I want to keep all the formatting and, of course, the column widths.

Let’s take a look at the sheet I want to copy from.

copied Google Sheet

I want to select the range from A1:G6 only and paste it into a new sheet.

Here is the code:

I first set my global variables to get my spreadsheet (Line 9) and then get the sheet I am copying from (Line 10), in this case “Weapons”.

In line 13, I start my function, copyAllInRange(). In this function, I first need to create a new sheet that I have named, “Copy with Format” (Line 15).

Next, I get the range I want to copy from the “Weapons” sheet (Line 17). In this case I am happy with the location of data, so I won’t alter the paste location. I then set the paste location of the newSheet, aka “Copy with Format” the same as the “Weapons” sheet (Line 19).

On line 21, I create the variable columnWidth and set the enumerator to PASTE_COLUMN_WIDTH.

Then I copy data from the “Weapons” sheet range and paste it into the newly created “Copy with format” sheet range (Line 22).

Finally, it’s time to add the column width by calling the copyTo method a second time, but this time including the the columnWidth variable. This second call will only get the column widths of the data and paste it in the new sheet.

The result gives us this:

Copy with format and column width in Google Sheets

At last. Copy and paste with the width I want.

Leave a Reply

Your email address will not be published. Required fields are marked *